oracle查询

1.-- 奖金为0也算有奖金
这个公司里面有奖金的人的数量
select count(comm) from emp ;
select count(*) from emp where comm is not null;
select avg(comm) from emp;
select sum(comm)/count(*) from emp;

2.计算工种数量
select count(distinct job) from emp;
select sum(sal) , sum(distinct sal)
from emp;

3.求公司每个工种人数
select 
    job , count(*)
from emp
where job is not null
group by job

4.求每个年度入职的员工人数
投影列 必须是 聚合列，分组条件列
select 
      to_char(hiredate,'yyyy'),count(*)
from emp 
    group by to_char(hiredate,'yyyy')
    
5.求每个部门每年入职的员工人数
10   1980   3
10   1981   2
20   1995   1
select 
    deptno , to_char(hiredate,'yyyy') , count(*) 
from emp
where deptno is not null and 
    hiredate is not null
group by
    deptno , to_char( hiredate , 'yyyy' )
order by     
    deptno , to_char( hiredate , 'yyyy' )
    
6.求入职人数不足5人的年度
（哪一些年度入职的员工还不足5个人）
select
    to_char(hiredate , 'yyyy') , count(*)
from 
    emp
where 
    hiredate is not null
group by     to_char(hiredate , 'yyyy') 
having count(*) < 5

7.等值查询（乘操作上做的等值过滤）
查询所有有部门的员工信息
员工编号  员工姓名  部门编号 部门名称
EMP * DEPT
select 
    *
from emp , dept 
where emp.deptno = dept.deptno

8.查询出所有员工信息
员工编号  员工姓名 领导编号 领导姓名
select
    e1.empno , e1.ename , e1.mgr , e2.ename 
from 
    emp e1 , emp e2
where 
    e1.mgr = e2.empno
    
9.create table sc
(
    sid number(4),
    cid number(4),
    score number(4,1),
    primary key ( sid , cid )
)

insert into sc values ( 1001 , 1 , 90 ); 
insert into sc values ( 1001 , 2 , 80 );
insert into sc values ( 1002 , 2 , 50 );
insert into sc values ( 1003 , 1 , 90 );
insert into sc values ( 1003 , 3 , 100 );

commit;

11.select * from sc;
求既修了课程1 又修了课程2的学生编号
select * from 
sc sc1 , sc sc2 
where sc1.sid = sc2.sid 
and sc1.cid = 1 and sc2.cid = 2

12.内连接 ==> 等效于等值连接
select 
    *
from emp , dept 
where emp.deptno = dept.deptno

select 
    *
from emp inner join dept 
on emp.deptno = dept.deptno

左外连接的撰写
求所有的员工信息
select 
    emp.empno,emp.ename,emp.deptno,dept.dname
from emp left outer join dept 
on  emp.deptno = dept.deptno

select 
    emp.empno,emp.ename,emp.deptno,dept.dname
from emp , dept 
where  emp.deptno = dept.deptno(+)

右外连接
select 
    emp.empno,emp.ename,emp.deptno,dept.dname
from emp , dept 
where  emp.deptno(+) = dept.deptno

select 
    emp.empno,emp.ename,emp.deptno,dept.dname
from emp right outer join dept 
on  emp.deptno = dept.deptno

13.求每个部门的员工人数
部门编号  部门名称  部门的员工人数
select 
    dept.deptno , dname , count(emp.empno)
from dept left outer join emp 
on emp.deptno = dept.deptno 
group by 
    dept.deptno , dname
    
select 
    dept.deptno , dname , emp.empno , emp.ename,
    loc
from dept left outer join emp 
on emp.deptno = dept.deptno and loc = 'NEW YORK'

select 
    dept.deptno , dname , emp.empno , emp.ename,
    loc
from dept left outer join emp 
on emp.deptno = dept.deptno where  loc = '纽约'

14.子查询
SELECT * FROM EMP 
WHERE EMPNO IN (7839 , 9999 , 1001 , 1002)

15.求部门在NEW YORK的员工信息
SELECT * FROM DEPT WHERE LOC = 'NEW YORK' =>     10 , 20
SELECT * FROM EMP WHERE DEPTNO IN (
    SELECT DEPTNO FROM DEPT WHERE LOC = 'NEW YORK'
)    

16.求部门名称是 ACCOUNTING 这个部门的员工信息
DNAME数据不能重复
SELECT DEPTNO FROM DEPT WHERE DNAME = 'ACCOUNTING'
SELECT * FROM EMP WHERE DEPTNO = (
    SELECT DEPTNO FROM DEPT WHERE DNAME =     'ACCOUNTING'
)

> < >= <= != 

select sal from emp where empno = 7566  --2975
select * from emp 
where sal > (select sal from emp where empno = 7566)

select * from emp 
where sal <ALL(
    select sal from emp 
    where deptno = 20
)

select * from emp 
where sal >ANY(
    select sal from emp 
    where deptno = 20
)

17.EXISTS 存在
select * from emp 
where EXISTS ( select * from dept where 1=0 )

18.求所有有部门的员工
select 
    *
from emp 
where EXISTS ( select     * from dept where emp.DEPTNO = dept.deptno
)