#<pre>
# this is the second assignment, you should have completed tutorial.sql first
# for each query you proceed it with its query number
# (i.e., i1 .. i15 and a brief comment on  what it does
# for the first queries I both the comment and the query
#
# for queries 2 and 3 I have given you the query, you need to write the comments
# note for comment  lines you use #
# programs which grade sql queries drop # and blank lines;
# but you need to make sure you have a query for each questions

# This file assumes you have already completed the tutorial
# if you dropped the fruit and inventory tables, recreate them before running script
#
# it also assumes you have the two tables fruit and inventory
# if not source /home/premiere/fruit.sql
# or get fruit table at http://cim.saddleback.edu/~premiere

# Remember "Grape" double quotes for strings,
#          '20' single quotes for literals
#          `fruit`.`fruitID` accent for table and field names
#			however for submit never specify a table name
#			do not use select * from fruit.fruit;
#			use select * from fruit;
#
# intermediate MySQL Videos will help you complete the assignment

#i1 show tables in the current selected database
# for your current database it should be two fruit and inventory
show tables;

#i2 Show the name and price of above average priced fruit
select name,price from fruit where price > (select avg( price ) from fruit );

#i3 Show the fruit ID, name, and unit price with dollar sign of the fruit
select fruitID, name,

concat( "$", price) as "unit price",

(select sum( quantity ) from inventory where inventory.fruitID = fruit.fruitID )
as "total quantity"

from fruit order by fruitID ASC;

#i4 Write query to insert peach w fruitID 5, name peach and price $1.25
#note I cannot test the output from insert so this query will pass
#but if the insert is wrong the next query will fail

#i5 display fruit name and price  sorted by ascending price, then by name lexicographically
# hint this is just order by price asc, name asc;
# hint remember never specify database names with submit


#i6 delete peach from fruit table -
# make sure insert and delete work, if not you may get errors later

#i7 display each inventory row for apple
#i8 Display name, and sum( quantity ) for Apple
#i9 display fruit row of only most expensive fruit, hint sort descending and limit, discussed in video 1

#i10 display average price of Table:fruit
#i11 display fruit table, as fruitID, name, price but prefix each price with $, sort by fruitID ASC
#i12 display name & value for all fruit in inventory
# where value is sum( quantity ) * price and each value field should start with a  $


#i13 display name, price and sum( quantity ) for all fruit that have inventory, order by name lexicographically

#i14 display name, price and quantity for all fruit
# with a single inventory line of quantity greater than 250,

#i15 display name and price for all fruit with prices $1.00 to $2.50 inclusive order by name lexicographically