数据库原理张红娟答案
11.（1）给学生表增加一个属性Nation,数据类型为Varchar（20）：
ALTER TABLE Student
ADD Nation VARCHAR(20) NULL;
（2）删除Nation：
ALTER TABLE Student
Drop Column Nation;
（3）向成绩表中插入记录("2001110","3",80)：
insert into Grade
values('2001110','3',80);
（4）将学号为2001110的学生的成绩改为70分：
update Grade
set Gmark='70'
where Sno='2001110';
（5）删除学号为'2001110'的学生的成绩记录：
delete from Grade
where Sno='2001110';
（6）在学生表的clno属性上创建一个名为'IX_Class'的索引，以班级号的升序排序：create index IX_Class
on Student (clno Asc);
（7）删除'IX_Class'索引：
drop index ;
12.（1）找出所有被学生选修了的课程号：
select distinct Cno
from Grade;
（2）找出01311班女学生的个人信息：
select * from Student
where Clno='01311' and Ssex='女';
（3）找出01311班和01312班的学生姓名、姓名、出生年份
select Sname,Ssex,2014-Sage as [year of birth]
from Student
where Clno='01311' or Clno='01312';
（4）找出所有姓李的学生的个人信息
select * from Student where Sname like '李%';
（5）找出学生李勇所在班级的学生人数
select number
from student inner join class
on =
where sname='李勇'
（6）找出课程名为操作系统的平均成绩、最高分、最低分
select AVG(Gmark) 平均成绩,MAX(Gmark) 最高分,MIN(Gmark) 最低分from Grade
where Cno in
(select Cno from Course
where Cname='操作系统')
（7）选修了课程的学生人数;
select COUNT(distinct sno) 学生人数
from Grade
（8）选修了操作系统的学生人数;
select COUNT(sno) 学生人数
from course inner join grade
on =
where Cname='操作系统'
（9）找出2000级计算机软件班的成绩为空的学生姓名
select Sname 学生姓名
from
(Student inner join class on =
inner join grade on =
where Speciality='计算机软件'
and inyear='2000'
and gmark is null
13.
1)找出和李勇在同一个班级的学生信息
select *
from Student
where clno in
(select Clno from Student
where Sname='李勇')
2)找出所有与学生李勇有相同选修课程的学生信息
select * from Student
where sno in
(select sno from grade
where cno in
(select cno from grade
where sno in (select sno from student
where Sname='李勇')));
3)找出年龄介于学生李勇和25岁之间的学生信息
select * from Student
where Sage <25 and Sage>
(select Sage from Student where Sname='李勇')
4)找出选修了课程是操作系统的学生学号和姓名
select Sno 学号,Sname 姓名 from Student
where sno in
(select sno from Grade
where Cno in
(select Cno from Course
where cno in (select cno from course
where Cname='操作系统')));
5)找出没有选修1号课程的所有学生姓名
select Sname 姓名 from Student
where not exists
(select * from Grade
where = and Cno='1')
6)找出选修了全部课程的学生姓名
select Sname 姓名 from Student
where not exists
(select * from Course
where not exists
(select * from Grade
where =
and =)
14.
1)查询选修了3号课程的学生学号及成绩,并按成绩的降序排列select Sno 学号,Gmark 成绩 from Grade
where Cno='3'
order by Gmark desc
2)查询全体学生信息,要求查询结果按班级号升序排列,同一班级学生按年龄降序排列
select * from Student
order by Clno asc ,Sage desc
3)求每个课程号及相应的选课人数
select 课程号,COUNT 选课人数
from Grade join Course on =
group by
4)查询选修了3门以上课程的学生学号
select Sno 学号 from Grade
group by Sno
having COUNT(Sno)>3
15.
1)将01311班的全体学生的成绩置零
update Grade set Gmark=0
where Sno in
(select Sno from Student
where Clno='01311')
2)删除2001级计算机软件的全体学生的选课记录
delete from Grade
where Sno in
(select Sno from Student
where Clno in
(select Clno from Class
where Speciality='计算机软件' and Inyear='2001'))
3)学生李勇已退学,从数据库中删除有关他的记录
delete from Grade
where Sno in
(select Sno from Student
where Sname='李勇')
update Class set Number=Number-1
where Clno in
(select Clno from Student
where Sname='李勇')
update Class set Monitor=case
when Monitor=(select Sno from Student where Sname='李勇')
then ''
end
from Class
where Clno in(select Clno from Student where Sname='李勇')
delete from Student
where Sname='李勇'
4)对每个班,求学生的平均年龄,并把结果存入数据库
alter table Class add Cage smallint null
update Class set Cage=case
when Clno='00311' then (select AVG(Sage) from Student where Clno='00311') when Clno='00312' then (select AVG(Sage) from Student where Clno='00312') when Clno='01311' then (select AVG(Sage) from Student where Clno='01311') end
from Class
16.
1、
create view stu_01311_1
as select student sno,sname,gmark
from student,grade
where cno=1
and clno=01311
and =
with check option
2、
create view stu_01311_2
an select *
from stu_01311_1
where gmark<60
3.
create view stu_year(sno,sname,years)
as select sno,sname,year(getdate())-sage
from student
4.
select sname
from stu_year
where years>1990
5.
select *
from stu_year
where sno in
(select sno
from stu_01311_2)
第四章
10.
创建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)
)
11.
插入
create trigger stu_insert
on student
after insert
as
update class
set number=number+1
from class,inserted
where = ;
删除
create trigger stu_delete
on student
after delete
as
update class
set number=number-1
from class,deleted
where = ;
12.
create trigger stu_update
on class
after update
as
if update(monitor)
if
( select monitor from inserted ) not in
( select sno from student
where clno = (select clno from deleted ) )
begin
print 'there is not the new monitor in the class'
rollback transaction
end
13.
新建product表
create table product
( pno char(6) primary key,
pname varchar(20) not null,
price decimal(7,2) )
创建仓库表
create table warehouse
( whno char(3) primary key,
whname varchar(20) not null,
whaddress varchar(20) )
创建库存商品表
create table whproduct
( whno char(3) references warehouse(whno) on delete no action on update cascade, pno char(6) references product(pno) on delete cascade on update cascade, number int )
当新增商品是，自动生成改商品在所有仓库的库存记录，库存数量为0
create trigger tri_product
on product
after insert
as
begin
declare @pno char(3)
select @pno=pno from inserted
insert into whproduct
select whno,@pno,0
from warehouse
end
当新增商品是，自动生成改仓库所有商品的库存记录，库存数量为0
create trigger tri_warehouse
on warehouse
after insert
as
begin
declare @whno char(6)
select @whno=whno from inserted
insert into whproduct
select @whno,pno,0
from product
end
14.
1）用户张勇对Student表和Course表有Select权力。
Grant select on student to 张勇
Grant select on course to 张勇
2）把对表Student的INSERT和Delete
权限授予用户张三，并允许他再把此权限授予其他用户。
Grant insert,delete on student to 张三
with grant option
3）把查询Course表和修改属性Credit的权限授给用户李四。
Grant select,update(credit) on course to 李四
4）授予用户李勇敏对Student表的所有权力（读、插、删、改），并具有给其他用户授权的权力。
Grant all privilege on student to 李勇敏 with grant option
5)删除张勇对student表的select权
revoke select
on student
from 张勇;
删除张勇对course表的select权
revoke select
on course
from 张勇;
6)删除张三对student表的insert、delete权
revoke insert,delete
on student
from 张三
cascade;
10.创建一存储过程，根据学生学号查询该学生所以选修课的成绩，学号作为参数输入。create procedure proc_StuGmark
@stu_no char(7)
as
select Gmark,Cno
from Grade
where Sno = @Stu_no
执行过程：execute proc_StuGmark @stu_no = '2000101'
11.创建一存储过程，用来输入的参数：班级名，判断该班级中是否已有学生存在，若有，存储过程返回1；若没有，存储过程返回0
create procedure proc_IsThereStudent
@Class_clno char(5)
as
declare @count int
select @count = count(*)
from Student
where Clno = @Class_clno
if @count = 0
return 0
else
return 1
执行过程：declare @retvalve int
execute @retvalve = proc_IsThereStudent '00311'
if @retvalve = 0
Print '该班级没有学生!'
else
Print '该班级有学生!'
14.
10)
create function Sno_Gmark(@stuno char(7))
returns table
as
return (select Cno,Gmark
from Grade
where Sno = @stuno)
执行过程：select Cno,Gmark
from Sno_Gmark('2000101');
11)
create function class_Stu(@classno char(5))
returns int
as
begin
declare @count int
select @count = count(*)
from Student
where Clno = @classno
if @count <> 0
set @count = 1
return (@count)
end
执行过程：declare @retvalve int
execute @retvalve = class_Stu '00311' if @retvalve = 0
Print '该班级没有学生!'
else
Print '该班级有学生!'