Intermediate SQL 1 Answers

inter_1_answers.markdown

Intermediate SQL 1 Answers

Given This Table:

INSERT INTO items (name, revenue, course)
VALUES ('lobster mac n cheese', 1200, 'side'),
       ('veggie lasagna', 1000, 'main'),
       ('striped bass', 500, 'main'),
       ('arugula salad', 1100, 'salad');

Write queries for the following:

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

SELECT sum(revenue) FROM items;

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

SELECT avg(revenue) FROM items;

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

SELECT min(revenue) FROM items;

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

SELECT max(revenue) FROM items;

1. What the count for items with a name?

SELECT count(name) FROM items;

Let's create an item that has all NULL values: INSERT into items (name, revenue, course) VALUES (NULL, NULL, NULL);

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;

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'

1. Return only the names of the main courses.

SELECT items.name FROM items WHERE course = 'main;

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

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

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

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

Given this Database:

CREATE TABLE seasons(id SERIAL, name TEXT);
CREATE TABLE items(id SERIAL, name TEXT, revenue INT, season_id INT);
CREATE TABLE categories(id SERIAL, name TEXT);
CREATE TABLE item_categories(item_id INT, category_id INT);

and this query

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

• 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, categories.name
FROM items
INNER JOIN item_categories
ON item_categories.item_id = items.id
INNER JOIN categories
ON item_categories.category_id = categories.id
WHERE items.name = 'arugula salad';

• 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 item_categories.category_id = categories.id
WHERE items.name = 'arugula salad';

Subqueries

1. Calculate the average revenue.
2. 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);

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

• 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 item_categories.category_id = categories.id
WHERE categories.name = 'dinner';

• Write a query that returns the sum of all items for each category.

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