第三章SQL练习题参考答案.doc

11、针对以上四个表，用SQL语言完成以下各项操作：
① 给学生表增加一属性Nation（民族），数据类型为Varchar（20）；
Alter table student
add Nation Varchar(20);
② 删除学生表中新增的属性Nation；
Alter table student
drop column Nation;
③ 向成绩表中插入记录（”2001110”，”3”，80）；
insert into Grade
values('2001103','3',80);
④ 修改学号为”2001103”的学生的成绩为70分；
update Grade
set Gmark=70
where Sno='2001103';
⑤ 删除学号为”2001110”的学生的成绩记录；
delete Grade
where Sno='2001110';
⑥ 为学生表创建一个名为，以班级号的升序排序；
create index IX_Class on student(Clno ASC);
⑦删除IX_Class索引
Drop index IX_Class

12、针对以上四个表，用SQL语言完成以下各项查询：
① 找出所有被学生选修了的课程号；
select distinct cno
from grade;
② 找出01311班女学生的个人信息；
select *
from student
where clno=01311 and ssex='女';
③ 找出01311班、01312班的学生姓名、性别、出生年份；
Select sname,ssex, year(getdata())-sage as ‘出生年份’
from student
where clno in('01311','01312');
④ 找出所有姓李的学生的个人信息；
Select *
from student
where sname like ’李%’；
⑤ 找出学生李勇所在班级的学生人数；
Select count(*)
from student
where clno in (select clno
from student
where sname= '李勇');

⑥ 找出课程名为操作系统的平均成绩、最高分、最低分；
Select avg(gmark),max(gmark),min(gmark)
from grade
where cno =(select cno
from course
where cname='操作系统');
⑦ 找出选修了课程的学生人数；
Select count(distinct sno)
from grade;
⑧ 找出选修了课程操作系统的学生人数；
Select count(sno)
from grade
where cno =(select cno
from course
where cname='操作系统');
⑨ 找出2000级计算机软件班的成绩为空的学生姓名。
Select sname
from student
where clno in (select clno
from class
where inyear=’2000’ and speciality=’计算机软件’) and sno in (select sno
from grade
where gmark is null);

13、针对以上四个表，用SELECT的嵌套查询完成以下各项查询：
① 找出与李勇在同一个班级的学生信息；
Select *
from student
where clno = (select clno
from student
where sname=’李勇’)；
② 找出所有与学生李勇有相同选修课程的学生信息；
Select *
from student
where sno in (select distinct sno
from grade
where cno in ( select cno
from grade
where sno=(select sno
from student
where sname='李勇')))

③ 找出年龄介于学生李勇和25岁之间的学生信息；
Select *
from student
where sage between (select sage
from student
where sname=’李勇’) and 25
④ 找出选修了课程操作系统的学生学号和姓名；
Select sno,sname
from student
where sno in (select sno
from grade
where cno = (select cno
from course
where cname=’操作系统’ ))
⑤ 找出所有没有选修1号课程的学生姓名；
Select sname
from student
where not exists (select *
from grade
where sno=student.sno and cno='1')
OR:
Select sname
from student
Where sno not in (Select sno
from grade
Where cno=’1’);

⑥ 找出选修了全部课程的学生姓名。
（提示：可找出这样的学生，没有一门课程是他不选修的。）
Select sname
from student
where not exists (select * from course
where not exists (select * from grade
where sno=student.sno
and cno=o))
解二:
Select sname from student
Where sno in
(Select sno from grade
Group by sno
Having count(*)=Select count(*) from course);

14、针对以上四个表，用SQL语言完成以下各项查询：
① 查询选修了3号课程的学生学号及其成绩，并按成绩的降序排列；
Select sno,gmark
from grade
where cno=’3’
order by gmark desc
② 查询全体学生信息，查询结果按班级号升序排列，同一班级学生按年龄降序排列；
select *
from student
order by clno,sage desc
③ 求每个课程号及相应的选课人数；
Select cno ,count(sno)
from grade
group by cno
④ 查询选修了3门以上课程的学生学号。
Select sno
from grade
group by sno
having count(*)>3

15、针对以上四个表，用SQL语言完成以下各项操作：
① 将01311班的全体学生的成绩置零；
update grade
set gmark=0
where sno in (select sno
from student
where clno=’01311’)
② 删除2001级计算机软件的全体学生的选课记录；
Delete from grade
where sno in (select sno
from student
where clno=(select clno
from class
where speciality=’计算机软件’
and inyear=’2001’))
③ 学生李勇已退学，从数据库中删除有关他的记录。
update class
set monitor=null
where clno in (select clno
from student
where sname='李勇')

delete from student
where sname='李勇'
注：grade表中外键sno设置了级联修改，因此该命令执行以后，grade表中李勇的选课记录自动删除）
④对每个班，求学生的平均年龄，并把结果存入数据库；
方案一：建立视图
create view v_sag
as select clno,AVG(sage) as ‘sag’
from student
group by clno)
方案二：class表中增加一列，用于存放平均年龄
alter table class
add avg_sage smallint
update class
set avg_sage=(select avg(sage)
from student
where clno=class.clno)
方案三：建立一存放平均年龄的表单，插入平均年龄
create table 平均年龄表
（clno char(5) primary key,
avg_sage smallint）
insert into 平均年龄表
select clno,avg(sage)
from student
groupe by clno

16、视图操作：
① 建立01312班选修了1号课程的学生视图Stu_01312_1；
create view Stu_00312_1
as select *
from Student
where Student.Sno=any(select Sno from Grade
where Grade.Cno='1')
and Student.Clno='00312'
②建立01312班选修了1号课程并且成绩不及格的学生视图Stu_01312_2；
create view Stu_00312_2
as select *
from Student
where Sno in (select Sno
from Grade
where Grade.Cno='1'and Grade.Gmark<='60')
and Clno='00312'
③建立视图Stu_year，由学生学号、姓名、出生年份组成。
create view Stu_year
as select sno as ‘学号’,sname as ‘姓名’,’出生年份’=year(getdate())-sage
from Student
④查询1990年以后出生的学生姓名。
select 姓名
from Stu_year
where 出生年份>1990
⑤查询01312班选修了1号课程并且成绩不及格的学生的学号、姓名、出生年份。
select *
from Stu_year
where 学号 in (select sno
From Stu_00312_2 )

1、 建立一个学生信息表student，表中有5个字段：学号Sno（字符型），姓名Sname（字符型），性别Ssex（字符型），年龄Sage（整型），所在系Sdept（字符型），其中Sno是关系的码；用SQL语言完成题目1-6：
（1）、建立学生信息表student。（要求：包含主码的定义；定义学生的年龄在15到40之间）
（1）、 
CREATE table student 
( sno char(8) primary key,
sname varchar(20) not null, 
ssex varchar(2) check(ssex in (‘男’,’女’)),
sage int check(sage between 15and 40),
sdept varchar(20))

定义视图，完成如下功能：输出学生平均年龄大于19的系及该系学生的平均年龄。

CREATE view v_ student
As 
SELECT sdept, avg(sage) 平均年龄
From student
Group by sdept 
Having avg(sage)>19