【MySQL】综合练习

1

SELECT 
	d.department_name, emplyee_name, max_salary
FROM
	employee
JOIN
	department d 
	ON employee.department_id = d.department_id
JOIN
	(SELECT department_id, MAX(salary) as max_salary
	 FROM employee
	 GROUP BY department_id) temp
	ON temp.department_id = d.department_id
WHERE 
	employee.salary = max_salary
ORDER BY
	max_salary DESC;

2

SELECT new_id as id ,student
CASE WHEN MOD(id,2)>0 THEN id-1
           ELSE id-1 END AS new_id
FROM seat 
ORDER BY new_id ASC;

3

SELECT  Score,
       DENSE_RANK() OVER (ORDER BY score  DESC) as Rank
FROM Scores;