##What is an INNER JOIN? Inner join returns records that match in both tables. An inner join requires each row in the two joined tables to have matching rows ##What is an LEFT OUTER JOIN? Left outer join has all tables in left table (say A). returns all the values from an inner join plus all values in the left table that do not match to the right table, including rows with empty values in the link column. ##What is an RIGHT OUTER JOIN? A right outer join is just like left outer join reversed. Every row from the "right" table (say B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those rows that have no match in B.
##Question - What does SERIAL do?
SERIAL is used for ids
##Write queries for the following:
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?
What the count for items with a name?
SELECT count(*) FROM items;
##Write queries for the following:
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';
##INNER JOINS first query, we are going to grab each item and its season using an INNER JOIN.
SELECT * FROM items INNER JOIN seasons on items.season_id = seasons.id;
get it to display only the name for the item and the name for the season? 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 items.name, categories.name FROM categories INNER JOIN item_categories ON item_categories.category_id = categories.id INNER JOIN items ON items.id = item_categories.item_id WHERE items.id = 7;
Can you change the column headings?
SELECT items.name AS item_name, categories.name AS category_name FROM categories INNER JOIN item_categories ON item_categories.category_id = categories.id INNER JOIN items ON items.id = item_categories.item_id WHERE items.id = 7;
##OUTER JOINS Notice the result when we run an INNER JOIN on items and seasons.
SELECT * FROM items INNER_JOIN seasons on items.season_id = seasons.id;
A LEFT OUTER JOIN will return all records from the left table (items) and return matching records from the right table (seasons). Update the previous query and the return value
SELECT * FROM items i LEFT OUTER JOIN seasons s ON i.season_id = s.id;
What do you think a RIGHT OUTER JOIN will do?
SELECT * FROM items i RIGHT OUTER JOIN seasons s ON i.season_id = s.id;
##SUBQUERIES Calculate the average 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 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(revenue) FROM (SELECT * FROM items INNER JOIN item_categories ON items.id = item_categories.item_id INNER JOIN categories ON item_categories.category_id = categories.id WHERE categories.id = 2) AS items;
Write a query that returns the sum of all items for each category. Oh! GROUP BY :)
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;