Difference between revisions of "CSC220 Homework 5 2010"
(→Dry Run) |
(→Dry Run) |
||
Line 45: | Line 45: | ||
SELECT `itemId` FROM `orders` WHERE `userId` IN ( | SELECT `itemId` FROM `orders` WHERE `userId` IN ( | ||
SELECT `userId` FROM `users` WHERE `userName`='Marie') ); | 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): | ||
+ | |||
+ | <code><pre> | ||
+ | ==> 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 | ||
+ | </pre></code> |
Revision as of 17:36, 13 October 2010
--D. Thiebaut 20:04, 13 October 2010 (UTC)
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
- Login to your 220a-xx account on one of the Hadoop machines.
- 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