ilana_intermediate_sql.md

What's the total revenue for all items? select sum(revenue) from items; What's the average revenue for all items? select avg(revenue) from items; What's the minimum revenue for all items? select min(revenue) from items; What's the maximum revenue for all items? select max(revenue) from items; What the count for items with a name? select count(name) from items; What else can you pass to count and still get 5 as your result? select count(items) from items; Return all main courses. Hint: What ActiveRecord method would you use to get this? select items from items where(course = 'main'); Return only the names of the main courses. select name from items where(course = 'main'); Return the min and max value for the main courses. select max(revenue), min(revenue) from items where(course = 'main'); What's the total revenue for all main courses? select sum(revenue) from items where(course = 'main'); Can you get it to display only the name for the item and the name for the season? select items.name, seasons.name from items inner join seasons on items.season_id = seasons.id Having two columns with the same name is confusing. Can you customize each heading using AS? select items.name as item_name, seasons.name as season_name from items inner join seasons on (items.season_id = seasons.id); Write a query that pulls all the category names for arugula salad. Hint: Use multiple INNER JOINs and a WHERE clause. select i.name, c.name from items i inner join item_categories ic on i.id = ic.item_id inner join categories c on c.id = ic.category_id where i.name = 'arugula salad'; Can you change the column headings? select i.name as item_name, c.name as category_name from items i inner join item_categories ic on i.id = ic.item_id inner join categories c on c.id = ic.category_id where i.name = 'arugula salad'; Write a query to test your guess. (right outer join): select * from items i right outer join seasons s on i.season_id = s.id; Calculate the average revenue. select * from items where revenue > (select avg(revenue) from items); Write a WHERE clause that returns the items that have a revenue greater than that average. select * from items where revenue < (select avg(revenue) from items); Write a query that returns the sum of all items that have a category of dinner. select sum(revenue) from items i inner join item_categories ic on ic.item_id = i.id inner join categories c on ic.category_id = c.id where c.name = 'dinner'; Write a query that returns the sum of all items for each category. select c.name, sum(i.revenue) from categories c inner join item_categories ic on c.id = ic.category_id inner join items i on i.id = ic.item_id group by c.name; What is an INNER JOIN? things that are in both tables What is a LEFT OUTER JOIN? all the left table information and queried info from the right table What is a RIGHT OUTER JOIN? all the right table information and queried info from the left table