SQL编程学习06

第一组练习：

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) not null