SQL Interview Questions and Answers 

Here are 80 commonly asked interview questions on SQL along with their answers and code examples. Please note that the answers and code examples provided here are simplified and may not cover all possible scenarios. It's important to tailor your answers to your specific experience and the requirements of the job you're applying for.

1. What is SQL? SQL (Structured Query Language) is a programming language used for managing and manipulating relational databases.

2. What are the different types of SQL statements? SQL statements can be categorized into four types: Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL).

3. What is the difference between DELETE and TRUNCATE in SQL? DELETE is a DML statement used to remove rows from a table, while TRUNCATE is a DDL statement used to remove all rows from a table, resulting in the reset of the table's structure.

4. What is a primary key? A primary key is a unique identifier for a row in a table. It ensures that each row in the table is uniquely identifiable and serves as a reference for relating data across tables.

Example:

sql
CREATE TABLE Employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  age INT
);

5. What is a foreign key? A foreign key is a field in a table that refers to the primary key in another table. It establishes a relationship between two tables, enforcing referential integrity.

Example:

sql
CREATE TABLE Orders (
  id INT PRIMARY KEY,
  product_name VARCHAR(50),
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES Customers(id)
);

6. What is the difference between UNION and UNION ALL in SQL? UNION combines the result sets of two or more SELECT statements, removing duplicate rows, while UNION ALL combines the result sets without removing duplicates.

7. What is a subquery? A subquery is a query nested inside another query. It can be used to retrieve data based on the results of another query.

Example:

sql
SELECT name
FROM Employees
WHERE age > (SELECT AVG(age) FROM Employees);

8. What is a self-join? A self-join is a join where a table is joined with itself. It is used to combine rows from the same table based on a related column.

Example:

sql
SELECT e1.name, e2.name
FROM Employees e1, Employees e2
WHERE e1.manager_id = e2.id;

9. Explain the difference between clustered and non-clustered indexes. A clustered index determines the physical order of data in a table, while a non-clustered index creates a separate structure that points to the physical location of data.

10. What is the difference between a primary key and a unique key? Both primary key and unique key enforce uniqueness, but a table can have only one primary key, while multiple unique keys can be defined.

11. What is the difference between a left join and an inner join? A left join returns all records from the left table and matching records from the right table, while an inner join returns only the matching records from both tables.

12. Explain the ACID properties of a transaction. ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity ensures that a transaction is treated as a single, indivisible unit. Consistency ensures that a transaction brings the database from one valid state to another. Isolation ensures that concurrent transactions do not interfere with each other. Durability guarantees that once a transaction is committed, its changes are permanent.

13. What is the difference between a view and a table? A table is a physical structure that stores data, while a view is a virtual table based on the result of a query. A view does not store data directly but provides a way to retrieve and manipulate data from one or more tables.

14. What is the difference between CHAR and VARCHAR data types? CHAR is a fixed-length character data type, while VARCHAR is a variable-length character data type. CHAR pads spaces to the right to match the specified length, while VARCHAR does not.

15. What is the purpose of the GROUP BY clause? The GROUP BY clause is used to group rows based on one or more columns. It is often used with aggregate functions like COUNT, SUM, AVG, etc., to calculate values on grouped data.

Example:

Sql
SELECT department, COUNT(*) as count
FROM Employees
GROUP BY department;

16. What is a stored procedure? A stored procedure is a prepared SQL code block that can be stored in the database and executed on demand. It encapsulates a series of SQL statements and can accept input parameters and return output.

Example:

sql
CREATE PROCEDURE GetEmployeeById (IN empId INT)
BEGIN
  SELECT * FROM Employees WHERE id = empId;
END;

17. What is the difference between the HAVING clause and the WHERE clause? The WHERE clause is used to filter rows before grouping in a query, while the HAVING clause is used to filter groups after the grouping operation has been performed.

18. Explain the difference between a correlated and a non-correlated subquery. A non-correlated subquery is independent of the outer query and can be executed separately. A correlated subquery depends on the outer query and is executed for each row returned by the outer query.

19. What is normalization in SQL? Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves splitting tables into smaller, well-defined structures and establishing relationships between them.

20. What is denormalization? Denormalization is the process of introducing redundancy into a database by combining tables or adding duplicate data. It is done to improve performance by reducing the number of joins required for queries.

21. What is the purpose of the ORDER BY clause? The ORDER BY clause is used to sort the result set based on one or more columns. It can sort in ascending (ASC) or descending (DESC) order.

Example:

sql
SELECT name, age
FROM Employees
ORDER BY age DESC;

22. What is the difference between a candidate key and a composite key? A candidate key is a column or set of columns that can uniquely identify a row in a table. A composite key is a key that consists of multiple columns.

23. What is a cross join? A cross join (Cartesian join) returns the Cartesian product of two tables, resulting in the combination of all rows from both tables.

Example:

sql
SELECT *
FROM Table1
CROSS JOIN Table2;

24. What is the difference between a subquery and a join? A subquery is a query nested inside another query, while a join is used to combine rows from two or more tables based on related columns. Subqueries are generally used for complex filtering or calculations, while joins are used for combining data from different tables.

25. What is the purpose of the COALESCE function? The COALESCE function is used to return the first non-null expression from a list of expressions. It is often used to provide a default value when a column has a null value.

Example:

sql
SELECT COALESCE(salary, 0) as salary
FROM Employees;

26. What is a cursor in SQL? A cursor is a database object used to retrieve and manipulate rows from a result set. It allows sequential processing of the rows and enables operations like fetching, updating, and deleting.

27. What is the difference between a unique constraint and a unique index? A unique constraint is a rule that enforces uniqueness on a column or a combination of columns, while a unique index is an index structure that enforces uniqueness and improves performance for queries involving the indexed columns.

28. Explain the concept of NULL in SQL. NULL represents the absence of a value or an unknown value. It is different from an empty string or zero. NULL values require special handling and are not equal to any other value, including other NULL values.

29. What is the purpose of the TRIGGER statement in SQL? A trigger is a database object that is associated with a table and automatically executes a set of SQL statements when a specific event (e.g., insert, update, delete) occurs on the table.

30. What is a correlated update? A correlated update is an update statement that references the same table in both the update condition and the update expression. It is used to update values based on related rows in the same table.

Example:

sql
UPDATE Employees e1
SET salary = (SELECT AVG(salary) FROM Employees e2 WHERE e1.department = e2.department);

31. What is the difference between the EXISTS and IN operators in SQL? The EXISTS operator is used to check the existence of rows returned by a subquery, while the IN operator is used to check if a value matches any value in a list or a subquery.

32. Explain the concept of database normalization forms (1NF, 2NF, 3NF). Normalization forms define a set of rules to organize data in a relational database.

• First Normal Form (1NF) eliminates duplicate data and ensures atomicity by making sure each column has a single value.
• Second Normal Form (2NF) removes partial dependencies by ensuring that non-key columns depend on the entire primary key.
• Third Normal Form (3NF) eliminates transitive dependencies by ensuring that non-key columns depend only on the primary key and not on other non-key columns.

33. What is the purpose of the UNION operator in SQL? The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. It removes duplicate rows by default.

Example:

sql
SELECT name FROM Employees
UNION
SELECT name FROM Customers;

34. What is the difference between the LIKE and the IN operator in SQL? The LIKE operator is used for pattern matching with wildcard characters, while the IN operator is used to compare a value against a list of specified values.

35. What is a deadlock in SQL? A deadlock is a situation where two or more transactions are waiting for each other to release resources, resulting in a circular dependency and a halt in the execution of those transactions.

36. What is the purpose of the ROLLUP operator in SQL? The ROLLUP operator is used to generate subtotal values for a given set of columns in a result set. It produces multiple levels of aggregation, creating a hierarchical report.

Example:

sql
SELECT department, region, SUM(sales) as total_sales
FROM Sales
GROUP BY ROLLUP (department, region);

37. What is the difference between the MAX() and the MIN() functions in SQL? The MAX() function returns the maximum value from a column, while the MIN() function returns the minimum value.

Example:

Sql
SELECT MAX(salary) as max_salary, MIN(salary) as min_salary
FROM Employees;

38. What is the purpose of the CASE statement in SQL? The CASE statement is used to perform conditional logic in SQL queries. It allows you to define different outputs based on specified conditions.

Example:

Sql
SELECT name,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age >= 18 AND age < 65 THEN 'Adult'
    ELSE 'Senior'
  END as age_group
FROM Persons;

39. Explain the difference between the COUNT() and COUNT(column_name) functions. COUNT() counts all rows in a table, regardless of the presence of NULL values in any column. COUNT(column_name) counts the non-null values in the specified column.

40. What is a correlated subquery? A correlated subquery is a subquery that references a column from the outer query. It is evaluated for each row of the outer query and can provide different results based on the values of the outer query.

Example:

sql
SELECT name
FROM Employees e1
WHERE salary > (SELECT AVG(salary) FROM Employees e2 WHERE e1.department = e2.department);

41. What is the purpose of the FETCH statement in SQL? The FETCH statement is used to retrieve a specified number of rows from a result set produced by a SELECT statement. It is often used with cursors to retrieve rows in a controlled manner.

42. What is the difference between the UNION and the UNION ALL operators in SQL? The UNION operator combines and returns distinct rows from multiple SELECT statements, removing duplicates. The UNION ALL operator combines and returns all rows from multiple SELECT statements, including duplicates.

43. What is the purpose of the EXISTS operator in SQL? The EXISTS operator is used to check the existence of rows returned by a subquery. It returns true if the subquery returns any rows, and false otherwise.

Example:

sql
SELECT name
FROM Employees e
WHERE EXISTS (SELECT * FROM Salaries s WHERE e.id = s.employee_id);

44. What is a natural join in SQL? A natural join is a join that automatically matches columns with the same name in both tables. It combines rows where the values in these matched columns are equal.

Example:

sql
SELECT *
FROM Employees
NATURAL JOIN Departments;

45. What is the purpose of the CASCADE option in a foreign key constraint? The CASCADE option specifies that when a referenced row in the parent table is deleted or updated, the corresponding rows in the child table should also be deleted or updated accordingly.

Example:

sql
CREATE TABLE Orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES Customers(id) ON DELETE CASCADE
);

46. What is the difference between the GROUP BY clause and the DISTINCT keyword? The GROUP BY clause is used to group rows based on one or more columns and perform aggregate functions on each group. The DISTINCT keyword is used to return unique rows from a result set by eliminating duplicates.

47. Explain the purpose of the ROW_NUMBER() function in SQL. The ROW_NUMBER() function assigns a unique sequential number to each row within a result set. It is often used to generate row numbers for pagination or ranking purposes.

Example:

sql
SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) as rank, name, salary
FROM Employees;

48. What is the purpose of the COALESCE() function in SQL? The COALESCE() function is used to return the first non-null expression from a list of expressions. It is often used to provide a default value when a column has a null value.

Example:

sql
SELECT COALESCE(salary, 0) as salary
FROM Employees;

49. What is a correlated subquery? A correlated subquery is a subquery that references a column from the outer query. It is evaluated for each row of the outer query and can provide different results based on the values of the outer query.

Example:

sql
SELECT name
FROM Employees e1
WHERE salary > (SELECT AVG(salary) FROM Employees e2 WHERE e1.department = e2.department);

50. What is the purpose of the FETCH statement in SQL? The FETCH statement is used to retrieve a specified number of rows from a result set produced by a SELECT statement. It is often used with cursors to retrieve rows in a controlled manner.

51. What is the difference between the UNION and the UNION ALL operators in SQL? The UNION operator combines and returns distinct rows from multiple SELECT statements, removing duplicates. The UNION ALL operator combines and returns all rows from multiple SELECT statements, including duplicates.

52. What is the purpose of the EXISTS operator in SQL? The EXISTS operator is used to check the existence of rows returned by a subquery. It returns true if the subquery returns any rows, and false otherwise.

Example:

sql
SELECT name
FROM Employees e
WHERE EXISTS (SELECT * FROM Salaries s WHERE e.id = s.employee_id);

53. What is a natural join in SQL? A natural join is a join that automatically matches columns with the same name in both tables. It combines rows where the values in these matched columns are equal.

Example:

sql
SELECT *
FROM Employees
NATURAL JOIN Departments;

54. What is the purpose of the CASCADE option in a foreign key constraint? The CASCADE option specifies that when a referenced row in the parent table is deleted or updated, the corresponding rows in the child table should also be deleted or updated accordingly.

Example:

sql
CREATE TABLE Orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES Customers(id) ON DELETE CASCADE
);

55. What is the difference between the GROUP BY clause and the DISTINCT keyword? The GROUP BY clause is used to group rows based on one or more columns and perform aggregate functions on each group. The DISTINCT keyword is used to return unique rows from a result set by eliminating duplicates.

56. Explain the purpose of the ROW_NUMBER() function in SQL. The ROW_NUMBER() function assigns a unique sequential number to each row within a result set. It is often used to generate row numbers for pagination or ranking purposes.

Example:

sql
SELECT ROW_NUMBER() OVER (ORDER BY salary DESC) as rank, name, salary
FROM Employees;

57. What is the purpose of the COALESCE() function in SQL? The COALESCE() function is used to return the first non-null expression from a list of expressions. It is often used to provide a default value when a column has a null value.

Example:

sql
SELECT COALESCE(salary, 0) as salary
FROM Employees;

58. What is the purpose of the LAG() function in SQL? The LAG() function is used to access the value of a column from a previous row within the same result set. It is often used to calculate the difference or the change between current and previous values.

Example:

sql
SELECT name, salary, LAG(salary) OVER (ORDER BY name) as previous_salary
FROM Employees;

59. Explain the purpose of the ROWS BETWEEN clause in SQL window functions. The ROWS BETWEEN clause is used to define a window frame within a window function. It specifies the range of rows over which the window function operates, allowing for more control in calculating aggregates and rankings.

Example:

sql
SELECT name, salary, AVG(salary) OVER (PARTITION BY department ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as avg_salary
FROM Employees;

60. What is the purpose of the HAVING clause in SQL? The HAVING clause is used to filter groups generated by the GROUP BY clause. It is applied after the grouping and can be used with aggregate functions to filter groups based on specified conditions.

Example:

sql

SELECT department, AVG(salary) as avg_salary
FROM Employees
GROUP BY department
HAVING AVG(salary) > 50000;

61. What is the difference between UNION and UNION ALL in SQL? The UNION operator combines the result sets of two or more SELECT statements, removing duplicate rows. The UNION ALL operator combines the result sets, including all rows, without removing duplicates.

62. Explain the concept of indexing in SQL. Indexing is a technique used to improve the performance of database operations, such as searching, sorting, and joining. It involves creating data structures (indexes) that store the values of specific columns in a sorted manner for faster data retrieval.

63. What is a self-join in SQL? A self-join is a join operation where a table is joined with itself. It is useful when there is a need to combine related rows within the same table based on a common column.

Example:

Sql
SELECT e1.name, e2.name as manager
FROM Employees e1
JOIN Employees e2 ON e1.manager_id = e2.id;

64. What is the purpose of the TRUNCATE TABLE statement in SQL? The TRUNCATE TABLE statement is used to remove all rows from a table, while keeping the structure of the table intact. It is faster than the DELETE statement because it does not generate individual row-level delete operations.

65. What is a primary key in SQL? A primary key is a column or a combination of columns that uniquely identifies each row in a table. It enforces data integrity and is used as a reference in relationships with other tables.

66. Explain the concept of data integrity in SQL. Data integrity refers to the accuracy, consistency, and reliability of data stored in a database. It is enforced through various mechanisms, such as constraints, to ensure that data meets specified rules and standards.

67. What is the purpose of the EXISTS operator in SQL? The EXISTS operator is used to check the existence of rows returned by a subquery. It returns true if the subquery returns any rows, and false otherwise.

Example:

sql
SELECT name
FROM Employees
WHERE EXISTS (SELECT * FROM Salaries WHERE Employees.id = Salaries.employee_id);

68. What is the difference between UNION and UNION ALL in SQL? The UNION operator combines the result sets of two or more SELECT statements, removing duplicate rows. The UNION ALL operator combines the result sets, including all rows, without removing duplicates.

69. Explain the concept of indexing in SQL. Indexing is a technique used to improve the performance of database operations, such as searching, sorting, and joining. It involves creating data structures (indexes) that store the values of specific columns in a sorted manner for faster data retrieval.

70. What is a self-join in SQL? A self-join is a join operation where a table is joined with itself. It is useful when there is a need to combine related rows within the same table based on a common column.

Example:

Sql
SELECT e1.name, e2.name as manager
FROM Employees e1
JOIN Employees e2 ON e1.manager_id = e2.id;

71. What is the purpose of the TRUNCATE TABLE statement in SQL? The TRUNCATE TABLE statement is used to remove all rows from a table, while keeping the structure of the table intact. It is faster than the DELETE statement because it does not generate individual row-level delete operations.

72. What is a primary key in SQL? A primary key is a column or a combination of columns that uniquely identifies each row in a table. It enforces data integrity and is used as a reference in relationships with other tables.

73. Explain the concept of data integrity in SQL. Data integrity refers to the accuracy, consistency, and reliability of data stored in a database. It is enforced through various mechanisms, such as constraints, to ensure that data meets specified rules and standards.

74. What is the purpose of the GROUP BY clause in SQL? The GROUP BY clause is used to group rows based on one or more columns. It is often used with aggregate functions, such as SUM, AVG, COUNT, to perform calculations on groups of data.

Example:

Sql
SELECT department, COUNT(*) as employee_count
FROM Employees
GROUP BY department;

75. What is the difference between the INNER JOIN and the OUTER JOIN in SQL? An INNER JOIN returns only the rows that have matching values in both tables being joined. An OUTER JOIN returns all rows from one table and the matching rows from the other table, with null values for non-matching rows.

76. What is the purpose of the UPDATE statement in SQL? The UPDATE statement is used to modify existing data in a table. It allows you to specify which columns to update and the new values to set.

Example:

sql
UPDATE Employees
SET salary = salary * 1.1
WHERE department = 'Sales';

77. What is the purpose of the CASE statement in SQL? The CASE statement is used to perform conditional logic in SQL queries. It allows you to define different outputs based on specified conditions.

Example:

Sql
SELECT name,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age >= 18 AND age < 65 THEN 'Adult'
    ELSE 'Senior'
  END as age_group
FROM Persons;

78. Explain the concept of database normalization forms (1NF, 2NF, 3NF). Normalization forms define a set of rules to organize data in a relational database.

• First Normal Form (1NF) eliminates duplicate data and ensures atomicity by making sure each column has a single value.
• Second Normal Form (2NF) removes partial dependencies by ensuring that non-key columns depend on the entire primary key.
• Third Normal Form (3NF) eliminates transitive dependencies by ensuring that non-key columns depend only on the primary key and not on other non-key columns.

79. What is the purpose of the UNION operator in SQL? The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. It removes duplicate rows by default.

Example:

sql
SELECT name FROM Employees
UNION
SELECT name FROM Customers;

80. What is a deadlock in SQL? A deadlock is a situation where two or more transactions are waiting for each other to release resources, resulting in a circular dependency and a halt in the execution of those transactions.