實訓資料庫開發應用——視圖預存程序

 

--1.建立查詢學生的學號,姓名,所在系,課程號,課程名,課程學分的視圖

create view v1

 as

  select s.sno,sname,sdept,c.cno,cname,credit 

    from student s join sc on s.sno= sc.sno

    join course c on c.cno = sc.cno

--2.建立查詢每個學生的平均成績的視圖,要求列出學生學號及平均成績

create view v2

as

  select sno,avg(grade) avg_g

    from sc

   group by sno

--3.建立查詢每個學生的總學分的視圖,要求列出學號和總學分

create view v3

as

  select sno,sum(credit) sum_c

    from sc join course c on c.cno = sc.cno

   where grade >= 60 and grade is not null

   group by sno

--4.建立查詢年齡大於等於20的男生的視圖,列出學號,姓名,所在系和年齡,

--並要求限制通過此視圖將年齡大於等於20的男生修改為年齡小於20

create view v4

as

  select sno,sname,sdept,sage from student

   where sage>=20 and ssex = '男'

   with check option

--(1)建立查詢每個學生的修課總學分的預存程序,要求列出學號及總學分

create proc p1

 as

  select sno,sum(credit)

    from sc join course c on c.cno = sc.cno

    where grade is not null

      and grade >=60

    group by sno

--(2)建立查詢指定系學生學號,姓名,課程號,課程名,課程學分的預存程序,將學生所在系作為輸入參數.

create proc p2

  @d varchar(20)

as

  select s.sno,sname,c.cno,cname,credit

    from course c join sc on c.cno = sc.cno

    join student s on sc.sno = s.sno

   where sdept = @d

--(3)計算1+2+3+...的預存程序

create proc p3

  @x int,@sum int output

as

  declare @i int

  set @i=1

  set @sum = 0

  while @i<=@x 

   begin

     set @sum = @sum + @i

     set @i = @i +1

   end

--(4)建立刪除指定學生的修課記錄的預存程序,學號為輸入參數

create proc p4

  @sno char(8)

as

  delete from sc where sno = @sno

--(5)建立修改指定課程的學分的預存程序.

create proc p5

  @cno char(8), @cr int=3

as

  update course set credit = @cr

   where cno= @cno