sql-Database Brush problem

As a personal summary, list only useful or difficult for yourself:

Leetcode Marking difficulty requirements:--------the Employee employee table with the top three levels of departmental salary contains all employee information, each employee has its corresponding ID, salary, and department ID. +----+-------+--------+--------------+| Id | Name | Salary | DepartmentID |+----+-------+--------+--------------+| 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 |+----+-------+--------+--------------+department table contains information about all departments of the company. +----+----------+| Id | Name |+----+----------+| 1 | IT | | 2 | Sales |+----+----------+ Write an SQL query to find the top three employees in each department. For example, based on the given table above, the query result should return: +------------+----------+--------+| Department | Employee | Salary |+------------+----------+--------+| IT | Max | 90000 | | IT | Randy | 85000 | | IT | Joe | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 |+------------+----------+--------+ First solution:; with Employee (Id,name,salary,departmentid) AS (select 1, ' Joe ', ' 70000 ', 1 Union allselect 2, ' Henry ', ' 80000 ', 2 Union allselect 3, ' Sam ', ' 60000 ', 2 Union allselect 4, ' Max ' , ' 90000 ', 1 Union allselect 5, ' Janet ', ' 69000 ', 1 Union allselect 6, ' Randy ', ' 85000 ', 1 ', Department (Id,name) as (SELECT 1, ' IT ' UNION all select 2, ' Sales ') select D.name as department,e.name as Employee,e.salary from (SELECT *,row_number () over (Partition by DepartmentID ORDER BY Salary Desc) as Rank from Employee) e Joi                                             n Department d on e.departmentid = d.id where rank<=3 ORDER BY d.id ASC The second solution: in order to avoid the same ranking, the use of Dense_rank ranking select D.name as department,e.name as Emplo                                             Yee,e.salary from (SELECT *,dense_rank () up (partition by DepartmentID ORDER BY Salary Desc) as Rank from Employee) E                                             Join Department D on e.departmentid = D.id   where rank<=3                                          ORDER BY D.id ASC 

  Leetcode Difficulty tag: simply given a Weather table, write an SQL query to find the IDs of all dates that are higher than the previous (yesterday's) date. +---------+------------------+------------------+| Id (INT) | Recorddate (DATE) |       Temperature (INT) |+---------+------------------+------------------+|       1 |               2015-01-01 |       10 | |       2 |               2015-01-02 |       25 | |       3 |               2015-01-03 |       20 | |       4 |               2015-01-04 | |+---------+------------------+------------------+ For example, return the following id:+----based on the given Weather table above +|  Id |+----+|  2 | | 4 |+----+select Id from Weather w where temperature > (select temperature from Weather L where DATEADD (Day,1,l.recordd ATE) = w.recorddate)  

  Leetcode Difficulty tag: simply write a SQL query to get the second highest salary (Salary) in the Employee table. +----+--------+| Id | Salary |+----+--------+| 1 | 100 | | 2 | 200 | | 3 | |+----+--------+ For example the Employee table above, the SQL query should return 200 as the second highest salary. If there is no second-highest salary, the query should return NULL. +---------------------+| Secondhighestsalary |+---------------------+| |+---------------------+ Solution: SELECT MAX (SALARY) as secondhighestsalary from Employee WHERE SALARY < (S Elect MAX (SALARY) from Employee)  

Leetcode difficulty Tag: The Medium Employee table contains all employee information, each employee has its corresponding ID, salary, and department ID. +----+-------+--------+--------------+| Id | Name | Salary | DepartmentID |+----+-------+--------+--------------+| 1 | Joe | 70000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 |+----+-------+--------+--------------+department table contains information about all departments of the company. +----+----------+| Id | Name |+----+----------+| 1 | IT | | 2 | Sales |+----+----------+ Write a SQL query to find the highest paid employees in each department. For example, based on the given table above, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department. +------------+----------+--------+| Department | Employee | Salary |+------------+----------+--------+| IT | Max | 90000 | | Sales | Henry | 80000 |+------------+----------+--------+/* Write your T-SQL query statement below */select D.name as Department, E. Name as employee, e.salary as Salaryfrom employee E join Department d on e.departmentid = D.id Join (sele CT DepartmentId, Max (Salary) as Salary from Employee Group by DepartmentID) GRP on e.salary = grp. Salary and E.departmentid = grp. DepartmentID

sql-Database Brush title