北大“数据库原理”上机考试题（11月5-7日）

作者名：不详　来源：网友提供 06年6月8日

设学校环境如下:一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生。.现要建立关于系、学生、班级的数据库，关系模式为：
班CLASS （班号，专业名，系名，入学年份，人数）
学生STUDENT （学号，姓名，年龄，班号）
系 DEPARTMENT （系号，系名）
试用SQL语言完成以下功能：
1 建表，在定义中要求声明：
（1）每个表的主外码。
（2）每个班级的人数不能超过30人。
（3）学生的年龄介于15到40岁之间。
（4）学生姓名不能为空。
2 插入如下数据
CLASS（
101，软件，计算机，1995，20； 102，微电子，计算机，1996，30；
111，无机化学，化学，1995，29； 112，高分子化学，化学，1996，25；
121，统计数学，数学，1995，20； 131，现代语言，中文，1996，20；
141，国际贸易，经济，1997，30； 142，国际金融，经济，1996，14；
）
STUDENT （
8101，张三，18，101； 8102，钱四，16，121；
8103，王玲，17，131； 8105，李飞，19，102；
8109，赵四，18，141； 8110，李可，20，142
8201，张飞，18，111； 8302，周瑜，16，112；
8203，王亮，17，111； 8305，董庆，19，102；
8409，赵龙，18，101； 8510，李丽，20，142 ）
DEPARTMENT（ 001， 数学； 002， 计算机；
003， 化学； 004， 中文；
005， 经济；）
3 完成以下查询功能
（1）找出所以姓李的学生。
（2）列出所有开设超过两个专业的系的名字。
（3）列出人数大于等于2的系的编号和名字。
4 学校又新增加了一个物理系，编号为006。
5 学生张三退学，请更新相关的表。

有某个学生运动会比赛信息的数据库，保存了如下的表：
运动员（运动员编号，运动员姓名，运动员性别，所属系号）
项目（项目编号，项目名称，项目比赛地点）
成绩（运动员编号，项目编号，积分）
请用SQL语句完成如下功能：
1. 建表，注意满足如下要求：
a) 表名必须加自己的帐号作前缀，比如“d10000001运动员”，
b) 定义各个表的主码外码约束
c) 运动员的姓名和所属系别不能为空值。
d) 积分要么为空值，要么为6,4,2,0, 分别代表第一，二，三名和其他名次的积分，注意名次可以有并列名次，后面的排名不往前提升，例如，如果有两个并列第一，则没有第二名。
2. 往表中插入数据：
运动员（ 1001, 李明，男，计算机系
1002，张三，男，数学系
1003, 李四，男，计算机系
1004, 王二，男，物理系
1005, 李娜，女，心理系
1006, 孙丽，女，数学系）
项目（ x001, 男子五千米，一操场
x002，男子标枪，一操场
x003, 男子跳远，二操场
x004, 女子跳高，二操场
x005, 女子三千米，三操场）
积分（ 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)
3. 完成如下查询
a) 求出目前总积分最高的系名，及其积分。
b) 找出在一操场进行比赛的各项目名称及其冠军的姓名
c) 找出参加了张三所参加的所有项目的其他同学的姓名
4. 经查张三因为使用了违禁药品，其成绩都记0分，请在数据库中作出相应修改。
5. 经组委会协商，需要删除女子跳高比赛项目。