nJFWVPfQ

1DROP TABLE IF EXISTS grades;
2DROP TABLE IF EXISTS exams;
3DROP TABLE IF EXISTS students;
4DROP TABLE IF EXISTS educations;
5
6DROP PROC IF EXISTS getPassedExams;
7DROP TRIGGER IF EXISTS onlyOneGradePrExam;
8
9GO
10CREATE TABLE educations (
11    id INT IDENTITY(1, 1) PRIMARY KEY,
12    name VARCHAR(64) NOT NULL
13);
14
15CREATE TABLE students (
16    id INT IDENTITY(1, 1) PRIMARY KEY,
17    education_id INT FOREIGN KEY REFERENCES educations,
18    name VARCHAR(64) NOT NULL
19);
20
21CREATE TABLE exams (
22    id INT IDENTITY(1, 1) PRIMARY KEY,
23    education_id INT FOREIGN KEY REFERENCES educations,
24    name VARCHAR(64) NOT NULL,
25    date DATETIME NOT NULL
26);
27
28CREATE TABLE grades (
29    id INT IDENTITY(1, 1) PRIMARY KEY,
30    education_id INT FOREIGN KEY REFERENCES educations,
31    exam_id INT FOREIGN KEY REFERENCES exams,
32    student_id INT FOREIGN KEY REFERENCES students,
33    grade INT NOT NULL,
34    CONSTRAINT chk_Grade CHECK (grade IN (-3, 0, 2, 4, 7, 10, 12))
35);
36
37GO
38INSERT INTO educations (name) VALUES('Datamatiker');
39
40INSERT INTO students (education_id, name) VALUES(1, 'Jacob');
41INSERT INTO students (education_id, name) VALUES(1, 'Christian');
42INSERT INTO students (education_id, name) VALUES(1, 'Tobias');
43
44INSERT INTO exams (education_id, name, date) VALUES(1, '1. Semester prøve', GETDATE());
45INSERT INTO exams (education_id, name, date) VALUES(1, '2. Semester eksamen', GETDATE());
46
47INSERT INTO grades (education_id, exam_id, student_id, grade) VALUES(1, 1, 1, 12);
48INSERT INTO grades (education_id, exam_id, student_id, grade) VALUES(1, 1, 2, 12);
49INSERT INTO grades (education_id, exam_id, student_id, grade) VALUES(1, 1, 3, -3);
50
51INSERT INTO grades (education_id, exam_id, student_id, grade) VALUES(1, 2, 1, 12);
52INSERT INTO grades (education_id, exam_id, student_id, grade) VALUES(1, 2, 2, 12);
53INSERT INTO grades (education_id, exam_id, student_id, grade) VALUES(1, 2, 3, 4);
54
55-- Opgave 4.a
56GO
57SELECT DISTINCT students.name FROM grades
58    JOIN students ON students.id = grades.student_id
59    WHERE grades.grade = 12;
60
61-- Opgave 4.b
62GO
63SELECT DISTINCT students.name FROM students
64    WHERE id IN (SELECT grades.student_id FROM grades WHERE grades.grade >= 2)
65    AND id NOT IN (SELECT grades.student_id FROM grades WHERE grades.grade < 2);
66
67-- Opgave 4.c
68GO
69SELECT exams.name, AVG(grades.grade) AS avgGrade 
70    FROM grades 
71    JOIN exams ON exams.id = grades.exam_id
72    WHERE grades.grade >= 2
73    GROUP BY exams.name;
74
75-- Opgave 5
76GO
77DROP PROC IF EXISTS getPassedExams;
78
79GO
80CREATE PROC getPassedExams
81@student_id INT
82AS
83SELECT exams.name AS exam, grades.grade FROM exams
84    JOIN grades ON exams.id = grades.exam_id
85    WHERE grades.student_id = @student_id
86    AND grades.grade >= 2
87
88GO
89EXECUTE getPassedExams 1;
90
91-- Opgave 6
92GO
93DROP TRIGGER IF EXISTS onlyOneGradePrExam;
94
95GO
96CREATE TRIGGER onlyOneGradePrExam
97ON grades
98AFTER INSERT
99AS
100IF EXISTS (SELECT grades.* FROM grades
101            JOIN inserted ON inserted.exam_id = grades.exam_id
102            WHERE grades.grade >= 2 AND grades.student_id = inserted.student_id)
103BEGIN
104    ROLLBACK TRAN
105    RAISERROR('Eksamen er allerede bestået', 16, 1)
106END;
107
108GO
109INSERT INTO grades (education_id, exam_id, student_id, grade) VALUES(1, 1, 3, 12);
110
111GO
112SELECT grades.* FROM grades
113            WHERE grades.grade >= 2 AND grades.student_id = 3 AND exam_id = 1