oracle 上机练习4及答案

oracle 上机练习(四)

题目：已知公司的员工表EMP(EID, ENAME, BDATE, SEX, CITY)，

部门表DEPT（DID, DNAME, DCITY），

工作表WORK（EID,DID,STARTDATE,SALARY）。各个字段说明如下：

EID——员工编号，最多6个字符。例如A00001（主键）

ENAME——员工姓名，最多10个字符。例如SMITH

BDATE——出生日期，日期型

SEX——员工性别，单个字符。F或者M

CITY——员工居住的城市，最多20个字符。例如：上海

DID——部门编号，最多3个字符。例如 A01 （主键）

DNAME——部门名称，最多20个字符。例如：研发部门

DCITY——部门所在的城市，最多20个字符。例如：上海

STARTDATE——员工到部门上班的日期，日期型

SALARY——员工的工资。整型。

请使用ORACLE的sql*plus 完成下列的操作

1、 创建表EMP，DEPT，WORK，并定义表的主键和外键。

2、 向每个表中插入适当的数据。例如：插入三条部门的数据，分别为每个部门插入两条员工数据

3、 查询“研发”部门的所有员工的基本信息

4、 查询拥有最多的员工的部门的基本信息（要求只取出一个部门的信息），如果有多个部门人数一样，那么取出部门编号最小的那个部门的基本信息。

5、 显示部门人数大于5的每个部门的编号，名称，人数

6、 显示部门人数大于5的每个部门的最高工资，最低工资

7、 列出员工编号以字母P至S开头的所有员工的基本信息

8、 删除年龄超过60岁的员工

9、 为工龄超过10年的职工增加10%的工资

练习四

1 创建表EMP，DEPT，WORK，并定义表的主键和外键。

create table emp(EID VARCHAR2(6),ENAME VARCHAR2(10),BDATE DATE,SEX CHAR(1),CITY VARCHAR(20),primary key(EID));

create table dept(DID VARCHAR2(3),DNAME VARCHAR2(20),DCITY VARCHAR2(20),primary key(DID));

create table work(EID VARCHAR2(6) REFERENCES EMP(EID),DID VARCHAR2(3) REFERENCES DEPT(DID),STARTDATE DATE,SALARY INTEGER);

2 向每个表中插入适当的数据。例如：插入三条部门的数据，分别为每个部门插入两条员工数据

insert into emp values('A00001','Robert',TO_DATE('1975-01-01','YYYY-MM-DD'),'M','上海');

insert into emp values('A00002','Maggie',TO_DATE('1978-08-12','YYYY-MM-DD'),'F','嘉定');

insert into emp values('A00003','John',TO_DATE('1966-11-03','YYYY-MM-DD'),'M','杭州');

insert into emp values('A00004','Jenny',TO_DATE('1982-09-11','YYYY-MM-DD'),'F','南京');

insert into emp values('A00005','Mary',TO_DATE('1977-03-28','YYYY-MM-DD'),'M','无锡');

insert into emp values('A00006','Jane',TO_DATE('1985-04-01','YYYY-MM-DD'),'F','松江');

insert into emp values('A00007','Shirly',TO_DATE('1987-05-01','YYYY-MM-DD'),'F','嘉兴');

insert into emp values('A00008','Coco',TO_DATE('1940-08-01','YYYY-MM-DD'),'F','上海');

insert into emp values('A00009','Lisa',TO_DATE('1983-05-15','YYYY-MM-DD'),'F','武汉');

insert into emp values('A00010','Umiga',TO_DATE('1978-08-15','YYYY-MM-DD'),'F','武汉');

insert into emp values('A00011','Amy',TO_DATE('1969-10-15','YYYY-MM-DD'),'F','杭州');

insert into emp values('A00014','Lilian',TO_DATE('1968-10-15','YYYY-MM-DD'),'F','杭州');

insert into emp values('S00012','Petty',TO_DATE('1979-12-25','YYYY-MM-DD'),'F','北京');

insert into emp values('P00015','Danny',TO_DATE('1975-12-31','YYYY-MM-DD'),'F','成都');

insert into emp values('R00013','Hugh',TO_DATE('1979-12-25','YYYY-MM-DD'),'M','北京');

insert into emp values('P00016','Annie',TO_DATE('1975-12-31','YYYY-MM-DD'),'F','济南');

insert into dept values('A01','流通部','上海');

insert into dept values('A02','会计科','嘉定');

insert into dept values('A03','研发部','上海');

insert into work values('A00005','A03',TO_DATE('1980-01-01','YYYY-MM-DD'),1500);

insert into work values('A00001','A03',TO_DATE('1965-06-12','YYYY-MM-DD'),3200);

insert into work values('A00002','A01',TO_DATE('1946-07-19','YYYY-MM-DD'),4000);

insert into work values('A00003','A02',TO_DATE('1998-01-02','YYYY-MM-DD'),3000);

insert into work values('A00004','A01',TO_DATE('2005-10-20','YYYY-MM-DD'),1900);

insert into work values('A00006','A02',TO_DATE('1998-05-06','YYYY-MM-DD'),2900);

insert into work values('A00007','A02',TO_DATE('1999-07-07','YYYY-MM-DD'),3300);

insert into work values('A00011','A02',TO_DATE('1980-08-07','YYYY-MM-DD'),5000);

insert into work values('A00009','A03',TO_DATE('2005-08-07','YYYY-MM-DD'),1800);

insert into work values('A00008','A03',TO_DATE('1998-08-20','YYYY-MM-DD'),1800);

insert into work values('A00014','A03',TO_DATE('1988-12-20','YYYY-MM-DD'),3800);

insert into work values('A00010','A03',TO_DATE('1988-12-20','YYYY-MM-DD'),3800);

insert into work values('S00012','A02',TO_DATE('2002-02-20','YYYY-MM-DD'),2000);

insert into work values('P00015','A02',TO_DATE('2002-02-20','YYYY-MM-DD'),2200);

insert into work values('R00013','A02',TO_DATE('2002-02-20','YYYY-MM-DD'),2400);

insert into work values('P00016','A03',TO_DATE('2002-02-20','YYYY-MM-DD'),3200);

3 查询“研发”部门的所有员工的基本信息

SELECT *

FROM EMP,DEPT,WORK

WHERE EMP.EID=WORK.EID AND DEPT.DID=WORK.DID AND DEPT.DNAME='研发部';

4 查询拥有最多的员工的部门的基本信息（要求只取出一个部门的信息），如果有多个部门人数一样，那么取出部门编号最小的那个部门的基本信息。

建立视图

create view vw_maxemp(did,empno)

as select did,count(*)

from work

group by did;

select * from dept

where did in(select min(did)

              from vw_maxemp

              where empno=(select max(empno) from vw_maxemp));

5 显示部门人数大于5的每个部门的编号，名称，人数

建立部门人数大于5的每个部门的编号，人数的视图

create view vw_emp1(did,empno)

as select did,count(*)

from work

group by did

having count(*)>5;

select vw_emp1.did,dname,empno

from dept,vw_emp1

where vw_emp1.did=dept.did;

6 显示部门人数大于5的每个部门的最高工资，最低工资

create view vw_emp2(did,empno)

as select did,count(*)

from work

group by did

having count(*)>5;

select did,max(salary),min(salary)

from work

group by did

having did in (select did

              from vw_emp2);

7 列出员工编号以字母P至S开头的所有员工的基本信息

SELECT *

FROM EMP

WHERE EID LIKE 'P%'

OR

EID LIKE 'Q%'

OR

EID LIKE 'R%'

OR

EID LIKE 'S%'

8 删除年龄超过60岁的员工

先删除WORK里面的相关员工记录

DELETE FROM WORK

WHERE EID IN (SELECT EID FROM EMP WHERE (SYSDATE-BDATE)/365>60));

再删除EMP里面的员工记录

DELETE

FROM EMP

WHERE (SYSDATE-BDATE)/365>60;

9 为工龄超过10年的职工增加10%的工资

UPDATE WORK SET SALARY=SALARY*1.10

WHERE (SYSDATE-STARTDATE)/365>10;