第一组练习:
1. 各部门工资最高的员工
create table employee
(
emp_id char(4) not null,
emp_name varchar(100) not null,
salary integer default 0,
department_id char(4) not null,
primary key (emp_id)
);
create table department
(
dep_id char(4) not null,
dep_name varchar(100) not null,
primary key (dep_id)
)
;
insert into employee values ('1','Joe','70000','1');
insert into employee values ('2','Henry','80000','2');
insert into employee values ('3','Sam','60000','2');
insert into employee values ('4','Max','90000','1');
insert into department values ('1','IT');
insert into department values ('2','Sales');
select department_id, emp_name, salary
from
(
select department_id, emp_name, salary
,rank() over (partition by d.dep_id order by e.salary desc) as rank_num
from employee e
left join department d
on e.department_id = d.dep_id
)a1
where rank_num = 1
;
2. 换座位
create table seat
(
seat_id char(4) not null,
stu_name varchar(100) not null
)
;
insert into seat values ('1','Abbot'),('2','Doris'),('3','Emerson'),('4','Green'),('5','Jeames');
3. 分数排名
create table score
(
score_id char(4) not null,
scores char(4)
)
;
insert into score values ('1','3.50'),('2','3.65'),('3','4.00'),('4','3.85'),('5','4.00'),('6','3.65');
select *
,dense_rank() over (order by scores desc) as rank_num
from score
;
5. 树节点
create table tree
(
cur_id char(4)