sql课后题.docx
《sql课后题.docx》由会员分享，可在线阅读，更多相关《sql课后题.docx（44页珍藏版）》请在冰点文库上搜索。
sql课后题
1作业
（1）从表中查询出数据
查询出scott.dept表中部门号（deptno）小于50的部门名称（dname）
此处有隐式转换，oracle把varchar2转换成了数字。
（2）描述表scott.emp的结构
（3）进行数学表达式的计算、使用列的别名
从scott.emp表中查出所有员工的工资和奖金之和。
（4）正确统计出scott.emp表中部门号为10的，每个员工全年收入
（5）运用SQL*Plus工具
将缓冲区中的SQL语句保存到一个文件，并执行该文件中的SQL语句。
SQL>L
1*select*fromtest
SQL>save'/u01/test02.sql'
上面（3）中的结果：
2作业
1。
创建一个查询，来显示雇员的名字和工资。
其工资要在$2850以上。
将该语句保存到一个文件中：
p2q1.sql
在sqlplus中执行该文件。
2.更改文件p2q1.sql中的命令，将查询条件改为：
工资（sal）在$1500和$2850之间。
重新执行该文件。
3.查询出雇员表中部门号为10和30的所有员工的姓名、工作、雇佣日期信息，要求按照雇佣日期的降序进行排序。
或隐身转换：
4.查询出姓名中第三个字母为：
“A”的所有员工的姓名。
5.查出佣金比工资多出10%的员工的姓名。
简单写成：
复杂写成：
注意如果sal有空的（注意分母不能为0，而且保持原来的数据最好用1）：
这道题颠倒一下就很难了：
3作业
以下练习题使用hr用户登录
1.查询所有员工及对应部门的记录，包括没有对应部门编号（department_id）的员工记录
a.XXX=b.XXX（+）等价于aleftouterjoinbon（a.XXX=b.XXX）;左外连接。
2.查询所有员工及对应部门的记录，包括没有任何员工的部门记录。
a.XXX（+）=b.XXX等价于arightouterjoinbon（a.XXX=b.XXX）;右外连接。
3.查询所有员工及对应部门的记录，包括没有对应部门编号（department_id）的员工记录和没有任何员工的部门记录。
全外连接只有一种写法：
aallouterjoinbon（a.XXX=b.XXX）;
4.
写一个查询来查询出雇员的名字，部门号，部门名称。
5.输出30号部门的所有工作的列表，以及部门名称。
6.
写一个查询，来查询出挣到佣金（comm）的雇员姓名，部门名称，和部门所处的位置。
7.
写一个查询，查出在名字里面有一个"A"的所有雇员姓名和其所在的部门的名称。
或者：
8.
写出一个查询，查出工作在DALLAS的所有员工的姓名、工作、部门号、部门名称。
9.
查出每个雇员的编号、姓名、其管理者的编号和姓名。
各个列分别命名为：
Emp#,EmpName,EmpM#,EmpName.
注意看下面的语句：
上述表是没有管理者的命名的，我们可以用自连接：
如果a.id=b.上司号，那么这时，a的信息就是当领导的人的信息（不管管几个人）。
也就是a表中的哥们是b表中哥们儿的的领导。
这时a表中有king，而且显示出了该领导管理的人员名单。
如果a.上司号=b.id，那么这时，a的信息就是有上司的人的信息（也就是有领导的人的信息）。
也就是b表中的哥们儿是a表中哥们的领导。
9题结果：
10.修改第9题的查询，使得没有管理者的员工的信息也可以显示出来。
4作业
1.查询出入职超过一年的员工信息
2查询出每个员工从工龄有多少个周
3今年公司准备给职位为ANALYST工资涨10%，CLERK工资涨15%，MANAGER涨20%，其它职位不变，请使用一个select语句模拟工资涨之后的各个员工工资
或写成：
4.写一个查询，选择出当前的日期。
5.查询出雇员表（emp）表中的雇员号、姓名、新的薪水（是原先薪水的1.25倍），并将该列标记为newsalary。
将该脚本保存到p3q2.sql中。
6.运行文件p3q2中的查询。
7.查询出雇员表中所有员工的姓名；雇佣日期；和工资调整日期（工作6个月后的第一个星期一）。
其格式要求是类似于：
“1981-09-23”。
8.
查出每个员工的名字（ename）、雇佣日期到现在的日期间隔的月数（要求为整数），该列命名为MONTHS_WORKED，并以该列的降序排序
9.写一个查询，查询出雇员的姓名，以及姓名的长度。
要求其姓名的第一个字母为大写，其它的字母为小写。
5作业
1.统计每个部门的平均工资，和最高工资，最低工资，并按照部门号排序
我表里有两个没部门的人，所以你的结果可能和我不同，是正常的。
2.查询出平均工资高于2000的部门以及其平均工资
6作业
创建多列的子查询。
在基于未知值的查询中运用子查询。
使用子查询，查询在一个数据集中存在，而在另一个数据集中不存在的数据。
1.查询出部门位置为“DALLAS”的员工号，姓名，工资，职位，部门号
2查询出部门位置为“DALLAS””，“NEWYORK”的员工号，姓名，工资，职位，部门号
3.查询出与员工号为7788的job和sal相匹配的员工信息
4.查询出与员工号为7788的job或者sal相匹配的员工信息
5.查询出其经理不是10部门经理的员工信息
注意10部门经理可以有空值的。
6.查出与BLACK在同一个部门的所有雇员的姓名和雇佣日期。
7.查出工资高于平均工资的所有员工的编号和姓名，其结果按照工资的降序排列。
如果是高于本部门的员工：
8.查出所有名字中包含一个"T"的员工所在的部门号，和这些部门所包含的所有员工编号和姓名。
9.查出所有工作在Dallas的员工的姓名、部门编号、工作。
10.查出由King直接管辖的所有员工的姓名和工资。
11.查询销售部（Sales）的编号，所有员工的名字和工作。
12.写出与任何一个能挣到佣金的雇员的部门号、薪水相匹配的所有雇员的名字、部门号。
13.查询出与在Dallas的任何一个雇员的薪水和佣金相匹配的所有雇员的姓名、部门名称和工资。
14.查询出所有工资和佣金与Scott相同的雇员的姓名、雇佣日期和工资。
注意：
结果中不包括Scott
应该nvl一下，否则：
15去除重复数据：
这两条数据没有什么不同，除了rowid。
这些是原先的数据。
这些是新的数据。
道理很简单，如果没有重复数据，那这条数据的rowid既是最大的，也是最小的。
如果有重复的数据，那么一般而言rowid小的是老数据，rowid大的是新数据（不是绝对的，请注意），在此只要保留一个就行了。
deletefrom就可以删除新的数据：
7作业
创建使用替换变量的查询。
创建包含变量的命令文件。
使用ACCEPT命令
1.使用替换变量，提示用户输入职位名称，查询出员工号，姓名，工资，部门号
2.预定义一个职位名称的一个变量，并在查询出员工号，姓名，工资，部门号
注意define最好给个初值。
或：
3.设置sqlplus环境的linesize为100，并让这个变量永久生效
4.写一个脚本，要求显示某个雇佣时间范围内，所有雇员的姓名、工作和雇佣日期的信息。
要求：
将名字和工作连接起来，中间用“,”隔开，将列命名为EMPLOYEES和HIREDATE。
并使用ACCEPT提示客户输入两个时间范围。
头标题为：
employeeandhiredateinfo
其输出结果应当类似下面：
Pleaseenterthelowdaterange（'MM/DD/YYYY'）:
01/01/1981
Pleaseenterthehighdaterange（'MM/DD/YYYY'）:
01/01/1982
EMPLOYEESHIREDATE
--------------------------------
KING,PRESIDENT17-NOV-81
BLAKE,MANAGER01-MAY-81
CLARK,MANAGER09-JUN-81
JONES,MANAGER02-APR-81
MARTIN,SALESMAN28-SEP-81
ALLEN,SALESMAN20-FEB-81
TURNER,SALESMAN08-SEP-81
JAMES,CLERK03-DEC-81
WARD,SALESMAN22-FEB-81
FORD,ANALYST03-DEC-81
10rowsselected.
首先拼出所要的语句：
然后替换需要的值：
再写出提示：
注意引号需要转义。
实验一下：
最后形成报表：
最后看一下结果：
8作业
（1）向表中插入数据行
（2）修改和删除表中的数据行
（3）控制事务
1.将员工信息表的部门号为10的员工备份到emp01表，emp01的表结构与emp表结构保持一致
2.更改7698号雇员变更到与7499号雇员相同的部门，并且职位也一样
3.做一个保持点的实例
４.在dept表中插入两行数据：
５.将上面的数据删除。
６.定义一个事务：
７.将60号部门的位置改为“TIANJIIN”
9作业
（1）使用CREATETABLEAS语句来创建一个新表
（2）更改列定义
（3）验证这个表是存在的
（4）为该表增加注释
（5）更改tables结构
（6）废弃该表
1.使用子查询创建一个dept10表，结构与dept表一致
2.从数据字典中查询，验证dept10表是否创建
3.将表dept10的列“DNAME”的数据类型改为“VARCHAR2（20）”
4.给表dept10添加注释为“thisisdeptInformation”
5.仿照dept表的格式创建表department并将dept中的数据插入。
6.为dept增加一个新的列，该列的名字为descript,类型为varchar2（100）。
缺省值为'newcolumn'。
7.创建如下结构的EMPLOYEE表：
NameNull?
Type
--------------------------------
IDNUMBER（7）
LAST_NAMEVARCHAR2（50）
FIRST_NAMEVARCHAR2（25）
DEPT_IDNUMBER（7）
8.查询数据字典表或者使用describe命令确认上表的存在。
并将它改名为：
EMPLOYEE_BAK
9.为表EMPLOYEE_BAK增加一条注释：
“thisisaduplicatedtable”.
duplicated意思是复制的。
10作业
1.创建一个结构与EMP表相同结构的EMPLOYEE表，使用ALTERTABLE语句为该表的empno列增加一个表级的主键约束，并命名该约束。
2.创建一个结构与DEPT表相同结构的DEPARTMENT表，在创建表时为该表的deptno列增加一个表级的主键约束，并命名该约束。
3.为EMPLOYEE表增加一个外键约束，保证雇员不能安排到一个不存在的系。
4.从数据字典视图USER_CONSTRAINTS中查询出所添加约束的名称、类型。
11作业
1.创建一个叫做EMP_VU的视图，它的列分别来自于EMP表的empno,ename,deptno；将ename列改名为EMPLOYEE。
该视图能够获取EMP所有行的信息。
不允许从该视图更改数据。
或:
2.查询出EMP_VU的所有数据。
并试图更改SMITH的名字为“JHON”。
3.从数据字典USER_VIEWS中，获取视图名称（VIEW_NAME）和视图文本（TEXT）的信息。
4.创建一个叫做DEPT20的视图，该视图中包括第20号部门的雇员号，雇员名字，部门名称；分别命名为：
EMPLOYEE_ID,EMPLOYEE,和DEPARTMENT_NAME。
或
12作业
（1）创建一个序列
（2）使用序列
（3）创建一个非唯一索引
（4）获取并展示关于序列和索引的数据字典信息
（5）丢弃索引
扩展作业：
1.创建一个序列来产生表DEPARTMENT的主键值。
该序列名字为DEPT_ID_SEQ，它的初始值为60，最大值为200，每次递增10,并且不能循环使用这些值。
2.从user_sequences视图中查出序列名称、最大值、递增值、何下一个值的信息。
3.写一个脚本，往DEPARTMENT表中插入两行信息，要求使用上面创建的序列来产生部门号，并提示用户输入部门名称和部门地址。
执行该脚本。
vi/home/oracle/input.sql
4.在EMPLOYEE表中的deptno列上创建一个非唯一索引deptno_idx。
创建非唯一性索引：
如果创建唯一性索引就是：
13作业
1.创建test01,test02的2个用户，密码和用户名一样
2.赋予test01用户在user表空间有创建视图的权限
没有users表空间的先创建一下：
下面先给用户分配配额：
或：
再赋予创建视图的权限：
3.赋予test02用户能查询scott.emp表的权限
grantcreatesessiontotest02;
有级联是：
4.收回test01用户在user表空间创建视图的权限
5.使用DBA账户创建一个账号为temp,密码为temp用户,并授予会话创建权限：
CREATESESSION
6.以SCOTT用户登录，将查询Scott用户的emp表的权限授权给temp用户
如果是想级联授予可以加withgrantoption;
7.使用temp用户登录。
查询emp表中部门号为20的所有雇员信息，看能够成功?
删除emp表中部门号为20的所有雇员信息，看能够成功?
8.以DBA用户，创建用户temp2,密码为temp2,并授予会话创建权限：
CREATESESSION
9.以DBA用户，创建角色temp_role
10.以scott用户登录，将查询scott的dept表的权限授权给temp_role
11.将角色temp_role授给用户temp和temp2
12.用temp2用户登录，查询表scott.dept的所有信息
13.删除角色temp_role。
重复操作第12题
14.删除用户temp和temp2.
（注：
专业文档是经验性极强的领域，无法思考和涵盖全面，素材和资料部分来自网络，供参考。
可复制、编制，期待你的好评与关注）
升级会员 