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...
