子查询-多行多列
# 4.查询工作和工资与MARTIN(马丁)完全相同的员工信息 SELECT * from emp WHERE (job,sal) in (SELECT job,sal FROM emp WHERE ename = 'MARTIN'); # 5."有2个以上直接下属" 的员工信息【查找经理信息,并且这个经理有2个以上下属】 #并显示下属个数 select ename,COUNT(mgr) FROM emp GROUP BY mgr HAVING COUNT(mgr)>2; SELECT * FROM emp t1,(select mgr,COUNT(mgr) FROM emp GROUP BY mgr HAVING COUNT(mgr) >= 2) t2 where t1.empno = t2.mgr; -- t2.mgr上级编号和t1的empno员工编号是对应关系 -- 6.查询员工编号为7788的员工名称、员工工资、部门名称、部门地址 -- 多表多列,用于表 SELECT d.depno,d.name,d.location FROM depart d; SELECT e.ename '员工名称',e.sal '员工工资',d.name '部门名称',d.location '部门地址' FROM emp e,(SELECT depno,name,location FROM depart) d where e.depno = d.depno AND e.empno = 7788; -- 求各个部门薪水最高的员工所有信息 -- 求每个部门最高的工资 SELECT depno,MAX(sal) maxsal FROM emp GROUP BY depno; SELECT t1.* FROM emp t1,(SELECT depno,MAX(sal) maxsal FROM emp GROUP BY depno) t2 WHERE t1.sal = t2.maxsal;