SQL QUERIES

Akash Rajan
 SQL QUERIES

1. Write a query to find the highest salary from an Employee table ?

Using a subquery (works across various SQL databases)

SELECT *
FROM Employee
WHERE Salary = (SELECT MAX(Salary) FROM Employee);

Explanation:
• The subquery (SELECT MAX(Salary) FROM Employee) finds the single highest salary
value from the table.
• The outer query then selects all employees whose Salary matches this maximum value,
ensuring that all employees with the highest salary are included.

2. Write a query to find the 2nd highest salary from an Employee table ?

SELECT MAX(salary) AS SecondHighestSalary

FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

Method 2: Using LIMIT with OFFSET (MySQL/PostgreSQL)

SELECT DISTINCT salary

FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

• ORDER BY salary DESC → highest salaries first

• OFFSET 1 skips the top (1st highest) salary
• LIMIT 1 returns the next one (2nd highest)

3. Write a query to find the 3nd highest salary from an Employee table ?

Method 1: Using LIMIT with OFFSET (MySQL, PostgreSQL)

SELECT DISTINCT salary

FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

1
https://www.linkedin.com/in/akash-rajan-bb3435288/
 • OFFSET 2 skips the top 2 highest salaries.
• LIMIT 1 picks the 3rd highest.
• DISTINCT handles duplicates.

Method 2: Using Subquery with MAX (Works in most SQL engines)

SELECT MAX(salary) AS ThirdHighestSalary

FROM Employee
WHERE salary < (
SELECT MAX(salary)
FROM Employee
WHERE salary < (
SELECT MAX(salary)
FROM Employee
)
);

• First MAX → highest salary

• Second MAX → 2nd highest
• Third MAX → 3rd highest

4. Write a SQL query to find Employees who have a same manager ?

SELECT manager_id
FROM Employee
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING COUNT(*) > 1;

This will return only the manager_ids who manage more than one employee.

5. Write a query to find the first and last record for each employee based on the “hire_date”
column ?

Case 1: Find the first and last hire_date in the whole table

-- First (earliest) hire

SELECT *
FROM Employee
WHERE hire_date = (SELECT MIN(hire_date) FROM
Employee);

2
https://www.linkedin.com/in/akash-rajan-bb3435288/
 -- Last (latest) hire
SELECT *
FROM Employee
WHERE hire_date = (SELECT MAX(hire_date) FROM
Employee);

6. Write a query to find the most recent transaction for each customer ?

Using a subquery with MAX()

SELECT *
FROM Transactions t
WHERE transaction_date = (
SELECT MAX(transaction_date)
FROM Transactions
WHERE customer_id = t.customer_id
);

7. Write a query to find the total salary of each department and display departments with a total
salary greater than a specified value (50,000).

total salary of each department and filter departments with total salary greater than
50,000, use the GROUP BY and HAVING clauses.

SELECT department_id, SUM(salary) AS total_salary

FROM Employee
GROUP BY department_id
HAVING SUM(salary) > 50000;

Explanation:
• GROUP BY department_id → groups employees by their department.
• SUM(salary) → calculates total salary for each department.
• HAVING → filters groups after aggregation (unlike WHERE).

3
https://www.linkedin.com/in/akash-rajan-bb3435288/
 8. Write a query to find the running total of orders for each customers sorted by order date.

Query to Find Running Total:

SELECT
customer_id,
order_id,
order_date,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS running_total
FROM Orders
ORDER BY customer_id, order_date;

Explanation:

• PARTITION BY customer_id → resets total for each customer.

• ORDER BY order_date → computes the running total in date order.
• SUM(amount) OVER (...) → calculates the running total.
• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is optional
(default behavior).

Output Columns:

• customer_id
• order_id
• order_date
• amount
• running_total → cumulative total of amount for each customer

9. Write a query to get the total number of employees hired per month and year.

SELECT
YEAR(hire_date) AS hire_year,
MONTH(hire_date) AS hire_month,
COUNT(*) AS total_hired
FROM Employee

4
https://www.linkedin.com/in/akash-rajan-bb3435288/
 GROUP BY hire_year, hire_month
ORDER BY hire_year, hire_month;

Alternative for SQL Server:

SELECT
YEAR(hire_date) AS hire_year,
MONTH(hire_date) AS hire_month,
COUNT(*) AS total_hired
FROM Employee

10. Write a query to display all employees who earn the more than the average salary for their
departments.

SELECT *
FROM Employee e
WHERE salary > (
SELECT AVG(salary)
FROM Employee
WHERE department_id = e.department_id
);

11. Write a query to get the second-lowest salary from the employee table without using LIMIT
and OFFSET.

To get the second-lowest salary from the Employee table without using LIMIT or
OFFSET, you can use a subquery with MIN() or DISTINCT + ORDER BY +
ROW_NUMBER() depending on your database support.

SELECT MIN(salary) AS second_lowest_salary

FROM Employee
WHERE salary > (
SELECT MIN(salary) FROM Employee
);

5
https://www.linkedin.com/in/akash-rajan-bb3435288/
 12. Write a query to list all products that have never been ordered (assuming on ‘orders’ table and
a ‘products’ table).

Method 1: Using LEFT JOIN and IS NULL (Most common)

SELECT p.*
FROM Products p
LEFT JOIN Orders o ON p.product_id = o.product_id
WHERE o.product_id IS NULL;

Explanation:
• LEFT JOIN keeps all products.
• If there’s no match in Orders, the joined o.product_id will be NULL — meaning the product
was never ordered.

Method 2: Using NOT EXISTS

SELECT *
FROM Products p
WHERE NOT EXISTS (
SELECT 1
FROM Orders o
WHERE o.product_id = p.product_id
);

13. Write a query to list all the employees who are also managers.

SELECT *
FROM Employee
WHERE emp_id IN (
SELECT DISTINCT manager_id
FROM Employee
WHERE manager_id IS NOT NULL
);

Explanation:

• The subquery finds all distinct manager_ids from the Employee table.
• The outer query returns employees whose emp_id matches one of those — meaning they are
managing others.

6
https://www.linkedin.com/in/akash-rajan-bb3435288/
 14. Write a query to find employees who have joined in the same month and year.

employees who joined in the same month and year, you want to identify employees whose
hire_date shares the same month and year as at least one other employee.

Step-by-step Plan:

1. Extract MONTH and YEAR from hire_date.

2. Group by YEAR + MONTH.
3. Keep only groups with more than one employee.
4. Then select those employees.

SELECT *
FROM Employee e
WHERE EXISTS (
SELECT 1
FROM Employee e2
WHERE
e.emp_id <> e2.emp_id AND
MONTH(e.hire_date) = MONTH(e2.hire_date)
AND
YEAR(e.hire_date) = YEAR(e2.hire_date)
);

Alternate Version: Using GROUP BY + HAVING

SELECT *
FROM Employee
WHERE DATE_FORMAT(hire_date, '%Y-%m') IN
(
SELECT DATE_FORMAT(hire_date, '%Y-%m')
FROM Employee
GROUP BY DATE_FORMAT(hire_date, '%Y-%m')
HAVING COUNT(*) > 1
);

This works in MySQL. For SQL Server or PostgreSQL, use TO_CHAR() or FORMAT()
instead of DATE_FORMAT.

Explanation:
• DATE_FORMAT(hire_date, '%Y-%m') groups by year and month.
• HAVING COUNT(*) > 1 ensures only months with more than one employee are considered.

7
https://www.linkedin.com/in/akash-rajan-bb3435288/
 15. Write a SQL query to get a list of employees who are older than the average age of employees
in their department.

SELECT *
FROM Employee e
WHERE age > (
SELECT AVG(age)
FROM Employee
WHERE department_id = e.department_id
);

16. Write a query to display the employee(s) with the longest tenure at the company.

SELECT *
FROM Employee
WHERE hire_date = (
SELECT MIN(hire_date)
FROM Employee
);

17. Write a query to delete all records from a table where the column value is NULL.
To delete all records from a table where a specific column value is NULL, use a DELETE
statement with a WHERE column IS NULL condition.
General Syntax:

DELETE FROM table_name

WHERE column_name IS NULL;

Example:
If you have a table called Employee and you want to delete rows where email is NULL:

DELETE FROM Employee

WHERE email IS NULL;

Important Notes:

• This operation cannot be undone unless you're using transactions (like in PostgreSQL,
MySQL with InnoDB, etc.).

8
https://www.linkedin.com/in/akash-rajan-bb3435288/
 • Always make a SELECT query first to preview the affected rows:

SELECT * FROM Employee WHERE email IS NULL;

18. Write a query to find all pairs of products that were ordered together at least once.

SQL Query: Find all product pairs that were ordered together at least once
SELECT
od1.ProductID AS Product1,
od2.ProductID AS Product2
FROM
OrderDetails od1
JOIN
OrderDetails od2
ON od1.OrderID = od2.OrderID
AND od1.ProductID < od2.ProductID
GROUP BY
od1.ProductID, od2.ProductID;

Explanation:

• We self-join OrderDetails on OrderID to find products in the same order.

• We use od1.ProductID < od2.ProductID to:
o Avoid duplicate pairs like (P1, P2) and (P2, P1).
o Avoid pairing the same product with itself.
• We GROUP BY the product pairs to get only unique combinations.

19. Write a query to find the average order value by customer for each month.

To find the average order value by customer for each month, we need to:
1. Sum the value of each order (e.g., Quantity * UnitPrice per order).
2. Group it by Customer and Month.
3. Compute average order value per customer per month.

9
https://www.linkedin.com/in/akash-rajan-bb3435288/
 Assumed Schema:

Orders(OrderID, CustomerID, OrderDate)

OrderDetails(OrderID, ProductID, Quantity, UnitPrice)
Customers(CustomerID, CustomerName, ...)

SQL Query:

SELECT
o.CustomerID,
DATE_FORMAT(o.OrderDate, '%Y-%m') AS
YearMonth,
AVG(od_total.OrderValue) AS AvgOrderValue
FROM
Orders o
JOIN (
SELECT
OrderID,
SUM(Quantity * UnitPrice) AS OrderValue
FROM
OrderDetails
GROUP BY
OrderID
) od_total ON o.OrderID = od_total.OrderID
GROUP BY
o.CustomerID, DATE_FORMAT(o.OrderDate, '%Y-%m')
ORDER BY
o.CustomerID, YearMonth;

Explanation:

• The subquery calculates total value for each order (Quantity * UnitPrice summed per
OrderID).
• DATE_FORMAT(o.OrderDate, '%Y-%m') extracts year and month (e.g., '2025-07').
• The outer query groups by Customer and Month, and takes the average of order values.

Notes:

• If you're using PostgreSQL, use TO_CHAR(OrderDate, 'YYYY-MM') instead of

DATE_FORMAT.
• If you need total value instead of average, replace AVG(...) with SUM(...).

10
https://www.linkedin.com/in/akash-rajan-bb3435288/
 20. Write a query to identify the customers who made a purchase every month for a past year.

SQL Query (MySQL style):

SELECT
o.CustomerID
FROM
Orders o
WHERE
o.OrderDate >= DATE_SUB(CURDATE(), INTERVAL 12
MONTH)
GROUP BY
o.CustomerID
HAVING
COUNT(DISTINCT DATE_FORMAT(o.OrderDate, '%Y-%m'))
= 12;

Explanation:

• DATE_SUB(CURDATE(), INTERVAL 12 MONTH): gets orders in the last 12 months.

• DATE_FORMAT(o.OrderDate, '%Y-%m'): extracts the year and month.
• COUNT(DISTINCT ...) = 12: ensures the customer made purchases in all 12 different
months.

21. Write a query to find the total revenue for each region in a given quarter.

To find the total revenue for each region in a given quarter, you need to:
1. Join orders with order details (to compute revenue).
2. Associate orders with customers (to get their region).
3. Filter orders by the desired quarter.
4. Group by region and sum the revenue.

Assumed Schema:

Orders(OrderID, CustomerID, OrderDate)

OrderDetails(OrderID, ProductID, Quantity, UnitPrice)
Customers(CustomerID, Region)

11
https://www.linkedin.com/in/akash-rajan-bb3435288/
 SQL Query (for a given quarter, e.g., Q1 = Jan–Mar 2025):

SELECT
c.Region,
SUM(od.Quantity * od.UnitPrice) AS TotalRevenue
FROM
Orders o
JOIN
OrderDetails od ON o.OrderID = od.OrderID
JOIN
Customers c ON o.CustomerID = c.CustomerID
WHERE
o.OrderDate >= '2025-01-01'
AND o.OrderDate < '2025-04-01' -- end of Q1
GROUP BY
c.Region
ORDER BY
TotalRevenue DESC;

Tips:

• Adjust the WHERE clause for any quarter:

o Q2: '2025-04-01' to '2025-07-01'
o Q3: '2025-07-01' to '2025-10-01'
o Q4: '2025-10-01' to '2026-01-01'
• You can also calculate the quarter dynamically using SQL functions if needed.

22. Write a query to find the first purchase date of each customer.

SELECT
CustomerID,
MIN(OrderDate) AS FirstPurchaseDate
FROM
Orders
GROUP BY
CustomerID
ORDER BY

12
https://www.linkedin.com/in/akash-rajan-bb3435288/
 FirstPurchaseDate;

Explanation:

• MIN(OrderDate) gives the first purchase date.

• GROUP BY CustomerID ensures we get one row per customer.
• ORDER BY FirstPurchaseDate is optional — it sorts customers by their first purchase.

23. Write a query to calculate the year-on-year growth of revenue.

To calculate year-on-year (YoY) growth of revenue, you need to:

1. Sum total revenue per year.

2. Use window functions or a self-join to compare each year’s revenue with the
previous year.
3. Calculate growth percentage using the formula:

YoY Growth = This Year Revenue − Last Year Revenue ×100

Last Year Revenue

SQL Query (Using Common Table Expression + Window Function)

WITH RevenuePerYear AS (

SELECT

YEAR(o.OrderDate) AS RevenueYear,

13
https://www.linkedin.com/in/akash-rajan-bb3435288/
 SUM(od.Quantity * od.UnitPrice) AS TotalRevenue

FROM

Orders o

JOIN

OrderDetails od ON o.OrderID = od.OrderID

GROUP BY

YEAR(o.OrderDate)

),

YoYGrowth AS (

SELECT

RevenueYear,

TotalRevenue,

LAG(TotalRevenue) OVER (ORDER BY RevenueYear)

AS LastYearRevenue

FROM

RevenuePerYear

SELECT

RevenueYear,

TotalRevenue,

LastYearRevenue,

ROUND(

(TotalRevenue - LastYearRevenue) / LastYearRevenue *

100, 2

) AS YoYGrowthPercent

14
https://www.linkedin.com/in/akash-rajan-bb3435288/
 FROM

YoYGrowth

WHERE

LastYearRevenue IS NOT NULL;

Explanation:

• LAG(...) OVER (ORDER BY RevenueYear) fetches the previous year’s revenue.

• ROUND(..., 2) gives a clean percentage with 2 decimal places.
• We exclude the first year in the result because there's no previous year to compare.

Example Output:

RevenueYear RevenueYear Last RevenueYear YoYGrowthPercent

2022 150000 120000 25.00
2023 180000 150000 20.00
2024 165000 180000 -8.33

24. Write a query to find the ‘Nth’ highest salary from the employee table (e.g., 5th
highest salary)

1. Using DISTINCT + ORDER BY + LIMIT + OFFSET (MySQL / PostgreSQL)

Get the 5th highest salary:

SELECT DISTINCT Salary

FROM Employee

ORDER BY Salary DESC

LIMIT 1 OFFSET 4; -- 4 means skip top 4, take the

5th

15
https://www.linkedin.com/in/akash-rajan-bb3435288/
 2. Using Subquery (Standard SQL – works in most RDBMS)

Get the Nth highest salary (e.g., 5th):

SELECT MIN(Salary) AS NthHighestSalary

FROM (

SELECT DISTINCT Salary

FROM Employee

ORDER BY Salary DESC

LIMIT 5

) AS TopN;

This will return the 5th highest distinct salary.

3. Using DENSE_RANK() (Best for handling ties)

WITH RankedSalaries AS (

SELECT

Salary,

DENSE_RANK() OVER (ORDER BY Salary DESC) AS

Rank

FROM Employee

SELECT Salary

FROM RankedSalaries

WHERE Rank = 5;

16
https://www.linkedin.com/in/akash-rajan-bb3435288/
 • This version is better if salaries have duplicates (e.g., 1st: ₹1L, 2nd: ₹90k, 2nd:
₹90k again, 4th: ₹80k, 5th: ₹75k).
• DENSE_RANK() gives same rank to duplicates but skips the next rank accordingly.

17
https://www.linkedin.com/in/akash-rajan-bb3435288/