Structured Query Language (SQL), is the backbone of the most relational database .You can process any complex dataset. Here we will explore SQL queries with examples for beginners to advance. If you will go through this, it will be very helpful for you to crack any job interview and grab a high paying job.
73 Important SQL Queries with Examples for Interview Preparation in 2025
Let’s now explore the most common SQL queries with examples for for Beginners and Experienced Professionals as given below:-
-
1. Extract all record from the table
SELECT *
FROM employees;
2. Create New Column
ALTER TABLE table_name
ADD COLUMN column_name data_type;
3. Remove all records from a table but keep the table structure
TRUNCATE TABLE table_name;
4. Convert the String in lower case
SELECT LOWER(‘HELLO WORLD’) AS lower_case_string;
5. Find the record where column has null( Column name is missing)
SELECT *
FROM table_name
WHERE column_name IS NULL;
6. Total sales per customer from sales table
SELECT customer_id, SUM(sale_amount) AS total_sales
FROM sales
GROUP BY customer_id;
7. How to check table is empty
SELECT CASE
WHEN EXISTS (SELECT 1 FROM employees) THEN ‘Not Empty’
ELSE ‘Empty’
END AS table_status;
8. Select second highest salaried employee
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
9. To find the N-th highest salary in SQL, you can use the DENSE_RANK() or ROW_NUMBER() window functions.
WITH RankedSalaries AS (
SELECT
Salary,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees
)
SELECT Salary
FROM RankedSalaries
WHERE Rank = N;
10. To find employees whose salary is greater than the average salary, you can use the following query:
SELECT EmployeeID, Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
11. Write a query to display current date and time
SELECT GETDATE() AS CurrentDateTime;
SELECT CURRENT_TIMESTAMP;
12. Find duplicate record in table
SELECT
Name,
Department,
COUNT(*) AS DuplicateCount
FROM
Employees
GROUP BY
Name, Department
HAVING
COUNT(*) > 1;
13. Delete duplicate records from table
WITH CTE AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY Column1, Column2, …, ColumnN ORDER BY ID) AS RowNum
FROM TableName
)
DELETE FROM TableName
WHERE ID IN (
SELECT ID
FROM CTE
WHERE RowNum > 1
);
14. Another way by Self join
DELETE t1
FROM TableName t1
JOIN TableName t2
ON
t1.Column1 = t2.Column1 AND
t1.Column2 = t2.Column2 AND
t1.ID > t2.ID;
15. Extract common records from table
SELECT *
FROM Table1
INTERSECT
SELECT *
FROM Table2;
16. Another way
SELECT *
FROM Table1 a
WHERE EXISTS (
SELECT 1
FROM Table2 b
WHERE a.Column1 = b.Column1 AND a.Column2 = b.Column2
);
17. Another way
SELECT a.*
FROM Table1 a
INNER JOIN Table2 b
ON a.ID = b.ID AND a.Name = b.Name AND a.Age = b.Age;
18. Extract the last 10 records from the table .
SELECT *
FROM TableName
ORDER BY ID DESC
LIMIT 10;
19. Fetches the top 5 products with the highest sales.
SELECT TOP 5 ProductName, SUM(SalesAmount) AS TotalSales
FROM SalesTable
GROUP BY ProductName
ORDER BY TotalSales DESC;
20. Calculate total salary of all employees
SELECT SUM(Salary) AS TotalSalary
FROM Employees;
21. Find employee who joined in year 2022
SELECT *
FROM Employees
WHERE YEAR(JoinDate) = 2022;
22. Find employees whose name start with A
SELECT *
FROM Employees
WHERE Name LIKE ‘A%’;
“OR”
SELECT *
FROM Employees
WHERE Name LIKE ‘A%’;
23. Find employee who don’t have manager
SELECT EmployeeID, Name
FROM Employees
WHERE ManagerID IS NULL;
24. Find the product that haven’t been sold, the query could look like this:
Assuming you have a Products table and a Sales table
SELECT p.ProductName
FROM Products p
LEFT JOIN Sales s ON p.ProductID = s.ProductID
WHERE s.ProductID IS NULL;
25. Find department with highest number of employee
SELECT DepartmentName, COUNT(EmployeeID) AS EmployeeCount
FROM Employees
GROUP BY DepartmentName
ORDER BY EmployeeCount DESC
LIMIT 1;
26. Find the product category with the highest sales in each category.
SELECT p.Category, SUM(s.SalesAmount) AS TotalSales
FROM Products p
JOIN Sales s ON p.ProductID = s.ProductID
GROUP BY p.Category
ORDER BY TotalSales DESC
LIMIT 1;
27. Count of employees in each department
SELECT DepartmentName, COUNT(EmployeeID) AS EmployeeCount
FROM Employees
GROUP BY DepartmentName;
28. Find employee with highest salary in each department
SELECT e.EmployeeID, e.Name, e.DepartmentID, e.Salary
FROM Employees e
WHERE e.Salary = (
SELECT MAX(Salary)
FROM Employees
WHERE DepartmentID = e.DepartmentID
);
29. Update the salary of all employees by increasing it by 10%,
UPDATE Employees
SET Salary = Salary * 1.10;
30. Find employees whose salary is between 50,000 and 100,000,
SELECT EmployeeID, Name, Salary
FROM Employees
WHERE Salary BETWEEN 50000 AND 100000;
31. Find youngest employee
SELECT *
FROM Employees
ORDER BY BirthDate DESC
LIMIT 1;
32. Fetch the first and last record from the employee table using UNION ALL:
SELECT * FROM employee
ORDER BY emp_id ASC
LIMIT 1
33. UNION ALL
SELECT * FROM employee
ORDER BY emp_id DESC
LIMIT 1;
34. Find employee whose manager id is 141
SELECT *
FROM employee
WHERE manager_id = 141;
35. Find total number of department in the company
SELECT COUNT(DISTINCT department_id) AS total_departments
FROM employees;
36. Employee who joined in last 2 years
SELECT *
FROM employees
WHERE hire_date >= DATEADD(YEAR, -2, GETDATE());
37. Find department with lowest average salary
SELECT TOP 1 department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
ORDER BY average_salary ASC;
38. Display customers who have been engaged with the company for the last 5 years
SELECT *
FROM customers
WHERE registration_date <= DATEADD(YEAR, -5, GETDATE());
39. Display the second most sold product
WITH ProductSales AS (
SELECT product_id, SUM(quantity_sold) AS total_sold
FROM sales
GROUP BY product_id
)
SELECT TOP 1 product_id, total_sold
FROM ProductSales
ORDER BY total_sold DESC
OFFSET 1 ROWS;
Also Read: 10 Most Asked SQL Interview Questions With Answers
40. Get all the employee in XML format
SELECT *
FROM employees
FOR XML AUTO;
41. Employees who do not have any subordinates in sales department
SELECT e.emp_id, e.name
FROM employees e
WHERE e.department = ‘Sales’
AND e.emp_id NOT IN (
SELECT DISTINCT manager_id
FROM employees
WHERE manager_id IS NOT NULL
AND department = ‘Sales’
);
42. Find employees having the same salary
SELECT *
FROM employees
WHERE salary IN (
SELECT salary
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1
);
43. Update salary as per their department like for department 101, 10% , department 102, 15 %
UPDATE employees
SET salary = CASE
WHEN department_id = 101 THEN salary * 1.10
WHEN department_id = 102 THEN salary * 1.05
ELSE salary
END;
44. Find employees who hired in last 6 months
SELECT *
FROM employees
WHERE hire_date >= DATEADD(MONTH, -6, GETDATE());
45. Find department wise total and average salary
SELECT department_id,
SUM(salary) AS total_salary,
AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;
46. Find employees who joined the company in the same month and year as their manager
SELECT e.employee_id AS employee_id,
e.name AS employee_name,
e.hire_date AS employee_hire_date,
m.employee_id AS manager_id,
m.name AS manager_name,
m.hire_date AS manager_hire_date
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id
AND YEAR(e.hire_date) = YEAR(m.hire_date)
AND MONTH(e.hire_date) = MONTH(m.hire_date);
47. Find employees whose salary is higher than their manager’s salary
SELECT e.employee_id AS employee_id,
e.name AS employee_name,
e.salary AS employee_salary,
m.employee_id AS manager_id,
m.name AS manager_name,
m.salary AS manager_salary
FROM employees e
JOIN employees m
ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;
48. Find employee who work under the same manager(managerID =12)
SELECT *
FROM employee
WHERE managerID = 12;
49. Find employees who work under the same manager, but without specifying the manager ID,
SELECT e1.name AS employee_name,
e1.managerID AS manager_id
FROM employee e1
WHERE e1.managerID IS NOT NULL
ORDER BY e1.managerID;
50. Find employee with more than 5 years experience in each department
SELECT department_id,
employee_id,
name,
DATEDIFF(CURDATE(), hire_date) / 365 AS years_of_service
FROM employees
WHERE DATEDIFF(CURDATE(), hire_date) / 365 > 5;
51. Calculate Running total for each day
SELECT SaleDate, Amount,
SUM(Amount) OVER (ORDER BY SaleDate) AS RunningTotal
FROM Sales;
52. Find customers who did not purchase from last year
SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
SELECT DISTINCT customer_id
FROM sales
WHERE sale_date >= CURDATE() – INTERVAL 1 YEAR
);
53. Find the best selling product in each department or the top performing product in each department.
SELECT region, product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region, product_id
HAVING total_sales = (
SELECT MAX(SUM(sales_amount))
FROM sales s2
WHERE s2.region = sales.region
GROUP BY s2.product_id
);
54. Find employee who exceeded their sales target
SELECT e.employee_id, e.name, e.sales_target, SUM(s.sales_amount) AS total_sales
FROM employees e
JOIN sales s ON e.employee_id = s.employee_id
GROUP BY e.employee_id, e.name, e.sales_target
HAVING total_sales > e.sales_target;
55. Check out products with low inventory or List the product with low stock level .
SELECT product_id, product_name, inventory_quantity
FROM products
WHERE inventory_quantity < 10;
56. Monitor the daily website traffic or count daily visitor count for the website.
SELECT DATE(visit_time) AS visit_date, COUNT(*) AS total_visits
FROM website_traffic
GROUP BY DATE(visit_time)
ORDER BY visit_date DESC;
57. Find customers who purchased product of Electronic category
SELECT c.customer_id, c.customer_name
FROM customers c
JOIN sales s ON c.customer_id = s.customer_id
WHERE s.product_id IN (SELECT product_id FROM products WHERE category = ‘Electronics’);
58. Calculate average delivery time for orders
SELECT AVG(DATEDIFF(delivery_date, order_date)) AS avg_delivery_time
FROM orders
WHERE delivery_date IS NOT NULL;
59. Find most frequently ordered product or product with highest number of orders
SELECT product_id, COUNT(*) AS order_count
FROM sales
GROUP BY product_id
ORDER BY order_count DESC
LIMIT 1;
60. Detect customers who file multiple complaints
SELECT customer_id, COUNT(*) AS complaint_count
FROM complaints
GROUP BY customer_id
HAVING complaint_count > 1;
61. Find employees who are close to retierment
SELECT employee_id, name, birth_date, (YEAR(CURDATE()) – YEAR(birth_date)) AS age
FROM employees
WHERE (YEAR(CURDATE()) – YEAR(birth_date)) >= 58;
62. Find products which are still pending for delivery or not delivered yet .
SELECT order_id, customer_id, order_date
FROM orders
WHERE delivery_date IS NULL;
63. Analyse sales performance by quarter
SELECT QUARTER(sale_date) AS quarter, YEAR(sale_date) AS year, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY YEAR(sale_date), QUARTER(sale_date)
ORDER BY year, quarter;
64. Identify overlapping Booking in a hotel or detect the conflict room booking for the same period
SELECT b1.booking_id, b2.booking_id, b1.room_id, b1.check_in, b1.check_out
FROM bookings b1
JOIN bookings b2 ON b1.room_id = b2.room_id
WHERE b1.booking_id <> b2.booking_id
AND b1.check_in < b2.check_out
AND b1.check_out > b2.check_in;
65. Find the most common issue in support tickets or most frequently reported issue
SELECT issue_type, COUNT(*) AS occurrence
FROM support_tickets
GROUP BY issue_type
ORDER BY occurrence DESC
LIMIT 1;
66. Find average purchase value per customer
SELECT customer_id, AVG(sales_amount) AS avg_purchase_value
FROM sales
GROUP BY customer_id;
67. Identify Which products have experienced a more than 50% drop in sales in the last 30 days compared to the previous period?
SELECT product_id, SUM(CASE WHEN sale_date >= CURDATE() – INTERVAL 30 DAY THEN sales_amount ELSE 0 END) AS last_30_days,
SUM(CASE WHEN sale_date < CURDATE() – INTERVAL 30 DAY THEN sales_amount ELSE 0 END) AS previous_period
FROM sales
GROUP BY product_id
HAVING last_30_days < previous_period / 2;
68. Find employee who work in multiple roles
SELECT employee_id, COUNT(DISTINCT role_id) AS role_count
FROM employee_roles
GROUP BY employee_id
HAVING role_count > 1;
69. Calculate employee attrition rate
SELECT YEAR(resignation_date) AS year,
COUNT(*) * 100.0 / (SELECT COUNT(*) FROM employees) AS attrition_rate
FROM employees
WHERE resignation_date IS NOT NULL
GROUP BY YEAR(resignation_date);
70. Find top product pair commonly purchased together
SELECT product_id_1, product_id_2, COUNT(*) AS pair_count
FROM (
SELECT a.product_id AS product_id_1, b.product_id AS product_id_2
FROM sales a
JOIN sales b ON a.order_id = b.order_id AND a.product_id < b.product_id
) AS product_pairs
GROUP BY product_id_1, product_id_2
ORDER BY pair_count DESC
LIMIT 5;
71. Update table with value 0 to 1 and 1 to 0
UPDATE employee
SET column_name = CASE
WHEN column_name = 0 THEN 1
WHEN column_name = 1 THEN 0
END;
72. Find users who have logged in for exactly 3 consecutive days
WITH LoginSequence AS (
SELECT
UserID,
LoginDate,
ROW_NUMBER() OVER (PARTITION BY UserID ORDER BY LoginDate) AS LoginRow
FROM UserLogins
),
ConsecutiveLogins AS (
SELECT
UserID,
MIN(LoginDate) AS StartDate,
MAX(LoginDate) AS EndDate,
COUNT(*) AS ConsecutiveDays
FROM LoginSequence
GROUP BY UserID, (DATEDIFF(DAY, LoginDate, LoginRow))
)
SELECT
UserID,
StartDate,
EndDate
FROM ConsecutiveLogins
WHERE ConsecutiveDays = 3;
73. Find those employees whose total sales are greater than 120% of the department’s average total sales.
WITH DepartmentSales AS (
SELECT
DepartmentID,
AVG(TotalSales) AS AvgSales
FROM (
SELECT
e.DepartmentID,
e.EmployeeID,
SUM(s.Revenue) AS TotalSales
FROM Employees e
JOIN Sales s ON e.EmployeeID = s.SalespersonID
GROUP BY e.DepartmentID, e.EmployeeID
) EmployeeSales
GROUP BY DepartmentID
),
EmployeeSales AS (
SELECT
e.EmployeeID,
e.Name,
e.DepartmentID,
SUM(s.Revenue) AS TotalSales
FROM Employees e
JOIN Sales s ON e.EmployeeID = s.SalespersonID
GROUP BY e.EmployeeID, e.Name, e.DepartmentID
)
SELECT
es.EmployeeID,
es.Name,
es.TotalSales,
ds.AvgSales,
(ds.AvgSales * 1.2) AS Threshold
FROM EmployeeSales es
JOIN DepartmentSales ds ON es.DepartmentID = ds.DepartmentID
WHERE es.TotalSales > ds.AvgSales * 1.2;
Conclusion
You can learn SQL by enrolling in Data Analysis or Data science which is high in demand in today’s world. As everything is completely digital now. Every organisation relies on data now. You can learn data analysis by enrolling in console flare where you will work on a real dataset.
And you will be unbeatable as compared to other candidates.
For more such content and regular updates, follow us on Facebook, Instagram, LinkedIn
