《數(shù)據(jù)庫技術(shù)與應(yīng)用：實驗四 SQL 數(shù)據(jù)查詢》由會員分享，可在線閱讀，更多相關(guān)《數(shù)據(jù)庫技術(shù)與應(yīng)用：實驗四 SQL 數(shù)據(jù)查詢（7頁珍藏版）》請在裝配圖網(wǎng)上搜索。
1、
/* 實驗四 SQL 數(shù)據(jù)查詢 */
-- 1. 列出不及格記錄的學(xué)生名單
SELECT sname as 姓名
FROM STUDENT s JOIN SC ON s.snum=sc.snum AND score<60
-- 2. 列出選修了計算機系課程的學(xué)生名單
SELECT DISTINCT sname AS 姓名
FROM STUDENT s JOIN SC ON s.snum=sc.snum
JOIN SECTIONS ON sections.secnum=SC.secnum
JOIN COURSE
2、ON COURSE.cnum=SECTIONS.cnum
WHERE COURSE.dept='計算機系'
-- 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ù)'
3、
--4. 選修了所有課程的學(xué)生名單
SELECT sname 姓名
FROM student s
WHERE 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分以上的學(xué)生名單
--答案一：
4、
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.snum
GROUP BY sname
HAVING min(score)>80
--6.檢索獲獎學(xué)金的學(xué)生名單（每門課程在80分以上，平均成績在90分以上）。
SELECT sname 姓名
FRO
5、M student JOIN sc ON student.snum=sc.snum
GROUP BY sname
HAVING min(score)>80 AND AVG(cast(score as real))>90
--7. 檢索選修了“大學(xué)英語”的學(xué)生名單和成績，并按成績從高到低排列
SELECT sname 姓名, score 成績
FROM student JOIN sc ON student.snum=sc.snum
JOIN sections ON sc.secnum = sections.secnum
JOIN
6、course ON um=um AND cname='大學(xué)英語'
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,sect
7、ions,course
WHERE 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 (SELECT snum FROM sc JOIN sections on sc.secnum =sections.secnum
JOIN course on um=um
8、 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 course
WHERE textbook like '%
9、高等教育出版社%'
--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=um
GROUP BY ALL cname
--答案二：
SELECT cname 課程名, MAX(score) 最高成績, MIN(score) 最低成績,
10、 AVG(score) 平均成績
FROM sc , sections,course
WHERE sc.secnum=sections.secnum AND um=um
GROUP 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=
11、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
12、(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 0
else 1 end) as 不及格人數(shù)
from student st,sc,sections se,course
13、co
where st.snum=sc.snum and se.secnum=sc.secnum and um=um
group by ame
--13. 查詢土木工程系、交通工程系和城市規(guī)劃系的學(xué)生學(xué)號和姓名
--答案一：
SELECT dept 系別,snum 學(xué)號, sname 姓名
FROM student
WHERE dept like '土木工程%' or dept like '交通工程%' or dept like '城市規(guī)劃%'
--答案二：
SELECT dept 系別,snum 學(xué)號, sname 姓名
FROM
14、 student
WHERE dept in ( '土木工程','交通工程','城市規(guī)劃')
--以下三題用集合運算符完成查詢操作：
--14. 查詢選修了數(shù)據(jù)庫技術(shù)或選修了多媒體的學(xué)生學(xué)號
(SELECT snum 學(xué)號
FROM sc JOIN sections ON sc.secnum =sections.secnum
JOIN course ON um=um
AND cname='數(shù)據(jù)庫技術(shù)')
UNION
(SELECT snum 學(xué)號
FROM sc JOIN sections ON sc.secnum =sect
15、ions.secnum
JOIN course ON um=um
AND cname='多媒體技術(shù)')
--15. 查詢計算機系且年齡不大于19歲的學(xué)生信息
SELECT *
FROM student
WHERE dept='計算機'
INTERSECT
SELECT *
FROM student
WHERE DATEDIFF(YEAR,birthday,GETDATE())<=19
--16. 查詢計算機系且年齡大于19歲的學(xué)生信息
--答案一：
SELECT *
FROM student
WHERE dept='計算機'
INTERSECT
SELECT *
FROM student
WHERE DATEDIFF(YEAR,birthday,GETDATE())>19
--答案二：
SELECT *
FROM student
WHERE dept='計算機'
EXCEPT
SELECT *
FROM student
WHERE DATEDIFF(YEAR,birthday,GETDATE())<=19
數(shù)據(jù)庫技術(shù)與應(yīng)用：實驗四 SQL 數(shù)據(jù)查詢
