-
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;
-
Now, write a query that returns a count for all rows without counting the id column (It's not common, but it's not necessary for a table to have an id column). The result should be 5. SELECT COUNT(*) FROM items;
-
Return all main courses. Hint: What ActiveRecord method would you use to get this? SELECT * 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 MIN(revenue), MAX(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 item_name, c.name category_name FROM items i INNER JOIN items_categories ic ON ic.item_id = i.id INNER JOIN categories c ON ic.category_id = c.id WHERE i.name = 'arugula salad';
What do you think a RIGHT OUTER JOIN will do? Write a query to test your guess. SELECT * FROM items i RIGHT OUTER JOIN seasons s ON i.season_id = s.id;
-
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);
-
Without looking at the previous solution, write a WHERE clause that returns the items that have a revenue less than the average revenue 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(i.revenue) FROM items i INNER JOIN items_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 items_categories ic ON ic.category_id = c.id INNER JOIN items i ON ic.item_id = i.id GROUP BY c.name;