oracle 公司 练习

oracle 公司 练习

现有一个公司的部门及雇员的数据库，由下面三个表组成：
雇员employee（雇员号empid，姓名empname，年龄age，地址address，薪水sal）
部门dep（部门号deptno，部门名dname，部门经理mgr）
工作work（雇员号empid，部门号deptno，在该部门工作的年限worktime）
试用SQL语言完成下列功能：
①建表，在定义中要求声明
 ⑴每个表的主外码
 ⑵雇员的年龄介于18到55之间
 ⑶雇员姓名和部门名不能为空
 ⑷薪水取1000到50000之间整数

--删除表
drop table work;
drop table dep;
drop table employee;

--雇员
create table employee(
empid char(3) primary key,
empname varchar2(10) not null,--雇员姓名不能为空
age number(2) check (age between 18 and 55),--雇员的年龄介于18到55之间
address varchar2(20),
sal number(5) check (sal between 1000 and 50000) --薪水取1000到50000之间整数
);

--部门
create table dep(
deptno char(3) primary key ,
dname varchar2(20) not null,--部门名不能为空
mgr char(3) references employee(empid)
);

--工作
create table work(
empid char(3) references employee(empid),
deptno char(3) references dep(deptno),
worktime number(1),
primary key (empid,deptno)
);

②往表中插入数据
雇员（   E01,tom,35,海淀，8000
         E02,dennis,24,朝阳,5000
         E03,john,20,海淀,4000
         E04,rick,41,朝阳,7000
         E05,bill,45,西城,7500
         E06,frank,35,海淀,6000
         E07,jank,25,东城,3000
         E08,mike,23,东城,3500）

insert into employee values ('E01','tom',35,'海淀',8000);
insert into employee values ('E02','dennis',24,'朝阳',5000);
insert into employee values ('E03','john',20,'海淀',4000);
insert into employee values ('E04','rick',41,'朝阳',7000);
insert into employee values ('E05','bill',45,'西城',7500);
insert into employee values ('E06','frank',35,'海淀',6000);
insert into employee values ('E07','jank',25,'东城',3000);
insert into employee values ('E08','mike',23,'东城',3500);

部门（   D01,人事,E04
         D02,信息,E01
         D03,销售,E05
         D04,财务,E06）
insert into dep values ('D01','人事','E04');
insert into dep values ('D02','信息','E01');
insert into dep values ('D03','销售','E05');
insert into dep values ('D04','财务','E06');

工作（   E01,D02,6
         E01,D03,1
         E01,D04,2
         E02,D01,2
         E02,D04,0
         E03,D02,0
         E04,D01,4
         E04,D02,1
         E05,D03,2
         E06,D04,4
         E07,D04,1
         E08,D03,2）
insert into work values ('E01','D02',6);
insert into work values ('E01','D03',1);
insert into work values ('E01','D04',2);
insert into work values ('E02','D01',2);
insert into work values ('E02','D04',0);
insert into work values ('E03','D02',0);
insert into work values ('E04','D01',4);
insert into work values ('E04','D02',1);
insert into work values ('E05','D03',2);
insert into work values ('E06','D04',4);
insert into work values ('E07','D04',1);
insert into work values ('E08','D03',2);

③用SQL语句完成下列查询
⑴求同时在三个部门工作的雇员姓名
select empname from employee a where (select count(deptno) from work where empid = a.empid) = 3;
 
⑵列出各部门的职工的工资总额，并按照总额大小降序排列
select b.dname,sum(a.sal) as tot from employee a,dep b,work c
where a.empid = c.empid and c.deptno = b.deptno
group by b.dname
order by tot desc;

⑶求和其部门经理住址相同的员工的姓名及经理姓名
select d.empname,c.leader from (select deptno,empname as leader,address from employee a,dep b
where a.empid = b.mgr) c,employee d,work e
where c.deptno = e.deptno and e.empid = d.empid
and d.address = c.address and c.leader <> d.empname;

④为财务部门的雇员加薪，5年以下加5%，5年以上加3%(PL/SQL和语句)
update employee a set sal = sal * 1.05 where
(select worktime from work where empid = a.empid and
deptno = (select deptno from dep where dname='财务')) < 5;

update employee a set sal = sal * 1.03 where
(select worktime from work where empid = a.empid and
deptno = (select deptno from dep where dname='财务')) >= 5;

 

⑤删除部门工作年限为0的工作记录，若删除后有雇员没有任何部门工作，删除其雇员记录（PL/SQL和语句）
delete from work where worktime = 0;

delete from  employee a where (select count(*) from work where empid = a.empid) = 0;