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)
Table Name – Employee
Columns – EmpId, EmpName,Salary
From Employee E1
Where (N-1) =
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 3rd 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 3rd 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
Left Join will give you 15 rows and the result will look like the one below
Doing Left Join Table A on Table B
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)
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
(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
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)
WHERE Dense_rank <=5
13. What is the difference between heap table and temporary table?(Amazon)
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.
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.
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.
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
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