sql 面试题

--select * from emp;
--select * from dept;
--统计部门表里的每个部门的员工人数，并打印出部门编号、名称
--思路：因为有些部门刚建立可能没有员工，要以部门表为主，使用右连接
select d.deptno as "部门编号",
       d.dname as "部门名称",
       count(e.deptno) as "部门人数"
  from emp e
 right join dept d
    on e.deptno = d.deptno
 group by d.deptno, d.dname;
 
 --统计至少有一个员工的部门
--思路1：如果在员工表里有对应的部门编号就说明该部门至少有一个员工
select d.deptno,d.dname from dept d where deptno in(select distinct deptno from emp);
--思路2：先从员工表里查询出部门人数大于等于1的部门编号，然后再从部门表里查
select d.deptno, d.dname
  from dept d
 where deptno in
       (select deptno from emp group by deptno having count(deptno) >= 1);
--思路三：原理和思路一差不多，只是写法不一样
select dname from dept d where exists(select null from emp  e where e.deptno=d.deptno);   

--找出没有选修过“李明”老师授课的学生姓名
--第一种方法：
--第一步：在课程表中找出“李明”老师讲授的所有课程
select cno from  Course where teacher="李明"；
--第二步：在选修表中找出对应的学生号条件是课程号不在第一步中的
select sno from SC where cno not in
     (select cno from  Course where teacher='李明')
--第三步：根据学生号找出对应的学生姓名
select sname from S where sno in
   (select sno from SC where cno not in
     (select cno from  Course where teacher='李明'))
--第二种方法：
/*
exists （sql 返回结果集为真）  
not exists (sql 不返回结果集为真）
not exists 里是一个子查询，当数据库在查询时，查询到teacher='李明'时
子查询就有数据，有数据就返回false ，整个查询语句就变成了
select sname from s where false;--不执行
相反当查询到teacher<>'李明'时,返回true ，就把对应学生名字查出来了
*/
select sname from s
 where not exists 
 (select * from sc, c where sc.cno = c.cno  and c.cteacher = '李明' and sc.sno = s.sno)
          
         
/*
表A有三个字段
years   a     b 
2001    1     1.1
2001    2     1.2
2001    3     1.3
2001    4     1.4
2002    1     1.1
2002    2     1.2
2002    3     1.3
2002    4     1.4
用一条SQL语句写，得出如下结果：
years    a1       a2     a3     a4 
2001     1.1  	1.2    1.3    1.4    
2002     1.1  	1.2   1.3    1.4
*/          
select years,
      sum(case a when 1 then b end)as a1,
      sum(case a when 2 then b end)as a2,
      sum(case a when 3 then b end)as a3,
      sum(case a when 4 then b end)as a4
from A 
group by years;