--为表vendor_master创建一个视图，该视图将VENADD1、VENADD2和VENADD3(地址的三部分，都为varchar2性) 3个列连接起来组成名为VENADDRES的列.
create view VENADDRES as
select VENADD1 from ndor_master union all
select VENADD1 from ndor_master union all
select VENADD1 from ndor_master
--.创建名为“my_seq”的序列,该序列的起始值为1000,并在每次查询时增加10,直到该序列达到1100,然后重新从1000开始.
drop sequence my_seq
create sequence my_seq
start with 1000
increment by 10
maxvalue 1100
minvalue 1000
cycle
nocache
select my_seq.nextval from dual
--在表order_detail表的orderno和itemcode列上创建一个唯一组合索引.
create unique index in_order on order_detail(orderno,itemcode)
create unique index in_emp on emp(empno,ename)
drop index in_emp
--1列出月薪比 "BLAKE" 少的所有雇员
select ename ,sal from emp where sal < (select sal from emp where ename='BLAKE')
--列出至少有一个雇员的部门详细信息
select distinct dname from dept where deptno in (select distinct deptno from emp)
--列出所有雇员的姓名及其直接上级的姓名
select e.ename , d.ename nanager from emp e,emp d where e.mgr=d.empno(+)
--列出入职日期早于其直接上级的所有雇员
select e.ename from emp e ,emp d where e.mgr=d.empno and e.hiredate < d.hiredate
--列出没有雇员的部门信息
select distinct dname from dept where deptno not in (select distinct deptno from emp)
--列出所有“CLERK”（办事员）的姓名及其部门名称
select ename,dname, job from emp ,dept where emp.deptno = dept.deptno and emp.job=upper('clerk');
--列出最低薪金大于1500的工作类别信息
select job ,min(sal) from emp group by job having min(sal) > 1500
--列出月薪高于公司平均水平的所有雇员
select ename,sal+nvl(comm,0) salary from emp where (sal+nvl(comm,0)) > (select avg(sal+nvl(comm,0)) from emp)
--列出与“SCOTT”从事相同工作的所有雇员
select ename from emp where job = (select job from emp where ename='SCOTT')
--列出某些雇员的姓名和薪金，条件是他们的月薪高于部门30中所有雇员的薪金
select ename, sal from emp where sal > all(select sal from emp where deptno = 30)
select ename, sal from emp where sal > (select max(sal) from emp where deptno = 30)
--列出每个部门的信息以及该部门中雇员的数量
select dname,d.deptno, count(ename) from emp e,dept d where d.deptno = e.deptno(+) group by dname,d.deptno
--列出所有雇员的雇员名称、部门名称和月薪
select ename,sal+nvl(comm,0) salary ,dname from emp left join dept on emp.deptno = dept.deptno
--列出各个部门的MANAGER（经理）的最低薪金
select dname,min(sal) from emp e join dept d on e.job='MANAGER' and e.deptno = d.deptno group by dname
--列出所有雇员的年薪，并且按年薪排序
select ename ,(sal+nvl(comm,0))*12 salary from emp order by salary
--列出薪金水平处于第四位到第七位的雇员
Select * from (Select ename,sal, rank() over (order by sal desc) as grade from emp) where grade between 4 and 10