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'