First select maxmium salary in each department with corresponding Id. Then join the result table with Employee to get the Employee name who has maximum salary, DepartmentId and maximum salary. Then join the result table with the Department table to get the Department Name based on Department id.

Method 1: The key is to generate the rank of rows. In MySQL, we can set a variable to help do that (which has the same output as dense_rank()). Note there could be rows with the same rank, so we need select the distinct salary.

SelectDistinct Salary From (

Select Id, Salary,

@rank := if(@preSalary = Salary, @rank, @rank+1) AS Rank,

@preSalary := Salary AS preSalary

From Employee, (Select @rank:=0, @preSalary:=-1) var

Orderby Salary DESC

) tmp

Where Rank=N;

Method 2: In SQL server and Oracle, we can use rank() and dense_rank() function to generate rank directly. Note if there are ties, dense_rank() always returns consecutive integers, while rank() returns discrete ones. For the difference between these two function, see here.

This is similar to ranking question, in which we set a variable to count consecutive numbers. We create a new column to record the number of consecutive numbers. If the Num is the same as the previous one, then add the count by one, and reset the count to be 1 if the Num is different.

Note: since it doesn't need rank, in SQL server and Oracle you can't use rank() to solve this problem.

This is also a ranking problem. But different from previous ones, we need generate the rank for each group of each department.

Method 1: In MySQL, we set a variable to generate the rank within each group. If the group(departmentId) changed, then set the rank to default. Then join the generated table with Department table to get the department name.

Method 2: Similarly, in SQL server and Oracle, we can use Dense_rank() function to generate the rank easily. Note according to the question, the tie should have the same rank. So Dense_rank() is used here.

Select D.Name AS Department, T.Name AS Employee, T.Salary

From

(

Select DepartmentId, Name, Salary,

Dense_rank() Over (partition by DepartmentId Orderby Salary DESC) AS Rank

Method 1: Set dummies variables, compare each day's temperature and date with its previous day. Then select the day in which temperature is rising and date is continuous. (Don't know why this method is very slow, almost exceeded time limit).

The key of this question is computing the cancellation rate. To compute it, we need count the number of trips cancelled by driver, and total number of trips within each group. The group is regularized by the date. Before the group by clause, use where clause to filter the rows which meet the requirement.