###Aggregate Function Queries ######Total Revenue for All Items:
SELECT sum(revenue) FROM items;######Average Revenue for All Items:
SELECT avg(revenue) FROM items;######Maximum Revenue for All Items:
SELECT max(revenue) FROM items;######Minimum Revenue for All Items:
SELECT min(revenue) FROM items;######Count Revenue for All Items:
SELECT count(revenue) FROM items;######Count Rows in a Table Without Using id:
SELECT count(*) FROM items;###Building on Aggregate Function Queries ######Return All Main Courses:
SELECT * FROM items WHERE course='main';######Return All Main Course Names:
SELECT name FROM items WHERE course='main';######Return the Min and Max Values for Main Courses:
SELECT min(revenue), max(revenue) FROM items WHERE course='main';######Return the Total Revenue for Main Courses:
SELECT sum(revenue) FROM items WHERE course='main';###Inner Joins ######Join Items and Season by Season ID and Only Display/Rename Their Names:
SELECT a.name AS item_name, b.name AS season_name FROM items a
INNER JOIN seasons b
ON a.season_id = b.id;######All Category Names for Arugula Salad:
SELECT a.name AS item_name, c.name AS category_name
FROM items a, item_categories b, categories c
WHERE a.id=b.item_id AND c.id=b.category_id AND a.name='arugula salad';###Outer Joins ######Left Join Items and Seasons:
SELECT * from items a
LEFT OUTER JOIN seasons b
on a.season_id = b.id;###Subqueries ######All Items with Less than Average Revenue:
SELECT * FROM items
WHERE revenue < (SELECT avg(revenue) FROM items);###Additional Challenges ######Sum of All Items that have Category Dinner:
SELECT SUM(a.revenue)
FROM items a, item_categories b, categories c
WHERE a.id=b.item_id AND c.id=b.category_id AND c.name='dinner';######Sum of All Items by Category:
SELECT c.name, sum(a.revenue)
FROM items a, item_categories b, categories c
WHERE a.id=b.item_id AND c.id=b.category_id
GROUP BY c.name;###Follow Up Questions ######What is an INNER JOIN? An inner join is a query that collects the specifed information from the SELECT statement that meets all criteria given in the WHERE statement. If a record does not match all of the specifications, it will not be included in the resulting table.
######What is a LEFT OUTER JOIN? A left outer join is a query that returns every record from the left (first) table. It will join on the specified information from the right table to the left table if it satisfies the conditions in the WHERE statement. If a data entry from the left table has no appropriate data joined on from the right table, it will still appear in the resulting query. The resulting table should have the same amount of records as the original left table has.
######What is a RIGHT OUTER JOIN? A right outer join is a query similar to the left outer join. As an outer join, all tables from the specifed table (Right or Second in this case) will be represented in the resulting query at least once. Typically in an outer join (used to represent a one-to-many relationship) the table that has one of the other table is the left table, and the table that has many of the other is the right table. This is why in a left outer join there is typically as many rows in the resulting table as there are in the left table. For the right outer join, there are typically as many rows as there are total relationships. For example, if there as an entry in the right table that has 4 matches in the left table, there would be 4 entries in the resulting table. However, unlike the left outer join, if there as a data entry in the left table that does not have a relationship to anything in the right table, it will not be represented in the resulting table.