Data Analyst Interview Preparation

Guide: SQL Module

SQL FOR
DATA
 ANALYSTS

INTERVIEW PREPARATION

30 Theory Questions & 50 Practical Problems

Tailored for Data Analytics Freshers

Created by

Nikhil Mishra
SQL for Data Analysts: Interview
Questions

Introduction

This guide provides a comprehensive set of interview questions for aspiring Data
Analysts, focusing specifically on SQL. It covers fundamental concepts, essential
commands, and advanced techniques like joins, subqueries, and window functions.
The questions are divided into theoretical and practical sections, each with detailed
answers and contextual information to facilitate effective revision and practice.

Module: SQL for Data Analysis

Theory Questions (30 Questions)

Category 1: SQL Fundamentals (8 Questions)

Q1: What is SQL and why is it important for a Data Analyst?

A1: SQL (Structured Query Language) is a domain-specific language used for

managing and manipulating relational databases. It’s crucial for Data Analysts because
it allows them to retrieve, filter, aggregate, and transform data stored in databases,
which is the primary source of information for analysis and reporting.

Q2: Explain the difference between DELETE , TRUNCATE , and DROP

statements in SQL.

A2:

DELETE : Removes rows from a table based on a WHERE clause. It’s a DML (Data
Manipulation Language) command, logs each deleted row, and can be rolled
 back. It retains the table structure.

TRUNCATE : Removes all rows from a table. It’s a DDL (Data Definition Language)
command, is faster than DELETE (as it deallocates data pages), and cannot be
rolled back. It retains the table structure.

DROP : Removes an entire table (structure and data) from the database. It’s a DDL
command and cannot be rolled back.

Q3: What is the purpose of the WHERE clause and the HAVING clause?
How do they differ?

A3:

WHERE clause: Used to filter individual rows before any grouping or aggregation
occurs. It operates on raw data.

HAVING clause: Used to filter groups of rows after grouping and aggregation
have been performed. It operates on aggregated data.

Q4: Explain DISTINCT and COUNT(DISTINCT column_name) .

A4:

DISTINCT : Used in the SELECT statement to return only unique values for the
specified columns. For example, SELECT DISTINCT city FROM customers; .

COUNT(DISTINCT column_name) : Counts the number of unique non-NULL values

in a specified column. For example, SELECT COUNT(DISTINCT product_id) FROM
sales; .

Q5: What is the order of execution of a SQL query?

A5: The logical order of execution (though not necessarily the physical order) is:

1. FROM and JOIN s: Determine the data source.

2. WHERE : Filter rows.

3. GROUP BY : Group rows into summary rows.

4. HAVING : Filter groups.

 5. SELECT : Select columns/expressions.

6. DISTINCT : Remove duplicate rows.

7. ORDER BY : Sort the result set.

8. LIMIT / TOP : Restrict the number of rows returned.

Q6: What are UNION and UNION ALL ? What is the key difference?

A6: Both UNION and UNION ALL are used to combine the result sets of two or more
SELECT statements.

UNION : Combines result sets and removes duplicate rows from the final output.
It also sorts the result by default.

UNION ALL : Combines result sets and includes all duplicate rows. It is generally
faster than UNION because it doesn’t perform the overhead of checking for and
removing duplicates.

Q7: Explain the concept of Aliases in SQL.

A7: Aliases are temporary names given to tables or columns in a SQL query. They are
used to make column names more readable, to shorten table names (especially in
joins), or to handle cases where two columns from different tables have the same
name. Aliases are defined using the AS keyword (though AS is often optional).

Q8: What is the difference between NULL and an empty string ( '' )?

A8:

NULL : Represents the absence of a value, meaning unknown or not applicable. It

is not equal to zero, an empty string, or any other value.

Empty string ( '' ): Represents a string with zero characters. It is a known value,
just an empty one.
Category 2: Data Types & Constraints (6 Questions)

Q9: What are SQL constraints? Name some common types.

A9: SQL constraints are rules enforced on data columns in a table to limit the type of
data that can go into a table. This ensures the accuracy and reliability of the data.
Common types include:

NOT NULL : Ensures a column cannot have a NULL value.

UNIQUE : Ensures all values in a column are different.

PRIMARY KEY : A combination of NOT NULL and UNIQUE , uniquely identifies each
row.

FOREIGN KEY : Links two tables together, ensuring referential integrity.

CHECK : Ensures all values in a column satisfy a specific condition.

DEFAULT : Sets a default value for a column when no value is specified.

Q10: Explain PRIMARY KEY and FOREIGN KEY with an example.

A10:

PRIMARY KEY : A column or a set of columns that uniquely identifies each record
in a table. It must contain unique values and cannot contain NULL values. A table
can have only one primary key.
Example: In a Customers table, customer_id would typically be the
primary key.

FOREIGN KEY : A column or a set of columns in one table that refers to the
PRIMARY KEY in another table. It establishes a link between two tables, enforcing
referential integrity (ensuring that relationships between tables remain
consistent).
Example: In an Orders table, customer_id would be a foreign key
referencing the customer_id in the Customers table.
Q11: What is the purpose of the UNIQUE constraint?

A11: The UNIQUE constraint ensures that all values in a column (or a group of
columns) are different. Unlike a PRIMARY KEY , a table can have multiple UNIQUE
constraints, and a UNIQUE column can accept NULL values (though only one NULL is
typically allowed, depending on the database system).

Q12: What are the common numeric data types in SQL?

A12: Common numeric data types include:

INT (or INTEGER ): For whole numbers.

DECIMAL(p, s) or NUMERIC(p, s) : For exact decimal numbers, where p is the

total number of digits and s is the number of digits after the decimal point.

FLOAT or REAL : For approximate floating-point numbers.

BIGINT , SMALLINT , TINYINT : For different ranges of whole numbers.

Q13: How do you store date and time information in SQL?

A13: Common data types for date and time include:

DATE : Stores only the date (e.g., ‘YYYY-MM-DD’).

TIME : Stores only the time (e.g., ‘HH:MI:SS’).

DATETIME or TIMESTAMP : Stores both date and time.

YEAR : Stores the year.

Q14: What is AUTO_INCREMENT (or IDENTITY ) and when is it used?

A14: AUTO_INCREMENT (MySQL) or IDENTITY (SQL Server) is a property that can be

assigned to a numeric column (typically an integer primary key). It automatically
generates a unique sequential number for each new record inserted into the table. It’s
commonly used for primary keys to ensure uniqueness and to simplify record insertion
without manually managing IDs.
Category 3: Joins (6 Questions)

Q15: Explain the different types of SQL JOINs.

A15:

INNER JOIN : Returns only the rows that have matching values in both tables.

LEFT JOIN (or LEFT OUTER JOIN ): Returns all rows from the left table, and the
matching rows from the right table. If there’s no match, NULL s are returned for
the right table’s columns.

RIGHT JOIN (or RIGHT OUTER JOIN ): Returns all rows from the right table, and
the matching rows from the left table. If there’s no match, NULL s are returned for
the left table’s columns.

FULL JOIN (or FULL OUTER JOIN ): Returns all rows when there is a match in
one of the tables. If there’s no match, NULL s are returned for the columns of the
table without a match.

Q16: When would you use a LEFT JOIN over an INNER JOIN ?

A16: You would use a LEFT JOIN when you want to retrieve all records from the left
table, regardless of whether there is a corresponding match in the right table. This is
useful when you want to see all entries from a primary table and any related
information from a secondary table, even if some entries in the primary table don’t
have matches.

Q17: What is a SELF-JOIN ? Provide a scenario where it would be

useful.

A17: A SELF-JOIN is a join in which a table is joined with itself. This is done by aliasing
the table to treat it as two separate tables in the FROM clause. It is useful for comparing
rows within the same table, such as finding employees who report to the same
manager, or finding products that are similar to each other. Scenario: Finding
employees who work in the same department from an Employees table that has
employee_id , employee_name , and department_id .
Q18: Explain the concept of CROSS JOIN .

A18: A CROSS JOIN produces a Cartesian product of the two tables involved. This
means it returns every possible combination of rows from the first table with rows
from the second table. If table A has m rows and table B has n rows, a CROSS JOIN
will return m * n rows. It is rarely used directly in data analysis but can be implicitly
formed if JOIN conditions are omitted.

Q19: What is the difference between INNER JOIN and FULL OUTER
JOIN ?

A19:

INNER JOIN : Returns only the rows where there is a match in both tables based
on the join condition. It discards rows that do not have a match in the other
table.

FULL OUTER JOIN : Returns all rows from both tables. If there is no match, NULL
values are returned for the columns of the table that does not have a match. It is
the union of LEFT JOIN and RIGHT JOIN .

Q20: How can you find records that exist in one table but not in
another?

A20: You can achieve this using a LEFT JOIN combined with a WHERE clause checking
for NULL values in the right table, or by using NOT EXISTS or EXCEPT (or MINUS in
some SQL dialects). Example (using LEFT JOIN): SELECT A.* FROM TableA A LEFT
JOIN TableB B ON [Link] = [Link] WHERE [Link] IS NULL;

Category 4: Subqueries and CTEs (4 Questions)

Q21: What is a subquery (or inner query)? When would you use it?

A21: A subquery is a query nested inside another SQL query. It can be used in SELECT ,
FROM , WHERE , and HAVING clauses. Subqueries are useful for performing operations
that require multiple steps, such as filtering data based on a result from another query,
or calculating an aggregate value that needs to be used in a WHERE clause.
Q22: Differentiate between correlated and non-correlated subqueries.

A22:

Non-correlated subquery: Executes independently of the outer query. It runs

once and its result is then used by the outer query. It does not reference any
columns from the outer query.

Correlated subquery: Executes once for each row processed by the outer query.
It depends on the outer query for its values and cannot be run independently. It
references one or more columns from the outer query.

Q23: What is a Common Table Expression (CTE) and what are its
advantages?

A23: A CTE (Common Table Expression) is a temporary, named result set that you can
reference within a single SELECT , INSERT , UPDATE , or DELETE statement. It is defined
using the WITH clause. Advantages:

Readability: Breaks down complex queries into logical, readable steps.

Reusability: Can be referenced multiple times within the same query.

Recursion: Supports recursive queries.

Modularity: Improves query organization and debugging.

Q24: Provide a scenario where a CTE would be more beneficial than a

subquery.

A24: A CTE is often more beneficial when a subquery needs to be reused multiple
times within the same query, or when the logic is complex and needs to be broken
down into smaller, more manageable steps. For example, calculating hierarchical data
(like an organizational chart) or performing multi-level aggregations where
intermediate results are needed.
Category 5: Window Functions (3 Questions)

Q25: What are SQL Window Functions? How do they differ from GROUP
BY ?

A25: Window functions perform calculations across a set of table rows that are related
to the current row. Unlike GROUP BY , window functions do not collapse rows; they
return a value for each row in the result set. They operate on a “window” of rows
defined by the OVER() clause. Difference from GROUP BY : GROUP BY aggregates rows
into a single output row, reducing the number of rows. Window functions perform
calculations on a group of rows but return individual results for each row, maintaining
the original number of rows.

Q26: Explain ROW_NUMBER() , RANK() , and DENSE_RANK() .

A26: These are ranking window functions:

ROW_NUMBER() : Assigns a unique sequential integer to each row within its

partition, starting from 1. If rows have the same value, they get different row
numbers.

RANK() : Assigns a rank to each row within its partition. If rows have the same
value, they receive the same rank, and the next rank is skipped (e.g., 1, 1, 3).

DENSE_RANK() : Assigns a rank to each row within its partition. If rows have the
same value, they receive the same rank, and no ranks are skipped (e.g., 1, 1, 2).

Q27: When would you use LEAD() and LAG() functions?

A27: LEAD() and LAG() are used to access data from a subsequent or preceding row
within the same result set without using a self-join.

LEAD(column, offset, default) : Accesses data from a row offset rows after
the current row.

LAG(column, offset, default) : Accesses data from a row offset rows before
the current row. They are useful for calculating differences between consecutive
rows, comparing current values with previous/next values (e.g., month-over-
month growth, tracking changes in stock prices).
Category 6: Advanced Concepts (3 Questions)

Q28: What is database normalization? Explain its importance.

A28: Database normalization is the process of organizing the columns and tables of a
relational database to minimize data redundancy and improve data integrity. It
involves breaking down a large table into smaller, related tables and defining
relationships between them. Importance:

Reduces Data Redundancy: Avoids storing the same data in multiple places.

Improves Data Integrity: Ensures data consistency and accuracy.

Enhances Data Modifiability: Makes it easier to update, insert, and delete data
without anomalies.

Simplifies Queries: Can lead to simpler queries in some cases.

Q29: How do indexes work in SQL and why are they important for
performance?

A29: An index is a special lookup table that the database search engine can use to
speed up data retrieval. It works much like an index in a book, allowing the database
to quickly locate data without scanning every row in a table. Indexes are created on
one or more columns of a table. Importance:

Faster Data Retrieval: Significantly speeds up SELECT queries, especially on

large tables.

Improved Sorting and Grouping: Can accelerate ORDER BY and GROUP BY

operations.

Unique Constraints: Enforce uniqueness on columns. Drawbacks: They

consume disk space and can slow down INSERT , UPDATE , and DELETE
operations because the index also needs to be updated.

Q30: Explain ACID properties in the context of database transactions.

A30: ACID is an acronym that stands for Atomicity, Consistency, Isolation, and
Durability. These are a set of properties that guarantee that database transactions are
processed reliably.
 Atomicity: A transaction is treated as a single, indivisible unit of work. Either all
of its operations are completed successfully, or none of them are.

Consistency: A transaction brings the database from one valid state to another. It
ensures that data remains valid according to defined rules and constraints.

Isolation: Concurrent transactions execute in such a way that they appear to be

executed sequentially. The intermediate state of one transaction is not visible to
other transactions.

Durability: Once a transaction has been committed, its changes are permanent
and will survive system failures (e.g., power outages, crashes).

Practical Questions (50 Questions)

Database Schema for Practical Questions

Throughout these practical questions, we will be using a simplified e-commerce

database schema. Please refer to these table descriptions for each question.

Table: Customers

customer_id (INT, PRIMARY KEY): Unique identifier for each customer.

first_name (VARCHAR): Customer’s first name.

last_name (VARCHAR): Customer’s last name.

email (VARCHAR, UNIQUE): Customer’s email address.

registration_date (DATE): Date when the customer registered.

city (VARCHAR): City where the customer resides.

country (VARCHAR): Country where the customer resides.

Table: Products

product_id (INT, PRIMARY KEY): Unique identifier for each product.

product_name (VARCHAR): Name of the product.

 category (VARCHAR): Category of the product (e.g., ‘Electronics’, ‘Apparel’,
‘Books’).

price (DECIMAL(10, 2)): Unit price of the product.

stock_quantity (INT): Current stock level of the product.

Table: Orders

order_id (INT, PRIMARY KEY): Unique identifier for each order.

customer_id (INT, FOREIGN KEY references Customers.customer_id ): ID of the

customer who placed the order.

order_date (DATE): Date when the order was placed.

total_amount (DECIMAL(10, 2)): Total amount of the order.

status (VARCHAR): Current status of the order (e.g., ‘Pending’, ‘Shipped’,

‘Delivered’, ‘Cancelled’).

Table: Order_Items

order_item_id (INT, PRIMARY KEY): Unique identifier for each order item.

order_id (INT, FOREIGN KEY references Orders.order_id ): ID of the order this

item belongs to.

product_id (INT, FOREIGN KEY references Products.product_id ): ID of the

product in this order item.

quantity (INT): Quantity of the product ordered.

unit_price (DECIMAL(10, 2)): Unit price of the product at the time of order.
Category 1: Basic Queries (P1-P10)

P1: Select all columns from the Customers table.

-- Question
-- Write a query to retrieve all information about customers.

-- Answer
SELECT *
FROM Customers;

P2: Select the product_name and price of all products.

-- Question
-- Retrieve the name and price for every product.

-- Answer
SELECT product_name, price
FROM Products;

P3: Find the customer_id and order_date for all orders.

-- Question
-- Get the customer ID and the date for each order placed.

-- Answer
SELECT customer_id, order_date
FROM Orders;
P4: List all unique product categories.

-- Question
-- Show a list of all distinct product categories available.

-- Answer
SELECT DISTINCT category
FROM Products;

P5: Get the first_name and last_name of customers who registered

after ‘2023-01-01’.

-- Question
-- Find the names of customers who joined the platform in 2023 or later.

-- Answer
SELECT first_name, last_name
FROM Customers
WHERE registration_date > '2023-01-01';

P6: Find all products with a price greater than 50.

-- Question
-- List products that cost more than 50 units.

-- Answer
SELECT product_name, price
FROM Products
WHERE price > 50;
P7: List orders that have a total_amount between 100 and 500
(inclusive).

-- Question
-- Retrieve orders where the total amount is within the range of 100 to 500.

-- Answer
SELECT order_id, total_amount
FROM Orders
WHERE total_amount BETWEEN 100 AND 500;

P8: Find all customers from ‘USA’ or ‘Canada’.

-- Question
-- Get the names of customers residing in either the USA or Canada.

-- Answer
SELECT first_name, last_name, country
FROM Customers
WHERE country IN ('USA', 'Canada');

P9: List products whose names start with ’S’.

-- Question
-- Find all products whose names begin with the letter 'S'.

-- Answer
SELECT product_name
FROM Products
WHERE product_name LIKE 'S%';
P10: Order customers by registration_date in descending order.

-- Question
-- Display customer information, with the most recently registered customers
appearing first.

-- Answer
SELECT *
FROM Customers
ORDER BY registration_date DESC;

Category 2: Aggregation & Grouping (P11-P20)

P11: Calculate the total number of orders.

-- Question
-- Find out how many orders have been placed in total.

-- Answer
SELECT COUNT(order_id)
FROM Orders;

P12: Find the average total_amount of all orders.

-- Question
-- Determine the average value of an order.

-- Answer
SELECT AVG(total_amount)
FROM Orders;
P13: Get the maximum price among all products.

-- Question
-- What is the highest price of any product?

-- Answer
SELECT MAX(price)
FROM Products;

P14: Calculate the sum of quantity for a specific product_id (e.g.,

product_id = 1 ).

-- Question
-- How many units of product with ID 1 have been sold in total?

-- Answer
SELECT SUM(quantity)
FROM Order_Items
WHERE product_id = 1;

P15: Count the number of orders for each customer_id .

-- Question
-- For each customer, show how many orders they have placed.

-- Answer
SELECT customer_id, COUNT(order_id) AS total_orders
FROM Orders
GROUP BY customer_id;
P16: Find the total total_amount for each status of orders.

-- Question
-- Calculate the sum of total amounts for orders based on their current
status (e.g., 'Delivered', 'Pending').

-- Answer
SELECT status, SUM(total_amount) AS total_sales_by_status
FROM Orders
GROUP BY status;

P17: List product categories that have an average price greater than
75.

-- Question
-- Identify product categories where the average price of products in that
category exceeds 75.

-- Answer
SELECT category, AVG(price) AS average_price
FROM Products
GROUP BY category
HAVING AVG(price) > 75;

P18: Find the customer_id s who have placed more than 2 orders.

-- Question
-- Show the IDs of customers who are frequent buyers (more than 2 orders).

-- Answer
SELECT customer_id, COUNT(order_id) AS order_count
FROM Orders
GROUP BY customer_id
HAVING COUNT(order_id) > 2;
P19: Calculate the total revenue generated from each product
category .

-- Question
-- Determine the total sales revenue for each product category.

-- Answer
SELECT [Link], SUM([Link] * oi.unit_price) AS total_revenue
FROM Products p
JOIN Order_Items oi ON p.product_id = oi.product_id
GROUP BY [Link];

P20: Find the month with the highest total sales amount.

-- Question
-- Identify which month (e.g., 'YYYY-MM') had the highest overall sales.

-- Answer
SELECT STRFTIME('%Y-%m', order_date) AS sales_month, SUM(total_amount) AS
monthly_sales
FROM Orders
GROUP BY sales_month
ORDER BY monthly_sales DESC
LIMIT 1;
-- Note: STRFTIME is for SQLite. Use FORMAT(order_date, 'yyyy-MM') for SQL
Server, TO_CHAR(order_date, 'YYYY-MM') for PostgreSQL/Oracle.
Category 3: Joins (P21-P30)

P21: Get the order_id , customer_name , and order_date for all orders.

-- Question
-- Combine order details with customer names for a comprehensive view of
each order.

-- Answer
SELECT o.order_id, c.first_name, c.last_name, o.order_date
FROM Orders o
INNER JOIN Customers c ON o.customer_id = c.customer_id;

P22: List all products that have been ordered, along with their
category .

-- Question
-- Show the names and categories of all products that appear in at least one
order.

-- Answer
SELECT DISTINCT p.product_name, [Link]
FROM Products p
INNER JOIN Order_Items oi ON p.product_id = oi.product_id;

P23: Find all customers who have placed an order, and the order_id s
associated with them.

-- Question
-- Retrieve customer names and their corresponding order IDs.

-- Answer
SELECT c.first_name, c.last_name, o.order_id
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id;
P24: List all customers and their orders. Include customers who have
not placed any orders.

-- Question
-- Display all customers, and if they have placed orders, show their order
IDs. Customers without orders should still be listed.

-- Answer
SELECT c.first_name, c.last_name, o.order_id
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;

P25: Find products that have never been ordered.

-- Question
-- Identify products that are currently in stock but have not appeared in
any order.

-- Answer
SELECT p.product_name
FROM Products p
LEFT JOIN Order_Items oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL;

P26: Get the order_id , product_name , quantity , and unit_price for

all items in all orders.

-- Question
-- Provide a detailed list of each item in every order, including product
name and quantity.

-- Answer
SELECT oi.order_id, p.product_name, [Link], oi.unit_price
FROM Order_Items oi
INNER JOIN Products p ON oi.product_id = p.product_id;
P27: Calculate the total revenue for each customer.

-- Question
-- For each customer, sum up the total amount they have spent across all
their orders.

-- Answer
SELECT c.first_name, c.last_name, SUM(o.total_amount) AS total_spent
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC;

P28: Find the top 5 products by total quantity sold.

-- Question
-- Which 5 products have sold the most units?

-- Answer
SELECT p.product_name, SUM([Link]) AS total_quantity_sold
FROM Products p
INNER JOIN Order_Items oi ON p.product_id = oi.product_id
GROUP BY p.product_name
ORDER BY total_quantity_sold DESC
LIMIT 5;
P29: List customers who have ordered products from the ‘Electronics’
category.

-- Question
-- Identify customers who have purchased any product categorized as
'Electronics'.

-- Answer
SELECT DISTINCT c.first_name, c.last_name
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
JOIN Order_Items oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
WHERE [Link] = 'Electronics';

P30: Find the average order_item_value (quantity * unit_price) for

each product category .

-- Question
-- Calculate the average value of individual order items for each product
category.

-- Answer
SELECT [Link], AVG([Link] * oi.unit_price) AS average_item_value
FROM Products p
JOIN Order_Items oi ON p.product_id = oi.product_id
GROUP BY [Link];
Category 4: Subqueries & CTEs (P31-P40)

P31: Find customers who have placed orders with a total_amount

greater than the average total_amount of all orders.

-- Question
-- Identify customers whose individual orders exceed the overall average
order value.

-- Answer
SELECT c.first_name, c.last_name
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.total_amount > (SELECT AVG(total_amount) FROM Orders);

P32: List products that have a price higher than the average price of
products in their own category .

-- Question
-- For each product, check if its price is above the average price of other
products within the same category.

-- Answer
SELECT p1.product_name, [Link], [Link]
FROM Products p1
WHERE [Link] > (SELECT AVG([Link]) FROM Products p2 WHERE [Link] =
[Link]);
P33: Find the customer_id of customers who have placed orders on
more than one distinct order_date .

-- Question
-- Identify customers who have placed orders on different days.

-- Answer
SELECT customer_id
FROM Orders
GROUP BY customer_id
HAVING COUNT(DISTINCT order_date) > 1;

P34: Using a subquery, find the product_name of the product with the
highest price .

-- Question
-- What is the name of the most expensive product?

-- Answer
SELECT product_name
FROM Products
WHERE price = (SELECT MAX(price) FROM Products);
P35: Using a CTE, find the total number of orders placed by each
customer.

-- Question
-- Use a CTE to calculate the total number of orders for each customer.

-- Answer
WITH CustomerOrderCounts AS (
SELECT customer_id, COUNT(order_id) AS order_count
FROM Orders
GROUP BY customer_id
)
SELECT c.first_name, c.last_name, coc.order_count
FROM Customers c
JOIN CustomerOrderCounts coc ON c.customer_id = coc.customer_id;

P36: Using a CTE, find the average total_amount for orders placed in
each city .

-- Question
-- Calculate the average order value for customers in each city using a CTE.

-- Answer
WITH CityOrderAmounts AS (
SELECT [Link], o.total_amount
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
)
SELECT city, AVG(total_amount) AS average_order_amount
FROM CityOrderAmounts
GROUP BY city;
P37: Find the customer_id s who have ordered all products in the
‘Electronics’ category.

-- Question
-- Identify customers who have purchased every single product that falls
under the 'Electronics' category.

-- Answer
SELECT c.customer_id
FROM Customers c
WHERE NOT EXISTS (
SELECT p.product_id
FROM Products p
WHERE [Link] = 'Electronics'
EXCEPT
SELECT oi.product_id
FROM Orders o
JOIN Order_Items oi ON o.order_id = oi.order_id
WHERE o.customer_id = c.customer_id
);
-- Note: This uses EXCEPT, which is standard SQL. Some databases might use
MINUS.

P38: List the product_name and its category for products that have
been ordered at least 3 times.

-- Question
-- Show products that have appeared in order items three or more times.

-- Answer
SELECT p.product_name, [Link]
FROM Products p
JOIN Order_Items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, [Link]
HAVING COUNT(oi.order_item_id) >= 3;
P39: Find the customer_id and email of customers who have not
placed any orders.

-- Question
-- Identify customers who are registered but have not made any purchases.

-- Answer
SELECT c.customer_id, [Link]
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

P40: Using a CTE, find the top 3 customers by total total_amount

spent.

-- Question
-- Use a CTE to determine the three customers who have spent the most money.

-- Answer
WITH CustomerTotalSpend AS (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM Orders
GROUP BY customer_id
)
SELECT c.first_name, c.last_name, cts.total_spent
FROM Customers c
JOIN CustomerTotalSpend cts ON c.customer_id = cts.customer_id
ORDER BY cts.total_spent DESC
LIMIT 3;
Category 5: Window Functions (P41-P50)

P41: Rank customers by their total_amount spent.

-- Question
-- Assign a rank to each customer based on their total spending, with the
highest spender ranked 1.

-- Answer
SELECT
c.first_name,
c.last_name,
SUM(o.total_amount) AS total_spent,
RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS customer_rank
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;

P42: Assign a ROW_NUMBER() to each order within each customer_id ,

ordered by order_date .

-- Question
-- For each customer, number their orders sequentially based on the date
they were placed.

-- Answer
SELECT
customer_id,
order_id,
order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS
order_sequence
FROM Orders;
P43: Find the second most expensive product in each category .

-- Question
-- For every product category, identify the product that is the second most
expensive.

-- Answer
WITH RankedProducts AS (
SELECT
product_name,
category,
price,
DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS
price_rank
FROM Products
)
SELECT product_name, category, price
FROM RankedProducts
WHERE price_rank = 2;

P44: Calculate the running total of total_amount for each customer,

ordered by order_date .

-- Question
-- For each customer, show the cumulative sum of their order amounts over
time.

-- Answer
SELECT
customer_id,
order_id,
order_date,
total_amount,
SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS
running_total_spent
FROM Orders;
P45: Find the previous order_date for each order placed by a
customer.

-- Question
-- For every order, show the date of the customer's immediately preceding
order.

-- Answer
SELECT
customer_id,
order_id,
order_date,
LAG(order_date, 1) OVER (PARTITION BY customer_id ORDER BY order_date)
AS previous_order_date
FROM Orders;

P46: Calculate the percentage of total_amount each order contributes

to its customer_id ’s total spending.

-- Question
-- For each order, determine what percentage of the customer's total
spending that order represents.

-- Answer
SELECT
customer_id,
order_id,
total_amount,
(total_amount * 100.0) / SUM(total_amount) OVER (PARTITION BY
customer_id) AS percentage_of_customer_total
FROM Orders;
P47: Find the customer_id and order_date of the first order for each
customer.

-- Question
-- For each customer, identify the date of their very first order.

-- Answer
WITH RankedOrders AS (
SELECT
customer_id,
order_id,
order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS
rn
FROM Orders
)
SELECT customer_id, order_date
FROM RankedOrders
WHERE rn = 1;

P48: Calculate the 3-day moving average of total_amount for all

orders, ordered by order_date .

-- Question
-- Compute a 3-day rolling average of the total order amounts.

-- Answer
SELECT
order_id,
order_date,
total_amount,
AVG(total_amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND
CURRENT ROW) AS three_day_moving_avg
FROM Orders;
P49: Find the customer_id s who have placed orders in the top 10% of
total_amount .

-- Question
-- Identify customers whose orders fall into the highest 10 percentiles of
order value.

-- Answer
SELECT DISTINCT customer_id
FROM (
SELECT
customer_id,
NTILE(10) OVER (ORDER BY total_amount DESC) AS percentile_group
FROM Orders
)
WHERE percentile_group = 1;

P50: For each order, show the order_id , order_date , total_amount ,

and the total_amount of the next order by the same customer.

-- Question
-- For every order, display its details along with the total amount of the
customer's subsequent order.

-- Answer
SELECT
customer_id,
order_id,
order_date,
total_amount,
LEAD(total_amount, 1) OVER (PARTITION BY customer_id ORDER BY
order_date) AS next_order_amount
FROM Orders;
SQL Practical Questions for Data
Analysts

Database Schema for Practical Questions

Throughout these practical questions, we will be using a simplified e-commerce

database schema. Please refer to these table descriptions for each question.

Table: Customers

customer_id (INT, PRIMARY KEY): Unique identifier for each customer.

first_name (VARCHAR): Customer’s first name.

last_name (VARCHAR): Customer’s last name.

email (VARCHAR, UNIQUE): Customer’s email address.

registration_date (DATE): Date when the customer registered.

city (VARCHAR): City where the customer resides.

country (VARCHAR): Country where the customer resides.

Table: Products

product_id (INT, PRIMARY KEY): Unique identifier for each product.

product_name (VARCHAR): Name of the product.

category (VARCHAR): Category of the product (e.g., ‘Electronics’, ‘Apparel’,

‘Books’).

price (DECIMAL(10, 2)): Unit price of the product.

stock_quantity (INT): Current stock level of the product.

Table: Orders

order_id (INT, PRIMARY KEY): Unique identifier for each order.

 customer_id (INT, FOREIGN KEY references Customers.customer_id ): ID of the
customer who placed the order.

order_date (DATE): Date when the order was placed.

total_amount (DECIMAL(10, 2)): Total amount of the order.

status (VARCHAR): Current status of the order (e.g., ‘Pending’, ‘Shipped’,

‘Delivered’, ‘Cancelled’).

Table: Order_Items

order_item_id (INT, PRIMARY KEY): Unique identifier for each order item.

order_id (INT, FOREIGN KEY references Orders.order_id ): ID of the order this

item belongs to.

product_id (INT, FOREIGN KEY references Products.product_id ): ID of the

product in this order item.

quantity (INT): Quantity of the product ordered.

unit_price (DECIMAL(10, 2)): Unit price of the product at the time of order.

Category 1: Basic Queries (P1-P10)

P1: Select all columns from the Customers table.

-- Question
-- Write a query to retrieve all information about customers.

-- Answer
SELECT *
FROM Customers;
P2: Select the product_name and price of all products.

-- Question
-- Retrieve the name and price for every product.

-- Answer
SELECT product_name, price
FROM Products;

P3: Find the customer_id and order_date for all orders.

-- Question
-- Get the customer ID and the date for each order placed.

-- Answer
SELECT customer_id, order_date
FROM Orders;

P4: List all unique product categories.

-- Question
-- Show a list of all distinct product categories available.

-- Answer
SELECT DISTINCT category
FROM Products;
P5: Get the first_name and last_name of customers who registered
after ‘2023-01-01’.

-- Question
-- Find the names of customers who joined the platform in 2023 or later.

-- Answer
SELECT first_name, last_name
FROM Customers
WHERE registration_date > '2023-01-01';

P6: Find all products with a price greater than 50.

-- Question
-- List products that cost more than 50 units.

-- Answer
SELECT product_name, price
FROM Products
WHERE price > 50;

P7: List orders that have a total_amount between 100 and 500
(inclusive).

-- Question
-- Retrieve orders where the total amount is within the range of 100 to 500.

-- Answer
SELECT order_id, total_amount
FROM Orders
WHERE total_amount BETWEEN 100 AND 500;
P8: Find all customers from ‘USA’ or ‘Canada’.

-- Question
-- Get the names of customers residing in either the USA or Canada.

-- Answer
SELECT first_name, last_name, country
FROM Customers
WHERE country IN ('USA', 'Canada');

P9: List products whose names start with ’S’.

-- Question
-- Find all products whose names begin with the letter 'S'.

-- Answer
SELECT product_name
FROM Products
WHERE product_name LIKE 'S%';

P10: Order customers by registration_date in descending order.

-- Question
-- Display customer information, with the most recently registered customers
appearing first.

-- Answer
SELECT *
FROM Customers
ORDER BY registration_date DESC;
Category 2: Aggregation & Grouping (P11-P20)

P11: Calculate the total number of orders.

-- Question
-- Find out how many orders have been placed in total.

-- Answer
SELECT COUNT(order_id)
FROM Orders;

P12: Find the average total_amount of all orders.

-- Question
-- Determine the average value of an order.

-- Answer
SELECT AVG(total_amount)
FROM Orders;

P13: Get the maximum price among all products.

-- Question
-- What is the highest price of any product?

-- Answer
SELECT MAX(price)
FROM Products;
P14: Calculate the sum of quantity for a specific product_id (e.g.,
product_id = 1 ).

-- Question
-- How many units of product with ID 1 have been sold in total?

-- Answer
SELECT SUM(quantity)
FROM Order_Items
WHERE product_id = 1;

P15: Count the number of orders for each customer_id .

-- Question
-- For each customer, show how many orders they have placed.

-- Answer
SELECT customer_id, COUNT(order_id) AS total_orders
FROM Orders
GROUP BY customer_id;

P16: Find the total total_amount for each status of orders.

-- Question
-- Calculate the sum of total amounts for orders based on their current
status (e.g., 'Delivered', 'Pending').

-- Answer
SELECT status, SUM(total_amount) AS total_sales_by_status
FROM Orders
GROUP BY status;
P17: List product categories that have an average price greater than
75.

-- Question
-- Identify product categories where the average price of products in that
category exceeds 75.

-- Answer
SELECT category, AVG(price) AS average_price
FROM Products
GROUP BY category
HAVING AVG(price) > 75;

P18: Find the customer_id s who have placed more than 2 orders.

-- Question
-- Show the IDs of customers who are frequent buyers (more than 2 orders).

-- Answer
SELECT customer_id, COUNT(order_id) AS order_count
FROM Orders
GROUP BY customer_id
HAVING COUNT(order_id) > 2;

P19: Calculate the total revenue generated from each product

category .

-- Question
-- Determine the total sales revenue for each product category.

-- Answer
SELECT [Link], SUM([Link] * oi.unit_price) AS total_revenue
FROM Products p
JOIN Order_Items oi ON p.product_id = oi.product_id
GROUP BY [Link];
P20: Find the month with the highest total sales amount.

-- Question
-- Identify which month (e.g., 'YYYY-MM') had the highest overall sales.

-- Answer
SELECT STRFTIME('%Y-%m', order_date) AS sales_month, SUM(total_amount) AS
monthly_sales
FROM Orders
GROUP BY sales_month
ORDER BY monthly_sales DESC
LIMIT 1;
-- Note: STRFTIME is for SQLite. Use FORMAT(order_date, 'yyyy-MM') for SQL
Server, TO_CHAR(order_date, 'YYYY-MM') for PostgreSQL/Oracle.

Category 3: Joins (P21-P30)

P21: Get the order_id , customer_name , and order_date for all orders.

-- Question
-- Combine order details with customer names for a comprehensive view of
each order.

-- Answer
SELECT o.order_id, c.first_name, c.last_name, o.order_date
FROM Orders o
INNER JOIN Customers c ON o.customer_id = c.customer_id;
P22: List all products that have been ordered, along with their
category .

-- Question
-- Show the names and categories of all products that appear in at least one
order.

-- Answer
SELECT DISTINCT p.product_name, [Link]
FROM Products p
INNER JOIN Order_Items oi ON p.product_id = oi.product_id;

P23: Find all customers who have placed an order, and the order_id s
associated with them.

-- Question
-- Retrieve customer names and their corresponding order IDs.

-- Answer
SELECT c.first_name, c.last_name, o.order_id
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id;

P24: List all customers and their orders. Include customers who have
not placed any orders.

-- Question
-- Display all customers, and if they have placed orders, show their order
IDs. Customers without orders should still be listed.

-- Answer
SELECT c.first_name, c.last_name, o.order_id
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
P25: Find products that have never been ordered.

-- Question
-- Identify products that are currently in stock but have not appeared in
any order.

-- Answer
SELECT p.product_name
FROM Products p
LEFT JOIN Order_Items oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL;

P26: Get the order_id , product_name , quantity , and unit_price for

all items in all orders.

-- Question
-- Provide a detailed list of each item in every order, including product
name and quantity.

-- Answer
SELECT oi.order_id, p.product_name, [Link], oi.unit_price
FROM Order_Items oi
INNER JOIN Products p ON oi.product_id = p.product_id;

P27: Calculate the total revenue for each customer.

-- Question
-- For each customer, sum up the total amount they have spent across all
their orders.

-- Answer
SELECT c.first_name, c.last_name, SUM(o.total_amount) AS total_spent
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC;
P28: Find the top 5 products by total quantity sold.

-- Question
-- Which 5 products have sold the most units?

-- Answer
SELECT p.product_name, SUM([Link]) AS total_quantity_sold
FROM Products p
INNER JOIN Order_Items oi ON p.product_id = oi.product_id
GROUP BY p.product_name
ORDER BY total_quantity_sold DESC
LIMIT 5;

P29: List customers who have ordered products from the ‘Electronics’
category.

-- Question
-- Identify customers who have purchased any product categorized as
'Electronics'.

-- Answer
SELECT DISTINCT c.first_name, c.last_name
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
JOIN Order_Items oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
WHERE [Link] = 'Electronics';
P30: Find the average order_item_value (quantity * unit_price) for
each product category .

-- Question
-- Calculate the average value of individual order items for each product
category.

-- Answer
SELECT [Link], AVG([Link] * oi.unit_price) AS average_item_value
FROM Products p
JOIN Order_Items oi ON p.product_id = oi.product_id
GROUP BY [Link];

Category 4: Subqueries & CTEs (P31-P40)

P31: Find customers who have placed orders with a total_amount

greater than the average total_amount of all orders.

-- Question
-- Identify customers whose individual orders exceed the overall average
order value.

-- Answer
SELECT c.first_name, c.last_name
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.total_amount > (SELECT AVG(total_amount) FROM Orders);
P32: List products that have a price higher than the average price of
products in their own category .

-- Question
-- For each product, check if its price is above the average price of other
products within the same category.

-- Answer
SELECT p1.product_name, [Link], [Link]
FROM Products p1
WHERE [Link] > (SELECT AVG([Link]) FROM Products p2 WHERE [Link] =
[Link]);

P33: Find the customer_id of customers who have placed orders on

more than one distinct order_date .

-- Question
-- Identify customers who have placed orders on different days.

-- Answer
SELECT customer_id
FROM Orders
GROUP BY customer_id
HAVING COUNT(DISTINCT order_date) > 1;

P34: Using a subquery, find the product_name of the product with the
highest price .

-- Question
-- What is the name of the most expensive product?

-- Answer
SELECT product_name
FROM Products
WHERE price = (SELECT MAX(price) FROM Products);
P35: Using a CTE, find the total number of orders placed by each
customer.

-- Question
-- Use a CTE to calculate the total number of orders for each customer.

-- Answer
WITH CustomerOrderCounts AS (
SELECT customer_id, COUNT(order_id) AS order_count
FROM Orders
GROUP BY customer_id
)
SELECT c.first_name, c.last_name, coc.order_count
FROM Customers c
JOIN CustomerOrderCounts coc ON c.customer_id = coc.customer_id;

P36: Using a CTE, find the average total_amount for orders placed in
each city .

-- Question
-- Calculate the average order value for customers in each city using a CTE.

-- Answer
WITH CityOrderAmounts AS (
SELECT [Link], o.total_amount
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
)
SELECT city, AVG(total_amount) AS average_order_amount
FROM CityOrderAmounts
GROUP BY city;
P37: Find the customer_id s who have ordered all products in the
‘Electronics’ category.

-- Question
-- Identify customers who have purchased every single product that falls
under the 'Electronics' category.

-- Answer
SELECT c.customer_id
FROM Customers c
WHERE NOT EXISTS (
SELECT p.product_id
FROM Products p
WHERE [Link] = 'Electronics'
EXCEPT
SELECT oi.product_id
FROM Orders o
JOIN Order_Items oi ON o.order_id = oi.order_id
WHERE o.customer_id = c.customer_id
);
-- Note: This uses EXCEPT, which is standard SQL. Some databases might use
MINUS.

P38: List the product_name and its category for products that have
been ordered at least 3 times.

-- Question
-- Show products that have appeared in order items three or more times.

-- Answer
SELECT p.product_name, [Link]
FROM Products p
JOIN Order_Items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, [Link]
HAVING COUNT(oi.order_item_id) >= 3;
P39: Find the customer_id and email of customers who have not
placed any orders.

-- Question
-- Identify customers who are registered but have not made any purchases.

-- Answer
SELECT c.customer_id, [Link]
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

P40: Using a CTE, find the top 3 customers by total total_amount

spent.

-- Question
-- Use a CTE to determine the three customers who have spent the most money.

-- Answer
WITH CustomerTotalSpend AS (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM Orders
GROUP BY customer_id
)
SELECT c.first_name, c.last_name, cts.total_spent
FROM Customers c
JOIN CustomerTotalSpend cts ON c.customer_id = cts.customer_id
ORDER BY cts.total_spent DESC
LIMIT 3;
Category 5: Window Functions (P41-P50)

P41: Rank customers by their total_amount spent.

-- Question
-- Assign a rank to each customer based on their total spending, with the
highest spender ranked 1.

-- Answer
SELECT
c.first_name,
c.last_name,
SUM(o.total_amount) AS total_spent,
RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS customer_rank
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;

P42: Assign a ROW_NUMBER() to each order within each customer_id ,

ordered by order_date .

-- Question
-- For each customer, number their orders sequentially based on the date
they were placed.

-- Answer
SELECT
customer_id,
order_id,
order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS
order_sequence
FROM Orders;
P43: Find the second most expensive product in each category .

-- Question
-- For every product category, identify the product that is the second most
expensive.

-- Answer
WITH RankedProducts AS (
SELECT
product_name,
category,
price,
DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS
price_rank
FROM Products
)
SELECT product_name, category, price
FROM RankedProducts
WHERE price_rank = 2;

P44: Calculate the running total of total_amount for each customer,

ordered by order_date .

-- Question
-- For each customer, show the cumulative sum of their order amounts over
time.

-- Answer
SELECT
customer_id,
order_id,
order_date,
total_amount,
SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS
running_total_spent
FROM Orders;
P45: Find the previous order_date for each order placed by a
customer.

-- Question
-- For every order, show the date of the customer's immediately preceding
order.

-- Answer
SELECT
customer_id,
order_id,
order_date,
LAG(order_date, 1) OVER (PARTITION BY customer_id ORDER BY order_date)
AS previous_order_date
FROM Orders;

P46: Calculate the percentage of total_amount each order contributes

to its customer_id ’s total spending.

-- Question
-- For each order, determine what percentage of the customer's total
spending that order represents.

-- Answer
SELECT
customer_id,
order_id,
total_amount,
(total_amount * 100.0) / SUM(total_amount) OVER (PARTITION BY
customer_id) AS percentage_of_customer_total
FROM Orders;
P47: Find the customer_id and order_date of the first order for each
customer.

-- Question
-- For each customer, identify the date of their very first order.

-- Answer
WITH RankedOrders AS (
SELECT
customer_id,
order_id,
order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS
rn
FROM Orders
)
SELECT customer_id, order_date
FROM RankedOrders
WHERE rn = 1;

P48: Calculate the 3-day moving average of total_amount for all

orders, ordered by order_date .

-- Question
-- Compute a 3-day rolling average of the total order amounts.

-- Answer
SELECT
order_id,
order_date,
total_amount,
AVG(total_amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND
CURRENT ROW) AS three_day_moving_avg
FROM Orders;
P49: Find the customer_id s who have placed orders in the top 10% of
total_amount .

-- Question
-- Identify customers whose orders fall into the highest 10 percentiles of
order value.

-- Answer
SELECT DISTINCT customer_id
FROM (
SELECT
customer_id,
NTILE(10) OVER (ORDER BY total_amount DESC) AS percentile_group
FROM Orders
)
WHERE percentile_group = 1;

P50: For each order, show the order_id , order_date , total_amount ,

and the total_amount of the next order by the same customer.

-- Question
-- For every order, display its details along with the total amount of the
customer's subsequent order.

-- Answer
SELECT
customer_id,
order_id,
order_date,
total_amount,
LEAD(total_amount, 1) OVER (PARTITION BY customer_id ORDER BY
order_date) AS next_order_amount
FROM Orders;
SQL Practical Questions for Data
Analysts

Database Schema for Practical Questions

Throughout these practical questions, we will be using a simplified e-commerce

database schema. Please refer to these table descriptions for each question.

Table: Customers

customer_id (INT, PRIMARY KEY): Unique identifier for each customer.

first_name (VARCHAR): Customer’s first name.

last_name (VARCHAR): Customer’s last name.

email (VARCHAR, UNIQUE): Customer’s email address.

registration_date (DATE): Date when the customer registered.

city (VARCHAR): City where the customer resides.

country (VARCHAR): Country where the customer resides.

Table: Products

product_id (INT, PRIMARY KEY): Unique identifier for each product.

product_name (VARCHAR): Name of the product.

category (VARCHAR): Category of the product (e.g., ‘Electronics’, ‘Apparel’,

‘Books’).

price (DECIMAL(10, 2)): Unit price of the product.

stock_quantity (INT): Current stock level of the product.

Table: Orders

order_id (INT, PRIMARY KEY): Unique identifier for each order.

 customer_id (INT, FOREIGN KEY references Customers.customer_id ): ID of the
customer who placed the order.

order_date (DATE): Date when the order was placed.

total_amount (DECIMAL(10, 2)): Total amount of the order.

status (VARCHAR): Current status of the order (e.g., ‘Pending’, ‘Shipped’,

‘Delivered’, ‘Cancelled’).

Table: Order_Items

order_item_id (INT, PRIMARY KEY): Unique identifier for each order item.

order_id (INT, FOREIGN KEY references Orders.order_id ): ID of the order this

item belongs to.

product_id (INT, FOREIGN KEY references Products.product_id ): ID of the

product in this order item.

quantity (INT): Quantity of the product ordered.

unit_price (DECIMAL(10, 2)): Unit price of the product at the time of order.

Category 1: Basic Queries (P1-P10)

P1: Select all columns from the Customers table.

-- Question
-- Write a query to retrieve all information about customers.

-- Answer
SELECT *
FROM Customers;
P2: Select the product_name and price of all products.

-- Question
-- Retrieve the name and price for every product.

-- Answer
SELECT product_name, price
FROM Products;

P3: Find the customer_id and order_date for all orders.

-- Question
-- Get the customer ID and the date for each order placed.

-- Answer
SELECT customer_id, order_date
FROM Orders;

P4: List all unique product categories.

-- Question
-- Show a list of all distinct product categories available.

-- Answer
SELECT DISTINCT category
FROM Products;
P5: Get the first_name and last_name of customers who registered
after ‘2023-01-01’.

-- Question
-- Find the names of customers who joined the platform in 2023 or later.

-- Answer
SELECT first_name, last_name
FROM Customers
WHERE registration_date > '2023-01-01';

P6: Find all products with a price greater than 50.

-- Question
-- List products that cost more than 50 units.

-- Answer
SELECT product_name, price
FROM Products
WHERE price > 50;

P7: List orders that have a total_amount between 100 and 500
(inclusive).

-- Question
-- Retrieve orders where the total amount is within the range of 100 to 500.

-- Answer
SELECT order_id, total_amount
FROM Orders
WHERE total_amount BETWEEN 100 AND 500;
P8: Find all customers from ‘USA’ or ‘Canada’.

-- Question
-- Get the names of customers residing in either the USA or Canada.

-- Answer
SELECT first_name, last_name, country
FROM Customers
WHERE country IN ('USA', 'Canada');

P9: List products whose names start with ’S’.

-- Question
-- Find all products whose names begin with the letter 'S'.

-- Answer
SELECT product_name
FROM Products
WHERE product_name LIKE 'S%';

P10: Order customers by registration_date in descending order.

-- Question
-- Display customer information, with the most recently registered customers
appearing first.

-- Answer
SELECT *
FROM Customers
ORDER BY registration_date DESC;
Category 2: Aggregation & Grouping (P11-P20)

P11: Calculate the total number of orders.

-- Question
-- Find out how many orders have been placed in total.

-- Answer
SELECT COUNT(order_id)
FROM Orders;

P12: Find the average total_amount of all orders.

-- Question
-- Determine the average value of an order.

-- Answer
SELECT AVG(total_amount)
FROM Orders;

P13: Get the maximum price among all products.

-- Question
-- What is the highest price of any product?

-- Answer
SELECT MAX(price)
FROM Products;
P14: Calculate the sum of quantity for a specific product_id (e.g.,
product_id = 1 ).

-- Question
-- How many units of product with ID 1 have been sold in total?

-- Answer
SELECT SUM(quantity)
FROM Order_Items
WHERE product_id = 1;

P15: Count the number of orders for each customer_id .

-- Question
-- For each customer, show how many orders they have placed.

-- Answer
SELECT customer_id, COUNT(order_id) AS total_orders
FROM Orders
GROUP BY customer_id;

P16: Find the total total_amount for each status of orders.

-- Question
-- Calculate the sum of total amounts for orders based on their current
status (e.g., 'Delivered', 'Pending').

-- Answer
SELECT status, SUM(total_amount) AS total_sales_by_status
FROM Orders
GROUP BY status;
P17: List product categories that have an average price greater than
75.

-- Question
-- Identify product categories where the average price of products in that
category exceeds 75.

-- Answer
SELECT category, AVG(price) AS average_price
FROM Products
GROUP BY category
HAVING AVG(price) > 75;

P18: Find the customer_id s who have placed more than 2 orders.

-- Question
-- Show the IDs of customers who are frequent buyers (more than 2 orders).

-- Answer
SELECT customer_id, COUNT(order_id) AS order_count
FROM Orders
GROUP BY customer_id
HAVING COUNT(order_id) > 2;

P19: Calculate the total revenue generated from each product

category .

-- Question
-- Determine the total sales revenue for each product category.

-- Answer
SELECT [Link], SUM([Link] * oi.unit_price) AS total_revenue
FROM Products p
JOIN Order_Items oi ON p.product_id = oi.product_id
GROUP BY [Link];
P20: Find the month with the highest total sales amount.

-- Question
-- Identify which month (e.g., 'YYYY-MM') had the highest overall sales.

-- Answer
SELECT STRFTIME('%Y-%m', order_date) AS sales_month, SUM(total_amount) AS
monthly_sales
FROM Orders
GROUP BY sales_month
ORDER BY monthly_sales DESC
LIMIT 1;
-- Note: STRFTIME is for SQLite. Use FORMAT(order_date, 'yyyy-MM') for SQL
Server, TO_CHAR(order_date, 'YYYY-MM') for PostgreSQL/Oracle.

Category 3: Joins (P21-P30)

P21: Get the order_id , customer_name , and order_date for all orders.

-- Question
-- Combine order details with customer names for a comprehensive view of
each order.

-- Answer
SELECT o.order_id, c.first_name, c.last_name, o.order_date
FROM Orders o
INNER JOIN Customers c ON o.customer_id = c.customer_id;
P22: List all products that have been ordered, along with their
category .

-- Question
-- Show the names and categories of all products that appear in at least one
order.

-- Answer
SELECT DISTINCT p.product_name, [Link]
FROM Products p
INNER JOIN Order_Items oi ON p.product_id = oi.product_id;

P23: Find all customers who have placed an order, and the order_id s
associated with them.

-- Question
-- Retrieve customer names and their corresponding order IDs.

-- Answer
SELECT c.first_name, c.last_name, o.order_id
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id;

P24: List all customers and their orders. Include customers who have
not placed any orders.

-- Question
-- Display all customers, and if they have placed orders, show their order
IDs. Customers without orders should still be listed.

-- Answer
SELECT c.first_name, c.last_name, o.order_id
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;
P25: Find products that have never been ordered.

-- Question
-- Identify products that are currently in stock but have not appeared in
any order.

-- Answer
SELECT p.product_name
FROM Products p
LEFT JOIN Order_Items oi ON p.product_id = oi.product_id
WHERE oi.order_item_id IS NULL;

P26: Get the order_id , product_name , quantity , and unit_price for

all items in all orders.

-- Question
-- Provide a detailed list of each item in every order, including product
name and quantity.

-- Answer
SELECT oi.order_id, p.product_name, [Link], oi.unit_price
FROM Order_Items oi
INNER JOIN Products p ON oi.product_id = p.product_id;

P27: Calculate the total revenue for each customer.

-- Question
-- For each customer, sum up the total amount they have spent across all
their orders.

-- Answer
SELECT c.first_name, c.last_name, SUM(o.total_amount) AS total_spent
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC;
P28: Find the top 5 products by total quantity sold.

-- Question
-- Which 5 products have sold the most units?

-- Answer
SELECT p.product_name, SUM([Link]) AS total_quantity_sold
FROM Products p
INNER JOIN Order_Items oi ON p.product_id = oi.product_id
GROUP BY p.product_name
ORDER BY total_quantity_sold DESC
LIMIT 5;

P29: List customers who have ordered products from the ‘Electronics’
category.

-- Question
-- Identify customers who have purchased any product categorized as
'Electronics'.

-- Answer
SELECT DISTINCT c.first_name, c.last_name
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
JOIN Order_Items oi ON o.order_id = oi.order_id
JOIN Products p ON oi.product_id = p.product_id
WHERE [Link] = 'Electronics';
P30: Find the average order_item_value (quantity * unit_price) for
each product category .

-- Question
-- Calculate the average value of individual order items for each product
category.

-- Answer
SELECT [Link], AVG([Link] * oi.unit_price) AS average_item_value
FROM Products p
JOIN Order_Items oi ON p.product_id = oi.product_id
GROUP BY [Link];

Category 4: Subqueries & CTEs (P31-P40)

P31: Find customers who have placed orders with a total_amount

greater than the average total_amount of all orders.

-- Question
-- Identify customers whose individual orders exceed the overall average
order value.

-- Answer
SELECT c.first_name, c.last_name
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.total_amount > (SELECT AVG(total_amount) FROM Orders);
P32: List products that have a price higher than the average price of
products in their own category .

-- Question
-- For each product, check if its price is above the average price of other
products within the same category.

-- Answer
SELECT p1.product_name, [Link], [Link]
FROM Products p1
WHERE [Link] > (SELECT AVG([Link]) FROM Products p2 WHERE [Link] =
[Link]);

P33: Find the customer_id of customers who have placed orders on

more than one distinct order_date .

-- Question
-- Identify customers who have placed orders on different days.

-- Answer
SELECT customer_id
FROM Orders
GROUP BY customer_id
HAVING COUNT(DISTINCT order_date) > 1;

P34: Using a subquery, find the product_name of the product with the
highest price .

-- Question
-- What is the name of the most expensive product?

-- Answer
SELECT product_name
FROM Products
WHERE price = (SELECT MAX(price) FROM Products);
P35: Using a CTE, find the total number of orders placed by each
customer.

-- Question
-- Use a CTE to calculate the total number of orders for each customer.

-- Answer
WITH CustomerOrderCounts AS (
SELECT customer_id, COUNT(order_id) AS order_count
FROM Orders
GROUP BY customer_id
)
SELECT c.first_name, c.last_name, coc.order_count
FROM Customers c
JOIN CustomerOrderCounts coc ON c.customer_id = coc.customer_id;

P36: Using a CTE, find the average total_amount for orders placed in
each city .

-- Question
-- Calculate the average order value for customers in each city using a CTE.

-- Answer
WITH CityOrderAmounts AS (
SELECT [Link], o.total_amount
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
)
SELECT city, AVG(total_amount) AS average_order_amount
FROM CityOrderAmounts
GROUP BY city;
P37: Find the customer_id s who have ordered all products in the
‘Electronics’ category.

-- Question
-- Identify customers who have purchased every single product that falls
under the 'Electronics' category.

-- Answer
SELECT c.customer_id
FROM Customers c
WHERE NOT EXISTS (
SELECT p.product_id
FROM Products p
WHERE [Link] = 'Electronics'
EXCEPT
SELECT oi.product_id
FROM Orders o
JOIN Order_Items oi ON o.order_id = oi.order_id
WHERE o.customer_id = c.customer_id
);
-- Note: This uses EXCEPT, which is standard SQL. Some databases might use
MINUS.

P38: List the product_name and its category for products that have
been ordered at least 3 times.

-- Question
-- Show products that have appeared in order items three or more times.

-- Answer
SELECT p.product_name, [Link]
FROM Products p
JOIN Order_Items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, [Link]
HAVING COUNT(oi.order_item_id) >= 3;
P39: Find the customer_id and email of customers who have not
placed any orders.

-- Question
-- Identify customers who are registered but have not made any purchases.

-- Answer
SELECT c.customer_id, [Link]
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

P40: Using a CTE, find the top 3 customers by total total_amount

spent.

-- Question
-- Use a CTE to determine the three customers who have spent the most money.

-- Answer
WITH CustomerTotalSpend AS (
SELECT customer_id, SUM(total_amount) AS total_spent
FROM Orders
GROUP BY customer_id
)
SELECT c.first_name, c.last_name, cts.total_spent
FROM Customers c
JOIN CustomerTotalSpend cts ON c.customer_id = cts.customer_id
ORDER BY cts.total_spent DESC
LIMIT 3;
Category 5: Window Functions (P41-P50)

P41: Rank customers by their total_amount spent.

-- Question
-- Assign a rank to each customer based on their total spending, with the
highest spender ranked 1.

-- Answer
SELECT
c.first_name,
c.last_name,
SUM(o.total_amount) AS total_spent,
RANK() OVER (ORDER BY SUM(o.total_amount) DESC) AS customer_rank
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name;

P42: Assign a ROW_NUMBER() to each order within each customer_id ,

ordered by order_date .

-- Question
-- For each customer, number their orders sequentially based on the date
they were placed.

-- Answer
SELECT
customer_id,
order_id,
order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS
order_sequence
FROM Orders;
P43: Find the second most expensive product in each category .

-- Question
-- For every product category, identify the product that is the second most
expensive.

-- Answer
WITH RankedProducts AS (
SELECT
product_name,
category,
price,
DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS
price_rank
FROM Products
)
SELECT product_name, category, price
FROM RankedProducts
WHERE price_rank = 2;

P44: Calculate the running total of total_amount for each customer,

ordered by order_date .

-- Question
-- For each customer, show the cumulative sum of their order amounts over
time.

-- Answer
SELECT
customer_id,
order_id,
order_date,
total_amount,
SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS
running_total_spent
FROM Orders;
P45: Find the previous order_date for each order placed by a
customer.

-- Question
-- For every order, show the date of the customer's immediately preceding
order.

-- Answer
SELECT
customer_id,
order_id,
order_date,
LAG(order_date, 1) OVER (PARTITION BY customer_id ORDER BY order_date)
AS previous_order_date
FROM Orders;

P46: Calculate the percentage of total_amount each order contributes

to its customer_id ’s total spending.

-- Question
-- For each order, determine what percentage of the customer's total
spending that order represents.

-- Answer
SELECT
customer_id,
order_id,
total_amount,
(total_amount * 100.0) / SUM(total_amount) OVER (PARTITION BY
customer_id) AS percentage_of_customer_total
FROM Orders;
P47: Find the customer_id and order_date of the first order for each
customer.

-- Question
-- For each customer, identify the date of their very first order.

-- Answer
WITH RankedOrders AS (
SELECT
customer_id,
order_id,
order_date,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS
rn
FROM Orders
)
SELECT customer_id, order_date
FROM RankedOrders
WHERE rn = 1;

P48: Calculate the 3-day moving average of total_amount for all

orders, ordered by order_date .

-- Question
-- Compute a 3-day rolling average of the total order amounts.

-- Answer
SELECT
order_id,
order_date,
total_amount,
AVG(total_amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND
CURRENT ROW) AS three_day_moving_avg
FROM Orders;
P49: Find the customer_id s who have placed orders in the top 10% of
total_amount .

-- Question
-- Identify customers whose orders fall into the highest 10 percentiles of
order value.

-- Answer
SELECT DISTINCT customer_id
FROM (
SELECT
customer_id,
NTILE(10) OVER (ORDER BY total_amount DESC) AS percentile_group
FROM Orders
)
WHERE percentile_group = 1;

P50: For each order, show the order_id , order_date , total_amount ,

and the total_amount of the next order by the same customer.

-- Question
-- For every order, display its details along with the total amount of the
customer's subsequent order.

-- Answer
SELECT
customer_id,
order_id,
order_date,
total_amount,
LEAD(total_amount, 1) OVER (PARTITION BY customer_id ORDER BY
order_date) AS next_order_amount
FROM Orders;