Difference between revisions of "CSC220 Homework 5 2010"
(→Dry Run) |
(→Dry Run) |
||
Line 30: | Line 30: | ||
<br /> | <br /> | ||
− | * Login to your 220a-xx account on one of the Hadoop machines. | + | * 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): [[CSC220 testSql.php | testSql.php]]. | * 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): [[CSC220 testSql.php | 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). | * 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). |
Revision as of 17:41, 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
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!
- 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