Student(Sid,Sname,Sage,Ssex) 學生表
Course(Cid,Cname,Tid) 課程表 SC(Sid,Cid,score) 成績表 Teacher(Tid,Tname) 教師表 練習內容： 1.查詢“某1”課程比“某2”課程成績高的所有學生的學號； SELECT a.sid FROM (SELECT sid,score FROM SC WHERE cid=1) a,(SELECT sid,score FROM SC WHERE cid=3) b WHERE a.score>b.score AND a.sid=b.sid; 此題知識點，嵌套查詢和給查出來的表起別名 2.查詢平均成績大於60分的同學的學號和平均成績； SELECT sid,avg(score) FROM sc GROUP BY sid having avg(score) >60; 此題知識點，GROUP BY 語句用於結合合計函數，根據一個或多個列對結果集進行分組。group by後面不能接where，having代替了where 3.查詢所有同學的學號、姓名、選課數、總成績 SELECT Student.sid,Student.Sname,count(SC.cid),sum(score)FROM Student left Outer JOIN SC on Student.sid=SC.cid GROUP BY Student.sid,Sname 4.查詢姓“李”的老師的個數； select count(teacher.tid)from teacher where teacher.tname like'李%' 5.查詢沒學過“葉平”老師課的同學的學號、姓名； SELECT Student.sid,Student.Sname FROM Student WHERE sid not in (SELECT distinct( SC.sid) FROM SC,Course,Teacher WHERE SC.cid=Course.cid AND Teacher.id=Course.tid AND Teacher.Tname='葉平'); 此題知識點，distinct是去重的作用 6.查詢學過“```”並且也學過編號“```”課程的同學的學號、姓名； select a.SID,a.SNAME from (select student.SNAME,student.SID from student,course,sc where cname='c++'and sc.sid=student.sid and sc.cid=course.cid) a, (select student.SNAME,student.SID from student,course,sc where cname='english'and sc.sid=student.sid and sc.cid=course.cid) b where a.sid=b.sid; 標准答案（但是好像不好使）SELECT Student.S#,Student.Sname FROM Student,SC WHERE Student.S#=SC.S# AND SC.C#='001'and exists( SELECT * FROM SC as SC_2 WHERE SC_2.S#=SC.S# AND SC_2.C#='002'); 此題知識點，exists是在集合裡找數據，as就是起別名 7.查詢學過“葉平”老師所教的所有課的同學的學號、姓名； select a.sid,a.sname from (select student.sid,student.sname from student,teacher,course,sc where teacher.TNAME='楊巍巍' and teacher.tid=course.tid and course.cid=sc.cid and student.sid=sc.sid) a 標准答案：SELECT sid,Sname FROM Student WHERE sid in (SELECT sid FROM SC ,Course ,Teacher WHERE SC.cid=Course.cid AND Teacher.tid=Course.tid AND Teacher.Tname='楊巍巍' GROUP BY sid having count(SC.cid)=(SELECT count(cid) FROM Course,Teacher WHERE Teacher.tid=Course.tid AND Tname='楊巍巍')) 8.查詢課程編號“”的成績比課程編號“”課程低的所有同學的學號、姓名； select a.sid,a.sname from(select student.SID,student.sname,sc.SCORE from student,sc where student.sid=sc.sid and sc.cid=1) a, (select student.SID,student.sname,sc.score from student,sc where student.sid=sc.sid and sc.cid=2) b where a.score<b.score and a.sid=b.sid 標准答案：SELECT sid,Sname FROM (SELECT Student.sid,Student.Sname,score , (SELECT score FROM SC SC_2 WHERE SC_2.sid=Student.sid AND SC_2.cid=1) score2 FROM Student,SC WHERE Student.sid=SC.sid AND cid=1) S_2 WHERE score2 <score; 9.查詢所有課程成績小於分的同學的學號、姓名； SELECT sid,Sname FROM Student WHERE sid not in (SELECT Student.sid FROM Student,SC WHERE Student.sid=SC.sid AND score>60); 此題知識點，先查出大於60分的，然後not in 就是小於60分的了 10.查詢沒有學全所有課的同學的學號、姓名； SELECT Student.sid,Student.Sname FROM Student,SC WHERE Student.sid=SC.sid GROUP BY Student.sid,Student.Sname having count(cid) <(SELECT count(cid) FROM Course); 11.查詢至少有一門課與學號為“”的同學所學相同的同學的學號和姓名； 12.查詢至少學過學號為“”同學所有一門課的其他同學學號和姓名； SELECT student.sid,student.Sname FROM Student,SC WHERE Student.sid=SC.sid AND cid in (SELECT cid FROM SC WHERE sid=1) 此題知識點，SELECT sid,Sname FROM Student,SC WHERE Student.sid=SC.sid AND cid in (SELECT cid FROM SC WHERE sid=1)這樣寫是錯誤的，因為from後面是兩個表，不能明確是哪個表裡面的sid和sname所以錯誤提示是“未明確定義列” 13.把“SC”表中“葉平”老師教的課的成績都更改為此課程的平均成績； update sc set score=(select avg(score) from sc,course,teacher where course.cid=sc.cid and course.tid=teacher.tid and teacher.tname='楊巍巍') 14.查詢和“”號的同學學習的課程完全相同的其他同學學號和姓名； SELECT sid FROM SC WHERE cid in (SELECT cid FROM SC WHERE sid=6) GROUP BY sid having count(*)=(SELECT count(*) FROM SC WHERE sid=6); 此題知識點，用數量來判斷 15.刪除學習“葉平”老師課的SC表記錄； delete from sc s where s.cid in (select c.cid from teacher t,course c where t.tid = c.tid and tname='李子') 此題知識點，嵌套查詢可以分布考慮，先查出李子老師都交了什麼課的id，然後再刪除那些id的值 16.向SC表中插入一些記錄，這些記錄要求符合以下條件：沒有上過編號“”課程的同學學號、課程的平均成績； Insert into SC SELECT sid,2,(SELECT avg(score) FROM SC WHERE cid=2) FROM Student WHERE sid not in (SELECT sid FROM SC WHERE cid=2); 17.按平均成績從高到低顯示所有學生的“數據庫”、“企業管理”、“英語”三門的課程成績，按如下形式顯示：學生ID,,數據庫,企業管理,英語,有效課程數,有效平均分；(沒做出來) 18.查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分，最低分； select cid as 課程號,max(score)as 最高分,min(score) as 最低分 from sc group by cid 標准答案（但是運行不好使）SELECT L.cid As 課程ID,L.score AS 最高分,R.score AS 最低分 FROM SC L ,SC AS R WHERE L.cid = R.cid AND L.score = (SELECT MAX(IL.score) FROM SC AS IL,Student AS IM WHERE L.cid = IL.cid AND IM.sid=IL.sid GROUP BY IL.cid) AND R.Score = (SELECT MIN(IR.score) FROM SC AS IR WHERE R.cid = IR.cid GROUP BY IR.cid ); 19.按各科平均成績從低到高和及格率的百分數從高到低順序 26.查詢每門課程被選修的學生數 select sc.cid,count(sc.sid) from sc,course where sc.cid=course.cid group by sc.cid 27.查詢出只選修了一門課程的全部學生的學號和姓名 SELECT SC.sid,Student.Sname,count(cid) AS 選課數 FROM SC ,Student WHERE SC.sid=Student.sid GROUP BY SC.sid ,Student.Sname having count(cid)=1; 32.查詢每門課程的平均成績，結果按平均成績升序排列，平均成績相同時，按課程號降序排列 SELECT Cid,Avg(score) FROM SC GROUP BY cid ORDER BY Avg(score),cid DESC ; 37.查詢不及格的課程，並按課程號從大到小排列 SELECT cid,sid FROM sc WHERE score <60 ORDER BY cid 38.查詢課程編號為且課程成績在分以上的學生的學號和姓名； select student.sid,student.sname from sc,student where sc.cid=1 and sc.score>60 and sc.sid=student.sid 40.查詢選修“葉平”老師所授課程的學生中，成績最高的學生姓名及其成績 select student.sname,sc.score from sc,student,teacher,course c where teacher.tname='李子' and teacher.tid=c.tid and c.cid=sc.cid and sc.sid=student.sid and sc.score=(select max(score)from sc where sc.cid=c.cid) 41.查詢各個課程及相應的選修人數 select sc.cid ,count(sc.sid)from sc,student where sc.sid=student.sid group by sc.cid 43.查詢每門功成績最好的前兩名 44.統計每門課程的學生選修人數（超過人的課程才統計）。要求輸出課程號和選修人數，查詢結果按人數降序排列，查詢結果按人數降序排列，若人數相同，按課程號升序排列 select sc.cid,count(sc.cid)from sc,course where sc.cid=course.cid group by sc.cid order by sc.cid desc 45.檢索至少選修兩門課程的學生學號 SELECT sid FROM sc group by sid having count(*) > = 2 rownum的用法 查詢所有成績第二名到第四名的成績 select * from （select rownum p,t.score from（SELECT s.score score FROM sc s ORDER BY score desc）t ）tt where tt.p>1 and tt.p<5 47.查詢沒學過“葉平”老師講授的任一門課程的學生姓名 select distinct sid from sc where sid not in(select sc.sid from sc,course,teacher where sc.cid=course.cid and course.tid=teacher.tid and teacher.tname='楊巍巍') 48.查詢兩門以上不及格課程的同學的學號及其平均成績 49.檢索“”課程分數小於，按分數降序排列的同學學號 select sc.sid from sc,course where sc.cid=course.cid and course.cname='java' and sc.score<90 50.刪除“”同學的“”課程的成績 delete from sc where sid=1 and cid=1
