Difference between revisions of "CSC220 Homework 5 2010"

From dftwiki3
Jump to: navigation, search
(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)


Page under construction!
UnderConstruction.jpg

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