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

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

(1)

现要为学校建立一个数据库，设学校环境如下：一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生。 . 现要建立关于系、学生、班级的数据库，关系模式为：
学生 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 班，请更新相关的表。

(2)

现有一个商店的数据库，记录顾客及其购物情况，由下面三个表组成：
商品（商品号，商品名，单价，商品类别，供应商）；
顾客（顾客号，姓名，住址）；
购买（顾客号，商品号，购买数量）；
试用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 删除从未被购买的商品记录。

(3)

现有一个公司的部门及雇员的数据库，由下面三个表组成：
雇员（雇员号，姓名，年龄，地址，薪水）
部门（部门号，部门名，部门经理）
工作（雇员号，部门号，在该部门工作的年限）
试用 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 的工作记录，若删除后有雇员没有任何部门工作，删除其雇员记录