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

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关键字。

--说明：student 有m条记录，class有n条记录数，两表有t条记录的id匹配.注意：比如Student表有一条记录的id为4，在class表中有10条id为4的记录，则至少有10条记录匹配。所以，即使是内连接，得到的记录也可能大于两表记录之和
(1)--内连接：join，inner join
--内连接：只返回两表连接列的匹配项
--以下三种查询结果一样。
--显示t条记录
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的结果一样了）
--以下两种查询结果一样
--显示m*n条记录

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


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

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）右外连接
--显示n条记录
select * from student s right join class c on s.classid=c.id;

（5）全外连接：full join（完全外连接） （在右边表最多只有一个符合条件时，记录数为：max in (M ,N)条记录到M+N条记录之间）
  产生M+N的结果集并列出两表的全部的，不符合条件的，以空值代替
--显示m+n-t条记录
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