北大数据库原理上机考题练习及参考答案练习二(学生、班、系)
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
题目：
(1)
现要为学校建立一个数据库，设学校环境如下：一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生。. 现要建立关于系、学生、班级的数据库，关系模式为：
班CLASS （班号，专业名，系名，入学年份）
学生STUDENT （学号，姓名，年龄，班号）
系 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 班，请更新相关的表。
参考答案：
如下是个人的做的参考答案，有问题的话多多指正。
（1）
create table student
(
stuno char(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
(
classno char(8) not null,
zhuanye char(15),
departmentname char(10),
startyear int,
primary key (classno)
)
create table department
(
departmentno char(8) not null,
departmentname char(15),
primary key (departmentno)
)
（2）
插入值：
insert into class (classno,zhuanye,departmentname,startyear) values ('101','软件','计算机',1995);
insert into class (classno,zhuanye,departmentname,startyear) values ('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 class (classno,zhuanye,departmentname,startyear) values ('131','现代语言','中文',1996);
insert into class (classno,zhuanye,departmentname,startyear) values ('141','国际贸易','经济',1997);
insert into class (classno,zhuanye,departmentname,startyear) values ('142','国际金
insert into student values('8101','张三',18,'101');
insert into student values('8102','钱四',16,'121');
insert into student values('8103','王玲',17,'131');
insert into student values('8105','李飞',19,'102');
insert into student values('8109','赵四',18,'141');
insert into student values('8110','李可',20,'142');
insert into student values('8201','张飞',18,'111');
insert into student values('8302','周瑜',16,'112');
insert into student values('8203','王亮',17,'111');
insert into student values('8305','董庆',19,'102');
insert into student values('8409','赵龙',18,'101');
insert into student values('8510','李丽',20,'142');
插入department表：
insert into DEPARTMENT values('001' , '数学' );
insert into DEPARTMENT values('002' , '计算机' );
insert into DEPARTMENT values('003' , '化学' );
insert into DEPARTMENT values('004' , '中文' );
insert into DEPARTMENT values('005' , '经济' );
（3）
select * from student where stuname like '李%' order by age asc;
select departmentname from class group by departmentname having count(*) >=2; select d.departmentno, d.departmentname from class c,student s,department d where c.classno = s.classno and d.departmentname = c.departmentname
group by d.departmentno,d.departmentname having count(*) >=2;
insert into department values ('006','物理');
update student set classno = '111' where stuname = '张三';