Top 115 SQL Interview Questions and Answers for 2024
1. What is SQL?
Answer: SQL (Structured Query Language) is a standard programming language used for managing and manipulating relational databases. It allows users to query, insert, update, and delete data from a database.
2. What is a database?
Answer: A database is an organized collection of data, generally stored and accessed electronically from a computer system. It provides a systematic way to store, retrieve, and manage data.
3. What is a primary key?
Answer: A primary key is a unique identifier for a record in a database table. It ensures that each record can be uniquely identified and is usually indexed for faster access.
4. What is a foreign key?
Answer: A foreign key is a field in one table that uniquely identifies a row of another table. It establishes and enforces a link between the data in two tables.
5. What is normalization?
Answer: Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing a database into two or more tables and defining relationships between them.
6. What are the different types of joins in SQL?
Answer: The different types of joins are:
- INNER JOIN: Returns records with matching values in both tables.
- LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table and the matched records from the right table.
- RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table and the matched records from the left table.
- FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either table.
- CROSS JOIN: Returns the Cartesian product of both tables.
7. What is a subquery?
Answer: A subquery is a query nested inside another query. It can be used to perform operations that need to be evaluated before the outer query.
8. What is an index?
Answer: An index is a database object that improves the speed of data retrieval operations on a table. It is created on a table’s columns to allow faster searches and queries.
9. What is a view?
Answer: A view is a virtual table based on the result of an SQL query. It does not store data physically but presents data from one or more tables as if it were a table itself.
10. What is an SQL injection?
Answer: SQL injection is a code injection technique that exploits vulnerabilities in an application’s software by inserting malicious SQL statements into an entry field, potentially compromising database security.
11. What is a stored procedure?
Answer: A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a single unit. It helps in improving performance and managing repetitive tasks.
12. What is a trigger?
Answer: A trigger is a set of SQL statements that automatically execute or fire when certain events occur in a database, such as insertions, updates, or deletions.
13. What is a transaction?
Answer: A transaction is a sequence of one or more SQL operations executed as a single unit of work. It ensures data integrity and consistency through properties known as ACID (Atomicity, Consistency, Isolation, Durability).
14. What is the difference between UNION and UNION ALL?
Answer:
- UNION: Combines the results of two or more SELECT queries and removes duplicate rows.
- UNION ALL: Combines the results of two or more SELECT queries, including all duplicates.
15. What is a data type?
Answer: A data type specifies the type of data that a column in a database can hold, such as INTEGER, VARCHAR, DATE, etc.
16. Explain the concept of ACID properties in a transaction.
Answer: ACID properties ensure reliable processing of database transactions:
- Atomicity: All parts of a transaction are completed successfully or none at all.
- Consistency: The database remains in a consistent state before and after the transaction.
- Isolation: Transactions are executed independently and transparently.
- Durability: Once a transaction is committed, it remains committed, even in the case of a system failure.
17. What is a SQL Server?
Answer: SQL Server is a relational database management system developed by Microsoft. It supports a wide range of transaction processing, business intelligence, and analytics applications.
18. How do you retrieve unique values from a column?
Answer: You can retrieve unique values using the DISTINCT keyword. For example:
sqlCopy codeSELECT DISTINCT column_name FROM table_name;
19. What is a schema?
Answer: A schema is a collection of database objects, such as tables, views, and indexes, that are related to each other. It defines the structure and organization of the database.
20. What is the difference between DELETE and TRUNCATE?
Answer:
- DELETE: Removes rows from a table based on a condition. It can be rolled back and triggers are fired.
- TRUNCATE: Removes all rows from a table and cannot be rolled back. It is faster than DELETE and does not fire triggers.
21. What is a relational database?
Answer: A relational database organizes data into tables (relations) that can be linked—or related—based on common data attributes. It uses SQL for managing and querying data.
22. How do you update a record in SQL?
Answer: You can update a record using the UPDATE statement. For example:
sqlCopy codeUPDATE table_name
SET column_name = value
WHERE condition;
23. What is a candidate key?
Answer: A candidate key is a column or set of columns that can uniquely identify a record in a table. Each table can have multiple candidate keys, but only one primary key.
24. What is the difference between CHAR and VARCHAR data types?
Answer:
- CHAR: Fixed-length string data type. Padding is added if the input is shorter than the specified length.
- VARCHAR: Variable-length string data type. It only uses as much storage as required by the actual string value.
25. What is a composite key?
Answer: A composite key is a primary key that consists of two or more columns used together to uniquely identify a record in a table.
26. What is denormalization?
Answer: Denormalization is the process of introducing redundancy into a database to improve read performance. It involves merging tables to reduce the number of joins needed in queries.
27. How do you create a table in SQL?
Answer: You create a table using the CREATE TABLE statement. For example:
sqlCopy codeCREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);
28. What is the difference between an inner join and an outer join?
Answer:
- Inner Join: Returns rows with matching values in both tables.
- Outer Join: Returns rows with matching values and also includes rows from one table that do not have matching rows in the other table.
29. What is a self-join?
Answer: A self-join is a join where a table is joined with itself. It is useful for querying hierarchical data or comparing rows within the same table.
30. What is indexing in SQL?
Answer: Indexing improves the speed of data retrieval operations on a database table. An index is a data structure that provides quick access to rows based on column values.
31. What are SQL constraints?
Answer: SQL constraints are rules applied to columns or tables to enforce data integrity. Common constraints include PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK.
32. What is a CTE (Common Table Expression)?
Answer: A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It is defined using the WITH keyword.
33. How do you find the number of records in a table?
Answer: You find the number of records using the COUNT function. For example:
sqlCopy codeSELECT COUNT(*) FROM table_name;
34. What is the purpose of the GROUP BY clause?
Answer: The GROUP BY clause groups rows that have the same values in specified columns into aggregated data, often used with aggregate functions like COUNT, SUM, AVG, etc.
35. What is the HAVING clause?
Answer: The HAVING clause is used to filter records after the GROUP BY clause has been applied. It is similar to the WHERE clause but is used for aggregated data.
36. What is the purpose of the ORDER BY clause?
Answer: The ORDER BY clause is used to sort the result set of a query by one or more columns in ascending or descending order.
37. What are aggregate functions?
Answer: Aggregate functions perform a calculation on a set of values and return a single value. Common aggregate functions include COUNT, SUM, AVG, MIN, and MAX.
38. What is a data warehouse?
Answer: A data warehouse is a centralized repository that stores large volumes of historical and current data from multiple sources. It is used for data analysis and reporting.
39. What is the difference between OLAP and OLTP?
Answer:
- OLAP (Online Analytical Processing): Optimized for complex queries and analysis. Used for data mining and reporting.
- OLTP (Online Transaction Processing): Optimized for handling large numbers of short online transaction queries. Used for day-to-day operations.
40. What is a pivot table?
Answer: A pivot table is a data processing tool used in SQL to summarize and aggregate data from a table, making it easier to analyze and present information.
41. What is a UNION query?
Answer: A UNION query combines the results of two or more SELECT queries into a single result set, eliminating duplicate rows.
42. What is a recursive query?
Answer: A recursive query is a query that references itself. It is often used to retrieve hierarchical data, such as organizational charts or file systems.
43. What is the difference between a DELETE and a DROP statement?
Answer:
- DELETE: Removes rows from a table but retains the table structure for future use.
- DROP: Removes the entire table or database, including its structure and data.
44. How do you handle null values in SQL?
Answer: Null values represent missing or unknown data. You handle null values using functions like IS NULL, IS NOT NULL, COALESCE, and IFNULL.
45. What is the purpose of the LIMIT clause?
Answer: The LIMIT clause restricts the number of rows returned by a query. It is commonly used to retrieve a subset of results.
46. What is a database trigger?
Answer: A database trigger is a stored procedure that is automatically executed in response to certain events on a table, such as INSERT, UPDATE, or DELETE.
47. What is the purpose of the DISTINCT keyword?
Answer: The DISTINCT keyword is used to remove duplicate rows from the result set of a query.
48. What is the difference between a view and a table?
Answer:
- View: A virtual table based on the result of a query. It does not store data physically.
- Table: A database object that stores data physically.
49. How do you perform a full-text search in SQL?
Answer: Full-text search is performed using the CONTAINS or FREETEXT functions in SQL Server, or similar functions in other RDBMS like MATCH in MySQL.
50. What is a stored function?
Answer: A stored function is a reusable piece of code that returns a single value. It can be used in queries and other database operations.
51. What is the difference between UNION and INTERSECT?
Answer:
- UNION: Combines results from two queries and removes duplicates.
- INTERSECT: Returns the common rows between two queries.
52. What is an EXPLAIN plan?
Answer: An EXPLAIN plan is a tool used to analyze and optimize SQL queries by showing how the database engine executes a query, including the order of operations and indexes used.
53. What is the purpose of the ALTER TABLE statement?
Answer: The ALTER TABLE statement is used to modify the structure of an existing table, such as adding or dropping columns or changing column data types.
54. What is the difference between an implicit and explicit join?
Answer:
- Implicit Join: Uses comma-separated table names in the
FROMclause and specifies join conditions in theWHEREclause. - Explicit Join: Uses the
JOINkeyword to define the relationship between tables in theFROMclause.
55. What are the different types of constraints in SQL?
Answer: Common constraints include:
- PRIMARY KEY
- FOREIGN KEY
- UNIQUE
- NOT NULL
- CHECK
56. What is a composite index?
Answer: A composite index is an index on multiple columns of a table. It improves query performance when searching on multiple columns.
57. What is a schema in a database?
Answer: A schema is a blueprint of how a database is constructed, including tables, columns, relationships, and constraints.
58. What is the difference between a clustered and non-clustered index?
Answer:
- Clustered Index: Determines the physical order of data in the table. Each table can have only one clustered index.
- Non-clustered Index: Creates a logical order for data rows and uses pointers to the physical rows. A table can have multiple non-clustered indexes.
59. What is the difference between SQL and NoSQL databases?
Answer:
- SQL Databases: Relational, use structured schema, and SQL for querying.
- NoSQL Databases: Non-relational, flexible schema, and various data models like document, key-value, column-family, and graph.
60. What is the purpose of the DROP TABLE statement?
Answer: The DROP TABLE statement is used to remove a table and its data permanently from the database.
61. What is the purpose of the TRUNCATE TABLE statement?
Answer: The TRUNCATE TABLE statement removes all rows from a table, but the table structure remains intact for future use.
62. What are SQL aggregate functions?
Answer: SQL aggregate functions perform calculations on a set of values and return a single result. Examples include SUM, AVG, COUNT, MIN, and MAX.
63. What is a cursor in SQL?
Answer: A cursor is a database object used to retrieve, manipulate, and navigate through a result set row by row.
64. What is the purpose of the SQL Server Profiler?
Answer: SQL Server Profiler is a tool used to monitor and analyze SQL Server events, including performance issues and query execution.
65. What is the difference between a procedure and a function in SQL?
Answer:
- Procedure: A stored procedure performs actions but does not return a value.
- Function: A stored function returns a single value and can be used in SQL queries.
66. What is the difference between a correlated subquery and a non-correlated subquery?
Answer:
- Correlated Subquery: Refers to columns from the outer query and executes once for each row of the outer query.
- Non-correlated Subquery: Independent of the outer query and executes once.
67. What is a dynamic SQL query?
Answer: A dynamic SQL query is constructed and executed at runtime based on user input or other variables.
68. What is the purpose of the SQL Server Management Studio (SSMS)?
Answer: SQL Server Management Studio (SSMS) is an integrated environment for managing SQL Server databases, including development, querying, and administration tasks.
69. What is a window function in SQL?
Answer: A window function performs calculations across a set of table rows related to the current row, such as ranking, running totals, and moving averages.
70. What is the purpose of the MERGE statement?
Answer: The MERGE statement combines INSERT, UPDATE, and DELETE operations into a single statement, allowing for conditional processing based on source and target data.
71. What is a surrogate key?
Answer: A surrogate key is an artificial or synthetic key used to uniquely identify a record, often implemented as an auto-incremented number.
72. What is a data mart?
Answer: A data mart is a subset of a data warehouse, typically focused on a specific business area or department, providing specialized data for analysis and reporting.
73. What is a transaction log?
Answer: A transaction log records all changes made to the database, providing a way to recover data and maintain database integrity in case of a failure.
74. What is a materialized view?
Answer: A materialized view is a precomputed table comprising the results of a query. It stores the data physically and can be refreshed periodically.
75. What is data mining?
Answer: Data mining is the process of discovering patterns and knowledge from large amounts of data using statistical, mathematical, and machine learning techniques.
76. What is the purpose of the EXISTS keyword?
Answer: The EXISTS keyword is used to test for the existence of rows in a subquery. It returns TRUE if the subquery returns one or more rows.
77. What is a join condition?
Answer: A join condition specifies how tables are related and which columns are used to match rows from different tables in a join operation.
78. What is a schema in a database?
Answer: A schema defines the structure of a database, including tables, columns, relationships, and constraints. It acts as a blueprint for the database.
79. What are the different types of joins in SQL?
Answer: Common types of joins include:
- INNER JOIN
- LEFT JOIN (or LEFT OUTER JOIN)
- RIGHT JOIN (or RIGHT OUTER JOIN)
- FULL JOIN (or FULL OUTER JOIN)
80. What is a primary key constraint?
Answer: A primary key constraint ensures that each row in a table has a unique, non-null identifier. It uniquely identifies records and enforces entity integrity.
81. What is a foreign key constraint?
Answer: A foreign key constraint enforces a link between columns in two tables, ensuring that values in the foreign key column match values in the primary key column of the related table.
82. What is normalization?
Answer: Normalization is the process of organizing database columns and tables to reduce data redundancy and improve data integrity. It involves dividing a database into two or more tables and defining relationships between them.
83. What is denormalization?
Answer: Denormalization is the process of combining tables to improve query performance at the cost of data redundancy. It involves merging tables to reduce the number of joins needed.
84. What is the purpose of indexing?
Answer: Indexing improves the speed of data retrieval operations on a database table by creating a data structure that allows faster access to rows based on column values.
85. What is a cross join?
Answer: A cross join returns the Cartesian product of two tables, meaning every row in the first table is combined with every row in the second table.
86. What is a self-join?
Answer: A self-join is a join operation where a table is joined with itself. It is useful for querying hierarchical data or comparing rows within the same table.
87. What is a trigger in SQL?
Answer: A trigger is a stored procedure that automatically executes in response to certain events on a table, such as INSERT, UPDATE, or DELETE operations.
88. What is the difference between a HAVING and a WHERE clause?
Answer:
- HAVING: Filters records after aggregation (GROUP BY).
- WHERE: Filters records before aggregation.
89. What is a subquery?
Answer: A subquery is a query nested inside another query. It provides a way to use the result of one query as a condition for another query.
90. What is a non-clustered index?
Answer: A non-clustered index creates a separate data structure that points to the physical data rows. It allows multiple indexes on a table and improves query performance for specific columns.
91. What is a clustered index?
Answer: A clustered index determines the physical order of data rows in a table. There can be only one clustered index per table, and it affects the table’s physical storage.
92. What is the purpose of the GROUP BY clause?
Answer: The GROUP BY clause groups rows that have the same values into summary rows, such as aggregating data for each group.
93. What is the purpose of the ORDER BY clause?
Answer: The ORDER BY clause sorts the result set of a query by one or more columns in ascending or descending order.
94. What is the difference between TRUNCATE and DELETE?
Answer:
- TRUNCATE: Removes all rows from a table quickly and resets identity columns.
- DELETE: Removes rows one at a time and can include a
WHEREclause to specify which rows to remove.
95. What is the purpose of the COALESCE function?
Answer: The COALESCE function returns the first non-null value from a list of expressions. It is used to handle null values and provide default values.
96. What is a user-defined function (UDF)?
Answer: A user-defined function (UDF) is a custom function created by the user to perform specific operations and return a result. It can be used in SQL queries like built-in functions.
97. What is a temporary table?
Answer: A temporary table is a table that exists only for the duration of a database session or transaction. It is used to store intermediate results temporarily.
98. What is a view in SQL?
Answer: A view is a virtual table that provides a specific representation of data from one or more tables. It does not store data itself but retrieves data based on a query.
99. What is the purpose of the BETWEEN operator?
Answer: The BETWEEN operator is used to filter results within a specified range, including the boundary values.
100. What is a correlated subquery?
Answer: A correlated subquery is a subquery that references columns from the outer query. It is executed once for each row selected by the outer query.
101. What is a non-correlated subquery?
Answer: A non-correlated subquery is a subquery that does not reference columns from the outer query. It is executed only once and returns a result set used by the outer query.
102. What is a composite key?
Answer: A composite key is a primary key composed of two or more columns used together to uniquely identify a record in a table.
103. What is a data type in SQL?
Answer: A data type defines the kind of data a column can hold, such as integers, strings, dates, or floating-point numbers. Examples include INT, VARCHAR, DATE, and FLOAT.
104. What is the difference between VARCHAR and CHAR data types?
Answer:
- VARCHAR: Variable-length string. It stores only the actual length of the data plus a small overhead.
- CHAR: Fixed-length string. It pads the data with spaces if it is shorter than the defined length.
105. What is a foreign key in SQL?
Answer: A foreign key is a column or set of columns in a table that uniquely identifies rows in another table. It establishes and enforces a link between the two tables.
106. What is a schema in SQL?
Answer: A schema is a collection of database objects, including tables, views, indexes, and procedures. It provides a logical grouping and organization of database elements.
107. What is an index in SQL?
Answer: An index is a database object that improves the speed of data retrieval operations on a table by creating a data structure that allows fast access to rows based on column values.
108. What is a primary key in SQL?
Answer: A primary key is a unique identifier for a record in a table. It ensures that each row has a distinct value and is not null.
109. What is normalization in SQL?
Answer: Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing tables into related tables and defining relationships between them.
110. What is denormalization in SQL?
Answer: Denormalization is the process of combining tables to improve performance, often by reducing the number of joins needed for queries. It may increase data redundancy.
111. What is a constraint in SQL?
Answer: A constraint is a rule applied to columns in a table to enforce data integrity. Examples include primary key constraints, foreign key constraints, and unique constraints.
112. What is a subquery in SQL?
Answer: A subquery is a query nested inside another query. It allows you to perform operations using the result of the inner query as input for the outer query.
113. What is a stored procedure?
Answer: A stored procedure is a precompiled collection of SQL statements and optional control-of-flow statements that can be executed as a unit. It is used to encapsulate repetitive tasks and business logic.
114. What is an SQL injection attack?
Answer: An SQL injection attack is a security vulnerability that allows an attacker to interfere with the queries an application makes to its database. It can lead to unauthorized access, data leakage, and other malicious actions.
115. How can you prevent SQL injection attacks?
Answer: To prevent SQL injection attacks, use parameterized queries, stored procedures, and input validation. Avoid concatenating user input directly into SQL queries and ensure proper escaping of special characters.
These questions cover a broad spectrum of SQL topics, from basic concepts to more advanced queries and techniques. For a complete and interactive experience, consider adding visual aids like diagrams, screenshots, and example code snippets to enhance understanding.
Other Important Q&A List :
- OOPs Interview Questions And Answers
- SQL Interview Questions and Answers
- Content Writing Interview Questions and Answers
- Email Marketing Interview Questions and Answers
- Django Interview Questions and Answers
- C Programming Interview Questions and Answers
- ReactJS Interview Questions and Answers
- Angular Interview Questions and Answers
- Power BI Interview Questions and Answers
- Top DBMS Interview Questions and Answers
- Python Interview Questions and Answers
- MySQL Interview Questions and Answers
- Java Interview Questions and Answers
- Data Structures Interview Questions
- Flutter Interview Questions and Answers
- JavaScript Interview Questions and Answers
- PHP Interview Questions and Answers
- CSS Interview Questions and Answers
- HTML Interview Questions and Answers
