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 testSQL 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_NAMEVARCHAR2(50) FIRST_NAMEVARCHAR2(25) DEPT_IDNUMBER(7)8、查询数据字典表或者使用describe命令确认上表得存在。并将它改名为：EMPLOYEE_BAK9、为表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、sql4、在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 SESSION6、以SCOTT用户登录，将查询Scott用户得emp表得权限授权给temp用户如果就是想级联授予可以加with grant option;7、使用temp用户登录。查询emp表中部门号为20得所有雇员信息，瞧能够成功?删除emp表中部门号为20得所有雇员信息，瞧能够成功?8、以DBA用户，创建用户temp2,密码为temp2,并授予会话创建权限：CREATE SESSION9、以DBA用户，创建角色temp_role10、以scott用户登录，将查询scott得dept表得权限授权给temp_role11、将角色temp_role授给用户temp与temp212、用temp2用户登录，查询表scott、dept得所有信息13、删除角色temp_role。重复操作第12题14、删除用户temp与temp2、