Intermediate SQL

intermediate_sql.md

What is an INNER JOIN?

An INNER JOIN combines two tables with matches. Any values that do not have a match are not returned.

What is a LEFT OUTER JOIN?

A LEFT OUTER JOIN will return all rows from the left table, regardless if there is a matching row in the right table. For the values that do not have a matching row on the right table, null is returned. Any rows in the right table that do not have a match are not returned.

What is a RIGHT OUTER JOIN?

A RIGHT OUTER JOIN will return all rows from the right table, regardless if there is a matching row in the left table. For the values that do not have a matching row on the left table, null is returned. Any rows in the left table that do not have a match are not returned.

Queries

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's the count for items with a name?

  SELECT count(name) FROM items;

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 max(revenue) FROM items WHERE course='main';
  SELECT min(revenue) FROM items WHERE course='main';

What's the total revenue for all main courses?

  SELECT sum(revenue) FROM items WHERE course='main';

Can you get it to display only the name for the item and the name for the season?

  SELECT items.name, seasons.name
  FROM items INNER JOIN seasons ON items.season_id = seasons.id;

Having two columns with the same name is confusing. Can you 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 AS item_name, categories.name AS category_name
  FROM categories INNER JOIN items_categories ON categories.id = items_categories.category_id INNER JOIN items ON items.id = items_categories.item_id
  WHERE items.name = 'arugula salad';

What do you think a RIGHT OUTER JOIN will do? Insert data into the right table that will not get returned on an INNER JOIN. Write a query to test your guess.

  INSERT INTO items (name, revenue, season_id) VALUES (NULL, 500, 1), (NULL, 400, 2);
  
  SELECT * FROM items i RIGHT OUTER JOIN seasons s ON i.season_id = s.id

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);