TyroCity

DATA 210 Week 3 Class 3 Answers

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

SELECT SUM(order_total) FROM orders;
Enter fullscreen mode Exit fullscreen mode

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

SELECT COUNT(*) FROM customers;
Enter fullscreen mode Exit fullscreen mode

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

SELECT AVG(order_total) FROM orders;
Enter fullscreen mode Exit fullscreen mode

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

SELECT MIN(order_total) FROM orders;
Enter fullscreen mode Exit fullscreen mode

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

SELECT MAX(order_total) FROM orders;
Enter fullscreen mode Exit fullscreen mode

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

SELECT customer_id, SUM(order_total) FROM orders GROUP BY customer_id;
Enter fullscreen mode Exit fullscreen mode

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

SELECT first_name, last_name FROM customers WHERE customer_id = (SELECT customer_id FROM orders GROUP BY customer_id ORDER BY SUM(order_total) DESC LIMIT 1);
Enter fullscreen mode Exit fullscreen mode

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

SELECT SUM(order_total) FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE last_name = 'Smith');
Enter fullscreen mode Exit fullscreen mode

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

===========

EXTRA STEPS - Do first

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;
Enter fullscreen mode Exit fullscreen mode

Query:

SELECT SUM(order_total) FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE city = (SELECT city FROM customers WHERE customer_id = 2));
Enter fullscreen mode Exit fullscreen mode

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

SELECT first_name, last_name FROM customers WHERE customer_id IN (SELECT customer_id FROM orders) UNION SELECT first_name, last_name FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);
Enter fullscreen mode Exit fullscreen mode

-- 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?

SELECT first_name, last_name, city FROM customers WHERE customer_id IN (SELECT customer_id FROM orders) UNION ALL SELECT first_name, last_name, city FROM customers WHERE customer_id NOT IN (SELECT customer_id FROM orders);
Enter fullscreen mode Exit fullscreen mode

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

UPDATE orders SET order_total = 100.00 WHERE order_id = 3;
Enter fullscreen mode Exit fullscreen mode

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

INSERT INTO orders (customer_id, order_total) VALUES (101, 150.00);
Enter fullscreen mode Exit fullscreen mode

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

DELETE FROM orders WHERE customer_id > 100;
Enter fullscreen mode Exit fullscreen mode

Top comments (0)