SQL Programming Test

Welcome to this amazing SQL programming test that will help anyone deeply interested in working with databases. This test will help you assess your strengths and find gaps in your SQL skills. Ultimately, you’ll know which part of the SQL you should focus on.

Test Your SQL Programming Skills

Before you delve into the questions and answer section, make sure you have a SQL set up or use some online SQL compiler. Here is the test data you should create as it is used for writing the SQL queries.

Sample Test Data for SQL Queries

Here is sample test data for the SQL programming test.

Context: Galaxy-Themed E-Commerce Store Database

Tables: cust_tbl, ord_tbl, and prod_tbl

You can create the sample data for the cust_tbl, ord_tbls, and prod_tbl tables using SQL statements like this:

• Create the cust_tbl Table:

CREATE TABLE cust_tbl (
    cust_id INT PRIMARY KEY,
    cust_name VARCHAR(255),
    email VARCHAR(255),
    join_date DATE
);

INSERT INTO cust_tbl (cust_id, cust_name, email, join_date)
VALUES
    (1, 'Sam', 'sam@email.com', '2022-01-15'),
    (2, 'Kim', 'kim@email.com', '2022-02-20'),
    (3, 'Kane', 'kane@email.com', '2022-03-25'),
    (4, 'Dave', 'dave@email.com', '2022-04-10');

• Create the ord_tbls Table:

CREATE TABLE ord_tbls (
    ord_id INT PRIMARY KEY,
    cust_id INT,
    ord_date DATE,
    ttl_amt DECIMAL(10, 2),
    FOREIGN KEY (cust_id) REFERENCES cust_tbl(cust_id)
);

INSERT INTO ord_tbls (ord_id, cust_id, ord_date, ttl_amt)
VALUES
    (101, 1, '2022-04-01', 100.00),
    (102, 2, '2022-04-05', 75.50),
    (103, 1, '2022-04-10', 45.25),
    (104, 3, '2022-05-02', 150.75),
    (105, 2, '2022-05-05', 65.00),
    (106, 4, '2022-05-10', 200.50);

• Create the prod_tbl Table:

CREATE TABLE prod_tbl (
    prod_id INT PRIMARY KEY,
    prod_name VARCHAR(255),
    unit_price DECIMAL(10, 2)
);

INSERT INTO prod_tbl (prod_id, prod_name, unit_price)
VALUES
    (1, 'Laptop', 800.00),
    (2, 'Phone', 400.00),
    (3, 'Tablet', 300.00),
    (4, 'Headphones', 50.00),
    (5, 'Mouse', 15.00),
    (6, 'Keyboard', 30.00);

These SQL statements will create the tables and insert the sample data. Adjust the data as needed for your tests.

SQL Programming Test

We hope you created the above sample data. You are all set to start facing the questions given below. However, if you see more test data is needed, add it yourself.

Also Read: SQL Complex Queries You Must Try

Beginner Level

Firstly, let’s face the beginner-level SQL programming test questions. Make sure you have created the demo data.

1. Retrieve the names of every customer who joined before ‘2022-03-25’.
   Ans.

   SELECT cust_name
   FROM cust_tbl
   WHERE join_date < '2022-03-25';

2. List out every order made by “Kim.”
   Ans.

   SELECT ord_id, ord_date, ttl_amt
   FROM ord_tbls
   WHERE cust_id = (SELECT cust_id FROM cust_tbl WHERE cust_name = 'Kim');

3. Calculate the number of products that are present in the prod_tbl.
   Ans.

   SELECT COUNT(*) AS Total_Products_Present
   FROM prod_tbl;

4. Find out how many orders were booked as per the data in the ord_tbls.
   Ans.

   SELECT COUNT(*) AS Total_Orders_Booked
   FROM ord_tbls;

5. What is the average order amount for customers who joined before ‘2022-03-25’?
   Ans.

   SELECT AVG(ttl_amt) AS Avg_Order_Amt
   FROM ord_tbls
   WHERE cust_id IN (SELECT cust_id FROM cust_tbl WHERE join_date < '2022-03-25');

6. Which product is the most popular among those customers who joined in April 2022?
   Ans.

   SELECT prod_name
   FROM prod_tbl
   WHERE prod_id = (
       SELECT prod_id
       FROM ord_tbls
       WHERE cust_id IN (SELECT cust_id FROM cust_tbl WHERE join_date >= '2022-04-01' AND join_date < '2022-05-01')
       GROUP BY prod_id
       ord_tbl BY COUNT(*) DESC
       LIMIT 1
   );

7. What is the total revenue generated from orders placed in May 2022?
   Ans.

   SELECT SUM(ttl_amt) AS Total_Revenue
   FROM ord_tbls
   WHERE ord_date >= '2022-05-01' AND ord_date < '2022-06-01';

8. What are the total orders placed by customers who have spent more than $500 on the store?
   Ans.

   SELECT COUNT(*) AS Order_Count
   FROM ord_tbls
   WHERE cust_id IN (
       SELECT cust_id
       FROM ord_tbls
       GROUP BY cust_id
       HAVING SUM(ttl_amt) > 500
   );

Check This: 25 SQL Interview Questions and Answers for 5+ Years

Intermediate Level

Secondly, let’s evaluate our skills with intermediate-level SQL programming test questions.

9. Find the customers who have booked more than two orders.
   Ans.

   SELECT cust_name
   FROM cust_tbl
   WHERE cust_id IN (
       SELECT cust_id
       FROM ord_tbls
       GROUP BY cust_id
       HAVING COUNT(*) > 2
   );

10. Calculate the total amount spent by each customer in April 2022.
    Ans.

   SELECT cust_name, SUM(ttl_amt) AS total_spent
   FROM cust_tbl
   JOIN ord_tbls ON cust_tbl.cust_id = ord_tbls.cust_id
   WHERE ord_date >= '2022-04-01' AND ord_date < '2022-05-01'
   GROUP BY cust_name;

11. List every product with a unit price between $50.00 and $400.00.
    Ans.

   SELECT prod_name, unit_price
   FROM prod_tbl
   WHERE unit_price BETWEEN 50.00 AND 400.00;

12. Find the customers who placed orders in both April and May 2022.
    Ans.

   SELECT cust_name
   FROM cust_tbl
   WHERE cust_id IN (
       SELECT cust_id
       FROM ord_tbls
       WHERE ord_date >= '2022-04-01' AND ord_date < '2022-05-01'
       INTERSECT
       SELECT cust_id
       FROM ord_tbls
       WHERE ord_date >= '2022-05-01' AND ord_date < '2022-06-01'
   );

13. Find the top 5 customers who have spent the most on their orders in the past 6 months.
    Ans.

   SELECT cust_name, SUM(ttl_amt) AS total_spent
   FROM cust_tbl
   JOIN ord_tbls ON cust_tbl.cust_id = ord_tbls.cust_id
   WHERE ord_date >= DATEADD(month, -6, GETDATE())
   GROUP BY cust_name
   ord_tbl BY total_spent DESC
   LIMIT 5;

14. What is the average order amount for customers who have purchased a “Laptop”?
    Ans.

   SELECT AVG(ttl_amt) AS average_ord_tbl_amount
   FROM ord_tbls
   WHERE cust_id IN (
       SELECT cust_id
       FROM ord_tbls
       WHERE prod_id = (
           SELECT prod_id
           FROM prod_tbl
           WHERE prod_name = 'Laptop'
       )
   );

15. Which product has the highest profit margin?
    Ans.

   SELECT prod_name, (unit_price - cost_price) AS profit_margin
   FROM prod_tbl
   ord_tbl BY profit_margin DESC
   LIMIT 1;

16. What is the percentage of customers who have placed more than one order?
    Ans.

   SELECT (COUNT(DISTINCT cust_id) / (SELECT COUNT(DISTINCT cust_id) FROM ord_tbls)) * 100 AS percentage
   FROM ord_tbls
   WHERE cust_id IN (
       SELECT cust_id
       FROM ord_tbls
       GROUP BY cust_id
       HAVING COUNT(*) > 1
   );

Must Read: 25 SQL Performance Interview Questions and Answers

Advanced Level

Certainly, here are the Advanced Level questions for the E-Commerce Store Database:

17. Retrieve the customer name who spent the most on one single order.
    Ans.

   SELECT cust_name
   FROM cust_tbl
   WHERE cust_id = (
       SELECT cust_id
       FROM ord_tbls
       ord_tbl BY ttl_amt DESC
       LIMIT 1
   );

18. List the products not ordered by any customer.
    Ans.

   SELECT prod_name
   FROM prod_tbl
   WHERE prod_id NOT IN (SELECT DISTINCT prod_id FROM ord_tbls);

19. Calculate the average total amount spent by customers in May 2022.
    Ans.

   SELECT AVG(ttl_amt) AS average_ttl_amt
   FROM ord_tbls
   WHERE ord_date >= '2022-05-01' AND ord_date < '2022-06-01';

20. Find the customers who have not placed any orders in May 2022.
    Ans.

   SELECT cust_name
   FROM cust_tbl
   WHERE cust_id NOT IN (
       SELECT DISTINCT cust_id
       FROM ord_tbls
       WHERE ord_date >= '2022-05-01' AND ord_date < '2022-06-01'
   );

21. Get the names of all customers, along with the total amount they have spent, ordered by the total amount spent from highest to lowest.
    Ans.

   SELECT cust_name, SUM(ttl_amt) AS Total_Spend
   FROM cust_tbl
   JOIN ord_tbls ON cust_tbl.cust_id = ord_tbls.cust_id
   GROUP BY cust_name
   ord_tbl BY total_spent DESC;

22. Find the customers who have placed orders in April and May 2022 but have not purchased a “Laptop.”
    Ans.

   SELECT cust_name
   FROM cust_tbl
   WHERE cust_id IN (
       SELECT cust_id
       FROM ord_tbls
       WHERE ord_date >= '2022-04-01' and ord_date < '2022-05-01'
       INTERSECT
       SELECT cust_id
       FROM ord_tbls
       WHERE ord_date >= '2022-05-01' and ord_date < '2022-06-01'
       EXCEPT
       SELECT cust_id
       FROM ord_tbls
       WHERE prod_id = (
           SELECT prod_id
           FROM prod_tbl
           WHERE prod_name = 'Laptop'
       )
   );

23. Design an SQL trigger that updates the product inventory level whenever an order is placed.
    Ans.

   CREATE TRIGGER UpdateInventory
   ON ord_tbls
   AFTER INSERT
   AS
   BEGIN
       UPDATE prod_tbl
       SET inventory = inventory - (SELECT COUNT(*) FROM inserted WHERE inserted.prod_id = prod_tbl.prod_id)
       WHERE prod_id IN (SELECT prod_id FROM inserted);
   END;

These were the Advanced Level questions for your E-Commerce Store Database. If you have any further questions or need additional clarification, don’t hesitate to ask.

You can write efficient queries using the SQL If statement and improve their performance with the SQL Union clause. If this catches your attention, don’t refrain from checking them out.

Summary

The above SQL questions cover a wide range of skills, from beginner to advanced levels, making it a comprehensive test to check your SQL programming skills. The use of an E-Commerce store database was to offer you a real scenario. Whether you’re a beginner or experienced, these questions give you a perfect chance to show your SQL knowledge.

We need your support to run this blog, so share this post on your social media accounts like Facebook / Twitter. This is how you will encourage us to come up with more informative stuff.

Happy Learning SQL,
Team TechBeamers.