Oracle 多表连接

多表连接

--第五章 1.
SELECT * FROM EMP;

1.写一个查询，显示所有员工姓名，部门编号，部门名称。
SELECT e.ENAME ,e.DEPTNO ,d.DNAME
FROM emp e,DEPT d
WHERE e.DEPTNO = d.DEPTNO;

SELECT e.ENAME ,e.DEPTNO ,d.DNAME
FROM emp e
INNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO;


2.写一个查询，显示所有工作在CHICAGO并且奖金不为空的员工姓名，工作地点，奖金
select emp.ename,dept.loc,emp.comm
from emp , dept
where emp.comm <> 0 AND dept.deptno=emp.deptno;

select emp.ename,dept.loc,emp.comm
from emp join dept on dept.deptno=emp.deptno
where emp.comm <> 0;




3.写一个查询，显示所有姓名中含有A字符的员工姓名，工作地点。
select emp.ename,dept.loc
from emp join dept on dept.deptno=emp.deptno
where emp.ename like '%A%';
练习2
1.查询每个员工的编号，姓名，工资，工资等级，所在工作城市，按照工资等级进行升序排序。
--工资登记表：salgrade
SELECT * from SALGRADE;
SELECT e.EMPNO,e.ename, e.sal,s.grade,d.loc
FROM EMP e,DEPT d,SALGRADE s
WHERE e.DEPTNO = d.DEPTNO
AND SAL BETWEEN s.losal AND s.hisal
ORDER BY s.grade ASC;


select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.loc
from emp join dept on dept.deptno=emp.deptno,salgrade
order by salgrade.grade asc;


练习3
1.查询所有工作在NEW YORK和CHICAGO的员工姓名，员工编号，以及他们的经理姓名，经理编号。
SELECT e1.ENAME 员工姓名 ,e1.empno 员工编号,e2.Ename 经理姓名,e1.MGR 经理编号,d.loc 工作地点
FROM EMP e1 ,EMP e2,DEPT d
WHERE E1.MGR = E2.EMPNO
AND E1.DEPTNO = d.DEPTNO
AND d.LOC IN ('NEW YORK','CHICAGO');

2.第上一题的基础上，添加没有经理的员工King，并按照员工编号排序。
SELECT e1.ENAME 员工姓名 ,e1.empno 员工编号,e2.Ename 经理姓名,e1.MGR 经理编号,d.loc 工作地点
FROM EMP e1 LEFT JOIN EMP e2 ON E1.MGR = E2.EMPNO ,DEPT d
WHERE
E1.DEPTNO = d.DEPTNO
AND d.LOC IN ('NEW YORK','CHICAGO')
order BY 2 ASC;

SELECT e1.ENAME 员工姓名 ,e1.empno 员工编号,e2.Ename 经理姓名,e1.MGR 经理编号,d.loc 工作地点
FROM EMP e1 ,EMP e2,DEPT d
WHERE E1.MGR = E2.EMPNO(+)
AND E1.DEPTNO = d.DEPTNO
AND d.LOC IN ('NEW YORK','CHICAGO')
order BY 2 ASC;

3.查询所有员工编号，姓名，部门名称，包括没有员工的部门也要显示出来。
SELECT e.empno,e.ename,d.dname 部门名称
from EMP e , DEPT d
WHERE e.deptno(+) = d.deptno ;

select e.empno,e.ename,d.deptno
from emp e
full outer join dept d
on e.deptno=d.deptno;


练习4
• 1.创建个员工表和部门表的交叉连接
select emp.empno,emp.ename,dept.dname from emp ,dept;
select emp.empno,emp.ename,dept.dname from emp CROSS JOIN dept;


• 2.使用自然连接，显示入职日期在80年5月1日之后的员工姓名，部门名称，入职日期
SELECT e.ename,d.dname,e.hiredate
FROM emp e INNER JOIN DEPT d on e.deptno = d.deptno
WHERE e.hiredate>'1-5月-80';

SELECT e.ename,d.dname,e.hiredate
FROM emp e
NATURAL JOIN dept d
where e.hiredate > '1-5月-80';


• 3.使用USING子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点
SELECT e.ENAME ,d.dname ,d.loc
FROM emp e join DEPT d using (DEPTNO)
where d.LOC = 'CHICAGO';

• 4.使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级
SELECT e.ename "员工姓名",dname "部门名称",e.sal "工资",s.grade "工资级别"
FROM emp e join dept d on (e.deptno=d.deptno) ,salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

SELECT e.ename "员工姓名",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;

• 5.使用左连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
SELECT e.ename,m.ename "经理名字"
from emp e
LEFT OUTER JOIN emp m
ON (e.mgr=m.EMPNO);

• 6.使用右连接，查询每个员工的姓名，经理姓名，没有经理的King也要显示出来。
SELECT e.ename,m.ename "经理名字"
FROM emp m
right outer join emp e
on m.mgr=e.empno;


课后作业
• 1.显示员工SMITH的姓名，部门名称，直接上级名称
SELECT e.ENAME,d.dname,m.ename
FROM emp e
join dept d using (deptno)
join emp m on (e.mgr=m.empno);

SELECT e.ename,d.dname,m.ename
FROM emp e
join dept d on e.deptno=d.deptno
join emp m on e.mgr=m.empno;

SELECT e.ename,d.dname,m.ename
FROM emp e,DEPT d,emp m
where e.deptno=d.deptno
AND e.mgr=m.empno;

• 2.显示员工姓名，部门名称，工资，工资级别，要求工资级别大于4级。
SELECT e.ename "员工姓名",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


• 3.显示员工KING和FORD管理的员工姓名及其经理姓名。
方法一：
SELECT e.ename "员工姓名",m.ename "经理姓名"
FROM emp e,emp m
WHERE e.mgr=m.empno
AND m.ename IN('KING','FORD');
方法二：
SELECT e.ename "员工姓名",m.ename "经理姓名"
FROM emp e,emp m
WHERE e.mgr=m.empno AND m.ename ='KING'
or e.mgr=m.empno and m.ename ='FORD';


• 4.显示员工姓名，参加工作时间，经理名，参加工作时间，要求参加时间比经理早。
SELECT e.ename "员工姓名", e.hiredate "参加工作时间" ,m.ename "经理姓名", m.hiredate "经理参加工作时间"
FROM emp e,emp m
WHERE e.mgr=m.empno
AND e.hiredate< m.hiredate;