Follow Us :

SQL Tricky Interview Questions

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

DeptNo Dname Loc
50 Service Delhi
51 Account Mumbai
SELECT Department_Name,
Max(Salary)
FROM Department_Table
GROUP BY Department_Name
Though the variable PaymentYear has already been defined in the first line of the query, but this is not the correct logical process order. The correct query will be SELECT Id, Year(PaymentDate) as PaymentYear FROM Bill_Table WHERE Year(PaymentDate)>2018;
The order of query goes like this:-
FROM – Choose and join tables to get the raw data
WHERE – First filtering condition
GROUP BY – Aggregates the base data
HAVING – Apply condition on the base data
SELECT – Return the final data
ORDER BY – Sort the final data
LIMIT – Apply a limit to the returned data
It assigns a unique id to each row returned from the query ,even if the ids are the same.
Sample query:- SELECT emp.*, row_number() over (order by salary DESC) Row_Number 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

Even when the salary is the same for Bhargav and Chirag, they have a different Row_Number, this means that the function row_number just gives a number to every row
The RANK() function is used to give a rank and not a row number to the data set. The basic difference between RANK() and ROW_NUMBER is that Rank will give equal number/rank to the data points with the same value. In the above case, RANK() will give a value of 2 to both Bhargav and Chirag and thus will rank Dinesh as 4. Similarly, it will give rank 5 to both Esha and Farhan.
Employee Name Salary Row_Number
Amit 7000 1
Bhargav 6000 2
Chirag 6000 2
Dinesh 5000 4
Esha 3000 5
Farhan 3000 6
SELECT emp.*, RANK() over (order by salary DESC) Ranking from Employee emp;