Skip to content

Instantly share code, notes, and snippets.

@icorson3
Last active July 15, 2016 02:54
Show Gist options
  • Save icorson3/ee925e7f5efeb1fb03c7fa5235e8c58c to your computer and use it in GitHub Desktop.
Save icorson3/ee925e7f5efeb1fb03c7fa5235e8c58c to your computer and use it in GitHub Desktop.

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment