· 9 years ago · Nov 21, 2016, 08:48 PM
1-- Elizabeth Rouze
2--Database
3-- homework 7
4-- 11/21/16
5
6GO --begins a new batch
7alter function fgetGPA
8(
9@studentM# char(8),
10@gradeOfEachCourse DECIMAL(4,1),
11@creditHoursOfCourse int
12)
13returns DECIMAL(2,1)
14BEGIN
15 set @studentM# = '123456789'; --???????????????????????????????################??????????????????????????????????
16
17DECLARE @gradeTable TABLE(
18 Grade char(1)
19);
20INSERT @gradeTable
21select MAX(Grade) from Enroll where M# = '12345678' group by Sid_;
22
23DECLARE @temporaryTable TABLE(
24 Grade decimal(4, 1)
25);
26INSERT @temporaryTable
27select Grade = CASE grade when 'A' then '4.0' when 'B' then '3.0' when 'C' then '2.0' when 'D' then '1.0' when 'F' then '0.0' END from @gradeTable;
28
29
30 set @gradeOfEachCourse = (select SUM(Grade) from @temporaryTable)
31 set @creditHoursOfCourse = (select SUM(credit) from Course where Course# IN (select course#
32 from Section
33 where Sid_ IN (select Sid_
34 from Enroll
35 where M# = '12345678')))
36
37 declare @GPA decimal(2,1);
38
39-- vv SET or SELECT??
40 select @GPA = @gradeOfEachCourse * @creditHoursOfCourse / ( (select COUNT(Grade) from @gradeTable) * @creditHoursOfCourse)
41
42 return @GPA;
43END
44
45GO
46--set @studentM# = (select M# from student where M#='&studentM#&');
47--(select credit from Course where Name = (select Name from Student where M# = @studentM#)))
48
49--PRINT 'The students GPA is ' + CAST(fgetGPA AS VARCHAR(3));
50
51
52
53GO --begins a new batch
54alter trigger updateTrig
55on ENROLL
56for UPDATE
57AS
58
59if EXISTS(
60 select dbo.fgetGPA('12345678', NULL,NULL)
61)
62
63
64
65 select GPA from Student;
66 update Student set GPA = 0.0;
67 select GPA from Student;
68
69
70GO -- begins a new batch
71alter PROCEDURE spTranscript -- IF ERROR 'There is already an obj named 'spTranscript' in the db... change CREATE to ALTER. you can now execute it.
72(
73 @studentM# char(8) = NULL --NULL ?
74 -- @tempTable TABLE
75 )
76
77AS
78
79BEGIN
80
81DECLARE @tempTable TABLE(
82 Course# varchar(5),
83 Name varchar(20),
84 Credit int,
85 Grade char(1),
86 Year_ char(4),
87 Semester varchar(10),
88 Course#2 varchar(5)
89);
90INSERT @tempTable
91select c.Course#, c.Name, c.Credit, e.Grade, s.Year_, s.Semester, s.Course#
92from Course c JOIN Section s ON c.Course# = s.Course#
93JOIN Enroll e ON s.Sid_ = e.Sid_
94where @studentM# IS NOT NULL AND e.M# = '12345678' --!!!!!!!!!!!!!!!! Match the M#'s together? (THE PROCEDURE SHOULD TAKE M# AS THE ONLY PARAMETER)
95order by s.Year_, s.Semester, s.Course#;
96
97select Course#, Name, Credit, Grade
98from @tempTable;
99
100END
101
102EXEC spTranscript @studentM#='12345678'