Top 10 - SQL Server Interview Queries

In this post I will talk about the top 10 most asked interview queries questions into the interview room from the SQL Developer/DotNet Developer it is also important for the experienced candidates.

First I'm going to create a database and tables then insert some data into database tables to perform actions.

Let's see one by one...

Step 1

Here I'm creating a database with InterviewPoint as the database name

Check all the questions in the video here for your reference.

Create Database InterviewPoint
Use InterviewPoint

Step 2

Create required tables,

Create table State(Id int primary key identity(1,1), Name varchar(50))
Create table City(Id int primary key identity(1,1), Name varchar(50), StateId int foreign key references State(Id))
Create table Employee(Id int Primary key identity(1,1), Name varchar(50), Email varchar(50) Unique, Mobile varchar(10), Salary decimal(10,2), Department Varchar(50), CityId int foreign key references City(Id), StateId int foreign key references State(Id))

Step 3

Insert data into created database tables,

--Insert Data into State table----
Insert into State(Name)Values('Delhi')
Insert into State(Name)Values('U.P.')
Insert into State(Name)Values('Maharastra')
Insert into State(Name)Values('M.P.')
--Insert data into City Table--
Insert into City(Name, StateId)Values('New Delhi',1)
Insert into City(Name, StateId)Values('Agra',2)
Insert into City(Name, StateId)Values('Lucknow',2)
Insert into City(Name, StateId)Values('Noida',2)
Insert into City(Name, StateId)Values('Mumbai',3)
Insert into City(Name, StateId)Values('Pune',3)
Insert into City(Name, StateId)Values('Bhopal',4)
Insert into City(Name, StateId)Values('Indore',4)
--Insert data into Employee table
Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('John','[email protected]', '9988778899', 35000, 'DotNet', 1, 1)
Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Peter','[email protected]', '9988778800', 40000, 'Android', 1, 1)
Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Mohan','[email protected]', '9988778888', 25000, 'Java', 2, 2)
Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Sohan','[email protected]', '9988778855', 80000, 'DotNet', 1, 1)
Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Ram','[email protected]', '9988777700', 35000, 'Android', 2, 2)
Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Ajay','[email protected]', '9988778811', 50000, 'DotNet', 3, 2)
Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Sumit','[email protected]', '7588778899', 35000, 'Python', 2, 2)
Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Martin','[email protected]', '9011778899', 75000, 'DotNet', 6, 3)
Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Sanjay','[email protected]', '9888778899', 35000, 'Java', 2, 2)
Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Rohit','[email protected]', '9088778899', 45000, 'DotNet', 1, 1)
Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Mukesh','[email protected]', '9500778899', 65000, 'Android', 7, 4)

Step 4

Now in this step, I'm writing a query for the given questions

WAQ to get a second highest salary from the employee table (WAQ* = Write a query)

Select Max(Salary) from Employee where Salary<(Select Max(Salary) from Employee)

WAQ to get details of all employees who are working in 'New Delhi' location

Select * from Employee where CityId=(Select Id from City where Name='New Delhi')

WAQ to get the last record from the Employee table

Select TOP(1) Name from Employee order by Id desc

WAQ to get Number of employees in Each Department

Select Department, Count(*) Count from Employee group by Department

WAQ to Create a table and Copy all data from another table

SELECT * INTO Employee2018 FROM Employee

WAQ to get last inserted Id

Select scope_identity()
Select Ident_current('Employee')

WAQ to display the sum of salary from Employee based on department

Select Department, Sum(Salary) from Employee group by Department

WAQ to get 3rd highest salary from the Employee table without using Max()

Select Salary from Employee e1 where 3-1=(Select count(distinct Salary) from Employee e2 where e2.Salary>e1.Salary)

WAQ to find Employee whose name starts with 's'

Select * from Employee where Name like 's%'

WAQ to add new Column(DOB) in the Employee table

Alter table Employee Add DOB datetime

WAQ to drop DOB column from Employee table

Alter table Employee drop column DOB