sql随笔

where 是对原始数据的过滤

having是对分组后的记录的过滤

join on中的On是连接条件

输出工资最高的前三名的每个员工的姓名，工资，工资等级，部门名称

select  top 3 emp.ename ,emp.sal,dept.dname,salgrade.grade

from emp

join dept

on emp.deptno = dept.deptno

join salgrade 

on emp.sal between salgrade.losal and salgrade.hisal

where emp.ename not like '%A%'

order by sal desc

求出每个员工的姓名，部门编号，薪水和薪水等级

select "E".ename,"E".deptno,"E".sal,"S".grade

from emp "E"

join dept "D"

on "E".deptno = "D".deptno

join salgrade "S"

on "E".sal >= "S".losal and "E".sal <= "S".hisal

查找每个部门的编号该部门的所有的员工的平均工资平均工资等级

select "temp"."平均工资","temp"."部门","s".grade "工资级别"

from

(select avg(sal) "平均工资","e".deptno "部门"

from emp "e"

group by "e".deptno) "temp"

join salgrade "s"

on "temp"."平均工资" between "s".losal and "s".hisal 

EMP表中所有领导的姓名

select distinct("e".ename)

from emp "e"

join emp "m"

on "e".empno = "m".mgr

求出平均薪水最高的部门的编号和部门的平均工资

select top 1 avg(sal) "s",emp.deptno 

from emp 

group by emp.deptno 

工资大于所有员工中工资最低的前三个人的姓名部门编号，部门名称，工资等级

select emp.ename,emp.deptno,dept.dname,salgrade.grade

from emp

join dept

on emp.deptno = dept.deptno

join salgrade

on emp.sal between salgrade.losal and salgrade.hisal

where emp.sal>

(

select min(sal)

from emp

)