必知必會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