SQL Server

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.
  1. ==================  
  2.  Consider below tables  
  3. ==================  
  1. EMPLOYEE
  2. empid   empname managerid   deptid  salary  DOB  
  3. 1       emp 1       0       1       6000    1982-08-06 00:00:00.000  
  4. 2       emp 2       0       5       6000    1982-07-11 00:00:00.000  
  5. 3       emp 3       1       1       2000    1983-11-21 00:00:00.000  
  6. 13      emp 13      2       5       2000    1984-03-09 00:00:00.000  
  7. 11      emp 11      2       1       2000    1989-07-23 00:00:00.000  
  8. 9       emp 9       1       5       3000    1990-09-11 00:00:00.000  
  9. 8       emp 8       3       1       3500    1990-05-15 00:00:00.000  
  10. 7       emp 7       2       5       NULL    NULL  
  11. 3       emp 3       1       1       2000    1983-11-21 00:00:00.000  
  12.   
  13. --DEPARTMENT TABLE  
  14. deptid  deptname  
  15. 1       IT  
  16. 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.
  1. ;with empCTE as    
  2. (    
  3. select e.empid, e.empname, e.managerid,  
  4.       CAST('' as varchar(50)) as Mname from employee e    
  5. where managerid = 0    
  6.   
  7. union all    
  8.   
  9. select e1.empid, e1.empname, e1.managerid,   
  10.        CAST(c.empname as varchar(50)) as Mname from employee e1    
  11. inner join empCTE as C on e1.managerid=c.empid    
  12. where e1.managerid>0    
  13. ) 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 
  1. SELECT E.EMPID,E.EMPNAME, E.DEPTID FROM EMPLOYEE E    
  2. left outer join DEPARTMENT d    
  3. on E.DEPTID = D.DEPTID    
  4. WHERE D.DEPTID IS NULL    
Using NOT IN
  1. SELECT E.EMPID,E.EMPNAME, E.DEPTID FROM EMPLOYEE E    
  2. where e.deptid not in (select deptid from department)    
Using NOT Exists 
  1. SELECT E.EMPID,E.EMPNAME, E.DEPTID FROM EMPLOYEE E  
  2. 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 
  1. SELECT deptid FROM EMPLOYEE  
  2. EXCEPT  
  3. SELECT DEPTID FROM DEPARTMENT  
Q3. Can you get the list of employees with same salary? 
Answer: 
With where clause
  1. Select distinct e.empid,e.empname,e.salary  
  2. from employee e, employee e1  
  3. where e.salary =e1.salary  
  4. and e.empid != e1.empid   
Q4 How can you find duplicate records in Employee table?
Answer: 
  1. SELECT EMPID,EMPNAME, SALARY, COUNT(*) AS CNT  
  2. FROM EMPLOYEE  
  3. GROUP BY EMPID,EMPNAME, SALARY  
  4. 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 
  1. set rowcount 1  
  2. DELETE FROM EMPLOYEE WHERE EMPID IN (  
  3. SELECT EMPID  
  4. FROM EMPLOYEE  
  5. GROUP BY EMPID,EMPNAME, SALARY  
  6. HAVING COUNT(*)>1  
  7. )  
  8. set rowcount 0  
Use auto increment primary key "add" if not available in the table, as in given example.
  1. alter table employee  
  2. 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 
  1. select * from employee where  
  2. empidpk not in ( select min(empidpk) from employee  
  3. group by EMPID,EMPNAME, SALARY )  
Now, delete.
  1. Delete from employee where  
  2. empidpk not in ( select min(empidpk) from employee  
  3. group by EMPID,EMPNAME, SALARY )  
Q6 Find the second highest salary.  
Answer
  1. Select max(Salary) from employee  
  2. 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 
  1. SELECT * FROM EMPLOYEE E    
  2. WHERE 2 = (SELECT COUNT(DISTINCT E1.SALARY)    
  3. FROM EMPLOYEE E1    
  4. 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
  1. select * from employee  
  2. 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 
  1. SELECT DISTINCT EmpName FROM Employee  
  2. WHERE DOB BETWEEN '01/01/1960' AND '12/31/1987'  
Q10  Please write a query to get the maximum salary from each department. 
Answer 
  1. select DeptId, max(salary) as Salary from employee group by deptid  
Q11 What is wrong with the following query?
 
  1. 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:
  1. SELECT empName  FROM   
  2. 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. 
  1. select deptname from department d where d.deptname='it'  
  2. union all  
  3. select deptname from department d1 where d1.deptname='it'  
 -- also cross join alias same table
  1. select d.deptname from department d, department d1  
  2. where d.deptname='it'  
Q13 Could you tell the output or result of the following SQL statements? 
Answer 
  1. select '7'  
  2. -- output = 7  
  3. select 7  
  4. -- output = 7  
  5. select count (7)  
  6. -- output = 1  
  7. SELECT COUNT('7')  
  8. -- output = 1  
  9. SELECT COUNT(*)  
  10.  -- 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 
  1. Set rowcount 3  
  2. Select * from employee order by empid desc  
  3. Set rowcount 0  
#2 
-- Alternative and  WITH and ROWNUMBER function
-- between 1 and 2
  1. With EMPC AS  
  2. ( SELECT empid, empname,salary,  
  3. ROW_NUMBER() OVER (order by empid desc) as RowNumber  
  4. FROM employee )  
  5. select *  
  6. from EMPC  
  7. Where RowNumber Between 1 and 7  
Q15  Will the following statements  run or give error?
Answer

NO error. 
  1. SELECT COUNT(*) + COUNT(*)  
  2. Output  = 2  
  3. SELECT (SELECT 'c#')  
  4. Output = c#  
Q16 Can you write a query to get employee names starting with a vowel?
Answer 
  
Using like operator and expression, 
  1. Select empid, empname from employee where empname like '[aeiou]%'    
Q17 Can you write a query to get employee names ending with a vowel? 
Answer 
  1. 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".
  1. select empid, empname from employee where empname like '[aeiou]%[aeiou]'   
Q19  Write a query to get employees whos ID is even.
Answer 
  1. select * from employee  
  2. where empid %2 =0  
Q20 Write a query to get employees whos ID is an odd number. 
Answer 
  1. select * from employee  
  2. where empid %2 !=0  
Q21 How can you get random employee record from the table?
Answer 
  1. select top 1 * from employee order by newid()  
Q22(Tricky) Below is the table data which has 1 columns and 7 rows
  1. Table -TESTONE
  3. DATACOL
  4. 10/12
  5. 1a/09
  6. 20/14
  7. 20/1c
  8. 3112
  9. 11/16
  10. 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.
  1. SELECT DataCol, 'CHECK' =
  2. CASE
  3. WHEN datacol like '%[0-9]%[^A-Z]%/%[^A-Z]%[0-9]%' then 'NUMBER'
  4. else 'NOT NUM'
  5. end
  6. from TestOne
Q23 Consider following 3 tables with one column
  1. Tbl1
  2. col1
  3. 1
  4. 1
  5. 1
  7. Tbl2
  8. col1
  9. 2
  10. 2
  11. 2
  13. Tbl3
  14. col1
  15. 3
  16. 3
  17. 3
How many rows will following query return? (0, 3 or 9)
  1. Select * from Tbl1 inner join tbl2 on tbl1.col1=tbl2.col1
  2. 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.  
  1. DECLARE  

  3. @i INT,  
  4. @a INT,  
  5. @count INT,  
  6. @result varchar(Max)  

  8. SET @i = 1  
  9. set @result=''  

  11. WHILE (@i <= 100)  
  12. BEGIN  
  13.       SET @count = 0  
  14.       SET @a = 1 

  16. -- logic to check prime number
  17.       WHILE (@a <= @i)  
  18.       BEGIN  
  19.          IF (@i % @a = 0)  
  20.             SET @count = @count + 1  
  21.   
  22.          SET @a = @a + 1  
  23.       END  

  25.       IF (@count = 2)  
  26.          set @result = @result+cast(@i as varchar(10))+' , '  
  27.   
  28. SET @i = @i + 1  
  29. END  
  30.   
  31. set @result = (select substring(@result, 1, (len(@result) - 1)))  
  32. 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.
  1. ;with numcte  
  2. AS  
  3. (  
  4. SELECT 1 [SEQUENCE]  
  5.   
  6. UNION ALL  
  7.   
  8. SELECT [SEQUENCE] + 1 FROM numcte WHERE [SEQUENCE] <100  
  9. )  
  10.   
  11. SELECT * FROM numcte  
Q.27 What will be the output of following SQL?(tricky)  
  1. Select $    
  2.   
  3. Options  -   
  4. a. 0.00,   
  5. b. $,  
  6. c. 0,  
  7. d. Syntax Error  
  8.   
  9. Answer  = 0.00  
Q.28 What will be the output of following SQL queries? 
  1. Select select 'TD'  
  1. Options - 
  2. 1. TD,  
  3. 2. Syntax Error,  
  4. 3. select TD  
  5.    
  6. Answer - Syntax Error. (Incorrect syntax near the keyword 'select'. )  
  7.    
  1. select * from 'Employee'  
  2.    
  3. Answer -  Incorrect syntax near 'Employee' .   
Q29 What will be the outputs in the following SQL queries with aggregate functions? 
  1. SELECT SUM (1+4*5)  
  2.   
  3. Options - a.21,      b.25,         c.Error        d.10   
  4.   
  5. Answer -: 21  
  6.   
  7.   
  8. SELECT MAX  (1,3,8)  
  9.   
  10. Options - a.8,        b. 12,        c.Error        d.1   
  11.   
  12. Answer -: Error. Max function takes only 1 argument.  
  13.   
  14.   
  15. SELECT Max ('TD')  
  16.   
  17. Options -  a.TD         b. Error      c. 1       d.0  
  18.   
  19. Answer-: TD  
  20.   
  21.   
  22. SELECT Max ('TD'+'AD')  
  23.   
  24. Options -  a.TDAD         b. Error      c. T2D       d.0  
  25. Answer-: TDAD  
Q.30 What will be the output of following queries? [Tricky involving 0] 
  1. SELECT 0/0  
  2.   
  3. A. Divide by 0 error,   B. 0  
  4. C. NULL,                   D. Incorrect syntax error  
  5.   
  6. Answer -:  Divide by 0 error  
  7.   
  8. SELECT  0/6  
  9.   
  10. A. Divide by 0 error,   B. 0  
  11. C. 6,                         D. Incorrect syntax error  
  12.   
  13. 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.

Up Next
    Ebook Download
    View all
    Learn
    View all
    Get Started

    You need to be a premium member to use this feature. To access it, you'll have to upgrade your membership.

    Become a sharper developer and jumpstart your career.

    Basic

    $0

    $

    . 00

    monthly

    For Basic members:

    • Standard Profile
    • Access learning courses
    • Access free e-books
    • 1x point
    Premium

    $20

    $

    . 00

    monthly

    For Premium members:

    • Earn 2x reward points
    • Premium profile with Verified check
    • Premium Avatar with status update
    • Learning courses
    • Unlimited Certifications and badges on profile
    • Unlimited Book downloads
    • Access to Premium content
    • Online Training
    • Unlimited PDF conversions and downloads
    • Professional Resume Writing
    • Interview Preparations Guide
    • 25 messages per month
    • 1 "Kodzu" avatar, basic
    Elite

    $45

    $

    . 00

    monthly

    For Elite members:

    • Bundle E-books
    • Access to all Learn Series
    • Unlimited Apply Jobs