PL/SQL题库三·EMP/DEPT/WORK(8题)

三、题库三（８题）
emp
列名 类型 描述
EID VARCHAR2(6) 员工编号
ENAME VARCHAR2（10） 员工姓名
BDATE date 生日
SEX VARCHAR2(1) 性别
CITY VARCHAR2(20) 所有城市

dept
列名 类型 描述
DID VARCHAR2(6) 部门编号
DNAME VARCHAR2（10） 部门姓名
DCITY VARCHAR2(20) 部门所在地

work
列名 类型 描述
EID VARCHAR2(6) 员工编号
DID VARCHAR2（10） 部门编号
STARTDATE date 受雇日期
SALARY VARCHAR2(1) 薪金

问题：
（1、创建表EMP，DEPT，WORK，并定义表的主键和外键。）

DROP TABLE EMP_WWW CASCADE CONSTRAINTS;
DROP TABLE DEPT CASCADE CONSTRAINTS;
DROP TABLE WORK CASCADE CONSTRAINTS;

CREATE TABLE EMP_WWW(EID VARCHAR2(6),ENAME VARCHAR2(19),BDATE DATE,SEX VARCHAR(1),CITY VARCHAR2(20),PRIMARY KEY(EID));
CREATE TABLE DEPT(DID VARCHAR2(3),DNAME VARCHAR2(20),DCITY VARCHAR2(20));
CREATE TABLE WORK(EID VARCHAR2(6),DID VARCHAR2(3),STARTDATE DATE,SALARY INTEGER,);

此处注意，需要赋权，才可以对标进行增删改查。

GRANT SELECT,INSERT,DELETE,UPDATE ON EMP_WWW TO SYSTEM;
GRANT SELECT,INSERT,DELETE,UPDATE ON DEPT TO SYSTEM;
GRANT SELECT,INSERT,DELETE,UPDATE ON WORK TO SYSTEM; 

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

INSERT INTO DEPT VALUES('A01','流通部','上海');
INSERT INTO DEPT VALUES('A02','会计部','嘉定');
INSERT INTO DEPT VALUES('A03','研发部','上海');

INSERT INTO EMP_WWW VALUES('A00001','Robert',TO_DATE('1975/01/01','YYYY/MM/DD'),'m','上海');
INSERT INTO EMP_WWW VALUES('A00002','Maggie',TO_DATE('1978/08/11','YYYY/MM/DD'),'F','嘉定');
INSERT INTO EMP_WWW VALUES('A00003','John',TO_DATE('1966/11/03','YYYY/MM/DD'),'M','杭州');
INSERT INTO EMP_WWW VALUES('A00004','Jenny',TO_DATE('1966/11/01','YYYY/MM/DD'),'F','南京');
INSERT INTO EMP_WWW VALUES('A00005','Mary',TO_DATE('1977/03/23','YYYY/MM/DD'),'M','无锡');
INSERT INTO EMP_WWW VALUES('A00006','Jane',TO_DATE('1985/04/23','YYYY/MM/DD'),'M','松江');
INSERT INTO EMP_WWW VALUES('A00007','Shirly',TO_DATE('1987/03/23','YYYY/MM/DD'),'M','嘉兴');
INSERT INTO EMP_WWW VALUES('A00008','Coco',TO_DATE('1940/03/23','YYYY/MM/DD'),'M','上海');
INSERT INTO EMP_WWW VALUES('A00009','Lisa',TO_DATE('1983/03/23','YYYY/MM/DD'),'M','武汉');
INSERT INTO EMP_WWW VALUES('A00010','Umiga',TO_DATE('1978/03/23','YYYY/MM/DD'),'M','武汉');
INSERT INTO EMP_WWW VALUES('A00011','Amy',TO_DATE('1969/03/23','YYYY/MM/DD'),'M','杭州');
INSERT INTO EMP_WWW VALUES('A00012','Lilian',TO_DATE('1968/03/23','YYYY/MM/DD'),'M','杭州');
INSERT INTO EMP_WWW VALUES('A00013','Petty',TO_DATE('1979/03/23','YYYY/MM/DD'),'M','北京');
INSERT INTO EMP_WWW VALUES('A00014','Danny',TO_DATE('1975/03/23','YYYY/MM/DD'),'M','成都');
INSERT INTO EMP_WWW VALUES('A00015','Hugh',TO_DATE('1979/03/23','YYYY/MM/DD'),'M','北京');
INSERT INTO EMP_WWW VALUES('A00016','Annie',TO_DATE('1975/03/23','YYYY/MM/DD'),'M','济南');

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/20','YYYY/MM/DD'),'1800');
INSERT INTO work VALUES('A00008','A03',TO_DATE('1988/08/01','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('A00012','A02',TO_DATE('2002/02/20','YYYY/MM/DD'),'2200');
INSERT INTO work VALUES('A00015','A02',TO_DATE('2002/02/20','YYYY/MM/DD'),'2200');
INSERT INTO work VALUES('A00013','A02',TO_DATE('2002/02/20','YYYY/MM/DD'),'2400');
INSERT INTO work VALUES('A00016','A03',TO_DATE('2002/02/20','YYYY/MM/DD'),'3200');

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

SELECT EMP.* FROM EMP,DEPT,WORK WHERE EMP.EID=WORK.EID AND DEPT.DID=WORK.DID AND DEPT.DNAME='研发';

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

CREATE VIEW VW_EMP(DID,EMPNO) AS SELECT DID,COUNT(*) FROM WORK GROUP BY DID;
SELECT DEPT.* FROM DEPT WHERE DID IN(SELECT MIN(DID) FROM VW_EMP WHERE EMPNO IN(SELECT MAX(EMPNO) FROM VW_EMP));

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

SELECT DEPT.DID,DNAME FROM DEPT WHERE DID IN (SELECT DID FROM WORK GROUP BY DID HAVING COUNT(*)>5) ;

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

CREATE 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);

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

SELECT EMP.* FROM EMP WHERE EIN LIKE'P%' OR EID LIKE 'Q%' OR EID LIKE'R%' OR EID LIKE 'S%';

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

DELETE FROM EMP WHERE MONTHS_BETWEEN(SYSDATE,BDATE)/12>60;

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

UPDATE WORK SET SALARY=1.1*SALARY WHERE MONTHS_BETWEEN(SYSDATE,STARTDATE)/12>10;