MySQL Queries Assignment Answers
Below are the MySQL queries for each question based on the provided database schema and
data.
1. Show all data from the customers table.
• SELECT * FROM customers;
2. Display first_name, last_name, and city of all customers ordered
alphabetically by last_name.
• SELECT first_name, last_name, city FROM customers ORDER BY last_name;
3. Show all unique countries represented in the customers table.
• SELECT DISTINCT country FROM customers;
4. List all products with a price greater than 50.
• SELECT * FROM products WHERE price > 50;
5. Display all Electronics products priced under 100 OR with a stock greater than
200.
• SELECT * FROM products WHERE (category = 'Electronics' AND price < 100) OR stock >
200;
6. Show all products that are NOT in the Stationery category.
• SELECT * FROM products WHERE category <> 'Stationery';
7. Find the lowest and highest product price.
• SELECT MIN(price) AS lowest_price, MAX(price) AS highest_price FROM products;
8. Count how many orders have the status 'PAID'.
• SELECT COUNT(*) AS paid_orders FROM orders WHERE status = 'PAID';
9. Calculate the total revenue of all orders combined.
• SELECT SUM(total_amount) AS total_revenue FROM orders;
10. Find the average product price in the Electronics category.
• SELECT AVG(price) AS avg_electronics_price FROM products WHERE category =
'Electronics';
11. List all customers whose last name starts with ‘M’.
• SELECT * FROM customers WHERE last_name LIKE 'M%';
12. Show all products whose name contains the word 'Desk' (case-insensitive).
• SELECT * FROM products WHERE LOWER(name) LIKE '%desk%';
13. Show all orders whose status is either 'PAID' or 'SHIPPED'.
• SELECT * FROM orders WHERE status IN ('PAID', 'SHIPPED');
14. Show all products priced between 20 and 100 dollars.
• SELECT * FROM products WHERE price BETWEEN 20 AND 100;
15. Insert a new product called 'Laptop Stand' in category 'Electronics' with
price = 29.99, stock = 50, and active = TRUE.
• INSERT INTO products (name, category, price, stock, active) VALUES ('Laptop Stand',
'Electronics', 29.99, 50, TRUE);
16. Update all Stationery products to increase their price by 10 %.
• UPDATE products SET price = price * 1.10 WHERE category = 'Stationery';
17. Show all customers where city is NULL.
• SELECT * FROM customers WHERE city IS NULL;
18. Display all products and any matching order_id (even if not ordered).
• SELECT p.*, oi.order_id FROM products p LEFT JOIN order_items oi ON p.product_id =
oi.product_id;
Instructions
1. Go to [Link] and select MySQL 9.
2. Copy the full schema and insert statements from your assignment.
3. Paste and build the schema.
4. Copy each query above and run it one at a time to verify your answers.
5. Save screenshots of results if required for submission.