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