说明：1，在创建数据库并添加示例数据后，改编第三章课后习题3.2，3.7，3.9，3.12。 

          附代码！

       2，以下代码在sql server 2005 express edition 环境中全部执行成功！

       3，此代码仅供学习交流使用！

       4，第一部分为创建数据库和插入示例数据，第二部分为习题，第三部分为删除数据库

-----------------------第二部分：练习题-----------------------------
--3.2.1检索年龄小于17岁的女学生的学号和姓名
select s#,sname from S
where age<17 and sex=0;
--3.2.2检索男生所学课程的课程号和课程名
select c#,cname from C
where c# in (select distinct c# from SC where s# in (select s# from S where sex=1))
--3.2.3检索男生所学课程的任课老师的工号和姓名
select t#,tname from T
where t# in(select distinct t# from C
where c# in(select distinct c# from SC
where s# in(select s# from S
where sex=1)));
--3.2.4检索至少选修两门课程的学生的学号
select s# from SC
group by s#
having count(c#)>=2;
--3.2.5检索至少有学号为080501和080503所学的课程和课程名
select c#,cname from C
where c# in((select c# from sc where s#='080501')
intersect
(select c# from sc where s#='080503') )
--3.2.6检索‘he’同学不学的课程号
--解法一，集合的减法
select c# from c
except
(select distinct c# from sc where s# =(select s# from s where sname='he'))
--解法二，反面法
select c# from c
where c# not in (select distinct c# from sc where s# =(select s# from s where sname='he'))
--3.2.7检索全部学生都选修的课程号和课程名
select c#,cname from c
where not exists(select s# from s
where c.c# not in (select c# from sc
where sc.s#=s.s# ))
--3.2.8检索选修课程包含'九如'老师所授课程的全部课程的学生的学号和姓名
select s#,sname from s
where not exists
(
(select c# from c where t#=(select t# from t where tname='九如')) --朱老师所授的全部课程
except
(select c# from sc where sc.s#=s.s#) --该名学生所选的全部课程
)
--3.7.1统计所有学生选课的课程的门数
select s#,count(c#)as sum_cou
from sc
group by s#
--3.7.2求选修00001课程的女学生的平均年龄
select avg(age) as avg_age from s
where s# in(select s# from sc
where c#='00001')
--3.7.3求九如老师所授每门课程的平均成绩
select c#,avg(score)as avg_scs from sc
where c# in(select c# from c where t#=(select t# from t where tname='九如'))
group by c#
--3.7.4统计每门课程的学生选修人数（超过3人的课程才统计）。
--要求显示课程号和人数，查询结果按人数降序排列，若人数相同，则按课程号升序排列
select c#,count(s#)as sum_ss from sc
group by c#
having count(s#)>3
order by sum_ss desc,c# asc;
--3.7.5检索学号比he同学大，而年龄比他小的学生姓名
--解法一
select sname from s
where age<(select age from s where sname='he')
and s#>(select s# from s where sname='he')
--解法二
declare @age tinyint,@s# nchar(6)
select @age=age,@s#=s# from s
where sname='he'
select sname from s
where s#>@s# and age<@age
--3.7.6在表中检索成绩为空值的学生的学号和课程号
select s#,c# from sc where score is null
--3.7.8求年龄大于女同学平均年龄的男学生的姓名和年龄
select sname,age from s
where sex=1 and age>(select avg(age) from s
where sex=0)
--3.7.9求年龄大于所有女同学的男学生的姓名和年龄
select sname,age from s
where sex=1 and age>all(select age from s
where sex=0)
--3.9.1检索每个学生的学习成绩，成绩显示时以等级形式
select s#,c#,case
when score>=80 then 'A'
when score>=60 then 'B'
when score>=40 then 'C'
else 'F'
end as GRADE from sc
--3.9.2检索每个等级的学生的人数
select GRADE,count(s#)as total from
(
select case
when score>=80 then 'A'
when score>=60 then 'B'
when score>=40 then 'C'
else 'F'
end as GRADE,S# from sc
)as temp
group by GRADE

--3.12.1往关系c中插入一个课程元组('00010','计算机网络','7805').
insert into c values('00010','计算机网络','7805')
--3.12.2检索所授每门课程品均成绩大于80分的教师姓名，
--并把检索到的值送到另一个已存在的表FACULTY（tanme）中
--解法一，利用select into 自动创建新表
if object_id(N'faculty')is not null
drop table faculty;
select tname into faculty from t
where t# in(select t# from c
where c# in(select sc.c# from sc
group by sc.c#
having avg(score)>80))
--select执行后，将在数据库中创建一个新的表（若表以存在，在执行失败）
drop table faculty;
--解法二，手动创建表
if object_id(N'faculty')is not null
drop table faculty;
create table faculty
( tname varchar(10));
insert into faculty select tname from t
where t# in(select t# from c
where c# in(select sc.c# from sc
group by sc.c#
having avg(score)>80))
--利用insert语句向表中插入数据
drop table faculty;
--解法三，定义临时表
create table #faculty
( tname varchar(10));
insert into #faculty select tname from t
where t# in(select t# from c
where c# in(select sc.c# from sc
group by sc.c#
having avg(score)>80))
--临时表可在会话结束后自动被删除
--解法四，定义表变量
declare @faculty table(tname varchar(10) primary key)
insert into @faculty select tname from t
where t# in(select t# from c
where c# in(select sc.c# from sc
group by sc.c#
having avg(score)>80))
--表变量亦不需要显示删除
--3.12.3在sc中删除尚无成绩的选课元组
delete from dbo.SC where score is null
--3.12.4把选修九如老师课程的女同学选课元组全部删去
delete from dbo.SC
where sc.s# in(select s.s# from s where sex=0)
and sc.c# in(select c# from c
where c.t# =(select t# from t
where tname='九如'))
--3.12.5把计算机课不及格的同学成绩全部改为60分
update sc
set score=60
where score<60
--3.12.6把低于所有课程总平均成绩的女同学的成绩提高5%
update sc
set score=score*1.05
where score<all(select avg(temp2.score) from sc as temp2)
and s# in(select s# from s where sex=0)
--3.12.7在表sc中修改00001的课程的成绩，当成绩小于等于70分时提高5%，
--若成绩大于70分时提高4%。（一，两个update语句；二，case语句）
--创建触发器，防止更新时违反约束
if object_id(N'up_sco','Tr')is not null
drop trigger up_sco;
go
create trigger up_sco on sc
instead of update ,insert
as
declare @s# nchar(6),@c# nchar(5),@score tinyint
select @s#=s#,@c#=c#,@score=cast(score as tinyint) from inserted
if(@score>=100)
set @score=100
delete from sc where s#=@s# and @c#=c#;
insert into sc(s#,c#,score) values(@s#,@c#,@score)
go
--解法一
update sc
set score=score*1.05
where score<=70;
update sc
set score=score*1.04
where score>70;
--解法二
update sc
set score=case
when score>70 then score*1.04
else score*1.05
end
--解法三，游标
declare cus_sc cursor for select score from sc for update of score
declare @ts tinyint
open cus_sc
fetch next from cus_sc into @ts
while @@fetch_status=0
begin
update sc
set score=case
when score>70 then score*1.04
else score*1.05
end
where current of cus_sc
fetch next from cus_sc into @ts
end
close cus_sc --关闭游标结果集
deallocate cus_sc --释放游标资源
--3.12.8在表sc中，当某个成绩低于全部课程的平均成绩时，提高5%
--解法一
update sc
set score=score*1.05
where score<(select avg_score from (select avg(score) as avg_score,s# from sc group by s#) as temp
where temp.s#=sc.s# )

--解法二：CTE
;with temp
as
(select avg(score)as avg_score,s# from sc group by s#)
update sc
set score=score*1.05
where score<(select avg_score from temp where temp.s#=sc.s#)

-----------------------------第一部分：创建数据库并插入示例数据--------------------- 

use master
go
if db_id('test2') is not null
   drop database test2;
go
create database test2
go
use test2
go
--创建教师表
if object_id(N'T')is not null
    drop table T;
create table T
(
 t# nchar(4) primary key,
 tname nvarchar(10) not null,
 title nvarchar(15) not null
)
--创建并修改课程表
if object_id(N'C')is not null
    drop table C;
create table C
( 
 c# nchar(5) primary key,
 cname nvarchar(16) not null,
 t# nchar(4)
)
alter table C
add constraint fk_c foreign key (t#) references T(t#);
alter table C
add constraint uni_c unique (cname)
go
--创建并修改学生表
if object_id(N'S')is not null
    drop table S;
create table S
(
 s# nchar(6) not null,
 sname nvarchar(10),
 age tinyint not null,
 sex bit not null,
 check(age<=50)
)
alter table S
alter column sname nvarchar(10) not null;
alter table S
add constraint pk_s primary key (s#); --若原表中定义s#为可为空（默认），则此时添加主键失败
go
--创建选课关系表
if object_id(N'SC')is not null
    drop table SC;
create table SC
(
 s# nchar(6) not null,
 c# nchar(5) not null,
 score tinyint  null,
 primary key (s#,c#),
 foreign key (s#) references S(s#),
 foreign key (c#) references C(c#)
)
alter table sc
add constraint ch_sco check(score<=100)
go

--插入示例数据

use test2
go
delete  from SC;  --由于存在外键约束，则truncate将会报错，即使依赖表为空
go
delete  from S;
go
delete  from C;
go
delete  from T;
go
set nocount on
go
--向教师表中插入数据
insert into T values('7801','九如','博士');
insert into T values('7802','风怜','讲师');
insert into T values('7803','梁萧','教授');
insert into T values('7804','花生','副教授');
insert into T values('7805','花晓霜','教授');

--向课程表中插入数据
insert into C(c#,t#,cname) values('00001','7801','计算机');
insert into C(c#,t#,cname) values('00002','7801','操作系统');
insert into C(c#,t#,cname) values('00003','7801','数据库');
insert into C(c#,t#,cname) values('00004','7802','外国文学史');
insert into C(c#,t#,cname) values('00005','7802','英语语法');
insert into C(c#,t#,cname) values('00006','7803','算术');
insert into C(c#,t#,cname) values('00007','7803','地理');
insert into C(c#,t#,cname) values('00008','7804','体育');
insert into C(c#,t#,cname) values('00009','7805','医术');
--向学生表中插入数据
insert into  S(s#,sname,sex,age) values('080501','dong',1,19);
insert into  S(s#,sname,sex,age) values('080502','he',1,21);
insert into  S(s#,sname,sex,age) values('080503','shi',1,21);
insert into  S(s#,sname,sex,age) values('080504','zhou',1,20);
insert into  S(s#,sname,sex,age) values('080505','gu',1,18);
insert into  S(s#,sname,sex,age) values('080506','tang',0,20);
insert into  S(s#,sname,sex,age) values('080507','liu',0,15);

--向选课表中中插入数据
insert into SC values('080501','00001',95);
insert into SC values('080501','00002',20);
insert into SC values('080501','00003',86);
insert into SC values('080501','00004',90);
insert into SC values('080501','00005',18);
insert into SC values('080501','00006',82);
insert into SC values('080501','00007',70);
insert into SC values('080501','00008',29);

insert into SC values('080502','00001',80);
insert into SC values('080502','00002',51);
insert into SC values('080502','00003',74);
insert into SC values('080502','00004',70);
insert into SC values('080502','00005',78);

insert into SC values('080503','00001',81);
insert into SC values('080503','00002',90);
insert into SC values('080503','00009',null);
insert into SC values('080503','00005',90);
insert into SC values('080503','00003',39);

insert into SC values('080504','00001',80);
insert into SC values('080504','00002',90);

insert into SC values('080505','00001',35);
insert into SC values('080505','00009',45);

insert into SC values('080506','00001',80);
insert into SC values('080506','00002',45);
insert into SC values('080506','00009',65);

insert into SC values('080507','00001',85);
insert into SC values('080507','00003',45);

set nocount off
go

---------------------第三部分：删除数据库-------------------------

use master
go
if db_id('test2') is not null
drop database test2;
go