杭电数据库张红娟编第3章习题参考答案
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
--创建course表
create table course
(
cno char(1) primary key,
cname varchar(20) not null,
credit smallint check (credit in ('1','2','3','4','5','6','7'))
)
--创建class表
create table class
(
clno char(5) primary key,
speciality varchar(20) not null,
inyear char(4) not null,
number integer check(number>1 and number<300),
monitor char(7)
)
--创建student表
create table student
(
sno char(7) primary key,
sname varchar(20) not null,
ssex char(2) not null default '男' check (ssex in ('男','女')),
sage smallint check (sage>14 and sage <65),
clno char(5) not null foreign key(clno) references class(clno) on update cascade )
--为class表添加参照完整性
alter table class
add constraint monitor foreign key (monitor) references student(sno)
--创建grade表
create table grade
(
sno char(7) not null foreign key (sno) references student(sno) on update cascade on delete cascade,
cno char(1) not null foreign key (cno) references course (cno) on update cascade on delete cascade,
gmark decimal(4,1) check(gmark>0 and gmark<100),
primary key (sno,cno)
)
--插入数据
insert into class (clno,speciality,inyear,number) values ('00311','计算机软件','2000',120)
insert into course values ('1','数据库',4)
insert into student
values ('2000101','李勇','男',20,'00311')
update class
set monitor='2000101'
where clno='00311'
insert into grade
values ('2000101','1',92)
--①找出所有被学生选修了的课程号；
select distinct cno
from grade
--②找出01311班女学生的个人信息；
select *
from student
where clno='01311' and ssex='女'
--③找出01311班、01312班的学生姓名、性别、出生日期；--法一
select sname,ssex,sage
from student
where clno='01311' or clno='01312'
--法二
select sname,ssex,sage
from student
where clno in ('01311','01312')
--法三
select sname,ssex,sage
from student
where clno='01311'
union
select sname,ssex,sage
from student
where clno='01312'
--错误解法
select sname,ssex,sage
from student
where clno like '0131_'
--④找出所有姓李的学生的个人信息；
select *
from student
where sname like '李%'
--⑤找出学生李勇所在班级的学生人数；
--法一
select number
from class join student on class.clno=student.clno where sname='李勇'
--法二
select number
from class,student
where class.clno=student.clno and sname='李勇'
--法三
select number
from class
where clno=(select clno
from student
where sname='李勇')
--法四
select number
from class
where clno in
(select clno
from student
where sname='李勇')
--法五
select count(*) --也可用count(sno)
from student
where clno = (select clno
from student
where sname='李勇')