30 SQL Basic Exercises for Advanced with Solutions
Master advanced SQL skills with our comprehensive list of top 30 exercises. Dive into coding challenges that improve your understanding and proficiency in SQL, setting a solid foundation for professional-level challenges. Start your journey to SQL mastery today!
Learning Objectives:
By the end of these exercises, you will master advanced SQL techniques, including window functions, recursive queries, and performance optimization.
Exercise Instructions:
- Start with the first exercise and attempt to solve it before checking the hint or solution.
- Ensure you understand the logic behind each solution, as this will help you in more complex problems.
- Use these exercises to reinforce your learning and identify areas that may require further study.
1. Retrieve orders where the Total_Amount is greater than the average Total_Amount of all orders.
Code In Sql
-- Table Name : Orders
-- | Order_ID | Customer_Name | Product | Quantity | Order_Date | Total_Amount | CATEGORY |
-- |----------|---------------|---------------|----------|------------|--------------|--------------|
-- | 101 | Ananya | Laptop | 1 | 2023-06-15 | 55000.00 | Electronics |
-- | 102 | Bharat | Mobile Phone | 2 | 2023-06-18 | 60000.00 | Electronics |
-- | 103 | Chitra | Office Chair | 4 | 2023-07-01 | 28000.00 | Furniture |
-- | 104 | Dev | Coffee Maker | 1 | 2023-07-04 | 4000.00 | Furniture |
-- | 105 | Esha | Dining Table | 1 | 2023-07-10 | 15000.00 | Furniture |
-- | 106 | Ananya | Headphones | 1 | 2023-06-16 | 2000.00 | Electronics |
-- | 107 | Bharat | Charger | 1 | 2023-06-20 | 500.00 | Electronics |
-- | 108 | Harsh | Headphones | 1 | 2023-07-16 | 2000.00 | Electronics |
-- | 109 | Ishita | Headphones | 2 | 2023-07-17 | 4000.00 | Electronics |
-- | 110 | Jai | Headphones | 2 | 2023-07-18 | 4000.00 | Electronics |
-- | 111 | Kavita | Headphones | 6 | 2023-07-19 | 12000.00 | Electronics |
-- Your Query Here
Output
Click Run Button to view compiled output
2. Display the total revenue generated for each product.
Code In Sql
-- Table Name : Orders
-- | Order_ID | Customer_Name | Product | Quantity | Order_Date | Total_Amount | CATEGORY |
-- |----------|---------------|---------------|----------|------------|--------------|--------------|
-- | 101 | Ananya | Laptop | 1 | 2023-06-15 | 55000.00 | Electronics |
-- | 102 | Bharat | Mobile Phone | 2 | 2023-06-18 | 60000.00 | Electronics |
-- | 103 | Chitra | Office Chair | 4 | 2023-07-01 | 28000.00 | Furniture |
-- | 104 | Dev | Coffee Maker | 1 | 2023-07-04 | 4000.00 | Furniture |
-- | 105 | Esha | Dining Table | 1 | 2023-07-10 | 15000.00 | Furniture |
-- | 106 | Ananya | Headphones | 1 | 2023-06-16 | 2000.00 | Electronics |
-- | 107 | Bharat | Charger | 1 | 2023-06-20 | 500.00 | Electronics |
-- | 108 | Harsh | Headphones | 1 | 2023-07-16 | 2000.00 | Electronics |
-- | 109 | Ishita | Headphones | 2 | 2023-07-17 | 4000.00 | Electronics |
-- | 110 | Jai | Headphones | 2 | 2023-07-18 | 4000.00 | Electronics |
-- | 111 | Kavita | Headphones | 6 | 2023-07-19 | 12000.00 | Electronics |
-- Your Query Here
Output
Click Run Button to view compiled output
3. Find the top 2 highest Total_Amount orders for each customer.
Code In Sql
-- Table Name : Orders
-- | Order_ID | Customer_Name | Product | Quantity | Order_Date | Total_Amount | CATEGORY |
-- |----------|---------------|---------------|----------|------------|--------------|--------------|
-- | 101 | Ananya | Laptop | 1 | 2023-06-15 | 55000.00 | Electronics |
-- | 102 | Bharat | Mobile Phone | 2 | 2023-06-18 | 60000.00 | Electronics |
-- | 103 | Chitra | Office Chair | 4 | 2023-07-01 | 28000.00 | Furniture |
-- | 104 | Dev | Coffee Maker | 1 | 2023-07-04 | 4000.00 | Furniture |
-- | 105 | Esha | Dining Table | 1 | 2023-07-10 | 15000.00 | Furniture |
-- | 106 | Ananya | Headphones | 1 | 2023-06-16 | 2000.00 | Electronics |
-- | 107 | Bharat | Charger | 1 | 2023-06-20 | 500.00 | Electronics |
-- | 108 | Harsh | Headphones | 1 | 2023-07-16 | 2000.00 | Electronics |
-- | 109 | Ishita | Headphones | 2 | 2023-07-17 | 4000.00 | Electronics |
-- | 110 | Jai | Headphones | 2 | 2023-07-18 | 4000.00 | Electronics |
-- | 111 | Kavita | Headphones | 6 | 2023-07-19 | 12000.00 | Electronics |
-- Your Query Here
Output
Click Run Button to view compiled output
4. List orders placed in the last 30 days.
Code In Sql
-- Table Name : Orders
-- | Order_ID | Customer_Name | Product | Quantity | Order_Date | Total_Amount | CATEGORY |
-- |----------|---------------|---------------|----------|------------|--------------|--------------|
-- | 101 | Ananya | Laptop | 1 | 2023-06-15 | 55000.00 | Electronics |
-- | 102 | Bharat | Mobile Phone | 2 | 2023-06-18 | 60000.00 | Electronics |
-- | 103 | Chitra | Office Chair | 4 | 2023-07-01 | 28000.00 | Furniture |
-- | 104 | Dev | Coffee Maker | 1 | 2023-07-04 | 4000.00 | Furniture |
-- | 105 | Esha | Dining Table | 1 | 2023-07-10 | 15000.00 | Furniture |
-- | 106 | Ananya | Headphones | 1 | 2023-06-16 | 2000.00 | Electronics |
-- | 107 | Bharat | Charger | 1 | 2023-06-20 | 500.00 | Electronics |
-- | 108 | Harsh | Headphones | 1 | 2023-07-16 | 2000.00 | Electronics |
-- | 109 | Ishita | Headphones | 2 | 2023-07-17 | 4000.00 | Electronics |
-- | 110 | Jai | Headphones | 2 | 2023-07-18 | 4000.00 | Electronics |
-- | 111 | Kavita | Headphones | 6 | 2023-07-19 | 12000.00 | Electronics |
-- Your Query Here
Output
Click Run Button to view compiled output
5. Retrieve orders for customers who placed more than one order.
Code In Sql
-- Table Name : Orders
-- | Order_ID | Customer_Name | Product | Quantity | Order_Date | Total_Amount | CATEGORY |
-- |----------|---------------|---------------|----------|------------|--------------|--------------|
-- | 101 | Ananya | Laptop | 1 | 2023-06-15 | 55000.00 | Electronics |
-- | 102 | Bharat | Mobile Phone | 2 | 2023-06-18 | 60000.00 | Electronics |
-- | 103 | Chitra | Office Chair | 4 | 2023-07-01 | 28000.00 | Furniture |
-- | 104 | Dev | Coffee Maker | 1 | 2023-07-04 | 4000.00 | Furniture |
-- | 105 | Esha | Dining Table | 1 | 2023-07-10 | 15000.00 | Furniture |
-- | 106 | Ananya | Headphones | 1 | 2023-06-16 | 2000.00 | Electronics |
-- | 107 | Bharat | Charger | 1 | 2023-06-20 | 500.00 | Electronics |
-- | 108 | Harsh | Headphones | 1 | 2023-07-16 | 2000.00 | Electronics |
-- | 109 | Ishita | Headphones | 2 | 2023-07-17 | 4000.00 | Electronics |
-- | 110 | Jai | Headphones | 2 | 2023-07-18 | 4000.00 | Electronics |
-- | 111 | Kavita | Headphones | 6 | 2023-07-19 | 12000.00 | Electronics |
-- Your Query Here
Output
Click Run Button to view compiled output
6. Display orders where Quantity is greater than the average quantity ordered per product.
Code In Sql
-- Table Name : Orders
-- | Order_ID | Customer_Name | Product | Quantity | Order_Date | Total_Amount | CATEGORY |
-- |----------|---------------|---------------|----------|------------|--------------|--------------|
-- | 101 | Ananya | Laptop | 1 | 2023-06-15 | 55000.00 | Electronics |
-- | 102 | Bharat | Mobile Phone | 2 | 2023-06-18 | 60000.00 | Electronics |
-- | 103 | Chitra | Office Chair | 4 | 2023-07-01 | 28000.00 | Furniture |
-- | 104 | Dev | Coffee Maker | 1 | 2023-07-04 | 4000.00 | Furniture |
-- | 105 | Esha | Dining Table | 1 | 2023-07-10 | 15000.00 | Furniture |
-- | 106 | Ananya | Headphones | 1 | 2023-06-16 | 2000.00 | Electronics |
-- | 107 | Bharat | Charger | 1 | 2023-06-20 | 500.00 | Electronics |
-- | 108 | Harsh | Headphones | 1 | 2023-07-16 | 2000.00 | Electronics |
-- | 109 | Ishita | Headphones | 2 | 2023-07-17 | 4000.00 | Electronics |
-- | 110 | Jai | Headphones | 2 | 2023-07-18 | 4000.00 | Electronics |
-- | 111 | Kavita | Headphones | 6 | 2023-07-19 | 12000.00 | Electronics |
-- Your Query Here
Output
Click Run Button to view compiled output
7. Calculate the cumulative total amount for each order based on the order date.
Code In Sql
-- Table Name : Orders
-- | Order_ID | Customer_Name | Product | Quantity | Order_Date | Total_Amount | CATEGORY |
-- |----------|---------------|---------------|----------|------------|--------------|--------------|
-- | 101 | Ananya | Laptop | 1 | 2023-06-15 | 55000.00 | Electronics |
-- | 102 | Bharat | Mobile Phone | 2 | 2023-06-18 | 60000.00 | Electronics |
-- | 103 | Chitra | Office Chair | 4 | 2023-07-01 | 28000.00 | Furniture |
-- | 104 | Dev | Coffee Maker | 1 | 2023-07-04 | 4000.00 | Furniture |
-- | 105 | Esha | Dining Table | 1 | 2023-07-10 | 15000.00 | Furniture |
-- | 106 | Ananya | Headphones | 1 | 2023-06-16 | 2000.00 | Electronics |
-- | 107 | Bharat | Charger | 1 | 2023-06-20 | 500.00 | Electronics |
-- | 108 | Harsh | Headphones | 1 | 2023-07-16 | 2000.00 | Electronics |
-- | 109 | Ishita | Headphones | 2 | 2023-07-17 | 4000.00 | Electronics |
-- | 110 | Jai | Headphones | 2 | 2023-07-18 | 4000.00 | Electronics |
-- | 111 | Kavita | Headphones | 6 | 2023-07-19 | 12000.00 | Electronics |
-- Your Query Here
Output
Click Run Button to view compiled output
8. Find the maximum Total_Amount within each month.
Code In Sql
-- Table Name : Orders
-- | Order_ID | Customer_Name | Product | Quantity | Order_Date | Total_Amount | CATEGORY |
-- |----------|---------------|---------------|----------|------------|--------------|--------------|
-- | 101 | Ananya | Laptop | 1 | 2023-06-15 | 55000.00 | Electronics |
-- | 102 | Bharat | Mobile Phone | 2 | 2023-06-18 | 60000.00 | Electronics |
-- | 103 | Chitra | Office Chair | 4 | 2023-07-01 | 28000.00 | Furniture |
-- | 104 | Dev | Coffee Maker | 1 | 2023-07-04 | 4000.00 | Furniture |
-- | 105 | Esha | Dining Table | 1 | 2023-07-10 | 15000.00 | Furniture |
-- | 106 | Ananya | Headphones | 1 | 2023-06-16 | 2000.00 | Electronics |
-- | 107 | Bharat | Charger | 1 | 2023-06-20 | 500.00 | Electronics |
-- | 108 | Harsh | Headphones | 1 | 2023-07-16 | 2000.00 | Electronics |
-- | 109 | Ishita | Headphones | 2 | 2023-07-17 | 4000.00 | Electronics |
-- | 110 | Jai | Headphones | 2 | 2023-07-18 | 4000.00 | Electronics |
-- | 111 | Kavita | Headphones | 6 | 2023-07-19 | 12000.00 | Electronics |
-- Your Query Here
Output
Click Run Button to view compiled output
9. List customers who have ordered products totaling more than 40000 in a single order.
Code In Sql
-- Table Name : Orders
-- | Order_ID | Customer_Name | Product | Quantity | Order_Date | Total_Amount | CATEGORY |
-- |----------|---------------|---------------|----------|------------|--------------|--------------|
-- | 101 | Ananya | Laptop | 1 | 2023-06-15 | 55000.00 | Electronics |
-- | 102 | Bharat | Mobile Phone | 2 | 2023-06-18 | 60000.00 | Electronics |
-- | 103 | Chitra | Office Chair | 4 | 2023-07-01 | 28000.00 | Furniture |
-- | 104 | Dev | Coffee Maker | 1 | 2023-07-04 | 4000.00 | Furniture |
-- | 105 | Esha | Dining Table | 1 | 2023-07-10 | 15000.00 | Furniture |
-- | 106 | Ananya | Headphones | 1 | 2023-06-16 | 2000.00 | Electronics |
-- | 107 | Bharat | Charger | 1 | 2023-06-20 | 500.00 | Electronics |
-- | 108 | Harsh | Headphones | 1 | 2023-07-16 | 2000.00 | Electronics |
-- | 109 | Ishita | Headphones | 2 | 2023-07-17 | 4000.00 | Electronics |
-- | 110 | Jai | Headphones | 2 | 2023-07-18 | 4000.00 | Electronics |
-- | 111 | Kavita | Headphones | 6 | 2023-07-19 | 12000.00 | Electronics |
-- Your Query Here
Output
Click Run Button to view compiled output
10. Show orders along with the rank of Total_Amount for each customer.
Code In Sql
-- Table Name : Orders
-- | Order_ID | Customer_Name | Product | Quantity | Order_Date | Total_Amount | CATEGORY |
-- |----------|---------------|---------------|----------|------------|--------------|--------------|
-- | 101 | Ananya | Laptop | 1 | 2023-06-15 | 55000.00 | Electronics |
-- | 102 | Bharat | Mobile Phone | 2 | 2023-06-18 | 60000.00 | Electronics |
-- | 103 | Chitra | Office Chair | 4 | 2023-07-01 | 28000.00 | Furniture |
-- | 104 | Dev | Coffee Maker | 1 | 2023-07-04 | 4000.00 | Furniture |
-- | 105 | Esha | Dining Table | 1 | 2023-07-10 | 15000.00 | Furniture |
-- | 106 | Ananya | Headphones | 1 | 2023-06-16 | 2000.00 | Electronics |
-- | 107 | Bharat | Charger | 1 | 2023-06-20 | 500.00 | Electronics |
-- | 108 | Harsh | Headphones | 1 | 2023-07-16 | 2000.00 | Electronics |
-- | 109 | Ishita | Headphones | 2 | 2023-07-17 | 4000.00 | Electronics |
-- | 110 | Jai | Headphones | 2 | 2023-07-18 | 4000.00 | Electronics |
-- | 111 | Kavita | Headphones | 6 | 2023-07-19 | 12000.00 | Electronics |
-- Your Query Here
Output
Click Run Button to view compiled output
