assignments_section3.sql

-- Connect to database


-- ASSIGNMENT 1: Subqueries in the SELECT clause

-- View the products table


-- View the average unit price


-- Return the product id, product name, unit price, average unit price,
-- and the difference between each unit price and the average unit price


-- Order the results from most to least expensive


-- ASSIGNMENT 2: Subqueries in the FROM clause

-- Return the factories, product names from the factory
-- and number of products produced by each factory


-- All factories and products


-- All factories and their total number of products


-- Final query with subqueries


-- ASSIGNMENT 3: Subqueries in the WHERE clause

-- View all products from Wicked Choccy's


-- Return products where the unit price is less than
-- the unit price of all products from Wicked Choccy's


-- ASSIGNMENT 4: CTEs

-- View the orders and products tables


-- Calculate the amount spent on each product, within each order


-- Return all orders over $200


-- Return the number of orders over $200


-- ASSIGNMENT 5: Multiple CTEs

-- Copy over Assignment 2 (Subqueries in the FROM clause) solution


-- Rewrite the Assignment 2 subquery solution using CTEs instead