#<pre>
#
# this is the third assignment, you should have completed tutorial.sql and select.sql
# for each query you proceed it with its query number
# (i.e., i1 .. i15 and a brief comment on  what it does

#  for the query....

# i1..i2 given comments, write query, Video 1 Intro Functions & Joining
# i3..i6 given comments, write query video 2 Insert & delete 
# i7..i11 given comments, write query video 3 Functions

# 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 https://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 display each inventory row for apple

#i2 display total quantity of apples in inventory as name, sum( quantity )
/**
  *   This is a difficult query, in PHPMyAdmin
  *   first write a query to display fruitID of apple
  *   then write a query to list all inventory rows for apple
  *   then write a query to sum( quantity ) of all inventory rows for apple
  *   then combine frut and inventory tables display name, sum( quantity )
  * Remember you can only submit one query for #i, but the one query can use
  * a sub-query, join two tables and also use the sum function.
  **/

#i3 delete peach from fruit table - 
# make sure delete peach, if not i4 will fail;
# nota bene, the tables we use for first 2 videos sets do not have peach in them
# the grading database has different content than the initial videos
# grading db starts of with a peach in it.

#i4 Display contents of table fruit, sort by fruitID in ascending order

#i5 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

#i6 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

# video on mysql functions

#i7 display fruit table, as fruitID, name, price but 
#prefix each price with $, sort by fruitID ASC

#i8 display average price of Table:fruit

#i9 display name, price for all fruit priced above overall average price,
# do this by making i8 a subquery

#i10 display the total number of pieces of fruit in inventory

#i11  display fruitID, and total inventory quantity for each fruit in inventory
# with a total quantity greater than 300. 

#real power comes when you join tables and use functions.

#i12 display each inventory row as fruitID, value where value is 
# quantity * price, with the prefix $

#i13 display name, price and sum quantity for all fruit, even ones with quantity null

#i14 display name & value for all fruit in inventory 
#  value is sum( quantity ) * price and each value field should start with a  $

# boolean and/or examples 

#i15 display name, price and quantity for all fruit that have
# inventory quantity line greater than 400 

#i16 looking at only fruit with inventory
# display name, price and quantity for all fruit that have 
# inventory quantity line greater than 200 or have a price
# greater than 2.50 sort fruitID ascending, then by quantity asc
# this is just order by fruit.fruitID asc, quantity asc;
# if use a comma to join tables you will have 3 items in with and/or where clause

# write the last 4 own your own as an exercise

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

#i18 display name and price for the least 3 expensive fruit item, order by price asc

#i19 display only the price of the highest priced item,
# display should have column header "Max Price" and prefix price with $ 
# you must use an aggregate function

#i20 write a query to display the number of lines in inventory
# column header is "inventory rows"