Post
Ask Question

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. 
  1. Create Database InterviewPoint    
  2. Use InterviewPoint    
Step 2
 
Create required tables,
  1. Create table State(Id int primary key identity(1,1), Name varchar(50))    
  2. Create table City(Id int primary key identity(1,1), Name varchar(50), StateId int foreign key references State(Id))    
  3.     
  4. 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,
  1. --Insert Data into State table----    
  2. Insert into State(Name)Values('Delhi')    
  3. Insert into State(Name)Values('U.P.')    
  4. Insert into State(Name)Values('Maharastra')    
  5. Insert into State(Name)Values('M.P.')    
  6.     
  7. --Insert data into City Table--    
  8. Insert into City(Name, StateId)Values('New Delhi',1)    
  9. Insert into City(Name, StateId)Values('Agra',2)    
  10. Insert into City(Name, StateId)Values('Lucknow',2)    
  11. Insert into City(Name, StateId)Values('Noida',2)    
  12. Insert into City(Name, StateId)Values('Mumbai',3)    
  13. Insert into City(Name, StateId)Values('Pune',3)    
  14. Insert into City(Name, StateId)Values('Bhopal',4)    
  15. Insert into City(Name, StateId)Values('Indore',4)    
  16.     
  17.     
  18. --Insert data into Employee table    
  19.     
  20. Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('John','[email protected]', '9988778899', 35000, 'DotNet', 1, 1)    
  21. Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Peter','[email protected]', '9988778800', 40000, 'Android', 1, 1)    
  22. Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Mohan','[email protected]', '9988778888', 25000, 'Java', 2, 2)    
  23. Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Sohan','[email protected]', '9988778855', 80000, 'DotNet', 1, 1)    
  24. Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Ram','[email protected]', '9988777700', 35000, 'Android', 2, 2)    
  25. Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Ajay','[email protected]', '9988778811', 50000, 'DotNet', 3, 2)    
  26. Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Sumit','[email protected]', '7588778899', 35000, 'Python', 2, 2)    
  27. Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Martin','[email protected]', '9011778899', 75000, 'DotNet', 6, 3)    
  28. Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Sanjay','[email protected]', '9888778899', 35000, 'Java', 2, 2)    
  29. Insert into Employee(Name,Email, Mobile, Salary,Department, CityId, StateId) Values('Rohit','[email protected]', '9088778899', 45000, 'DotNet', 1, 1)    
  30. 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)
  1. 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
  1. Select * from Employee where CityId=(Select Id from City where Name='New Delhi')    
WAQ to get the last record from the Employee table
  1. Select TOP(1) Name from Employee order by Id desc    
WAQ to get Number of employees in Each Department
  1. Select Department, Count(*) Count from Employee group by Department    
WAQ to Create a table and Copy all data from another table
  1. SELECT * INTO Employee2018 FROM Employee    
WAQ to get last inserted Id
  1. Select scope_identity()    
  2. Select Ident_current('Employee')    
WAQ to display the sum of salary from Employee based on department
  1. Select Department, Sum(Salary) from Employee group by Department    
WAQ to get 3rd highest salary from the Employee table without using Max()
  1. 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'
  1. Select * from Employee where Name like 's%'    
WAQ to add new Column(DOB) in the Employee table
  1. Alter table Employee Add DOB datetime  
WAQ to drop DOB column from Employee table
  1. Alter table Employee drop column DOB    
Next Recommended Reading A Scenario Based SQL Interview Queries/ Test Model/Training