sql 实验三.sql

这是一个学生选课系统

create table Department
 (DepID varchar(20) not null primary key,
  DepName varchar(100)
 );

create table Student
 (SNum varchar(20) not null primary key,
  SName varchar(20),
  Sex char(2),
  SBir datetime,
  DepID varchar(20),
  foreign key(DepID) references Department 
 );

create table Teacher
 (TNum varchar(20) not null primary key,
  TName varchar(20),
  PJT varchar(20),
  DepID varchar(20) not null,
  foreign key(DepID) references Department
 );

create table Course
 (CNum varchar(20) not null primary key,
  CName varchar(100),
  Cate varchar(20),
  Credit float,
  TNum varchar(20),
  foreign key(TNum) references Teacher
 );

create table SelCourse
 (SNum varchar(20) not null,
  CNum varchar(20) not null,
  Mark float,
  primary key(SNum,CNum)
 );

insert into Department values('01','土木工程');
insert into Department values('02','电器工程');
insert into Department values('03','应用物理');
insert into Department values('04','机械与制造');
insert into Department values('05','应用化学');
insert into Department values('06','工商管理');
insert into Department values('07','数学与计量');
insert into Department values('08','信息安全');
insert into Department values('09','法律');
insert into Department values('10','外语');
insert into Department values('11','汉语言文学');
insert into Department values('12','影视与艺术');

insert into Student values('030102','Ada','女','85-09-23','01');
insert into Student values('030202','Bill','男','85-03-21','02');
insert into Student values('040303','Emily','女','86-08-07','03');
insert into Student values('050403','Gene','男','87-01-01','04');
insert into Student values('020502','Harry','男','84-03-06','05');
insert into Student values('050601','Jack','男','87-04-15','06');
insert into Student values('040701','Flora','女','86-10-31','07');
insert into Student values('030802','Basil','男','85-07-13','08');
insert into Student values('040901','Jeff','男','86-03-26','09');
insert into Student values('041001','Jean','女','86-10-11','10');

insert into Teacher values('0101','William','讲师','01');
insert into Teacher values('0201','Tony','讲师','02');
insert into Teacher values('0301','Ginny','讲师','03');
insert into Teacher values('0401','Ted','讲师','04');
insert into Teacher values('0501','Vincent','教授','05');
insert into Teacher values('0601','Javier','助教','06');
insert into Teacher values('0602','Karen','讲师','06');
insert into Teacher values('0701','Kelly','教授','07');
insert into Teacher values('0801','Mandy','讲师','08');
insert into Teacher values('0901','Nell','教授','09');
insert into Teacher values('1001','Henry','助教','10');
insert into Teacher values('1101','Ian','讲师','11');
insert into Teacher values('1102','Gavin','教授','11');
insert into Teacher values('1201','Luther','讲师','12');

insert into Course values('1000','数学','必修','5.0','0701');
insert into Course values('1001','物理','必修','3.5','0301');
insert into Course values('1002','英语','必修','3.5','1001');
insert into Course values('1003','建筑学','必修','3.0','0101');
insert into Course values('1004','法律学基础','必修','2.0','0901');
insert into Course values('1005','计算机网络','必修','4.0','0801');
insert into Course values('1006','电子电路','必修','5.0','0201');
insert into Course values('1007','工程力学','必修','4.5','0401');
insert into Course values('1008','化学与工艺','必修','5.0','0501');
insert into Course values('1009','管理学','必修','3.5','0601');
insert into Course values('1010','影视艺术赏析','选修','2.0','1201');
insert into Course values('1011','硬笔书法','选修','2.0','1101');
insert into Course values('1012','现代礼仪','选修','2.0','0602');
insert into Course values('1013','孙子兵法','选修','2.0','1102');

insert into SelCourse values('030102','1003','76');
insert into SelCourse values('030102','1000','85');
insert into SelCourse values('030102','1013','90');
insert into SelCourse values('030202','1006','58');
insert into SelCourse values('030202','1011','73');
insert into SelCourse values('040303','1001','80');
insert into SelCourse values('040303','1000','63');
insert into SelCourse values('050403','1007','78');
insert into SelCourse values('050403','1002','65');
insert into SelCourse values('050403','1012','70');
insert into SelCourse values('020502','1008','42');
insert into SelCourse values('020502','1001','83');
insert into SelCourse values('050601','1009','81');
insert into SelCourse values('050601','1011','76');
insert into SelCourse values('040701','1000','86');
insert into SelCourse values('040701','1004','69');
insert into SelCourse values('030802','1005','88');
insert into SelCourse values('030802','1006','79');
insert into SelCourse values('030802','1010','70');
insert into SelCourse values('040901','1004','59');
insert into SelCourse values('040901','1002','74');
insert into SelCourse values('040901','1012','95');
insert into SelCourse values('041001','1002','85');
insert into SelCourse values('041001','1009','78');
insert into SelCourse values('041001','1010','75');

--给出学生名，查询学生所选的所有课程名、成绩，累计学分、若成绩不及格，要提示
create procedure Student_info   --定义名为Student_info的存储过程
       @sna varchar(20)         --参数
as
declare MK cursor fast_forward  --定义能选出学生姓名及其各科成绩的游标
   for
   select CName,Mark from Course,SelCourse
   where SelCourse.CNum = Course.CNum
     and SelCourse.SNum = (select SNum from Student where SName = @sna)
open MK                         --打开游标
declare @result float           
declare @cna varchar(100)
declare @s varchar(200)

select SName 学生名,CName 课程名,Mark 成绩  --查询学生姓名、所选课程名、成绩
   from Student,Course,SelCourse
   where Student.SNum = (select SNum from Student where SName = @sna)
     and Course.CNum = SelCourse.CNum
     and SelCourse.SNum = Student.SNum

select sum(Credit) 累计学分                 --计算该生的累计学分
   from Course,SelCourse
   where Course.CNum = SelCourse.CNum
      and SNum = (select SNum from Student where SName = @sna)
   group by SNum

fetch MK into @cna,@result        --取出游标中的一行放入变量@cna,@result
while (@@fetch_status = 0 )       --当成功取出了一行
   begin
      if(@result < 60)            --判断成绩是否小于60
         begin
            set @s = @sna + '的' + @cna + '不及格'
            print @s
         end
   fetch MK into @cna,@result     --取游标中的下一条信息
   end 
close MK                          --关闭游标 
deallocate MK                     --删除游标
 
--执行存储过程
execute Student_info 'Jeff'

--给出课程名，查询学生的平均成绩
create procedure AvgMark
   @cna varchar(100)
as
   select distinct CName 课程名,avg(Mark) 平均成绩
   from Course,SelCourse
   group by CName,Course.CNum,SelCourse.CNum
   having CName = @cna
      and Course.CNum = SelCourse.CNum

--执行存储过程AvgMark
execute AvgMark '英语'

--各门课按平均成绩排序
select distinct CName 课程名,avg(Mark) 平均成绩
   from Course,SelCourse
   group by CName,Course.CNum,SelCourse.CNum
   having Course.CNum = SelCourse.CNum
   order by avg(Mark) desc

--显示所有的课程名和讲课教师名
select distinct CName 课程名,TName 教师名
   from Course,Teacher
   where Course.TNum = Teacher.TNum

--给出讲课教师名，查询所讲课程的课程名、学生成绩
create procedure Teacher_info
   @tna varchar(20)
as
   select TName 教师名,CName 课程名,Mark 学生成绩
   from Teacher,Course,SelCourse
   where TName = @tna
     and Course.TNum = Teacher.TNum
     and SelCourse.CNum = Course.CNum

--执行存储过程Teacher_info
execute Teacher_info 'Kelly'