SQL Interview Questions

SQL Interview Questions with Answers

In this article I am sharing sql interview questions with answers. In this tutorial I am trying to cover almost all frequently asked sql questions. These sql interview questions will help both fresher and experienced candidates. This article will help to prepare sql concepts for the interview point of view. These are 26 frequently asked interview questions with answers

What is Database

Database is the collection of data which is stored in an organized way.

What is RDBMS

RDBMS stands for Relational Database Management System. It is the collection of tables which is related to each other

What is SQL

SQL stands for Structured Query Language. This language is used to interact with Relational Database Management System.

SQL Commands are case sensitive or not

No, sql commands are not case sensitive.

What does Null Represent

Null Represents undefined value

What is distinct

Distinct is used to supress duplicate row in select query

How to sort data in sql

We use order by clause to sort data either in ascending or descending order

What is the default order of sorting in sql

Ascending is the default order

What is Data Integrity

Data integrity ensures that the data which is going to enter in the database is reliable, accurate and consistent. Data integrity maintains the correctness and completeness of data.

What is Constraints

Constraints are the rules which we applied on the table’s column to ensure the data integrity.

What is the use of check constraints

When we want to make a particular column to be restricted for particular data in that case we use check constraints

For example

We want to restrict gender column to take value only either ‘M’ or ‘F’ then we use check constraints in the following manner

		
create table t7(id int, gender char (1) check (gender in ('M','F'))); 


		
		

Take another example in which we have a column age in which we want to add age above 36 then we use check constraints.

		
create table t8(id int, age int check(age>36)); 

How to create auto increment column in sql server

We use identity in sql server to create auto increment column.

		
create table emp(id int primary key identity(1,1), name varchar(100), salary int)

What is Stored Procedure

The stored procedure is a set of SQL Statements. It is precompiled object. Stored procedure increases the performance of the database as they are precompiled.

What is trigger

Triggers can be defined as an area of code Which executes in response of a particular action like insert, update, delete, Create, drop etc. Triggers executes automatically.

What is the type of triggers

We have two types of triggers

a) DDL Triggers b) DML Trigger

DML Triggers further categorized in following two category

• a) For trigger
• b) Instead of trigger

Which operator is used to select null value

We use IS NULL operator to select null value.

Which operator we use to match pattern

We use like operator to match pattern.

What is difference between primary key and unique key

Difference between primary key and unique key are as follows: -
a) We cannot enter null in primary key but we can have one null value in unique key column.
b) When we create primary key cluster index created automatically but unique key non clustered index created automatically.
c) Searching will be fast with the help of primary key as compare to unique key.
d) A table can have only one primary key but we can have more than one unique key.

Difference between Delete and Truncate

Both commands are used to remove data from tables but they have following differences: -
1. Delete is DML command but Truncate is DDL command.
2. Delete command can be used with where clause if we need to delete specific row but truncate command doesn’t work with where clause so it removes all rows.
3. Truncate set the identity from the beginning but delete not.
4. Delete command delete row one by one and maintains its data in transaction log but truncate de allocates its data pages.
5. Delete is slower than truncate as it has to maintains its each entry in transaction log.
6. Delete can be roll backed but truncate not.
7. Delete command activates all triggers if any associated but truncate not.
Note: - delete and truncate both can be roll backed if used with transaction.

What is difference between count and count (*)

1. Count function require column to count data but count (*) will count all rows
2. Count function do not count NULL values in column but count (*) will count all rows whether any column having null or not.

What is difference between where and having clause

1. Where clause is used to filter the row but having clause is used to filter groups
2. We can use where clause with select, insert and update statements but having can be used only with select statement.
3. Where clause comes before group and having always comes after the group in select statement.

What is difference between stored procedure and user defined function

There are following difference between stored procedure and user defined function
a) We can use all DML command like insert, update and delete and select command in stored procedure but user defined function can use only select statement.
b) Function must have to return a value but in case stored procedure it is not mandatory.
c) We can call function from stored procedure but we can’t call stored procedure from function.
d) We can use function in select statement and can use its output with select query but we can’t call stored procedure from select query. We need to use exec command to execute stored procedure.
e) We can’t use transactions and exception handling in function but stored procedure support both transactions and exception handling.
f) Function support only input parameter not ouput parameter but in stored procedure we can use both.
g) User defined function can return only single value but stored procedure can return multiple value result set.

Write a query to find out the 2nd highest salary

Let’s assume we have following table

So we will perform following query to find out the 2nd highest salary records.

select top 1 * from (select top 2 * from emp order by salary desc) as temp_table order by salary 
			
		

it will show the following output

What is identity?

Identity is used to create auto increment column in sql server.

How to delete column from the table?

We use alter command to delete column from the table. Let’s we have following emp table in which I want to delete salary column

alter table emp drop column salary
		
		

How to delete table in sql server

We use drop command to delete the table

Syntax

Drop table table_name

drop table emp