sql sever 练习

数据库中有以下三个表:

学生基本情况表student_info

课程信息表curriculum

学生成绩表grade

请写出完成以下功能的SQL语句

1、创建一个视图，查询至少有5名学生选修的并以1开头的课程号的平均分。

create view view1
as
select 课程编号,avg(分数)a from grade a
where 课程编号 like '1%'
group by 课程编号
having count(*)>4

老师给的答案 

Create view ab
AS
Select 课程号，avg(分数) from grade
Where 课程号 like '1%'
Group by 课程号
Having count(*)>5

第一题答案与老师给的没多大区别。最后 having 语句可能是理解的不同吧。

2、查询选修“0001”课程的成绩高于“0002”号学生成绩的所有学生记录，并按成绩从高到低排列。

select 姓名,分数 
from student_info,grade
where student_info.学号 = grade.学号 and 课程编号='0001' and 分数>
    (select 分数 from grade where 课程编号='0001' and 学号='0002' )	
order by 分数 desc

老师给的答案  

Select x.课程号，x.学号，x.分数 
from grade x,grade y
Where x.课程号='0001'  and x.分数>y.分数
And y.学号='0002' and x.课程号='0001'
Order by x.分数 desc

出现两个x，y临时表，相当于分开用的两个grade表。

或

Select 课程号，学号，分数 from grade 
Where 课程号='0001'  and 分数>(Select 分数 from grade
where 学号='0002' and 课程号='0001')
Order by x.分数 desc

使用子查询的方法结构比较清晰。

3、开始一个事务，查询与学号为“0001”的学生同年出生的所有学生的学号，姓名和出生日期。并将0001号学生的家庭住址改为“广东省中山市”。提交事务。

begin transaction
    select 学号,姓名,出生日期 from student_info
    where year(出生日期)=(select year(出生日期) from student_info where 学号='0001')
    go
    update student_info
    set 家庭地址='广东省中山市'
    where 学号='0001'
    go
COMMIT TRANSACTION

老师给的答案  

Begin transaction
Select 学号，姓名，出生日期
From student_info 
Where year(出生日期)=（Select year(出生日期)
From student_info
Where 学号='0001'）
Update student_info set 家庭住址='广东省中山市' where 学号='0001'
Commit transaction

4、创建一个存储过程，查询给定学号学生参加的所有课程的平均成绩，并返回其姓名及成绩等级，其中成绩等级以ABCD显示。

A等：[85-100]; B等：[75-85)

C等:[60,75); D等：<60

create procedure p_dingji @xuehao char(4)
as
declare @fenshu int
set @fenshu=(select avg(分数) from grade where 学号=@xuehao) 
select 姓名,
case 
 when @fenshu>=85 then 'A'
 when 85>@fenshu and @fenshu>=75 then 'B'
 when 75>@fenshu and @fenshu>=60 then 'C'
 when @fenshu<60 then 'D'
end as '成绩'
from student_info where 学号=@xuehao

一般写程序应该将85>@fenshu and @fenshu>=75 改为 @fenshu<85 and @fenshu>=75 来写才对。但是这里这样写好像是能运行的。

老师给的答案：

Create procedure proA @no char(4),@name char(10) output,@score char(2) output
AS
Select @name=姓名，@score=
Case
    When avg(分数)>=85 and avg(分数)<=100 then ‘A’
    When avg(分数)>=75 and avg(分数)<85 then ‘B’
    When avg(分数)>=60 and avg(分数)<75 then ‘C’
    When avg(分数)<60 then ‘D’
end
From student_info inner join grade
On student_info.学号=grade.学号
Where student_info.学号=@no
GROUP BY 姓名
GO

创建存储过程，设定参数。（后面加output用于返回值）。

老师这里的select是赋值用的，我的答案的select是查询用的。

5.  创建一个teacher表，有教师编号，教师姓名，性别，年龄,部门五个列，主键为教师编号，性别列取值为“男”或“女”。为curriculum表增加一列：教师编号，并建立curriculum表与teacher表的联系。

create table teacher(
教师编号 char(4)primary key,
教师姓名 char(10) ,
性别 char(2) check(性别='男' or 性别='女'),
年龄 int,
部门 varchar(20)
)
go
alter table curriculum 
add 教师编号 char(4) foreign key references teacher(教师编号)

老师给的答案：

Create table teacher(
   教师编号 char(4) primary key,
   教师姓名 varchar(20),
   性别 char(2) check(性别 in(‘男’ ,‘女’))
   年龄 int,
   部门 varchar(20)
)
Alter table curriculum
Add column 教师编号 char(4)
Alter table curriculum
Add constraint fk_c foreign key(教师编号) refereces teacher(教师编号)

 

老师这里的外键是在创建表后再把约束加上去。我是更改表结构创建列的时候直接加的。

6. 当删除student_info表中的学生信息时，同时删除grade表中的相关信息。

create trigger d_grade
on student_info after delete
as
delete grade where 学号 = (select 学号 from deleted)

老师给的答案：

CREATE  trigger stu_delete on student_info
After delete
AS
      declare @no varchar(5)
      SELECT @no=学号 FROM deleted
      DELETE FROM  grade WHERE 学号=@no
GO

没什么区别。上面用的是子查询找到刚刚删除的学号信息，来进行匹对。后面老师的是通过一个变量来完成信息的传递，将学号信息从deleted表传到grade表这样。

7. 新建一个表course，具有课程自动编号，课程名称，学分，教师编号四列。其中“课程自动编号”列为自动编号，其余三列从curriculum表中导入数据。

create table course(
课程自动编号 int identity(1,1) primary key,
课程名称 varchar(50),
学分 int,
教师编号 char(4)
)
go
insert into course(课程名称,学分,教师编号) select 课程名称,学分,教师编号 from curriculum 
go

老师给的答案：

Create table course(
课程自动编号 int identity(1,1),
课程名称 varchar(50),
学分 int,
教师编号 char(4))
Insert into course(课程名称, 学分, 教师编号)
Select课程名称, 学分, 教师编号from curriculum

使用 insert into……select 可以大批量的插入数据。

8. 使用游标,查询没有担任课程的教师编号,姓名及部门.

declare st_cursor1 cursor for select 教师编号,教师姓名,部门 
from teacher where 教师编号 in (select 教师编号 from curriculum)
open stu_cursor1
fetch next from stu_cursor
while @@FETCH_STATUS=0
begin
 fetch next from stu_cursor
end
close stu_cursor1

老师给的答案：

declare tea_cursor cursor scroll
for
select 教师编号,姓名,部门 from teacher where教师编号 not in
(select 教师编号from curriculum)
GO

OPEN tea_cursor              --打开游标  
FETCH NEXT FROM tea_cursor
WHILE @@FETCH_STATUS = 0          --FETCH语句执行成功  
    FETCH NEXT FROM tea_cursor 
CLOSE tea_cursor            
DEALLOCATE tea_cursor

9. 统计各门课程的成绩分布情况,即统计各分数段人数,显示:

课程编号,课程名称,[100-85],[85-70],[70-60],[ <60]

select curriculum.课程编号,课程名称,
sum(case when 分数>=85 and 分数<=100 then 1 else 0 end) '[100-85]',
sum(case when 分数>=70 and 分数<=84 then 1 else 0 end)'[84-70]',
sum(case when 分数>=60 and 分数<=69 then 1 else 0 end)'[69-60]',
sum(case when 分数<60 then 1 else 0 end) '[<60]'
from curriculum,grade
where curriculum.课程编号=grade.课程编号
group by curriculum.课程编号,课程名称

老师给的答案：

SELECT grade.课程编号,课程名称,
SUM(CASE WHEN 分数 BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100-85] ,
SUM(CASE WHEN 分数 BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85-70] ,
SUM(CASE WHEN 分数 BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70-60] ,
SUM(CASE WHEN 分数 < 60 THEN 1 ELSE 0 END) AS [<60] 
FROM grade inner join curriculum on grade.课程编号=curriculum.课程编号
GROUP BY grade.课程编号,课程名称

基本是一样的。利用case作判断，符合条件返回1这样，通过sum数出有多少个符合条件的

两边的'[100-85]'或AS [100-85]其实效果是一样的。

除了用sum还有用count 也可以

select count(case when 分数>=85 then 学号 end)A,
count(case when (分数<85 and 分数>=70) then 学号 end)B,
count(case when (分数<70 and 分数>=60) then 学号 end)C,
count(case when 分数<60 then 学号 end)D
from grade

但原理解释起来很怪

10. 向grade表中插入一些记录，这些记录要求符合以下条件：没有上过编号“0003”课程的同学学号、0002号课的平均成绩.

insert into grade(学号,课程编号,分数) select 学号,
'X002',
(select avg(分数) from grade where 课程编号='0002') 
from student_info where 学号 not in (select 学号 from grade where 课程编号='0003')

老师给的答案：

Insert grade(学号,课程编号,分数)
select 学号,'0002',(Select avg(分数) from grade where 课程编号='0002') 
from Student_info 
where 学号 not in (Select 学号 from grade where 课程编号='0003')

按照我的理解就是学号为课程编号0003没有成绩的，分数统一为平均分这样。由于grade表不能少了课程编号，所以插入的数据除学号和分数插多一个课程编号。考虑到没有0003成绩的学生会有0002的，所以课程编号用x002代替。

11. 建立触发器,当向curriculum表插入课程信息时,承担课程的教师只允许插入现有教师信息.

create trigger tr_i_laoshi
on curriculum after insert
as
begin
 declare @bianhao char(4)
 select @bianhao= inserted.教师编号 from inserted
 if not exists(
 select 教师编号 
 from teacher 
 where 教师编号=@bianhao
 )
 begin
 raiserror('输入的教师编号不存在',16,1)
 rollback
 end
end

老师给的答案：

Create trigger tri_ins on curriculum
After insert
AS
  declare @teano varchar(4)
  SELECT @teano=教师编号 FROM inserted
  IF not exists (SELECT * FROM teacher WHERE教师编号=@teano)
  Print(‘指定插入的教师编号不存在!请确认后重新插入!’)

Rollback

GO