面试题3
96、题目：
活期存款中，“储户”通过“存取款单”和“储蓄所”发生联系。假定储户包括：账号，姓名，电话，地址，存款额；“储蓄所”包括：储蓄所编号，名称，电话，地址（假定一个储户可以在不同得储蓄所存取款）
1、写出设计以上表格的语句。
Create table 储户 （ 账号 int primary key,姓名char(10),电话 int,地址 nvarchar(50)，存款金额 money ) Create table 储蓄所 （ 编号 int primary key,名称 char(10),电话 int,地址 nvarchar(50) ) 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=1
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
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#='001' and c2.c#='002' and c1.score>c2.score /*2 */ select a.s# from (select s#,score from Sc where c#='001')a,(select s#,score from Sc where c#='002')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)>60
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#='001' and c2.c#='002') --先找出学了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#='002' and c1.c#='001' 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>60)
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#='1001')
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#='001')
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#='002') and sc.s#!='002'
group by Student.s#,Student.sname having COUNT(c#)=(select COUNT(c#) from Sc where sc.s#='002')
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#,'002',(select AVG(score) from Sc where c#='002' ) from Student where s# not in (select s# from sc where c#='003')
17.按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
select s# as 学生ID,(select score from Sc where sc.s#=t.s# and c#='004') as 数据库, (select score from Sc where sc.s#=t.s# and c#='001') as 企业管理, (select score from Sc where sc.s#=t.s# and c#='006') 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),0) as 平均成绩,100*SUM(case when isnull(score,0)>60 then 1 else 0 end)/count(*) as 及格率 from sc, Course where sc.c#=Course.c# group by sc.c#,cname order by AVG(score),
100*SUM(case when isnull(score,0)>60 then 1 else 0 end)/count(*) desc
20.统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]（红色为列名）
select sc.c#,cname, sum(case when score between 85 and 100 then 1 else 0 end) as [100-85], sum(case when score between 85 and 70 then 1 else 0 end) as [85-70], sum(case when score between 70 and 60 then 1 else 0 end) as [70-60], sum(case when score <60 then 1 else 0 end) as [<60] 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#)=1
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(*)>1
26.1981年出生的学生名单(注：Student表中Sage列的类型是datetime)
select sname,CONVERT(int,DATEPART(year,sage)) as 年纪 from Student where CONVERT(int,DATEPART(year,sage))='1981'
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)>85
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<60
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>70
32.查询不及格的课程，并按课程号从大到小排列 ；
select sc.c#,cname from sc,Course where sc.c#=Course.c# and score<60 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#='003' and score>80
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#)
