CSC220 Homework 5 2010

--D. Thiebaut 20:04, 13 October 2010 (UTC)

---

Page under construction!

This assignment is due on 10/20/10, at 11:59 p.m. + 1 minute. You can work on it in pairs.

Setup

• Your assignment is to write a collection of mysql queries that will be the answers to different questions.

• In order to test these queries easily, you will need to include all of them in a text file (test.sql) that will become a single SQL program. This is the file you will submit for the assignment.

• you will generate the different queries using phpmyadmin running on either hadoop101 or hadoop110.

• You can test the correctness of your test.sql program by testing it with the same php program I will use to grade the assignment. More about this at the end of this page.

Dry Run

This section is just to make sure you format your result file correctly. If you result file does not generate a good output with the setup explained here, it will not work well with the grading program, so pay close attention to details!

This section also assume that you have done Lab 5 and that you have 3 tables named users, items, and orders, with information in them.

• Login to your 220a-xx account on one of the Hadoop machines. (you can ssh to them remotely, no need to be in FH342.)
• Get a copy of the following php program which you will store in your regular main directory (no need to store it in your public_html directory): testSql.php.
• Locate the two lines in the php code that requires editing, and enter your own account and password (the password is the same one as your 220a-xx account on beowulf).
• create a new text file, in the same directory where you put testSql.php and call it test.sql. Store the following information in it:

SELECT * FROM `items`;

SELECT `itemName` FROM `items`;

SELECT `itemName` FROM `items`WHERE `itemId`=2;

SELECT * FROM `users` ORDER BY `userName` ASC;

SELECT * FROM `orders` WHERE `userId`=1;

SELECT `itemName` FROM `items` WHERE `itemId` IN ( 1, 2, 3 );

SELECT `itemName` FROM `items` WHERE `itemId` IN ( SELECT `itemId` FROM `orders` WHERE `userId`=1 );

SELECT `itemName` FROM `items` WHERE `itemId` IN ( 
            SELECT `itemId` FROM `orders` WHERE `userId` IN  ( 
                        SELECT `userId` FROM `users` WHERE `userName`='Marie') );

• You can now make your php program execute all the queries.

php  testSql.php 

• Verify that you get an output similar to this one (although your tables might contain different information):

==> Connected to database


=========================================================
query = SELECT * FROM `items`

---------------------------------------------------------
Result of query: 
1	pencil	1	
2	eraser	0.5	
3	paper	5	
4	ink	30	
5	ruler	1.5	
6	markers	2.5	


=========================================================
query = SELECT `itemName` FROM `items`

---------------------------------------------------------
Result of query: 
pencil	
eraser	
paper	
ink	
ruler	
markers	


=========================================================
query = SELECT `itemName` FROM `items`WHERE `itemId`=2

---------------------------------------------------------
Result of query: 
eraser	


=========================================================
query = SELECT * FROM `users` ORDER BY `userName` ASC

---------------------------------------------------------
Result of query: 
6	Dominique	
5	Elaine	
2	John	
3	Manon	
1	Marie	
4	Rob	


=========================================================
query = SELECT * FROM `orders` WHERE `userId`=1

---------------------------------------------------------
Result of query: 
1	1	1	
2	1	2	
3	1	3	
4	1	4	


=========================================================
query = SELECT `itemName` FROM `items` WHERE `itemId` IN ( 1, 2, 3 )

---------------------------------------------------------
Result of query: 
pencil	
eraser	
paper	


=========================================================
query = SELECT `itemName` FROM `items` WHERE `itemId` IN ( SELECT `itemId` FROM
`orders` WHERE `userId`=1 )

---------------------------------------------------------
Result of query: 
pencil	
eraser	
paper	
ink	


=========================================================
query = SELECT `itemName` FROM `items` WHERE `itemId` IN (              SELECT
`itemId` FROM `orders` WHERE `userId` IN  (                          
SELECT `userId` FROM `users` WHERE `userName`='Marie') )

---------------------------------------------------------
Result of query: 
pencil	
eraser	
paper	
ink