/* 實驗四 SQL 數(shù)據查詢 */- 1. 列出不及格記錄的學生名單SELECT sname as 姓名FROM STUDENT s JOIN SC ON s.snum=sc.snum AND score<60- 2. 列出選修了計算機系課程的學生名單SELECT DISTINCT sname AS 姓名FROM STUDENT s JOIN SC ON s.snum=sc.snum JOIN SECTIONS ON sections.secnum=SC.secnum JOIN COURSE ON COURSE.cnum=SECTIONS.cnum WHERE COURSE.dept='計算機系'- 3. 檢索選修了數(shù)據庫技術課程的學生姓名和系別SELECT sname 姓名,s.dept 系別FROM STUDENT s JOIN SC ON s.snum=sc.snum JOIN SECTIONS ON sections.secnum=SC.secnum JOIN COURSE ON COURSE.cnum=SECTIONS.cnum AND cname='數(shù)據庫技術'-4. 選修了所有課程的學生名單SELECT sname 姓名FROM student sWHERE not exists(SELECT * FROM course where cnum not in(SELECT cnum FROM sc JOIN sections ON sc.secnum=sections.secnum WHERE sc.snum=s.snum)- 5. 檢索每門課程都在80分以上的學生名單-答案一：SELECT sname 姓名FROM student WHERE snum not in (SELECT snum FROM sc WHERE score <=80) -答案二：SELECT sname 姓名 FROM student JOIN sc ON student.snum=sc.snumGROUP BY snameHAVING min(score)>80-6.檢索獲獎學金的學生名單（每門課程在80分以上，平均成績在90分以上）。SELECT sname 姓名FROM student JOIN sc ON student.snum=sc.snumGROUP BY snameHAVING min(score)>80 AND AVG(cast(score as real)>90-7. 檢索選修了“大學英語”的學生名單和成績，并按成績從高到低排列SELECT sname 姓名, score 成績FROM student JOIN sc ON student.snum=sc.snum JOIN sections ON sc.secnum = sections.secnum JOIN course ON um=um AND cname='大學英語'ORDER BY score DESC-8. 統(tǒng)計每門課程的選修人數(shù)，輸出列名為課程號、人數(shù)-答案一：SELECT um 課程號, count(snum) 人數(shù)FROM sc JOIN sections ON sc.secnum =sections.secnum right JOIN course ON um=um GROUP BY all um-答案二：SELECT um 課程號, count(snum) 人數(shù)FROM sc,sections,courseWHERE sc.secnum =sections.secnum AND um=um GROUP BY all um-9. 查詢選修了數(shù)據庫技術、沒有選修高等數(shù)學的學生姓名和系別SELECT sname 姓名, dept 系別FROM student WHERE snum in (SELECT snum FROM sc JOIN sections on sc.secnum =sections.secnum JOIN course on um=um AND cname='數(shù)據庫技術') AND snum not in (SELECT snum FROM sc JOIN sections on sc.secnum =sections.secnum JOIN course on um=um AND cname='高等數(shù)學') -10. 檢索使用高等教育出版社出版的教材的課程名SELECT cname 課程名FROM courseWHERE textbook like '%高等教育出版社%' -11. 統(tǒng)計所有課程的最高成績、最低成績和平均成績。-答案一：SELECT cname 課程名, MAX(score) 最高成績, MIN(score) 最低成績, AVG(score) 平均成績FROM sc JOIN sections ON sc.secnum=sections.secnum RIGHT JOIN course ON um=umGROUP BY ALL cname-答案二：SELECT cname 課程名, MAX(score) 最高成績, MIN(score) 最低成績, AVG(score) 平均成績FROM sc , sections,courseWHERE sc.secnum=sections.secnum AND um=umGROUP BY ALL cname-12. 統(tǒng)計每門課程的選修人數(shù)及不及格人數(shù)-答案一：select ame as 課程名,選課數(shù),不及格人數(shù)from(select cname ,count(st.snum) as 選課數(shù) from student st,sc,sections se,course co where st.snum=sc.snum and se.secnum=sc.secnum and um=um group by ame) as co1 JOIN(select cname , count(*) as 不及格人數(shù) from student st,sc,sections se,course co where st.snum=sc.snum and se.secnum=sc.secnum and um=um and score<60 group by all ame) as co2 ON ame=ame-答案二：select cname as 課程名 ,count(st.snum) as 選課數(shù),sum(1-score/60) as 不及格人數(shù) from student st,sc,sections se,course co where st.snum=sc.snum and se.secnum=sc.secnum and um=um group by ame-答案三：select cname as 課程名 ,count(st.snum) as 選課數(shù),sum(case when score>=60 then 0else 1 end) as 不及格人數(shù) from student st,sc,sections se,course co where st.snum=sc.snum and se.secnum=sc.secnum and um=um group by ame-13. 查詢土木工程系、交通工程系和城市規(guī)劃系的學生學號和姓名 -答案一： SELECT dept 系別,snum 學號, sname 姓名 FROM student WHERE dept like '土木工程%' or dept like '交通工程%' or dept like '城市規(guī)劃%'-答案二： SELECT dept 系別,snum 學號, sname 姓名 FROM student WHERE dept in ( '土木工程','交通工程','城市規(guī)劃')-以下三題用集合運算符完成查詢操作： -14. 查詢選修了數(shù)據庫技術或選修了多媒體的學生學號 (SELECT snum 學號 FROM sc JOIN sections ON sc.secnum =sections.secnum JOIN course ON um=um AND cname='數(shù)據庫技術')UNION(SELECT snum 學號 FROM sc JOIN sections ON sc.secnum =sections.secnum JOIN course ON um=um AND cname='多媒體技術')-15. 查詢計算機系且年齡不大于19歲的學生信息SELECT * FROM student WHERE dept='計算機'INTERSECTSELECT * FROM studentWHERE DATEDIFF(YEAR,birthday,GETDATE()<=19-16. 查詢計算機系且年齡大于19歲的學生信息-答案一：SELECT * FROM student WHERE dept='計算機'INTERSECTSELECT * FROM studentWHERE DATEDIFF(YEAR,birthday,GETDATE()>19-答案二：SELECT * FROM student WHERE dept='計算機'EXCEPTSELECT * FROM studentWHERE DATEDIFF(YEAR,birthday,GETDATE()<=19