Top 100 Most Asked SQL Queries in Interviews

1. Select all columns from a table

SELECT * FROM table_name;

2. Select specific columns

SELECT column1, column2 FROM table_name;

3. WHERE clause

SELECT * FROM table_name WHERE condition;

4. AND, OR, NOT

SELECT * FROM table_name WHERE condition1 AND condition2;

5. ORDER BY

SELECT * FROM table_name ORDER BY column1 ASC, column2 DESC;

6. GROUP BY

SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

7. HAVING clause

SELECT column1, COUNT(*) FROM table_name GROUP BY column1 HAVING COUNT(*) > 1;

8. LIKE operator

SELECT * FROM table_name WHERE column LIKE 'A%';

9. BETWEEN operator

SELECT * FROM table_name WHERE column BETWEEN value1 AND value2;

10. IN operator

SELECT * FROM table_name WHERE column IN (value1, value2);

11. IS NULL / IS NOT NULL

SELECT * FROM table_name WHERE column IS NULL;

SELECT a.column1, b.column2 FROM table1 a INNER JOIN table2 b ON a.id = b.id;

13. LEFT JOIN

SELECT a.column1, b.column2 FROM table1 a LEFT JOIN table2 b ON a.id = b.id;

14. RIGHT JOIN

SELECT a.column1, b.column2 FROM table1 a RIGHT JOIN table2 b ON a.id = b.id;

15. FULL JOIN

SELECT a.column1, b.column2 FROM table1 a FULL OUTER JOIN table2 b ON a.id = b.id;

16. UNION

SELECT column FROM table1 UNION SELECT column FROM table2;

17. INSERT INTO

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

18. UPDATE

UPDATE table_name SET column1 = value1 WHERE condition;

19. DELETE

DELETE FROM table_name WHERE condition;

20. CREATE TABLE

CREATE TABLE table_name (column1 datatype, column2 datatype);

21. ALTER TABLE

ALTER TABLE table_name ADD column_name datatype;

22. DROP TABLE

DROP TABLE table_name;

23. TRUNCATE TABLE

TRUNCATE TABLE table_name;

SELECT DISTINCT column FROM table_name;

25. LIMIT

SELECT * FROM table_name LIMIT 10;

26. Find second highest salary

SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM

employees);

27. Find duplicate records

SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING

COUNT(*) > 1;

28. Delete duplicate records

DELETE FROM table_name WHERE id NOT IN (SELECT MIN(id) FROM table_name GROUP BY

column_name);

29. Subquery in SELECT

SELECT name, (SELECT MAX(salary) FROM employees) AS max_salary FROM employees;

30. Subquery in FROM

SELECT AVG(salary) FROM (SELECT salary FROM employees WHERE department = 'IT') AS

it_salaries;

31. Correlated subquery

SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees

WHERE department = e.department);

32. CASE statement

SELECT name, salary, CASE WHEN salary > 50000 THEN 'High' ELSE 'Low' END AS salary_level

FROM employees;
33. COALESCE function

SELECT name, COALESCE(phone, 'Not Provided') FROM employees;

34. NVL function

SELECT name, NVL(commission, 0) FROM employees;

35. DECODE function

SELECT name, DECODE(status, 'A', 'Active', 'I', 'Inactive') FROM users;

36. EXISTS operator

SELECT name FROM employees e WHERE EXISTS (SELECT 1 FROM departments d WHERE

e.dept_id = d.id);

37. NOT EXISTS

SELECT name FROM employees e WHERE NOT EXISTS (SELECT 1 FROM departments d

WHERE e.dept_id = d.id);

38. ANY operator

SELECT name FROM employees WHERE salary > ANY (SELECT salary FROM employees

WHERE department = 'HR');

39. ALL operator

SELECT name FROM employees WHERE salary > ALL (SELECT salary FROM employees

WHERE department = 'HR');

40. Rank function

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) FROM employees;

41. Dense_Rank function

SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) FROM employees;

42. Row_Number function

SELECT name, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC)

43. CTE (WITH clause)

WITH DepartmentSalaries AS (SELECT department, AVG(salary) avg_sal FROM employees

GROUP BY department) SELECT * FROM DepartmentSalaries;

44. Self Join

SELECT A.name, B.name FROM employees A JOIN employees B ON A.manager_id = B.id;

45. Pivot table

SELECT * FROM (SELECT department, gender, salary FROM employees) PIVOT (SUM(salary)

FOR gender IN ('M' AS Male, 'F' AS Female));

46. Unpivot table

SELECT * FROM (SELECT name, math, science FROM students) UNPIVOT (marks FOR subject

IN (math AS 'Math', science AS 'Science'));

47. Update using Join

UPDATE employees SET department = d.name FROM departments d WHERE employees.dept_id

= d.id;

48. Delete with Join

DELETE e FROM employees e INNER JOIN departments d ON e.dept_id = d.id WHERE d.name =

'HR';

49. Select Nth highest salary

SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk

FROM employees) WHERE rnk = 3;

50. Fetch top 3 records by each group

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY

salary DESC) AS rn FROM employees) WHERE rn <= 3;

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

52. Extract year from date

SELECT name, EXTRACT(YEAR FROM join_date) AS join_year FROM employees;

53. Get current date and time

SELECT CURRENT_TIMESTAMP;

54. Find difference between two dates

SELECT DATEDIFF(end_date, start_date) AS duration FROM projects;

55. Add days to a date

SELECT DATE_ADD(start_date, INTERVAL 7 DAY) FROM projects;

56. String functions - LENGTH

SELECT name, LENGTH(name) AS name_length FROM employees;

57. String functions - SUBSTRING

SELECT SUBSTRING(name, 1, 3) FROM employees;

58. Convert to upper case

SELECT UPPER(name) FROM employees;

59. Convert to lower case

SELECT LOWER(name) FROM employees;

60. Trim whitespaces

SELECT TRIM(name) FROM employees;

61. Replace characters

SELECT REPLACE(name, 'a', 'A') FROM employees;

62. Use of CHARINDEX

SELECT CHARINDEX('a', name) FROM employees;

SELECT CAST(salary AS VARCHAR) FROM employees;

64. Union All

SELECT column FROM table1 UNION ALL SELECT column FROM table2;

65. Difference between UNION and UNION ALL

-- UNION removes duplicates; UNION ALL retains all records

66. Create View

CREATE VIEW high_salary AS SELECT * FROM employees WHERE salary > 50000;

67. Update View

CREATE OR REPLACE VIEW high_salary AS SELECT * FROM employees WHERE salary >

60000;

68. Drop View

DROP VIEW high_salary;

69. Index creation

CREATE INDEX idx_name ON employees(name);

70. Drop Index

DROP INDEX idx_name;

71. Composite Primary Key

CREATE TABLE orders (order_id INT, product_id INT, PRIMARY KEY(order_id, product_id));

72. Foreign Key Constraint

ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (cust_id) REFERENCES

customers(id);

73. Check Constraint

CREATE TABLE employees (id INT, age INT CHECK (age >= 18));
74. Unique Constraint

ALTER TABLE employees ADD CONSTRAINT unique_email UNIQUE(email);

75. Default Constraint

CREATE TABLE products (id INT, stock INT DEFAULT 100);

76. Stored Procedure

CREATE PROCEDURE GetEmployees AS BEGIN SELECT * FROM employees; END;

77. Execute Stored Procedure

EXEC GetEmployees;

78. Function

CREATE FUNCTION GetSalary(@id INT) RETURNS INT AS BEGIN RETURN (SELECT salary

FROM employees WHERE id = @id); END;

79. Trigger Example

CREATE TRIGGER trg_update ON employees AFTER UPDATE AS BEGIN PRINT 'Employee

Updated'; END;

80. Transactions

BEGIN TRANSACTION; UPDATE employees SET salary = 60000 WHERE id = 1; COMMIT;

81. Rollback Example

BEGIN TRANSACTION; DELETE FROM employees WHERE id = 2; ROLLBACK;

82. Savepoint Usage

SAVEPOINT sp1; DELETE FROM employees WHERE id = 3; ROLLBACK TO sp1;

83. Deadlock

-- When two transactions block each other waiting for a resource

84. Normalization

-- Process to reduce data redundancy and improve integrity

-- Combining tables to improve performance

86. ACID Properties

-- Atomicity, Consistency, Isolation, Durability

87. OLTP vs OLAP

-- OLTP: operational systems; OLAP: analytical systems

88. Materialized View

CREATE MATERIALIZED VIEW mv_high_salary AS SELECT * FROM employees WHERE salary >

60000;

89. Temporary Table

CREATE TEMPORARY TABLE temp_table (id INT);

90. IF EXISTS clause

DROP TABLE IF EXISTS temp_table;

91. COPY command (PostgreSQL)

COPY table_name FROM '/path/file.csv' DELIMITER ',' CSV HEADER;

92. Import CSV in MySQL

LOAD DATA INFILE 'file.csv' INTO TABLE table_name FIELDS TERMINATED BY ',';

93. JSON functions

SELECT JSON_VALUE(json_column, '$.name') FROM employees;

94. XML data handling

SELECT * FROM OPENXML(@idoc, '/ROOT/row', 1) WITH (name NVARCHAR(100));

95. Recursive CTE

WITH RECURSIVE nums AS (SELECT 1 AS n UNION ALL SELECT n + 1 FROM nums WHERE n

< 10) SELECT * FROM nums;

SELECT id FROM employees WHERE access_level & 1 = 1;

97. Windowing with LEAD()

SELECT name, salary, LEAD(salary, 1) OVER (ORDER BY salary) AS next_salary FROM

employees;

98. WITH TIES

SELECT TOP 3 WITH TIES * FROM employees ORDER BY salary DESC;

99. JSON to Table

SELECT * FROM OPENJSON(@json) WITH (name NVARCHAR(100));

100. Index on multiple columns

CREATE INDEX idx_multi ON employees(name, department);