oracle 数据库 练习题4

一、数据库对象

1.为表vendor_master(vid,vname, VENADD1, VENADD2, VENADD3)创建一个视图，该视图将VENADD1、VENADD2和VENADD3(地址的三部分，都为varchar2性) 3个列连接起来组成名为VENADDRES的列.

2.创建名为“my_seq”的序列,该序列的起始值为1000,并在每次查询时增加10,直到该序列达到1100,然后重新从1000开始.

3.在表order_detail(oid,orderno,venderid, itemcode,customerid,customaddr)表的orderno和itemcode列上创建一个唯一组合索引.

--1.为表vendor_master(vid,vname, VENADD1, VENADD2, VENADD3)创建一个视图，--该视图将VENADD1、VENADD2和VENADD3(地址的三部分，都为varchar2性) 3个列连接起来组成名为VENADDRES的列.CREATE TABLE VENDOR_MASTER(Vid NUMBER(4) PRIMARY KEY,Vname VARCHAR2(20) NOT NULL,VENADD1 VARCHAR2(20) NOT NULL,VENADD2 VARCHAR2(20) NOT NULL,VENADD3 VARCHAR2(20) NOT NULL);--创建视图CREATE VIEW VENADD5 ASSELECT "CONCAT"("CONCAT"(VENADD1,VENADD2),VENADD3) AS VENADDRESFROM VENDOR_MASTER;SELECT * FROM VENADD5;--2.创建名为“my_seq”的序列,该序列的起始值为1000,并在每次查询时增加10,直到该序列达到1100,然后重新从1000开始.CREATE SEQUENCE my_seqSTART WITH 1000INCREMENT BY 10MAXVALUE 1100CYCLE;--3.在表order_detail(oid,orderno,venderid, itemcode,customerid,customaddr)表的orderno和itemcode列上创建一个唯一组合索引.CREATE TABLE ORDER_DETAIL(oid NUMBER(4) NOT NULL,orderno NUMBER(4) NOT NULL,venderid NUMBER(4) NOT NULL,itemcode NUMBER(4),customerid NUMBER(4) NOT NULL,customaddr VARCHAR2(20) );CREATE UNIQUE INDEX XON ORDER_DETAIL(orderno,itemcode);

二、使用如下表

emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)

dept部门表(deptno部门编号/dname部门名称/loc地点)

尽量用视图完成以下查询

1、列出月薪比 "BLAKE"少的所有雇员

2、列出至少有一个雇员的部门详细信息

3、列出所有雇员的姓名及其直接上级的姓名。

4、列出入职日期早于其直接上级的所有雇员

5、列出没有雇员的部门信息

6、列出所有“CLERK”（办事员）的姓名及其部门名称

7、列出最低薪金大于1500的工作类别信息

8、列出月薪高于公司平均水平的所有雇员

9、列出与“SCOTT”从事相同工作的所有雇员

10、列出某些雇员的姓名和薪金，条件是他们的月薪高于部门30中所有雇员的薪金

11、列出每个部门的信息以及该部门中雇员的数量--

12、列出所有雇员的雇员名称、部门名称和月薪

13、列出各个部门的MANAGER（经理）的最低薪金

14、列出所有雇员的年薪，并且按年薪排序

15、列出薪金水平处于第四位到第七位的雇员

-- 创建总的视图，将两个表合并在一个视图中CREATE VIEW VEMP ASSELECT EMP.*,DEPT.DNAME,DEPT.LOC FROM DEPT,EMPWHERE DEPT.DEPTNO=EMP.DEPTNO;--1、列出月薪比 "BLAKE" 少的所有雇员SELECT EMPNO,ENAMEFROM VEMPWHERE SAL<(SELECT SAL FROM VEMP WHERE ENAME='BLAKE');--2、列出至少有一个雇员的部门详细信息SELECT DISTINCT DEPTNO,DNAME,LOCFROM VEMPWHERE DEPTNO IN(SELECT DEPTNOFROM VEMP GROUP  BY DEPTNOHAVING "COUNT"(DEPTNO)>0);--3、列出所有雇员的姓名及其直接上级的姓名。SELECT V.ENAME,W.ENAMEFROM VEMP V LEFT JOIN VEMP WON W.EMPNO=V.MGR;--4、列出入职日期早于其直接上级的所有雇员SELECT V.ENAME,W.ENAMEFROM VEMP V LEFT JOIN VEMP WON W.EMPNO=V.MGRWHERE V.HIREDATE<W.HIREDATE;--5、列出没有雇员的部门信息SELECT DISTINCT DEPTNO,DNAME,LOCFROM DEPTWHERE DEPTNO NOT IN(SELECT DEPTNOFROM VEMP GROUP  BY DEPTNOHAVING "COUNT"(DEPTNO)>0);--6、列出所有“CLERK”（办事员）的姓名及其部门名称-SELECT ENAME,DNAMEFROM VEMPWHERE JOB='CLERK';--7、列出最低薪金大于1500的工作类别信息SELECT JOB,DNAMEFROM VEMPWHERE SAL>1500;--8、列出月薪高于公司平均水平的所有雇员SELECT EMPNO,ENAMEFROM VEMPWHERE SAL>(SELECT "AVG"(SAL) FROM VEMP);--9、列出与“SCOTT”从事相同工作的所有雇员SELECT EMPNO,ENAMEFROM VEMPWHERE JOB=(SELECT JOB FROM VEMP WHERE ENAME='SCOTT') AND ENAME!='SCOTT';--10、列出某些雇员的姓名和薪金，条件是他们的月薪高于部门30中所有雇员的薪金SELECT ENAME ,SALFROM VEMPWHERE SAL>(SELECT "MAX"(SAL) FROM VEMP WHERE DEPTNO=30);--11、列出每个部门的信息以及该部门中雇员的数量SELECT DEPT.*,(SELECT "COUNT"(*) FROM VEMP W WHERE DEPT.DEPTNO=W.DEPTNO)FROM DEPT;--12、列出所有雇员的雇员名称、部门名称和月薪SELECT ENAME,DNAME,SAL+"NVL"(COMM,0) MONTHSALFROM VEMP;--13、列出各个部门的MANAGER（经理）的最低薪金SELECT DEPTNO,"MIN"(SAL)FROM VEMPWHERE "JOB"='MANAGER'GROUP BY DEPTNO;--14、列出所有雇员的年薪，并且按年薪排序SELECT ENAME,(SAL+"NVL"(COMM,0))*12 YEARSALFROM VEMPORDER BY YEARSAL ASC;--15、列出薪金水平处于第四位到第七位的雇员SELECT *FROM (SELECT V.*,ROW_NUMBER() OVER(ORDER BY SAL)RANK FROM VEMP V ) YEARAWHERE YEARA."RANK" BETWEEN 4 AND 7; 

总结：这里学的是视图的概念，所以使用的是视图来进行查询。但是自我感觉还是有点缺陷，就是用两个表来创建视图的时候，DEPT表中有一个DEPTNO属性和EMP表进行连接，可是问题是，DEPT中含有的一个DEPTNO，而EMP表中没有，这就导致了整个表并不完整。当查询各个部门的时候就会出现缺陷，这是一个问题，暂时未解决。