TOP 15 SQL Interview Questions and Answers

Updated on: February 19, 2023

Following are the top 15 SQL Interview Questions and Answers:

1. What are Indexes in SQL

Indexes are applied on table columns to improve the SELECT query results. We can add one or more Indexes on one or more columns of a table. Indexes creates lookup table which database search engine uses to search the results. Following is the way to create Index on table:

First, we will create a new Table with name as "Book":

Create Table Book
title nvarchar(200),
price float,
authorname nvarchar(200),
publishername nvarchar(500)

Now, we will create an Index on Book Table, following is the syntax of creating new Index on a table:

CREATE INDEX index_name on TABLE_NAME(Column_Name)

Now, Create UNIQUE Index named as book_title on Book table

CREATE UNIQUE INDEX book_title on Book(title)

Now, Create Non Unique Index named as book_author on Book table:

CREATE INDEX book_author on Book(authorname)

Following is the Schema of Book Table: It has created 3 Indexes on Book table, one is Clustered Index on "id" column which is our Primary Key in our table, and other 2 are non clustered index on title and authorname columns.

If we want to create Index on multiple columns, then following is the way to create it:

CREATE INDEX book_search on book(title,authorname)

Following is the syntax to use to DROP Index on book table:

DROP INDEX book_title on book

Clustered Index Vs Non Clustered Index:

Clustered Index
Non Clustered Index
Clustered Index sorts and store data rows in a table based on their key value, just like DictionaryNon Clustered stores data at one location and have pointer stored at another location to point to the data.
it is faster than Non-Clustered IndexIt is slower than Clustered Index
It requires less memory to execute the operation.It requires more memory to execute the operation.
A table will have only one Clustered IndexA table can have multiple Non-Clustered Index
Clustered Index stores the data pages in leaf nodes of IndexNon Clustered Index never stores data pages in leaf nodes of Index

2. What are Joins in SQL

3. What is View in SQL

4. What is CTE in SQL

5. What is Temp Table

6. What is Magic Tables

7. How to improve performance of SQL Stored Procedure

8. What is Deadlock in SQL

9. Transaction Scope in SQL SERVER

10. Scalar vs Table valued functions

11. Difference between Union and Union All

12. What are Constraints

13. Top Functions (COALESCE, Convert, CAST, CONCAT, LOWER, UPPER, MAX,    AVG, STUFF)

14. What are the way to get 3rd Highest Salary in SQL

15. Write query to delete Duplicate rows from table