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

• 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') );