SQL SERVER INTERVIEW QUESTION AND ANSWERS PART 1

 

SQL Server Query Interview Questions with Answers

SQL Server DATEADD() Function

1) Write a Query to display the date after 15 days? 

SELECT DATEADD(dd, 15,getdate())

2) Write a Query to display the date after 12 months?

SELECT DATEADD(mm, 2, getdate())

2) Write a Query to display the date before 15 days? 

SELECT DATEADD(dd, -15, getdate())

SQL Server DATEDIFF() Function

3) Write a Query to display employee details along with exp?

SELECT *
DATEDIFF(yy, doj, getdate()) AS ‘Exp’ FROM employee

4) Write a Query to display employee details who is working in ECE department & who his having more than 3 years of exp?

SELECT *
DATEDIFF(yy, doj, getdate()) AS ‘Exp’ 
FROM employee 
WHERE DATEDIFF(yy, doj, getdate())>3 AND dept_name=’ECE’

5) Write a Query to display employee details along with age?

SELECT *
DATEDIFF(yy, dob, getdate()) AS ‘Age’ FROM employee

6) Write a Query to display employee details whose age >18?

SELECT *
DATEDIFF(yy, dob, getdate()) AS ‘Age’ FROM employee
WHERE DATEDIFF(yy, dob, getdate())>18

SQL Server Multi-Row Functions

7) Write a Query to display the minimum salary of an employee?

SELECT MIN (salary)
FROM employee

8) Write a Query to display the maximum salary of an employee?

SELECT MAX(salary)
FROM employee

8) Write a Query to display the total salary of all employees?

SELECT SUM(salary) FROM employee

10) Write a Query to display the average salary of an employee?

SELECT AVG(salary) FROM employee

11) Write a Query to count the number of employees working in the company?

SELECT COUNT(*) FROM employee

12) Write a Query to display the minimum & maximum salary of the employee?

SELECT MIN(salary) AS ‘min sal’, MAX(salary) AS ‘max sal’ FROM employee

13) Write a Query to count the number of employees working in the ECE department?

SELECT COUNT(*) FROM employee WHERE dept_name=’ECE’

14) Write a Query to display the second max salary of an employee?

SELECT MAX(salary)
FROM employee
WHERE salary < (SELECT MAX(salary) FROM emp)

15) Write a Query to display the third max salary of an employee?

SELECT MAX(salary)
FROM employee
WHERE salary < (SELECT MAX(salary) FROM emp where salary < (SELECT MAX(salary) FROM emp))

SQL SERVER: GROUP BY Clause

16) Write a Query to display the total salary of employees based on the city?

SELECT city, SUM(salary) 
FROM employee 
GROUP BY city;

17) Write a Query to display a number of employees based on the city?

SELECT city, COUNT(emp_no) 
FROM employee
GROUP BY city;

(OR)

SELECT city, COUNT(emp_no) AS ‘no.of employees’ 
FROM employee 
GROUP BY city;

18) Write a Query to display the total salary of employees based on region?

SELECT region, SUM(salary) AS ‘total_salary’ 
FROM employee
GROUP BY region;

19) Write a Query to display the number of employees working in each region?

SELECT region, COUNT(gender) 
FROM employee
GROUP BY region;

(OR) 

SELECT region, COUNT(gender) AS ‘no.of males’
FROM employee
GROUP BY region;

20) Write a Query to display minimum salary & maximum salary based on dept_name?

SELECT dept_name, MIN(salary) AS ‘min sal’, MAX(salary) AS ‘max sal’ 
FROM employee
GROUP BY dept_name

21) Write a Query to display the total salary of employees based on dept_name?

SELECT dept_name, SUM(salary) AS ‘total_sal’ 
FROM employee
GROUP BY dept_name

22) Write a Query to display no. of males in each department?

SELECT dept_name, COUNT(gender) 
FROM employee
GROUP BY dept_name 
WHERE gender=’male’

(OR)

SELECT dept_name, COUNT(gender) AS ‘no.of males’ 
FROM employee
WHERE gender=’male’ 
GROUP BY dept_name;

Note: We cannot apply where condition in GROUP BY CLAUSE if we want to apply use having clause.

We have to use WHERE condition before GROUP BY but cannot apply where condition after GROUP BY.

SQL SERVER: Having Clause

23) Write a Query to display the total salary of employees based on whose total salary > 12000?

SELECT city, SUM(salary) AS ‘total_salary’ 
FROM employee
GROUP BY city
HAVING SUM(salary)>12000;

24) Write a Query to display the total salary of all employees based on a city whose average salary >= 23000?

SELECT city, SUM(salary) AS ‘total_salary’ 
FROM employee
GROUP BY city
HAVING AVG(salary)  >= 23000;

SQL SERVER: SUB QUERIES

25) Write a Query to display employee details whose employee numbers are 101, 102?

SELECT *
FROM employee
WHERE Emp_No in (101, 102)

(OR)

SELECT * FROM employee
WHERE Emp_No in (select emp_no from emp)

26) Write a Query to display employee details belongs to the ECE department?

SELECT Emp_No, Emp_Name, Salary
FROM employee
WHERE dept_no in (select dept_no from dept where dept_name = ‘ECE’)

SQL SERVER TOP Clause

27) Write a Query to display the first record from the table?

SELECT TOP 1 * 
FROM employee

28) Write a Query to display the top 3 records from the table?

SELECT TOP 3 *
FROM employee

29) Write a Query to display the last record from the table?

SELECT TOP 1 *
FROM employee 
ORDER BY emp_no descending

SQL SERVER: Ranking Functions

Student Details Table:

Student_No	Student_Name	Percentage	Row_ID	Rank_ID	DenseRank_ID
105	James	87	1	1	1
106	John	83	2	2	2
101	Anil	83	3	2	2
104	Vijay	83	4	2	2
108	Rakesh	76	5	5	3
102	Sunil	76	6	5	3
103	Ajay	76	7	5	3
107	Ram	75	8	8	4

30) Write a Query to display student details along with the row_no order by student name?

SELECT *, ROW_NUMBER() OVER (ORDER BYstudent_name) AS ‘Row_ID’
FROM employee

31) Write a Query to display even records from the table?

SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY student_no) AS ‘ Row_ID’ FROM student)
WHERE row_id %2=0

32) Write a Query to display odd records from the student table?

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY student_no) AS Row_ID FROM student)
WHERE row_id %2!=0