北大“数据库原理”上机实践题目总结（14） -自考串讲笔记

　　/*3 A、列出销售了所有颜色为“Red”的商品的部门名 */

　　select 部门。部门名 from 销售记录，部门，商品 where 销售记录。商品号=商品。商品号 and 销售记录。部门号=部门。部门号

　　and 商品。商品颜色='red' group by 部门。部门名

　　/* B、列出每个供应商供应给每个部门的商品总数量，并按总量由大到小排列。 */

　　select 供应商。供应商名，部门。部门名，sum（供应记录。商品数量）as 总供应量 from 供应记录，供应商，部门 where 供应记录。部门号=部门。部门号

　　and 供应商。供应商号=供应记录。供应商号 group by 供应商。供应商名，部门。部门名 order by 总供应量 desc

　　/* C、列出销售商品的总量排在第一位的部门名称

　　*/

　　select 部门。部门名 from 销售记录，部门 where 销售记录。部门号=部门。部门号

　　group by 部门。部门名 having sum（销售记录。数量）>=all （select sum（销售记录。数量）

　　from 销售记录 group by 部门号 ）

　　以上程序在“SQL Server 2000”中调试运行成功！

　　某个学生运动会比赛信息的数据库原理上机练习及答案

　　有某个学生运动会比赛信息的数据库，保存了如下的表：

　　运动员（运动员编号，运动员姓名，运动员性别，所属系号）

　　项目（项目编号，项目名称，项目比赛地点）

　　成绩（运动员编号，项目编号，积分）

　　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

　　请用SQL语句完成如下功能：

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

　　W193

　　a） 表名必须加自己的帐号作前缀，比如“d10000001运动员”，

　　b） 定义各个表的主码外码约束

　　c） 运动员的姓名和所属系别不能为空值。

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

　　注意名次可以有并列名次，后面的排名不往前提升，例如，如果有两个并列第一，则没有第二名。

　　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）