|
南陽師范學院2013-2014（1）數據庫課程 教育技術學12.4班 SQL語句競賽 （提示：所有的操作均用SQL語句完成。） 有某個學生運動會比賽信息的數據庫，保存了如下的表： 運動員（運動員編號，運動員姓名，運動員性別，所屬系名） 項目（項目編號，項目名稱，項目比賽地點） 成績（運動員編號，項目編號，積分） 請用SQL語句完成如下功能： 1． 建立數據庫，數據庫名稱為y_x_c（4分） Create database y_x_c 2. 建立數據表，并滿足如下條件：（30分） （1）定義各個表的主碼、外碼約束 （2）運動員的姓名和所屬系別不能為空值。 （3）積分要么為空值，要么為6,4,2,0,分別代表第一，二，三名和其他名次的積分。 Create table 運動員( 運動員編號 char(8) primary key, 運動員姓名 char(20) not null, 運動員性別 check(運動員姓名 in('男','女'), 所屬系名 varchar(100) not null ) Create table 項目( 項目編號 char(6) primary key, 項目名稱 varchar(50), 項目比賽地點 varchar(50)) Create table 成績( 運動員編號 char(8) foreign key references 運動員(運動員編號), 項目編號 char(6) foreign key references 項目(項目編號), 積分 tinyint check(積分 in(6,4,2,0)) --注釋：沒有寫not null 就是允許空 ) 3．往表中插入數據：(提示: 每個表只需要書寫插入前面兩行數據的命令即可)（6分） 運動員（1001，李明，男，計算機系 1002，張三，男，數學系 1003，李四，男，計算機系 1004，王二，男，物理系 1005，李娜，女，心理系 1006，孫麗，女，數學系） 項目 （ x001，男子五千米，1操場 x002，男子標槍，1操場 x003，男子跳遠，2操場 x004，女子跳高，2操場 x005，女子三千米，3操場） 成績（ 1001，x001，6 1002，x001，4 1003，x001，2 1004，x001，0 1001，x003，4 1002，x003，6 1004，x003，2 1005，x004，6 1006，x004，4） 這部分比較簡單，省略答案 4．使用完成如下查詢 （1）找出參加了張三所參加的所有項目的其他同學的姓名。（10分） select 運動員姓名 from 運動員 where 運動員姓名!='張三' and 運動員編號 in(select distinct 運動員編號 from 成績 where 項目編號 in(select 項目.項目編號 from dbo.成績,dbo.項目,dbo.運動員 where dbo.成績.運動員編號=dbo.運動員.運動員編號 and dbo.成績.項目編號=dbo.項目.項目編號 and 運動員編號=(select 運動員編號 from 運動員 where 運動員姓名='張三'))) （2）求出目前總積分最高的系名，及其積分。（10分） select 所屬系名,sum(積分) from 運動員,成績 where 運動員.運動員編號=成績.運動員編號 group by 所屬系名 having sum(積分)>=all --注釋： 和any不同，>=all等同于大于等于子查詢中的最大值 (select sum(積分) from 運動員,成績 where 運動員.運動員編號=成績.運動員編號 group by 所屬系名) （3）找出在1操場進行比賽的各項目名稱及其冠軍的姓名。（10分） select 項目名稱,運動員姓名 from 運動員,成績,項目 where 運動員.運動員編號=成績.運動員編號 and 項目.項目編號=成績.項目編號 and項目比賽地點='1操場'and 積分>= (select max(積分) from 成績,項目 where 項目比賽地點='1操場' and 成績.項目編號=項目.項目編號) （4）找出每個項目的最高分及對應的項目編號。（5分） Select項目編號,max(積分) From 項目 Group by 項目編號 5．建立視圖xm_ydy_yx，查詢每個項目的項目名稱、運動員姓名和所屬系名。（10分） create view xm_ydy_yx as select 項目名稱,運動員姓名,所屬系名 from 項目,運動員,成績 where 項目.項目編號=成績.項目編號 and 運動員.運動員編號=成績.運動員編號 6．經查張三因為使用了違禁藥品，其成績都記0分，請在數據庫中做出相應修改。（10分） Update 成績 Set 積分=0 Where 運動員編號=( Select 運動員編號 from 運動員 where 運動員姓名='張三') 7．經組委會協商，需要刪除女子跳高比賽項目。（5分） 這道題看似只需要刪除項目中的數據，實際上并沒有這么簡單。因為項目表作為主表對外表成績表有影響，所以要想刪除女子跳高必須先將參加女子跳高的信息都刪除。 Delete from 成績 Where 項目編號=(select 項目編號 from 項目 where 項目名稱='女子跳高') 然后刪除項目表中的數據： Delete from 項目 Where項目名稱='女子跳高' 8．將男子標槍項目調到2操場進行比賽。 Update 項目 Set項目比賽地點='2操場' Where 項目名稱='男子標槍' |
|
|
