SQL Intermediate Questions

Let’s get started with some intermediate level SQL queries. Try to frame an approach before hoping to the solution because once you see the answer you will find it easy to understand.

I think there will be around 15 questions in the article below and do try to score at least 12+ before moving to the next article.

Day 2 – Introduction to SQL
Day 3 – SQL queries

1. You know why so many interviewers ask you if you know how to find the second and third and nth largest salary? It’s because it gives them a glimpse of your query understanding. We have already solved the question in the last day’s article, but I remember when I was interviewing for Myntra they asked me 3 different approaches to solve the same question. I knew that shit, so did good 😛

Get the third largest salary from the employee table(Myntra,Ola)

Schema –

Table Name – Employee
Columns – EmpId, EmpName,Salary

Solution/Approach

 
 Select *
 From Employee E1
 Where (N-1) =
 (Select Count(Distinct(EmpSalary)) 
 From Employee E2 
 Where E2.EmpSalary > E1.EmpSalary)
 

The above query first take the distinct Salary’s count whenever the inner query is pulling salary greater than outer query. When you need 3^rd largest salary then you need to eliminate the top 2 salaries which is done by the where condition in the inner query.

2. Well, the above query looks a bit difficult to me to understand, can you get me a better query which is easier to understand? (Myntra)

Sure, We can try a simpler and easy way to understand it.

Select Top 1 EmpSalary
From (Select Distinct Top N EmpSalary From Employee Order By EmpSalary    Desc) a
Order by EmpSalary

Here N will be 3, So the inner query will get me top 3 salaries ordered in a descending order and then the outer query will fetch the top result which is the 3^rd largest salary.

3. I will give you complete marks if you can tell me one more method to do the same thing(Myntra)

Now I think I will have to make a temp table may be. Let’s see

 With CTE as 
 (Select EmpId,
 EmpName,
 EmpSalary,
 Row_Number() Over(Order By EmpSalary Desc) as Rank
 From Employee)
 
 Select * 
 From CTE
 Where Rank = N

The following questions have been repeated in at least 4 interviews I gave recently(Myntra,Ola,Affine Analytics and Unbxd)

You have two tables with one column each. The table A has 5 values and all the values are 1 i.e. 1,1,1,1,1 and Table B has 3 values and all the values are 1 i.e. 1,1,1.

How many rows will be there if we do the following:-

4. Inner Join

There will be 15 rows because each of the values from both the table will interact
I actually tried this in Python using Data Frame and following is the screenshot

5. Outer Join

Again there will be 15 rows because each values from both Table A and B will have to interact with each other

6. Left Join

Left Join will also be the same i.e. 15 rows. There is a catch in the order of the results though. Suppose we have two columns in each table

Table A
1 q
1 w
1 e
1 r
1 t

Table B
1 a
1 b
1 c

Left Join will give you 15 rows and the result will look like the one below

Doing Left Join Table A on Table B

Output

1 q a
1 q b
1 q c
1 w a
1 w b
1 w c

and so on.

7. Right Join

You must have guessed by now that the number of rows would be  15. The order of result would be

1 a q
1 a w
1 a e
1 a r
1 a t
1 b q

And so on.

Next is the classic question on self join and you gotta see this in your upcoming interviews. These questions were asked in the final round of EXL Services, Bangalore

8. There is a table with employee and manager information, the schema is given below. Write a query to get the EmployeeName and ManagerName. (Ola, Bangalore)

Schema

Table Name – Employee
Column Name – EmpId,EmpName,MangaerId

 SELECT e1.Name EmployeeName, e2.name AS ManagerName
 FROM Employee e1
 INNER JOIN Employee e2
 ON e1.ManagerId = e2.EmpId
 

9. What is one limitation in the above approach?(Ola, Bangalore)

If there is a NULL value in the ManagerId then it will not show up in the above table. To fix this we can have a  Left Join and can fill the NULL value with ‘NA’ or any other word.

SELECT e1.Name EmployeeName, ISNULL(e2.name, 'Top Manager') AS ManagerName
FROM Employee e1
LEFT JOIN Employee e2
ON e1.ManagerID = e2.EmployeeID

10. The Employee table have Department and Salary. I want to know the Maximum salary of each department

Schema –

Employee(Dept,Salary)

Solution

 SELECT Dept,Max(Salary) 
 FROM 
 (SELECT E1.Dept, E1,Salary 
 FROM Employee E1,(SELECT Dept,Max(Salary) FROM Employee GROUP BY Dept)E2 
 WHERE E2.Salary > E1.Salary) E
 GROUP BY Dept
 
  SELECT E1.Dept, E1,Salary 
 FROM Employee E1,SELECT Dept,Max(Salary) FROM Employee GROUP BY Dept 
 

This will get you all but the maximum salary. So the final group by will be applied to the dataset which do not have the second maximum salary.

11. Use Dense Rank to get the third highest salary(Deloitte)

SELECT T as( 
 SELECT EmployeeName,Dept,Salary, DENSE_RANK() Over (Partition By Dept Order By Salary Desc) as Dense_rank
 FROM Employee)
 
 SELECT * 
 FROM T
 WHERE Dense_rank = 3
 

12. What if I want to get the top 5 salary information? What will you change in the above query?(Deloitte)

 SELECT *
 FROM T
 WHERE Dense_rank <=5
 

13. What is the difference between heap table and temporary table?(Amazon)
Heap tables:
Heap tables are found in memory. They are used for high-speed storage on a temporary basis. They do not allow BLOB or TEXT fields.
Heap tables do not support AUTO_INCREMENT.
Indexes should be NOT NULL.

Temporary tables:
The temporary tables are used to keep the transient data. Sometimes it is beneficial in cases to hold temporary data. The Temporary table is deleted after the current client session terminates.

Main differences:
The heap tables are shared among clients while temporary tables are not shared.
Heap tables are just another storage engine, while for temporary tables you need a special privilege (create temporary table).

14. What is the result of following query?(Accenture)
select case when null=null then ‘Amit’ else ‘Rahul’ end from dual;

The null=null is always false.so the Answer of this query is Rahul.

15. What is the use of NVL function in Oracle?(Accenture)
NVL function is most important function to replace null value with another value.

Example:
select NVL(null,’Sachin’)
from dual;

which will give you output as Sachin.

16. Can we have another column in a table other than a primary key column which will act as a primary key?(Accenture)
Yes we can have another column in the table other than primary key which acts like primary key.But in database design this is not the recommended. One table can have only one primary key. But if you create other column with unique and not null constraint it acts like primary key.

17. Count the total salary department number wise where more than 2 employees exist.(BookMyShow)

SELECT deptno, sum(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2

18. How to fetch only common records from two tables emp and emp1?

(Select * from emp) Intersect (Select * from emp1)

I can go on and on with this article, but these are the snippets from my next book, so I need to hold my horses 😛

Will be back with some syntax heavy advance level interview questions

Keep Learning 🙂

The Data Monk