K9n8uWwu

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