100 Most Asked Real

Interview SQL Questions

Comprehensive guide covering technical concepts, query examples, and advanced techniques

 LinkedIn  Topmate

  
Technical SQL Query Examples Advanced Techniques
Questions 1-20 Questions 21-80 Questions 81-100

Pankaj Namekar – 6x Microsoft Certified | DM to Learn Power BI

 Part 1: Technical SQL Questions (1-5)

Q1 Q2
INNER JOIN vs LEFT JOIN Primary Key

 INNER JOIN: Only matching rows from both tables  Unique identifier for each row
 LEFT JOIN: All rows from left table + matching from right  No NULL or duplicate values

Q3 Q4
WHERE vs HAVING Normalization

 WHERE: Filters before aggregation  Process to structure tables

 HAVING: Filters after aggregation (with GROUP BY)  Reduces data redundancy
 Improves data integrity

Q5
Foreign Key

 Column that establishes relationship between two tables

 References primary key of another table
 Enforces referential integrity
 Part 1: Technical SQL Questions (6-10)

Q6 Q7
SQL View CTE (Common Table Expression)

 Virtual table based on a query  Temporary named result set

 Helps in abstraction and security  Defined using WITH clause
 Improves readability and enables recursion

Q8 Q9
DELETE vs TRUNCATE Index

 DELETE: Removes rows one by one, can use WHERE  Performance optimization structure
 TRUNCATE: Removes all rows, faster, resets identity  Speeds up data retrieval using pointers

Q10
UNION vs UNION ALL

 UNION: Removes duplicates, slower

 UNION ALL: Keeps duplicates, faster
 Part 1: Technical SQL Questions (11-15)

Q11 Q12
Aggregate Functions Subquery

 COUNT, SUM, AVG, MIN, MAX  Query nested inside another query
 Perform calculations on value sets  Used in WHERE, SELECT, FROM clauses

Q13 Q14
Clustered vs Non-Clustered Index Self Join

 Clustered: Data sorted physically (1 per table)  Join table with itself
 Non-Clustered: Separate structure with pointers  Compare rows within same table

Q15
ACID Property

A C I D
tomicity onsistency solation urability

 Ensures reliable transactions

 Part 1: Technical SQL Questions (16-20)

Q16 Q17
Stored Procedure CHAR vs VARCHAR

 Precompiled SQL block  CHAR: Fixed length

 Can be executed multiple times  VARCHAR: Variable length (saves space)
 Improves performance

Q18 Q19
DISTINCT vs GROUP BY Constraint

= Both remove duplicates  Rules applied to columns

 DISTINCT: Works on selected columns  NOT NULL, UNIQUE, CHECK, FOREIGN KEY
 GROUP BY: Aggregates data

Q20
EXISTS vs IN

 EXISTS: Stops after finding first match (faster for large subqueries)
 IN: Checks all values; good for small lists
 Part 2: SQL Query Questions (21-25)

Q21 Q22
Top 5 highest salaries Employees joined in last 6 months

SELECT TOP 5 * FROM Employee ORDER BY Salary DESC; SELECT * FROM Employee WHERE DATEDIFF(MONTH, JoinDate,
GETDATE()) <= 6;

Q23 Q24
Customers with no orders 2nd highest salary

SELECT [Link], [Link] FROM Customers c LEFT JOIN SELECT MAX(Salary) FROM Employee WHERE Salary < ( SELECT
Orders o ON [Link] = [Link] WHERE [Link] MAX(Salary) FROM Employee );
IS NULL;

Q25
Employee count by department

SELECT DeptID, COUNT(*) AS EmpCount FROM Employee GROUP BY DeptID;

 Part 2: SQL Query Questions (26-30)

Q26 Q27
Retrieve duplicate records Delete duplicate rows (keep one)

SELECT Name, COUNT(*) FROM Employee GROUP BY Name HAVING DELETE FROM Employee WHERE ID NOT IN ( SELECT MIN(ID) FROM
COUNT(*) > 1; Employee GROUP BY Name, Salary );

Q28 Q29
Employees with names starting with 'A' Departments with >10 employees

SELECT * FROM Employee WHERE Name LIKE 'A%'; SELECT DeptID, COUNT(*) AS Total FROM Employee GROUP BY
DeptID HAVING COUNT(*) > 10;

Q30
List employees with manager name

SELECT [Link] AS Employee, [Link] AS Manager FROM Employee e LEFT JOIN Employee m ON [Link] = [Link];
 Part 2: SQL Query Questions (31-35)

Q31 Q32
Customers with >3 orders Max salary by department

SELECT CustomerID, COUNT(OrderID) AS Orders FROM Orders SELECT DeptID, MAX(Salary) AS MaxSalary FROM Employee GROUP
GROUP BY CustomerID HAVING COUNT(OrderID) > 3; BY DeptID;

Q33 Q34
Employees with no department Avg salary by department

SELECT * FROM Employee WHERE DeptID IS NULL; SELECT DeptID, AVG(Salary) AS AvgSal FROM Employee GROUP BY
DeptID;

Q35
Employees with same salary

SELECT Salary, COUNT(*) FROM Employee GROUP BY Salary HAVING COUNT(*) > 1;
 Part 2: SQL Query Questions (36-40)

Q36 Q37
Last 3 hired employees Employees with salary 50K-80K

SELECT TOP 3 * FROM Employee ORDER BY JoinDate DESC; SELECT * FROM Employee WHERE Salary BETWEEN 50000 AND 80000;

Q38 Q39
Department names and total salary Employees with name length >5

SELECT [Link], SUM([Link]) AS TotalSalary FROM SELECT * FROM Employee WHERE LEN(Name) > 5;
Department d JOIN Employee e ON [Link] = [Link] GROUP BY
[Link];

Q40
Employees who joined in 2023

SELECT * FROM Employee WHERE YEAR(JoinDate) = 2023;

 Part 2: SQL Query Questions (41-45)

Q41 Q42
Employee count by year of joining Salary > department average

SELECT YEAR(JoinDate) AS YearJoined, COUNT(*) AS Total FROM SELECT e.* FROM Employee e JOIN ( SELECT DeptID, AVG(Salary)
Employee GROUP BY YEAR(JoinDate); AS AvgSal FROM Employee GROUP BY DeptID ) d ON [Link] =
[Link] WHERE [Link] > [Link];

Q43 Q44
Employee details with department Highest-paid employee per department

SELECT [Link], [Link], [Link] FROM Employee e JOIN SELECT * FROM Employee e WHERE Salary = ( SELECT MAX(Salary)
Department d ON [Link] = [Link]; FROM Employee WHERE DeptID = [Link] );

Q45
3rd highest salary using DENSE_RANK

SELECT * FROM ( SELECT Name, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS rnk FROM Employee ) t WHERE rnk = 3;
 Part 2: SQL Query Questions (46-50)

Q46 Q47
Running total of salary Employees hired before manager

SELECT Name, Salary, SUM(Salary) OVER (ORDER BY EmpID) AS SELECT [Link] FROM Employee e JOIN Employee m ON [Link]
RunningTotal FROM Employee; = [Link] WHERE [Link] < [Link];

Q48 Q49
Employees not in dept 1 and 2 Departments with no employees

SELECT * FROM Employee WHERE DeptID NOT IN (1,2); SELECT [Link] FROM Department d LEFT JOIN Employee e ON
[Link] = [Link] WHERE [Link] IS NULL;

Q50
Employee count per manager

SELECT ManagerID, COUNT(*) AS EmpCount FROM Employee GROUP BY ManagerID;

 Part 2: SQL Query Questions (51-55)

Q51 Q52
Salary difference using LAG Duplicate email IDs

SELECT Name, Salary, Salary - LAG(Salary) OVER (ORDER BY SELECT Email, COUNT(*) FROM Users GROUP BY Email HAVING
EmpID) AS Diff FROM Employee; COUNT(*) > 1;

Q53 Q54
First and last employee by date Top 2 earners per department

SELECT TOP 1 * FROM Employee ORDER BY JoinDate ASC; SELECT SELECT * FROM ( SELECT Name, DeptID, Salary, DENSE_RANK()
TOP 1 * FROM Employee ORDER BY JoinDate DESC; OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS rnk FROM
Employee ) t WHERE rnk <= 2;

Q55
Salary > department average (correlated subquery)

SELECT Name FROM Employee e WHERE Salary > ( SELECT AVG(Salary) FROM Employee WHERE DeptID = [Link] );
 Part 2: SQL Query Questions (56-60)

Q56 Q57
Total sales by month Orders in last 7 days

SELECT MONTH(SaleDate) AS Month, SUM(Amount) AS Total FROM SELECT * FROM Orders WHERE OrderDate >= DATEADD(DAY, -7,
Sales GROUP BY MONTH(SaleDate); GETDATE());

Q58 Q59
Employees with multiple projects Projects with no employees

SELECT EmpID FROM EmpProject GROUP BY EmpID HAVING SELECT * FROM Project p LEFT JOIN EmpProject ep ON
COUNT(ProjectID) > 1; [Link] = [Link] WHERE [Link] IS NULL;

Q60
Employees with department total salary (window function)

SELECT Name, DeptID, Salary, SUM(Salary) OVER (PARTITION BY DeptID) AS TotalDeptSalary FROM Employee;
 Part 2: SQL Query Questions (61-65)

Q61 Q62
Customers ordering every month in 2024 Employees in top 10% salary

SELECT CustomerID FROM Orders WHERE YEAR(OrderDate)=2024 SELECT * FROM ( SELECT *, NTILE(10) OVER (ORDER BY Salary
GROUP BY CustomerID HAVING COUNT(DISTINCT DESC) AS tile FROM Employee ) t WHERE tile = 1;
MONTH(OrderDate))=12;

Q63 Q64
Total orders and revenue per customer Employees joined on same day

SELECT CustomerID, COUNT(*) AS TotalOrders, SUM(Amount) AS SELECT JoinDate, COUNT(*) FROM Employee GROUP BY JoinDate
TotalAmount FROM Orders GROUP BY CustomerID; HAVING COUNT(*) > 1;

Q65
Top 3 customers by purchase amount

SELECT TOP 3 CustomerID, SUM(Amount) AS Total FROM Orders GROUP BY CustomerID ORDER BY Total DESC;
 Part 2: SQL Query Questions (66-70)

Q66 Q67
Duplicate employee names Employees earning > manager

SELECT Name FROM Employee GROUP BY Name HAVING COUNT(*) > 1; SELECT [Link] FROM Employee e JOIN Employee m ON [Link]
= [Link] WHERE [Link] > [Link];

Q68 Q69
Depts with all employees >50K Employees joined same month as manager

SELECT DeptID FROM Employee GROUP BY DeptID HAVING SELECT [Link] FROM Employee e JOIN Employee m ON [Link]
MIN(Salary) > 50000; = [Link] WHERE MONTH([Link])=MONTH([Link]);

Q70
Employees not assigned to any project

SELECT * FROM Employee WHERE EmpID NOT IN (SELECT EmpID FROM EmpProject);
 Part 2: SQL Query Questions (71-75)

Q71 Q72
Daily orders with running total Depts with > average employee count

SELECT OrderDate, COUNT(*) AS Orders, SUM(COUNT(*)) OVER SELECT DeptID FROM Employee GROUP BY DeptID HAVING COUNT(*)
(ORDER BY OrderDate) AS RunningTotal FROM Orders GROUP BY > ( SELECT AVG(Cnt) FROM ( SELECT COUNT(*) AS Cnt FROM
OrderDate; Employee GROUP BY DeptID ) t );

Q73 Q74
Top salary employees Employee count by gender and dept

SELECT * FROM Employee WHERE Salary = ( SELECT MAX(Salary) SELECT DeptID, Gender, COUNT(*) AS Total FROM Employee GROUP
FROM Employee ); BY DeptID, Gender;

Q75
Months with no sales

SELECT [Link] FROM Months m LEFT JOIN Sales s ON [Link] = MONTH([Link]) WHERE [Link] IS NULL;
 Part 2: SQL Query Questions (76-80)

Q76 Q77
Customers ordering every quarter Salary range by department

SELECT CustomerID FROM Orders GROUP BY CustomerID HAVING SELECT DeptID, MAX(Salary) AS MaxSal, MIN(Salary) AS MinSal
COUNT(DISTINCT DATEPART(QUARTER, OrderDate)) = 4; FROM Employee GROUP BY DeptID;

Q78 Q79
Managers with team size First employee by department

SELECT ManagerID, COUNT(*) AS TeamSize FROM Employee GROUP SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY
BY ManagerID; DeptID ORDER BY JoinDate ASC) AS rn FROM Employee ) t WHERE
rn=1;

Q80
Customers above average spending

SELECT CustomerID, SUM(Amount) AS Total FROM Orders GROUP BY CustomerID HAVING SUM(Amount) > ( SELECT AVG(TotalAmt) FROM ( SELECT
SUM(Amount) AS TotalAmt FROM Orders GROUP BY CustomerID ) t );
 Part 3: Advanced SQL Queries (81-85)

Q81 Q82
Continuous absent days using LAG Cumulative revenue by month and region

SELECT EmpID, Date, CASE WHEN DATEADD(DAY,-1,Date)=LAG(Date) SELECT Region, Month, SUM(Revenue) OVER (PARTITION BY Region
OVER (PARTITION BY EmpID ORDER BY Date) THEN 'Continuous' ORDER BY Month) AS CumRevenue FROM Sales;
ELSE 'Break' END AS Status FROM Attendance;
 Window function calculates running totals per region
 LAG compares current date with previous date

Q83 Q84
Rank customers by revenue in each region Pivot department-wise employee count

SELECT Region, CustomerID, SUM(Amount) AS Total, RANK() OVER SELECT * FROM ( SELECT DeptName, Gender FROM Employee e JOIN
(PARTITION BY Region ORDER BY SUM(Amount) DESC) AS Department d ON [Link]=[Link] ) src PIVOT (COUNT(Gender)
RankByRegion FROM Orders GROUP BY Region, CustomerID; FOR Gender IN ([Male],[Female])) p;

 RANK() assigns position within each region group  Transforms row data into columnar format

Q85
Employees promoted more than once

SELECT EmpID FROM Promotions GROUP BY EmpID HAVING COUNT(*) > 1;

 Simple grouping to identify multiple occurrences

 Part 3: Advanced SQL Queries (86-90)

Q86 Q87
Identify gaps in employee IDs Latest order per customer using ROW_NUMBER

SELECT [Link]+1 AS MissingID FROM Employee a LEFT JOIN SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY
Employee b ON [Link]+1=[Link] WHERE [Link] IS NULL; CustomerID ORDER BY OrderDate DESC) AS rn FROM Orders ) t
WHERE rn=1;
 Self-join finds missing sequential numbers
 Window function ranks orders by date within each customer

Q88 Q89
Employees changing departments Salary stats using ROLLUP

SELECT EmpID FROM EmployeeHistory GROUP BY EmpID HAVING SELECT DeptID, SUM(Salary) AS TotalSal, MIN(Salary) AS
COUNT(DISTINCT DeptID) > 1; MinSal, MAX(Salary) AS MaxSal, AVG(Salary) AS AvgSal FROM
Employee GROUP BY ROLLUP (DeptID);
 Groups by employee and counts distinct departments
 ROLLUP adds subtotals and grand total row

Q90
Department and employee count using CUBE

SELECT DeptID, Gender, COUNT(*) AS EmpCount FROM Employee GROUP BY CUBE (DeptID, Gender);

 CUBE generates all combinations of grouping sets

 Part 3: Advanced SQL Queries (91-95)

Q91 Q92
Customers with increasing order amounts Top N rows per group

SELECT CustomerID, OrderID, Amount, LAG(Amount) OVER SELECT * FROM ( SELECT Name, DeptID, Salary, ROW_NUMBER()
(PARTITION BY CustomerID ORDER BY OrderDate) AS PrevAmount OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS rn FROM
FROM Orders WHERE Amount > LAG(Amount) OVER (PARTITION BY Employee ) t WHERE rn <= 2;
CustomerID ORDER BY OrderDate);
 ROW_NUMBER() helps pick top-N results within each partition
 LAG compares current order amount with previous order

Q93 Q94
Months with missing sales data 2nd highest salary per department

SELECT [Link] FROM Calendar m LEFT JOIN (SELECT DISTINCT SELECT * FROM ( SELECT DeptID, Name, Salary, DENSE_RANK()
MONTH(SaleDate) AS MonthNo FROM Sales) s ON [Link] = OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS rnk FROM
[Link] WHERE [Link] IS NULL; Employee ) t WHERE rnk = 2;

 Compares full month list with actual sales months to find gaps  DENSE_RANK() per department gets 2nd top salary per group

Q95
Average time between orders per customer

SELECT CustomerID, AVG(DATEDIFF(DAY, LAG(OrderDate) OVER (PARTITION BY CustomerID ORDER BY OrderDate), OrderDate)) AS AvgGapDays
FROM Orders GROUP BY CustomerID;

 Calculates average gap in days between consecutive orders using LAG()

 Part 3: Advanced SQL Queries (96-100)

Q96 Q97
Overlapping project periods Orders in consecutive months

SELECT [Link], [Link], [Link] FROM Projects a SELECT DISTINCT [Link] FROM Orders a JOIN Orders b ON
JOIN Projects b ON [Link] = [Link] AND [Link] <> [Link]=[Link] AND DATEDIFF(MONTH, [Link],
[Link] AND [Link] < [Link] AND [Link] > [Link])=1;
[Link];
 Matches orders of same customer placed in consecutive months
 Checks if project timelines overlap for same employee

Q98 Q99
Year-over-year sales growth Top 20% revenue customers (Pareto)

SELECT YEAR(OrderDate) AS Year, SUM(Amount) AS TotalSales, WITH cte AS ( SELECT CustomerID, SUM(Amount) AS Total,
SUM(Amount) - LAG(SUM(Amount)) OVER (ORDER BY SUM(SUM(Amount)) OVER () AS GrandTotal, SUM(SUM(Amount))
YEAR(OrderDate)) AS Growth FROM Orders GROUP BY OVER (ORDER BY SUM(Amount) DESC) AS RunningTotal FROM Orders
YEAR(OrderDate); GROUP BY CustomerID ) SELECT * FROM cte WHERE RunningTotal

 LAG compares this year's total sales vs previous year % Identifies top contributors using cumulative sums (Pareto principle)

Q100
Salary jump anomalies (>50%)

SELECT EmpID, RevisionDate, Salary, LAG(Salary) OVER (PARTITION BY EmpID ORDER BY RevisionDate) AS PrevSal FROM SalaryHistory WHERE
Salary > 1.5 * LAG(Salary) OVER (PARTITION BY EmpID ORDER BY RevisionDate);

 Compares salary revisions to detect abnormal increments for audits