分组函数(与group by连用的聚合函数)只能出现在选择列表,having,order by字句中;
select depno,max(subsidy) from employee group by depno having depno!='01' order by depno asc;
//如果select语句中同时包涵group by ,having,order by,那么执行顺序是
group by->having->order by;
例如
select depno,max(subsidy) from employee group by depno order by depno asc having depno!='01' ; //错误
//在选择列中如果有一个(多个)列,表达式,和分组函数,那么这些列和表达式必须有一个(多个)出现在group by字句中,否则报错
select depno,avg(basicWage),max(basicWage) from employee group by job having avg(basicWage)<50000 order by depno asc;//报错:不是group by 字句
select depno,job,avg(basicWage),max(basicWage) from employee group by job having avg(basicWage)<50000 order by depno asc;
select depno,avg(basicWage),max(basicWage) from employee group by depno having avg(basicWage)<50000 order by depno asc;//正确语句
select avg(basicWage),max(basicWage) from employee group by depno having avg(basicWage)<50000 order by depno asc;//正确语句
登录Scott用户
//显示雇员的名字,工资以及所在的部门 注意:当使用多表联合查询的时,如果不带任何条件(where)会出现笛卡尔积(后面表的一行与前面表的每行匹配,直到后面行匹配完)。
select ename,sal,dname from emp, dept ;//规定:多表查询的条件(where)是 至少不能少于(表的个数-1)
select ename,sal,dname from emp, dept where emp.deptno=dept.deptno;
//显示部门号为10的员工的名字,部门名与工资;
select ename,sal,dname from emp,dept where emp.deptno=dept.deptno and dept.deptno='10';
//显示各个员工的名字,工资,以及工资所在的级别;
select ename,sal,grade from emp, salgrade where emp.sal>= salgrade.losal and emp.sal<= salgrade.hisal;
select ename,sal,grade from emp, salgrade where emp.sal between salgrade.losal and salgrade.hisal;
//显示员工的上级领导的姓名
select x.ename,y.ename "上级领导" from emp x,emp y where x.mgr=y.empno(+)//king没有领导,空值处理用(+)
/显示员工的上上级领导的姓名
select x.ename,z.ename from emp x,emp y,emp z where x.mgr=y.empno and y.mgr=z.empno;
//如何显示工资比30号部门的所有员工的工资高的员工姓名,工资,部门号。
select ename,sal,deptno from emp where sal> (select max(sal) from emp group by deptno having deptno=30);
或者select ename,sal,deptno from emp where sal> (select max(sal) from emp where deptno=30);
或者select ename,sal,deptno from emp where sal>all (select sal from emp where deptno=30);
//如何显示工资比30号部门的随意一个员工的工资都高的员工姓名,工资,部门号。