必知必会sql面试题练习总结之学生课程篇

先建表 插入数据（oracle数据库为例，建表没有考虑外键约束）
create table tb_student(
student_id number(11),
s_name varchar2(20),
s_age number(11),
s_sex varchar2(2))


create table tb_course(
course_id number(11),
c_name varchar2(20),
teacher_id number(11)
)


create table tb_score(
student_id number(11),
score varchar2(20),
course_id number(11)
)


create table tb_teacher(
teacher_id number(11),
t_name varchar2(20)
)
将tb_score 字段score的类型改为 number类型 
alter table tb_score rename column score to score_tmp;
alter table tb_score add  score number(11)
update  tb_score set score = trim(score_tmp)
alter table tb_score drop column score_tmp;


开始实战练习
1.查询课程id=1的成绩大于课程id=2的成绩的学生学号
select sc1.student_id
  from tb_score sc1, tb_score sc2
 where sc1.course_id = 1
   and sc2.course_id = 2
   and sc1.score > sc2.score
   and sc1.student_id = sc2.student_id
   
 select * from
 (select student_id ,Score from tb_score  where course_id=1) a,
 (select student_id ,Score from tb_score  where course_id=2) b
 where a.student_id=b.student_id and a.Score> b.score
2.查询平均成绩大于60分的同学的学号和平均成绩；
select student_id 学生学号 ,avg(score) 平均成绩 from  tb_score group by student_id having avg(score)>60 order by avg(score) desc
3.查询所有同学的学号、姓名、选课数、总成绩；
select s.student_id 学生学号,
       s.s_name 学生姓名,
       count(course_id) 选课数,
       sum(score) 总成绩
  from tb_student s, tb_score s1
 where s.student_id = s1.student_id
 group by s.student_id , s.s_name , s.s_age ,s.s_sex order by sum(score) desc
4.查询姓“李”的老师的个数；
select count(distinct teacher_id) from tb_teacher where t_name like '李%'
5.查询没学过“肖老师”老师课的同学的学号、姓名；用 (not) exists 代替 (not) in 
select s.student_id 学生编号, s.s_name 学生姓名
  from tb_student s
 where  exists (select *
          from tb_teacher tt, tb_course tc, tb_score ts
         where s.student_id = ts.student_id
           and ts.course_id = tc.course_id
           and tt.teacher_id = tc.teacher_id
           and tt.t_name = '肖老师')
6.查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
select s.student_id 学生编号, s.s_name 学生姓名 
 from tb_student s ,tb_score ts
 where s.student_id = ts.student_id and ts.course_id =1 and exists (select *
 from tb_student s ,tb_score ts
 where s.student_id = ts.student_id and ts.course_id =2)
 
 select s.student_id 学生编号, s.s_name 学生姓名 
 from tb_student s ,tb_score ts
 where s.student_id = ts.student_id and ts.course_id =1  
 intersect
 select s.student_id 学生编号, s.s_name 学生姓名 
 from tb_student s ,tb_score ts
 where s.student_id = ts.student_id and ts.course_id =2
PS:EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。那么，这里我们来看一下in和exists的区别：
①in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。
②一直以来认为exists比in效率高的说法是不准确的。
　-->如果查询的两个表大小相当，那么用in和exists差别不大。
　-->如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。
（7）查询学过“肖老师”老师所教的所有课的同学的学号、姓名；
 select s.student_id 学生编号, s.s_name 学生姓名 from tb_student s ,tb_score ts ,tb_teacher tt ,tb_course tc
 where s.student_id = ts.student_id and ts.course_id = tc.course_id and tc.teacher_id = tt.teacher_id and tt.t_name = '肖老师'
（8）查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
 select s.student_id 学生编号, s.s_name 学生姓名  from tb_student s, 
 (select student_id ,score from tb_score ts where ts.course_id =1) a ,
 (select student_id ,score from tb_score ts where ts.course_id =2) b 
 where s.student_id = a.student_id and s.student_id = b.student_id and a.score < b.score
9.查询有课程成绩小于60分的同学的学号、姓名；
 select s.student_id 学生编号, s.s_name 学生姓名, min(score) 最低成绩
   from tb_student s, tb_score ts
  where s.student_id = ts.student_id
  group by s.student_id, s.s_name, s.s_age, s.s_sex
 having min(score) < 60
 select s.student_id 学生编号, s.s_name 学生姓名
 from tb_student s
 where s.student_id in
 (
 select distinct(ts.student_id) from tb_score ts where  ts.student_id = s.student_id and ts.score <60
 )
10.查询没有学全所有课的同学的学号、姓名；
select s.student_id 学生编号, s.s_name 学生姓名
  from tb_student s
 where s.student_id not in
       (select ts.student_id
          from tb_score ts
         group by ts.student_id
        having count(distinct ts.course_id) = (select count(distinct
                                                           tc.course_id)
                                                from tb_course tc))
（11）查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
select distinct(s.student_id) 学生编号, s.s_name 学生姓名
  from tb_student s ,tb_score ts 
  where s.student_id = ts.student_id and ts.course_id in (
  select distinct（course_id）  from tb_score ts2 where ts2.student_id = 1001
  ) order by s.student_id
（13）把“成绩”表中“温老师”老师教的课的成绩都更改为此课程的平均成绩；
 update tb_score t
    set t.score = (select avg(score)
                   from tb_score ts, tb_course tc, tb_teacher tt
                  where ts.course_id = tc.course_id
                    and tc.teacher_id = tt.teacher_id
                    and tt.t_name = '温老师')
  where t.course_id = (
  select course_id from tb_course tc ,tb_teacher tt where tc.teacher_id = tt.teacher_id and tt.t_name = '温老师'）


（14）查询和“1001”号的同学学习的课程完全相同的其他同学学号和姓名；
select distinct (s.student_id) 学生编号, s.s_name 学生姓名
  from tb_student s
 where s.student_id != 1001
   and s.student_id in
       (select distinct (t.student_id)
          from tb_score t
         where t.course_id in (select ts.course_id
                                 from tb_score ts
                                where ts.student_id = 1001)
         group by t.student_id
        having count(course_id) = (select count(course_id)
                                    from tb_score
                                   where student_id = 1001))


（17）按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
select t.student_id as 学生编号,
       (select score
          from tb_score t1
         where t.student_id = t1.student_id
           and t1.course_id = 1) as 语文,
       (select score
          from tb_score t1
         where t.student_id = t1.student_id
           and t1.course_id = 4) as 数学,
       (select score
          from tb_score t1
         where t.student_id = t1.student_id
           and t1.course_id = 2) as 英语,
       avg(t.score) as 平均成绩,
       count(t.course_id) as 有效科目数
  from tb_score t
 group by t.student_id
 order by avg(t.score) desc