Top 25 SQL Interview Questions and Answers for 2022

If you are preparing for the SQL interview then this article will be very helpful for your preparation. We have focused on the Top 25 frequently asked SQL Interview Questions and answers based on actual interview experience.

Let’s get started to SQL Interview Questions!

SQL Interview Questions and Answers

Que 1. What is SQL?

SQL stands for Structured Query Language. SQL is a standard language for RDBMS (Relational Database Management System). Basically, it is used to communicate with the Database and perform different data manipulation operations on the data. Data manipulation operations are nothing but insertion, update, deletion, and retrieval of data from the Database.

Que 2. What are the tables, fields, and records in SQL?

Table: A table is a set of organized data that contains rows and columns. Rows can be considered as horizontal and columns as vertical.

Fields: A table contains numbers of columns which is called fields.

Records: A table contains rows which can be called as records.

Example:
Table: Student
Fields: StudentId, Name, DateOfBirth, City
Record: 101, John D, 12/23/1982, Mumbai

Que 3. What is a Primary Key?

A primary key is a single column or set of columns value which used to identify a database record uniquely.

Primary Key has the following characteristics:

A primary key value cannot be NULL
A primary key value must be unique
The primary key values cannot be updated
The primary key must be given a value when a new record is inserted
The primary key creates a clustered index for the single column by default

Que 4. What is a Unique key?

A unique key constraint identifies each database record uniquely. This provides uniqueness for the column/columns and used to identify each record uniquely.

Unique Key has the following characteristics:

The unique key allows only one NULL value.
The Unique key doesn’t accept a duplicate value.
The unique key creates a nonclustered index by default

Que 5. What is the Foreign key?

A foreign key references the primary key of another table. It helps connect two tables and maintains referential integrity by enforcing a link between the data in two tables.

Foreign Key has the following characteristics:

A foreign key can have a different name from its primary key
It ensures rows in one table have corresponding rows in another
Unlike the Primary key, they do not have to be unique. Most often they aren’t
Foreign keys accept NULL values even though primary keys cannot

Que 6. What's the difference between a primary key and a unique key?

Both primary key and unique key enforce the uniqueness of the column on which they are defined. However, by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another main difference is that the primary key doesn’t allow NULL values where a unique key allows one NULL value.

Que 7. How do you implement one-to-one, one-to-many, and many-to-many relationships while designing tables?

One-to-One relationship: This can be implemented as a single table and sometimes as two tables with the primary and foreign key relationships.

One-to-Many relationships: This can be implemented by splitting the data into two tables with the primary key and foreign key relationships.

Many-to-Many relationships: THis can be implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

Que 8. Define candidate key, alternate key, composite key.

Candidate Key: A candidate key is a key that identifies each row of a table uniquely. A candidate key becomes the primary key of the SQL table.

Alternate Key: If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys.

Composite Key: A composite key is a primary key composed of a set of columns used to identify a record uniquely. In other words, a key formed by combining at least two or more columns is called a composite key.

Que 9. What is a View in SQL?

A view in SQL is a virtual table that consists of a subset of data contained in a table. Though a view contains rows and columns like an actual SQL table, those are not virtually present.

The view has the following characteristics:

Views contain rows and columns like an actual SQL table
Views are not virtually present
Views take less space to store
A view can have data of one or more tables combined based on the relationship
Indexes can be created on columns in Views

Que 10. What are Joins in SQL?

JOIN clause in SQL is used to combine rows from two or more tables in the SQL database based on a related column between them.

Look the selection from Orders and Customers tables:

#Student and StudentsReport Tables

You must notice that the “StudentId” column in the “StudentsReport” table refers to the “StudentId” in the “Student” table. The relationship between these two tables is the “StudentId” column.

Now, we can create the following SQL statement, that selects records that have matching values in both tables. We used an INNER JOIN for this example.

You must notice that the CustomerId column in the Orders table refers to the CustomerId in the Customers table. The relationship between these two tables is the CustomerId column.

Now, we can create the following SQL statement, that selects records that have matching values in both tables. We used an INNER JOIN for this example.

SELECT o.OrderId, c.Name, o.OrderDate, c.City
FROM Orders o
INNER JOIN Customers c 
ON o.CustomerID = c.CustomerID;

The result will be:

Que 11. What are the different types of Joins in SQL?

Following are the types of Joins in SQL

INNER JOIN: Returns matching records/rows from both the tables
LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

Que 12. What is an Index in SQL?

Indexes speed up the querying process by providing swift access to rows in the database tables, similarly to the way a book’s index helps you find information quickly within that book.

A database index is a kind of data structure that provides a quick lookup of data in a column or columns of a database table.

Indexes are created on columns in tables or views. The index provides a fast way to lookup data based on the values within those columns. You can create indexes on most columns in a table or a view. The exceptions are primarily those columns configured with large object data types, such as image, text, and varchar(max). You can create indexes on XML columns as well, but those indexes are different from the basic index.

--Create Index:
CREATE INDEX index_name
ON table_name (column_1, column_2);

--Delete Index:
DROP INDEX index_name; 	 /* Drop Index */

Basic Types of Indexes:

Clustered Indexes:
When you create a clustered index on a table, all the rows in the table are stored in the order of the clustered index key. So, there can be only one clustered index per table.
Non-Clustered Indexes: Non-clustered indexes have their storage separate from the table data storage. Non-Clustered Indexes helps to improve the performance of the queries which not covered by the Clustered index.

Disadvantages of Indexes:
The primary disadvantage of indexes are:

If you create an index on each column of a table, it improves the query performance, as the query optimizer can choose from all the existing indexes to come up with an efficient execution plan. However, at the same time, data modification operations (Ex: INSERT, UPDATE, DELETE) will become slow, as every time data changes in the table, all the indexes need to be updated.
Indexes require disk space. If you have more indexes then more disk space is used.

This is not only important SQL Interview Questions but also a very important concept that every SQL developer must learn it.

Que 13. What are Cursors in SQL?

A cursor is a database object used by applications to manipulate data in a set on a row-by-row basis rather than typical SQL commands that operate on all the rows in the set at one time.

Following SQL statements are required to work with cursors:

DECLARE CURSOR
OPEN
FETCH
CLOSE
DEALLOCATE

Syntax:

DECLARE CursorName CURSOR
    FOR SelectStatement;

OPEN CursorName;
FETCH FROM CursorName INTO Variable;

WHILE condition  
    BEGIN
        FETCH FROM CursorName INTO Variable;
    END;
CLOSE CursorName;
DEALLOCATE CursorName

Example:

DECLARE @sum INTEGER;
DECLARE @sal INTEGER;
SET @sum = 0;
DECLARE empCursor CURSOR FOR SELECT SALARY FROM EMPLOYEE;
OPEN empCursor;
FETCH NEXT FROM empCursor INTO @sal;
WHILE  @@FETCH_STATUS = 0 
	BEGIN
		SET @sum = @sum + @sal;
		FETCH NEXT FROM empCursor INTO @sal;
	END
CLOSE empCursor
DEALLOCATE empCursor

Que 14. What is a Stored Procedure in SQL?

A stored procedure is nothing but a prepared SQL statement that accepts inputs as parameters, performs tasks (DML operations on Database), and may return a value.

Basically, Stored Procedures are stored SQL statements that can be reused over and over again to perform DML operations on the Database. When you need to execute SQL query frequently then you can save it as a stored procedure, and then just call it to execute it.

Syntax:

--Create a Stored Procedure
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
	-- Add the parameters for the stored procedure here
	<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
	<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

--Execute a Stored Procedure
EXEC procedure_name;

Example:

CREATE PROCEDURE SP_GetAllStudents
	@City NVARCHAR(25)
AS
BEGIN
	SET NOCOUNT ON;
	SELECT * FROM Student WHERE City = @City
END
GO

--Execute the above-stored procedure:
EXEC SP_GetAllStudents 'Mumbai'

Que 15. What are User-Defined Functions in SQL?

SQL Server User-Defined functions are routine that accepts parameters, perform an action (such as complex calculations), and return the result of that action a value. This return value can be a single scalar value or a result set.

Syntax:

--Create User-Defined FUNCTION
CREATE FUNCTION <Inline_Function_Name, sysname, FunctionName> 
(	
	-- Add the parameters for the function here
	<@param1, sysname, @p1> <Data_Type_For_Param1, , int>, 
	<@param2, sysname, @p2> <Data_Type_For_Param2, , char>
)
RETURNS TABLE/INT/VARCHAR
AS
RETURN 
(
	-- Add the SELECT statement with parameter references here
	SELECT 0
)
GO

--Execute a User-Defined Function:
dbo.FunctionName(parameters if any)

Example:

CREATE FUNCTION dbo.StudentScore
(	
	@StudentId INT
)
RETURNS INT
AS
BEGIN
	DECLARE @Ret INT
	SET @Ret = 0
	SELECT @Ret = Score FROM Student 
	WHERE StudentId = @StudentId
	RETURN @Ret
END
GO

Que 16. What are the different types of User-Defined Functions in SQL Server?

SQL Server has the following two types of user-defined functions

1. Scalar Function: Scalar Functions return a single data value of the type defined in the RETURNS clause.

Example:

CREATE FUNCTION dbo.ufnStudentScore
(	
	@StudentId INT
)
RETURNS INT
AS
BEGIN
	DECLARE @Ret INT
	SET @Ret = 0
	SELECT @Ret = s.Score FROM Student s
	WHERE s.StudentId = @StudentId
	RETURN @Ret
END
GO

INT type is defined in this user-defined scalar functions so it returns a single integer value.
Use:

SELECT Name, DateOfBirth, Grade, dbo.ufnStudentScore(StudentId) AS Score  
FROM Student WHERE City = 'Mumbai'

2. Table-Valued Functions: Table-Valued user-defined functions return a table data type.

Example:

CREATE FUNCTION dbo.ufnStudentDetails
(	
	@City VARCHAR(25)
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT 
		s.StudentId,
        s.Name,
        s.DateOfBirth,
        s.Grade
    FROM
        Student s
    WHERE
        s.City = @City
)
GO

Use:

SELECT * FROM  dbo.ufnStudentDetails('Mumbai');

Que 17. What are the differences between Stored Procedure and User-Defined Functions in SQL Server?

Stored Procedure	User-Defined Functions
Stored Procedure return zero or n values	User-Defined Function return one value which is mandatory
Stored Procedure can have input as well as output parameters	User-Defined Function can have only input parameters
Stored Procedure allows SELECT as well as DML (INSERT, UPDATE, DELETE) operations	User-Defined Function allows only SELECT statements. It cannot be used to perform actions that modify the database state.
User-Defined Function can be called from Stored Procedure	Stored Procedure cannot be called from User-Defined Function
Stored Procedure support TRY...CATCH	User-Defined Function does not support TRY...CATCH

Que 18. What is Having and Group By clause in SQL?

Having Clause

Having clause in SQL specifies that an SQL statement should return only rows where aggregate values meet the specified condition. Group by clause should be present.

Group By Clause

Group By clause group records into the summary.
Group By returns one record for each group.
Typically involves with aggregate (MIN, MAX, SUM, AVG, etc.) functions

Example:

SELECT COUNT(StudentId) as TotalStudents, City 
FROM [dbo].[Students] 
WHERE City = 'Mumbai'
GROUP BY City
HAVING COUNT(StudentId) >= 1
ORDER BY COUNT(StudentId) DESC

Que 19. What are the triggers in SQL?

A SQL trigger is a procedural code that gets executed automatically when an INSERT, UPDATE, or DELETE operation takes place on a particular table in a database. Triggers can be used to restrict access to specific data, perform logging, or audit data modifications. Triggers can’t be invoked on demand. They get executed only when an INSERT/UPDATE/DELETE action happens on a table on which they are defined.

Syntax:

CREATE TRIGGER <Schema_Name, sysname, Schema_Name>.<Trigger_Name, sysname, Trigger_Name> 
   ON  <Schema_Name, sysname, Schema_Name>.<Table_Name, sysname, Table_Name> 
   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE>
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here

END
GO

Example:

CREATE TRIGGER LogInsertOperation
on [dbo].[Student]
AFTER INSERT
as
	PRINT 'AFTER INSERT TRIGGER EXECUTED SUCCESSFULLY'
GO

Use of Trigger:
Following are the basic uses of Triggers in SQL.

To implement business rules
Enforce the referential integrity checks
To perform logging
Provide auditing
Prevent invalid transactions

Que 20. What are the alternatives for Cursor in SQL?

Following are the alternatives for the cursor in SQL Server

Use a Temporary table.
Use Table variable
Selecting a comma-delimited list of items
Use WHILE LOOPS
Use correlated sub-queries
Use the CASE statement
Perform multiple queries

Que 21. What is a Temporary Table in SQL?

In SQL Server, the Temporary table is a database table that exists temporarily. These are very useful in the SQL Server as it allows for short term use of data on. You can use temporary tables when you have a large number of records in a table and you need to interact with a small subset of those records. Temporary table created on the system database tempdb of the SQL Server.

Temporary Table Types:
SQL Server support following two types of temporary tables

Local Temporary Tables: It is available only to the current connection to the database for the current user and is dropped when the connection is closed.
Global Temporary Tables: It available to other connections once created and it dropped when the last connection using it is closed.

Creating the Temporary Tables: Temporary tables can be created by using CREATE TABLE or SELECT..INTO statement. Use # with table name for a local temporary table and use ## to make it a global temporary table.

--Option 1: CREATE TABLE
CREATE TABLE #mumbaiStudents(
  Id INT,
  FullName VARCHAR(50),
  DateOfBirth 	DATETIME,
  City VARCHAR(25) 
 )

--Option 2: SELECT..INTO
SELECT StudentId, Name, DateOfBirth, City
INTO #mumbaiStudents
FROM Student
WHERE City = 'Mumbai

Querying data from the Temporary Table:
Use SELECT select statement to query data from the temporary table

--Local Temp Table
SELECT * FROM #mumbaiStudents

--Global Temp Table
SELECT * FROM ##mumbaiStudents

Que 22. What is table variables in SQL?

The table variable is a special type of variable that can hold the rows data. The alternative of the Temporary table is the Table variable which can do all kinds of operations that we can perform in the Temp table. Use @ with variable name to create a table variable.

Syntax:

DECLARE @TableVariableName TABLE (
    column_list
)

Example:

DECLARE @mumbai_student_table TABLE (
  Id INT,
  FullName VARCHAR(50),
  DateOfBirth 	DATETIME,
  City VARCHAR(25) 
)

Inserting data into the Table Variables:
The table variable is empty after declaration. Following INSERT statement will be used to insert the rows into the table variable.

INSERT INTO @mumbai_student_table
SELECT
    Id,
    Name,
    DateOfBirth,
	City
FROM
    dbo.Student
WHERE
    City = 'Mumbai'

--OR--

INSERT INTO @mumbai_student_table values ( 1, 'John D', '02/04/1982', 'Mumbai');

Querying data from the Table Variables:
Use SELECT select statement to query data from the table variables.

SELECT * FROM @mumbai_student_table

When to Use Table Variable Over Temporary Table:
The table variable is always useful for less data. If the result set returns a large number of records then use a temporary table. SQL Server doesn’t create statistics on the table variables. If you require to create indexes on it then the temporary table should be used.

Que 23. What are the constraints in SQL?

Constraints are database objects. It enables the RDBMS to enforce the integrity of the database automatically, without needing you to create triggers, rules, or defaults.

Types of Constraints in SQL Server:

PRIMARY KEY – Uniquely identifies each row in a table. It is a combination of a NOT NULL and UNIQUE.
FOREIGN KEY – Uniquely identifies a record in another table
UNIQUE – Ensures that all values in a column are different
NOT NULL – Ensures that a column cannot have a NULL value
CHECK – Ensures that all values in a column satisfy a specific condition

Que 24. Why truncate is faster than delete?

This is a very important SQL Interview Question. Many candidates fail to give the proper answer. Please read it many times until you understand the background logic.

Delete table is a logged operation, therefore the deletion of every row gets logged within the transaction log, which makes it slow.
Truncate table also deletes all the rows in a table, but it won’t log the deletion of every row, instead, it logs the de-allocation of the data pages of the table, which makes it faster. The truncate table can’t be rolled back.

TRUNCATE is faster than DELETE due to the way TRUNCATE “removes” rows. Basically, TRUNCATE doesn’t remove data, but it deallocates whole data pages and removes pointers to indexes. The data still exists until it’s overwritten or the database is shrunk. This action doesn’t require a lot of resources hence it is very fast. It is a common mistake to think that TRUNCATE is not logged. This is often wrong. The deallocation of the data pages is recorded within the log file. Hence BOL refers to TRUNCATE operations as “minimally logged” operations. You can use TRUNCATE within a transaction, and when this transaction is rolled back, the data pages are reallocated again and the database is again in its original and consistent state.

Que 25. What is database Normalization and Denormalization?

Normalization:

Database normalization is the process of restructuring a relational database in accordance with a series of so-called normal forms to reduce data redundancy and improve data integrity.

Normalization is the process of minimizing redundancy from a relation or set of relations. Redundancy in relation may cause insertion, deletion, and update anomalies. So, it helps to minimize the redundancy in relations. Normal forms are used to eliminate or reduce redundancy in database tables.

Normalization is a database design technique that organizes tables in a manner that reduces redundancy and dependency of data.

Normalization divides larger tables to smaller tables and links them using relationships.

Denormalization:

Denormalization is the reverse process of normalization. It’s the controlled introduction of redundancy into the database design. It helps improve query performance as the number of joins could be reduced

Summary

These are the frequently asked SQL Interview Questions and Answers based on our interview experience. I hope this article will be helpful for your SQL developer interview preparation.

Continue learning: Useful links for Technical Interview Questions and Answers