SQL Tricky Questions

You are already done with the basics of SQL on Day 10. No matter how good you are at it, there are some tricky questions which might confuse you in its trap. Below are some not so difficult but confusing questions. Try to solve it on your own before hoping on the solution.

For the next 7-8 questions, we will be referring to the below table

Emp. ID	Name	Salary
123	Amit	50000
453	Sumit	50000
232	Rakshit	30000
124	Aman	40000
543	Rahul	30000

1. What is ROW_NUMBER() function in SQL?
– The ROW_NUMBER() ranking returns a unique and sequential number to
each row of the table without repeating or skipping any number. If there are two rows which are partitioned and holds the same value as Amit and Sumit, then the row number will be given randomly.

2. Write the syntax to create a new column using Row Number over the Salary column

SELECT *, ROW_NUMBER() OVER (Order By Salary) as Row_Num
FROM Employee

Output

Emp. ID	Name	Salary	Row_Num
232	Rakshit	30000	1
543	Rahul	30000	2
124	Aman	40000	3
123	Amit	50000	4
453	Sumit	50000	5

3. What is PARTITION BY clause?
PARTITION BY clause is used to create a partition of ranking in a table. If you partition by Salary in the above table, then it will provide a ranking based on each unique salary. Example below:-

SELECT *, ROW_NUMBER() OVER (PARTITION BY Salary ORDER BY Salary) as Row_Num

Emp. ID	Name	Salary	Row_Num
232	Rakshit	30000	1
543	Rahul	30000	2
124	Aman	40000	1
123	Amit	50000	1
453	Sumit	50000	2

4. What is a RANK() function? How is it different from ROW_NUMBER()?
– RANK() function gives ranking to a row based on the value on which you want to base your ranking. If there are equal values, then the rank will be repeated and the row following the repeated values will skip as many ranks as there are repeated values row. Confused?? Try out the example below:-

SELECT *, RANK() OVER (ORDER BY Salary) as Row_Num
FROM Employee

Output

Emp. ID	Name	Salary	Row_Num
232	Rakshit	30000	1
543	Rahul	30000	1
124	Aman	40000	3
123	Amit	50000	4
453	Sumit	50000	4

As you can see, the rank 2 has been skipped because there were two employees with the same Salary and the result is ordered in ascending order by default.

5. How to use PARTITION BY clause in RANK() function?
– There is no point using PARTITION BY clause with RANK() function. The ranking result will have no meaning, as the rank will be done according to Salary values per each partition, and the data will be partitioned according to the Salary values. And due to the fact that each partition will have rows with the same Salary values, the rows with the same Salary values in the same partition will be ranked with the value equal to 1.

SELECT *, RANK() OVER (PARTITION BY Salary ORDER BY Salary) as Row_Num

OUTPUT

Emp. ID	Name	Salary	Row_Num
232	Rakshit	30000	1
543	Rahul	30000	1
124	Aman	40000	1
123	Amit	50000	1
453	Sumit	50000	1

6. What is Dense Ranking?
– DENSE_RANK() is similar to the RANK() function but it does not skip any rank, so if there are two equal values then both will be termed as 1, the third value will be termed as 3 and not 2.

Syntax:-
SELECT *, DENSE_RANK() OVER (PARTITION BY Salary ORDER BY Salary) as Row_Num
FROM Employee

Output:-

Emp. ID	Name	Salary	Row_Num
232	Rakshit	30000	1
543	Rahul	30000	1
124	Aman	40000	3
123	Amit	50000	4
453	Sumit	50000	4
432	Nihar	60000	6

7. What is NTILE() function?
-NTILE() is similar to percentile NTILE(3) will divide the data in 3 parts.

SELECT *, NTILE() OVER (ORDER BY Salary) as Ntile
FROM Employee

The number of rows should be 6/3 = 2, therefore we need to divide the 2 rows for each percentile

Emp. ID	Name	Salary	Ntile
232	Rakshit	30000	1
543	Rahul	30000	1
124	Aman	40000	2
123	Amit	50000	2
453	Sumit	50000	3
432	Nihar	60000	3

8. How to get the second highest salary from a table?
Select MAX(Salary)
from Employee
Where Salary NOT IN (SELECT MAX(Salary) from Employee)

9. Find the 3rd Maximum salary in the employee table
-Select distinct sal
from emp e1
where 3 = ((select count(distinct sal) from emp e2 where e1.sal <= e2.sal);

10. Get all employee detail from EmployeeDetail table whose “FirstName” not start with any single character between ‘a-p’
– SELECT *
FROM EmployeeDetail
WHERE FirstName like ‘[^a-p]%’

11. How to fetch only even rows from a table?
-The best way to do it is by adding a row number using ROW_NUMBER() and then pulling the alternate row number using row_num%2 = 0

Suppose, there are 3 columns in a table i.e. student_ID, student_Name, student_Grade. Pull the even rows

SELECT *
FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY student_ID) as row_num FROM student) x
WHERE x.row_num%2=0

12. How to fetch only odd rows from the same table?
-Simply apply the x.row_num%2 <> 0 to get the odd rows

SELECT *
FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY student_ID) as row_num FROM student) x
WHERE x.row_num%2 <> 0

13. How to find the minimum salary using subquery?
-SELECT *
FROM employee
WHERE salary = (select MIN(salary) from employee);

14. How to find the second minimum salary?
– SELECT *
FROM employee
WHERE salary = (SELECT MIN(salary) FROM employee > SELECT MIN(salary) FROM employee)

Similarly, find the third minimum salary

– SELECT *
FROM employee
WHERE salary = (SELECT MIN(salary) FROM employee > SELECT MIN(salary) FROM employee > SELECT MIN(salary) FROM employee)

15. The above query is too lengthy, write a query to get the third minimum salary with some other method.

– SELECT DISTINCT (salary)
FROM emp e1 where 3 = (SELECT COUNT(DISTINCT salary) FROM emp e2 WHERE e1.sal >= e2.sal);

16. How to get 3 Min salaries?
-SELECT DISTINCT salary FROM emp a WHERE 3 >= (SELECT COUNT(DISTINCT salary) FROM emp b WHERE a.salary >= b.salary);

17. Some basic SQL Select questions
– SELECT 125
125
-SELECT ‘Ankit’+’1’
Ankit1
-SELECT ‘Ankit’+1
Error
– SELECT ‘2’+2
4
-SELECT SUM(‘1’)
1

18.
SELECT CURDATE(); à It returns the current date(MySQL)
SELECT NOW(); à It returns the current date and time(MySQL)
SELECT getdate(); à It returns the current date and time(SQL Server Query)
SELECT SYSDATE FROM DUAL à This Oracle query returns the current date and time

19. Explain Scalar functions.
Scalar functions are those functions which are used to return a single value based on the input. Following are the commonly used scalar functions in SQL:-
1. UCASE() – Convert to Upper Case
2. LCASE() – Convert to Lower Case
3. MID() – It extracts a substring from a string. SELECT MID(“ABCDEFGH”,3,6) AS ExtractedString
It will extract 6 characters starting from 3^rd character i.e. C,D,E,F,G,H
4. FORMAT() – It specifies the display format
5. LEN() – Gives the length of a text field
6. ROUND() – Rounds up the decimal field in a number

20. Write a generic method to fetch the nth highest salary without TOP or Limit
–
SELECT Salary
FROM Worker W1
WHERE n-1 = (
 SELECT COUNT( DISTINCT ( W2.Salary ) )
 FROM Worker W2
 WHERE W2.Salary >= W1.Salary
 );

Basically clear your concept about Ranking, sub-queries, joins and functions.

We will also be publishing “100 Tough SQL questions to practice before Data Science Interview”. Keep checking for the update.

Keep practicing.

XtraMous