Submitting your work: Keep a gist with the queries you write.
- SELECT sum(revenue) FROM items;
- SELECT ROUND(avg(revenue),2) FROM items;
- SELECT min(revenue) FROM items;
- SELECT max(revenue) FROM items;
- SELECT count(*) FROM items WHERE name IS NOT null;
- SELECT * FROM items WHERE course = 'main';
- SELECT name FROM items WHERE course = 'main';
- SELECT min(revenue), max(revenue) FROM items WHERE course = 'main';
- SELECT sum(revenue) FROM items WHERE course = 'main';
- select items.name, seasons.name FROM items INNER JOIN seasons ON items.season_id = seasons.id;
- select items.name as item_name, seasons.name as season_name FROM items INNER JOIN seasons ON items.season_id = seasons.id;
- SELECT items.name, categories.name FROM items INNER JOIN item_categories on items.id = item_categories.item_id INNER JOIN categories on item_categories.category_id = categories.id WHERE items.name = 'arugula salad';
- SELECT items.name as item_name, categories.name as category_name FROM items INNER JOIN item_categories on items.id = item_categories.item_id INNER JOIN categories on item_categories.category_id = categories.id WHERE items.name = 'arugula salad';
- SELECT * FROM items i RIGHT OUTER JOIN seasons s ON i.season_id = s.id;
- INSERT INTO seasons(name) VALUES ('fall');
- SELECT * FROM items where revenue > (SELECT avg(revenue) from items);
- SELECT sum(revenue) 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.name = 'dinner';
- SELECT categories.name as name, sum(revenue) FROM items INNER JOIN item_categories on items.id = item_categories.item_id INNER JOIN categories on item_categories.category_id = categories.id GROUP BY categories.name;
Answer the following questions:
What is an INNER JOIN?
- takes everything from the first table that has a matching row from the second table and brings in all fields from both tables.
What is a LEFT OUTER JOIN?
- takes everything from the first table whether or not it has a matching row from the second table and brings in all fields from both tables.
What is a RIGHT OUTER JOIN?`
- takes everything from the second table whether or not it has a matching row from the first table and brings in all fields from both tables.