So I am reading a book from Pratt and it is called A Guide to MySQL. Apparently the book stinks and I am trying to answer some of the questions from the book.

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

#fc2 wait til you cover intrinsics video 2
select name, price from fruit where price > (select avg( price ) from fruit );

#fc3 wait til you cover combining tables video 3
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;

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

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

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

#fc7 display each inventory row for apple

#fc8 Display name, and sum( quantity ) for Apple

#fc9 display fruit row of only most expensive fruit, hint sort descending and limit, discussed in video 1

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

#fc13 display name, price and sum( quantity ) for all fruit that have inventory, 

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

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

insert into `fruit`

(`fruitID`, `name`, `price`)

values ('5','peach','1.25');

select name, price
from fruit
order by price asc;

delete from fruit where fruitID = '5';

SELECT * FROM `inventory` WHERE name = "apple";
select "a";
select "a";

select avg( price ) from fruit;

select fruitID, name, CONCAT ("$", price) as 'price'
from fruit;

select name, CONCAT( "$", SUM( quantity ) * price) as "value"
from fruit, inventory
where fruit.fruitID = inventory.fruitID
GROUP by inventory.fruitID;

Sorry about the comments, they moved around a bit but they each represent a query of their own.

The ones that are "blank" are the ones I struggle with... Can someone help me with queries 7,8,9,13,14,15? I got the others, just those few are troubling me quite a bit...

3 Years
Discussion Span
Last Post by <M/>
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.