Are you preparing for an SQL interview?

Whether you’re a beginner or an experienced professional, having a strong grasp of SQL concepts is essential to cracking technical interviews.

In this comprehensive guide, we’ve compiled the top 100 SQL interview questions and answers, covering everything from basic queries to advanced SQL techniques.

These frequently asked SQL interview questions will help you understand joins, indexing, stored procedures, performance optimization, and more with practical examples.

Mastering these SQL interview questions for freshers and experienced candidates will boost your confidence and improve your chances of landing your dream job.

Keep reading to explore real-world SQL problems, best practices, and expert solutions to ace your next SQL interview!

1. What is SQL?

Answer: SQL (Structured Query Language) is used to interact with databases, allowing users to store, retrieve, and manipulate data.

2. What are the different types of SQL commands?

Answer:

• DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE
• DML (Data Manipulation Language): INSERT, UPDATE, DELETE
• DQL (Data Query Language): SELECT
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

3. What is the difference between SQL and MySQL?

Answer: SQL is a language for managing databases, while MySQL is a database management system (DBMS) that implements SQL.

4. What is the difference between CHAR and VARCHAR?

Answer:

• CHAR(n): Fixed-length storage (always uses n bytes).
• VARCHAR(n): Variable-length storage (uses actual data length + 1 byte).

CREATE TABLE test (
    name CHAR(10),
    description VARCHAR(10)
);

5. What is a Primary Key?

Answer: A column (or set of columns) that uniquely identifies each row.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100)
);

6. What is a Foreign Key?

Answer: A column that establishes a relationship between two tables.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

7. What is the difference between DELETE and TRUNCATE?

Answer:

• DELETE: Removes specific rows.
• TRUNCATE: Removes all rows but retains table structure.

DELETE FROM employees WHERE department = 'HR';
TRUNCATE TABLE employees;

8. What is the difference between WHERE and HAVING?

Answer:

• WHERE: Filters rows before aggregation.
• HAVING: Filters groups after aggregation.

SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 10;

9. What are the types of JOINs?

Answer:

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL JOIN

SELECT employees.name, departments.name FROM employees
JOIN departments ON employees.department_id = departments.id;

10. What is an Index?

Answer: A database structure that improves query performance.

CREATE INDEX idx_customer_name ON customers(name);

11. What is Normalization?

Answer: A process to reduce redundancy and improve data integrity.

12. Explain different normal forms.

Answer:

• 1NF: No repeating groups.
• 2NF: 1NF + No partial dependencies.
• 3NF: 2NF + No transitive dependencies.

13. What is a Stored Procedure?

Answer: A reusable SQL block stored in the database.

CREATE PROCEDURE GetAllEmployees()
AS
BEGIN
    SELECT * FROM employees;
END;

14. What is a Trigger?

Answer: A procedure that executes automatically when an event occurs.

CREATE TRIGGER after_insert_employee
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO logs (action) VALUES ('New employee added');
END;

15. How do you find the second highest salary?

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

16. How do you delete duplicate rows?

DELETE FROM employees WHERE id NOT IN (
    SELECT MIN(id) FROM employees GROUP BY name, salary
);

17. What is the difference between EXISTS and IN?

SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments);
SELECT * FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE employees.department_id = departments.id);

18. What is the difference between UNION and UNION ALL?

SELECT name FROM customers UNION SELECT name FROM suppliers;
SELECT name FROM customers UNION ALL SELECT name FROM suppliers;

19. What is an Execution Plan?

Answer: A breakdown of how a query executes for optimization.

EXPLAIN ANALYZE SELECT * FROM employees;

20. What are Window Functions?

Answer: Functions that perform calculations across a set of table rows related to the current row.

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

SQL Interview Questions and Answers (21-40)

21. What is a View in SQL?

Answer: A virtual table based on a SQL query.

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

22. What is a Common Table Expression (CTE)?

Answer: A temporary result set within a query.

WITH EmployeeCTE AS (
    SELECT name, salary FROM employees WHERE salary > 50000
)
SELECT * FROM EmployeeCTE;

23. What is a Temporary Table?

Answer: A table that exists for the session.

CREATE TEMP TABLE temp_employees (id INT, name VARCHAR(50));

24. What is a Subquery?

Answer: A query within another query.

SELECT name FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);

25. What is the difference between RANK, DENSE_RANK, and ROW_NUMBER?

Answer:

• RANK(): Skips ranks when values are the same.
• DENSE_RANK(): No gaps in ranking.
• ROW_NUMBER(): Assigns unique numbers.

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

26. What is ACID in SQL?

Answer: Atomicity, Consistency, Isolation, Durability (ensures database reliability).

27. What is a Transaction in SQL?

Answer: A sequence of operations treated as a single unit.

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

28. What is the difference between COMMIT and ROLLBACK?

Answer:

• COMMIT: Saves changes.
• ROLLBACK: Undoes changes.

BEGIN TRANSACTION;
UPDATE employees SET salary = salary + 5000;
ROLLBACK;

29. What is Deadlock in SQL?

Answer: A situation where two transactions block each other.

30. What is a Cursor?

Answer: A database object used to retrieve row-by-row processing.

DECLARE cursor_name CURSOR FOR SELECT name FROM employees;

31. What is the difference between a Clustered and Non-Clustered Index?

Answer:

• Clustered Index: Sorts and stores data physically.
• Non-Clustered Index: Stores pointers to data.

CREATE CLUSTERED INDEX idx ON employees(salary);

32. What is the COALESCE function in SQL?

Answer: Returns the first non-null value.

SELECT COALESCE(NULL, 'Default', 'Fallback');

33. What is the IFNULL function?

Answer: Returns a specified value if the expression is NULL.

SELECT IFNULL(NULL, 'No Data');

34. What is GROUP BY in SQL?

Answer: Groups rows sharing a common field value.

SELECT department, COUNT(*) FROM employees GROUP BY department;

35. What is the HAVING clause?

Answer: Filters group results after aggregation.

SELECT department, COUNT(*) FROM employees GROUP BY department HAVING COUNT(*) > 5;

36. What is a Self Join?

Answer: A table joins itself.

SELECT e1.name, e2.name FROM employees e1 JOIN employees e2 ON e1.manager_id = e2.id;

37. What is a Cross Join?

Answer: Produces the Cartesian product of two tables.

SELECT * FROM employees CROSS JOIN departments;

38. What is a Full Outer Join?

Answer: Returns all records when there’s a match in either table.

SELECT * FROM employees FULL OUTER JOIN departments ON employees.department_id = departments.id;

39. How to find duplicate records?

SELECT name, COUNT(*) FROM employees GROUP BY name HAVING COUNT(*) > 1;

40. How to fetch top N records?

SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

SQL Interview Questions and Answers (41-60)

41. What is a Materialized View?

Answer: A physical copy of a query result stored for faster access.

CREATE MATERIALIZED VIEW emp_salary AS
SELECT name, salary FROM employees;

42. What is an Alias in SQL?

Answer: A temporary name for a table or column.

SELECT name AS EmployeeName FROM employees;

43. What is a Case Statement in SQL?

Answer: Used for conditional logic.

SELECT name, salary,
CASE 
    WHEN salary > 50000 THEN 'High'
    WHEN salary > 30000 THEN 'Medium'
    ELSE 'Low'
END AS SalaryCategory
FROM employees;

44. What is the difference between COUNT(*) and COUNT(column_name)?

Answer:

• COUNT(*): Counts all rows.
• COUNT(column_name): Counts non-null values.

SELECT COUNT(*) FROM employees;
SELECT COUNT(salary) FROM employees;

45. What is a Recursive Query?

Answer: A query that refers to itself.

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id FROM employees e
    JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM EmployeeHierarchy;

46. What is an Auto-Increment Field?

Answer: A column that automatically increments its value.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

47. What is the Default Constraint?

Answer: Assigns a default value when no value is provided.

ALTER TABLE employees ADD COLUMN status VARCHAR(10) DEFAULT 'Active';

48. What is a Unique Constraint?

Answer: Ensures unique values in a column.

CREATE TABLE students (
    student_id INT UNIQUE,
    name VARCHAR(50)
);

49. What is the difference between CHAR and TEXT?

Answer:

• CHAR(n): Fixed length.
• TEXT: Variable-length string storage.

CREATE TABLE products (
    code CHAR(10),
    description TEXT
);

50. What is the difference between NOW() and CURRENT_TIMESTAMP?

Answer: Both return the current date and time, but NOW() is function-based while CURRENT_TIMESTAMP is keyword-based.

SELECT NOW(), CURRENT_TIMESTAMP;

51. What is the BETWEEN operator?

Answer: Filters values within a range.

SELECT * FROM employees WHERE salary BETWEEN 30000 AND 60000;

52. What is an Aggregate Function?

Answer: Performs calculations on multiple rows.

• Examples: SUM(), AVG(), COUNT(), MAX(), MIN()

SELECT department, AVG(salary) FROM employees GROUP BY department;

53. What is the Difference Between DROP, DELETE, and TRUNCATE?

Answer:

• DROP: Deletes the table structure.
• DELETE: Removes specific rows.
• TRUNCATE: Deletes all rows but keeps the structure.

DROP TABLE employees;
DELETE FROM employees WHERE id = 1;
TRUNCATE TABLE employees;

54. How to Find the Third Highest Salary?

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2;

55. What is a Foreign Key Constraint?

Answer: Links two tables together.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

56. What is the Difference Between UNION and UNION ALL?

Answer:

• UNION: Removes duplicates.
• UNION ALL: Includes duplicates.

SELECT name FROM customers UNION SELECT name FROM suppliers;
SELECT name FROM customers UNION ALL SELECT name FROM suppliers;

57. How to Convert a String to a Date in SQL?

SELECT STR_TO_DATE('2023-12-31', '%Y-%m-%d');

58. What is the Difference Between a Stored Procedure and a Function?

Answer:

• Stored Procedure: Can perform operations but doesn’t return a value.
• Function: Always returns a value.

CREATE FUNCTION GetTotalSalary()
RETURNS INT
BEGIN
    RETURN (SELECT SUM(salary) FROM employees);
END;

59. How to Find the Number of Rows in a Table?

SELECT COUNT(*) FROM employees;

60. What is the Difference Between a Primary Key and a Unique Key?

Answer:

• Primary Key: Cannot be null and uniquely identifies a row.
• Unique Key: Ensures uniqueness but allows null values.

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

SQL Interview Questions and Answers (61-80)

61. What is Normalization in SQL?

Answer: The process of organizing data to reduce redundancy.

62. What are the Different Normal Forms?

Answer:

• 1NF: No duplicate columns.
• 2NF: No partial dependencies.
• 3NF: No transitive dependencies.
• BCNF: Stronger version of 3NF.

63. What is Denormalization?

Answer: The process of combining tables for performance improvement.

64. What is an Index in SQL?

Answer: Speeds up queries by creating a data structure on columns.

CREATE INDEX idx_name ON employees(name);

65. What is a Composite Index?

Answer: An index on multiple columns.

CREATE INDEX idx_multi ON employees(department, salary);

66. What is a Partial Index?

Answer: An index on a subset of data.

CREATE INDEX idx_active ON employees(status) WHERE status = 'Active';

67. What is the Difference Between a Primary Index and a Secondary Index?

Answer:

• Primary Index: Created on primary key.
• Secondary Index: Created on non-primary key columns.

68. What is the Use of the EXPLAIN Statement?

Answer: Analyzes query execution plans.

EXPLAIN SELECT * FROM employees WHERE salary > 50000;

69. How to Remove Duplicates from a Table?

DELETE FROM employees WHERE id NOT IN (
    SELECT MIN(id) FROM employees GROUP BY name
);

70. What is a Check Constraint?

Answer: Ensures column values meet a condition.

ALTER TABLE employees ADD CHECK (salary > 0);

71. What is the Difference Between a Subquery and a JOIN?

Answer:

• Subquery: Nested query inside another query.
• JOIN: Combines multiple tables in a single query.

72. What is the Difference Between EXISTS and IN?

Answer:

• EXISTS: Returns true if subquery finds a match.
• IN: Checks if a value is in a list.

SELECT name FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE employees.department_id = departments.id);

73. What is the Difference Between DELETE and TRUNCATE?

Answer:

• DELETE: Removes specific rows, can be rolled back.
• TRUNCATE: Removes all rows, cannot be rolled back.

74. How to Find the Second Highest Salary?

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

75. What is a Stored Procedure?

Answer: A reusable SQL script stored in the database.

CREATE PROCEDURE GetEmployees()
BEGIN
    SELECT * FROM employees;
END;

76. What is a Trigger?

Answer: An automatic action executed when an event occurs.

CREATE TRIGGER after_insert_employee
AFTER INSERT ON employees
FOR EACH ROW
INSERT INTO log_table(action) VALUES('New employee added');

77. What is the Difference Between a Function and a Stored Procedure?

Answer:

• Function: Returns a value, can be used in queries.
• Stored Procedure: Can perform multiple operations, doesn’t always return a value.

78. What is the Difference Between UNION and JOIN?

Answer:

• UNION: Combines results of multiple queries.
• JOIN: Combines columns from multiple tables.

79. What is a ROW_NUMBER() Function?

Answer: Assigns a unique row number to each record.

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

80. What is the Difference Between VARCHAR and TEXT?

Answer:

• VARCHAR: Fixed-length storage.
• TEXT: Variable-length, can store large strings.

SQL Interview Questions and Answers (81-100)

81. What is the difference between CROSS JOIN and FULL OUTER JOIN?

Answer:

• CROSS JOIN: Produces a Cartesian product of two tables.
• FULL OUTER JOIN: Returns matching and non-matching rows from both tables.

SELECT * FROM employees CROSS JOIN departments;
SELECT * FROM employees FULL OUTER JOIN departments ON employees.dept_id = departments.id;

82. What is a Self Join?

Answer: A join where a table is joined with itself.

SELECT e1.name, e2.name AS manager FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.id;

83. What is the COALESCE() function?

Answer: Returns the first non-null value from a list.

SELECT name, COALESCE(phone, 'No Phone') FROM employees;

84. What is the IFNULL() function?

Answer: Returns an alternate value if a column is NULL.

SELECT name, IFNULL(email, 'No Email') FROM employees;

85. What is the difference between RANK() and DENSE_RANK()?

Answer:

• RANK(): Skips ranking if there is a tie.
• DENSE_RANK(): Doesn’t skip ranking.

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

86. What is the LEAD() function?

Answer: Fetches the next row’s value in the result set.

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

87. What is the LAG() function?

Answer: Fetches the previous row’s value in the result set.

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

88. What is a View in SQL?

Answer: A virtual table based on a query result.

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

89. What is the MERGE statement?

Answer: Used to perform INSERT, UPDATE, or DELETE in a single statement.

MERGE INTO employees AS target
USING new_data AS source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET target.salary = source.salary
WHEN NOT MATCHED THEN INSERT (id, name, salary) VALUES (source.id, source.name, source.salary);

90. What is the GROUP_CONCAT() function?

Answer: Concatenates multiple row values into a single string.

SELECT department, GROUP_CONCAT(name) FROM employees GROUP BY department;

91. How to Find Duplicate Records in a Table?

SELECT name, COUNT(*) FROM employees GROUP BY name HAVING COUNT(*) > 1;

92. How to Delete Duplicate Records but Keep One?

DELETE FROM employees WHERE id NOT IN (
    SELECT MIN(id) FROM employees GROUP BY name
);

93. What is the CASE statement?

Answer: Performs conditional logic in queries.

SELECT name, salary,
CASE
    WHEN salary > 70000 THEN 'High'
    WHEN salary > 50000 THEN 'Medium'
    ELSE 'Low'
END AS SalaryCategory
FROM employees;

94. What is the JSON data type in SQL?

Answer: Stores JSON-formatted data.

CREATE TABLE customers (
    id INT PRIMARY KEY,
    details JSON
);

95. What is a Pivot Table in SQL?

Answer: Converts row data into columns.

SELECT department,
    SUM(CASE WHEN gender = 'Male' THEN 1 ELSE 0 END) AS Male_Count,
    SUM(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS Female_Count
FROM employees
GROUP BY department;

96. How to Perform Pagination in SQL?

SELECT * FROM employees ORDER BY id LIMIT 10 OFFSET 20;

97. What is the Difference Between SERIAL and AUTO_INCREMENT?

Answer: Both generate unique values automatically, but SERIAL is specific to PostgreSQL, while AUTO_INCREMENT is used in MySQL.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50)
);

98. How to Update Multiple Rows in SQL?

UPDATE employees
SET salary = CASE id
    WHEN 1 THEN 50000
    WHEN 2 THEN 60000
    ELSE salary
END;

99. How to Perform a Bulk Insert in SQL?

INSERT INTO employees (id, name, salary) VALUES
(1, 'Alice', 50000),
(2, 'Bob', 60000),
(3, 'Charlie', 70000);

100. What is ACID in SQL?

Answer:

• Atomicity: Ensures all operations in a transaction are completed.
• Consistency: Ensures database remains in a valid state.
• Isolation: Ensures transactions do not affect each other.
• Durability: Ensures committed transactions persist even in case of failure.

We hope this extensive list of top 100 SQL interview questions and answers helps you in your preparation. Whether you are applying for a SQL developer, database administrator, or data analyst role, mastering these commonly asked SQL interview questions will give you a competitive edge.

By practicing these real-world SQL problems and understanding best practices, you’ll be well-prepared to handle any SQL-related challenge in your next interview.

Don’t forget to bookmark this page and revisit it for a quick revision before your interview.

If you found this guide helpful, share it with others preparing for SQL interviews. Keep practicing, and best of luck with your SQL job interview!