/* 时间:2020/09/11 功能: 一 练习题 二 其他连接 */
一 练习题
-- 查询: 员工姓名、工资、工资等级 select "E".ename, "E".sal, "S".grade from emp "E" join salgrade "S" on "E".sal between "S".losal and "S".hisal -- 查询: 员工姓名第二个字母为A、 -- 输出: 员工姓名、工资、部门编号、部门名称、工资等级 select "E".ename, "E".sal, "E".deptno, "D".dname, "S".grade from emp "E" join dept "D" on "E".deptno = "D".deptno join salgrade "S" on "E".sal between "S".losal and "S".hisal where "E".ename like '_a%'
-- 每个员工姓名、部门编号、部门名称、薪水、薪水等级 select "E".ename, "E".deptno, "D".dname, "E".sal, "S".grade from emp "E" join dept "D" on "E".deptno = "D".deptno join salgrade "S" on "E".sal between "S".losal and "S".hisal
-- 查找每个部门的编号 该部门所有员工的平均工资 平均工资的等级 -- 写法一 select "T".deptno, "T"."dept_avg(sal)", "S".grade from salgrade "S" join( select deptno, avg(sal) "dept_avg(sal)" from emp group by deptno) "T" on "S".losal <= "T"."dept_avg(sal)" and "S".hisal >= "T"."dept_avg(sal)" -- 写法二 select "T".deptno, "T"."dept_avg(sal)", "S".grade from (select deptno, avg(sal) "dept_avg(sal)" from emp group by deptno) "T" join salgrade "S" on "T"."dept_avg(sal)" between "S".losal and "S".hisal -- 写法三 select "T".deptno, "T"."dept_avg(sal)", "S".grade from (select deptno, avg(sal) "dept_avg(sal)" from emp group by deptno) "T", salgrade "S" where "T"."dept_avg(sal)" between "S".losal and "S".hisal
-- 查找每个部门的编号 部门名称 该部门所有员工的平均工资 平均工资的等级 select "T".deptno, "D".dname, "T"."dept_avg(sal)", "S".grade from (select deptno, avg(sal) "dept_avg(sal)" from emp group by deptno) "T" join dept "D" on "T".deptno = "D".deptno join salgrade "S" on "T"."dept_avg(sal)" between "S".losal and "S".hisal
-- 求出emp表中所有领导的信息 select * from emp where empno in ( select mgr from emp) -- 求出emp表中所有非领导的信息 select * from emp where empno not in ( select isnull(mgr, 0) from emp)
-- 求出平均薪水最高的部门的编号和部门的平均工资 -- 方法一 select top 1 deptno, avg(sal) from emp group by deptno order by avg(sal) desc -- 方法二 select * from ( select deptno, avg(sal) "avg_sal" from emp group by deptno )"E" where "E"."avg_sal" = ( select max("avg_sal") from (select deptno, avg(sal) "avg_sal" from emp group by deptno) "T" )
-- 有一个人工资最低 把这个人排除掉 -- 剩下的人中工资最低的前3个人的姓名 工资 部门编号 部门名称 工资等级 输出 select top 3 "T".ename, "T".sal, "T".deptno, "D".dname, "S".grade from( select * from emp "E" where sal > (select min(sal) from emp)) "T" join dept "D" on "T".deptno = "D".deptno join salgrade "S" on "T".sal between "S".losal and "S".hisal order by "T".sal
二 其他连接
-- 外连接 -- 左外连接 select * from dept "D" left join emp "E" on "D".deptno = "E".deptno -- 右外连接 select * from emp "E" right join dept "D" on "D".deptno = "E".deptno -- 完全连接 select * from dept full join emp on dept.deptno = emp.deptno -- 交叉连接 select * from emp cross join dept -- 等价于 select * from emp, dept -- 自连接 -- 把最高工资人的信息输出, 不准用聚合函数。 select * from emp where empno not in( select distinct "E1".empno from emp "E1" join emp "E2" on "E1".sal < "E2".sal ) -- 联合 -- 输出每个员工姓名 工资 上司姓名 select "E1".ename, "E1".sal, "E2".ename "Boss name" from emp "E1" join emp "E2" on "E1".mgr = "E2".empno -- 第一步: 求上司姓名 union select ename, sal, '最大老板' from emp where mgr is null