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