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