Oracle查询入门练习
上一篇 / 下一篇 2012-08-07 19:30:42 / 个人分类：oracle
练习针对Oracle的SCOTT方案
============查询及函数部分==============
第一部分：
1、使用基本查询语句.
(1)查询DEPT表显示所有部门名称.
(2)查询EMP表显示所有雇员名及其全年收入(月收入=工资+补助),处理NULL行,并指定列别名为"年收入"。（NVL(comm,0) comm取空值时用0替代）
(3)查询显示不存在雇员的所有部门号。
2、限制查询数据
(1)查询EMP表显示工资超过2850的雇员姓名和工资。
(2)查询EMP表显示工资不在1500～2850之间的所有雇员及工资。
(3)查询EMP表显示代码为7566的雇员姓名及所在部门代码。
(4)查询EMP表显示部门10和30中工资超过1500的雇员名及工资。
(5)查询EMP表显示第2个字符为"A"的所有雇员名其工资。
(6)查询EMP表显示补助非空的所有雇员名及其补助。
3、排序数据
(1)查询EMP表显示所有雇员名、工资、雇佣日期，并以雇员名的升序进行排序。
(2)查询EMP表显示在1981年2月1日到1981年5月1日之间雇佣的雇员名、岗位及雇佣日期，并以雇佣日期进行排序。
(3)查询EMP表显示获得补助的所有雇员名、工资及补助，并以工资升序和补助降序排序。
第二部分：
1．列出至少有一个雇员的所有部门。
2．列出薪金比“SMITH”多的所有雇员。
3．列出所有雇员的姓名及其上级的姓名。
4．列出入职日期早于其直接上级的所有雇员。
5．列出部门名称和这些部门的雇员，同时列出那些没有雇员的部门。
6．列出所有“CLERK”（办事员）的姓名及其部门名称。
7．列出各种岗位的最低薪金，并显示最低薪金大于1500所有工作岗位及其最低薪资。
8．列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号。
9．列出薪金高于公司平均的所有雇员。
10．列出与“SCOTT”从事相同工作的所有雇员。
11．列出薪金等于在部门30工作的所有雇员的薪金的雇员的姓名和薪金。
12．列出薪金高于在部门30工作的所有雇员的薪金的雇员的姓名和薪金。
13．列出在每个部门工作的雇员的数量以及其他信息。
14．列出所有雇员的雇员名称、部门名称和薪金。
15．列出从事同一种工作但属于不同部门物雇员的不同组合。
16．列出分配有雇员数量的所有部门的详细信息即使是分配有0个雇员。
17．列出各种类别工作的最低工资。
18．列出各个部门的MANAGER（经理）的最低薪金。
19．列出按计算的字段排序的所有雇员的年薪。
第三部分：
1.找出各月倒数第3天受雇的所有员工.
2.找出早于12年前受雇的员工.
3.以首字母大写的方式显示所有员工的姓名.
4.显示正好为5个字符的员工的姓名.
5.显示不带有"R"的员工的姓名
6.显示所有员工姓名的前三个字符.
7.显示所有员工的姓名,用a替换所有"A"
8.显示满10年服务年限的员工的姓名和受雇日期.:
9.显示员工的详细资料,按姓名排序.
10.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
11.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.
12.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.
13.显示在一个月为30天的情况所有员工的日薪金,忽略余数.
14.找出在(任何年份的)2月受聘的所有员工。
15.对于每个员工,显示其加入公司的天数.
16.显示姓名字段的任何位置包含"A"的所有员工的姓名.
17.以年月日的方式显示所有员工的服务年限. (大概)
第四部分：
1 显示雇员雇佣期满6个月后下一个星期五的日期，显示格式为 "2001年 , 12月 23日,星期六",并按雇佣日期排序。
2 显示雇员的姓名、受雇用日期及受雇用的当天是星期几（列标题为DAY）,并以DAY排序
3 显示2005年的母亲节是什么日期(格式：年-月-日)
(每年5月的第二个星期日)
4 显示当前周的起止日期，默认周日为第一天，周六为最后一天
5 显示下一周的今天的日期
6 用一个查询语句，实现查询各个岗位的总工资和各个部门的总工资和所有雇员的总工资
7 用一个查询语句，实现查询各个岗位的总工资和所有雇员的总工资
8 用一个查询语句，实现查询各个部门的总工资和各个岗位的总工资
9 用集合操作实现第8题的要求
10 通过查询显示每个员工的姓名和工资等级
11 通过查询显示每个员工的姓名和部门名称，并通过部门的不同分组
12 查询每个员工的姓名和他的上司的名字
=============数据操作部分==============
1、向emp表中插入一纪录，员工TOM，80年1月10日入职，薪金为3000，没有补贴（comm）
2、利用子查询建立表emps，与表emp的结构相同，但是只是需要存储10号部门和岗位为MANAGER的员工。
3、将emps表中的与emp表中scott用户具有相同工作岗位的人的工资更改为原来的105%
4、将emp表中的属于同一工资等级的且此级别人数最多的那些人的工资增加3%。
5、将emp表中的部门平均工资最低的部门的所有人按照工资等级分别增加1级5%，2级4%，3级3%，4级2%，5级1%。
6、将emp表中岗位平均工资最高的岗位的所有人插入到新表hi_job_emp.
7、创建my_employee表，并向表中添加数据，数据参考如下：
ID Last_name First_name UserID Salary
1 Patel Ralph rpatel 795
2 Dancs Betty bdancs 860
3 Biri Ben bbiri 110
4 Newman Chard cnewman 750
5 Ropeburn Audry apopebur 1550
1、编写如下的脚本:
set echo off
set feedback off
prompt Creating The My_employee table. Please wait...
create table my_employee (
id number(4) constraint my_employee_id_nn not null,
last_name varchar2(25),
first_name varchar2(25),
userid varchar28),
salary number(9,2))
/
(2).显示my_employee表的结构
(3).向my_employee表中添加首条纪录，要求不在insert语句中使用字段列表
(4).向my_employee表中添加第二条纪录，要求在insert语句中使用字段列表
(5).验证数据是否添加成功
(6).创建脚本文件loademp.sql，以交互方式向向my_employee表添加纪录。提示用户输入雇员的id, first_name, last_name, salary,
userid(由first_name的第一个字母及last_name的前7个字母组成)
(7).运行脚本，插入下两条纪录。
(8).验证表中的纪录
(9).使数据的添加成为永久性的。
2、将3号员工的Last_name修改为Drexler
3、将所有工资小于900的员工的工资修改为1000，并验证数据修改
4、将Betty Dancs从my_employee表中删除，验证删除后的结果，并使数据修改变为永久的。
5、运行脚本文件loademp.sql添加最后一条纪录，并验证数据的添加。
6、将当前数据状态保存为一个事务存储点，删除表中的所有数据，并确认表是否为空。
7、放弃所有的数据删除，并将数据恢复到删除前的状态，并验证数据是否已经恢复。
8、把数据的变化变为永久的。
第五部分综合练习
1、创建一个查询显示如下信息：
EMP_INF
The Job Title for SCOTT is sales .
......
The Job Title for MILLER is clerk .
已选择14行。
2、检索姓名最后一个字符为'N'的雇员的姓名（姓名首字母大写），还显示姓名的长度，以及姓名中的字母'A'的位置。
3、计算出雇员进入公司的星期数。
4、显示受雇时间不满250个月的雇员的编号、受雇日期、受雇的月数、满六个月的复审日期受雇后的第一个星期五以及受雇当月当月的最后一天。
5、以$99，999的形式显示雇员工资及雇员的姓名、部门名称和工资等级。
6、显示没有上级管理者的公司首脑的姓名，并在MGR列上显示‘No Manager’。
7、显示雇员雇佣期满6个月后下一个星期五的日期，显示格式为'yyyy年mm月dd日'，并且按照雇佣日期排序。
8、显示雇员姓名、受雇日期及工资复审日期（复审日期为受雇后6个月后的第一个星期一），复审日期
以列标题review显示，并且显示的日期形式为“星期一,12月的第23天，1998年”。
9、显示每个雇员的姓名并计算出从受雇日期起到目前一共工作了多少个月，以列标题'month_worked'显示
月数（四舍五入），输出以月数升序排列。
10、编写一个查询显示名字以J、A、M开头的雇员的姓名及姓名所占的字符数，姓名的显示格式为第一个字母
大写其他字母小写，为每个列设置合适的列标题。
11、显示雇员的姓名、受雇日期及受雇当天是星期几（列标题为DAY），并以DAY升序排列。
12、创建一个查询显示雇员姓名及其奖金（列标题为comm），如果该雇员不能获得奖金则以‘No Commission’显示。
13、创建一个查询显示员工名，工资，部门名称和工资等级。
============查询及函数部分==============
第一部分：
1、使用基本查询语句.
(1)查询DEPT表显示所有部门名称.
(2)查询EMP表显示所有雇员名及其全年收入(月收入=工资+补助),处理NULL行,并指定列别名为"年收入"。（NVL(comm,0) comm取空值时用0替代）
(3)查询显示不存在雇员的所有部门号。
2、限制查询数据
(1)查询EMP表显示工资超过2850的雇员姓名和工资。
(2)查询EMP表显示工资不在1500～2850之间的所有雇员及工资。
(3)查询EMP表显示代码为7566的雇员姓名及所在部门代码。
(4)查询EMP表显示部门10和30中工资超过1500的雇员名及工资。
(5)查询EMP表显示第2个字符为"A"的所有雇员名其工资。
(6)查询EMP表显示补助非空的所有雇员名及其补助。
3、排序数据
(1)查询EMP表显示所有雇员名、工资、雇佣日期，并以雇员名的升序进行排序。
(2)查询EMP表显示在1981年2月1日到1981年5月1日之间雇佣的雇员名、岗位及雇佣日期，并以雇佣日期进行排序。
(3)查询EMP表显示获得补助的所有雇员名、工资及补助，并以工资升序和补助降序排序。
第二部分：
1．列出至少有一个雇员的所有部门。
2．列出薪金比“SMITH”多的所有雇员。
3．列出所有雇员的姓名及其上级的姓名。
4．列出入职日期早于其直接上级的所有雇员。
5．列出部门名称和这些部门的雇员，同时列出那些没有雇员的部门。
6．列出所有“CLERK”（办事员）的姓名及其部门名称。
7．列出各种岗位的最低薪金，并显示最低薪金大于1500所有工作岗位及其最低薪资。
8．列出从事“SALES”（销售）工作的雇员的姓名，假定不知道销售部的部门编号。
9．列出薪金高于公司平均的所有雇员。
10．列出与“SCOTT”从事相同工作的所有雇员。
11．列出薪金等于在部门30工作的所有雇员的薪金的雇员的姓名和薪金。
12．列出薪金高于在部门30工作的所有雇员的薪金的雇员的姓名和薪金。
13．列出在每个部门工作的雇员的数量以及其他信息。
14．列出所有雇员的雇员名称、部门名称和薪金。
15．列出从事同一种工作但属于不同部门物雇员的不同组合。
16．列出分配有雇员数量的所有部门的详细信息即使是分配有0个雇员。
17．列出各种类别工作的最低工资。
18．列出各个部门的MANAGER（经理）的最低薪金。
19．列出按计算的字段排序的所有雇员的年薪。
第三部分：
1.找出各月倒数第3天受雇的所有员工.
2.找出早于12年前受雇的员工.
3.以首字母大写的方式显示所有员工的姓名.
4.显示正好为5个字符的员工的姓名.
5.显示不带有"R"的员工的姓名
6.显示所有员工姓名的前三个字符.
7.显示所有员工的姓名,用a替换所有"A"
8.显示满10年服务年限的员工的姓名和受雇日期.:
9.显示员工的详细资料,按姓名排序.
10.显示员工的姓名和受雇日期,根据其服务年限,将最老的员工排在最前面.
11.显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序.
12.显示所有员工的姓名、加入公司的年份和月份,按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面.
13.显示在一个月为30天的情况所有员工的日薪金,忽略余数.
14.找出在(任何年份的)2月受聘的所有员工。
15.对于每个员工,显示其加入公司的天数.
16.显示姓名字段的任何位置包含"A"的所有员工的姓名.
17.以年月日的方式显示所有员工的服务年限. (大概)
第四部分：
1 显示雇员雇佣期满6个月后下一个星期五的日期，显示格式为 "2001年 , 12月 23日,星期六",并按雇佣日期排序。
2 显示雇员的姓名、受雇用日期及受雇用的当天是星期几（列标题为DAY）,并以DAY排序
3 显示2005年的母亲节是什么日期(格式：年-月-日)
(每年5月的第二个星期日)
4 显示当前周的起止日期，默认周日为第一天，周六为最后一天
5 显示下一周的今天的日期
6 用一个查询语句，实现查询各个岗位的总工资和各个部门的总工资和所有雇员的总工资
7 用一个查询语句，实现查询各个岗位的总工资和所有雇员的总工资
8 用一个查询语句，实现查询各个部门的总工资和各个岗位的总工资
9 用集合操作实现第8题的要求
10 通过查询显示每个员工的姓名和工资等级
11 通过查询显示每个员工的姓名和部门名称，并通过部门的不同分组
12 查询每个员工的姓名和他的上司的名字
=============数据操作部分==============
1、向emp表中插入一纪录，员工TOM，80年1月10日入职，薪金为3000，没有补贴（comm）
2、利用子查询建立表emps，与表emp的结构相同，但是只是需要存储10号部门和岗位为MANAGER的员工。
3、将emps表中的与emp表中scott用户具有相同工作岗位的人的工资更改为原来的105%
4、将emp表中的属于同一工资等级的且此级别人数最多的那些人的工资增加3%。
5、将emp表中的部门平均工资最低的部门的所有人按照工资等级分别增加1级5%，2级4%，3级3%，4级2%，5级1%。
6、将emp表中岗位平均工资最高的岗位的所有人插入到新表hi_job_emp.
7、创建my_employee表，并向表中添加数据，数据参考如下：
ID Last_name First_name UserID Salary
1 Patel Ralph rpatel 795
2 Dancs Betty bdancs 860
3 Biri Ben bbiri 110
4 Newman Chard cnewman 750
5 Ropeburn Audry apopebur 1550
1、编写如下的脚本:
set echo off
set feedback off
prompt Creating The My_employee table. Please wait...
create table my_employee (
id number(4) constraint my_employee_id_nn not null,
last_name varchar2(25),
first_name varchar2(25),
userid varchar28),
salary number(9,2))
/
(2).显示my_employee表的结构
(3).向my_employee表中添加首条纪录，要求不在insert语句中使用字段列表
(4).向my_employee表中添加第二条纪录，要求在insert语句中使用字段列表
(5).验证数据是否添加成功
(6).创建脚本文件loademp.sql，以交互方式向向my_employee表添加纪录。提示用户输入雇员的id, first_name, last_name, salary,
userid(由first_name的第一个字母及last_name的前7个字母组成)
(7).运行脚本，插入下两条纪录。
(8).验证表中的纪录
(9).使数据的添加成为永久性的。
2、将3号员工的Last_name修改为Drexler
3、将所有工资小于900的员工的工资修改为1000，并验证数据修改
4、将Betty Dancs从my_employee表中删除，验证删除后的结果，并使数据修改变为永久的。
5、运行脚本文件loademp.sql添加最后一条纪录，并验证数据的添加。
6、将当前数据状态保存为一个事务存储点，删除表中的所有数据，并确认表是否为空。
7、放弃所有的数据删除，并将数据恢复到删除前的状态，并验证数据是否已经恢复。
8、把数据的变化变为永久的。
第五部分综合练习
1、创建一个查询显示如下信息：
EMP_INF
The Job Title for SCOTT is sales .
......
The Job Title for MILLER is clerk .
已选择14行。
2、检索姓名最后一个字符为'N'的雇员的姓名（姓名首字母大写），还显示姓名的长度，以及姓名中的字母'A'的位置。
3、计算出雇员进入公司的星期数。
4、显示受雇时间不满250个月的雇员的编号、受雇日期、受雇的月数、满六个月的复审日期受雇后的第一个星期五以及受雇当月当月的最后一天。
5、以$99，999的形式显示雇员工资及雇员的姓名、部门名称和工资等级。
6、显示没有上级管理者的公司首脑的姓名，并在MGR列上显示‘No Manager’。
7、显示雇员雇佣期满6个月后下一个星期五的日期，显示格式为'yyyy年mm月dd日'，并且按照雇佣日期排序。
8、显示雇员姓名、受雇日期及工资复审日期（复审日期为受雇后6个月后的第一个星期一），复审日期
以列标题review显示，并且显示的日期形式为“星期一,12月的第23天，1998年”。
9、显示每个雇员的姓名并计算出从受雇日期起到目前一共工作了多少个月，以列标题'month_worked'显示
月数（四舍五入），输出以月数升序排列。
10、编写一个查询显示名字以J、A、M开头的雇员的姓名及姓名所占的字符数，姓名的显示格式为第一个字母
大写其他字母小写，为每个列设置合适的列标题。
11、显示雇员的姓名、受雇日期及受雇当天是星期几（列标题为DAY），并以DAY升序排列。
12、创建一个查询显示雇员姓名及其奖金（列标题为comm），如果该雇员不能获得奖金则以‘No Commission’显示。
13、创建一个查询显示员工名，工资，部门名称和工资等级。
TAG: