《數(shù)據(jù)庫技術(shù)與應(yīng)用：實(shí)驗(yàn)四 SQL 數(shù)據(jù)查詢》由會(huì)員分享，可在線閱讀，更多相關(guān)《數(shù)據(jù)庫技術(shù)與應(yīng)用：實(shí)驗(yàn)四 SQL 數(shù)據(jù)查詢（7頁珍藏版）》請(qǐng)?jiān)谘b配圖網(wǎng)上搜索。

1、/* 實(shí)驗(yàn)四 SQL 數(shù)據(jù)查詢 */- 1. 列出不及格記錄的學(xué)生名單SELECT sname as 姓名FROM STUDENT s JOIN SC ON s.snum=sc.snum AND score60- 2. 列出選修了計(jì)算機(jī)系課程的學(xué)生名單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=計(jì)算機(jī)系

2、- 3. 檢索選修了數(shù)據(jù)庫技術(shù)課程的學(xué)生姓名和系別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ù)據(jù)庫技術(shù)-4. 選修了所有課程的學(xué)生名單SELECT sname 姓名FROM student sWHERE not exists(SELECT * FROM course where cnum not in(SELECT cn

3、um FROM sc JOIN sections ON sc.secnum=sections.secnum WHERE sc.snum=s.snum)- 5. 檢索每門課程都在80分以上的學(xué)生名單-答案一：SELECT sname 姓名FROM student WHERE snum not in (SELECT snum FROM sc WHERE score 80-6.檢索獲獎(jiǎng)學(xué)金的學(xué)生名單（每門課程在80分以上，平均成績(jī)?cè)?0分以上）。SELECT sname 姓名FROM student JOIN sc ON student.snum=sc.snumGROUP BY snameHAVIN

4、G min(score)80 AND AVG(cast(score as real)90-7. 檢索選修了“大學(xué)英語”的學(xué)生名單和成績(jī)，并按成績(jī)從高到低排列SELECT sname 姓名, score 成績(jī)FROM student JOIN sc ON student.snum=sc.snum JOIN sections ON sc.secnum = sections.secnum JOIN course ON um=um AND cname=大學(xué)英語ORDER BY score DESC-8. 統(tǒng)計(jì)每門課程的選修人數(shù)，輸出列名為課程號(hào)、人數(shù)-答案一：SELECT um 課程號(hào), count(

5、snum) 人數(shù)FROM sc JOIN sections ON sc.secnum =sections.secnum right JOIN course ON um=um GROUP BY all um-答案二：SELECT um 課程號(hào), count(snum) 人數(shù)FROM sc,sections,courseWHERE sc.secnum =sections.secnum AND um=um GROUP BY all um-9. 查詢選修了數(shù)據(jù)庫技術(shù)、沒有選修高等數(shù)學(xué)的學(xué)生姓名和系別SELECT sname 姓名, dept 系別FROM student WHERE snum in (

6、SELECT snum FROM sc JOIN sections on sc.secnum =sections.secnum JOIN course on um=um AND cname=數(shù)據(jù)庫技術(shù)) AND snum not in (SELECT snum FROM sc JOIN sections on sc.secnum =sections.secnum JOIN course on um=um AND cname=高等數(shù)學(xué)) -10. 檢索使用高等教育出版社出版的教材的課程名SELECT cname 課程名FROM courseWHERE textbook like %高等教育出版社

7、% -11. 統(tǒng)計(jì)所有課程的最高成績(jī)、最低成績(jī)和平均成績(jī)。-答案一：SELECT cname 課程名, MAX(score) 最高成績(jī), MIN(score) 最低成績(jī), AVG(score) 平均成績(jī)FROM sc JOIN sections ON sc.secnum=sections.secnum RIGHT JOIN course ON um=umGROUP BY ALL cname-答案二：SELECT cname 課程名, MAX(score) 最高成績(jī), MIN(score) 最低成績(jī), AVG(score) 平均成績(jī)FROM sc , sections,courseWHERE s

8、c.secnum=sections.secnum AND um=umGROUP BY ALL cname-12. 統(tǒng)計(jì)每門課程的選修人數(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 不及格

9、人數(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 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ī)劃系的學(xué)生學(xué)號(hào)和姓名 -答案一： SELECT dept

10、系別,snum 學(xué)號(hào), sname 姓名 FROM student WHERE dept like 土木工程% or dept like 交通工程% or dept like 城市規(guī)劃%-答案二： SELECT dept 系別,snum 學(xué)號(hào), sname 姓名 FROM student WHERE dept in ( 土木工程,交通工程,城市規(guī)劃)-以下三題用集合運(yùn)算符完成查詢操作： -14. 查詢選修了數(shù)據(jù)庫技術(shù)或選修了多媒體的學(xué)生學(xué)號(hào) (SELECT snum 學(xué)號(hào) FROM sc JOIN sections ON sc.secnum =sections.secnum JOIN cour

11、se ON um=um AND cname=數(shù)據(jù)庫技術(shù))UNION(SELECT snum 學(xué)號(hào) FROM sc JOIN sections ON sc.secnum =sections.secnum JOIN course ON um=um AND cname=多媒體技術(shù))-15. 查詢計(jì)算機(jī)系且年齡不大于19歲的學(xué)生信息SELECT * FROM student WHERE dept=計(jì)算機(jī)INTERSECTSELECT * FROM studentWHERE DATEDIFF(YEAR,birthday,GETDATE()19-答案二：SELECT * FROM student WHERE dept=計(jì)算機(jī)EXCEPTSELECT * FROM studentWHERE DATEDIFF(YEAR,birthday,GETDATE()=19