没有合适的资源?快使用搜索试试~ 我知道了~
ORACLE_SQL练手 5篇
需积分: 9 2 下载量 17 浏览量
2018-02-06
15:24:04
上传
评论
收藏 513KB PDF 举报
温馨提示
试读
21页
经典习题,搭配 http://blog.csdn.net/ml_1019/article/details/79257438 味更美。
资源推荐
资源详情
资源评论
一:...................................................................................................................................................................................................................................................3
用表...........................................................................................................................................................................................................................................3
emp....................................................................................................................................................................................................................................3
dept....................................................................................................................................................................................................................................3
1. 列出至少有一个员工的所有部门。.............................................................................................................................................................................. 4
2. 列出薪金比 “ SMITH ” 多的所有员工。..............................................................................................................................................................4
3. 列出所有员工的姓名及其直接上级的姓名。.............................................................................................................................................................. 4
4. 列出受雇日期早于其直接上级的所有员工。.............................................................................................................................................................. 5
5. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门..................................................................................................................5
6. 列出所有 “ CLERK ” (办事员)的姓名及其部门名称。.................................................................................................................................5
7. 列出最低薪金大于 1500 的各种工作。...................................................................................................................................................................... 5
8. 列出在部门 “ SALES ” (销售部)工作的员工的姓名,假定不知道销售部的部门编号。......................................................................... 6
9. 列出薪金高于公司平均薪金的所有员工...................................................................................................................................................................... 6
10. 列出与 “ SCOTT ” 从事相同工作的所有员工。............................................................................................................................................... 6
11. 列出与部门 30 中员工的工作相同的所有员工的姓名和薪金。............................................................................................................................6
12. 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金。................................................................................................................7
13. 列出在每个部门工作的员工数量、平均工资............................................................................................................................................................ 7
14. 列出所有员工的姓名、部门名称和工资.................................................................................................................................................................... 7
15. 列出所有部门的详细信息和部门人数。.................................................................................................................................................................... 7
16. 列出各种工作的最低工资。........................................................................................................................................................................................ 7
17. 列出各个部门的 MANAGER (经理)的最低薪金。............................................................................................................................................8
18. 列出所有员工的年工资 , 按年薪从低到高排序。...................................................................................................................................................8
19. 用一条 sql 语句查询出 emp 表中每个部门工资前三位的数据,显示结果如下:............................................................................................8
二:...........................................................................................................................................................................................................................................8
用表...........................................................................................................................................................................................................................................8
emp....................................................................................................................................................................................................................................8
dept....................................................................................................................................................................................................................................9
1. 找出 EMP 表中的姓名( ENAME )第三个字母是 A 的员工姓名。....................................................................................................................9
2. 找出 EMP 表员工名字中含有 A 和 N 的员工姓名。................................................................................................................................................9
3. 找出所有有佣金的员工,列出姓名、工资、佣金,显示结果按工资从小到大,佣金从大到小。........................................................................ 9
4. 列出部门编号为 20 的所有 职位。............................................................................................................................................................................. 10
5. 列出不属于 SALES 的 部门。..................................................................................................................................................................................... 10
6. 显示工资不在 1000 到 1500 之间的员工信息:名字、工资,按工资从大到小排序。...................................................................................... 10
7. 显示职位为 MANAGER 和 SALESMAN ,年薪在 15000 和 20000 之间的员工的信息:名字 、 职位、年薪。.................................... 10
8. 说明以下两条 SQL 语句的输出结果:........................................................................................................................................................................10
9. 让 SELECT 语句的输出结果为.....................................................................................................................................................................................11
10. 判断 SELECT ENAME,SAL FROM EMP WHERE SAL > '1500' 是否抱错,为什么?........................................................................................11
用表.........................................................................................................................................................................................................................................11
EMPLOYEES................................................................................................................................................................................................................... 11
HR.REGIONS;.............................................................................................................................................................................................................. 11
1. 让 SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM < 5 输出结果的货币单位是¥和 $ 。...........11
2. 列出前五位每个员工的名字 , 工资 、 涨薪后的的工资 ( 涨幅为 8% ) , 以 “ 元 ” 为单位进行四舍五入。.............................12
3. 找出谁是最高领导,将名字按大写形式显示。........................................................................................................................................................... 12
4. 找出 First_Name 为 David , Last_Name 为 Austin 的直接领导名字。............................................................................................................. 12
5. First_Name 为 Alexander , Last_Name 为 Hunold 的直接下属员工是哪些 。..................................................................................................12
6
.
哪些员工的工资高于他直接上司的工资,列出员工的名字和工资,上司的名字和工资..................................................................................... 12
7. 哪些员工和 Chen(LAST_NAME) 同部门。................................................................................................................................................................ 12
8. 哪些员工跟 De Haan(LAST_NAME) 做一样职位。.................................................................................................................................................. 13
9. 哪些员工跟 Hall(LAST_NAME) 不在同一个部门。..................................................................................................................................................13
10. 哪些员工跟 William ( FIRST_NAME ) 、 Smith(LAST_NAME) 做不一样的职位。.................................................................................13
11. 显示有提成的员工的信息:名字、提成、所在部门名称、所在地区的名称。.....................................................................................................13
12. 显示 Executive 部门有哪些职位。............................................................................................................................................................................. 13
13. 整个公司中,最高工资和最低工资相差多少。......................................................................................................................................................... 14
14. 提成大于 0 的人数。................................................................................................................................................................................................... 14
15. 显示整个公司的最高工资、最低工资、工资总和、平均工资保留到整数位。.....................................................................................................14
16. 整个公司有多少个领导。............................................................................................................................................................................................. 14
17. 列出在同一部门入职日期晚但工资高于其他同事的员工:名字、工资、入职日期。........................................................................................ 14
用表.........................................................................................................................................................................................................................................14
表名:REGIONS........................................................................................................................................................................................................... 14
表名:COUNTRIES...................................................................................................................................................................................................... 14
表名:LOCATIONS...........................................................................................................................................................................................................15
表名:DEPARTMENTS...................................................................................................................................................................................................... 15
表名:JOBS.....................................................................................................................................................................................................................15
表名:EMPLOYEES...........................................................................................................................................................................................................15
ER 图:..................................................................................................................................................................................................................................15
1. 各个部门平均、最大、最小工资、人数,按照部门号升序排列。...........................................................................................................................16
2. 各个部门中工资大于 5000 的员工人数。................................................................................................................................................................... 16
3
.
各个部门平均工资和人数,按照部门名字升序排列。..............................................................................................................................................16
4. 列出每个部门中有同样工资的员工的统计信息,列出他们的部门号,工资,人数。.......................................................................................... 16
5. 列出同部门中工资高于 1000 的员工数量超过 2 人的部门,显示部门名字、地区名称。................................................................................ 17
6. 哪些员工的工资,高于整个公司的平均工资,列出员工的名字和工资(降序 ) 。.......................................................................................... 17
7. 哪些员工的工资,介于 50 号 和 80 号 部门平均工资之间。...............................................................................................................................17
8. 所在部门平均工资高于 5000 的员工名字。............................................................................................................................................................... 17
9. 列出各个部门中工资最高的员工的信息:名字、部门号、工资。...........................................................................................................................18
10. 最高的部门平均工资是多少。..................................................................................................................................................................................... 18
五:.................................................................................................................................................................................................................................................18
用表.........................................................................................................................................................................................................................................18
表名:REGIONS........................................................................................................................................................................................................... 18
表名:COUNTRIES...................................................................................................................................................................................................... 18
表名:LOCATIONS...........................................................................................................................................................................................................18
表名:DEPARTMENTS...................................................................................................................................................................................................... 19
表名:JOBS.....................................................................................................................................................................................................................19
表名:EMPLOYEES...........................................................................................................................................................................................................19
ER 图:..................................................................................................................................................................................................................................19
1. 哪些部门的人数比 90 号部门的人数多。................................................................................................................................................................... 19
2. Den (FIRST_NAME) 、 Raphaely (LAST_NAME) 的领导是谁(非关联子查询 in) 。.....................................................................................20
3. Den (FIRST_NAME) 、 Raphaely (LAST_NAME) 领导谁(树形查询) 。.......................................................................................................... 20
4. Den (FIRST_NAME) 、 Raphaely (LAST_NAME) 的领导是谁(关联子查询 exists ) 。..................................................................................20
5. 列出在同一部门共事,入职日期晚但工资高于其他同事的员工:名字、工资、入职日期.................................................................................. 20
6. Finance
部门有哪些职位(非关联子查询
in
) 。............................................................................................................................................. 20
7. Finance
部门有哪些职位(关联子查询
exists
) 。....................................................................................................................................... 20
一:
用表
emp
Name
Type
Nullable
Comments
EMPNO
NUMBER(4)
员工号
ENAME
VARCHAR2(10)
Y
员工姓名
JOB
VARCHAR2(9)
Y
工作
MGR
NUMBER(4)
Y
上级编号
HIREDATE
DATE
Y
雇佣日期
SAL
NUMBER(7,2)
Y
薪金
COMM
NUMBER(7,2)
Y
佣金
DEPTNO
NUMBER(2)
Y
部门编号
表中数据
CREATE TABLE emp AS SELECT * FROM scott.emp;
INSERT INTO EMP VALUES (102, 'EricHu', 'Developer',1455, DATE '2011-5-26', 5500.00,14.00,10 );
INSERT INTO EMP VALUES (104, 'huyong', 'PM', 1455, DATE '2011-5-26', 5500.00,14.00,10 );
INSERT INTO EMP VALUES (105, 'WANGJING', 'Developer', 1455, DATE '2011-5-26', 5500.00,14.00,10 );
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO
7369
SMITH
CLERK
7902
1980-12-17
800
20
7499
ALLEN
SALESMAN
7698
1981-02-20
1600
300
30
7521
WARD
SALESMAN
7698
1981-02-22
1250
500
30
7566
JONES
MANAGER
7839
1981-04-02
2975
20
7654
MARTIN
SALESMAN
7698
1981-09-28
1250
1400
30
7698
BLAKE
MANAGER
7839
1981-05-01
2850
30
7782
CLARK
MANAGER
7839
1981-06-09
2450
10
7788
SCOTT
ANALYST
7566
1987-04-19
3000
20
7839
KING
PRESIDENT
1981-11-17
5000
10
7844
TURNER
SALESMAN
7698
1981-09-08
1500
0
30
7876
ADAMS
CLERK
7788
1987-05-23
1100
20
7900
JAMES
CLERK
7698
1981-12-03
950
30
7902
FORD
ANALYST
7566
1981-12-03
3000
20
7934
MILLER
CLERK
7782
1982-01-23
1300
10
102
EricHu
Developer
1455
2011-05-26
5500
14
10
104
huyong
PM
1455
2011-05-26
5500
14
10
105
WANGJING
Developer
1455
2011-05-26
5500
14
10
dept
Name
Type
Nullable
Comments
DEPTNO
NUMBER(2)
部门编号
DNAME
VARCHAR2(14)
Y
部门名称
LOC
VARCHAR2(13)
Y
地点
表中数据
CREATE TABLE DEPT AS SELECT * FROM scott.DEPT;
INSERT INTO DEPT VALUES (50,'50abc','50def');
INSERT INTO DEPT VALUES (60,'Developer','HaiKou');
DEPTNO
DNAME
LOC
10
ACCOUNTING
NEW YORK
20
RESEARCH
DALLAS
30
SALES
CHICAGO
40
OPERATIONS
BOSTON
50
50abc
50def
60
Developer
HaiKou
1
. 列出至少有一个员工的所有部门。
/*不是所有的部门下面都有员工,需要列出有员工的部门*/
DEPTNO DNAME LOC
------ -------------- -------------
30 SALES CHICAGO
20 RESEARCH DALLAS
10 ACCOUNTING NEW YORK
3 rows selected
2. 列出薪金比 “ SMITH ” 多的所有员工。
/*SMITH 的工资是 800*/
SAL ENAME
--------- ----------
800.00 SMITH
1 row selected
/*用一个语句查出比 SMITH 工资(不包括提成)多的员工*/
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 10
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-05-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-03 950.00 30
7902 FORD ANALYST 7566 1981-12-03 3000.00 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 10
102 EricHu Developer 1455 2011-05-26 5500.00 14.00 10
104 huyong PM 1455 2011-05-26 5500.00 14.00 10
105 WANGJING Developer 1455 2011-05-26 5500.00 14.00 10
16 rows selected
3
. 列出所有员工的姓名及其直接上级的姓名。
EMPNO ENAME MRG_NAME
----- ---------- ----------
102 EricHu
104 huyong
105 WANGJING
7369 SMITH FORD
7499 ALLEN BLAKE
7521 WARD BLAKE
7566 JONES KING
7654 MARTIN BLAKE
7698 BLAKE KING
7782 CLARK KING
7788 SCOTT JONES
7839 KING
7844 TURNER BLAKE
7876 ADAMS SCOTT
7900 JAMES BLAKE
7902 FORD JONES
7934 MILLER CLARK
17 rows selected
4. 列出受雇日期早于其直接上级的所有员工。
EMPNO ENAME
----- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7698 BLAKE
7782 CLARK
6 rows selected
5
. 列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门
DEPTNO DNAME EMPNO ENAME
------ -------------- ----- ----------
20 RESEARCH 7369 SMITH
30 SALES 7499 ALLEN
30 SALES 7521 WARD
20 RESEARCH 7566 JONES
30 SALES 7654 MARTIN
30 SALES 7698 BLAKE
10 ACCOUNTING 7782 CLARK
20 RESEARCH 7788 SCOTT
10 ACCOUNTING 7839 KING
30 SALES 7844 TURNER
20 RESEARCH 7876 ADAMS
30 SALES 7900 JAMES
20 RESEARCH 7902 FORD
10 ACCOUNTING 7934 MILLER
10 ACCOUNTING 102 EricHu
10 ACCOUNTING 104 huyong
10 ACCOUNTING 105 WANGJING
50 50abc
40 OPERATIONS
60 Developer
20 rows selected
6
. 列出所有 “
CLERK
” (办事员)的姓名及其部门名称。
DEPTNO DNAME EMPNO ENAME
------ -------------- ----- ----------
10 ACCOUNTING 7934 MILLER
20 RESEARCH 7876 ADAMS
20 RESEARCH 7369 SMITH
30 SALES 7900 JAMES
4 rows selected
7. 列出最低薪金大于 1500(不包括 1500) 的各种工作。
JOB
剩余20页未读,继续阅读
资源评论
VeeLe
- 粉丝: 90
- 资源: 6
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
最新资源
- AIS2024 valid
- 最入门的爬虫代码 python.docx
- 爬虫零基础入门-爬取天气预报.pdf
- 最通俗易懂的 MongoDB 非结构化文档存储数据库教程.zip
- 以mongodb为数据库的订单物流小项目.zip
- 腾讯云-mongodb数据库, 项目部署.zip
- 腾讯 APIJSON 的 MongoDB 数据库插件.zip
- 理解非关系型数据库和关系型数据库的区别.zip
- 操作简单的Mongodb网页web管理工具,基于Spring Boot2.0支持mongodb集群.zip
- tms-mongodb-web,提供访问mongodb数据的REST API和可灵活扩展的mongodb web 客户端.zip
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功