【安博培训笔记】Oracle4 事务和数据库对象 作业20130910

Oracle4 事务和数据库对象 作业
一、数据库对象
1.为表vendor_master创建一个视图，该视图将VENADD1、VENADD2和VENADD3(地址的三部分，都为varchar2性) 3个列连接起来组成名为VENADDRES的列.
create view venadds as
       select venadd1 from vendor_master union all
       select venadd2 from vendor_master union all
       select venadd3 from vendor_master;


grant create any view to scott;
create view emp_master as
       select empno from emp union all
       select deptno from emp; 
select * from emp_master order by empno;
CREATE VIEW 视图名称 AS 
  SELECT * FROM 第1张表 UNION ALL
  SELECT * FROM 第2张表 UNION ALL
  SELECT * FROM 第3张表;
2.创建名为“my_seq”的序列,该序列的起始值为1000,并在每次查询时增加10,直到该序列达到1100,然后重新从1000开始.
CREATE SEQUENCE my_seq
  START WITH 1000
  INCREMENT BY 10
  MAXVALUE 1100
  MINVALUE 1000
  CYCLE
  CACHE 10;
SELECT my_seq.NEXTVAL FROM emp;
SELECT my_seq.CURRVAL FROM emp;
DROP SEQUENCE my_seq;
3.在表order_detail表的orderno和itemcode列上创建一个唯一组合索引.
create unique index x_order_detail
     ON order_detail(orderno, itemcode);
     
CREATE UNIQUE INDEX x_emp
     ON emp(ename, empno);
drop INDEX x_emp;
二、使用如下表
emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
dept部门表(deptno部门编号/dname部门名称/loc地点)
完成以下查询
1、列出月薪比 "BLAKE" 少的所有雇员
select * from emp where sal<
       (select sal from emp where ename = 'BLAKE');
2、列出至少有一个雇员的部门详细信息
select * from dept where deptno in 
       (select distinct deptno from emp);
select * from dept where deptno in 
       (select distinct deptno from emp 
       group by deptno having count(deptno)>0);
3、列出所有雇员的姓名及其直接上级的姓名。
select t1.ename 员工的姓名,t2.ename 直接上级的姓名 
       from emp t1,emp t2 where t1.mgr = t2.empno;
4、列出入职日期早于其直接上级的所有雇员
select t1.ename 员工的姓名,t1.hiredate,t2.ename 直接上级的姓名,t2.hiredate 
       from emp t1,emp t2 where t1.mgr = t2.empno and t1.hiredate < t2.hiredate;
5、列出没有雇员的部门信息
select * from dept where deptno not in 
       (select distinct deptno from emp);
6、列出所有“CLERK”（办事员）的姓名及其部门名称
select ename,dname from emp,dept
       where emp.deptno = dept.deptno and job = 'CLERK';
7、列出最低薪金大于1500的工作类别信息
select job,min(sal) 最低薪金 from emp 
       group by job having min(sal) > 1500;
8、列出月薪高于公司平均水平的所有雇员
select * from emp where sal >
       (select avg(sal) from emp);
9、列出与“SCOTT”从事相同工作的所有雇员
select * from emp where job = 
       (select job from emp where ename = 'SCOTT');    
10、列出某些雇员的姓名和薪金，条件是他们的月薪高于部门30中所有雇员的薪金
select ename,sal from emp where sal > 
       (select max(sal) from emp where deptno = 30);
11、列出每个部门的信息以及该部门中雇员的数量--  
select dept.deptno,dname,loc,count(ename) 人数
       from emp,dept where emp.deptno = dept.deptno
       group by dept.deptno,dname,loc;
select d1.deptno,d1.dname,d1.loc,d2.c 部门人数 
       from dept d1 left join 
       (select count(*) c,deptno dn from emp group by deptno)
       d2 on d1.deptno = d2.dn;
12、列出所有雇员的雇员名称、部门名称和月薪
select emp.ename,dept.dname,emp.sal+nvl(emp.comm,0) 工资 
       from emp,dept where emp.deptno = dept.deptno;
select ename,nvl(dname,'无部门') 部门名称,sal+nvl(comm,0) 工资 
       from emp left join dept on emp.deptno = dept.deptno;
13、列出各个部门的MANAGER（经理）的最低薪金
select dept.deptno,dname,job,ename,min(sal+nvl(comm,0))
        from emp,dept where emp.deptno = dept.deptno and job = 'MANAGER'
        group by dept.deptno,dname,job,ename;
select dname,min(sal) from emp join dept on emp.deptno = dept.deptno 
       where job = 'MANAGER' group by dname;
14、列出所有雇员的年薪，并且按年薪排序
select ename,(sal + nvl(comm,0))*12 年薪 from emp order by 年薪; 
15、列出薪金水平处于第四位到第七位的雇员
SELECT t1.* FROM   
       (SELECT e.*,row_number() over(ORDER BY nvl(sal, 0) + nvl(comm, 0) DESC) grade FROM   emp e) t1 
       WHERE  t1.grade BETWEEN 4 AND 7;
select t.*,rownum from
       (select * from emp order by nvl(sal, 0) + nvl(comm, 0) desc) t
       where  rownum <= 7
       minus
       select t.*,rownum from
       (select * from emp order by nvl(sal, 0) + nvl(comm, 0) desc) t
       where  rownum < 4;
select * from (
       select t.*, rownum rwn  from emp t where rownum <= 7 order by nvl(sal, 0) + nvl(comm, 0) desc
       ) where rwn >= 4;