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