北大“數(shù)據(jù)庫(kù)原理”上機(jī)實(shí)踐題目總結(jié)（14） -自考串講筆記

　　/*3 A、列出銷(xiāo)售了所有顏色為“Red”的商品的部門(mén)名 */

　　select 部門(mén)。部門(mén)名 from 銷(xiāo)售記錄，部門(mén)，商品 where 銷(xiāo)售記錄。商品號(hào)=商品。商品號(hào) and 銷(xiāo)售記錄。部門(mén)號(hào)=部門(mén)。部門(mén)號(hào)

　　and 商品。商品顏色='red' group by 部門(mén)。部門(mén)名

　　/* B、列出每個(gè)供應(yīng)商供應(yīng)給每個(gè)部門(mén)的商品總數(shù)量，并按總量由大到小排列。 */

　　select 供應(yīng)商。供應(yīng)商名，部門(mén)。部門(mén)名，sum（供應(yīng)記錄。商品數(shù)量）as 總供應(yīng)量 from 供應(yīng)記錄，供應(yīng)商，部門(mén) where 供應(yīng)記錄。部門(mén)號(hào)=部門(mén)。部門(mén)號(hào)

　　and 供應(yīng)商。供應(yīng)商號(hào)=供應(yīng)記錄。供應(yīng)商號(hào) group by 供應(yīng)商。供應(yīng)商名，部門(mén)。部門(mén)名 order by 總供應(yīng)量 desc

　　/* C、列出銷(xiāo)售商品的總量排在第一位的部門(mén)名稱

　　*/

　　select 部門(mén)。部門(mén)名 from 銷(xiāo)售記錄，部門(mén) where 銷(xiāo)售記錄。部門(mén)號(hào)=部門(mén)。部門(mén)號(hào)

　　group by 部門(mén)。部門(mén)名 having sum（銷(xiāo)售記錄。數(shù)量）>=all （select sum（銷(xiāo)售記錄。數(shù)量）

　　from 銷(xiāo)售記錄 group by 部門(mén)號(hào) ）

　　以上程序在“SQL Server 2000”中調(diào)試運(yùn)行成功！

　　某個(gè)學(xué)生運(yùn)動(dòng)會(huì)比賽信息的數(shù)據(jù)庫(kù)原理上機(jī)練習(xí)及答案

　　有某個(gè)學(xué)生運(yùn)動(dòng)會(huì)比賽信息的數(shù)據(jù)庫(kù)，保存了如下的表：

　　運(yùn)動(dòng)員（運(yùn)動(dòng)員編號(hào)，運(yùn)動(dòng)員姓名，運(yùn)動(dòng)員性別，所屬系號(hào)）

　　項(xiàng)目（項(xiàng)目編號(hào)，項(xiàng)目名稱，項(xiàng)目比賽地點(diǎn)）

　　成績(jī)（運(yùn)動(dòng)員編號(hào)，項(xiàng)目編號(hào)，積分）

　　athlete（a#，aname，sex，dno）

　　project（p#，pname，address）

　　grade（a#，p#，jf）

　　DROP TABLE W193_GRADE

　　DROP TABLE W193_PROJECT

　　DROP TABLE W193_ATHLETE

　　請(qǐng)用SQL語(yǔ)句完成如下功能：

　　1. 建表，注意滿足如下要求：

　　W193

　　a） 表名必須加自己的帳號(hào)作前綴，比如“d10000001運(yùn)動(dòng)員”，

　　b） 定義各個(gè)表的主碼外碼約束

　　c） 運(yùn)動(dòng)員的姓名和所屬系別不能為空值。

　　d） 積分要么為空值，要么為6，4，2，0， 分別代表第一，二，三名和其他名次的積分，

　　注意名次可以有并列名次，后面的排名不往前提升，例如，如果有兩個(gè)并列第一，則沒(méi)有第二名。

　　CREATE TABLE W193_ATHLETE

　　（A# CHAR（8），

　　ANAME CHAR（12） NOT NULL，

　　SEX CHAR（2），

　　DNO CHAR（16） NOT NULL，

　　PRIMARY KEY （A#））

　　CREATE TABLE W193_PROJECT

　　（P# CHAR（8），

　　PNAME VARCHAR（18），

　　ADDRESS VARCHAR（16），

　　PRIMARY KEY （P#））

　　CREATE TABLE W193_GRADE

　　（A# CHAR（8），

　　P# CHAR（8），

　　JF INT，

　　PRIMARY KEY（A#，P#），

　　FOREIGN KEY （A#） REFERENCES W193_ATHLETE（A#），

　　FOREIGN KEY （P#） REFERENCES W193_PROJECT（P#） ON DELETE CASCADE，

　　CHECK （JF=NULL OR JF=6 OR JF=4 OR JF=2 OR JF=0））

　　DROP TABLE W193_GRADE

　　DROP TABLE W193_ATHLETE

　　DROP TABLE W193_PROJECT

　　athlete（a#，aname，sex，dno）

　　project（p#，pname，address）

　　grade（a#，p#，jf）