北大“数据库原理”上机考试题

作者：不详　 来源：中国自考网  http://www.csai.cn  2005年10月9日

 　　(一)

　　现要为学校建立一个数据库，设学校环境如下：一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生。.现要建立关于系、学生、班级的数据库，关系模式为：

学生STUDENT （学号，姓名，年龄，班号）

班CLASS （班号，专业名，系名，入学年份）

系 DEPARTMENT （系号，系名）

　　试用SQL语言完成以下功能：

　　1 建表，在定义中要求声明：

　　（1）每个表的主外码。

　　（2）每个班级的人数不能超过30人。

　　（3）学生的年龄介于15到40岁之间。

　　（4）学生姓名不能为空。

　　（5）只有班里没有学生的时候，才能删除该班级。

　　2 插入如下数据

　　CLASS（

　　101，软件，计算机，1995； 102，微电子，计算机，1996；

　　111，无机化学，化学，1995； 112，高分子化学，化学，1996；

　　121，统计数学，数学，1995； 131，现代语言，中文，1996；

　　141，国际贸易，经济，1997； 142，国际金融，经济，1996；

　　）

　　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 学生张三转到化学系111班，请更新相关的表。

　　(二)

　　现有一个商店的数据库，记录顾客及其购物情况，由下面三个表组成：

　　商品（商品号，商品名，单价，商品类别，供应商）；

　　顾客（顾客号，姓名，住址）；

　　购买（顾客号，商品号，购买数量）；

　　试用SQL语言完成下列功能：

　　1 建表，在定义中要求声明：

　　(1)每个表的主外码；

　　(2)顾客的姓名和商品名不能为空值；

　　(3)单价必须大于0，购买数量必须再0到20之间；

　　2 往表中插入数据：

　　商品（M01，佳洁士，8.00，牙膏，宝洁；

　　M02，高露洁，6.50，牙膏，高露洁；

　　M03，洁诺，5.00，牙膏，联合利华；

　　M04，舒肤佳，3.00，香皂，宝洁；

　　M05，夏士莲，5.00，香皂，联合利华；

　　M06，雕牌，2.50，洗衣粉，纳爱斯

　　M07，中华，3.50，牙膏，联合利华；

　　M08，汰渍，3.00，洗衣粉，宝洁；

　　M09，碧浪，4.00，洗衣粉，宝洁；）

　　顾客（C01，Dennis，海淀；

　　C02，John，朝阳；

　　C03，Tom，东城；

　　C04，Jenny，东城；

　　C05，Rick，西城；）

　　购买(C01，M01，3； C01，M05，2；

　　C01，M08，2； C02，M02，5；

　　C02，M06，4； C03，M01，1；
　　
　　C03，M05，1； C03，M06，3；

　　C03，M08，1； C04，M03，7；

　　C04，M04，3； C05，M06，2；

　　C05，M07，8；）

　　商品有9 条,顾客有5条,

　　购买有5条

　　3 用SQL语句完成下列查询：

　　（1）求购买了供应商"宝洁"产品的所有顾客；

　　（2）求购买的商品包括了顾客"Dennis"所购买商品的顾客（姓名）；

　　（3）求牙膏卖出数量最多的供应商。

　　4 将所有的牙膏商品单价增加10%。

　　5 删除从未被购买的商品记录。

　　(三)

　　现有一个公司的部门及雇员的数据库，由下面三个表组成：

　　雇员（雇员号，姓名，年龄，地址，薪水）

　　部门（部门号，部门名，部门经理）

　　工作（雇员号，部门号，在该部门工作的年限）

　　试用SQL语言完成下列功能：

　　①建表，在定义中要求声明

　　⑴每个表的主外码

　　⑵雇员的年龄介于18到55之间

　　⑶雇员姓名和部门名不能为空

　　⑷薪水取1000到50000之间整数

　　②往表中插入数据

　　雇员（E01,tom,35,海淀，8000

　　E02,dennis,24,朝阳,5000

　　E03,john,20,海淀,4000

　　E04,rick,41,朝阳,7000

　　E05,bill,45,西城,7500

　　E06,frank,35,海淀,6000

　　E07,jank,25,东城,3000

　　E08,mike,23,东城,3500）

　　部门（D01,人事,E04

　　D02,信息,E01

　　D03,销售,E05

　　D04,财务,E06）

　　工作（E01,D02,6

　　E01,D03,1

　　E01,D04,2

　　E02,D01,2

　　E02,D04,0

　　E03,D02,0

　　E04,D01,4

　　E04,D02,1

　　E05,D03,2

　　E06,D04,4

　　E07,D04,1

　　E08,D03,2）

　　③用SQL语句完成下列查询

　　⑴求同时在三个部门工作的雇员和姓名

　　⑵列出各部门的职工的工资总额，并按照总额大小降序排列

　　⑶求和其部门经理住址相同的员工的姓名及经理姓名

　　④为财务部门的雇员加薪，5年以下加5%，5年以上加3%

　　⑤删除部门工作年限为0的工作记录，若删除后有雇员没有任何部门工作，删除其雇员记录