SQL习题

1.部门平均薪水的等级
    select deptno, avg_sal, grade from
      (select deptno, avg(sal) avg_sal from emp group by deptno) t
          join salgrade s on (t.avg_sal between s.losal and s.hisal);

2.部门平均的薪水等级
    select deptno, avg(grade) avg_grade from
    (select deptno, sal, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal))
        group by deptno; 

3.哪些人是经理
    select ename from emp where empno in (select distinct mgr from emp);
    
4.不用组函数求薪水的最高值（面试题）
    select distinct sal from emp where sal not in
    (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));
    
5.平均薪水最高的部门的部门编号
    select deptno, avg_sal from
    (select deptno, avg(sal) avg_sal from emp group by deptno)
        where avg_sal = 
            (select max(avg_sal) from (select deptno, avg(sal) avg_sal from emp group by deptno));
    
6.求平均薪水最高的部门的部门名称
    select dname from dept where deptno = (
        select deptno from (
        select deptno, avg(sal) avg_sal from emp group by deptno
    )
    where avg_sal = (
        select max(avg_sal) from (select deptno, avg(sal) avg_sal from emp group by deptno)
    )
    );
    
7.求平均薪水的等级最低的部门的部门名称
    select dname, t1.deptno, grade, avg_sal from dept where deptno = (
        select deptno from (
            select deptno, avg_sal, grade from (
                select deptno, avg(sal) avg_sal from emp group by deptno
                ) t1 join salgrade s on (t1.avg_sal between s.losal and s.hisal)
        ) where grade = (
            select min(grade) from (
                select deptno, avg_sal, grade from (
                    select deptno, avg(sal) avg_sal from emp group by deptno
                ) t join salgrade s on (t.avg_sal between s.losal and s.hisal)
            )
        )
    );

视图创建:
    create view v$_dept_avg_sal_info as
    select deptno, avg_sal, grade from (
      select deptno, avg(sal) avg_sal from emp group by deptno
  ) t join salgrade s on (t.avg_sal between s.losal and s.hisal);

8.比普通员工薪水还要高的经理人名称
    select ename from emp where empno in (
        select distinct mgr from emp where mgr is not null
    ) and sal > (
        select max(sal) from emp where empno not in (
            select distinct mgr from emp where mgr is not null
        )
    );
    
9.面试题：比较效率
    select * from emp where deptno = 10 and ename like '%A%';
    select * from emp where ename like'%A%' and deptno = 10;
    第一个快，先比较数字快。数字不对可以直接不比较字符串。
    
10.有三个表，S,C,SC,
S(SNO,SNAME)代表（学号，姓名）
C(CNO,CNAME,CTEACHER)代表（课号，课名，教师）
SC(SNO,CNO,SCGRADE)代表（学号，课号成绩）
问题：
①找出没选过“黎明”老师的所有学生姓名
    select sname from s join sc on (s.sno = sc.sno) join c on (c.sno = sc.sno) where c.teacher <> 'liming';
②找出2门以上（含2门）不及格学生姓名及平均成绩
    select sname where sno in (select sno from sc where grade < 60 group by sno having count(*) >= 2);
③即学过1号课程也学过2号课程的所有学生的姓名
    select sname from s where sno in (select sno from sc where cno = 1 and sno in (select sno from sc where cno = 2));