北大数据库原理上机考题练习及参考答案练习二(学生、班、系)

1、题目：(1)现要为学校建立一个数据库， 个班，每个班有若干个学生。设学校环境如下： 一个系有若干个专业， 每个专业一年只招一 . 现要建立关于系、学生、班级的数据库，关系模式为：班 CLASS （班号，专业名，系名，入学年份）学生 STUDENT （学号，姓名，年龄，班号） 系 DEPARTMENT （系号，系名） 试用 SQL 语言完成以下功能：建表，在定义中要求声明：1 ）每个表的主外码。2 ）每个班级的人数不能超过30 人。3 ）学生的年龄介于 15 到 40 岁之间。4 ）学生姓名不能为空。5 ）只有班里没有学生的时候，才能删除该班级。插入如下数据CLASS(101，软件，计算机，19

2、95 ；102 ，微电子，计算机，1996 ；111，无机化学，化学，1995 ；112，高分子化学，化学，1996121，统计数学，数学，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 ，1428201 ，张飞，18 ，111 ； 8302 ，周瑜，16 ，112 ；8203 ，王亮，17 ，1

3、11 ； 8305 ，董庆，19 ，102 ；8409 ，赵龙，18 ，101 ； 8510 ，李丽，20 ，142 )DEPARTMENT(001 ，数学； 002 ， 计算机；003 ，化学；004 ， 中文；005 ，经济；）完成以下查询功能31 ）找出所有姓李的学生，并按其年龄由小到大排序。2 ）列出所有开设超过两个专业的系的名字。3 ）列出学生人数大于等于2 的系的编号和名称。学校又新增加了一个物理系，编号为006 。学生张三转到化学系 111班，请更新相关的表。参考答案：如下是个人的做的参考答案，有问题的话多多指正。(1)create table student(stuno cha

4、r(8) not null, stuname char(15) not null,age smallint,classno char(8) ,primary key (stuno),foreign key (classno) references class(classno) on delete cascade,check (age between 15 and 40) create table class(class no char(8) not nu II, zhua nye char(15),dep artme ntn ame char(10), startyear int,p rima

5、ry key (class no) create table dep artme nt(dep artme ntno char(8) not n ull, dep artme ntn ame char(15), p rimary key (de partme ntno)插入值：insert into class (class no,zhua ny e,de partme ntn ame,startyear) values (101软件,计 算机,1995);insert into class (class no,zhua ny e,de partme ntn ame,startyear) va

6、lues (102微电子, 计算机,1996);insert into class (classno,zhuanye,departmentname,startyear)values (111,无机化 学,化学,1995);insert into class (classno,zhuanye,departmentname,startyear)values (112,高分子 化学,化学,1996);insert into class (classno,zhuanye,departmentname,startyear)values (121,统计数 学,数学,1995);insert into cl

7、ass (classno,zhuanye,departmentname,startyear)values (131,现代语 言,中文,1996);insert in to class (class no ,zhua nye,de partme ntn ame,startyear)values (141,国际贸 易,经济,1997);insert into class (classno,zhuanye,departmentname,startyear)values (142,国际金 融,经济,1996)select * from sysobjects where xtype = U知识补充：查询

8、用户表+! sfudenf .nsert nsert nsert nsert nsert nsert nsert nsert nsert nsert nsert nsertnfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf a nfo sfudenf adepartment 帀insertinsertinsertinsertues(82 ues(

9、802 ues(803 ues(805 ues(809 ues(8=0 ues(822 ues(8302 ues(8203 ues(-8305 ues(8409 ues(850顒您田画耒顒您顒田梵耒a莒洱羽雪凹S 妙S山|O_x_x_x_x_x|o_x_x_x_x_x089768089768 士 S 竺 E 22 *nfo DEPARTMENTS_ues(oor - = nfo DEPARTMENT a_ues(002- - 言-)- nfo DEPARTMENT a_ues(003-去強-)- nfo DEPARTMENT a_ues(004 -召洱-)-insert Ho DEPARTMENT a_ues(005 -歧一审-)-(3)se-ecf * from sfudenf where sfuname -ike -顒order by age asGse-ecf departmenfname from c-ass group by departmenfname having counf(*) VH2八se-ecf d.departmenfno- d.departmenfname from c-ass c-sfudenf s-departmenf dwhere c.c-assno H s.c-assno and d.departmenfname H c.depa