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