Whether you’re on the hunt for a new job to leverage your SQL expertise or you’re a hiring manager preparing to evaluate candidates for an SQL-related role, understanding common SQL interview questions and their answers is crucial.

This article provides an overview of 100 essential SQL questions and answers, suitable for both beginners and those with intermediate experience. This guide will help job seekers get ready for interviews and give hiring managers insights into what to expect from candidates.

While this article is primarily aimed at job seekers, it will also be valuable for recruiters and hiring managers conducting initial SQL interviews.

1. What is SQL?
a. SQL (Structured Query Language) is a programming language used for managing relational databases. It allows users to store, manipulate, and retrieve data from databases.

2. What are the different types of SQL statements?
a. SQL statements can be categorized into three types:
i. Data Definition Language (DDL): Used for creating, altering, and dropping database objects.
ii. Data Manipulation Language (DML): Used for querying, inserting, updating, and deleting data.
iii. Data Control Language (DCL): Used for controlling access to the database, granting or revoking privileges.

3. What is a primary key?
a. A primary key is a column or a set of columns that uniquely identifies each record in a table. It ensures data integrity and allows efficient retrieval of data.

4. What is a foreign key?
a. A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It establishes a relationship between the two tables.

5. What is a composite key?
a. A composite key is a primary key composed of two or more columns. Together, these columns uniquely identify each record in a table.

6. What is the difference between DELETE and TRUNCATE?
a. DELETE is a DML statement used to remove specific rows from a table, whereas TRUNCATE is a DDL statement used to remove all rows from a table, effectively resetting the table.

7. What is a subquery?
a. A subquery is a query nested within another query. It can be used to retrieve data from one table based on values from another table or perform complex calculations.

8. What is the difference between a subquery and a join?
a. A subquery is a query nested within another query, whereas a join is used to combine rows from two or more tables based on related columns.

9. What is a self-join?
a. A self-join is a join operation where a table is joined with itself. It is useful when you want to compare rows within the same table.

10. What are the different types of JOIN operations?
a. The different types of JOIN operations are:
i. INNER JOIN: Returns only the matching rows from both tables.
ii. LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
iii. RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.
iv. FULL JOIN: Returns all rows from both tables.

11. What is normalization in SQL?
a. Normalization is the process of organizing data in a database to eliminate redundancy and dependency issues. It involves splitting tables into smaller, more manageable entities.

12. What are the different normal forms in database normalization?
a. The different normal forms are:
i. First Normal Form (1NF): Eliminates duplicate rows and ensures atomicity of values.
ii. Second Normal Form (2NF): Ensures that each non-key column depends on the entire primary key.
iii. Third Normal Form (3NF): Ensures that each non-key column depends only on the primary key and not on other non-key columns.
iv. Fourth Normal Form (4NF): Eliminates multi-valued dependencies.
v. Fifth Normal Form (5NF): Eliminates join dependencies.

13. What is an index?
a. An index is a database structure that improves the speed of data retrieval operations on database tables. It allows faster searching, sorting, and filtering of data.

14. What is a clustered index?
a. A clustered index determines the physical order of data in a table. Each table can have only one clustered index, and it is generally created on the primary key column(s).

15. What is a non-clustered index?
a. A non-clustered index is a separate structure from the table that contains a sorted list of selected columns. It enhances the performance of searching and filtering operations.

16. What is the difference between a primary key and a unique key?
a. A primary key is a column or a set of columns that uniquely identifies each record in a table and cannot contain NULL values. A unique key, on the other hand, allows NULL values and enforces uniqueness but does not automatically define the primary identifier of a table.

17. What is ACID in database transactions?
a. ACID stands for Atomicity, Consistency, Isolation, and Durability. It is a set of properties that ensure reliability and integrity in database transactions.

18. What is the difference between UNION and UNION ALL?
a. UNION combines the result sets of two or more SELECT statements and removes duplicates, whereas UNION ALL combines the result sets without removing duplicates.

19. What is a view?
a. A view is a virtual table derived from one or more tables. It does not store data but provides a way to present data in a customized or simplified manner.

20. What is a stored procedure?
a. A stored procedure is a precompiled set of SQL statements that performs a specific task. It can be called and executed multiple times with different parameters.

21. What is a trigger?
a. A trigger is a set of SQL statements that are automatically executed in response to a specific event, such as INSERT, UPDATE, or DELETE operations on a table.

22. What is a transaction?
a. A transaction is a logical unit of work that consists of one or more database operations. It ensures that all operations within the transaction are treated as a single unit, either all succeeding or all failing.

23. What is a deadlock?
a. A deadlock is a situation where two or more transactions are unable to proceed because each is waiting for a resource held by another transaction. This can result in a perpetual wait state.

24. What is the difference between CHAR and VARCHAR data types?
a. CHAR is a fixed-length character data type that stores a specific number of characters, while VARCHAR is a variable-length character data type that stores a varying number of characters.

25. What is the difference between a function and a stored procedure?
a. A function returns a value and can be used in SQL statements, whereas a stored procedure does not return a value directly but can perform various actions.

26. What is the difference between GROUP BY and HAVING clauses?
a. GROUP BY is used to group rows based on one or more columns, while HAVING is used to filter grouped rows based on specific conditions.

27. What is the difference between a database and a schema?
a. A database is a collection of related data that is stored and organized. A schema, on the other hand, is a logical container within a database that holds objects like tables, views, and procedures.

28. What is a data warehouse?
a. A data warehouse is a large repository of data collected from various sources, structured and organized to support business intelligence and reporting.

29. What is the difference between OLTP and OLAP?
a. OLTP (Online Transaction Processing) is used for day-to-day transactional operations and focuses on real-time processing. OLAP (Online Analytical Processing) is used for complex analytical queries and focuses on historical data analysis.

30. What is a correlated subquery?
a. A correlated subquery is a subquery that references columns from the outer query. It is executed for each row of the outer query, making it dependent on the outer query’s results.

31. What is the difference between a temporary table and a table variable?
a. A temporary table is a physical table that is created and used temporarily within a session or a specific scope, whereas a table variable is a variable with a structure similar to a table and exists only within the scope of a user-defined function or a stored procedure.

32. What is the difference between UNION and JOIN?
a. UNION combines rows from two or more tables vertically, while JOIN combines columns from two or more tables horizontally based on related columns.

33. What is the difference between WHERE and HAVING clauses?
a. WHERE is used to filter rows before grouping in a query, while HAVING is used to filter grouped rows after grouping.

34. What is the difference between a database and a data warehouse?
a. A database is a collection of related data organized for transactional purposes, while a data warehouse is a large repository of data organized for analytical purposes.

35. What is the difference between a primary key and a candidate key?
a. A candidate key is a column or a set of columns that can uniquely identify each record in a table. A primary key is a chosen candidate key that becomes the main identifier for the table.

36. What is the difference between a schema and a database?
a. A database is a collection of related data, while a schema is a logical container within a database that holds objects like tables, views, and procedures.

37. What is a self-join?
a. A self-join is a join operation where a table is joined with itself. It is used when you want to compare rows within the same table.

38. What is a recursive SQL query?
a. A recursive SQL query is a query that refers to its own output in order to perform additional operations. It is commonly used

39. What is a CTE (Common Table Expression)?
a. A CTE is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement. It is used to simplify complex queries and improve readability.

40. What is the difference between a CTE and a subquery?
a. A CTE is defined before the main query and can be referenced multiple times within the query, while a subquery is nested within the main query and is executed once for each reference.

41. What is the purpose of the DISTINCT keyword in the SELECT statement?
a. The DISTINCT keyword in the SELECT statement is used to retrieve unique values from a column in the result set, eliminating duplicate rows.

42. What is a UNION ALL operator?
a. The UNION ALL operator combines the result sets of two or more SELECT statements and includes all duplicates. It does not remove duplicate rows.

43. What is the purpose of the LIKE operator?
a. The LIKE operator is used to search for a specified pattern in a column. It supports wildcard characters such as ‘%’ for any sequence of characters and ‘_’ for a single character.

44. What is the purpose of the BETWEEN operator?
a. The BETWEEN operator is used to filter the result set within a range of values. It is inclusive, meaning it includes the boundary values specified.

45. What is a CASE statement in SQL?
a. The CASE statement is used to perform conditional logic in SQL queries. It allows you to return different values based on different conditions.

46. What is a view in SQL?
a. A view is a virtual table that provides a way to present data from one or more tables in a customized manner. It does not store data but provides a saved query for retrieval.

47. What is the difference between a view and a table?
a. A view is a virtual representation of data that does not store data itself but provides a way to query data from one or more tables. A table is a physical storage structure that holds data.

48. What is a trigger in SQL?
a. A trigger is a predefined action that is automatically executed in response to certain events on a table, such as INSERT, UPDATE, or DELETE operations.

49. What is a stored procedure in SQL?
a. A stored procedure is a precompiled set of SQL statements stored in the database that can be executed as a single unit. It allows for modular and reusable code.

50. What is a function in SQL?
a. A function is a stored program that can be used to perform calculations and return a value. It can be used in SQL statements and can accept parameters.

51. What is a cursor in SQL?
a. A cursor is a database object used to retrieve, manipulate, and navigate through a result set row by row. It allows for row-by-row processing of query results.

52. What is the difference between an implicit and an explicit cursor?
a. An implicit cursor is automatically created by the database when executing a query, while an explicit cursor is defined by the user and provides more control over query processing.

53. What is the purpose of the ROLLBACK statement?
a. The ROLLBACK statement is used to undo changes made during a transaction and revert the database to its previous state before the transaction began.

54. What is the purpose of the COMMIT statement?
a. The COMMIT statement is used to save all changes made during a transaction to the database permanently.

55. What is a temporary table?
a. A temporary table is a database table that is created and used temporarily within a session or a specific scope. It is automatically dropped when the session ends or the scope is exited.

56. What is a table variable?
a. A table variable is a type of variable that holds a table structure and can be used to store a set of rows temporarily within the scope of a stored procedure or function.

57. What is a normalization?
a. Normalization is the process of organizing data in a database to minimize redundancy and improve data integrity by dividing data into related tables.

58. What is denormalization?
a. Denormalization is the process of introducing redundancy into a database by combining tables to improve query performance and simplify data retrieval.

59. What is a data dictionary?
a. A data dictionary is a repository that contains metadata about the database, including information about tables, columns, data types, relationships, and constraints.

60. What is a schema in SQL?
a. A schema is a logical container within a database that holds objects like tables, views, procedures, and other database objects. It helps in organizing and managing database objects.

61. What is a database snapshot?
a. A database snapshot is a read-only, static view of a database at a specific point in time. It can be used for reporting and analysis without affecting the original database.

62. What is the purpose of the EXPLAIN command?
a. The EXPLAIN command is used to analyze and display the execution plan of a SQL query. It provides information about how the database engine will execute the query.

63. What is a SQL injection attack?
a. A SQL injection attack is a security vulnerability where an attacker can execute arbitrary SQL code by injecting malicious input into a query. It can compromise database security and integrity.

64. How can you prevent SQL injection attacks?
a. To prevent SQL injection attacks, use parameterized queries, prepared statements, and proper input validation to ensure user input is safely handled and does not alter the SQL query structure.

65. What is a rollback segment?
a. A rollback segment is a part of the database that stores old versions of data so that transactions can be rolled back if needed. It helps in maintaining data consistency and integrity.

66. What is a surrogate key?
a. A surrogate key is an artificial or synthetic key used to uniquely identify a record in a table. It is often used when no natural key exists or when natural keys are not suitable.

67. What is a natural key?
a. A natural key is a key that is derived from the actual data and has a real-world meaning, such as a social security number or email address.

68. What is a dimension table in a data warehouse?
a. A dimension table is a table in a data warehouse that contains descriptive attributes related to dimensions, such as time, location, or product. It is used to provide context for fact data.

69. What is a fact table in a data warehouse?
a. A fact table is a table in a data warehouse that contains quantitative data or measures, such as sales amounts or quantities. It is used to store and analyze performance metrics.

70. What is a star schema?
a. A star schema is a type of database schema used in data warehousing that consists of a central fact table surrounded by dimension tables. It is designed for efficient querying and reporting.

71. What is a snowflake schema?
a. A snowflake schema is a type of database schema used in data warehousing where dimension tables are normalized into multiple related tables. It is a more complex version of the star schema.

72. What is data mining?
a. Data mining is the process of discovering patterns, correlations, and insights from large datasets using statistical, mathematical, and machine learning techniques.

73. What is ETL?
a. ETL stands for Extract, Transform, Load. It is a process used to extract data from various sources, transform it into a suitable format, and load it into a data warehouse or database.

74. What is a materialized view?
a. A materialized view is a database object that stores the result of a query physically. It can be refreshed periodically to reflect changes in the underlying tables.

75. What is a database cursor?
a. A database cursor is a database object that allows for row-by-row processing of query results. It provides a way to iterate over and manipulate individual rows.

76. What is a database trigger?
a. A database trigger is a predefined action that is automatically executed in response to certain events on a table, such as INSERT, UPDATE, or DELETE operations.

77. What is a SQL JOIN?
a. A SQL JOIN is used to combine rows from two or more tables based on a related column. It allows for querying and retrieving data from multiple tables in a single query.

78. What is an SQL constraint?
a. An SQL constraint is a rule applied to a column or a set of columns in a table to enforce data integrity and restrict the type of data that can be inserted or updated.

79. What is the purpose of the GROUP BY clause?
a. The GROUP BY clause is used to group rows based on one or more columns and perform aggregate functions, such as COUNT, SUM, AVG, MIN, or MAX, on each group.

80. What is the purpose of the HAVING clause?
a. The HAVING clause is used to filter the result set of a GROUP BY query based on conditions applied to aggregated data.

81. What is a SQL schema?
a. A SQL schema is a logical container within a database that holds database objects, such as tables, views, and procedures. It

82. What is an aggregate function in SQL?
a. An aggregate function is a function that performs a calculation on a set of values and returns a single result. Examples include COUNT, SUM, AVG, MIN, and MAX.

83. What is the purpose of the ORDER BY clause?
a. The ORDER BY clause is used to sort the result set of a query based on one or more columns, either in ascending or descending order.

84. What is a SQL subquery?
a. A SQL subquery is a query nested within another query. It can be used to retrieve data from one table based on values from another table or to perform complex calculations.

85. What is a SQL view?
a. A SQL view is a virtual table that provides a way to present data from one or more tables in a customized manner. It does not store data but provides a saved query for retrieval.

86. What is a SQL function?
a. A SQL function is a stored program that performs a specific task and returns a value. It can be used in SQL statements and accepts parameters.

87. What is a SQL procedure?
a. A SQL procedure is a precompiled set of SQL statements stored in the database that can be executed as a single unit. It allows for modular and reusable code.

88. What is a SQL trigger?
a. A SQL trigger is a predefined action that is automatically executed in response to certain events on a table, such as INSERT, UPDATE, or DELETE operations.

89. What is a SQL index?
a. A SQL index is a database object that improves the speed of data retrieval operations on a table by providing a fast access path to the rows.

90. What is a SQL stored procedure?
a. A SQL stored procedure is a precompiled set of SQL statements that can be executed as a single unit. It allows for modular code and can accept parameters.

91. What is a SQL table variable?
a. A SQL table variable is a type of variable that holds a table structure and is used to store a set of rows temporarily within a stored procedure or function.

92. What is a SQL schema?
a. A SQL schema is a logical container within a database that holds database objects, such as tables, views, and procedures. It helps organize and manage database objects.

93. What is a SQL data dictionary?
a. A SQL data dictionary is a repository of metadata that contains information about the database, including tables, columns, data types, constraints, and relationships.

94. What is a SQL materialized view?
a. A SQL materialized view is a database object that stores the result of a query physically. It can be refreshed periodically to reflect changes in the underlying tables.

95. What is a SQL dimension table?
a. A SQL dimension table is a table in a data warehouse that contains descriptive attributes related to dimensions, such as time, location, or product. It provides context for fact data.

96. What is a SQL fact table?
a. A SQL fact table is a table in a data warehouse that contains quantitative data or measures, such as sales amounts or quantities. It is used to store and analyze performance metrics.

97. What is a SQL star schema?
a. A SQL star schema is a database schema used in data warehousing that consists of a central fact table surrounded by dimension tables. It is designed for efficient querying and reporting.

98. What is a SQL snowflake schema?
a. A SQL snowflake schema is a database schema used in data warehousing where dimension tables are normalized into multiple related tables. It is a more complex version of the star schema.

99. What is a SQL data warehouse?
a. A SQL data warehouse is a large repository of data collected from various sources, structured and organized to support business intelligence and reporting.

100. What is the purpose of the DISTINCT keyword in the SELECT statement?
a. The DISTINCT keyword in the SELECT statement is used to retrieve unique values from a column in the result set, eliminating duplicate rows.