根据ER图创建表结构

/*
Navicat Oracle Data Transfer
Oracle Client Version : 10.2.0.5.0

Source Server         : MyOracle
Source Server Version : 110200
Source Host           : localhost:1521
Source Schema         : KAIFAMIAO

Target Server Type    : ORACLE
Target Server Version : 110200
File Encoding         : 65001

Date: 2021-10-19 17:16:36
*/


-- ----------------------------
-- Table structure for DEPT
-- ----------------------------
DROP TABLE "KAIFAMIAO"."DEPT";
CREATE TABLE "KAIFAMIAO"."DEPT" (
"ID" NUMBER NOT NULL ,
"DNAME" VARCHAR2(20 BYTE) NULL ,
"LOC" VARCHAR2(100 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of DEPT
-- ----------------------------
INSERT INTO "KAIFAMIAO"."DEPT" VALUES ('1001', '开发部', '西安');
INSERT INTO "KAIFAMIAO"."DEPT" VALUES ('1002', '市场部', '兰州');
INSERT INTO "KAIFAMIAO"."DEPT" VALUES ('1003', '销售部', '北京');

-- ----------------------------
-- Table structure for EMP
-- ----------------------------
DROP TABLE "KAIFAMIAO"."EMP";
CREATE TABLE "KAIFAMIAO"."EMP" (
"ID" NUMBER NOT NULL ,
"ENAME" VARCHAR2(50 BYTE) NULL ,
"JOB_ID" NUMBER NULL ,
"MGR" NUMBER NULL ,
"JOINDATE" DATE NULL ,
"SALARY" NUMBER(7,2) NULL ,
"BONUS" NUMBER(7,2) NULL ,
"DEPT_ID" NUMBER NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of EMP
-- ----------------------------
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10001', '张斌', '102', '0', TO_DATE('2019-08-09 06:30:10', 'YYYY-MM-DD HH24:MI:SS'), '9500', '8000', null);
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10002', '顾辞', '101', '1', TO_DATE('2019-08-09 06:30:10', 'YYYY-MM-DD HH24:MI:SS'), '8500', '5000', '1001');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10003', '陈平安', '103', '2', TO_DATE('2019-10-09 06:30:10', 'YYYY-MM-DD HH24:MI:SS'), '8500', '5000', '1001');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10004', '赔钱', '104', '2', TO_DATE('2019-10-10 06:30:10', 'YYYY-MM-DD HH24:MI:SS'), '8300', '5000', '1001');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10005', '张三丰', '101', '1', TO_DATE('2019-09-19 06:30:10', 'YYYY-MM-DD HH24:MI:SS'), '8500', '4000', '1002');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10006', '曹慈', '101', '1', TO_DATE('2019-10-18 18:30:10', 'YYYY-MM-DD HH24:MI:SS'), '8500', '3900', '1003');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10007', '左右', '106', '2', TO_DATE('2019-10-13 15:30:10', 'YYYY-MM-DD HH24:MI:SS'), '4500', '2500', '1003');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10008', '白也', '105', '2', TO_DATE('2019-10-01 10:40:10', 'YYYY-MM-DD HH24:MI:SS'), '4500', '2000', '1002');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('10009', '助理1', '107', '3', TO_DATE('2020-04-29 10:40:10', 'YYYY-MM-DD HH24:MI:SS'), '4000', '0', '1001');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('100010', '助理2', '107', '3', TO_DATE('2021-05-10 10:40:10', 'YYYY-MM-DD HH24:MI:SS'), '4000', '1000', '1001');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('100011', '助理3', '107', '3', TO_DATE('2019-04-20 10:40:10', 'YYYY-MM-DD HH24:MI:SS'), '3000', '0', '1002');
INSERT INTO "KAIFAMIAO"."EMP" VALUES ('100012', '助理4', '107', '3', TO_DATE('2018-10-29 10:40:10', 'YYYY-MM-DD HH24:MI:SS'), '3000', '1000', '1003');

-- ----------------------------
-- Table structure for INFOS
-- ----------------------------
DROP TABLE "KAIFAMIAO"."INFOS";
CREATE TABLE "KAIFAMIAO"."INFOS" (
"STUID" VARCHAR2(7 BYTE) NOT NULL ,
"STUNAME" VARCHAR2(10 BYTE) NOT NULL ,
"GENDER" VARCHAR2(5 BYTE) NOT NULL ,
"AGE" NUMBER(2) NOT NULL ,
"SEAT" NUMBER(2) NOT NULL ,
"ENROLLDATE" DATE NULL ,
"STUADDRESS" VARCHAR2(50 CHAR) DEFAULT '地址不详'  NOT NULL ,
"CLASSNO" VARCHAR2(4 BYTE) NOT NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of INFOS
-- ----------------------------
INSERT INTO "KAIFAMIAO"."INFOS" VALUES ('s100102', '林冲', '男', '22', '2', TO_DATE('2021-10-19 14:45:14', 'YYYY-MM-DD HH24:MI:SS'), '西安', '1001');
INSERT INTO "KAIFAMIAO"."INFOS" VALUES ('s100104', '阮小二', '男', '26', '3', TO_DATE('2021-10-18 21:16:10', 'YYYY-MM-DD HH24:MI:SS'), '地址不详', '1001');

-- ----------------------------
-- Table structure for JOB
-- ----------------------------
DROP TABLE "KAIFAMIAO"."JOB";
CREATE TABLE "KAIFAMIAO"."JOB" (
"ID" NUMBER NOT NULL ,
"JNAME" VARCHAR2(30 BYTE) NULL ,
"DESCRIPTION" VARCHAR2(200 BYTE) NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of JOB
-- ----------------------------
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('101', '部门经理', '负责整个部门的具体工作，向总经理汇报');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('103', '开发工程师', '负责部门的具体开发工作，向部门经理汇报');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('102', '总经理', '负责公司整体的运行');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('104', '运维测试工程师', '负责开发部产品的运维测试工作');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('105', '市场开发人员', '负责公司市场的开发拓展');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('106', '销售员', '负责销售公司产品');
INSERT INTO "KAIFAMIAO"."JOB" VALUES ('107', '助理', '协助上一级员工完成工作');

-- ----------------------------
-- Table structure for SALARYGRADE
-- ----------------------------
DROP TABLE "KAIFAMIAO"."SALARYGRADE";
CREATE TABLE "KAIFAMIAO"."SALARYGRADE" (
"GRADE" NUMBER NOT NULL ,
"LOSALARY" NUMBER NULL ,
"HISALARY" NUMBER NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of SALARYGRADE
-- ----------------------------
INSERT INTO "KAIFAMIAO"."SALARYGRADE" VALUES ('1', '9000', '10000');
INSERT INTO "KAIFAMIAO"."SALARYGRADE" VALUES ('2', '8000', '9000');
INSERT INTO "KAIFAMIAO"."SALARYGRADE" VALUES ('3', '5500', '7000');
INSERT INTO "KAIFAMIAO"."SALARYGRADE" VALUES ('4', '3000', '5000');

-- ----------------------------
-- Table structure for SCORES
-- ----------------------------
DROP TABLE "KAIFAMIAO"."SCORES";
CREATE TABLE "KAIFAMIAO"."SCORES" (
"ID" NUMBER NULL ,
"TERM" VARCHAR2(2 BYTE) NULL ,
"STUID" VARCHAR2(7 BYTE) NOT NULL ,
"EXAMNO" VARCHAR2(7 BYTE) NOT NULL ,
"WRITTENSCORE" NUMBER(4,1) NOT NULL ,
"LABSCORE" NUMBER(4,1) NOT NULL 
)
LOGGING
NOCOMPRESS
NOCACHE

;

-- ----------------------------
-- Records of SCORES
-- ----------------------------
INSERT INTO "KAIFAMIAO"."SCORES" VALUES ('1001', 'S2', 's100104', '4', '98', '89');

-- ----------------------------
-- Sequence structure for MYSEQ
-- ----------------------------
DROP SEQUENCE "KAIFAMIAO"."MYSEQ";
CREATE SEQUENCE "KAIFAMIAO"."MYSEQ"
 INCREMENT BY 1
 MINVALUE 1
 MAXVALUE 9999999999999999999999999999
 START WITH 21
 CACHE 20;

-- ----------------------------
-- Indexes structure for table DEPT
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table DEPT
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."DEPT" ADD PRIMARY KEY ("ID");

-- ----------------------------
-- Indexes structure for table EMP
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table EMP
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."EMP" ADD PRIMARY KEY ("ID");

-- ----------------------------
-- Uniques structure for table INFOS
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."INFOS" ADD UNIQUE ("STUNAME");
ALTER TABLE "KAIFAMIAO"."INFOS" ADD UNIQUE ("STUID");

-- ----------------------------
-- Checks structure for table INFOS
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK (AGE >=0 AND AGE<=100);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ((CLASSNO >='1001' AND CLASSNO<='1999') OR 
(CLASSNO >='2001' AND CLASSNO<='2999'));
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK (GENDER = '男' OR GENDER = '女');
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK (SEAT >=0 AND SEAT <=50);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("STUID" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("STUNAME" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("GENDER" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("AGE" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("SEAT" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("STUADDRESS" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."INFOS" ADD CHECK ("CLASSNO" IS NOT NULL);

-- ----------------------------
-- Indexes structure for table JOB
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table JOB
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."JOB" ADD PRIMARY KEY ("ID");

-- ----------------------------
-- Indexes structure for table SALARYGRADE
-- ----------------------------

-- ----------------------------
-- Primary Key structure for table SALARYGRADE
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."SALARYGRADE" ADD PRIMARY KEY ("GRADE");

-- ----------------------------
-- Checks structure for table SCORES
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."SCORES" ADD CHECK (TERM = 'S1' OR TERM ='S2');
ALTER TABLE "KAIFAMIAO"."SCORES" ADD CHECK ("STUID" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."SCORES" ADD CHECK ("EXAMNO" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."SCORES" ADD CHECK ("WRITTENSCORE" IS NOT NULL);
ALTER TABLE "KAIFAMIAO"."SCORES" ADD CHECK ("LABSCORE" IS NOT NULL);

-- ----------------------------
-- Foreign Key structure for table "KAIFAMIAO"."EMP"
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."EMP" ADD FOREIGN KEY ("DEPT_ID") REFERENCES "KAIFAMIAO"."DEPT" ("ID");
ALTER TABLE "KAIFAMIAO"."EMP" ADD FOREIGN KEY ("JOB_ID") REFERENCES "KAIFAMIAO"."JOB" ("ID");

-- ----------------------------
-- Foreign Key structure for table "KAIFAMIAO"."SCORES"
-- ----------------------------
ALTER TABLE "KAIFAMIAO"."SCORES" ADD FOREIGN KEY ("STUID") REFERENCES "KAIFAMIAO"."INFOS" ("STUID");

使用上表完成数据查询

1.选择某一个部门的所有员工

select e.id, e.ename, j.jname, e.salary, e.bonus, d.dname 
       from job j join emp e on j.id = e.job_id 
            join dept d on d.id = e.dept_id 
                 where e.dept_id = 1003;

2.列出所有第二级员工的姓名，编号，部门编号

select e.ename, e.id, e.dept_id from emp e where mgr = 2;

3.找出奖金高于薪水的员工

select * from emp where bonus > salary;

4.找出奖金高于薪水60%的员工

select * from emp where bonus > salary * 0.6;

5.找出部门某个部门的经理和某个部门的所有第二级员工的详细资料【两个部门不相同】

select * from emp where dept_id = 1001 and mgr = 1 or mgr = (select mgr from emp where dept_id = 1002 and mgr = 2);

6.找出部门某个部门的经理，某个部门的所有第二级员工，既不是经理又不是办事员但其薪资小于或等于3000的员工的详细工作

SQL> select ename, j.jname, salary 
	from emp join job j on emp.job_id = j.id 
		where (emp.mgr = 1 and emp.dept_id = 1001) 
			or (emp.mgr = 2 and emp.dept_id = 1002) 
				or (emp.mgr != 1 and emp.mgr != 2 and emp.salary > 2000);

7.找出收取奖金的员工的不同工作

-- select distinct j.jname from emp e join job j on e.job_id = j.id where e.bonus != 0;
 select distinct j.jname from emp e join job j on e.job_id = j.id where e.bonus is not null;

8.找出不收取奖金或者奖金低于5000 的员工

select distinct j.jname from emp e join job j on e.job_id = j.id where e.bonus = 0 or e.bonus < 5000;

9.找出各月倒数第三天受雇的所有员工

SELECT * FROM EMP WHERE JOINDATE = LAST_DAY(JOINDATE)-2 ;

10.找出早于2年前受雇的员工

select * from emp where JOINDATE <= add_months(sysdate,-24);
select * from emp where JOINDATE <= JOINDATE + INTERVAL '-2' YEAR;

基于SCOTT 示例数据库进行练习

1.列出至少一个员工的所有部门

-- 在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。
SELECT E.DEPTNO,D.DNAME,COUNT(E.EMPNO) AS EMPNUM FROM EMP E 
       JOIN DEPT D ON E.DEPTNO = D.DEPTNO 
       GROUP BY E.DEPTNO,D.DNAME
       HAVING COUNT(E.EMPNO) > 1
-- 这样写不太严谨 如果 编号和名字不对应就会有问题 所以上面可以不去查 名字 查编号用编号分组
-- 想要全部列出 可以用分词查询结果作为表和dept表连接
-- having 写 group by 之后之前都可以
select d.deptno, d.dname, d1.cou from dept d,
		(select deptno, count(empno) cou having 			count(empno) > 1 group by deptno)
	where d.deptno = d1.deptno

2.列出薪水比’SMITH‘多的所有员工

SELECT * FROM EMP
       WHERE SAL > 
             (SELECT SAL FROM EMP WHERE ENAME = 'SMITH')

3.列出所有员工的姓名及其直接上级的姓名

SELECT E.ENAME AS EMPNAME, E1.ENAME AS MGRNAME FROM EMP E 
       JOIN EMP E1 ON E.MGR = E1.EMPNO

4.列出受雇日期早于其直接上级的所有员工

SELECT E.* FROM EMP E 
       JOIN EMP E1 ON E.MGR = E1.EMPNO
            WHERE E.HIREDATE > E1.HIREDATE

5.列出部门名称和这些部门的员工信息 同时列出那些没有员工的部门

SELECT D.DNAME,D.DEPTNO, E.* FROM EMP E 
       RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO
       
-- left
select 
  d.deptno,d.dname,e.empno,e.ename 
from 
  dept d left join emp e
on
  d.deptno=e.deptno
-- Oracle 特殊用法 +
SELECT D.DNAME,D.DEPTNO, E.* FROM EMP E , DEPT D
	WHERE E.DEPTNO (+) = ED.DEPTNO
	
select 
  d.deptno,d.dname,e.empno,e.ename 
from 
  dept d ,emp e
where 
  d.deptno=e.deptno(+)

6.列出薪水大于1500的各种工作

SELECT JOB, MIN(SAL) FROM EMP
       GROUP BY JOB
            HAVING MIN(SAL) > 1500 

7.列出在部门 ’SALES‘工作的员工姓名

SELECT ENAME FROM EMP
       WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES')
-- 2       
select 
  e.ename,d.dname 
from 
  emp e,dept d 
where 
  d.dname='SALES' 
  and 
  e.deptno=d.deptno

8.列出薪水高于公司平均薪水的所有员工

select ename,sal from emp where sal > (select avg(sal) from emp)

9.列出薪水等于30部门中员工的薪水的所有员工的姓名和薪水

select ename,sal,deptno from emp where sal in (select sal from emp where deptno = 30)

10.列出薪水高于在部门30工作的所有员工的薪水的员工姓名和薪水

select ename,sal,deptno from emp where sal > ALL(select sal from emp where deptno = 30)
-- 2
select 
  ename,sal 
from 
  emp 
where 
  sal>(select max(sal) from emp where deptno=30)

11.列出在每个部门工作的员工数量，平均工资和平均服务期限

SELECT DEPTNO, COUNT(*) AS 人数, AVG(SAL), AVG(SYSDATE - HIREDATE)
    FROM EMP
    GROUP BY DEPTNO

 select count(empno),round(avg(sal),2),round(avg(sysdate-hiredate)/365,0)
  from emp
  group by deptno

12.列出所有员工的姓名，部门名和工资

 select e.ename,d.dname,e.sal from emp e ,dept d where e.deptno = d.deptno;

13.列出所有部门的详细信息和部门人数

-- ...
SELECT D.DEPTNO,D.DNAME,D.LOC, E1.COU 人数
	FROM  DEPT D,(SELECT DEPTNO,COUNT(EMPNO) COU FROM EMP GROUP BY DEPTNO) E1
WHERE D.DEPTNO = E1.DEPTNO 

14.列出各种工作的最低工资

select job, min(sal) from emp group by job;

15.列出各个部门的 ‘MANAGER’ 的最低薪水

select deptno, min(sal) from emp where job = 'MANAGER' group by deptno;

16.列出所有员工的年工资，按年薪从低到高排序

select ename, ((sal+NVL(COMM, 0)) * 12) year_sal from emp order by year_sal;

17.查询所有81年之后入职的员工信息

select * from emp 
    where to_char(hiredate, 'yyyy') > to_char(to_date('1981', 'yyyy'), 'yyyy');-- 按81年算的即82即之后年份入职的
-- select * from emp where hiredate > to_date('1981-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'); -- 按81年元月1日算的
 -- select * from emp where to_char(hiredate,'YYYY') > 81;

18.查询雇员表中，姓名为 ‘SMITH’ 的雇员，截至到今天共工作了多少周

select ROUND((sysdate - hiredate) / 7) from emp where ename = 'SMITH';

19.查询所有工作时间超过一年的员工编号

select empno from emp where to_char(sysdate,'YYYY') - to_char(hiredate,'YYYY') > 1 ;

20.查询今年十二月份倒数第二周周一是几号

select next_day((add_months(trunc(sysdate,'year'),12)-1) + (interval '-14' day),2) from dual;