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
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
Drop table table_name
drop table emp