Top 100 interview questions on SQL along with answers

Top 100 interview questions on SQL along with answers:

1. What is SQL? SQL stands for Structured Query Language, which is a programming language used to manage and manipulate relational databases.
2. What is a database? A database is a structured collection of data stored and organized in a way that allows easy access, retrieval, and management of the data.
3. What is a relational database? A relational database is a type of database that organizes data into tables consisting of rows and columns, with relationships defined between them.
4. What is a primary key? A primary key is a unique identifier for each record in a table. It ensures that each row in a table can be uniquely identified and provides a way to access specific records in the table.
5. What is a foreign key? A foreign key is a field or combination of fields in a table that refers to the primary key of another table. It establishes a link between two tables based on a common attribute.
6. What is a query in SQL? A query in SQL is a request for specific information from a database. It is used to retrieve, manipulate, and perform operations on data stored in tables.
7. What is the SELECT statement used for? The SELECT statement is used to retrieve data from one or more tables in a database. It allows you to specify the columns to retrieve, filter rows based on conditions, and sort the results.
8. What is the syntax for the SELECT statement? The basic syntax for the SELECT statement is:

sqlCopy code

SELECT column1, column2, ... FROM table_name WHERE condition;

9. How do you retrieve all columns from a table? To retrieve all columns from a table, you can use the asterisk (*) wildcard character in the SELECT statement. For example:

sqlCopy code

SELECT * FROM table_name;

10. What is the difference between WHERE and HAVING clauses? The WHERE clause is used to filter rows before grouping them, while the HAVING clause is used to filter groups after they have been formed.
11. What is the difference between DELETE and TRUNCATE? The DELETE statement is used to remove specific rows from a table, while the TRUNCATE statement is used to remove all rows from a table, effectively resetting the table.
12. What is the difference between INNER JOIN and OUTER JOIN? An INNER JOIN returns only the matching rows from both tables, while an OUTER JOIN returns all rows from one table and the matching rows from the other table(s).
13. What is a subquery? A subquery is a query nested within another query. It is used to retrieve data based on the results of another query and can be placed in the SELECT, FROM, WHERE, or HAVING clause.
14. What is a self-join? A self-join is a join operation where a table is joined with itself. It is useful when you want to combine rows from a table based on a related column within the same table.
15. What is the UNION operator used for? The UNION operator is used to combine the results of two or more SELECT statements into a single result set. It removes duplicate rows by default.
16. What is the difference between UNION and UNION ALL? UNION removes duplicate rows from the result set, while UNION ALL does not. UNION ALL simply combines all rows from the involved SELECT statements.
17. What is the ORDER BY clause used for? The ORDER BY clause is used to sort the result set based on one or more columns. It can sort the data in ascending (default) or descending order.
18. What is the GROUP BY clause used for? The GROUP BY clause is used to group rows based on one or more columns. It is typically used in combination with aggregate functions like SUM, COUNT, AVG, etc.
19. What is the difference between GROUP BY and HAVING clauses? The GROUP BY clause is used to group rows before applying any filtering, while the HAVING clause is used to filter groups after they have been formed.
20. What is a stored procedure? A stored procedure is a named set of SQL statements that are stored and can be executed on the database server. It allows for code reusability and improves performance.
21. What is a view? A view is a virtual table derived from one or more tables or views. It does not store data itself but retrieves data from the underlying tables based on the defined query.
22. What is normalization? Normalization is the process of organizing data in a database to eliminate redundancy and dependency issues. It involves breaking large tables into smaller ones and establishing relationships between them.
23. What are the different levels of normalization? The different levels of normalization are:

• First Normal Form (1NF)
• Second Normal Form (2NF)
• Third Normal Form (3NF)
• Fourth Normal Form (4NF)
• Fifth Normal Form (5NF)

24. What is an index? An index is a database object that improves the speed of data retrieval operations on a table. It is created on one or more columns and allows for faster searching and sorting of data.
25. What is a transaction? A transaction is a sequence of database operations that are treated as a single unit. It ensures that all operations within the transaction are executed together and can be rolled back if necessary.
26. What is the ACID property in database systems? ACID stands for Atomicity, Consistency, Isolation, and Durability. It is a set of properties that guarantee reliable processing of database transactions.
27. What is a deadlock? A deadlock is a situation where two or more transactions are waiting indefinitely for each other to release resources, resulting in a standstill and preventing further progress.
28. What is a cursor? A cursor is a database object used to retrieve and manipulate data row by row. It allows for fine-grained control over result sets and enables navigation and modification of data.
29. What is the difference between a clustered and non-clustered index? 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 data in the table.
30. What is the LIKE operator used for? The LIKE operator is used in the WHERE clause to search for a specified pattern in a column. It is commonly used with wildcard characters (% and _).
31. What are the wildcard characters in SQL? The wildcard characters in SQL are:

• % (percentage sign): Matches any sequence of characters.
• _ (underscore): Matches any single character.

32. What is a NULL value? A NULL value represents the absence of a value in a database column. It is not the same as zero or an empty string and requires special handling in SQL queries.
33. What is the difference between UNION and JOIN? UNION combines rows from different tables into a single result set, while JOIN combines columns from different tables based on a related column.
34. What is a constraint in SQL? A constraint is a rule defined on a column or a table to enforce data integrity. It can be used to specify conditions that the data must satisfy, such as uniqueness or referential integrity.
35. What is the difference between a unique constraint and a primary key? A unique constraint ensures that all values in a column are unique, while a primary key is a unique identifier for each row in a table and cannot contain NULL values.
36. What is the difference between a correlated and non-correlated subquery? A correlated subquery is a subquery that refers to a column from the outer query, while a non-correlated subquery can be executed independently of the outer query.
37. What is a temporary table? A temporary table is a table that is created and exists only for the duration of a session or a transaction. It is used to store and manipulate temporary data.
38. What is the COALESCE function used for? The COALESCE function is used to return the first non-null expression in a list. It is often used to replace NULL values with alternative values.
39. What is the difference between UNION and UNION ALL? UNION removes duplicate rows from the result set, while UNION ALL does not. UNION ALL simply combines all rows from the involved SELECT statements.
40. What is the difference between a correlated and non-correlated subquery? A correlated subquery is a subquery that refers to a column from the outer query, while a non-correlated subquery can be executed independently of the outer query.
41. What is a temporary table? A temporary table is a table that is created and exists only for the duration of a session or a transaction. It is used to store and manipulate temporary data.
42. What is the COALESCE function used for? The COALESCE function is used to return the first non-null expression in a list. It is often used to replace NULL values with alternative values.
43. What is the difference between a primary key and a unique key? A primary key is a unique identifier for each row in a table and cannot contain NULL values. A unique key, on the other hand, enforces uniqueness but allows NULL values.
44. 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 specify different actions based on different conditions.
45. What is the difference between a subquery and a join? A subquery is a query nested within another query, while a join combines columns from different tables based on a related column.
46. What is the difference between a cross join and an inner join? A cross join (or Cartesian join) returns the Cartesian product of two tables, while an inner join returns only the matching rows from both tables.
47. What is the difference between CHAR and VARCHAR data types? CHAR is a fixed-length character data type that always occupies the specified length, while VARCHAR is a variable-length character data type that only uses the necessary storage.
48. What is the difference between TRUNCATE and DELETE? TRUNCATE is a DDL command that removes all rows from a table and resets the table, while DELETE is a DML command that removes specific rows from a table.
49. What is the difference between the EXISTS and IN operators? The EXISTS operator checks for the existence of any rows returned by a subquery, while the IN operator checks if a value matches any value in a list or subquery.
50. What is the purpose of the DISTINCT keyword? The DISTINCT keyword is used to remove duplicate rows from the result set of a SELECT statement.
51. What is the purpose of the GROUP BY ROLLUP clause? The GROUP BY ROLLUP clause is used to generate multiple levels of grouping in a result set. It produces a result set that includes subtotals and grand totals.
52. What is the purpose of the WITH clause in SQL? The WITH clause, also known as a Common Table Expression (CTE), is used to create temporary named result sets that can be referenced multiple times in a query.
53. What is the purpose of the ROW_NUMBER() function? The ROW_NUMBER() function assigns a unique sequential number to each row in the result set. It is often used for pagination or ranking purposes.
54. What is the difference between the UNION and UNION ALL operators? The UNION operator removes duplicate rows from the result set, while the UNION ALL operator includes all rows from both SELECT statements.
55. What is the purpose of the TOP keyword? The TOP keyword is used to limit the number of rows returned by a SELECT statement. It allows you to specify the number or percentage of rows to retrieve.
56. What is the purpose of the OFFSET-FETCH clause? The OFFSET-FETCH clause is used to implement result set pagination. It allows you to skip a specified number of rows and retrieve a specified number of rows from a result set.
57. What is a correlated update? A correlated update is an UPDATE statement that references another table in the WHERE clause to update the rows based on a related condition.
58. What is the purpose of the MERGE statement? The MERGE statement is used to perform both INSERT and UPDATE operations on a target table based on a condition specified in the source table.
59. What is the purpose of the LAG function? The LAG function is used to access data from a previous row in the result set. It is often used to calculate the difference between the current and previous values.
60. What is the purpose of the LEAD function? The LEAD function is used to access data from a following row in the result set. It is often used to calculate the difference between the current and next values.
61. What is the purpose of the PIVOT operator? The PIVOT operator is used to rotate rows into columns, creating a cross-tabulation or a summary report from a table with repeated values.
62. What is the purpose of the UNPIVOT operator? The UNPIVOT operator is used to convert columns into rows, essentially reversing the operation performed by the PIVOT operator.
63. What is the purpose of the CROSS APPLY operator? The CROSS APPLY operator is used to invoke a table-valued function for each row of a table expression, returning the combined result set.
64. What is the purpose of the STRING_AGG function? The STRING_AGG function is used to concatenate values from a column into a single string, separated by a specified delimiter.
65. What is the purpose of the JSON_VALUE function? The JSON_VALUE function is used to extract a scalar value from a JSON string based on a specified JSON path.
66. What is the purpose of the JSON_QUERY function? The JSON_QUERY function is used to extract an object or an array from a JSON string based on a specified JSON path.
67. What is the purpose of the JSON_MODIFY function? The JSON_MODIFY function is used to modify a JSON string by adding, updating, or deleting values based on a specified JSON path.
68. What is the purpose of the STRING_SPLIT function? The STRING_SPLIT function is used to split a string into multiple rows based on a specified separator.
69. What is the purpose of the TRY...CATCH block? The TRY...CATCH block is used for error handling in SQL Server. It allows you to catch and handle exceptions that occur within a specific block of code.
70. 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 replace NULL values with alternative values.
71. What is the purpose of the CONCAT function? The CONCAT function is used to concatenate two or more strings together into a single string.
72. What is the purpose of the DATEADD function? The DATEADD function is used to add a specified number of units (such as days, months, or years) to a date.
73. What is the purpose of the DATEDIFF function? The DATEDIFF function is used to calculate the difference between two dates in terms of a specified interval (such as days, months, or years).
74. What is the purpose of the GETDATE function? The GETDATE function is used to retrieve the current system date and time from the database server.
75. What is the purpose of the DATEPART function? The DATEPART function is used to extract a specific part (such as the year, month, or day) from a date.
76. What is the purpose of the CAST and CONVERT functions? The CAST and CONVERT functions are used to convert one data type to another in SQL Server.
77. What is the purpose of the RANK function? The RANK function is used to assign a rank to each row within a result set, based on the specified ranking criteria.
78. What is the purpose of the NTILE function? The NTILE function is used to divide a result set into a specified number of approximately equal-sized groups or buckets.
79. What is the purpose of the PARTITION BY clause? The PARTITION BY clause is used to divide a result set into partitions or groups based on one or more columns. It is often used with window functions.
80. What is the purpose of the ROWS BETWEEN clause? The ROWS BETWEEN clause is used to define a window or a subset of rows within a result set for window functions.
81. What is the purpose of the OVER clause? The OVER clause is used to define a window or a subset of rows within a result set for window functions.
82. What is the purpose of the LEAD and LAG functions? The LEAD and LAG functions are used to access data from preceding or following rows in a result set. They are often used to calculate differences or compare values.
83. What is the purpose of the FIRST_VALUE and LAST_VALUE functions? The FIRST_VALUE and LAST_VALUE functions are used to retrieve the first or last value in a window defined by the OVER clause.
84. What is the purpose of the ROLLUP and CUBE operators? The ROLLUP and CUBE operators are used to generate multiple levels of grouping in a result set, producing subtotals and grand totals.
85. What is the purpose of the STRING_AGG function? The STRING_AGG function is used to concatenate values from a column into a single string, separated by a specified delimiter.
86. What is the purpose of the TOP-N query? The TOP-N query is used to retrieve the top N rows from a result set, based on a specified ordering criteria.
87. What is the purpose of the PIVOT operator? The PIVOT operator is used to rotate rows into columns, creating a cross-tabulation or a summary report from a table with repeated values.
88. What is the purpose of the UNPIVOT operator? The UNPIVOT operator is used to convert columns into rows, essentially reversing the operation performed by the PIVOT operator.
89. What is the purpose of the EXISTS operator? 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.
90. What is the purpose of the APPLY operator? The APPLY operator is used to invoke a table-valued function for each row of a table expression, returning the combined result set.
91. What is the purpose of the FOR XML clause? The FOR XML clause is used to return the result of a SELECT statement as XML data.
92. What is the purpose of the FOR JSON clause? The FOR JSON clause is used to return the result of a SELECT statement as JSON data.
93. What is the purpose of the MERGE statement? The MERGE statement is used to perform both INSERT and UPDATE operations on a target table based on a condition specified in the source table.
94. What is the purpose of the TRIGGER in SQL? A trigger is a special type of stored procedure that is automatically executed or fired in response to a specific event (such as an INSERT, UPDATE, or DELETE operation) on a table.
95. What is the purpose of the CURSOR in SQL? A cursor is a database object used to retrieve and manipulate data row by row. It allows for fine-grained control over result sets and enables navigation and modification of data.
96. What is the purpose of the SQL Server Agent? The SQL Server Agent is a Microsoft SQL Server component that is responsible for the scheduling and execution of jobs, including automated backups, database maintenance tasks, and data import/export operations.
97. What is the purpose of the CHECKPOINT process in SQL Server? The CHECKPOINT process in SQL Server is responsible for writing dirty pages (modified pages) from memory to disk and updating the transaction log, ensuring data durability and recovery.
98. What is the purpose of the LOG SHIPPING feature in SQL Server? Log shipping is a high-availability and disaster recovery solution in SQL Server that involves automatically sending transaction log backups from a primary database to one or more secondary databases for standby purposes.
99. What is the purpose of the FULL recovery model in SQL Server? The FULL recovery model is a database recovery model in SQL Server that allows for point-in-time recovery and requires regular transaction log backups in addition to full and differential backups.
100. What is the purpose of the FILESTREAM feature in SQL Server? The FILESTREAM feature in SQL Server allows for the storage and management of unstructured data (such as documents, images, and audio/video files) directly in the file system, while still providing transactional consistency and integration with relational data.

Top of Form