100 SQL

Interview Q&A
Made by Want More

1.What is SQL?

SQL (Structured Query Language) is a programming language used for

managing relational databases. It is used to perform various operations
like creating, modifying, and querying databases.

2. What are the different types of SQL statements?

The different types of SQL statements are:

DDL (Data Definition Language): Used for creating, modifying, and

deleting database objects (e.g., CREATE, ALTER, DROP).

DML (Data Manipulation Language): Used for manipulating data within

the database (e.g., SELECT, INSERT, UPDATE, DELETE).

DCL (Data Control Language): Used for controlling access to data in the
database (e.g., GRANT, REVOKE).

TCL (Transaction Control Language): Used for managing transactions (e.g.,

COMMIT, ROLLBACK).

3.What is a primary key?

A primary key is a column or a combination of columns that uniquely

identifies each row in a table. It ensures the uniqueness and integrity of
the data in the table.

4.What is a foreign key?

A foreign key is a column or a combination of columns that establishes a

link between two tables. It represents a relationship between the data in
the two tables, enforcing referential integrity.

5.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. It allows retrieving data from multiple tables
simultaneously.

6.What are the different types of joins in SQL?

The different types of joins are:

INNER JOIN: Returns only the matching rows from both tables.

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

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

FULL JOIN: Returns all rows from both tables, including the unmatched
rows.

CROSS JOIN: Returns the Cartesian product of the two tables.

7. What is normalization in SQL?

Normalization is the process of organizing data in a database to minimize

redundancy and dependency. It involves breaking down a table into
smaller tables and establishing relationships between them to eliminate
data anomalies.

8. What is a subquery?

A subquery is a query nested within another query. It is used to retrieve

data from one or more tables and use the result of the subquery as a
condition or value in the outer query.

9.What is the difference between DELETE and TRUNCATE in SQL?

DELETE: It is a DML statement used to delete specific rows from a table

based on a condition. It can be rolled back and can use a WHERE clause.

TRUNCATE: It is a DDL statement used to delete all rows from a table,

resetting the table to its initial state. It cannot be rolled back and does not
use a WHERE clause.

10.What is an index in SQL?

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 of a table and
allows faster searching, sorting, and filtering of data.

11.What is the difference between UNION and UNION ALL in SQL?

UNION: It is used to combine the result sets of two or more SELECT

statements into a single result set. It removes duplicate rows from the
result set.

UNION ALL: It also combines the result sets of two or more SELECT
statements into a single result set but does not remove duplicate rows. It
is faster than UNION as it does not perform duplicate removal.

12. What is ACID in the context of database transactions?

ACID stands for Atomicity, Consistency, Isolation, and Durability. It is a set

of properties that guarantee reliable and consistent transactions in a
database.

Atomicity: Ensures that a transaction is treated as a single unit of work,

either fully completed or fully rolled back.

Consistency: Ensures that a transaction brings the database from one

valid state to another. It preserves the integrity of the data.

Isolation: Ensures that concurrent transactions do not interfere with each

other. Each transaction is isolated and operates as if it is the only
transaction running.

Durability: Ensures that once a transaction is committed, its changes are

permanent and will survive any subsequent failures.

13. What is a view in SQL?

A view is a virtual table derived from one or more tables or views. It acts as
a stored query and does not store any data itself. Views simplify complex
queries, provide security, and present a customized representation of the
data.

14.What is the difference between CHAR and VARCHAR in SQL?

CHAR: It is a fixed-length character data type. It stores a specific number

of characters, padding the remaining space with spaces if the data is
shorter.

VARCHAR: It is a variable-length character data type. It stores a varying

number of characters, using only the required space without padding.
15. What is the difference between a clustered and non-clustered index?

Clustered index: It determines the physical order of data rows in a table.

Each table can have only one clustered index, and it affects the way data is
stored on disk.

Non-clustered index: It is a separate structure that contains a sorted copy

of selected columns from a table. A table can have multiple non-clustered
indexes, and they do not affect the physical order of data rows.

16. What is the difference between a stored procedure and a function in

SQL?

Stored procedure: It is a named set of SQL statements that are stored in

the database. It can have input and output parameters, and it may or may
not return a value. Stored procedures are used for performing complex
database operations.

Function: It is a named program unit that returns a value. It always returns

a value and can be used in SQL statements wherever an expression can be
used. Functions are used for calculations and data manipulation.

17. What is the difference between a candidate key and a primary key?

Candidate key: It is a column or a combination of columns that can

uniquely identify a row in a table. There can be multiple candidate keys in
a table, and one of them is chosen as the primary key.

Primary key: It is a candidate key that is selected as the main unique

identifier for a table. It uniquely identifies each row in the table and
enforces data integrity.

18. What is the difference between a clustered and non-clustered index?

Clustered index: It determines the physical order of data rows in a table.

Each table can have only one clustered index, and it affects the way data is
stored on disk.

Non-clustered index: It is a separate structure that contains a sorted copy

of selected columns from a table. A table can have multiple non-clustered
indexes, and they do not affect the physical order of data rows.

19. What is a self-join in SQL?

A self-join is a type of join where a table is joined with itself. It is used
when data in a table relates to other data within the same table. By using
aliases for the table, different rows in the table can be joined together.

20. What is the difference between the HAVING and WHERE clauses in
SQL?

WHERE clause: It is used to filter rows before grouping and aggregation

operations are performed. It is used with the SELECT, UPDATE, and
DELETE statements.

HAVING clause: It is used to filter groups of rows after grouping and

aggregation operations are performed. It is used with the SELECT
statement, specifically with GROUP BY.

21. What is the difference between a database and a schema in SQL?

Database: It is a collection of related data stored and organized in a

structured format. It can contain multiple tables, views, indexes, etc.

Schema: It is a logical container within a database. It is used to group

database objects together, such as tables, views, and procedures. A
database can have multiple schemas.

22. What is a transaction in SQL?

A transaction is a sequence of one or more SQL statements that are

treated as a single unit of work. It allows multiple operations to be
grouped together, ensuring that either all of them are successfully
completed, or none of them are applied.

23. What is the difference between a clustered and non-clustered index?

Clustered index: It determines the physical order of data rows in a table.

Each table can have only one clustered index, and it affects the way data is
stored on disk.

Non-clustered index: It is a separate structure that contains a sorted copy

of selected columns from a table. A table can have multiple non-clustered
indexes, and they do not affect the physical order of data rows.

24. What is the difference between UNION and UNION ALL in SQL?

UNION: It is used to combine the result sets of two or more SELECT

statements into a single result set. It removes duplicate rows from the
result set.

UNION ALL: It also combines the result sets of two or more SELECT
statements into a single result set but does not remove duplicate rows. It
is faster than UNION as it does not perform duplicate removal.

25. What is a correlated subquery in SQL?

A correlated subquery is a subquery that refers to a column from the outer

query. It executes once for each row of the outer query and is used to
perform calculations or filtering based on values from the outer query.

26. What is the difference between a primary key and a unique key?

Primary key: It is a column or a combination of columns that uniquely

identifies each row in a table. It enforces data integrity and does not allow
duplicate or null values.

Unique key: It is a column or a combination of columns that ensures the

uniqueness of data in a table. Unlike the primary key, it allows null values.

27. What is the difference between a correlated and non-correlated

subquery?

Correlated subquery: It is a subquery that refers to a column from the

outer query. It is executed once for each row of the outer query.

Non-correlated subquery: It is a subquery that can be executed

independently of the outer query. It is executed only once and its result is
used by the outer query.

28. What is a constraint in SQL?

A constraint is a rule defined on a column or a set of columns in a table to

enforce data integrity. It can be used to enforce uniqueness, primary key,
foreign key, and other rules on the data.

29. What is the difference between a LEFT JOIN and a RIGHT JOIN?

LEFT JOIN: It returns all rows from the left table and the matching rows
from the right table. If there is no match, NULL values are returned for the
columns of the right table.

RIGHT JOIN: It returns all rows from the right table and the matching rows
from the left table. If there is no match, NULL values are returned for the
columns of the left table.
30. What is a trigger in SQL?

A trigger is a database object that is automatically executed in response to

a specific event, such as an INSERT, UPDATE, or DELETE operation on a
table. Triggers are used to enforce business rules, perform auditing, or
maintain data consistency.

31.What is the difference between a unique constraint and a unique

index?

Unique constraint: It is a database constraint that ensures the uniqueness

of values in one or more columns. It is implemented internally as a unique
index.

Unique index: It is an index created explicitly on one or more columns to

enforce uniqueness. It can be used for fast searching of unique values.

32. What is the difference between the GROUP BY and HAVING clauses?

GROUP BY clause: It is used to group rows based on one or more columns.

It is typically used with aggregate functions to perform calculations on
groups of rows.

HAVING clause: It is used to filter groups of rows after the GROUP BY

operation has been performed. It is used with aggregate functions and
conditions.

33. What is the purpose of the COMMIT statement in SQL?

The COMMIT statement is used to permanently save the changes made

within a transaction to the database. It marks the successful completion of
a transaction and makes the changes visible to other users.

34. What is the purpose of the ROLLBACK statement in SQL?

The ROLLBACK statement is used to undo the changes made within a

transaction and restore the database to its state before the transaction
began. It is used to discard the changes in case of errors or failures.

35. What is the purpose of the SAVEPOINT statement in SQL?

The SAVEPOINT statement is used to define a point within a transaction to

which you can roll back later. It allows you to undo a part of a transaction
without rolling back the entire transaction.
36. What is the difference between the MINUS and EXCEPT operators in
SQL?

MINUS: It is used to return the rows from the first query that are not
present in the second query. It removes duplicates from the result set.

EXCEPT: It is used to return the rows from the first query that are not
present in the second query. It preserves duplicates in the result set.

37. What is the difference between the EXISTS and IN operators in SQL?

EXISTS: It is used to check the existence of rows returned by a subquery. It

returns true if the subquery returns at least one row.

IN: It is used to compare a value with a set of values returned by a

subquery or a list of explicit values. It returns true if the value is found in
the set.

38. What is the purpose of the CASE statement in SQL?

The CASE statement is used to perform conditional branching within SQL

queries. It allows you to conditionally evaluate expressions and return
different results based on the conditions.

39. What is the purpose of the COALESCE function in SQL?

The COALESCE function is used to return the first non-null expression in a

list of expressions. It is often used to provide a default value when a
column has a null value.

40. What is a recursive query in SQL?

A recursive query is a query that references its own output in the query
definition. It is used to perform hierarchical or self-referencing queries,
such as traversing tree structures or calculating running totals.

41. What is the purpose of the GROUPING SETS clause in SQL?

The GROUPING SETS clause is used to specify multiple grouping sets in a

single query. It allows you to generate multiple levels of grouping in a
single result set.

42. What is the purpose of the PIVOT and UNPIVOT operators in SQL?

PIVOT: It is used to rotate rows into columns, creating a crosstab report

from a normalized table.
UNPIVOT: It is used to rotate columns into rows, converting the columns of
a crosstab report into rows.

43. What is the purpose of the WITH clause in SQL?

The WITH clause, also known as a Common Table Expression (CTE), is used
to define temporary result sets that can be referenced within a query. It
simplifies complex queries and improves query readability.

44. What is the purpose of the ROW_NUMBER() function in SQL?

The ROW_NUMBER() function is used to assign a unique sequential

number to each row within a result set. It is often used for pagination or
ranking purposes.

45. What is the purpose of the TOP clause in SQL?

The TOP clause is used to limit the number of rows returned by a query. It
specifies the maximum number of rows to be included in the result set.

46. What is the purpose of the OFFSET-FETCH clause in SQL?

The OFFSET-FETCH clause is used to implement pagination in SQL queries.

It allows you to specify the starting point and the number of rows to fetch
from the result set.

47. What is the purpose of the MERGE statement in SQL?

The MERGE statement is used to perform INSERT, UPDATE, or DELETE

operations on a target table based on the values from a source table. It
combines the capabilities of INSERT, UPDATE, and DELETE statements in a
single statement.

48. What is the purpose of the CROSS APPLY and OUTER APPLY
operators in SQL?

CROSS APPLY: It applies a table-valued function to each row of a table

expression, producing the result as a derived table.

OUTER APPLY: It is similar to CROSS APPLY but includes unmatched rows

from the left table in the result set.

49. What is the purpose of the STRING_AGG function in SQL?

The STRING_AGG function is used to concatenate values from multiple

rows into a single string, separated by a specified delimiter. It simplifies
the process of concatenating strings in SQL queries.

50. What is the purpose of the LEAD and LAG functions in SQL?

LEAD: It is used to access the value of a column from the next row in the
result set.

LAG: It is used to access the value of a column from the previous row in
the result set.

51. What is the purpose of the TRUNCATE TABLE statement in SQL?

The TRUNCATE TABLE statement is used to remove all rows from a table,
effectively deleting all data in the table. Unlike the DELETE statement, it
does not generate individual delete operations for each row and is faster
for large tables.

52. What is the purpose of the RANK() function in SQL?

The RANK() function is used to assign a rank to each row within a result
set based on a specified criteria. It assigns the same rank to rows with the
same values and leaves gaps in the ranking sequence in case of ties.

53. What is the purpose of the DENSE_RANK() function in SQL?

The DENSE_RANK() function is used to assign a rank to each row within a

result set based on a specified criteria. It assigns consecutive ranks to
rows with the same values, without leaving gaps in the ranking sequence.

54. What is the purpose of the NTILE() function in SQL?

The NTILE() function is used to divide a result set into a specified number
of groups, assigning a group number to each row. It is often used for data
distribution or bucketing purposes.

55. What is the purpose of the COALESCE function in SQL?

The COALESCE function is used to return the first non-null expression in a

list of expressions. It is often used to provide a default value when a
column has a null value.

56. What is the purpose of the NULLIF function in SQL?

The NULLIF function is used to compare two expressions and return null if
they are equal. It is often used to handle division by zero errors or to
replace specific values with null.
57. What is the purpose of the HAVING clause in SQL?

The HAVING clause is used to filter groups of rows after the GROUP BY
operation has been performed. It is used with aggregate functions and
conditions.

58. 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 from
the result set.

59. What is the purpose of the UNION ALL operator in SQL?

The UNION ALL operator is used to combine the result sets of two or more
SELECT statements into a single result set, including duplicate rows. It is
faster than UNION as it does not perform duplicate removal.

60. What is the purpose of the INNER JOIN in SQL?

The INNER JOIN is used to retrieve records that have matching values in
both tables being joined. It returns only the rows where the join condition
is satisfied.

61. What is the purpose of the LEFT JOIN in SQL?

The LEFT JOIN is used to retrieve all records from the left table and the
matching records from the right table. If there is no match, NULL values
are returned for the columns of the right table.

62. What is the purpose of the RIGHT JOIN in SQL?

The RIGHT JOIN is used to retrieve all records from the right table and the
matching records from the left table. If there is no match, NULL values are
returned for the columns of the left table.

63. What is the purpose of the FULL JOIN in SQL?

The FULL JOIN is used to retrieve all records from both tables being joined,
regardless of whether there is a match or not. If there is no match, NULL
values are returned for the columns of the non-matching table.

64. 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 typically used with aggregate functions to perform
calculations on groups of rows.

65. What is the purpose of the ORDER BY clause in SQL?

The ORDER BY clause is used to sort the result set based on one or more
columns. It specifies the sort order, which can be ascending (ASC) or
descending (DESC).

66. What is the purpose of the DISTINCT keyword in SQL?

The DISTINCT keyword is used to eliminate duplicate rows from the result
set. It considers the entire row when determining duplicates.

67. What is the purpose of 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 such as '%' (matches any sequence of
characters) and '_' (matches any single character).

68. What is the purpose of the BETWEEN operator in SQL?

The BETWEEN operator is used to select values within a specified range. It

is inclusive of the endpoints.

69. What is the purpose of the IN operator in SQL?

The IN operator is used to specify multiple values in a WHERE clause. It

checks if a value matches any value in a list or subquery.

70. 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 at least one row.

71 .What is the purpose of the NOT operator in SQL?

The NOT operator is used to negate a logical condition. It reverses the

result of a Boolean expression.

72. What is the purpose of the COUNT() function in SQL?

The COUNT() function is used to count the number of rows that match a
specified condition in a SELECT statement. It can also be used without a
condition to count all rows in a table.

73. What is the purpose of the SUM() function in SQL?

The SUM() function is used to calculate the sum of values in a column. It is
often used with the GROUP BY clause to calculate sums per group.

74. What is the purpose of the AVG() function in SQL?

The AVG() function is used to calculate the average of values in a column.

It is often used with the GROUP BY clause to calculate averages per group.

75. What is the purpose of the MAX() function in SQL?

The MAX() function is used to retrieve the maximum value in a column. It

is often used with the GROUP BY clause to find the maximum per group.

76. What is the purpose of the MIN() function in SQL?

The MIN() function is used to retrieve the minimum value in a column. It is

often used with the GROUP BY clause to find the minimum per group.

77. What is the purpose of the UPPER() function in SQL?

The UPPER() function is used to convert a string to uppercase.

78. What is the purpose of the LOWER() function in SQL?

The LOWER() function is used to convert a string to lowercase.

79. What is the purpose of the CONCAT() function in SQL?

The CONCAT() function is used to concatenate two or more strings

together.

80. What is the purpose of the SUBSTRING() function in SQL?

The SUBSTRING() function is used to extract a substring from a string.

81. What is the purpose of the DATE() function in SQL?

The DATE() function is used to extract the date part from a datetime value.

82. What is the purpose of the YEAR() function in SQL?

The YEAR() function is used to extract the year part from a date or
datetime value.

83. What is the purpose of the MONTH() function in SQL?

The MONTH() function is used to extract the month part from a date or
datetime value.
84. What is the purpose of the DAY() function in SQL?

The DAY() function is used to extract the day part from a date or datetime
value.

85. What is the purpose of the GETDATE() function in SQL?

The GETDATE() function is used to retrieve the current date and time.

86. What is the purpose of the NOW() function in SQL?

The NOW() function is used to retrieve the current date and time.

87. What is the purpose of the COUNT() function in SQL?

The COUNT() function is used to count the number of rows in a table.

88. What is the purpose of the GROUP_CONCAT() function in SQL?

The GROUP_CONCAT() function is used to concatenate values from

multiple rows into a single string, separated by a specified delimiter. It is
often used with the GROUP BY clause.

89. What is the purpose of the CASE statement in SQL?

The CASE statement is used to perform conditional branching within SQL

queries. It allows you to conditionally evaluate expressions and return
different results based on the conditions.

90. What is the purpose of the COALESCE() function in SQL?

The COALESCE() function is used to return the first non-null expression in

a list of expressions. It is often used to provide a default value when a
column has a null value.

91. What is the purpose of the NULLIF() function in SQL?

The NULLIF() function is used to compare two expressions and return null
if they are equal. It is often used to handle division by zero errors or to
replace specific values with null.

92. What is the purpose of the TRIM() function in SQL?

The TRIM() function is used to remove leading and trailing spaces from a
string.

93. What is the purpose of the REPLACE() function in SQL?

The REPLACE() function is used to replace occurrences of a specified
string with another string in a column.

94. What is the purpose of the CAST() function in SQL?

The CAST() function is used to convert a value of one data type to another
data type.

95. What is the purpose of the ISNULL() function in SQL?

The ISNULL() function is used to replace null values with a specified value.

96. What is the purpose of the ROUND() function in SQL?

The ROUND() function is used to round a numeric value to a specified

number of decimal places.

97. What is the purpose of the TRUNC() function in SQL?

The TRUNC() function is used to truncate a numeric value to a specified

number of decimal places.

98. What is the purpose of the AVG() function in SQL?

The AVG() function is used to calculate the average of a set of values.

99. What is the purpose of the DISTINCT keyword in SQL?

The DISTINCT keyword is used to eliminate duplicate values from a result

set.

100. What is the purpose of the AS keyword in SQL?

The AS keyword is used to alias a column or table with a different name. It

is often used to provide more meaningful names to columns or to make
column names unique in a result set.