sql课后题.doc

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 * from test
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(+) 等价于 a left outer join b on (a、XXX=b、XXX);左外连接。
2、查询所有员工及对应部门得记录，包括没有任何员工得部门记录。

a、XXX(+)=b、XXX 等价于 a right outer join b on (a、XXX=b、XXX);右外连接。
3、查询所有员工及对应部门得记录，包括没有对应部门编号(department_id)得员工记录与没有任何员工得部门记录。

全外连接只有一种写法：a all outer join b on (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倍），并将该列标记为new salary。将该脚本保存到p3q2、sql中。

6、运行文件p3q2中得查询。

7、查询出雇员表中所有员工得姓名；雇佣日期；与工资调整日期（工作6个月后得第一个星期一）。其格式要求就是类似于：“1981-09-23”。

8、
查出每个员工得名字（ename）、雇佣日期到现在得日期间隔得月数（要求为整数），该列命名为MONTHS_WORKED，并以该列得降序排序

9、写一个查询，查询出雇员得姓名，以及姓名得长度。要求其姓名得第一个字母为大写，其它得字母为小写。

5作业
1、统计每个部门得平均工资，与最高工资，最低工资，并按照部门号排序

我表里有两个没部门得人，所以您得结果可能与我不同，就是正常得。
2、查询出平均工资高于2000得部门以及其平均工资

6 作业
创建多列得子查询。
在基于未知值得查询中运用子查询。
使用子查询，查询在一个数据集中存在，而在另一个数据集中不存在得数据。
1、查询出部门位置为“DALLAS”得员工号，姓名，工资，职位，部门号

2查询出部门位置为“DALLAS” ”，“NEW YORK”得员工号，姓名，工资，职位，部门号

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大得就是新数据（不就是绝对得，请注意），在此只要保留一个就行了。
delete from 就可以删除新得数据：

7 作业
创建使用替换变量得查询。
创建包含变量得命令文件。
使用ACCEPT 命令
1、使用替换变量，提示用户输入职位名称，查询出员工号，姓名，工资，部门号

2、预定义一个职位名称得一个变量，并在查询出员工号，姓名，工资，部门号

注意define最好给个初值。
或：

3、设置sqlplus环境得linesize为100，并让这个变量永久生效

4、 写一个脚本，要求显示某个雇佣时间范围内，所有雇员得姓名、工作与雇佣日期得信息。要求：将名字与工作连接起来，中间用“,”隔开，将列命名为EMPLOYEES与HIREDATE。并使用ACCEPT提示客户输入两个时间范围。头标题为：employee and hiredate info
其输出结果应当类似下面：
Please enter the low date range ('MM/DD/YYYY'): 01/01/1981
Please enter the high date range ('MM/DD/YYYY'): 01/01/1982
EMPLOYEES HIREDATE
----------------- ---------------
KING, PRESIDENT 17-NOV-81
BLAKE, MANAGER 01-MAY-81
CLARK, MANAGER 09-JUN-81
JONES, MANAGER 02-APR-81
MARTIN, SALESMAN 28-SEP-81
ALLEN, SALESMAN 20-FEB-81
TURNER, SALESMAN 08-SEP-81
JAMES, CLERK 03-DEC-81
WARD, SALESMAN 22-FEB-81
FORD, ANALYST 03-DEC-81
10 rows selected、
首先拼出所要得语句：

然后替换需要得值：

再写出提示：

注意引号需要转义。
实验一下：

最后形成报表：

最后瞧一下结果：

8作业
（1）向表中插入数据行
（2）修改与删除表中得数据行
（3）控制事务
1、将员工信息表得部门号为10得员工备份到emp01表，emp01得表结构与emp表结构保持一致

2、更改7698号雇员变更到与7499号雇员相同得部门，并且职位也一样

3、做一个保持点得实例

４、在dept表中插入两行数据：

５、将上面得数据删除。

６、定义一个事务：

７、将60号部门得位置改为“TIANJIIN”

9 作业
（1）使用 CREATE TABLE AS 语句来创建一个新表
（2）更改列定义
（3）验证这个表就是存在得
（4）为该表增加注释
（5）更改tables结构
（6）废弃该表
1、使用子查询创建一个dept10表，结构与dept表一致

2、从数据字典中查询，验证dept10表就是否创建

3、将表dept10得列“DNAME”得数据类型改为“VARCHAR2(20)”

4、给表dept10添加注释为“this is dept Information”

5、仿照dept表得格式创建表department并将dept中得数据插入。

6、为dept增加一个新得列，该列得名字为descript,类型为varchar2(100)。缺省值为'new column'。

7、创建如下结构得EMPLOYEE表：
Name Null? Type
------------- -------- -----------
ID NUMBER(7)

LAST_NAME VARCHAR2(50)

FIRST_NAME VARCHAR2(25)

DEPT_ID NUMBER(7)

8、查询数据字典表或者使用describe命令确认上表得存在。并将它改名为：EMPLOYEE_BAK

9、为表EMPLOYEE_BAK增加一条注释：“this is a duplicated table”、

duplicated 意思就是复制得。
10 作业
1、创建一个结构与EMP表相同结构得EMPLOYEE表，使用ALTER TABLE语句为该表得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表得权限
grant create session to test02;

有级联就是：

4、收回test01用户在user表空间创建视图得权限

5、使用DBA账户创建一个账号为temp,密码为temp用户,并授予会话创建权限：CREATE SESSION

6、以SCOTT用户登录，将查询Scott用户得emp表得权限授权给temp用户

如果就是想级联授予可以加with grant option;
7、使用temp用户登录。查询emp表中部门号为20得所有雇员信息，瞧能够成功?
删除emp表中部门号为20得所有雇员信息，瞧能够成功?

8、以DBA用户，创建用户temp2,密码为temp2,并授予会话创建权限：CREATE SESSION

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、