>>>>>>SQL Tricky Interview Questions
##### Write a query to find maximum salary of each department in an organization
 DeptNo Dname Loc 50 Service Delhi 51 Account Mumbai
SELECT Department_Name,
Max(Salary)
FROM Department_Table
GROUP BY Department_Name
##### What is wrong with the following query? SELECT Id, Year(PaymentDate) as PaymentYear FROM Bill_Table WHERE PaymentYear > 2018;
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;
##### What is the order of execution in a query?
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
##### What is ROW_NUMBER() function?
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
##### What is RANK() function?
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;