大连东软信息学院计算机系课程
数据库原理与技术简明教程
案例题库
二○一二
数据库系统案例及习题汇总
一、授课选课系统案例及习题
1．按如下要求设计数据库
授课选课系统有如下的实体、属性及语义描述：
每名学生可以学习若干门课程，每门课程可以有若干学生学习，每名学生学完一门课程后能得到一个成绩。每门课程只由一名教师讲授，每名教师只能教授一门课程。学生、课程和教师的信息如下：
学生：学号，姓名，性别，年龄，所在系，所属班级
课程：课程号，课程名，学分
教师：教师号，教师姓名，性别，出生日期，职称，所在系
（1）设计该授课系统的E-R图；
（2）将该E-R图转换为关系模型结构；
学生关系(学号，姓名，性别，年龄，所在系，所属班级)
课程关系(课程号，课程名，学分)
教师关系(教师号，姓名，性别，出生日期，所在系，职称)
学生选课关系(学号，课程号，成绩)
教师授课关系(教师号，课程号)
（3）指出转换结果中每个关系模式的候选码。
学生关系的候选码为学号；课程关系的候选码为课程号；
教师关系的候选码为教师号；学生选课关系的候选码为学号与课程号；
教师授课关系的候选码为教师号与课程号；
2．按照以上设计，创建如下关系，并完成相关习题：
请根据如下四张表做后面的练习：
学生表Student ( Sno, Name, Sex, Age,Sdept)
教师表Teacher ( Tno, Name, Sex, Birthday, Prof, Dept)
课程表Course ( Cno, CName, Credit，Tno)
成绩表Score ( Sno, Cno, Grade)
2.1按照要求用SQL语句创建下面四张表
(1)学生表：表名——Student
属性：Sno 字符型，最大10个字符表示学生学号
Sname 字符型，最大8个字符表示学生姓名
Sage 整型表示学生年龄
Ssex 字符型，最大2个字符表示学生性别
Sdept 字符型，长度为20 表示所在系
约束：主码——Sno 非空属性——Sname
create table Student
(Sno char(10) primary key,
Sname varchar(8) not null,
Sage int,
Ssex char(2),
Sdept varchar(20)
)
(2)教师表：表名：Teacher
属性：Tno 字符型，最大8个字符表示教师代号
TName 字符型，最大10个字符表示教师姓名
Sex 字符型，2个字符表示教师性别
Birthday 日期时间类型表示出生日期
Prof 字符型，8个字符表示职称
Dept 字符型，长度为20 表示所在系约束：主码——Cno 非空属性——Cname
Create table Teacher
(
Tno char(8) primary key,
Tname varchar(10) not null,
Sex char(2),
Birthday datetime,
Prof char(8),
Dept varchar(20)
)
(3)课程表：表名：Course
属性：Cno 字符型，最大5个字符表示课程编号
Cname 字符型，最大10个字符表示课程名
Tno 字符型，最大8个字符表示教师代号
Credit 整型表示学分
约束：主码——Tno 非空属性——TName Tno——外码
create table Course
(Cno varchar(5) primary key,
Cname varchar(10) not null,
Tno varchar(8) foreign key (Tno) references Teacher(tno),
Credit int
)
(4)选课表：表名——SC
属性：Sno 字符型，最大10个字符表示学生学号
Cno 字符型，最大5个字符表示课程号
Grade 整型表示成绩
约束：主码——Sno，Cno；Sno，Cno均为外码
create table SC
(Sno varchar(10),
Cno varchar(5),
grade int,
primary key (Sno,Cno),
foreign key (sno) references Student(Sno),
foreign key (Cno) references Course(Cno)
)
2.2针对上面的四个基本表做如下的练习
(1) 向student表中插入记录（学号：04101，姓名：张三，性别：男，年龄：18，系别：
计算机系）
insert into student values(‘04101’,‘张三’,‘男’,18，‘计算机系’)
(2) 向student表中插入记录（学号：04102，姓名：李四）
insert into student(sno,sname) values(‘04102’,‘李四’)
(3) 向student表中插入记录（学号：04103，姓名：王五，系别：信管系）
insert into student (sno,sname,sdept) values(‘04103’，‘王五’,‘信管系’)
(4) 向student表中插入记录（学号：04104，姓名：赵六，性别：女，年龄：null，系别：
null）
insert into student vlause(‘04104’,‘赵六’,‘女’, null, null)
(5) 修改student表中学号为04103的学生性别为女
update student set sex=‘女’ where sno=‘04103’
(6) 修改student表中学号为04104的学生年龄为22，系别为计算机系
update student set sage=22, sdept=‘计算机系’ where sno=‘04104’
(7) 修改年龄为null的学生年龄为20
update student set sage=20 where sage is null
(8) 将计算机系所有学生的年龄设为null
update student set sage=null where sdept = ‘计算机系’
(9) 删除student表中学号为04104的学生
delete from student where sno=‘04104’
(10) 删除student表中计算机系的所有男生
delete from student where sdept=‘计算机系’
(11) 查询全体学生的学号与姓名
Select sno,sname from student
(12) 查询全体学生的姓名、学号、所在系
Select sname,sno,sdept from student
(13) 查询全体学生的详细记录
Select * from student
(14) 查询全体学生的姓名及出生年份
Select sname , year(getdate())-sage from student
(15) 查询全体学生的姓名、出生年份、所在系，且要求用小写字母表示所有系名
select sname,’year of birth’,2005-sage,islower(sdept) from student
select sname NAME, ‘year of birth’, BIRTH,2005-sage BIRTHDAY,islower(sdept) DEPARTMENT from student
(16) （消除重复行）查询选修了课程的学生学号
Select distinct sno from sc
(17) 查询计算机系所有学生
Select * from student where sdept=’计算机系’
(18) 查询所有年龄在20岁以下的学生姓名及其年龄
Select sname,sage from student where sage<20
(19) 查询考试成绩不及格的学生学号
Select sno from sc where grade<60
(20) 查询年龄在20－23岁之间的学生的姓名、年龄和系别
Select sname,sage,sdept from student where sage between 20 and 23
(21) 查询年龄不在20－23岁之间的学生姓名、年龄和系别
Select sname,sage,sdept from student where sage not between 20 and 23
(22) 查询信息系、数学系和计算机系的学生的姓名和性别
Select sname,sex from student where sdept in (‘信息系’,‘数学系’,‘计算机系’)
(23) 查询既不是信息系、数学系，也不是计算机系的学生的姓名和性别
Select sname,sex from student where sdept not in (‘信息系’,‘数学系’,‘计算机系’) (24) 查询学号为95001的学生的详细情况（可用like做）
Select * from student where sno like ‘95001’
或：Select * from student where sno=‘95001’
(25) 查询所有姓刘的学生的姓名、学号和性别
Select sname,sno,sex from student where sname like ‘刘%’
(26) 查询姓李且全名为三个汉字的学生的姓名
Select sname from student where sname like ‘李_ _’
(27) 查询名字中第二个字为阳字的学生的姓名和学号
Select sname,sno from student where sname like ‘_阳_’
(28) 查询所有不姓刘的学生姓名
Select sname from student where sname not like ‘刘%’
(29) 查询DB_Design课程的课程号和学分
select Cno,Ccredit from Course where Cname like ‘DB\_Design’ ESCAPE’\’
(30) 查询以?DB_?开头，且倒数第3个字符为i的课程的详细情况
select *from course where Cname like ‘DB\_%i__’ ESCAPE’\’
(31) 查询缺少成绩的学生的学号和相应的课程号
Select sno,cno from sc where grade is null
(32) 查询所有有成绩的学生的学号和课程号
Select sno,cno from sc where grade is not null
(33) 查询计算机系年龄20岁以下的学生姓名
Select sname from student where sdept =’计算机系’ and sage<20
(34) 查询选修了c03号课程的学生的学号及成绩，查询结果按分数的降序排列。
Select sno,grade from sc where cno=’c03’ order by grade desc
(35) 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排
列。
Select * from student order by sdept asc,sage desc
(36) 查询学生的总人数
Select count(*) from student
(37) 查询选修了课程的学生人数
Select count(distinct sno) from sc
(38) 计算c01号课程的学生平均成绩
Select avg(grade) from sc where cno=’c01’
(39) 查询选修c01号课程的学生最高分数和最低分数。
Select max(grade),min(grade) from sc where cno=’c01’
(40) 求各个课程号及相应的选课人数。
Select cno ,count(sno) from sc group by cno
(41) 查询每名学生的选课门数和平均成绩。
Select sno,count(cno),avg(grade) from sc group by sno
(42) 查询选修了3门以上课程的学生学号。
Select sno from sc group by sno having count(*) >3
(43) 查询选课门数等于或大于4门的学生的平均成绩和选课门数
Select sno,avg(grade),count(cno) from sc group by sno having count(cno)>=4
(44) 查询每个学生及其选修课程的情况
Select sno,count(cno),avg(grade),max(grade),min(grade),sum(grade) from student,sc,course where student.sno=sc.sno and https://www.doczj.com/doc/6c15265110.html,o=https://www.doczj.com/doc/6c15265110.html,o group by sno
(45) 查询选修2号课程且成绩在90分以上的所有学生
(46) 查询计算机系学生的选课情况，要求列出学生的名字、所修课的课程号和成绩
select student.sno,cno,grade from student join sc on student.sno =sc.sno
where sdept=’计算机系’
(47) 查询信息系选修VB课程的学生的成绩，要求列出学生姓名、课程号和成绩
select sname,https://www.doczj.com/doc/6c15265110.html,o,grade from student s join sc on s.sno=sc.sno join course c on https://www.doczj.com/doc/6c15265110.html,o=https://www.doczj.com/doc/6c15265110.html,o where sdept=’信息系’ and cname=’VB’
(48) 查询所有选修了VB课程的学生的情况，要求列出学生姓名和所在的系
select sname,sdept from student s join sc on s.sno=sc.sno join course c on
https://www.doczj.com/doc/6c15265110.html,o=https://www.doczj.com/doc/6c15265110.html,o where cname=’VB’
(49) (自连接)查询与刘晨在同一系学习的学生的姓名和所在的系
select s2.sname,s2.sdept from student s1 join student s2 on s1.sdept=s2.sdept where s1.sname=’刘晨’and s2.sname!=’刘晨’
select sno,sname,sdept from student where sdept in
(select sdept from student where sname=’刘晨’)
(50) （外连接）查询学生的选课情况，包括选修课程的学生和没有选修课程的学生
select student.sno,sname,cno,grade from student left join sc on student.sno=sc.sno (51) 查询成绩大于90分的学生的学号和姓名。
Select sno,sname form student where sno in
(select sno from sc where grade >90)
(52) 查询选修了?数据库基础?课程的学生的学号和姓名
select sno,sname form student where sno in
(select sno from sc where cno in
(select cno from course where cname=’数据库基础’))
或：select student.sno,sname from student join sc on student.sno=sc.sno join course on https://www.doczj.com/doc/6c15265110.html,o=https://www.doczj.com/doc/6c15265110.html,o where cname=’数据库基础’
(53) （使用子查询进行存在性测试）查询选修了课程?C01?的学生姓名
select sname from student
where exists
(select * from sc where sno=student.sno and cno=’c01’)
或：select sname from student join sc on sc.sno=student.sno
where cno=’c01’
(54) 向student表添加Class列的信息，要求类型为字符串型，最大长度30位；
alter table student add Class nvarchar(30)
(55) 向基本表Student中插入学生元组（”0201901”，”ZHAO”，18，”女”，”计算机系”，NULL）insert into Student values(‘0201901’,‘zhao’,18,’女’,’计算机系’,NULL)
(56) 向teacher表插入一条记录（010125，刘冰，女，1975-06-25，助教，电子工程系）；
insert into teacher(‘010125’,‘刘冰’，‘女’，‘1975-06-25’，‘助教’，‘电子工程系’) (57) 将student表中学号为‘209’的学生班级改为‘95031’
update student set class=‘95031’ where sno=‘209’
(58) 删除选修?刘冰?教师所教课程的学生选课信息
delete from score where cno in (select cno from course where tno in (select tno from teacher where name=‘刘冰’))
(59) 把WANG同学的选课记录全部删除
delete from SC where sno in (select sno from Student where Sname=’WANG’);
(60) 显示student表中‘95031’班或性别为‘女’的同学记录
select * from student where class=‘95031’ or sex=‘女’
(61) (61)查找最低分大于70分，最高分小于90分的学生sNo；
select sno from score group by no having min(grade)>70 and max(grade)<90
(62) 查询平均成绩在75分以上的学生学号和其平均成绩
Select sno,avg(grade) from sc group by sno having avg(grade)>75
(63) 查找score表中至少有5名学生选修的并以3开头的课程号的平均分数
select cno,avg(grade) from score where cno like ‘3%’group by cno having count(*)>=5
(64) 显示‘95033’班所选课程的平均分；
select https://www.doczj.com/doc/6c15265110.html,o,avg(grade) from student,score where student.sno=score.sno and student.class=’95033’group by https://www.doczj.com/doc/6c15265110.html,o
(65) 显示?张旭?教师任课的学生成绩。
Select cno,sno,grade from score where cno in ( select https://www.doczj.com/doc/6c15265110.html,o from course,teacher where course.tno=teacher.tno and https://www.doczj.com/doc/6c15265110.html,=‘张旭’)
(66) 查询刘老师所授课程的课程号和课程名
SELECT Cno，Cname FROM Course WHERE Tno in (select tno from teacher where tname=‘LIU’)
(67) 显示选修某课程的同学人数多于5人的教师姓名
select name from teacher where tno in (select course.tno from course ,score where https://www.doczj.com/doc/6c15265110.html,o=https://www.doczj.com/doc/6c15265110.html,o group by https://www.doczj.com/doc/6c15265110.html,o having count(https://www.doczj.com/doc/6c15265110.html,o)>5)
(68) 显示所有存在85分以上成绩的课程cno
select distinct cno from score where grade in (select grade from score where grade>85)
(69) 显示选修了课程号为‘3-105’课程且成绩高于选修课程号为‘3-245’课程的学生的
cno，sno和grade。
Select cno,sno,grade from score where cno=‘3-105’and grade>all ( select grade from score where cno=‘3-245’)
(70) 显示所有未讲课的教师的name和depart
select name,depart from teacher where tno not in (select tno from course)
(71) 显示和李军同性别且同班的同学姓名
select name from student where sex=(select sex from student where name=’李军’) and class=(select class from student where name=’李军)
(72) 显示所有选修’计算机导论’课程的男同学的成绩表
select sno, cno, grade from score where sno in (select sno from student where sex=’男’) and cno=(select cno from course where cname=’计算机导论’)
(73) 显示最高分学生的sno，cno和grade列
select sno, cno, grade from score where grade = (select max(grade) from score) (74) 按班级和年龄从大到小的顺序显示student表的全部记录
select * from student order by class, birthday
(75) 查询其他系比信息系年龄最小的学生还小的学生姓名、年龄
select sname,sage from student where sage< (select min(sage) from student where sdept='信息系') And sdept<>’信息系’
(76) 查询所有缺考的学生的学号、姓名和系别
select student.sno,sname,sdept from student join sc on student.sno=sc.sno where sc.grade is null
(77) 查询选修了?C01?课程的学生总人数和最高分数
select count(*),max(grade) from sc where cno='c01'
(78) 查询计算机系年龄超过20岁的男学生的学号和姓名
select sno,sname from student where sdept=’计算机系’ and sage>20 and ssex=’男’(79) 查询与刘晨选修的一门课程相同的学生姓名
select sname from student where sno in(select sno from sc where cno in (select cno from sc where sno=(select sno from student where sname=’刘晨’)))
(80) 查询至少选修LIU老师所授课程中一门课程的女学生姓名
SELECT Sname FROM Student WHERE Ssex=‘女’ AND S no IN (SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Tno in (select tno from teacher where tname=‘LIU’)))
(81) 查询与QIAN老师同一职称的其它老师的姓名
select tname from teacher where prof=(select prof from teacher where tname=’qian’) and tname<>’qian’
(82) 查询哪些课程没有人选修
select cno,cname from course where cno not in (select cno from sc)
(83) 查询选修了课程‘C02’且成绩高于此课程的平均成绩的学生的学号和成绩
select sno,grade from sc where cno=’c02’and grade>(select avg(grade) from sc where cno=’c02’)
(84) 求平均分最高的学生学号和平均分
select sno,avg(grade) from sc group by sno having avg(grade)>=all(select avg(grade) from sc group by sno)
(85) 显示20岁学生的基本信息和选课信息（课程名和分数），若没有选课，也要将基本信
息显示出来
select student.*,https://www.doczj.com/doc/6c15265110.html,o,cname,grade from student left join sc on student.sno=sc.sno left join course on https://www.doczj.com/doc/6c15265110.html,o=https://www.doczj.com/doc/6c15265110.html,o where sage=20
(86) 建立计算机系选修了课程‘c01’的学生的视图
Create view v_cs_s1(sno,sname,grade) as select student.sno,sname,grade from student join sc on student.sno=sc.sno where sdept=’计算机系’ and cno=’c01’
(87) 建立一个包含所有学生sno，sname，cname，grade 和class字段的视图degrees
create view degrees (no,name,cname,grade,class) as select student.sno, https://www.doczj.com/doc/6c15265110.html,, https://www.doczj.com/doc/6c15265110.html,ame, score.grade, student.class where student.no=score.no and https://www.doczj.com/doc/6c15265110.html,o=https://www.doczj.com/doc/6c15265110.html,o
(88) 根据上题所建视图，查询‘95031’班级的学生选课信息
select * from degrees where calss=’95031’
(89) 查询选修四门课以上课程的学生总成绩（不统计不及格的课程），
select no, sum(grade) from score where grade>=60 group by no having count(*)>=4 order by sum(grade) desc
(90) 建立视图（SGrade），包含每个学生的学号（Sno），选课门数（Count_Cno），平均分
（Avg_Grade）
create view SGrade(Sno,count_Cno,Avg_Grade) as select Sno,count(Cno),avg(grade) from SC group by sno;
(91) 利用上述视图进行查询：列出平均分大于80分的学生的学号及其选课门数
select Sno,cout_Cno from SGrade where Avg_Grade>80;
(92) 从视图中查询选修了3门以上课程的学生学号
select sno from sgrade where count_cno>=3
(93) 查询以DB开头且倒数第三个字母是i的课程的课程号、课程名
select cno,cname from course where cname like ‘DB%i_ _’
(94) 求平均分最高的学号、学生姓名、平均成绩
select student.sno,sname,avg(grade) from student join sc on student.sno=sc.sno group by student.sno,sname having avg(grade)>=all(select avg(grade) from sc group by sno)
二、图书借阅系统的案例及习题
1．按照下列要求设计图书借阅系统的数据库
图书借阅系统有如下的实体、属性及语义描述：
在一个图书馆中，一本图书可以被多个读者借阅，而一个读者可以借阅多本图书。图书有书号、书名、作者、出版社和价格等信息；读者包括读者的编号、姓名、性别、出生日期、Email等信息；每位读者借阅一本图书时应该记录读者的借阅时间；读者归还图书时还应记录下来图书的归还时间。
(1) 设计系统的E-R模型
(2) 将E-R 模型转换为关系模式，并用下划线标识出各个表的主码。
图书(书号，书名，作者，出版社，定价)
读者(编号，姓名，性别，出生日期)
借阅(图书编号，读者编号，借书日期，还书日期)
(3) 判断各关系模式属于几范式，并指出各关系模式中的外码。
三个关系都属于3范式。
借阅表中有外码，分别是：
图书编号参照图书表中的主码书号；
读者编号参照读者表中的主码编号；
2．按照要求定义图书借阅系统的数据库
(1) 创建数据库Library ，并打开它
Create Database Library
Use Library
(2)
(
Id char(10) primary key,
Name
varchar(30) not null unique,
Author varchar(10) not null ,
Publish varchar(20) default ‘清华大学出版社’,
Price decimal(6,2)
)
(3) 表名：Reader
(
Id char(10) primary key,
Name varchar(8) not null,
Sex char(2) check(sex=’男’ or sex=’女’) ,
Birthdate datetime
)
(4)
Create Table Borrow
(
BookID char(10) foreign key (bookid) references book(id),
ReaderID char(10) foreign key (readerid) references reader(id),
BorrowDate datetime,
ReturnDate datetime,
Primary key (bookid, readerid, borrowdate, returndate)
)
(5) 删除表Book, Reader, Borrow
Drop table book,reader,borrow
(6) 修改表Book，添加列ISBN char(13)
Alter table book add ISBN char(13)
(7) 修改表Reader，添加列Email varchar(50)
Alter Table Reader add Email varchar(50)
(8) 修改表Reader，Email列改为varchar(30)
Alter Table Reader alter column Email varchar(30)
(9) 删除Book表中的ISBN列；
Alter Table book drop column ISBN
(10) 删除Reader表中的Email列；
Alter Table reader drop column Email
3.按照如下要求操纵数据库Library
(1) 向book表中插入记录（ID：B32DT00001，Name：高等数学，Author：赵丹，publish：
同济大学出版社，price：42）
Insert into book values(‘B32DT00001’,’高等数学’,’赵丹’,’同济大学出版社’,42)
(2) 向book表中插入记录（ID：B32DT00002，Name：离散数学）
Insert into book (ID,Name) values(‘B32DT00002’,‘离散数学’)
(3) 向book表中插入记录（ID：B32DT00003，Name：线性代数，Publish：高等教育出
版社）
Insert into book (ID,Name,Publish) values(‘B32DT00003’,‘线性数学’,‘高等教育出版社’) (4) 向book表中插入记录（ID：B32DT00004，Name：概率论，Publish：NULL，Price：
22）
Insert into Book(ID,Name,Publish,Price) values(‘B32DT00004’,‘概率论’,22)
(5) 修改book表中ID为B32DT00002的书Author为王旭，publish为高等教育出版社，
价格为31）
Update book set Author=‘王旭’,publish=‘高等教育出版社’,price=31 where ID=‘B32DT00002’
(6) 修改book表中publish为NULL的书publish为机械工业出版社
Update book set publish=‘机械工业出版社’ where publish is null
(7) 修改book表中价格高于20元的书publish为NULL
Update Book set publish=NULL where price>20
(8) 删除book表中ID为B32DT00003的书
Delete from book where ID=‘B32DT00003’
(9) 删除book表中price为NULL的书
Delete from book where price is null
(10) 删除book表中高等教育出版社出版的价格高于40元钱的书
Delete from book where publish=‘高等教育出版社’ and price>40
(11) 把表Book中的所有数据都显示出来。
Select * from book
(12) 把Reader表的所有记录显示出来，并且列名用汉字表示。
Select id 书号,name 书名, author 作者, publish 出版社,price 价格from book
(13) 显示所有读者（Reader）的姓名和年龄。
Select name , year(getdate()) – year(birthdate) from reader
(14) 查询borrow表中所有读者借阅图书的信息。
Select bookid,readerid, borrowdate,returndate from borrow
(15) 查询所有借书的读者编号。
Select distinct readerid from borrow
(16) 把表Book中的出版社为?清华大学出版社?的书选出来。
Select * from book where publish=‘清华大学出版社’
(17) 把表Book中出版社为?清华大学出版社?，并且定价不超过25元的书选出来。Select * from book where publish=‘清华大学出版社’and price<=25
(18) 把表Book中出版社为?清华大学出版社?，或者定价不超过25元的书选出来。Select * from book where publish=‘清华大学出版社’or price <=25
(19) 显示定价在20到25元之间（包含20元和25元）的图书信息。
Select * from book where price between 20 and 25
(20) 显示定价不在20到25元之间（不包含20元和25元）的图书信息。
Select * from book where price not between 20 and 25
(21) 显示出版社为?清华大学出版社?、?同济大学出版社?或者?高等教育出版社?的图书
信息。
Select * from book where publish in (‘清华大学出版社’,‘同济大学出版社’,‘高等教育出版社’) (22) 显示出版社为?清华大学出版社?、?同济大学出版社?或者?高等教育出版社?以外的
其它出版社出版的图书信息。
Select * from book where publish not in (‘清华大学出版社’,‘同济大学出版社’,‘高等教育出版社’)
(23) 显示书名包含?数学?的图书信息。
Select * from book where name like ‘%数学%’
(24) 显示姓名为三个字的读者信息。
Select * from reader where name like ‘_ _ _’
(25) 显示由姓李的作者写的书的信息。
Select * from book where author like ‘李%’
(26) 把所有的图书按照定价由高到低进行排序。
Select * from book order by price desc
(27) 把所有的图书按照定价由高到低进行排序，如果定价相同，再按照编号进行升序排列。Select * from book order by price desc, id asc
(28) 求定价最低的图书的定价。
Select min(price) from book
(29) 求由?清华大学出版社?出版的图书的定价的平均值。
Select avg(price) from book where publish=‘清华大学出版社’
(30) 求读者表中有多少位读者。
Select count(*) from reader
(31) 查询每个出版社的平均价格、最高价格和最低价格，并用中文显示列名。
Select publish 出版社,avg(price) 平均价格, max(price) 最高价格，min(price) 最低价格from book group by publish
(32) 查询每个借过书的读者的借书数量。
Select readerid,count(*) from borrow group by readerid
(33) 查询每个出版社出版的图书数量。
Select publish, count(*) from book group by publish
(34) 查询借阅表中每本图书被几个读者借阅。
Select bookid, count(*) from borrow group by bookid
(35) 求出版了两本以上（含两本）图书的出版社所出版的图书定价的平均值。
Select publish, avg(price) from book group by publish having count(*) >=2
(36) 查询平均价格高于30元钱的出版社名称。
Select publish from book group by publish having avg(price)>30
(37) 查询最高价格高于40元钱的出版社名称和最高价格。
Select publish ,max(price) from book group by publish having max(price)>40
(38) 求定价最低的图书的书名以及定价。
Select name,price from book where price=(select min(price) from book)
(39) 求在2005年2月3日借书的人的姓名。
Select name from reader where id in (select readerid from borrow where borrowdate=
‘2005-2-3’)
(40) 求平均定价最高的出版社的名称。
Select publish from book group by publish having avg(price)>=all(select avg(price) from book group by publish)
(41) 查询平均价格高于机械工业出版社出版的图书平均价格的出版社信息。
Select publish from book group by publish having avg(price) >(select avg(price) from book where publish=‘机械工业出版社’)
(42) 查询价格高于机械工业出版社出版的图书平均价格的图书书名和出版社。
Select name,publish from book where price > select avg(price) from book where
publish=‘机械工业出版社’)
(43) 求出版图书数量最多的出版社所出版的图书定价的平均值。
Select publish,avg(price) from book group by publish having count(*) >=all(select count(*) from book group by publish)
(44) 求读者施秋乐所借的图书编号、书名和出版社信息。
Select id,name,publish from book where id in(select bookid from borrow where readerid in (select id from reader where name=‘施秋乐’))
(45) 查询借阅了数据库原理这本书的读者编号和姓名。
Select id,name from reader where id in (select readerid from borrow where bookid in (select id from book where name=‘数据库原理’))
(46) 求在2005年2月3日借书的人的姓名及其所借书的书名。
Select https://www.doczj.com/doc/6c15265110.html,, https://www.doczj.com/doc/6c15265110.html, from book,reader,borrow where book.id=borrow.bookid and reader.id=borrow.readerid and borrowdate =‘2005-2-3’
(47) 求读者施秋乐所借的图书编号、书名和出版社信息及借书时间。
Select book.id, https://www.doczj.com/doc/6c15265110.html,, publish, borrowdate from book,reader,borrow where
book.id=borrow.bookid and reader.id=borrow.readerid and https://www.doczj.com/doc/6c15265110.html,=‘施秋乐’
(48) 查询借阅了数据库原理这本书的读者编号和姓名。
Select reader.id, https://www.doczj.com/doc/6c15265110.html, from book,reader,borrow where book.id=borrow.bookid and reader.id=borrow.readerid and https://www.doczj.com/doc/6c15265110.html,=‘数据库原理’
(49) 查询所有的借阅信息，要求列出借阅人编号、姓名、所借图书编号、书名和借阅日期，
并用中文显示列名。
Select reader.id, https://www.doczj.com/doc/6c15265110.html,, book.id, https://www.doczj.com/doc/6c15265110.html,, borrowdate from book,reader, borrow where book.id=borrow.bookid and reader.id=borrow.readerid
(50) 创建清华大学出版社出版的图书信息的视图QH_Book，并要保证对该视图的修改都要
符合出版社名称为?清华大学出版社?这个条件。
Create view QH_Book
As
Select * from book where publish =‘清华大学出版社’
(51) 创建R_B_Book视图，查询所有读者借阅图书的读者编号、姓名、图书编号、书名、
出版社、价格、借阅日期信息。
Create view R_B_Book(读者编号,姓名,图书编号,书名,出版社,价格,借阅日期)
As
Select reader.id, https://www.doczj.com/doc/6c15265110.html,, book.id, https://www.doczj.com/doc/6c15265110.html,, publish, price, borrowdate from
book,reader, borrow where book.id=borrow.bookid and reader.id=borrow.readerid
(52) 基于上例中的视图R_B_Book，创建姓?王?的读者借阅的图书书名和出版社信息和借
阅日期的视图R_B_Book1。
Create view R_B_Book1(书名,出版社,借阅日期)
As
Select书名,出版社,借阅日期from R_B_Book where姓名like ‘王%’
(53) 创建读者信息的视图ReaderInfo,包括读者编号、姓名和年龄，在视图中
的列名分别为ID，Name和Age。
Create view ReaderInfo(ID,Name, Age)
As
Select id,name,year(getdate())-year(birthdate) from reader
(54) 创建各个出版社出版的图书的平均价格的视图Book_AVG,包括出版社（在视图中列名
为Publish）和平均价格（在视图中列名为AVG_Price）。
Create view Book_AVG（Publish,AVG_Price）
As
Select publish, avg(price) from book group by publish
(55) 基于上面创建好的视图R_B_Book，查询借阅《数据库原理》一书的读者编号和姓名。Select读者编号,姓名from R_B_Book where 书名=‘数据库原理’
(56) 基于上面创建好的视图ReaderInfo，查询年龄高于20岁的读者编号和姓名。
Select id, name from ReaderInfo where age>20
三、商品定购系统的案例及习题
1．按照下列要求设计售货系统的数据库
设有商业销售系统数据库。一个顾客（顾客编号，姓名，性别，单位，电话号码）可以定购多种商品，一种商品（商品编号，名称，型号，厂商，单价）可以提供给多个顾客。顾客定购商品时需要确定定购商品的数量，并记录定购日期与取货日期。
(1)
(2) 给出相应的关系数据模型
顾客(编号, 姓名, 性别, 单位, 电话)
商品(编号, 名称, 型号, 单价, 厂商)
定购(顾客编号, 商品编号, 定购数量, 定购日期, 取货日期)
2．已知有顾客定购商品信息的三张表：顾客表Customer、定购表Order、商品表Commodity。按要求创建三张表
(1) 表名：Customer
属性：ID 字符型最大10个字符——顾客编号
NAME 字符型最大16个字符——顾客姓名
SEX 字符型最大2个字符——性别
MOBILE 字符型最大11个字符——移动电话
ADDRESS 字符型最大50个字符——家庭住址
约束：ID——主码；NAME——非空属性；SEX——取值?男?或?女?；
MOBILE——唯一性；ADDRESS——默认为UNKOWN；
Create table Customer
(
ID char(10) primary key,
Name not null,
Sex char(2) check(sex=’男’ or sex=’女’),
Mobile char(11) unique,
Address default ‘UNKOWN’
)
(2) 表名：OrderBook
属性：CSID 字符型最大10个字符——顾客编号
CMID 字符型最大12个字符——商品编号
COUNT 整型——定购数量
BOOKDATE 日期型——订货日期
TAKEDATE 日期型——交货日期
约束：CSID，CMID——主码；定购数量要大于0；订货日期要小于交货日期；
CSID——外码，引用Customer表的ID；
CMID——外码，引用Commodity表的ID；
Create Table OrderBook
(
CSID char(10) foreign key (CSID) references Customer(ID),
CMID char(10) foreign key (CMID) references Commodity(ID),
Count int check(count>0),
BookDate datetime,
TakeDate datetime,
Check(Takedate>bookdate),
Primary key(CSID, CMID)
)
(3) 表名：Commodity
属性：ID 字符型最大12个字符——商品编号
NAME 字符型最大20个字符——商品名称
MANUFACTURE 字符型最大20个字符——生产厂商
PRICE 小数型最大不超过4位数，保留2位小数——商品单价
约束：ID——主码；NAME——非空；
Create Table Commodity
(
ID char(12) primary key,
Name varchar(20) not null,
MANUFACTUR varchar(20),
Price decimal(6,2)
)
3. 针对上面的三个基本表做如下练习：
(1) 往基本表Customer中插入顾客元组（”0421F901”，”WU”，”女”，139********）insert into Customer(id,name,sex, Mobile) values(‘0421F901’, ‘WU’, ‘女’, ‘139********’) (2) 往基本表Commodity中插入一条商品记录（?0311*******?，?牙膏?，?保洁公司?，
5.00）
isnert into Commodity(id, name, manufacture, price) values(‘0311*******’, ‘牙膏’, ‘保洁公司’, 5)
(3) 修改?WANGYAN?顾客定购商品的记录交货日期为2005-12-25。
Update order set taketime=’2005-12-25’ where csid in (select id from customer where name=’wangyan’)
(4) 把?雀巢奶粉?的定购商品记录全部删去。
Delete from order where cmid in (select id from commodity where name=’雀巢奶粉’) (5) 查询?ANAN?顾客的手机号和住址。
Select mobile, address from customer where name=’anan’
(6) 查询商品的平均价格高于75元钱的厂商名称。
Select manufacture from commodity group by manufacture having avg(price) >75 (7) 查询顾客的定购信息，并按订货日期升序排列，若订货日期相同，则按定购数量降序排
列。
Select * from orderbook order by bookdate,count desc
(8) 查询定购数量超过100的顾客姓名、电话和住址。
Select name, mobile, address from customer where id in(select csid from orderbook where count>100)
(9) 查询没有订单的商品编号、名称和厂商。
Select id,name, manufacture from comodity where id not in(select csid from orderbook)
(10) 查询定购了商品号为?M900532481?的顾客总人数和最高数量。
Select count(*) ,max(count) from order where cmid=’m900532481’
(11) 查询定购了?可比克?商品的顾客编号、姓名。
Select id,name from customer where id in(select csid from order where cmid in(select id from comodity where name=’可比克’))
(12) 查询商品名中包含?糖?的所有商品的编号、厂商、单价。
Select id,manufacture ,price from comodity where name like ‘%糖%’
(13) 查询至少有两位顾客定购的商品的名称。
Select name from comodity where id in(select cmid from order group by cmid having count(*)>=2)
(14) 查询截至2005年底所有商品订单最晚的交货日期。
SELECT taketime from order where taketime = (select max(taketime) from order where taketime<’2005-12-31’)
(15) 查询住址中含有?软件园?三个字的顾客人数。
Select count(*) from customer where address like’%软件园%’
(16) 建立定购商品号为?M900532481?的顾客的编号、住址、订货数量、交货日期的视图
View001。
Create view view001 (编号,住址,订货数量,交货日期)
As
Select id,address,count,taketime from order where cmid=’m900532481’
(17) 建立视图（View002），包含每位顾客的编号、订单数量、平均订购数量、最早交货日
期和最晚交货日期。
Create view view002(顾客编号,订单数量,平均订购数量,最早交货日期,最晚交货日期) As
Select csid, count(*), avg(count), min(taketime), max(taketime) from order group by csid
(18) 利用上述视图进行查询：列出平均数量大于80的顾客编号及其订单数量。
Select顾客编号,订单数量from view002 where平均订购数量>80