第1页 / 共8页
第2页 / 共8页
第3页 / 共8页
第4页 / 共8页
第5页 / 共8页
第6页 / 共8页
第7页 / 共8页
第8页 / 共8页
Oracle SQL：经典练习题（附答案）.doc
本文使用的实例表结构与表的数据如下：
emp 员工表结构如下：
Type
1.
2.
3.
4.
5.
6.
7.
8.
9.
10. DEPTNO
Name
Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO
员工号
ENAME
JOB
MGR
HIREDATE DATE
SAL
COMM
NUMBER(4)
VARCHAR2(10) Y
VARCHAR2(9) Y
NUMBER(4)
Y
Y
NUMBER(7,2) Y
NUMBER(7,2) Y
Y
NUMBER(2)
工作
薪金
佣金
上级编号
雇佣日期
员工姓名
部门编号
dept 部门表：
1.
2.
3.
4.
5.
Type
Name
Nullable Default Comments
------ ------------ -------- ------- --------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14) Y
LOC
VARCHAR2(13) Y
地点
部门编号
部门名称
提示：工资 = 薪金 + 佣金
emp 表的现有数据如下：
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
SQL> select * from emp;
SAL
JOB
MGR HIREDATE
300.00
500.00
EMPNO ENAME
COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH
20
30
7499 ALLEN
30
7521 WARD
7566 JONES
20
30
7654 MARTIN
30
7698 BLAKE
10
7782 CLARK
7788 SCOTT
20
10
7839 KING
30
7844 TURNER
7876 ADAMS
20
30
7900 JAMES
7902 FORD
20
7902 1980-12-17
7698 1981-2-20
7698 1981-2-22
7839 1981-4-2
7698 1981-9-28
7839 1981-5-1
7839 1981-6-9
7566 1987-4-19
1981-11-17
CLERK
SALESMAN
SALESMAN
MANAGER
SALESMAN
MANAGER
MANAGER
ANALYST
PRESIDENT
SALESMAN
CLERK
CLERK
ANALYST
800.00
1600.00
1250.00
2975.00
1250.00
2850.00
2450.00
4000.00
5000.00
1500.00
1100.00
950.00
3000.00
7698 1981-9-8
7788 1987-5-23
7698 1981-12-3
7566 1981-12-3
1400.00
0.00
7934 MILLER
18.
19.
20. 14 rows selected
CLERK
7782 1982-1-23
1300.00
10
dept 表的现有数据如下：
SQL> select * from dept;
1.
2.
3.
4.
5.
6.
7.
8.
9.
10. 4 rows selected
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
DEPTNO DNAME
------ -------------- -------------
LOC
NEW YORK
DALLAS
CHICAGO
BOSTON
用 SQL 完成以下问题列表：
第一篇
1. 列出至少有一个员工的所有部门。
2. 列出薪金比“SMITH”多的所有员工。
3. 列出所有员工的姓名及其直接上级的姓名。
4. 列出受雇日期早于其直接上级的所有员工。
5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门
6. 列出所有“CLERK”(办事员)的姓名及其部门名称。
7. 列出最低薪金大于 1500 的各种工作。
8. 列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。
9. 列出薪金高于公司平均薪金的所有员工。
10. 列出与“SCOTT”从事相同工作的所有员工。
11. 列出薪金等于部门 30 中员工的薪金的所有员工的姓名和薪金。
12. 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金。
13. 列出在每个部门工作的员工数量、平均工资和平均服务期限。
14. 列出所有员工的姓名、部门名称和工资。
15. 列出所有部门的详细信息和部门人数。
16. 列出各种工作的最低工资。
17. 列出各个部门的 MANAGER(经理)的最低薪金。
18. 列出所有员工的年工资,按年薪从低到高排序。
第二篇
1. 找出 EMP 表中的姓名（ENAME）第三个字母是 A 的员工姓名。
2. 找出 EMP 表员工名字中含有 A 和 N 的员工姓名。
3. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。
4. 列出部门编号为 20 的所有职位。
5. 列出不属于 SALES 的部门。
6. 显示工资不在 1000 到 1500 之间的员工信息：名字、工资，按工资从大到小排序。
7. 显示职位为 MANAGER 和 SALESMAN，年薪在 15000 和 20000 之间的员工的信息：名字、职位、
年薪。
8. 说明以下两条 SQL 语句的输出结果：
1.
2.
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
9. 让 SELECT 语句的输出结果为
1.
2.
3.
4.
5.
SELECT * FROM SALGRADE;
SELECT * FROM BONUS;
SELECT * FROM EMP;
SELECT * FROM DEPT;
……
列出当前用户有多少张数据表，结果集中存在多少条记录。
10. 判断 SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否报错，为什么？
答案
--创建 EMP 表
CREATE TABLE EMP(
"EMPNO" NUMBER(4,0) PRIMARY KEY,
"ENAME" VARCHAR2(10 BYTE),
"JOB" VARCHAR2(9 BYTE),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
);
COMMENT ON COLUMN "EMP"."EMPNO" IS '员工号';
COMMENT ON COLUMN "EMP"."ENAME" IS '员工姓名';
COMMENT ON COLUMN "EMP"."JOB" IS '工作';
COMMENT ON COLUMN "EMP"."MGR" IS '上级编号';
COMMENT ON COLUMN "EMP"."HIREDATE" IS '雇佣日期';
COMMENT ON COLUMN "EMP"."SAL" IS '薪金';
COMMENT ON COLUMN "EMP"."COMM" IS '佣金';
COMMENT ON COLUMN "EMP"."DEPTNO" IS '部门编号';
--创建 DEPT 表
CREATE TABLE DEPT (
"DEPTNO" NUMBER(2,0) PRIMARY KEY,
"DNAME" VARCHAR2(14 BYTE),
"LOC" VARCHAR2(13 BYTE)
);
COMMENT ON COLUMN "DEPT"."DEPTNO" IS '部门编号';
COMMENT ON COLUMN "DEPT"."DNAME" IS '部门名称';
COMMENT ON COLUMN "DEPT"."LOC" IS '地点';
-- 插入数据 EMP 表
INSERT INTO EMP(EMPNO,ENAME, JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES(7369,
'SMITH', 'CLERK',7902,TO_DATE('1980-12-17','YYYY-MM-DD'
),
800,'',
20);
INSERT INTO EMP(EMPNO,ENAME, JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES(7499
,'ALLEN','SALESMAN',
TO_DATE('1981-2-20','YYYY-MM-DD'),
7698,
1600,
300, 30);
INSERT INTO EMP(EMPNO,ENAME, JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES(7521,
7698,TO_DATE('1981-2-22','YYYY-MM-DD'
'WARD', 'SALESMAN',
),
1250,
500 ,30);
INSERT INTO EMP(EMPNO,ENAME, JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES(7566
,'JONES', 'MANAGER', 7839,TO_DATE('1981-4-02','YYYY-MM-DD'
), 2975,'',
20);
INSERT INTO EMP(EMPNO,ENAME, JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES(7654
'SALESMAN',7698,TO_DATE('1981-9-28','YYYY-MM-DD'
,'MARTIN',
,1400,
30);
INSERT INTO EMP(EMPNO,ENAME, JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES(7698
2850,'',30);
,7839,TO_DATE('1981-5-1','YYYY-MM-DD'
'MANAGER'
,'BLAKE',
INSERT INTO EMP(EMPNO,ENAME, JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES(7782,
'CLARK' ,'MANAGER',7839,TO_DATE('1981-6-9','YYYY-MM-DD'
,10);
INSERT INTO EMP(EMPNO,ENAME, JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES( 7788,
3000,'',20);
'SCOTT', 'ANALYST', 7566,TO_DATE('1987-4-19','YYYY-MM-DD'
),
),
),1250
),
2450,''
INSERT INTO EMP(EMPNO,ENAME, JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES(7839,
,'PRESIDENT','',TO_DATE('1981-11-17','YYYY-MM-DD'),5000,'',10);
'KING'
INSERT INTO EMP(EMPNO,ENAME, JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES(7844
7698,TO_DATE('1981-9-8','YYYY-MM-DD'
'SALESMAN',
,'TURNER',
),
1500,'',
30);
INSERT INTO EMP(EMPNO,ENAME, JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES(7876,
'CLERK',7788,TO_DATE('1987-5-23','YYYY-MM-DD'
'ADAMS',
),
1100,'',
20);
INSERT INTO EMP(EMPNO,ENAME, JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
),
VALUES(7900,
'JAMES', 'CLERK',7698,TO_DATE('1981-12-3','YYYY-MM-DD'
950 ,'',
30);
INSERT INTO EMP(EMPNO,ENAME, JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES(7902,
'ANALYST', 7566,TO_DATE('1981-12-3','YYYY-MM-DD'
)
,
3000,'',
'FORD',
20);
INSERT INTO EMP(EMPNO,ENAME, JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
-- 插入数据 DEPT 表
INSERT INTO DEPT(DEPTNO,DNAME,LOC)VALUES(10,
INSERT INTO DEPT(DEPTNO,DNAME,LOC)VALUES(20,
INSERT INTO DEPT(DEPTNO,DNAME,LOC)VALUES(30,
INSERT INTO DEPT(DEPTNO,DNAME,LOC)VALUES(40,
'ACCOUNTING',
'RESEARCH' ,'DALLAS');
'SALES'
'OPERATIONS',
,'CHICAGO');
'BOSTON');
'NEW YORK');
------第一篇
--1.列出至少有一个员工的所有部门。
select dname from dept where deptno in(select deptno from emp group by deptno having
count(deptno)>=3);
--2.2.列出薪金比“SMITH”多的所有员工。
select * from emp where sal>(select sal from emp where ename='SMITH');
--3.列出所有员工的姓名及其直接上级的姓名。
select a.ename,(select ename from emp b where b.empno=a.mgr) as boss_name from emp a;
--4.列出受雇日期早于其直接上级的所有员工。
select a.ename from emp a where a.hiredate>(select hiredate from emp b where b.empno=a.mgr);
--5.列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门(left join 从左表那里返回所
有的行，即使在右表中没有匹配的行。)
select a.dname,b.empno,b.ename,b.job,b.mgr,b.hiredate,b.sal,b.deptno from dept a left
a.deptno=b.deptno;
join emp b on
--6.列出所有“CLERK”(办事员)的姓名及其部门名称。（:as 是 alias 的缩写,是“别名”的意思，
后面接中文需要用 "" 。）
select a.ename,(select dname from dept b where b.deptno=a.deptno) as dname from emp a where
a.job='CLERK';
select a.ename,b.dname from emp a join dept b on a.deptno=b.deptno and a.job='CLERK';
--7.列出最低薪金大于 1500 的各种工作。(distinct 筛选重复值)
select distinct job from emp group by job having min(sal)>1500;
--8.列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。
select ename from emp where deptno=(select deptno from dept where dname='SALES');
--9.列出薪金高于公司平均薪金的所有员工。
select ename from emp where sal>(select avg(sal) from emp);
--10.列出与“SCOTT”从事相同工作的所有员工。
select ename from emp where job=(select job from emp where ename='SCOTT');
--11.列出薪金等于部门 30 中员工的薪金的所有员工的姓名和薪金。(in 操作符允许我们在 where 子
句中规定多个值)
select ename,sal from emp where sal in(select sal from emp where deptno=30);
--12.列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金。（max() 求最大值）
select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);
--13.列出在每个部门工作的员工数量、平均工资和平均服务期限。（group by+[分组字段](可以有多
个)用于结合合计函数，根据一个或多个列对结果集进行分组。）
select (select b.dname from dept b where b.deptno=a.deptno) 部门, count(deptno) 平均工资,avg(sal) 平
均服务期限 from emp a group by deptno;
--14.列出所有员工的姓名、部门名称和工资。
select ename,(select b.dname from dept b where b.deptno= a.deptno) 部门,sal from emp a;
--15.列出所有部门的详细信息和部门人数。(count(列名)函数返回指定列的值的数目，返回的是一个
数字)
select deptno, dname, loc,(select count(deptno) from emp b where b.deptno=a.deptno group by deptno) 部
门人数 from dept a;
--16.列出各种工作的最低工资。( min(列名) 求最小值 ；max(列名) 求最大值)
select job, min(sal) from emp group by job;
--17.列出各个部门的 MANAGER(经理)的最低薪金。
select deptno,(select dname from dept b where b.deptno=a.deptno) 部 门 ,min(sal) from emp a where
job='MANAGER' group by deptno;
--18．列出所有员工的年工资,按年薪从低到高排序。( nvl(表达式,value):如果表达式计算结果为
null ，则返回 value)
select ename,(sal+nvl(comm,0))*12 as salpersal from emp order by salpersal;
------第二篇
--1. 找出 EMP 表中的姓名（ENAME）第三个字母是 A 的员工姓名。(like 操作符用于在 where 子
句中搜索列中的指定模式。 "%" 可用于定义通配符（模式中缺少的字母）。)
select ename from emp where ename like '__A%';
--2. 找出 EMP 表员工名字中含有 A 和 N 的员工姓名。(and 并且 ； or 或者)
select ename from emp where ename like '%A%' and ename like '%N%'
--3. 找出所有有佣金的员工，列出姓名、工资、佣金，显示结果按工资从小到大，佣金从大到小。
（order by 默认按照升序排序；order by 列名 desc 降序）
select ename,sal+comm as wage,comm from emp order by wage,comm desc;
--4. 列出部门编号为 20 的所有职位。(和第一篇的第七题类似)
select distinct job from emp where deptno=20;
--5. 列出不属于 SALES 的部门。
--in 和 exists 的区别：
--exists（相关子查询）:存在，后面一般都是子查询，不返回列表的值，只是返回一个 ture 或 false
的结果
--in：包含，in()后面的子查询 是返回结果集的
select distinct * from dept where dname not in('SALES');
select distinct * from dept where exists(select dname from dept where dname='SALES');
--6. 显示工资不在 1000 到 1500 之间的员工信息：名字、工资，按工资从大到小排序。(between ... and
会选取介于两个值之间的数据范围。)
select ename,sal+comm as wage from emp where sal+comm not between 1000 and 1500 order by wage
desc;
select ename,sal+comm as wage from emp where sal+comm<1000 or sal+comm>1500 order by wage
desc;
-- 7. 显示职位为 MANAGER 和 SALESMAN，年薪在 15000 和 20000 之间的员工的信息：名字、
职位、年薪。
select ename 姓 名 ,job 职 位 ,(sal+nvl(comm,0))*12 年 薪 from emp where (sal+comm)*12 between
15000 and 20000 and job in('MANAGER','SALESMAN')
-- 8. 说明以下两条 SQL 语句的输出结果：
--
--
--
--
SELECT EMPNO,COMM FROM EMP WHERE COMM IS NULL;
SELECT EMPNO,COMM FROM EMP WHERE COMM = NULL;
答案：IS NULL：是判断某个‘字段’是否为空，为空并不等价于为空字符串或为数字 0；
= NULL ：是判断某个‘值’是否等于 NULL，NULL=NULL 和 NULL="" 都返回 false。
--9.让 SELECT 语句的输出结果为
-- SELECT * FROM SALGRADE;
-- SELECT * FROM BONUS;
-- SELECT * FROM EMP;
-- SELECT * FROM DEPT;
-- ……
--列出当前用户有多少张数据表，结果集中存在多少条记录。
select * from user_tables;
--10.判断 SELECT ENAME,SAL FROM EMP WHERE SAL > '1500'是否报错，为什么？
--答：不会抱错，存在隐式数据类型的。
相关推荐
2023年江西萍乡中考道德与法治真题及答案.doc
2012年重庆南川中考生物真题及答案.doc
2013年江西师范大学地理学综合及文艺理论基础考研真题.doc
2020年四川甘孜小升初语文真题及答案I卷.doc
2020年注册岩土工程师专业基础考试真题及答案.doc
2023-2024学年福建省厦门市九年级上学期数学月考试题及答案.doc
2021-2022学年辽宁省沈阳市大东区九年级上学期语文期末试题及答案.doc
2022-2023学年北京东城区初三第一学期物理期末试卷及答案.doc
2018上半年江西教师资格初中地理学科知识与教学能力真题及答案.doc
2012年河北国家公务员申论考试真题及答案-省级.doc
2020-2021学年江苏省扬州市江都区邵樊片九年级上学期数学第一次质量检测试题及答案.doc
2022下半年黑龙江教师资格证中学综合素质真题及答案.doc
