Assignment Questions (Based on Above
Tables)
Here are student-friendly yet real-world e-commerce SQL problems:
Basic Queries
1. Insert at least 5 customers, 8 products, and 3 orders into the database.
2. Display all products available in the store.
3. Show all customers who registered in the last 7 days.
4. Find all orders with status = 'Pending'.
SELECT + WHERE
5. Get all products priced greater than 500.
6. Display all customers whose name starts with 'A'.
7. Find all orders made by a particular customer (use customer email).
ORDER BY, LIMIT
8. List the top 5 most expensive products.
9. Show the latest 3 orders placed.
Aggregate Functions
[Link] the minimum and maximum price of products.
[Link] how many customers are registered.
[Link] the total sales amount from the payments table.
[Link] the average order amount.
LIKE, IN, BETWEEN
[Link] products where category = "Electronics" OR "Clothing".
[Link] products priced between 1000 and 5000.
[Link] for products where name contains the word 'Phone'.
Joins
[Link] all orders with customer name and email (INNER JOIN customers & orders).
[Link] all order items with product name and price (JOIN order_items & products).
[Link] all orders with payment status and method (JOIN orders & payments).
[Link] customers who have placed at least one order (INNER JOIN customers & orders).
[Link] products that have never been ordered (LEFT JOIN).
[Link] all customers and their orders, even if they have no order yet (LEFT JOIN).
[Link] all orders with multiple products (JOIN with GROUP BY + COUNT).
Advanced Queries
[Link] the top 3 customers who spent the most money (JOIN + SUM + ORDER BY).
[Link] categories that generated the highest revenue.
[Link] customers who ordered at least 2 different product categories.
[Link] orders where total amount is greater than the average order amount.
[Link] CASE to label products as 'Cheap' (<1000), 'Moderate' (1000–5000),
'Expensive' (>5000).
[Link] revenue grouped by payment method (GROUP BY + SUM).
[Link] orders that don’t have any successful payment (NOT EXISTS).
