Follow Us :

SQL Tricky Interview Questions

Avatto > > DATA SCIENTIST > > SHORT QUESTIONS > > SQL Tricky Interview Questions

NTILE() function distributes the rows in an ordered partition into a specific number of groups. These groups are numbered. For example, NTILE(5) will divide a result set of 10 records into 5 groups with 2 records per group. If the number of records is not divided equally in the given group, the function will set more records to the starting groups and less to the following groups.
Employee Name Salary Row_Number
Amit 7000 1
Bhargav 6000 1
Chirag 6000 2
Dinesh 5000 2
Esha 3000 3
Farhan 3000 3
SELECT emp.*, NTILE(3) over (order by salary DESC) as GeneratedRank from Employee emp This will divide the complete data set in 3 groups from the top. So the GeneratedRank will be 1 for Amit and Bhargav, 2 for Chirag and Dinesh: 3 for Esha and Farhan
This gives the rank of each row within a result set partition, with no gaps in the ranking values. Basically there is no gap, so if the top 2 employees have the same salary then they will get the same rank i.e. 1, much like the RANK() function. But, the third person will get a rank of 2 in DENSE_RANK as there is no gap in the ranking whereas the third person will get a rank of 3 when we use the RANK() function. The syntax below:-
Employee Name Salary Row_Number
Amit 7000 1
Bhargav 6000 2
Chirag 6000 2
Dinesh 5000 3
Esha 3000 4
Farhan 3000 5
SELECT emp.*, DENSE_RANK() OVER (order by salary DESC) DenseRank from Employee emp;
Employee Name Salary Row_Number
Amit 7000 1
Bhargav 6000 2
Chirag 6000 3
Dinesh 5000 4
Esha 3000 5
Farhan 3000 6
SELECT EmpID, EmpName
FROM Employee
where EmpName like ‘[aeiou]%’
Employee Name Salary Row_Number
Amit 7000 1
Bhargav 6000 2
Chirag 6000 3
Dinesh 5000 4
Esha 3000 5
Farhan 3000 6
SELECT EmpID,EmpName
FROM Employee
where EmpName like ‘[aeiou]%[aeiou]’
There are three types of statements in SQL:-
a. DDL – Data Definition Language
b. DML – Data Manipulation Language
c. DCL – Data Control Language