intermediate sql

1. SELECT sum(revenue) FROM items;
2. SELECT avg(revenue) FROM items;
3. SELECT min(revenue) FROM items;
4. SELECT max(revenue) FROM items;
5. SELECT count(name) FROM items;

SELECT count(*) FROM items;

1. SELECT * FROM items WHERE course='main'; (active record: items.where(course: "main"))
2. SELECT name FROM items WHERE course='main';
3. SELECT max(revenue), min(revenue) FROM items WHERE course='main';
4. 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 i.name AS item_name, c.name AS category_name from items i, categories c, items_categories ic
WHERE i.name='arugula salad' AND i.id = ic.item_id AND ic.category_id = c.id;

right outer join will return all of the data for seasons, and matching data from items

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 i, categories c, items_categories ic
WHERE c.id = 2 AND c.id = ic.category_id AND i.id = ic.item_id;

SELECT c.name, sum(revenue) FROM items i, categories c, items_categories ic
WHERE c.id = ic.category_id AND i.id = ic.item_id
GROUP BY c.name;