oracle 多表查询联系

1.查询30号部门员工的job和10号部门的loc
2.选择所有有奖金的员工的name , 部门名称 , location
3.查询每个部门的最低工资
4.显示所有部门在"NEW YORK"(dept表 loc字段)的员工姓名
5. 显示员工"SCOTT的姓名，部门名称
6. 显示员工姓名，部门名称，工资，工资级别(salgrade表 grade字段),要求工资级别大于4级
7. 显示参加工作时间比他的经理早员工名，参加工作时间，经理名，参加工作时间
8.列出至少有一个员工的所有部门
9.查询部门名和部门所拥有的员工信息
10.查询员工信息及其部门名

--1.查询30号部门员工的job和10号部门的loc
--union 和 nuion 均可以合并多个select结果集
-- union 默认选取不同值，不含重复
select e.job 
from emp e, dept d 
where e.deptno = d.deptno
and d.deptno = 30
union
select loc
from dept
where deptno = 10;
-- union all 允许重复
select e.job 
from emp e, dept d 
where e.deptno = d.deptno
and d.deptno = 30
union all
select loc
from dept
where deptno = 10;

--2.选择所有有奖金的员工的name , 部门名称 , location
-- is not
select e.ename, d.dname, d.loc as "location"
from emp e, dept d
where e.deptno = d.deptno
and comm is not null;
-- and not
select e.ename, d.dname, d.loc as "location"
from emp e, dept d
where e.deptno = d.deptno
and not comm is null;

select e.ename, d.dname, d.loc as "location"
from emp e, dept d
where e.deptno = d.deptno
and nvl(comm, 0);

--3.查询每个部门的最低工资
-- group by
select min(sal)
from emp
group by deptno;

--4.显示所有部门在"NEW YORK"(dept表 loc字段)的员工姓名
-- like
select e.ename
from emp e, dept d
where e.deptno = d.deptno
and d.loc like 'NEW YORK';
-- =
select e.ename
from emp e, dept d
where e.deptno = d.deptno
and d.loc = 'NEW YORK';

--5. 显示员工"SCOTT的姓名，部门名称
select e.ename, d.dname
from emp e, dept d
where e.deptno = d.deptno
and e.ename = 'SCOTT';

--6. 显示员工姓名，部门名称，工资，工资级别(salgrade表 grade字段),要求工资级别大于4级
-- 两表连接
select e.ename, d.dname, e.sal, s.grade 
from emp e, dept d, salgrade s 
where e.deptno = d.deptno 
and e.sal between s.losal and s.hisal and s.grade >4;

--7. 显示参加工作时间比他的经理早员工名，参加工作时间，经理名，参加工作时间
-- 内连接
select e1.ename, e1.hiredate, e2.ename, e2.hiredate 
from emp e1
inner join emp e2
on e1.mgr = e2.empno
where e1.hiredate < e2.hiredate;
-- 自连接
select e1.ename, e1.hiredate, e2.ename, e2.hiredate 
from emp e1, emp e2
where e1.mgr = e2.empno
and e1.hiredate < e2.hiredate;

--8.列出至少有一个员工的所有部门
-- group by deptno
select * from dept
where deptno in(
  select deptno
  from emp
  group by deptno
  having count(deptno) >= 1
);
-- count(1)
select * from dept
where deptno in(
  select deptno
  from emp
  group by deptno
  having count(1) >= 1
);
-- count(*)
select * from dept
where deptno in(
    select deptno
    from emp
    group by deptno
    having count(*) >= 1
);
--in
select * from dept
where deptno in(
      select deptno from emp
);
--9.查询部门名和部门所拥有的员工信息；
-- left join
select dname, ename
from dept left join emp 
on dept.deptno = emp.deptno;
-- + 
select dname, ename
from dept, emp 
where dept.deptno = emp.deptno(+);

--10.查询员工及其部门名。
-- right join
select emp.*, dname
from emp right join dept
on dept.deptno = emp.deptno;
-- +
select emp.*, dname
from emp, dept
where emp.deptno(+) = dept.deptno;