sql习题部分

第六章习题

 --习题一
 CREATE TABLE employee (
 id int(10),
 name varchar(32),
 salary int(10),
 departmentid int(10)
 ​
 );
 ​
 insert employee values(1,'Joe',70000,1),(2,'Henry',80000,2),(3,'Sam',60000,2),(4,'Max',90000,1);
 ​
 ​
 CREATE table department(
 id int(10),
 name varchar(32)
 ​
 );
 insert department values(1,'IT'),(2,'Sales');
 ​
 #编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。
 ​
 select department,employee,salary
 from 
 (select b.`name` department,a.`name` employee,a.salary
 ,rank()over(partition by a.departmentid order by salary desc) as ranks
 from employee a join  department b
 on a.departmentid=b.id
 )c where c.ranks=1
 ​

 习题三
 select 
 class,score_avg,rank()over() as rank1,dense_rank()over() as rank2,row_number()over() as rank3
 from score 
 ​
 ​