一、多表查询 SELECT 列名,列名... FROM 表名 表的别名,表名 表的别名.... WHERE 条件 ORDER BY 排序字段。。。  1、笛卡尔积：是多张表记录的乘积。 SELECT * FROM emp,dept WHERE ename='SMITH';  SELECT * FROM emp; SELECT * FROM dept;  SELECT ename,emp.deptno,dname FROM emp,dept;  2、等值连接，两张表中存在含义相同，值相等的列的  SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno;   SELECT a.deptno,ename,dname FROM emp a,dept b WHERE A.deptno=b.deptno ORDER BY A.deptno;  3、不等值连接 SELECT * FROM salgrade;  SELECT * FROM emp A,salgrade b --WHERE a.sal >=b.losal and a.sal2;

---查询各个部门的平均工资大于2000的部门编号和平均工资SELECT deptno,avg(sal)FROM empGROUP BY deptnoHAVING avg(sal)>2000;

where与having的区别：WHERE是在分组前进行过滤的，where的后面不能直接使用分组函数做比较HAVING是在分组后进行过滤的。

3、分组函数的嵌套---查询部门平均工资最高的平均工资SELECT MAX(avg(sal))FROM empGROUP BY deptno;

注意：当select后面的分组函数嵌套使用时，SELECT后面不能有任意列名，只能存在嵌套分组函数

第六章课后作业：1.查询部门平均工资在2500元以上的部门名称及平均工资。SELECT dname,avg(sal)FROM emp,deptWHERE emp.deptno=dept.deptnoGROUP BY dnameHAVING avg(sal)>2500;2.查询员工岗位中不是以“SA”开头并且平均工资在2500元以上的岗位及平均工资，并按平均工资降序排序。SELECT JOB,avg(sal)FROM empWHERE upper(JOB) NOT LIKE 'SA%'GROUP BY JOBHAVING avg(sal)>2500ORDER BY 2 desc;3.查询部门人数在2人以上的部门名称、最低工资、最高工资,并对求得的工资进行四舍五入到整数位。SELECT dname,round(MIN(sal)),round(MAX(sal))FROM emp,deptWHERE emp.deptno=dept.deptnoGROUP BY dnameHAVING count(empno)>2;4.查询岗位不为SALESMAN，工资和大于等于2500的岗位及每种岗位的工资和。 SELECT JOB,sum(sal)FROM empWHERE upper(JOB)!='SALESMAN'GROUP BY JOBHAVING sum(sal)>=2500;5.显示经理号码和经理姓名，这个经理所管理员工的最低工资，没有经理的KING也要显示，不包括最低工资小于3000的，按最低工资由高到低排序。SELECT m.empno,m.ename,min(e.sal)FROM emp e,emp mWHERE e.mgr=m.empno(+)GROUP BY m.empno,m.enameHAVING MIN(e.sal)>=3000ORDER BY min(e.sal) desc;6.写一个查询，显示每个部门最高工资和最低工资的差额SELECT deptno,MAX(sal)-MIN(sal)FROM empGROUP BY deptno;

4.使用ON子句，显示工作在CHICAGO的员工姓名，部门名称，工作地点，薪资等级   select ename,dname,loc ,grade   FROM emp JOIN dept  ON emp.deptno =dept.deptno         JOIN salgrade ON sal BETWEEN losal AND hisal   WHERE dept.loc='CHICAGO';   三、子查询子查询一般用()括起来子查询出现的位置：select,FROM,WHERE,HAVING嵌套子查询：一个查询语句中嵌套了另一个查询语句，子查询的语句可以直接运行

单列子查询：子查询的返回结果是单行单列的，经常用在where,HAVING多行子查询：子查询的返回结果是单列多行的，经常用在where多列子查询：子查询的返回结果是多行多列的，经常用在from，where

1、单列子查询---查询与smith相同职务的其它员工信息SELECT JOB FROM emp WHERE lower(ename)='smith';

SELECT *FROM empWHERE job=(SELECT JOB FROM emp WHERE lower(ename)='smith') and lower(ename)!='smith';

------查询部门平均工资最高的平均工资和部门名称SELECT MAX(avg(sal))FROM empGROUP BY deptno;

SELECT dname,round(avg(sal),2)FROM emp JOIN dept ON emp.deptno=dept.deptnoGROUP BY dnameHAVING avg(sal)=(SELECT MAX(avg(sal))                  FROM emp                  GROUP BY deptno);                  2、多行子查询：集合运算：in---查询与30部门职务相同的其它部门的员工信息SELECT distinct JOB FROM emp WHERE deptno=30;

SELECT *FROM empWHERE job in(SELECT distinct JOB FROM emp WHERE deptno=30) and deptno!=30;

ANY:=ANY:相当于inSELECT *FROM empWHERE job=any(SELECT distinct JOB FROM emp WHERE deptno=30) and deptno!=30;

>ANY:比子查询返回结果的最小值要大---查询比10部门的最低工资要高的其它部门员工信息SELECT *FROM empWHERE sal>ANY(SELECT sal               FROM emp              WHERE deptno=10) and deptno!=10;

all(SELECT sal FROM emp WHERE deptno=20);

(3-1)*3 and t.r(3-1)*3 and t.r(5-1)*3 and t.r2---2.查询部门平均工资大于2000，且人数大于2的部门编号，部门名称，部门人数，部门平均工资，并按照部门人数升序排序。select dept.deptno,dname,count(empno),avg(sal)from emp,deptwhere emp.deptno=dept.deptnogroup by dept.deptno,dnamehaving avg(sal)>=2 and avg(sal)>2000order by count(empno)

--第七章--练习1---1.查询入职日期最早的员工姓名，入职日期select ename,hiredatefrom empwhere hiredate=(select min(hiredate) from emp)---2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名，工资，部门名称select ename,sal,dnamefrom emp,deptwhere sal>(select sal from emp where ename='SMITH') and loc='CHICAGO' and emp.deptno=dept.deptno---3.查询入职日期比20部门入职日期最早的员工还要早的员工姓名，入职日期select ename,hiredatefrom empwhere hiredate(select avg(count(empno)) from emp group by deptno)--练习2---1.查询入职日期比10部门任意一个员工晚的员工姓名、入职日期，不包括10部门员工select ename,hiredatefrom empwhere hiredate>any(select hiredate from emp where deptno=10)and deptno10---2.查询入职日期比10部门所有员工晚的员工姓名、入职日期，不包括10部门员工select ename,hiredatefrom empwhere hiredate>all(select hiredate from emp where deptno=10)---3.查询职位和10部门任意一个员工职位相同的员工姓名，职位，不包括10部门员工select ename,jobfrom empwhere job=any(select distinct(job) from emp where deptno=10)and deptno10--练习3---1.查询职位及经理和10部门任意一个员工职位及经理相同的员工姓名，职位，不包括10部门员工select ename,emp.jobfrom emp,(select job,mgr from emp where deptno=10) twhere emp.mgr=t.mgr and emp.job=t.job and deptno10---2.查询职位及经理和10部门任意一个员工职位或经理相同的员工姓名，职位，不包括10部门员工select ename,emp.jobfrom emp,(select job,mgr from emp where deptno=10) twhere (emp.mgr=t.mgr or emp.job=t.job) and deptno10--练习4---1.查询比自己职位平均工资高的员工姓名、职位，部门名称，职位平均工资select ename,emp.job,dname,sal,avgsalfrom emp,dept,(select job, avg(sal) avgsal from emp group by job) twhere emp.deptno=dept.deptno and emp.job=t.job and sal>avgsal---2.查询职位和经理同员工SCOTT或BLAKE完全相同的员工姓名、职位，不包括SCOOT和BLAKE本人。select ename,t.jobfrom emp,(select  job,mgr from emp           where ename in('SCOTT','BLAKE')) twhere emp.ename not in('SCOTT','BLAKE') and emp.mgr=t.mgr---3.查询不是经理的员工姓名。select enamefrom empwhere empno not in(select distinct mgr from emp where mgr is not null)--练习5---1.查询入职日期最早的前5名员工姓名，入职日期。select ename,emp.hiredatefrom emp,(select rownum r, hiredate from emp order by hiredate) twhere r(select min(sal) from emp where deptno=10)

---4.查询员工工资为其部门最低工资的员工的编号和姓名及工资。select empno,ename,salfrom empwhere (deptno,sal) in(select deptno, min(sal) from emp group by deptno)---5.显示经理是KING的员工姓名，工资。select ename,salfrom empwhere mgr=(select empno from emp where ename='KING')---6.显示比员工SMITH参加工作时间晚的员工姓名，工资，参加工作时间。select ename,sal,hiredatefrom empwhere hiredate>(select hiredate from emp where ename='SMITH')---7.使用子查询的方式查询哪些职员在NEW YORK工作。select *from empwhere deptno=(select deptno from dept where loc='NEW YORK')---8.写一个查询显示和员工SMITH工作在同一个部门的员工姓名，雇用日期，查询结果中排除SMITH。select ename,hiredatefrom empwhere deptno=(select deptno from emp where ename='SMITH')and ename'SMITH'---9.写一个查询显示其工资比全体职员平均工资高的员工编号、姓名。select empno,enamefrom empwhere sal>(select avg(sal) from emp)---10.写一个查询显示其上级领导是King的员工姓名、工资。select ename,salfrom empwhere mgr=(select empno from emp where initcap(ename)='King')---11.显示所有工作在RESEARCH部门的员工姓名，职位。select ename,jobfrom empwhere deptno=(select deptno from dept where dname='RESEARCH')---12.查询每个部门的部门编号、平均工资，要求部门的平均工资高于部门20的平均工资。select deptno,avg(sal)from empgroup by deptnohaving avg(sal)>(select avg(sal) from emp where deptno=20)---13.查询大于自己部门平均工资的员工姓名，工资，所在部门平均工资，高于部门平均工资的额度。select ename,sal,t.a,sal-t.a,t.deptnofrom emp,(select deptno,avg(sal) a from emp group by deptno) twhere sal >t.a and emp.deptno=t.deptno---14.    列出至少有一个雇员的所有部门select deptnofrom emp group by deptnohaving count(empno)>0---15.    列出薪金比"SMITH"多的所有雇员select *from empwhere sal>(select sal from emp where ename='SMITH')---16.    列出入职日期早于其直接上级的所有雇员select *from emp e,emp mwhere e.hiredate(select avg(sal) from emp )---23.    显示出工资大于本部门平均工资的员工姓名，工资select ename,salfrom emp,(select deptno,avg(sal) a from emp group by deptno) twhere sal >t.a and emp.deptno=t.deptno---24.    显示每位经理管理员工的最低工资，及最低工资者的姓名select ename,salfrom emp,(select mgr, min(sal) m from emp group by mgr) twhere sal =t.m and t.mgr=emp.mgr

select ename,salfrom empwhere (sal,mgr) in (select min(sal),mgr from emp group by mgr)---25.    显示比工资最高的员工参加工作时间晚的员工姓名，参加工作时间select ename,hiredate from emp where hiredate>(select hiredate from emp where sal=(select max(sal) from emp ))---26.    显示出平均工资最高的的部门平均工资及部门名称select dname,avg(sal)from emp,deptwhere emp.deptno=dept.deptnogroup by dnamehaving avg(sal)=(select max(avg(sal))                 from emp                 group by deptno)