100 SQL interview questions with their answers:

1. What is SQL?

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

2. What are the different types of SQL commands?

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

3. What is the difference between DELETE, TRUNCATE, and DROP?

• DELETE: Removes rows from a table but retains the table structure and can be rolled back.
• TRUNCATE: Removes all rows from a table and cannot be rolled back.
• DROP: Deletes the table structure and all data permanently.

4. What is a primary key?

A primary key uniquely identifies each record in a database table. It must be unique and not null.

5. What is a foreign key?

A foreign key is a column or set of columns in a table that links to the primary key of another table, ensuring referential integrity.

6. What is a join in SQL?

A join is used to combine rows from two or more tables based on a related column between them. Types of joins:

• INNER JOIN: Returns rows with matching values in both tables.
• LEFT JOIN: Returns all rows from the left table and matched rows from the right table.
• RIGHT JOIN: Returns all rows from the right table and matched rows from the left table.
• FULL OUTER JOIN: Returns all rows when there is a match in one of the tables.

7. What is the difference between INNER JOIN and OUTER JOIN?

• INNER JOIN: Returns only rows where there is a match in both tables.
• OUTER JOIN: Returns all rows from one table and matched rows from the other. If no match, NULLs are returned.

8. What are indexes in SQL?

Indexes are used to speed up the retrieval of data from a table. They are created on columns that are frequently queried.

9. What is normalization in SQL?

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller tables.

10. What is denormalization?

Denormalization is the process of combining tables to improve performance by reducing the number of joins required to retrieve data.

11. What is the difference between WHERE and HAVING?

• WHERE: Filters records before any groupings are made.
• HAVING: Filters records after groupings are made (used with aggregate functions).

12. What is an aggregate function?

Aggregate functions perform a calculation on a set of values and return a single value. Examples: COUNT(), SUM(), AVG(), MAX(), MIN().

13. What is a subquery?

A subquery is a query within another query. It can be used in the SELECT, INSERT, UPDATE, or DELETE statements.

14. What is a view in SQL?

A view is a virtual table based on the result set of a SQL query. It does not store data but provides a way to access data.

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

• UNION: Combines the result sets of two queries and removes duplicates.
• UNION ALL: Combines the result sets of two queries without removing duplicates.

16. What is a stored procedure?

A stored procedure is a precompiled set of SQL statements that can be executed as a single unit.

17. What is a trigger in SQL?

A trigger is a set of SQL statements that automatically execute when a specific event occurs on a particular table or view.

18. What is a transaction in SQL?

A transaction is a sequence of one or more SQL operations executed as a single unit. It ensures data integrity. Transactions are controlled with COMMIT, ROLLBACK, and SAVEPOINT.

19. What is a cursor in SQL?

A cursor is a database object used to retrieve, manipulate, and traverse the rows of a result set.

20. What is the difference between IS NULL and = NULL?

• IS NULL: Used to check if a column has a NULL value.
• = NULL: Incorrect syntax; NULL cannot be directly compared using =.

21. What are constraints in SQL?

Constraints are rules applied to columns in a table to ensure the accuracy and integrity of the data. Examples: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK.

22. What is the difference between CHAR and VARCHAR?

• CHAR: A fixed-length character data type.
• VARCHAR: A variable-length character data type.

23. What is the difference between DELETE and TRUNCATE?

• DELETE: Deletes rows from a table and can be rolled back.
• TRUNCATE: Removes all rows and cannot be rolled back.

24. What is an alias in SQL?

An alias is a temporary name given to a table or column for the duration of a query. You can use the AS keyword to define an alias.

25. What is the BETWEEN operator in SQL?

The BETWEEN operator is used to filter the result set within a certain range of values. It is inclusive of the boundary values.

26. What is the IN operator in SQL?

The IN operator is used to check if a value matches any value in a list or subquery.

27. What is the LIKE operator in SQL?

The LIKE operator is used to search for a specified pattern in a column. It is often used with wildcard characters % (matches zero or more characters) and _ (matches a single character).

28. What is the EXISTS operator in SQL?

The EXISTS operator checks if a subquery returns any records. It returns TRUE if the subquery has one or more rows.

29. What is a composite key?

A composite key is a combination of two or more columns used to uniquely identify a record in a table.

30. What is an inner join?

An inner join returns rows when there is a match in both tables. Non-matching rows are excluded.

31. What is an outer join?

An outer join returns all rows from one table and the matching rows from the other table. If there is no match, NULL values are returned for the non-matching rows.

32. What is a full outer join?

A full outer join returns all rows when there is a match in one of the tables. If there is no match, the result will contain NULL values.

33. What is a left join?

A left join returns all rows from the left table and the matching rows from the right table. If there is no match, NULL values will be returned for the right table.

34. What is a right join?

A right join returns all rows from the right table and the matching rows from the left table. If there is no match, NULL values will be returned for the left table.

35. What is a SELF JOIN?

A self join is when a table is joined with itself. It is used when a table has a relationship with itself.

36. What is a GROUP BY clause?

The GROUP BY clause is used to group rows that have the same values in specified columns. It is often used with aggregate functions.

37. What is HAVING in SQL?

The HAVING clause is used to filter records after a GROUP BY operation. It works with aggregate functions like SUM, COUNT, etc.

38. What is the ORDER BY clause in SQL?

The ORDER BY clause is used to sort the result set in ascending or descending order.

39. What is the DISTINCT keyword in SQL?

The DISTINCT keyword is used to remove duplicate records from the result set.

40. What is a case statement in SQL?

The CASE statement is a conditional expression used to return a value based on conditions.

41. What is the difference between LEFT JOIN and RIGHT JOIN?

• LEFT JOIN: Returns all rows from the left table and matching rows from the right table.
• RIGHT JOIN: Returns all rows from the right table and matching rows from the left table.

42. What are window functions in SQL?

Window functions perform calculations across a set of table rows related to the current row. Examples: ROW_NUMBER(), RANK(), DENSE_RANK().

43. What is a database schema?

A schema is the structure that defines the organization of data in a database, including tables, views, indexes, and relationships.

44. What is a temporary table in SQL?

A temporary table is a table that is created and used for a short duration, and is automatically dropped when the session ends.

45. What is the difference between UNION and JOIN?

• UNION combines the result sets of two or more queries vertically.
• JOIN combines tables horizontally based on a condition.

46. What is a UNIQUE constraint?

The UNIQUE constraint ensures that all values in a column are distinct.

47. What is the CHECK constraint?

The CHECK constraint ensures that all values in a column satisfy a specific condition.

48. What is an AUTO_INCREMENT attribute in SQL?

The AUTO_INCREMENT attribute automatically generates a unique value for a column when a new record is inserted.

49. What is the difference between TRUNCATE and DELETE?

• TRUNCATE: Removes all rows from a table without logging individual row deletions.
• DELETE: Removes rows from a table and logs each row deletion.

50. What is a JOIN in SQL?

A join is used to combine rows from two or more tables based on a related column between them.

51. What is the difference between CHAR and VARCHAR?

• CHAR: Fixed-length, padding spaces if the value is shorter than the defined length.
• VARCHAR: Variable-length, storing only the required number of characters.

52. What is a stored procedure?

A stored procedure is a precompiled collection of one or more SQL statements that can be executed as a unit.

53. What is a function in SQL?

A function is a predefined SQL operation that can accept input parameters and return a value. Functions are typically used to encapsulate logic.

54. What is a schema in SQL?

A schema is the organizational blueprint for the structure of a database. It defines tables, views, indexes, and relationships between tables.

55. What is a sequence in SQL?

A sequence is a database object used to generate a sequence of unique numbers. They are typically used for generating unique primary key values.

56. What is a stored function?

A stored function is similar to a stored procedure, but it returns a single value and can be used in SQL expressions.

57. What is a TRIGGERS in SQL?

A trigger is a stored procedure that automatically runs when certain events occur on a table, such as INSERT, UPDATE, or DELETE.

58. What is the EXPLAIN keyword used for in SQL?

The EXPLAIN keyword is used to obtain the execution plan for a query. It helps in understanding how SQL queries are executed by the database.

59. What is the ROLLBACK statement in SQL?

ROLLBACK is used to undo changes made by a transaction. It reverts the database to its state before the transaction began.

60. What is the COMMIT statement in SQL?

COMMIT saves all the changes made in the current transaction to the database permanently.

61. What is the SAVEPOINT command in SQL?

SAVEPOINT sets a point within a transaction to which you can later roll back without affecting the entire transaction.

62. What is a data type in SQL?

A data type defines the type of data that can be stored in a column, such as INTEGER, VARCHAR, DATE, BOOLEAN.

63. What is a primary key?

A primary key is a unique identifier for each record in a database table. It must contain unique values and cannot be NULL.

64. What is a composite key?

A composite key is a primary key that consists of more than one column. It is used when a single column cannot uniquely identify a record.

65. What is the difference between INNER JOIN and OUTER JOIN?

• INNER JOIN returns only matching rows from both tables.
• OUTER JOIN returns matching rows from both tables plus non-matching rows from one table with NULL values for missing data from the other.

66. What is the DISTINCT keyword in SQL?

The DISTINCT keyword is used to remove duplicate rows from the result set of a query.

67. What is Normalization in SQL?

Normalization is the process of organizing data in a database to minimize redundancy and dependency.

68. What is Denormalization?

Denormalization is the process of combining normalized tables into fewer tables for the sake of query performance.

69. What is the GROUP BY clause in SQL?

The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows, often used with aggregate functions.

70. What is HAVING in SQL?

HAVING is used to filter records after the GROUP BY clause is applied. It is similar to WHERE, but it works with aggregate functions.

71. What are aggregate functions in SQL?

Aggregate functions perform a calculation on a set of values to return a single value. Examples include COUNT(), SUM(), MAX(), MIN(), AVG().

72. What is AUTO_INCREMENT in SQL?

The AUTO_INCREMENT keyword is used to automatically generate a unique number when a new record is inserted into a table (commonly used for primary key fields).

73. What is the LIKE operator in SQL?

The LIKE operator is used to search for a specified pattern in a column. Common wildcards are % (matches zero or more characters) and _ (matches exactly one character).

74. What is INNER JOIN?

An INNER JOIN returns only the rows where there is a match in both tables based on the join condition.

75. What is a LEFT JOIN in SQL?

A LEFT JOIN returns all rows from the left table and the matching rows from the right table. If no match is found, NULL values are returned for the right table.

76. What is a RIGHT JOIN in SQL?

A RIGHT JOIN returns all rows from the right table and the matching rows from the left table. If no match is found, NULL values are returned for the left table.

77. What is the ISNULL() function in SQL?

ISNULL() is used to replace NULL values with a specified value.

78. What is a CASE expression in SQL?

The CASE expression is SQL's way of handling conditional logic in queries. It returns values based on conditions.

79. What is NULL in SQL?

NULL represents the absence of any value or an unknown value in a database column.

80. What is EXCEPT in SQL?

The EXCEPT operator returns the difference between two result sets, excluding rows that are common to both.

81. What is INTERSECT in SQL?

The INTERSECT operator returns only the common rows between two result sets.

82. What is the difference between JOIN and UNION?

• JOIN combines columns from multiple tables based on a relationship.
• UNION combines rows from multiple queries and removes duplicates.

83. What is ROW_NUMBER() in SQL?

ROW_NUMBER() is a window function that assigns a unique number to each row based on the order specified in the query.

84. What is RANK() in SQL?

RANK() is a window function that assigns a rank to each row within a partition of a result set, with gaps between ranks for duplicate values.

85. What is DENSE_RANK() in SQL?

DENSE_RANK() is similar to RANK(), but it does not leave gaps in ranking for ties (duplicate values).

86. What is a FOREIGN KEY?

A foreign key is a column in a table that links to the primary key of another table, establishing a relationship between the two tables.

87. What is a UNIQUE constraint in SQL?

The UNIQUE constraint ensures that all values in a column are distinct (no duplicates).

88. What is the IN operator in SQL?

The IN operator is used to check if a value exists in a set of values or subquery results.

89. What is a INDEX in SQL?

An index is a database object used to speed up the retrieval of rows from a table based on the values of one or more columns.

90. What is the difference between INDEX and PRIMARY KEY?

• PRIMARY KEY: Ensures unique identification of records and does not allow NULL values.
• INDEX: Improves query performance by providing quick access to rows but does not enforce uniqueness.

91. What is TRUNCATE in SQL?

TRUNCATE removes all rows from a table without logging individual row deletions and cannot be rolled back.

92. What is DROP in SQL?

DROP is used to delete an entire table or database from the database system permanently.

93. What is a subquery in SQL?

A subquery is a query nested within another query. It can be used in SELECT, INSERT, UPDATE, and DELETE operations.

94. What is the difference between WHERE and HAVING?

• WHERE filters rows before grouping.
• HAVING filters rows after grouping (works with aggregate functions).

95. What is LIMIT in SQL?

The LIMIT clause is used to restrict the number of rows returned in a query result.

96. What is OFFSET in SQL?

The OFFSET clause is used to skip a specified number of rows before starting to return rows in a query.

97. What is the TRIM() function in SQL?

The TRIM() function removes leading and trailing spaces from a string.

98. What is COALESCE() in SQL?

COALESCE() returns the first non-NULL value from a list of arguments.

99. What is CONCAT() in SQL?

CONCAT() concatenates two or more strings into a single string.

100. What is CAST() and CONVERT() in SQL?

• CAST(): Converts a value from one data type to another.
• CONVERT(): Similar to CAST(), but allows for more flexible conversions in some databases (e.g., SQL Server).

These are 100 SQL interview questions and answers to help you prepare for interviews. Each of these topics is essential for demonstrating your knowledge of SQL in real-world applications.