資料庫單錶查詢

實驗五：資料庫單錶查詢

一、實驗目的

1. 掌握select語句的基本語法和查詢條件表示方法；

2. 掌握查詢條件表示式和使用方法；

3. 掌握group by 子句的作用和使用方法；

4. 掌握h**ing子句的作用和使用方法；

5. 掌握order by子句的作用和使用方法。

請使用t-sql 語句實現以下操作：

1. 列出所有不姓劉的所有學生；

select * from student where sname not like '劉%

2. 列出姓「沈」且全名為3個漢字的學生；

select * from student1 where sname like'沈__'

3. 顯示在2023年以後出生的學生的基本資訊；

select * from student where year(getdate())-sage>1985

4. 按照「性別、學號、姓名、年齡、院系」的順序列出學生資訊，其中性別按以下規定顯示：性別為男顯示為男生，性別為女顯示為女生，其他顯示為「條件不明」；

select 性別= case when s***='男' then'男生' when s***='女' then'女生' else '條件不明' end,sno 學號,sname 碼,sage 年齡,sdept 院系 from student

5. 查詢出課程名含有「資料」字串的所有課程基本資訊；

select * from course where cname like '%資料%」

6. 顯示學號第八位或者第九位是1、2、3、4或者9的學生的學號、姓名、性別、年齡及院系；

from student where sno like1,2,3,4,9][1,2,3,4,9]%」

7. 列出選修了『1』課程的學生，按成績的降序排列；

select sc from sc where '1' order by grade des

8. 列出同時選修「1」號課程和「2」號課程的所有學生的學號；

select sno from sc where cno = '1' intersetc select sno

from sc where cno = '2'

9. 列出課程表中全部資訊，按先修課的公升序排列；

select * from course order by cpno asc

10. 列出年齡超過平均值的所有學生名單，按年齡的降序顯示；

select * from student where sage> ( select **g(sage) from student ) order by sage desc

11. 按照出生年份公升序顯示所有學生的學號、姓名、性別、出生年份及院系，在結果集中列標題分別指定為「學號，姓名，性別，出生年份，院系」；

select sno 學號,sname 姓名,s*** 性別,year(getdate ())-sage 出生年份,sdept 所在院系 from student order by year(getdate ())-sage

12. 按照院系降序顯示所有學生的「院系，學號、姓名、性別、年齡」等資訊，其中院系按照以下規定顯示：院系為cs顯示為計算機系，院系為is顯示為資訊系，院系為ma顯示為數學系，院系為en顯示為外語系，院系為cm顯示為中醫系，院系為wm顯示為西醫系，其他顯示為院系不明；

select sdept= case when sdept='cs' then '計算機系' when sdept='is' then '資訊系' when sdept='ma' then '數學系' when sdept='en' then '外語系' when sdept='cm' then '中醫系' when sdept='wm' then '西醫系' else '條件不明' end ,sno,sname,s***,sage from student order by sdept desc

13. 顯示所有院系（要求不能重複，不包括空值），並在結果集中增加一列字段「院系規模」，其中若該院系人數》=5則該字段值為「規模很大」，若該院系人數大於等於4小於5則該字段值為「規模一般」，若該院系人數大於等於2小於4則該字段值為「規模稍小」，否則顯示「規模很小」；

select sdept ,院系規模= case when count(sno)>=5 then'規模很大' when count(sno)>=4then'規模一般' when count(sno)>=2then'規模稍小' else '規模很小' end from student where sdept is not null group by sdept

14. 按照課程號、成績降序顯示課程成績在70-80之間的學生的學號、課程號及成績；

select sno,cno,grade from sc where grade between 70 and 80 order by cno,grade dese

15. 顯示學生資訊表中的學生總人數及平均年齡，在結果集中列標題分別指定為「學生總人數，平均年齡」；

select count(*)學生總人數,**g(sage) 平均年齡 from student

16. 顯示選修的課程數大於3的各個學生的選修課程數；

select sno 學號,count(sno)選修課程數 from sc group by sno h**ing count(*)>=3

17. 按課程號降序顯示選修各個課程的總人數、最高成績、最低成績及平均成績；

use student select cno '課程號',count(*)'總人數',max(grade)'最高分',min(grade)'最低分',**g(grade)'平均分' from sc group by cno order by cno desc

18. 顯示平均成績大於「200515001」學生平均成績的各個學生的學號、平均成績；

use student select sno '學號',**g(grade)'平均成績' from sc group by sno h**ing **g(grade)>( select **g(grade) from sc where sno='200515001」

19. 顯示選修各個課程的及格的人數、及格比率；

use student select cno'課程號' ,count(*)'及格人數',cast(cast(count(case when grade>=60 then 1 end)as float)/count(*)as float(1))'及格率' from sc group by cno

20. 顯示選修課程數最多的學號及選修課程數最少的學號；

use student select sno '學號',count(*)'選修課程數' from sc group by sno h**ing count(cno)>=all ( select count(*) from sc group by sno ) union select sno '學號',count(*)'選修課程數' from sc group by sno h**ing count(cno)<=all ( select count(*) from sc group by sno)

21. 顯示各個院系男女生人數，其中在結果集中列標題分別指定為「院系名稱、男生人數、女生人數」；

select sdept ,count(case when s***='女'then 1 end)'女生人數', count(case when s***='男'then 1 end)'男生人數' from student group by sdept,s***

22. 列出有二門以上課程（含兩門）不及格的學生的學號及該學生的平均成績；

select sno 學號,**g(grade)平均成績 from sc group by sno h**ing count(case when grade<60 then 1 end)>=2

六、出現問題及解決辦法

如：某些查詢操作無法執行，如何解決？

資料庫單錶查詢

實驗三資料庫SQL基本表查詢

資料庫實驗三查詢

實驗一資料庫查詢

資料庫單錶查詢

實驗三資料庫SQL基本表查詢

資料庫實驗三 查詢

實驗一資料庫查詢

相關推薦

資料庫實驗三查詢