96、题目：
活期存款中，“储户”通过“存取款单”和“储蓄所”发生联系。假定储户包括：账号，姓名，电话，地址，存款额；“储蓄所”包括：储蓄所编号，名称，电话，地址（假定一个储户可以在不同得储蓄所存取款）
1、写出设计以上表格的语句。
Create table 储户 （ 账号 int primary key,姓名char(),电话 int,地址 nvarchar()，存款金额 money ) Create table 储蓄所 （ 编号 int primary key,名称 char(),电话 int,地址 nvarchar() ) Create table 存取款单 （ 账号 Int not null,编号 Int not null,时间 datetime not null, 存取标志 int not null，存取金额 money )
2、创建一个触发器TR1完成下面内容：
当向“存取款单”表中插入数据时，如果存取标志＝１则应该更改储户表让存款额加上存取金额，如果存取标志＝0则应该更改储户表让存款额减去存取金额,如果余额不足显示余额不足错误。
Create trigger InsertInfo on 存取款单 for insert
As
Declare @BZ int,@money money,@zh int
Select @BZ=存取标志，@money=存取金额，@zh=账号
From inserted //从inserted 表中得到插入的记录信息
If @BZ=0 //取钱
Begin
Declare @sy money
Select @sy=存款金额 from 储户 //拿到用户的存款金额
If (@sy<@money) //如果存款金额小于所取的金额，说明金额不够
begin
raiserror ('余额不足')
rollback
End
Else
begin
Update 储户 set 存款金额-=@money where 账号=@zh //更新储户表
End
end
If @BZ=
Begin
Update 储户 set 存款金额+=@money where 账号=@zh
end
97、本题用到下面三个关系表：
CARD 借书卡: (CNO 卡号，NAME 姓名，CLASS 班级)
BOOKS 图书: (BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数 )
BORROW 借书记录: (CNO 借书卡号，BNO 书号，RDATE 还书日期
备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。
要求实现如下处理：
1. 写出自定义函数，要求输入借书卡号能得到该卡号所借书金额的总和。
Create function fun_GetPrice
( @cno int )
Returns money //指定函数的类型
As
Begin
Declare @sum money
Select @sum=sum(price) from book where bno in (select BNO from borrow where CNO=@cno ）
Return @sum //函数的最后一条必须是return语句
end
2. 找出借书超过5本的读者,输出借书卡号及所借图书册数。
Select CNO,Count(bno) as 借书数量 from borrow group by CNO having Count(bno)>
3. 查询借阅了"水浒"一书的读者，输出姓名及班级。
Select Name,Class from Card where CNO in (select CNO from borrow where BNO in(select BNO from books where bname=’水浒’ ) )
4. 查询过期未还图书，输出借阅者（卡号）、书号及还书日期。
Select CNO,BNO,RDate from borrow where RDate<getdate()
5. 查询书名包括"网络"关键词的图书，输出书号、书名、作者。
Select BNO,BName,AUTHOR from books where BName like ‘%网络%’
6. 查询现有图书中价格最高的图书，输出书名及作者。
Select BName,AUTHOR from books where price in(select max(prcie) from books)
7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。
Select CNO from borrow where BNO in (select BNO from books Where bname=’计算方法’) and cno not in (select cno from borrow where
bno in (select bno from books where bname=’计算方法习题集’））order by cno
98.创建以下表结构，并添加一些测试数据：
Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
1. 查询“001”课程比“002”课程成绩高的所有学生的学号；
/*1 */ select c1.s# from Sc as c1,Sc as c2 where c1.s#=c2.s# and c1.c#=''
and c2.c#='' and c1.score>c2.score
/*2 */ select a.s# from (select s#,score from Sc where c#='')a,(select s#,score
from Sc where c#='')b where a.score>b.score and a.s#=b.s#
2. 查询平均成绩大于60分的同学的学号和平均成绩；
select S#,AVG(score) from Sc group by s# having AVG(score)>
3. 查询所有同学的学号、姓名、选课数、总成绩；
select stu.s#,stu.sname,COUNT(C.c#),SUM(c.score) from Student as stu,Sc as c
where stu.s#=c.s# group by stu.s#,stu.sname - - 和聚合函数出现在一起的列，必须都在group by 语句中出现
4. 查询姓“李”的老师的个数；
1、 select COUNT(tname),tname from Teacher where tname like '杨%' group by tname --如果没有不会显示信息
2、 select COUNT(distinct(tname)) from Teacher where tname like '杨%' --distinct 排除重复的列
5. 查询没学过“叶平”老师课的同学的学号、姓名；
1、 select stu.s#,stu.sname from Student as stu where stu.s# not in
(select s.s# from sc as s where s.c# in (select c.c# from Teacher as t,Course as c where t.t#=c.t# and tname='叶平'))
2、 select stu.s#,stu.sname from Student as stu where stu.s# not in (select c.s# from Sc as c,Course as cc,Teacher
as t where c.c#=cc.c# and cc.t#=t.t# and t.tname='叶平') --三表查询,这个写起来简单
6.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select stu.s#,stu.sname from Student as stu where stu.s# in (select c1.s# from Sc as c1,Sc as c2 where c1.s#=c2.s#
and c1.c#='' and c2.c#='') --先找出学了001和002的同学的学号，类似第一题
7.查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select stu.s#,stu.sname from Student as stu where stu.s# in (select c.s# from sc as c,Course as cc,Teacher as t where
c.c#=cc.c# and cc.t#=t.t# and t.tname='叶平') --跟第5题相反
8.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select stu.s#,stu.sname from Student as stu where stu.s# in (select c2.s# from Sc as c1,Sc as c2 where c1.s#=c2.s#
and c2.c#='' and c1.c#='' and c2.score<c1.score)
9.查询所有课程成绩小于60分的同学的学号、姓名；
select stu.s#,stu.sname from Student as stu where stu.s# not in (select hh.s# from Sc as c,Student as hh where
hh.s#=c.s# and c.score>)
10.查询没有学全所有课的同学的学号、姓名；
select s.s#,s.sname from Student as s,Sc as c where s.s#=c.s# group by s.s#,s.sname having COUNT(c#) <
(select COUNT(C#) from Course)
11.查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
select distinct stu.s#,stu.sname from Student as stu,Sc as c where stu.s#=c.s# and c.c# in (select c# from Sc where s#='')
12.查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；
select distinct stu.s#,stu.sname from Student as stu,Sc where stu.s#=sc.s# and c# in (select c# from Sc where Sc.s#='')
13.把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
update Sc set sc.score=( select AVG(c.score) from Sc as c,Course,Teacher where c.c#=Course.c# and
Course.t#=Teacher.t# and Teacher.tname='叶平' and c.c#=sc.c#)
14.查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
select Student.s#,Student.sname from Student,Sc where Student.s#=sc.s# and Sc.c# in(select sc.c# from Sc where sc.s#='') and sc.s#!=''
group by Student.s#,Student.sname having COUNT(c#)=(select COUNT(c#) from Sc where sc.s#='')
15.删除学习“叶平”老师课的SC表记录；
delete Sc where sc.c#=(select Course.c# from Course,Teacher where Course.t#=Teacher.t# and Teacher.tname='叶平')
16.向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2号课的平均成绩；
insert Sc select s#,'',(select AVG(score) from Sc where c#='' ) from Student where s# not in (select s# from sc where c#='')
17.按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
select s# as 学生ID,(select score from Sc where sc.s#=t.s# and c#='') as 数据库,
(select score from Sc where sc.s#=t.s# and c#='') as 企业管理,
(select score from Sc where sc.s#=t.s# and c#='') as 英语
from Sc as t group by s# order by AVG(score)
18.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
select c#,max(score),min(score) from sc group by c#
19.按各科平均成绩从低到高和及格率的百分数从高到低顺序
select sc.c#,cname,isnull(avg(score),) as 平均成绩,*SUM(case when isnull(score,)> then else end)/count(*) as 及格率 from sc,
Course where sc.c#=Course.c# group by sc.c#,cname order by AVG(score),
*SUM(case when isnull(score,)> then else end)/count(*) desc
20.统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]（红色为列名）
select sc.c#,cname,
sum(case when score between and then else end) as [-],
sum(case when score between and then else end) as [-],
sum(case when score between and then else end) as [-],
sum(case when score < then else end) as [<]
from sc,Course where sc.c#=Course.c# group by sc.c#,cname
21.查询每门课程被选修的学生数
select C#,COUNT(S#) from sc group by c#
22.查询出只选修了一门课程的全部学生的学号和姓名
select Student.s#,Student.sname,COUNT(C#) from sc,Student where sc.s#=Student.s# group by Student.s#,Student.sname having COUNT(C#)=
23.查询男生、女生人数
select count(ssex) from Student where ssex='男'
select count(ssex) from Student where ssex='女'
24.查询姓“张”的学生名单
select sname from Student where sname like '张%'
25.查询同名同性学生名单，并统计同名人数
select Student.sname,COUNT(*) from Student group by Student.sname having COUNT(*)>
26.1981年出生的学生名单(注：Student表中Sage列的类型是datetime)
select sname,CONVERT(int,DATEPART(year,sage)) as 年纪 from Student where CONVERT(int,DATEPART(year,sage))=''
CONVERT() 函数可以用不同的格式显示日期/时间数据。
27.查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
select c#,AVG(score) from sc group by c# order by AVG(score),c# DESC
28.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select sc.s#,Student.sname,avg(score) from Student,sc where Student.s#=sc.s# group by sc.s#,Student.sname having AVG(score)>
29.查询课程名称为“数据库”，且分数低于60的学生姓名和分数
select Student.s#,sc.score from Student,sc,Course where Student.s#=sc.s# and sc.c#=Course.c# and cname='数据库' and score<
30.查询所有学生的选课情况；
select Student.s#,sc.c#,sname,cname from Student,sc,Course where Student.s#=sc.s# and sc.c#=Course.c#
31.查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select sname,cname,score from Student,Course,sc where Student.s#=sc.s# and sc.c#=Course.c# and score>
32.查询不及格的课程，并按课程号从大到小排列 ；
select sc.c#,cname from sc,Course where sc.c#=Course.c# and score< group by sc.c# order by sc.c# desc
33.查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
select Student.s#,sname from Student,sc where Student.s#=sc.s# and sc.c#='' and score>
34.求选了课程的学生人数 ；
select count(s#) from sc
35.查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩 ；
select Student.sname,sc.score from student,sc,Course,Teacher where tname='叶平' and Student.s#=sc.s# and sc.c#=Course.c# and
Course.t#=Teacher.t# and sc.score=(select max(score) from sc where sc.c#=Course.c#)
DATEPART