intermediate_sql_saylerb.md

Intermediate SQL Homework

Summary Questions

1. What is an INNER JOIN?

An inner join will combine the records that exist two tables

1. What is a LEFT OUTER JOIN?

A left outer join will produce all the records in the table on the left side 
of the operation, along with matching records in the table on the right side 
of the operation, if they exist.  If a left side record exists and a right one
doesn't, nulls will exist in the right hand column.

1. What is a RIGHT OUTER JOIN?

A right outer join will produce all the records in the table on the right side
of the operation, along with matching records in the table on the left side of 
the operation, if they exist.  If a right side record exists without a matching
one on the left hand side, nulls will exist in the left hand column.

Setup

1. What does SERIAL do?

Serial creates an id number for that entry. It is not a real SQL data type, but actually syntactic sugar for adding an integer column for unique identifier. It adds an autoincrementing four-byte integer. Currently using SERIAL is equivalent to integer NOT NULL DEFAULT nextval('tablename_colnume_seq').

Aggregate Functions

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

   SELECT sum(revenue) FROM items;

2. What's the average revenue for all items

   SELECT avg(revenue) FROM items;

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

   SELECT max(revenue) FROM items;

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

   SELECT min(revenue) FROM items;

5. What the count for items with a name?

   SELECT count(name) FROM items;

6. Typically you count records in a table by counting on the id column, like SELECT COUNT(id) FROM items;. However, it's not necessary for a table to have an id column. What else can you pass to count and still get 5 as your result?

SELECT count(*) FROM items;

Building on Aggregate Functions

1. Return all main courses. Hint: What ActiveRecord method would you use to get this?

   SELECT * FROM items WHERE course = 'main';

The ActiveRecord method would be:

Item.all

1. Return only the names of the main courses.

   SELECT name FROM items WHERE course = 'main';

2. Return the min and max value for the main courses.

   SELECT min(revenue), max(revenue) FROM items WHERE course = 'main';

3. What's the total revenue for all main courses?

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

Inner Joins

1. 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 seasons.id = items.season_id;

2. 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 seasons.id = items.season_id;

3. Write a query that pulls all the category names for arugula salad. Hint: Use multiple INNER JOINs and a WHERE clause.

    SELECT items.name, categories.name 
    FROM items
    INNER JOIN item_categories
    ON item_categories.item_id = items.id
    INNER JOIN categories
    ON categories.id = item_categories.category_id
    WHERE items.name = 'arugula salad';

4. Can you change the column headings?

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

Outer Joins

1. A LEFT OUTER JOIN will return all records from the left table (items) and return matching records from the right table (seasons). What do you think a RIGHT OUTER JOIN will do?

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

The RIGHT OUTER JOIN will return all the records from seasons
and only the matching records from items (i.e. all the items
that have a season).

Subqueries

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

2. 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

1. Write a query that returns the sum of all items that have a category of dinner.

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

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

   SELECT categories.name, sum(revenue)
   FROM items
   INNER JOIN item_categories
   ON item_categories.item_id = items.id
   INNER JOIN categories
   ON categories.id = item_categories.category_id
   GROUP BY categories.name

3. Take a look at your RailsEngine project. Take a look at you methods for handling business logic. Use the to_sql method to see what SQL ActiveRecord is generating. What things are things more clear? What things are still unclear?

• N/A