SERIAL generates id's
SELECT sum(revenue) FROM items;
SELECT avg(revenue) FROM items;
SELECT max(revenue) FROM items;
SELECT min(revenue) FROM items;
SELECT count(name) FROM items;
SELECT count(*) FROM items;
SELECT max(revenue), min(revenue) FROM items;
SELECT course 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 items_categories ON items.id=items_categories.item_id INNER JOIN categories ON items_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 items_categories ON items.id=items_categories.item_id INNER JOIN categories ON items_categories.category_id=categories.id WHERE items.name='arugula salad';
SELECT*FROM items WHERE revenue > (SELECT avg(revenue) FROM items);
SELECT*FROM items WHERE revenue < (SELECT avg(revenue) FROM items);
SELECT SUM(revenue) FROM items INNER JOIN items_categories ON items.id = items_categories.item_id INNER JOIN categories ON categories.id = items_categories.category_id WHERE categories.name = 'dinner';
SELECT SUM(revenue) FROM items INNER JOIN items_categories ON items.id = items_categories.item_id INNER JOIN categories ON categories.id = items_categories.category_id GROUP BY categories.name;
- What is an INNER JOIN? - an inner join combines the rows of 2 tabls that match up
- What is a LEFT OUTER JOIN? - A LEFT OUTER JOIN is one of the JOIN operations that allow you to specify a join clause. It preserves the unmatched rows from the first (left) table, joining them with a NULL row in the shape of the second (right) table
- What is a RIGHT OUTER JOIN? - A RIGHT OUTER JOIN is one of the JOIN operations that allow you to specify a JOIN clause. It preserves the unmatched rows from the second (right) table, joining them with a NULL in the shape of the first (left) table.