icorson3 · July 15, 2016 02:54 · Jul 15, 2016 · Jul 11, 2016
diff --git a/ilana_intermediate_sql.md b/ilana_intermediate_sql.md
@@ -6,7 +6,18 @@ 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.
-What's the total revenue for all main courses?
-
-
+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
diff --git a/ilana_intermediate_sql.md b/ilana_intermediate_sql.md
@@ -0,0 +1,12 @@
+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.
+What's the total revenue for all main courses?
+
+
No results found