Intermediate_SQL_I.md

Intermediate SQL I

https://github.com/turingschool/lesson_plans/blob/master/ruby_03-professional_rails_applications/intermediate_sql.md

What does SERIAL do?

it creates a unique ID that is an integer and will automatically increment.

Write queries for the following:

1. What's the total revenue for all items?

SELECT sum(revenue) FROM items; 3800

1. What's the average revenue for all items?

SELECT avg(revenue) FROM items; 950

1. What's the minimum revenue for all items?

SELECT min(revenue) FROM items; 500

1. What's the maximum revenue for all items?

SELECT max(revenue) FROM items; 1200

1. What the count for items with a name?

SELECT count(name) FROM items; 4

To include NULL item: SELECT count(*) FROM items; 5

Write queries for the following:

1. Return all main courses. Hint: What ActiveRecord method would you use to get this? SELECT * FROM items WHERE course='main';
2. Return only the names of the main courses. SELECT name FROM items WHERE course='main';
3. Return the min and max value for the main courses. ?? Going to return min/max name value SELECT min(name) AS min, max(name) AS max FROM items WHERE course='main';
4. What's the total revenue for all main courses? SELECT sum(revenue) AS total_revenue FROM items WHERE course='main';

INNER JOINS w/ Seasons Tables

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

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

• Having two columns with the same name is confusing. Can you customize each heading using AS?

  SELECT i.name AS item_name, s.name AS season_name FROM items i
  INNER JOIN seasons s ON i.season_id=s.id;
  

• Write a query that pulls all the category names for arugula salad.

  SELECT i.name AS item_name, c.name AS category_name FROM items i
  INNER JOIN item_categories ic ON i.id=ic.item_id
  INNER JOIN categories c ON c.id=ic.category_id
  WHERE i.name='arugula salad';
  

What do you think a RIGHT OUTER JOIN will do? It will only return the items that have a season_id

• Write a query to test your guess.

  SELECT i.name items, s.name seasons
  FROM items i RIGHT OUTER JOIN seasons s
  ON i.season_id=s.id;
  

• Insert data into the right table that will not get returned on an INNER JOIN.

  INSERT INTO seasons(name)
  VALUES ('space');
  

SELECT i.name items, s.name seasons       
FROM items i RIGHT OUTER JOIN seasons s
ON i.season_id=s.id;

        items         | seasons
----------------------+---------
 hot dog              | summer
 veggie lasagna       | summer
 striped bass         | summer
 burger               | summer
 arugula salad        | autumn
 lobster mac n cheese | winter
 grilled cheese       | spring
                      | space

1. Without looking at the previous solution, write a WHERE clause that returns the items that have a revenue less than the average revenue.

  SELECT * FROM items
  WHERE revenue < (SELECT avg(revenue) FROM items);

Additional Challenges

• Write a query that returns the sum of all items that have a category of dinner. This seems like an odd query, but I just did a sum of the revenue.

  SELECT sum(revenue) FROM items i
  JOIN item_categories ic ON i.id=ic.item_id
  JOIN categories c ON ic.category_id=c.id
  WHERE c.name='dinner';
  

• Write a query that returns the sum of all items for each category. The end result should look like this:

name       | sum
-----------+------
dinner     | 2600
vegetarian | 2900
lunch      | 3900
side       | 2300
(4 rows)

SELECT c.name category, sum(i.revenue) sum FROM categories c
JOIN item_categories ic ON ic.category_id=c.id
JOIN items i ON i.id=ic.item_id
GROUP BY c.name;