-- 1. SUM: What is the total amount of all orders?
SELECT SUM(order_total) FROM orders;
-- 2. COUNT: How many customers are there in the database?
SELECT COUNT(*) FROM customers;
-- 3. AVG: What is the average total amount of all orders?
SELECT AVG(order_total) FROM orders;
-- 4. MIN: What is the minimum total amount of all orders?
SELECT MIN(order_total) FROM orders;
-- 5. MAX: What is the maximum total amount of all orders?
SELECT MAX(order_total) FROM orders;
-- 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;
-- 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);
-- 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');
-- 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;
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));
-- 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);
-- 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);
-- 12. UPDATE: Change the total amount of order_id 3 to 100.00.
UPDATE orders SET order_total = 100.00 WHERE order_id = 3;
-- 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);
-- 14. DELETE: Delete all orders with customer_id more than 100
DELETE FROM orders WHERE customer_id > 100;
Top comments (0)