约束+典型的查询+几种常见的联结

1.约束 
alter table student 
  add constraint pk_stu_stuId primary key(stuId) 


alter table student 
  add constraint ck_stu_stuAge  check(stuAge>0) 
  
alter table student 
  add constraint uq_stu_stuName  unique(stuName) 


alter table student 
  add constraint fk_stu_stuCid  foreign key(stuCid) references   class(cid) 
  
2查询简述 
除了cross join 不可以加on外，其他的join连接必须加上on关键字。 

(1)--内连接：join，inner join 
--内连接：只返回两表连接列的匹配项 
--以下三种查询结果一样。 
select * from student s inner join  class c on s.classid=c.id; 
select * from student s       join  class c on s.classid=c.id;  
select * from student s ,class c where s.classid=c.id;          

(2)笛卡尔乘积：cross join 
笛卡尔乘积的连接：不加任何条件时，得到M*N的结果集（注：如果在cross  join 后面加where条件，就会与inner join  on的结果一样了） 
--以下两种查询结果一样 

select * from student s cross join class c; 
select * from student ,class; 


(3)左外连接：left join 
左外连接：列出左边的表的全部，以及右边表符合条件的，不符合条件的以空值代替。 
注：下面两个结果一样。哪个带加号，即哪个需要条件符合，下面的是左连接。 


select * from student s left join class c on s.classid=c.id; 
select * from student s,class c where s.classid=c.id(+); 

（4）右外连接 


（5）全外连接：full join（完全外连接） （在右边表最多只有一个符合条件时，记录数为：max in (M ,N)条记录到M+N条记录之间） 
  产生M+N的结果集并列出两表的全部的，不符合条件的，以空值代替 
select * from student s full join class c on  s.classid=c.id 
. 







3.查询（19个经典查询） 

select * from ( 
  select emp.*,rownum as rn  from emp  where   rownum<=10  order by sal desc 
)b where b.rn>=5 

--2. 查询各部门工资最高的员工信息 
select * from emp; 

select *  from dept; 

select *　from emp where (deptno,sal) in ( 
select deptno, max(sal)　 from emp group by  deptno 
) 

--3查询每个部门工资最高的前2 名员工  
--此题十分经典  ！！！！！！！！！！！！！！！！！！！！！！！！！！ 
select * from emp a  where (select count(*) from emp where  sal>a.sal and deptno=a.deptno ) <2 
order by   a.deptno 


--4. 查询出有3 个以上下属的员工信息 
  select * from emp where empno in( 
    select b.empno from emp a inner join emp b on a.mgr=b.empno group by  b.empno having count(*)>=3 
  ) 
  
--5. 查询所有大于本部门平均工资的员工信息() 
  select * from emp  a  where sal > (select avg(sal) from emp where deptno=a.deptno) 


--6. 查询平均工资最高的部门信息 
select * from dept where deptno in( 
select deptno from emp group by deptno  having avg(sal)>=all ( 
  select avg(sal) from emp group by deptno 
) 
) 

--7. 查询大于各部门总工资的平均值的部门信息 
select * from dept where deptno in ( 
select deptno from emp  group by deptno having avg(sal)>(select avg(sal) from emp ) 
) 

--8. 查询大于各部门总工资的平均值的部门下的员工信息 
select * from emp where sal >(  select avg(sal) from emp) 

--9、查询出工资成本最高的部门的部门号和部门名称 
select deptno,dname from dept where deptno in ( 
    select deptno from emp group by deptno  having sum(sal)>=all( 
     select sum(sal) from emp group by deptno 
  ) 
) 


--10、查询出工资不超过2500的人数最多的部门名称 
SELECT dname FROM DEPT WHERE DEPTNO IN ( 
  select deptno from emp where sal<=2500 group by deptno  HAVING COUNT(*)>=ALL ( 
  select count(*) from emp where sal<=2500 group by deptno 
) 
) 


--11、查询出没有下属员工的人的名字和他的职位 
select ename,job from emp where empno not in ( 
    select distinct  a.mgr from emp a,emp b where  a.mgr is not null 
) 

--12、查询出人数最多的那个部门的部门编号和部门名称 
select deptno,dname from dept where deptno in ( 
select deptno from emp  group by deptno  having count(*) >=all ( 
select count(*) from emp  group by deptno 
) 
) 


--13、查询出没有员工的那个部门的部门编号和部门名称(要求用两种方法,其中一种要用集合运算) 
--方法一 
select deptno,dname　 from  dept where deptno not in ( 
select distinct deptno from emp  
)　 
--方法二 

select deptno,dname　 from  dept where   deptno in ( 
select distinct  deptno from dept  --所有的部门号 
minus 
select distinct deptno from emp   --存在的 
) 


--14、查询出员工名字以A打头的人数最多的部门名称和员工名字 


select b.dname,a.ename from emp  a inner join dept b on  a.ename like 'A%' and a.deptno =b.deptno and a.deptno in ( 
select deptno  from emp where  ename like 'A%' group  by deptno having count(*) >=all( 
select count(*)  from emp where  ename like 'A%' group  by deptno 
) 
) 




--15、现在公司要给员工增加工龄工资,规则是:30*工作年限，请按以下格式显示下面结果:   
      部门名称 员工姓名 原工资 增加额度 新工资 
select b.dname 部门名称,a.ename 员工姓名,a.sal 原工资, 
floor( months_between(sysdate,hiredate)/12)*30 增加额度, 
floor( months_between(sysdate,hiredate)/12)*30+a.sal 新工资  from emp a  
  inner join  dept b on a.deptno =b.deptno; 

--16、针对DEPT和EMP表，查询出下面格式的结果并要求按部门编号和工资降序排列。 
      部门名称  员工姓名  工资 
      select b.dname,a.ename,a.sal from 
       emp a left  join dept b on   a.deptno=b.deptno order by a.deptno desc,a.sal desc; 
      

--17、针对DEPT和EMP表，查询出下面格式的结果。 
      部门编号  部门名称  部门工资最小值  部门工资最大值  部门工资平均值  部门工资合计值 
     select b.deptno,b.dname,min(sal),max(sal),avg(sal),sum(sal) 
      from emp a inner join dept b on a.deptno=b.deptno group by b.deptno,b.dname 

--18、针对DEPT和EMP表，查询出SMITH所在部门的部门名称、部门工资平均值。（要求使用子查询） 
select a.dname,b.avgsal from  dept a inner join ( 
  select deptno,avg(sal) avgsal  from emp where deptno in( 
   select deptno from emp where ename='SMITH' 
)group by deptno 
)b on a.deptno =b.deptno 


--19、针对DEPT和EMP表，查询出下面格式的结果。（要求使用外连接，没有员工的部门名也要显示。 员工姓名如果是空值，要求用“不存在”代替；如果工资是空值，要求用0代替。）  
      部门名称  员工姓名  工资 
      
      select a.dname, NVL( b.ename,'不存在') ,NVL(b.sal,0) from dept a  full join emp b on a.deptno = b.deptno