DATA 210 Week 3 Class 3 Resources

Let's create a database to start with:

CREATE DATABASE WK3CLASS3;

Now, let's create 2 tables (orders & customers) and input data for the table:

CREATE TABLE orders (
  order_id INT NOT NULL AUTO_INCREMENT,
  customer_id INT NOT NULL,
  order_date DATE NOT NULL,
  order_total DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (order_id)
);

INSERT INTO orders (customer_id, order_date, order_total)
VALUES (1, '2022-12-01', 100.50),
       (1, '2022-12-02', 75.25),
       (2, '2022-12-01', 200.00),
       (2, '2022-12-03', 150.75),
       (3, '2022-12-02', 50.00),
       (3, '2022-12-03', 125.50);

CREATE TABLE customers (
  customer_id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  email VARCHAR(100) NOT NULL,
  PRIMARY KEY (customer_id)
);

INSERT INTO customers (first_name, last_name, email)
VALUES ('John', 'Doe', '[email protected]'),
       ('Jane', 'Smith', '[email protected]'),
       ('Bob', 'Johnson', '[email protected]');

Questions to practice

-- 1. SUM: What is the total amount of all orders?

-- 2. COUNT: How many customers are there in the database?

-- 3. AVG: What is the average total amount of all orders?

-- 4. MIN: What is the minimum total amount of all orders?

-- 5. MAX: What is the maximum total amount of all orders?

-- 6. GROUP BY: What is the total amount of orders for each customer?

-- 7. SELECT subquery: What is the first name and last name of the customer with the highest total amount of orders?

-- 8. WHERE subquery: What is the total amount of orders made by customer with last_name in Smith?

-- 9. FROM subquery: What is the total amount of orders made by customers who live in the same city as customer_id 2? (ALTER table to include city field and add values to it with UPDATE)

Do perform the following action before writing SQL for the question::

ALTER TABLE customers ADD COLUMN city VARCHAR(255);

UPDATE customers SET city = 'New York' WHERE customer_id = 1;
UPDATE customers SET city = 'Kathmandu' WHERE customer_id = 2;
UPDATE customers SET city = 'Kathmandu' WHERE customer_id = 3;

-- 10. UNION: What are the customer names of customers who have placed an order, as well as customers who have not placed an order?

Do perform the following action before writing the query:

INSERT INTO customers (first_name, last_name, email)
VALUES ('Happy', 'Says', '[email protected]');

-- 11. UNION ALL: What are the customer names of customers who have placed an order, as well as customers who have not placed an order, and what is the corresponding city for each customer?

-- 12. UPDATE: Change the total amount of order_id 3 to 100.00.

-- 13. INSERT: Add a new order for customer_id 101 with order_total 150.00.

-- 14. DELETE: Delete all orders with customer_id more than 100