【数据库练习】3.子查询

-- 1. 执⾏下列SQL语句，这条语句出错的原因在于 ( C )

select ename, sal from emp
where sal =
      (select sal from emp
       where ename = 'smith' or deptno = 20);

A. ⼦查询中不能出现where⼦句
B. 逻辑运算符 or 不允许出现在where⼦句中
C. ⼦查询得到多⾏结果，主查询中使⽤的是单⾏⽐较运算符
D. ⼦查询得到单⾏结果，主查询中使⽤的是多⾏⽐较运算符

-- 2.查询哪个部⻔的平均⼯资是最⾼的，列出部⻔编码，平均⼯资。

select e.deptno,max(avg_sal)
from (select avg(sal)avg_sal,deptno from emp group by deptno)e;

-- 3.列出各个部⻔中⼯资最⾼的员⼯的信息：名字、部⻔号、⼯资。

select max(sal),deptno from emp group by deptno;

select e.ename,e.sal,e.deptno 
from emp e, (select max(sal)sal_max,deptno from emp group by deptno)d 
where e.sal=d.sal_max;

-- 4. 查询管理者是“KING”的员⼯姓名（ename）和⼯资（sal）。

select empno from emp where mgr=(select empno from emp where ename = 'KING');

-- 5. 查询部⻔所在地（loc）为“NEW YORK”的部⻔的员⼯姓名、部⻔名称和岗位名称。//子查询可以放查询里

select * from dept where loc='NEW YORK';

select e.ename,d.dname,e.job 
from emp e join (select * from dept where loc='NEW YORK')d
on d.deptno=e.deptno ;

-- 6.查询⼯资⽐公司平均⼯资⾼的所有员⼯的员⼯号，姓名和⼯资。

select empno,ename,sal from emp where sal>(select AVG(sal) from emp);

-- 7. 查询姓名中包含字⺟“u”的员⼯在相同部⻔的员⼯的员⼯号和姓名。

select empno,ename 
from emp 
where deptno in (select deptno from emp where ename like '%u%');

-- 8. 查询哪些员⼯的薪⽔⽐本部⻔的平均薪⽔低。

select e.ename,e.sal,d.avg_sal 
from emp e join (select deptno,avg(sal)avg_sal from emp group by deptno) d
on d.deptno=e.deptno and e.sal>d.avg_sal;

-- 9. SALES部⻔有哪些职位？

select DISTINCT job 
from emp 
where deptno=(select deptno from dept where dname='SALES');

-- 10. 哪些⼈不是别⼈的经理？

select DISTINCT ifnull(mgr,0) from emp;

select empno 
from emp 
where empno not in (select DISTINCT ifnull(mgr,0) from emp) ;

-- 11. 谁的薪⽔⽐FORD⾼？如果有多个同名，⽐任何⼀个叫FORD的⼈⾼就⾏

select sal from emp where ename='ford';

select ename 
from emp 
where sal>any(select sal from emp where ename='ford');

-- 12. 谁和FORD同部⻔？列出除了FORD之外的员⼯名字

select deptno from emp where ename = 'ford';

select ename 
from emp 
where deptno in (select deptno from emp where ename = 'ford') and ename <> 'ford';

-- 13. 哪个部⻔的⼈数⽐部⻔20的⼈数多

select deptno,COUNT(empno) 
from emp
group by deptno 
having COUNT(empno)>(select COUNT(empno) from emp where deptno=20);

-- 14.列出员⼯名字和职位，查询员⼯所在的部⻔平均薪⽔⼤于2000元的员⼯信息

select deptno from emp group by deptno having avg(sal)>2000;

select ename,job 
from emp 
where deptno in (select deptno from emp group by deptno having avg(sal)>2000);

-- 15. 每个部门除了最高薪水的人

select a.ename, a.sal, a.deptno, b.maxsal
from emp a,
     (select deptno, max(sal) maxsal
      from emp
      group by deptno) b
where a.deptno = b.deptno
  and a.sal < b.maxsal;

 
-- 16. 找出EMP中那些⼯资⾼于他们所在部⻔普通员⼯（不包含管理者）平均⼯资的员⼯。

select empno from emp where empno not in (select DISTINCT ifnull(mgr,0) from emp) ;

select avg(sal),deptno from emp group by deptno;

select e.empno,e.sal,d.avg_sal
from (select * from emp where empno not in (select DISTINCT ifnull(mgr,0) from emp))e ,
(select avg(sal) avg_sal,deptno from emp where empno in
(select empno from emp where empno not in (select DISTINCT ifnull(mgr,0) from emp)
group by empno)group by deptno)d
where   e.sal>d.avg_sal
and e.deptno=d.deptno;

-- 17. 针对Student表执⾏下列SQL语句：
/*select a.name, a.score, a.classid, b.avgscore
from student a,
     (select classid, avg(score) avgscore
      from student
      group by classid) b
where a.classid = b.classid
  and a.score > b.avgscore*/
-- 关于运⾏结果，下列描述正确的是 ( )
/*A. 第⼀⾏出现错误
B. 第三⾏出现错误
C. 第六⾏出现错误
D. 语句正常执⾏*/
-- D