sql练习2

  --创建视图
CREATE TABLE vendor_master(
VID int NOT NULL PRIMARY KEY,
VNAME VARCHAR2(8)NOT NULL,
VENADD1 VARCHAR2(8) NOT NULL,
VENADD2 VARCHAR2(8) NOT NULL,
VENADD3 VARCHAR2(8) NOT NULL
);
CREATE VIEW VENADDRES
AS
SELECT v.VENADD1,v.VENADD2,v.VENADD3 
FROM vendor_master v;




SELECT * FROM VENADDRES;
--创建序列
CREATE SEQUENCE SEQ_my_seq
START WITH 1000
INCREMENT BY 10
MAXVALUE 1100
MINVALUE 1000
CYCLE
NOCACHE;
select SEQ_my_seq.nextval from dual;


--创建索引
CREATE TABLE order_detail(
OID int NOT NULL PRIMARY KEY,
ORDERNO VARCHAR2(8)NOT NULL,
ITEMCODE VARCHAR2(8) NOT NULL,
CUSTOMERID VARCHAR2(8) NOT NULL,
CUSTOMADDR VARCHAR2(8) NOT NULL
);
CREATE UNIQUE INDEX x_ord ON order_detail(orderno,itemcode)
--TABLESPACE INDEX_;




















--创建视图
CREATE VIEW v
AS SELECT  d. DEPTNO,d.DNAME,d.loc,
e.EMPNO,e.ENAME,e. JOB,e.MGR,e.HIREDATE,e. SAL,e.COMM
FROM EMP e,DEPT d
WHERE e.DEPTNO=d.DEPTNO;


--1、列出月薪比 "BLAKE" 少的所有雇员
SELECT ENAME from v where SAL<(SELECT SAL from v WHERE ENAME='BLAKE');
--2、列出至少有一个雇员的部门详细信息
SELECT V.DEPTNO,V.DNAME,V.loc FROM V v WHERE (SELECT COUNT(V.DEPTNO)FROM V)>0;
--3、列出所有雇员的姓名及其直接上级的姓名  
SELECT a.ename,b.ename from v a,v b where a.mgr=b.empno;
--4、列出入职日期早于其直接上级的所有雇员
SELECT a.ename from v a,v b where a.mgr=b.empno and a.hiredate < b.hiredate;
--5、列出没有雇员的部门信息 
--select VT.DEPTNO,VT.DNAME,VT.loc from v_temp vt where deptno not in (select distinct deptno from v_temp);
--SELECT a.DNAME,a.DEPTNO FROM V_TEMP a WHERE (SELECT COUNT(VT.DEPTNO) FROM V_TEMP VT)=0;
select distinct dname from dept where deptno not in (select distinct deptno from emp);
--6、列出所有“CLERK”（办事员）的姓名及其部门名称  
select v.ename,v.job from v v where v.job='CLERK';
--7、列出各种工作类别中月薪大于1500的最低薪金
select job ,min(sal) from v group by job having min(sal) > 1500;
--8、列出月薪高于公司平均水平的所有雇员
 select ename,sal+nvl(comm,0) "月薪"  from v where (sal+nvl(comm,0)) > (select avg(sal+nvl(comm,0)) from v);
--9列出与“SCOTT”从事相同工作的所有雇员
SELECT ename from v where job in(SELECT job from v where ename='SCOTT');
--10、列出某些雇员的姓名和薪金，
select ename, sal from v where sal  > (select max(sal)  from v where deptno = 30);
--11、列出每个部门的信息以及该部门中雇员的数量--
SELECT a. dname,a.deptno,COUNT (a.ename) FROM V a,V b WHERE a.deptno = b.deptno (+) GROUP BY a.dname,a.deptno;
--12、列出所有雇员的雇员名称、部门名称和月薪
SELECT ENAME,DNAME,sal+nvl(comm,0)"月薪" FROM V ;
--13、列出各个部门的MANAGER（经理）的最低薪金
SELECT dname,MIN(sal) FROM V  WHERE JOB='MANAGER' GROUP BY DNAME;
--14、列出所有雇员的年薪，并且按年薪排序
SELECT  ENAME,SAL*12 FROM V  ORDER BY (SAL*12);
--15、列出薪金水平处于第四位到第七位的雇员
Select * from (Select ename,sal, rank() over (order by sal desc) as grade from V ) where grade between 4 and 7;