数据库12.17实验

数据库实验

实验十四：T-SQL(12)
1  实验目的
    1．  熟悉数据库的交互式SQL工具。
    2．  熟悉通过SQL对数据库进行操作。
    3．  完成作业的上机练习。
2  实验平台
利用SQLServer及其交互式工具SSMS来熟悉T-SQL。
3  实验内容及要求
     基于student、course、sc表，用SQL语句实现如下要求，填写实验报告，记录所有的实验用例。
（1）编写一个存储过程，可以查询指定系的学生的选课信息，列出学号、姓名、所在系、课程名和成绩等内容。调用该存储过程，测试执行结果。
（2）编写一个存储过程，返回指定课程的平均分。调用该存储过程，测试执行结果。
（3）编写一个存储过程，可以查询指定系指定成绩等级的学生的选课信息，列出学号、姓名、所在系、课程名和成绩等内容。调用该存储过程，测试执行结果。（成绩等级为优、良、中、及格、不及格，其中成绩在90分到100分之间为‘优’，在80分到89分之间为‘良’，在70分到79分之间为‘中’，在60分到69分之间为‘及格’，在0分到59分之间为‘不及格’。）

-1 建表代码

create database Students
on
primary(name=Students,
   filename='D:\test\test.mdf',
size=4mb,
maxsize=10mb,
filegrowth=2mb
  )
log on
(name=Studentslog,
filename='D:\test\testlog.ldf',
size=1mb,
maxsize=5mb,
filegrowth=1mb
)
use Students;
create table Student
(
sno char(8) not null primary key,
Sname char(15) null,
Ssex char(4) null,
Sage int null,
Sdept char(10) null
)
create table Course
(
Cno char(6) not null,
Cname char(10),
Cpno char(6),
Ccredit int null,
primary key(cno),
)
go 
create table SC
(
Sno char(8) not null foreign key references Student(sno),
Cno char(6) not null foreign key references course(cno),
primary key(Sno,Cno),
Grade int  null
)
insert into Student values('19130201','丁鹏','男',20,'Software')
insert into Student values('19130202','王韵婷','女',20,'Software')
insert into Student values('19130203','尹嘉琪','男',18,'Software')
insert into Student values('19130204','卢冬冬','男',20,'Software')
insert into Student values('19130205','史逸凡','男',19,'Software')
insert into Course values('1','数据库',5,4)
insert into Course values('2','数学',null,2)
insert into Course values('3','信息系统',1,4)
insert into Course values('4','操作系统',6,3)
insert into Course values('5','数据结构',7,5)
insert into Course values('6','数据处理',null,2)
insert into Course values('7','PASCAL语言',6,4)
insert into SC values('19130201',1,99)
insert into SC values('19130202',5,95)
insert into SC values('19130203',3,100)
insert into SC values('19130205',1,93)
insert into SC values('19130204',5,92)
alter table course add  foreign key(cpno) references course(cno)
select * from Course;
select * from SC;
select * from Student;

0 上课示例

create procedure sp_1(@sdept char(4))
as
begin
    select a.sno,a.sname,b.grade 
    from student a,sc b
    where sdept =  @sdept
    and a.sno=b.sno
end
//调用
execute sp_1 'software'

create proc sp_2(@cno1 char(4),@avge int output)
as
begin
    select @avge = avg(grade) 
    from sc 
    where cno=@cno1
end

declare @avge1 int
declare @cno2 char(4)
set @cno2='001'
select  @cno2='001'
exec sp_2 @cno2,@avge1 output
select  @avge1

if @x1>1 and @x1<10
begin
end
else
begin
end

（1）编写一个存储过程，可以查询指定系的学生的选课信息，列出学号、姓名、所在系、课程名和成绩等内容。调用该存储过程，测试执行结果。

create procedure sp_2(@sdept char(20))
as
begin
select a.sno,a.sname,a.sdept,b.cname,c.grade
from Student a,course b,sc c
where a.sno=c.sno
and b.cno=c.cno
and a.sdept=@sdept
end
execute sp_2 'software'

（2）编写一个存储过程，返回指定课程的平均分。调用该存储过程，测试执行结果。

create proc sp_3(@cno1 char(4),@avge int output)
as
begin
    select @avge = avg(grade) 
    from sc 
    where cno=@cno1
end
declare @avge1 int
declare @cno2 char(4)
set  @cno2='3'
exec sp_3 @cno2,@avge1 output
select  @avge1

（3）编写一个存储过程可以查询指定系指定成绩等级的学生的选课信息，列出学号、姓名、所在系、课程名和成绩等内容。调用该存储过程，测试执行结果。（成绩等级为优、良、中、及格、不及格，其中成绩在90分到100分之间为‘优’，在80分到89分之间为‘良’，在70分到79分之间为‘中’，在60分到69分之间为‘及格’，在0分到59分之间为‘不及格’。）

create  procedure sp_6(@sdept varchar(20),@GradeLevel varchar(6))
as
Declare @SQLText varchar(200),@GradeStr varchar(30)
Set @SQLText=
'Select S.sno, S.sname, S.Sdept, C.Cname, SC.grade 
From Student S 
Left Join SC 
on S.sno=SC.sno 
Left Join Course C 
on SC.Cno=C.cno'
Set @GradeStr= Case 
    When @GradeLevel='优' then 'between 90 And 100'
    When @GradeLevel='良' then 'between 80 And 89'
    When @GradeLevel='中' then 'between 70 And 79'
    When @GradeLevel='及格' then 'between 60 And 69'
    When @GradeLevel='不及格' then 'between 0 And 59'
    When @GradeLevel IS NULL then 'IS NULL'
    Else  'LevelError' 
        end
IF @GradeStr='LevelError' 
      print '错误：输入的成绩等级不符合要求！'
Else
       Execute(@SQLText+' where Sdept='''+@sdept +''' And Grade '+@GradeStr)
Execute sp_6  'software','优'