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

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.

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()?**

–

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 ****sub****query?**

-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

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