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;