北大数据库原理上机考题练习及参考答案 练习六班级学生系.docx

北大数据库原理上机考题练习及参考答案 练习六班级学生系.docx

《北大数据库原理上机考题练习及参考答案 练习六班级学生系.docx》由会员分享，可在线阅读，更多相关《北大数据库原理上机考题练习及参考答案 练习六班级学生系.docx（9页珍藏版）》请在冰豆网上搜索。

北大数据库原理上机考题练习及参考答案练习六班级学生系

题目：

设学校环境如下:

一个系有若干个专业，每个专业一年只招一个班，

每个班有若干个学生。

现要建立关于系、学生、班级的数据库，关系模式为：

班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学生张三退学，请更新相关的表。

/*******************************************

 2CreateDB.sql创建第二个题目的数据库。

 *******************************************/

Createdatabasepku2

/************************************************

 2CreateTB.sql分别创建下列三个表：

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

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

 系DEPARTMENT（系号，系名）

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

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

（1）每个表的主外码。

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

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

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

 ************************************************/

usepku2

createtablec

（c#char（4）,

 mnamevarchar（20）,

 dnamevarchar（20）,

 en_yearchar（4）,

 pe_numtinyint,

 primarykey（c#）,

 --foreignkey（d#）referencesd（d#）ondeletecascade,

 check（pe_num<=30））      --班级人数不超过30人

createtables

（s#char（8）,

 snamevarchar（20）notnull,--学生姓名不为空

 agetinyint,

 c#char（4）,

 primarykey（s#）,

 foreignkey（c#）referencesc（c#）ondeletecascade,

 check（age>=15andage<=40））

createtabled

（d#char（8）,

 dnamevarchar（20）,

 primarykey（d#））

/************************************

 2insert.sql分别为表c，s，d插入数据

 ************************************/

usepku2--如果已经在pku2的工作环境下就不用此举了。

        --可以在本页标题栏看到相关信息。

/*为表c插入以下数据：

  101，软件，计算机，1995，20； 

  102，微电子，计算机，1996，30；

  111，无机化学，化学，1995，29； 

  112，高分子化学，化学，1996，25；

  121，统计数学，数学，1995，20；

  131，现代语言，中文，1996，20；

  141，国际贸易，经济，1997，30；

  142，国际金融，经济，1996，14；*/

insertintoc

      values（'101','软件','计算机','1995','20'）

insertintoc

      values（'102','微电子','计算机','1996','30'）

insertintoc

      values（'111','无机化学','化学','1995','29'）

insertintoc

      values（'112','高分子化学','化学','1996','25'）

insertintoc

      values（'121','统计数学','数学','1995','20'）

insertintoc

      values（'131','现代语言','中文','1996','20'）

insertintoc

      values（'141','国际贸易','经济','1997','30'）

insertintoc

      values（'142','国际金融','经济','1996','14'）

/* 为表s插入以下数据：

   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 */

insertintos

      values（'8101','张三','18','101'）

insertintos

      values（'8102','钱四','16','121'）

insertintos

      values（'8103','王玲','17','131'）

insertintos

      values（'8105','李飞','19','102'）

insertintos

      values（'8109','赵四','18','141'）

insertintos

      values（'8110','李可','20','142'）

insertintos

      values（'8201','张飞','18','111'）

insertintos

      values（'8302','周瑜','16','112'）

insertintos

      values（'8203','王亮','17','111'）

insertintos

      values（'8305','董庆','19','102'）

insertintos

      values（'8409','赵龙','18','101'）

insertintos

      values（'8510','李丽','20','142'）

/* 为表d插入以下数据：

   001， 数学；

   002， 计算机；

   003， 化学；

   004， 中文；

   005， 经济 */

insertintod

      values（'001','数学'）

insertintod

      values（'002','计算机'）

insertintod

      values（'003','化学'）

insertintod

      values（'004','中文'）

insertintod

      values（'005','经济'）

--嘿咻，关是复制粘贴都粘了我半个小时。

/*******************************************

 2Query.sql完成以下查询功能：

（1）找出所有姓李的学生。

（2）列出所有开设超过两个专业的系的名字。

（3）列出人数大于等于2的系的编号和名字。

 *******************************************/

--

（1）找出所有姓李的学生。

-- 这一问很变态，翻烂了课本都不知道怎么找姓李的。

selectsname

froms

wheresnamelike'李%'

--大家可以从这篇文章里得到更多信息

--

--

（2）列出所有开设超过两个专业的系的名字。

selectdname

fromc

groupbydname

havingcount（mname）>=2

--（3）列出人数大于等于2的系的编号和名字。

-- 这一问太奇怪，不想做！

/****************************************

 2Update.sql完成本题的最后两问：

 4学校又新增加了一个物理系，编号为006。

 5学生张三退学，请更新相关的表。

 ****************************************/

-- 4学校又新增加了一个物理系，编号为006。

-- 学校“又”增加了一个物理系！

usepku2

insertintod

      values（'006','物理'）

-- 5学生张三退学，请更新相关的表。

deletefroms

wheresname='张三'