Paula Noone SQL - Summer, 2009 Select Assignment #1. Display fruit table SELECT * FROM fruit LIMIT 0 , 30 fruitID Primary key name Fruit name price Fruit price 1 apple 2.50 2 pear 3.00 4 grape 0.75 8 pluot 4.50 6 plum 1.25 3 banana 1.00 #2. Display 1st 3 rows of Inventory SELECT * FROM Inventory LIMIT 3 FruitID Foreign key to Fruit table Quantity Shipment quantity 1 100 3 500 2 100 #3. Display name, quantity sum, price sum, quantity * price as value from fruit and Inventory tables SELECT name, sum( price ) , sum( Quantity ) , Quantity * price AS Value FROM fruit, Inventory GROUP BY name LIMIT 0 , 30 name Fruit name sum(price) sum(Quantity) Value apple 15.00 1160 250.00 banana 6.00 1160 100.00 grape 4.50 1160 75.00 pear 18.00 1160 300.00 plum 7.50 1160 125.00 pluot 27.00 1160 450.00 #4. Count records in fruit SELECT count( * ) FROM fruit (*) 6 #5. Show fruit sorted by price in desc order SELECT * FROM fruit GROUP BY price DESC LIMIT 0 , 30 fruitID Primary key name Fruit name price Fruit price 8 pluot 4.50 2 pear 3.00 1 apple 2.50 6 plum 1.25 3 banana 1.00 4 grape 0.75 #6. Output FruitID of banana SELECT FruitID FROM fruit WHERE name = "banana" LIMIT 0 , 30 FruitID 3 #7. Display total # of fruits in Inventory SELECT count( * ) FROM fruit (*) 6 #8. Display fruit ordered by fruitID in asc order from fruit table SELECT * FROM fruit ORDER BY fruitID ASC LIMIT 0 , 30 fruitID Primary key name Fruit name price Fruit price 1 apple 2.50 2 pear 3.00 3 banana 1.00 4 grape 0.75 6 plum 1.25 8 pluot 4.50 #9. Display FruitID in Inventory SELECT FruitID FROM Inventory LIMIT 0 , 30 FruitID Foreign key to Fruit table 1 3 2 5 1 2 #10. Display name, price from fruit SELECT name, price FROM fruit LIMIT 0 , 30 name Fruit name price Fruit price apple 2.50 pear 3.00 grape 0.75 pluot 4.50 plum 1.25 banana 1.00 #11. Describe Inventory table Field Type Null Key Default Extra FruitID tinyint(11) NO NULL Quantity int(11) NO NULL Re-Muliple Times - last attempt is recorded