Oracle综合练习一

题目一：

表结构如下：

 创建表语句：

create table teamstats(
teamstats_name varchar2(20),
teamstats_pos varchar2(2),
teamstats_ab number(3),
teamstats_hits number(3),
teamstats_walks number(3),
teamstats_singles number(3),
teamstats_doubles number(3),
teamstats_triples number(3),
teamstats_hr number(3),
teamstats_so number(3),
constraint rk_teamstats primary key(teamstats_name)
);

1.创建对表插入的存储过程：

create or replace procedure teamstats_insert(
v_teamstats_name varchar2,v_teamstats_pos varchar2,v_teamstats__ab number,v_teamstats_hits number,v_teamstats_walks number,
v_teamstats_singles number,v_teamstats_doubles number,v_teamstats_triples number,v_teamstats_hr number,v_teamstats_so number)
is
begin
  insert into teamstats values(v_teamstats_name,v_teamstats_pos,v_teamstats__ab,v_teamstats_hits,v_teamstats_walks,  v_teamstats_singles,v_teamstats_doubles,v_teamstats_triples,v_teamstats_hr,v_teamstats_so);
  commit;
end;

测试语句：
BEGIN 
  TEAMSTATS_INSERT('JONES','1B',145,45,34,31,8,1,5,10);
  TEAMSTATS_INSERT('DONKNOW','3B',175,65,23,50,10,1,4,15);
  TEAMSTATS_INSERT('WORLEY','LF',157,49,15,5,8,3,3,16);
  TEAMSTATS_INSERT('DAVID','OF',100,45,34,23,1,1,2,12);
  TEAMSTATS_INSERT('HAMHOCKER','3B',90,46,23,2,23,2,3,12);
  TEAMSTATS_INSERT('CASEY','DH',80,58,34,23,3,20,4,15);
  END;

SELECT * FROM TEAMSTATS;

--查看WALKS小于20的球员的个数。
SELECT COUNT(*)TEAMSTATS_NAME FROM TEAMSTATS WHERE TEAMSTATS_WALKS<20;

--查看表中所有记录的个数。
SELECT COUNT(*) FROM TEAMSTATS;

--查看队员总的HITS数。
SELECT SUM(TEAMSTATS_HITS) AS 总HITS数 FROM TEAMSTATS;

--查看3B位置上的队员总的DOUBLES及总的SO。
SELECT SUM(TEAMSTATS_DOUBLES) AS 总DOUBLE数,SUM(TEAMSTATS_SO) AS 总SO数 FROM TEAMSTATS WHERE TEAMSTATS_POS='3B';

--查看漏球（SO）的平均数。
SELECT AVG(TEAMSTATS_SO) AS SO平均数 FROM TEAMSTATS;

--查看TRIPLES为0的队员的HITS的平均数。
SELECT AVG(TEAMSTATS_HITS) AS HITS平均数 FROM TEAMSTATS WHERE TEAMSTATS_TRIPLES=0;

--查看所有队员的最高SIGGLES是多少。
SELECT MAX(TEAMSTATS_SINGLES) AS SIGGLES最高数 FROM TEAMSTATS;

题目二：

表结构如下：

EID——员工编号，最多6个字符。例如A00001（主键）
ENAME——员工姓名，最多10个字符。例如SMITH
BDATE——出生日期，日期型
SEX——员工性别，单个字符。F或者M
CITY——员工居住的城市，最多20个字符。例如：上海
DID——部门编号，最多3个字符。例如 A01 （主键）
DNAME——部门名称，最多20个字符。例如：研发部门
DCITY——部门所在的城市，最多20个字符。例如：上海
STARTDATE——员工到部门上班的日期，日期型
SALARY——员工的工资。整型。

删除表语句：

DROP TABLE TEST_EMP;
DROP TABLE TEST_DEPT;
DROP TABLE TEST_WORK;

创建表语句：

CREATE TABLE TEST_EMP(
EID VARCHAR2(6),
ENAME VARCHAR2(10),
BDATE DATE,
SEX VARCHAR2(2) CHECK(SEX='男' OR SEX ='女'),
CITY VARCHAR2(20),
CONSTRAINT PK_TEST_EMP PRIMARY KEY (EID)
);

SELECT TO_DATE('1999/09/27','YYYY/MM/DD') from dual; 字符串转日期

INSERT INTO TEST_EMP VALUES('A00001','EMP1',TO_DATE('1999/09/27','YYYY/MM/DD')，'男','十堰');
INSERT INTO TEST_EMP VALUES('A00002','EMP2',TO_DATE('1999/01/27','YYYY/MM/DD')，'男','成都');
INSERT INTO TEST_EMP VALUES('A00003','EMP3',TO_DATE('1999/02/27','YYYY/MM/DD')，'女','北京');
INSERT INTO TEST_EMP VALUES('A00004','EMP4',TO_DATE('1999/03/27','YYYY/MM/DD')，'男','武汉');
INSERT INTO TEST_EMP VALUES('A00005','EMP5',TO_DATE('1999/04/27','YYYY/MM/DD')，'女','十堰');
INSERT INTO TEST_EMP VALUES('A00006','EMP6',TO_DATE('1999/05/27','YYYY/MM/DD')，'男','西藏');
INSERT INTO TEST_EMP VALUES('A00007','EMP1',TO_DATE('1999/09/27','YYYY/MM/DD')，'男','十堰');
INSERT INTO TEST_EMP VALUES('A00008','EMP2',TO_DATE('1999/01/27','YYYY/MM/DD')，'男','成都');
INSERT INTO TEST_EMP VALUES('A00009','EMP3',TO_DATE('1999/02/27','YYYY/MM/DD')，'女','北京');
INSERT INTO TEST_EMP VALUES('A00010','EMP4',TO_DATE('1999/03/27','YYYY/MM/DD')，'男','武汉');
INSERT INTO TEST_EMP VALUES('A00011','EMP5',TO_DATE('1999/04/27','YYYY/MM/DD')，'女','十堰');
INSERT INTO TEST_EMP VALUES('A00012','EMP6',TO_DATE('1999/05/27','YYYY/MM/DD')，'男','西藏');
INSERT INTO TEST_EMP VALUES('P00012','EMP6',TO_DATE('1999/05/27','YYYY/MM/DD')，'男','西藏');

commit;

CREATE TABLE TEST_DEPT(
DID VARCHAR2(3),
DNAME VARCHAR2(20),
DCITY VARCHAR2(20),
CONSTRAINT PK_TEST_DEPT PRIMARY KEY(DID)
);

INSERT INTO TEST_DEPT VALUES('A01','研发部','十堰');
INSERT INTO TEST_DEPT VALUES('A02','维护部','十堰');
INSERT INTO TEST_DEPT VALUES('A03','销售部','十堰');
commit;

CREATE TABLE TEST_WORK(
EID VARCHAR2(6),
DID VARCHAR2(6),
SALARY NUMBER,
CONSTRAINT PK_TEST_WORK PRIMARY KEY(EID,DID),
FOREIGN KEY(EID) REFERENCES TEST_EMP(EID),
FOREIGN KEY(DID) REFERENCES TEST_DEPT(DID)
);

INSERT INTO TEST_WORK VALUES('A00009','A01',5000);
INSERT INTO TEST_WORK VALUES('A00010','A01',5000);
INSERT INTO TEST_WORK VALUES('A00007','A01',5000);
INSERT INTO TEST_WORK VALUES('A00008','A01',5000);
INSERT INTO TEST_WORK VALUES('A00001','A01',5000);
INSERT INTO TEST_WORK VALUES('A00002','A01',5000);
INSERT INTO TEST_WORK VALUES('A00003','A02',5000);
INSERT INTO TEST_WORK VALUES('A00004','A02',5000);
INSERT INTO TEST_WORK VALUES('A00005','A03',5000);
INSERT INTO TEST_WORK VALUES('A00006','A03',5000);
INSERT INTO TEST_WORK VALUES('P00012','A03',5000);
COMMIT;

--查询拥有最多的员工的部门的基本信息

SELECT * FROM TEST_DEPT WHERE DID IN (
SELECT Max(Did) FROM TEST_WORK 
WHERE ROWNUM =1 
GROUP BY DID) ;

(SELECT TEST_WORK.EID,TEST_WORK.DID,DNAME,DCITY FROM TEST_DEPT INNER JOIN TEST_WORK ON TEST_DEPT.DID=TEST_WORK.DID) T ;
--SELECT * FROM (SELECT TEST_WORK.DID,DNAME,DCITY FROM TEST_DEPT INNER JOIN TEST_WORK ON TEST_DEPT.DID=TEST_WORK.DID) T INNER JOIN TEST_EMP ON T.EID=TEST_EMP.EID ;
SELECT T.DID,DNAME,DCITY FROM (
                                SELECT Max(Did) AS DID 
                                FROM TEST_WORK 
                                WHERE ROWNUM =1 
                                GROUP BY DID ORDER BY DID
                              ) T 
                          INNER JOIN TEST_DEPT 
                          ON TEST_DEPT.DID = T.DID;
                          
--显示部门人数大于5的每个部门的最高工资，最低工资
SELECT MAX(SALARY),MIN(SALARY) FROM 
                               (SELECT * FROM TEST_WORK INNER JOIN TEST_EMP ON TEST_WORK.EID =TEST_EMP.EID) T 
                               INNER JOIN 
                               (SELECT DID FROM TEST_WORK GROUP BY DID HAVING COUNT(*)>5) S
                               ON T.DID=S.DID;

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

SELECT TEST_EMP.EID,ENAME,CITY,BDATE FROM TEST_EMP INNER JOIN  
                    (SELECT EID FROM TEST_WORK WHERE TEST_WORK.DID IN 
                            (SELECT DID FROM TEST_DEPT WHERE TEST_DEPT.DNAME='研发部')
                    ) S 
                    ON TEST_EMP.EID=S.EID;

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

SELECT  DID,COUNT(*) as 人数  FROM TEST_WORK GROUP BY DID HAVING COUNT(*)>5;

SELECT TEST_DEPT.DID AS 编号,TEST_DEPT.DNAME as 名称,S.人数  FROM 
                                           (SELECT  DID,COUNT(*) as 人数  FROM TEST_WORK GROUP BY DID HAVING COUNT(*)>5) S
                                           INNER JOIN TEST_DEPT 
                                           ON TEST_DEPT.DID = S.DID;

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

SELECT TEST_EMP.EID AS 编号,TEST_EMP.ENAME AS 名字,TEST_EMP.BDATE AS 日期,TEST_EMP.CITY AS 城市 FROM TEST_EMP INNER JOIN 
(SELECT upper(SUBSTR(S.EID,1,1))AS 首字母,S.EID FROM (SELECT EID FROM TEST_EMP) S) T
ON T.EID = TEST_EMP.EID WHERE ascii(首字母)<83 and ascii(首字母)>=80;

select substr('teststring',1,1) from dual;//获取首字母                
SELECT ascii('A'),chr(65),ascii('Z'),chr(90) FROM DUAL;//字符和ascii转换

--删除年龄超过60岁的员工
SELECT CURRENT_DATE FROM DUAL;//查看当前时间
TO_DATE('1999/03/27','YYYY/MM/DD')//字符串转日期

SELECT ROUND(TO_NUMBER(CURRENT_DATE-TO_DATE('2021/7/25','YYYY-MM-DD'))),TO_DATE('2021/7/25','YYYY-MM-DD') FROM DUAL;//日期做差

SELECT TO_NUMBER(S.BDATE) from (SELECT BDATE FROM TEST_EMP) S ;

DELETE FROM TEST_WORK WHERE TEST_WORK.EID IN(
                                  SELECT EID FROM TEST_EMP 
                                  WHERE ROUND(TO_NUMBER(CURRENT_DATE-TEST_EMP.BDATE)/365)>60
                                  );
DELETE FROM TEST_EMP WHERE TEST_WORK.EID IN(
                                  SELECT EID FROM TEST_EMP 
                                  WHERE ROUND(TO_NUMBER(CURRENT_DATE-TEST_EMP.BDATE)/365)>60
                                  );                     

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

update TEST_WORK SET SALARY = SALARY*(1+0.1) WHERE EID IN
(SELECT EID FROM TEST_EMP WHERE ROUND(TO_NUMBER(CURRENT_DATE-TEST_EMP.BDATE)/365)>10);

SELECT * FROM TEST_WORK;