目錄

練習題?

題目一

題目二?

題目三?

題目四?

題目五?

題目六?

題目七?

題目八?

題目九

題目十

---

練習題?

題目一

找出年齡小于20歲且是“物理學院”的學生的學號、姓名、院系名稱,按學號排序

create or replace view test6_01 as
select S.sid,S.name,S.dname
from pub.student S
where S.age<20 and
S.dname='物理學院'
order by S.sid

關鍵點：

1、create or replace：可以創建或者替代table和view，方便調試錯誤

題目二?

查詢統計2009級、軟件學院所有學生的學號、姓名、總成績（列名sum_score）（如果有學生沒有選一門課，則總成績為空值）

create or replace view test6_02 as
select S.sid,S.name,sum(SC.score) sum_score
from pub.student S left outer join pub.student_course SC
on S.sid=SC.sid
where S.dname='軟件學院' andS.class=2009
group by S.sid,S.name

關鍵點：

1、left outer join 需要利用on來確定連接的原則

2、select結果集、view和table三者都是不同的。select結果集是一次性顯示的在數據庫中不占有任何內存；view在數據庫中占有非常小的內存，因為它并不存儲數據而是動態從table中獲取；table是真實存儲數據的，是占有內存的

題目三?

查詢所有課的最高成績、最高成績人數，test6_06有四個列：課程號cid、課程名稱name、最高成績max_score、最高成績人數max_score_count（一個學生同一門課成績都是第一，只計一次,需要考慮刷成績情況，一個同學選了一個課程多次，兩次都是最高分。如果結果錯一行，可能就是沒有考慮這種情況，這里是為了考核“去重復計數”知識點的）。如果沒有學生選課，則最高成績為空值,最高成績人數為零

提示：參考講義關于標量子查詢（只返回包含單個屬性的單個元組）

create or replace view test6_03 as
select*
from
(select C.cid,C.name,max(SC.max_score) max_scorefrom pub.course C left outer join (select sid,cid,max(score) max_scorefrom pub.student_coursegroup by sid,cid)SCon C.cid=SC.cidgroup by C.cid,C.name
) 
natural full outer join
(select SC.cid,count(sid) max_score_countfrom (select sid,cid,max(score) max_scorefrom pub.student_coursegroup by sid,cid)SCgroup by SC.cid,SC.max_scorehaving (SC.max_score=(select max(score) from pub.student_course tempwhere SC.cid=temp.cid))
)

關鍵點：

1、多個select結果處理：每一個sql程序只能有一個最終的select，而不能重復顯示select多次的結果（結果會覆蓋從而報錯）；如果select結果要按行合并則用union（刪除重復行）/union all（不刪除重復行）；如果select結果要按列合并，則可以用join、natural full outer join

2、select結果是臨時的不是表也不是視圖，所以要將select結果合并需要把select語句放在from中當成一個臨時關系來處理

3、當select提取較為復雜時，可以考慮分開兩個select語句處理，再將select結果合并處理

4、沒有學生考試的課程也要呈現出課程的cid和name，就是后面的max_score以及count用NULL來處理。用left outer join/full outer join來完成這一功能

題目四?

找出選修了“操作系統”并取得學分或者選修“數據結構”并且取得學分，但是沒有選修“程序設計語言”或者沒有取得這門課的學分的男學生的學號、姓名

create or replace view test6_04 as
select SC.sid,S.name
from pub.student_course SC,pub.student S,pub.course C
where SC.sid=S.sid and SC.cid=C.cid
and SC.score>=60 and (C.name='操作系統' or C.name='數據結構'）
and S.sex='男' and (SC.sid not in(select SC.sidfrom pub.student_course SC,pub.course Cwhere SC.cid=C.cid andC.name='程序設計語言' andSC.score>=60))

關鍵點：

1、對于或的關系可以直接在where 的條件中利用or來表達，但是對于與的關系不能在where中利用and來寫，而是要select后將結果取交集（intersect）

2、沒有 或 沒有 =有 與 有?取一個否定?。所以在實現上直接在where上利用not in 來實現

題目五?

查詢20歲的所有有選課的學生的學號、姓名、平均成績(avg_score，此為列名，下同)（平均成績四舍五入到個位）、總成績(sum_score)

Test6_05有四個列，并且列名必須是：sid、name、avg_score、sum_score。通過下面方式實現列名定義：

create or replace view test6_05 as select sid,name，（表達式） avg_score,（表達式） sum_score? from ……

create or replace view test6_05 as
select S.sid sid,S.name name,round(avg(score),0) avg_score,round(sum(score),0) sum_score
from pub.student S,pub.student_course SC
where S.sid=SC.sid and S.age=20
group by S.sid,S.name

關鍵點：

1、四舍五入處理round函數：round（number，digit）

digit>0：四舍五入到第digit位小數

digit=0：四舍五入到整數

digit<0：在整數位置四舍五入（-1：四舍五入到十位；-2：四舍五入到百位）

題目六?

找出同一個同學同一門課程有兩次或以上不及格的所有學生的學號、姓名（即一門課程需要補考兩次或以上的學生的學號、姓名）

create or replace view test6_06 as
select S.sid,S.name
from pub.student S,pub.student_course SC
where S.sid=SC.sid and
SC.score<60
group by S.sid,S.name,SC.cid
having (count(*)>=2)

關鍵點：

1、這里查找的對象是一個學生在一門課上的所有考試記錄?

題目七?

找出選修了所有課程并且每門課程每次考試成績均及格的學生的學號、姓名。（題6的延伸和鞏固）

create or replace view test6_07 as
select distinct S.sid,S.name
from pub.student S,pub.student_course SC
where S.sid=SC.sid and S.sid in(select sidfrom pub.student_course SCwhere not exists(select cidfrom pub.course Cminusselect cidfrom pub.student_course Tempwhere Temp.sid=SC.sid)
) and S.sid not in(select sidfrom pub.student_course SCwhere SC.score<60
)

關鍵點：

1、選修了所有課程就是除法的應用

2、所有成績都及格查找對象是一個學生在一門課上的所有考試記錄?

題目八?

找出選修了所有課程并且得到所有課程的學分（即每門課程最少一次考試及格）的學生的學號、姓名。（題6的 延伸和鞏固）

create or replace view test6_08 as
with T as(select sid, cid, MAX(score) AS max_scorefrom pub.student_coursegroup by sid, cid
) 
select distinct S.sid,S.name
from pub.student S,T SC
where S.sid=SC.sid and S.sid in(select sidfrom T SCwhere not exists(select cidfrom pub.course Cminusselect cidfrom T Tempwhere Temp.sid=SC.sid)
) and S.sid not in(select sidfrom T SCwhere SC.max_score<60
)

關鍵點：

1、本題和上一題不同點在于：查找的對象轉變為：一個學生在一門課上的最高成績

題目九

查詢統計2010級、化學與化工學院的學生總學分表，內容有學號、姓名、總學分sum_credit。（不統計沒有選課的學生）

create or replace view test6_09 as
select S.sid,S.name,sum(C.credit) sum_credit
from pub.student_course SC,pub.student S,pub.course C
where SC.cid=C.cid and SC.sid=S.sid
and S.dname='化學與化工學院'
and S.class=2010 and SC.score>=60
group by S.sid,S.name

題目十

查詢學生表中每一個姓氏及其人數（不考慮復姓，用到取子串函數substr(string,postion,length)）），test6_10有兩個列：second_name、p_count

create or replace view test6_10 as
select substr(S.name,1,1) second_name,count(*) p_count
from pub.student S
group by substr(S.name,1,1)

關鍵點：

1、group by后面可以跟表達式（例如：to_char(trunc(sc.score, -1), 'fm000') || '-' || to_char(trunc(sc.score, -1) + 9, 'fm000') as score）、字段（substr）

?總結?

本文的所有題目均來自《數據庫系統概念》（黑寶書）、山東大學數據庫實驗六。不可用于商業用途轉發。

如果能幫助到大家，大家可以點點贊、收收藏呀~?