该楼层疑似违规已被系统折叠 隐藏此楼查看此楼
select avg(sal) from emp where sal>1200 group by deptno having avg(sal)>1500 order by avg(sal) desc;查询sal大于1200的平均值按照deptno排序,并且sal的平均值大于1500,然后用sal平均值的降序排列
子查询
select ename,sal,deptno from emp where sal in(select max(sal) from emp group by deptno);查询所有部门中sal最高的人名和sal以及部门编号。(这是错误的,最后查询出来的值会变多,因为首先查询出来的是各部门最高的薪水值,前面查询语句查询所有的人名,薪水值和部门编号in这些薪水值,所以薪水值只要在各部门最高薪水中的任何一个,都会被查询出来)
select ename,sal from emp join (select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal=t.max_sal and emp.deptno=t.deptno);
第十四课
select e.ename,t.ename from (select * from emp) t,emp e where t.empno=e.mgr;
select e1.ename,e2.ename from emp e1,emp e2 where e2.empno=e1.mgr;--查询雇员的上级经理姓名
sql1992与sql1999对比
sql1999where后面直接写数据过滤条件
select ename,dname from emp,dept;--1992sql标准
select ename,dname from emp cross join dept;--1999sql新标准 cross join交叉连接
select ename,dname from emp,dept where emp.deptno=dept.deptno;
select ename,dname from emp join dept on (emp.deptno=dept.deptno);
select ename,dname from emp join dept using(deptno);--等值连接可以使用这种方式
select ename,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);
select ename,dname,grade from emp e join dept d on (d.deptno=e.deptno) join salgrade s on (e.sal between s.losal and s.hisal) where ename not like '_A%';
select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr=e2.empno);
--外连接
select e1.ename,e2.ename from emp e1 left join emp e2 on (e1.mgr=e2.empno);--左外连接(可写为left outer join)
select ename,dname from emp e right join dept d on (e.deptno=d.deptno);--右外连接
select ename,dname from emp e full join dept d on (e.deptno=d.deptno);--全外连接
十六课
--求部门中哪些人的薪水最高
select ename,sal from emp join(select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal=t.max_sal and emp.deptno=t.deptno);
--求部门平均薪水的等级
select deptno avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal);
select deptno,avg(grade) from (select deptno,ename,grade from emp join salgrade on (sal between losal and hisal)) t group by deptno;--查询每个部门的平均薪水等级
select ename from emp where empno in (select mgr from emp); --雇员中哪些是经理人
select ename from emp where empno in (select distant mgr from emp); --雇员中哪些是经理人
select sal from emp where sal not in(select e.sal from emp e join (select sal from emp) t on e.sal
select sal from emp t where t.sal >= all (select sal from emp);--貌似all是聚组函数
--求平均薪水最高的部门编号
20
--查询平均薪水最高的部门编号
select deptno,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal=
(select max(avg_sal) from