35 Essential Basic-Advanced SQL Query-Questions Asked In Technical Interviews

This article is an attempt to answer the most asked essential queries in SQL Server technical interviews, based on my own experience. No theoretical questions are included; this article is meant to be helpful in technical coding interviews.

 

Sample tables are listed to visualize the data and associate with query answers given.

==================
 Consider below tables
==================

EMPLOYEE
empid   empname managerid   deptid  salary  DOB
1       emp 1       0       1       6000    1982-08-06 00:00:00.000
2       emp 2       0       5       6000    1982-07-11 00:00:00.000
3       emp 3       1       1       2000    1983-11-21 00:00:00.000
13      emp 13      2       5       2000    1984-03-09 00:00:00.000
11      emp 11      2       1       2000    1989-07-23 00:00:00.000
9       emp 9       1       5       3000    1990-09-11 00:00:00.000
8       emp 8       3       1       3500    1990-05-15 00:00:00.000
7       emp 7       2       5       NULL    NULL
3       emp 3       1       1       2000    1983-11-21 00:00:00.000

--DEPARTMENT TABLE
deptid  deptname
1       IT
2       Admin

Q1 Employee and Manager ID are in the same table; can you get manager names for employees?

Answer:

With the help of Common table expressions, we can achieve this.

;with empCTE as
(
select e.empid, e.empname, e.managerid,
      CAST('' as varchar(50)) as Mname from employee e
where managerid = 0

union all

select e1.empid, e1.empname, e1.managerid,
       CAST(c.empname as varchar(50)) as Mname from employee e1
inner join empCTE as C on e1.managerid=c.empid
where e1.managerid>0
) select * from empCTE

Q2 Can you get employee details whose department id is not valid or department id not present in department table?

Answer

Identifying Department IDs in employee table, which are not available in master.

There are multiple ways to do this. 

 

Using Left JOIN 

SELECT E.EMPID,E.EMPNAME, E.DEPTID FROM EMPLOYEE E
left outer join DEPARTMENT d
on E.DEPTID = D.DEPTID
WHERE D.DEPTID IS NULL

Using NOT IN

SELECT E.EMPID,E.EMPNAME, E.DEPTID FROM EMPLOYEE E
where e.deptid not in (select deptid from department)

Using NOT Exists 

SELECT E.EMPID,E.EMPNAME, E.DEPTID FROM EMPLOYEE E
where NOT EXISTS (select deptid from department where e.deptid=department.deptid)

Note

"Not In"  is the least recommended, considering performance. Outer join and Not Exists are preferred.

Using EXCEPT KEYWORD

if you want to list Department IDs only. INTERSECT and EXCEPT keywords have rules 

SELECT deptid FROM EMPLOYEE
EXCEPT
SELECT DEPTID FROM DEPARTMENT

Q3. Can you get the list of employees with same salary? 

Answer: 

With where clause

Select distinct e.empid,e.empname,e.salary
from employee e, employee e1
where e.salary =e1.salary
and e.empid != e1.empid

Q4 How can you find duplicate records in Employee table?

Answer: 

SELECT EMPID,EMPNAME, SALARY, COUNT(*) AS CNT
FROM EMPLOYEE
GROUP BY EMPID,EMPNAME, SALARY
HAVING COUNT(*)>1

Q5 How can you  DELETE DUPLICATE RECORDS?

Answer

 

There are multiple options to perform this operation.

Using row count to restrict delete only 1 record 

set rowcount 1
DELETE FROM EMPLOYEE WHERE EMPID IN (
SELECT EMPID
FROM EMPLOYEE
GROUP BY EMPID,EMPNAME, SALARY
HAVING COUNT(*)>1
)
set rowcount 0

Use auto increment primary key "add" if not available in the table, as in given example.

alter table employee
add empidpk int identity (1,1)

Now, perform query on min of auto pk id, group by duplicate check columns - this will give you latest duplicate records 

select * from employee where
empidpk not in ( select min(empidpk) from employee
group by EMPID,EMPNAME, SALARY )

Now, delete.

Delete from employee where
empidpk not in ( select min(empidpk) from employee
group by EMPID,EMPNAME, SALARY )

Q6 Find the second highest salary.  

Answer

Select max(Salary) from employee
where Salary not in (Select max(Salary) from employee)

Q7 Now, can you find 3rd, 5th or 6th i.e. N'th highest Salary?

Answer 

 

Query for 3rd highest salary 

SELECT * FROM EMPLOYEE E
WHERE 2 = (SELECT COUNT(DISTINCT E1.SALARY)
FROM EMPLOYEE E1
WHERE E1.SALARY>E.SALARY)

Here, 2= 3-1 i.e. N-1 ; can be applied for any number. 

 

Q8. Can you write a query to find employees with age greater than 30? 

Answer

select * from employee
where datediff(year,dob, getdate()) >30

Q9 Write an SQL Query to print the name of the distinct employees whose DOB is between 01/01/1960 to 31/12/1987

Answer 

SELECT DISTINCT EmpName FROM Employee
WHERE DOB BETWEEN '01/01/1960' AND '12/31/1987'

Q10  Please write a query to get the maximum salary from each department. 

Answer 

select DeptId, max(salary) as Salary from employee group by deptid

Q11 What is wrong with the following query?

 

SELECT empName FROM employee WHERE salary <> 6000

 Answer

The following query will not fetch record with the salary of 6000 but also will skip the record with NULL.

As per SQL Server logic, it works on 3 values in matching conditions. TRUE or FALSE and UNKNOWN. Here,  NULL implies UNKNOWN.

 

to fix this:

SELECT empName  FROM
employee WHERE salary is NULL or salary <> 6000

Q12. Can you show one row twice in results from a table?

Answer

Yes. We can use union all or cross join to obtain this. 

select deptname from department d where d.deptname='it'
union all
select deptname from department d1 where d1.deptname='it'

 -- also cross join alias same table

select d.deptname from department d, department d1
where d.deptname='it'

Q13 Could you tell the output or result of the following SQL statements? 

Answer 

select '7'
-- output = 7
select 7
-- output = 7
select count (7)
-- output = 1
SELECT COUNT('7')
-- output = 1
SELECT COUNT(*)
 -- output = 1

Q14 What is an alternative for TOP clause in SQL? 

Answer 

 

- There can be two alternatives for the top clause in SQL.

#1 

-- Alternative - ROWCOUNT function 

Set rowcount 3
Select * from employee order by empid desc
Set rowcount 0

#2 

-- Alternative and  WITH and ROWNUMBER function

-- between 1 and 2

With EMPC AS
( SELECT empid, empname,salary,
ROW_NUMBER() OVER (order by empid desc) as RowNumber
FROM employee )
select *
from EMPC
Where RowNumber Between 1 and 7

Q15  Will the following statements  run or give error?

Answer

NO error. 

SELECT COUNT(*) + COUNT(*)
Output  = 2
SELECT (SELECT 'c#')
Output = c#

Q16 Can you write a query to get employee names starting with a vowel?

Answer 

  

Using like operator and expression, 

Select empid, empname from employee where empname like '[aeiou]%'

Q17 Can you write a query to get employee names ending with a vowel? 

Answer 

Select empid, empname from employee where empname like '%[aeiou]'

Q18 Can you write a query to get employee names starting and ending with a vowel?

Answer 

 

Here you will get only one record of "empone".

select empid, empname from employee where empname like '[aeiou]%[aeiou]'

Q19  Write a query to get employees whos ID is even.

Answer 

select * from employee
where empid %2 =0

Q20 Write a query to get employees whos ID is an odd number. 

Answer 

select * from employee
where empid %2 !=0

Q21 How can you get random employee record from the table?

Answer 

select top 1 * from employee order by newid()

Q22(Tricky) Below is the table data which has 1 columns and 7 rows

Table -TESTONE

DATACOL
10/12
1a/09
20/14
20/1c
3112
11/16
mm/pp

Give data in a table is of format 'NN/NN', verify that the first and last two characters are numbers and that the middle character is '/'.

 Answer

Print the expression 'NUMBER' if valid, 'NOT NUM' if not valid.

This can be done using like operator and expression. Checking numbers and not characters.

SELECT DataCol, 'CHECK' =
CASE
WHEN datacol like '%[0-9]%[^A-Z]%/%[^A-Z]%[0-9]%' then 'NUMBER'
else 'NOT NUM'
end
from TestOne

Q23 Consider following 3 tables with one column

Tbl1
col1
1
1
1

Tbl2
col1
2
2
2

Tbl3
col1
3
3
3

How many rows will following query return? (0, 3 or 9)

Select * from Tbl1 inner join tbl2 on tbl1.col1=tbl2.col1
Left outer join Tbl3 on Tbl3.Col1=Tbl2.Col1

Answer- 0 .

 

Q24 If all values from tbl2 are deleted. What will be the output of the following query?

Answer 

 

select Tbl1.* from tbl1,tbl2

Ans - 0 Rows.

 

Q25 Can you write a query to print prime numbers from 1 to 100?

Answer

For this, we have to use a loop as in other programming languages.  

DECLARE

@i INT,
@a INT,
@count INT,
@result varchar(Max)

SET @i = 1
set @result=''

WHILE (@i <= 100)
BEGIN
      SET @count = 0
      SET @a = 1

-- logic to check prime number
      WHILE (@a <= @i)
      BEGIN
         IF (@i % @a = 0)
            SET @count = @count + 1

         SET @a = @a + 1
      END

      IF (@count = 2)
         set @result = @result+cast(@i as varchar(10))+' , '

SET @i = @i + 1
END

set @result = (select substring(@result, 1, (len(@result) - 1)))
print(@result)

Q26 Write query to print numbers from 1 to 100 without using loops

Answer 

 

This can be done using Common Table Expression without using a loop.

;with numcte
AS
(
SELECT 1 [SEQUENCE]

UNION ALL

SELECT [SEQUENCE] + 1 FROM numcte WHERE [SEQUENCE] <100
)

SELECT * FROM numcte

Q.27 What will be the output of following SQL?(tricky)  

Select $

Options  -
a. 0.00,
b. $,
c. 0,
d. Syntax Error

Answer  = 0.00

Q.28 What will be the output of following SQL queries? 

Select select 'TD'

Options -
1. TD,
2. Syntax Error,
3. select TD

Answer - Syntax Error. (Incorrect syntax near the keyword 'select'. )

select * from 'Employee'

Answer -  Incorrect syntax near 'Employee' .

Q29 What will be the outputs in the following SQL queries with aggregate functions? 

SELECT SUM (1+4*5)

Options - a.21,      b.25,         c.Error        d.10

Answer -: 21


SELECT MAX  (1,3,8)

Options - a.8,        b. 12,        c.Error        d.1

Answer -: Error. Max function takes only 1 argument.


SELECT Max ('TD')

Options -  a.TD         b. Error      c. 1       d.0

Answer-: TD


SELECT Max ('TD'+'AD')

Options -  a.TDAD         b. Error      c. T2D       d.0
Answer-: TDAD

Q.30 What will be the output of following queries? [Tricky involving 0] 

SELECT 0/0

A. Divide by 0 error,   B. 0
C. NULL,                   D. Incorrect syntax error

Answer -:  Divide by 0 error

SELECT  0/6

A. Divide by 0 error,   B. 0
C. 6,                         D. Incorrect syntax error

Answer -:  0

Q31 What will be the output of given statement? 

SELECT SUM (NULL)

Answer = Error. Cannot pass null type in SUM function.

Operand data type NULL is invalid for avg operator. 

Q32 What will be the output of given statement?

SELECT

MAX (NULL)

Answer = Error. Operand data type NULL is invalid for MAX operator.

Q. 33 Will following statement give error or 0 as output? 

SELECT

AVG (NULL)

Answer = Error. Operand data type NULL is invalid for Avg operator. 

Note

MIN, MAX,SUM,AVG none of these function takes NULL parameter/argument. Also, these functions accept only one argument.

Q.34 Will the following statements execute? if yes what will be output?

SELECT NULL+1

SELECT NULL+'1'

Answer - Yes, no error. The output will be NULL. Perform any operation on NULL will get the NULL result.

Q35 Will following statement give Divide by Zero error?

SELECT NULL/0

Answer- No. It will execute and result will be NULL.