基于scott做练习1
2021/10/21 23:13:07
本文主要是介绍基于scott做练习1,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
根据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;
这篇关于基于scott做练习1的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-10-04el-table 开启定时器下,表格的选中状态会消失是什么原因-icode9专业技术文章分享
- 2024-10-03如何安装和初始化飞牛私有云 fnOS?-icode9专业技术文章分享
- 2024-10-03如何安装 App 并连接到飞牛 NAS?-icode9专业技术文章分享
- 2024-10-03如何安装飞牛 TV 并连接到影视服务器?-icode9专业技术文章分享
- 2024-10-03如何在PVE和ESXI上安装飞牛私有云 fnOS?-icode9专业技术文章分享
- 2024-10-03fnOS国产最强NAS安装系统异常情况处理-icode9专业技术文章分享
- 2024-10-03飞牛NAS如何创建存储空间?-icode9专业技术文章分享
- 2024-10-03fnOS国产最强NAS硬盘会自动休眠吗?-icode9专业技术文章分享
- 2024-10-03fnOS国产最强NAS如何安装飞牛影视和创建媒体库?-icode9专业技术文章分享
- 2024-10-03fnOS国产最强NAS如何为家人朋友开通影视账号?-icode9专业技术文章分享