EducationSystem

CREATE Database MiniProject

DROP TABLE Education, Class, Student, StudentHasClass;

--Assignment 1

CREATE TABLE Education(
Name VARCHAR(20),
U_ID INT NOT NULL,
StartDate DATE,
SlutDate DATE,
PRIMARY KEY(U_ID),
);

CREATE TABLE Class(
Name VARCHAR(20),
Class_ID INT NOT NULL,
U_ID INT NOT NULL,
PRIMARY KEY(Class_ID),
FOREIGN KEY(U_ID) REFERENCES Education(U_ID)
);

CREATE TABLE Student(
Name VARCHAR(30),
Student_ID INT NOT NULL,
Adress VARCHAR (40),
PRIMARY KEY(Student_ID),
);

CREATE TABLE StudentHasClass(
Student_ID INT NOT NULL,
Class_ID INT NOT NULL,
Attempts INT NOT NULL,
Passed INT NOT NULL,
Grade INT NOT NULL,
FOREIGN KEY (Student_ID) REFERENCES Student(Student_ID),
FOREIGN KEY (Class_ID) REFERENCES Class(Class_ID)
);

--Assignment 2

INSERT INTO Student(Name, Student_ID, Adress)
VALUES('Albert Brown', 111990, 'Main Road 1b');

INSERT INTO Student(Name, Student_ID, Adress)
VALUES('Charles Davidson', 221991, 'Beach Road 70');

INSERT INTO Student(Name, Student_ID, Adress)
VALUES('Emma Finley', 331992, 'Side Road 42');

INSERT INTO Student(Name, Student_ID, Adress)
VALUES('George Hanson', 441993, 'Country Road 91');

INSERT INTO Student(Name, Student_ID, Adress)
VALUES('Irene Jansen', 551994, 'City Road 3d');

INSERT INTO Education(Name, U_ID, StartDate, SlutDate)
VALUES('Datamatician', 98765, '2018-08-27', '2020-05-28');

INSERT INTO Class(Name, Class_ID, U_ID)
VALUES('Programming', 1234, 98765);

INSERT INTO Class(Name, Class_ID, U_ID)
VALUES('System Development', 3456, 98765);

INSERT INTO Class(Name, Class_ID, U_ID)
VALUES('Databases', 5678, 98765);

INSERT INTO StudentHasClass(Student_ID, Class_ID, Attempts, Grade, Passed)
VALUES(111990, 1234, 1, 4, 1);

INSERT INTO StudentHasClass(Student_ID, Class_ID, Attempts, Grade, Passed)
VALUES(111990, 3456, 1, 7, 1);

INSERT INTO StudentHasClass(Student_ID, Class_ID, Attempts, Grade, Passed)
VALUES(111990, 5678, 2, 10, 1);

INSERT INTO StudentHasClass(Student_ID, Class_ID, Attempts, Grade, Passed)
VALUES(221991, 1234, 2, 7, 1);

INSERT INTO StudentHasClass(Student_ID, Class_ID, Attempts, Grade, Passed)
VALUES(221991, 3456, 2, 7, 1);

INSERT INTO StudentHasClass(Student_ID, Class_ID, Attempts, Grade, Passed)
VALUES(221991, 5678, 1, 10, 1);

INSERT INTO StudentHasClass(Student_ID, Class_ID, Attempts, Grade, Passed)
VALUES(331992, 1234, 1, 2, 1);

INSERT INTO StudentHasClass(Student_ID, Class_ID, Attempts, Grade, Passed)
VALUES(331992, 3456, 1, 7, 1);

INSERT INTO StudentHasClass(Student_ID, Class_ID, Attempts, Grade, Passed)
VALUES(331992, 5678, 1, 4, 1);

INSERT INTO StudentHasClass(Student_ID, Class_ID, Attempts, Grade, Passed)
VALUES(441993, 1234, 3, 4, 1);

INSERT INTO StudentHasClass(Student_ID, Class_ID, Attempts, Grade, Passed)
VALUES(441993, 3456, 2, 7, 1);

INSERT INTO StudentHasClass(Student_ID, Class_ID, Attempts, Grade, Passed)
VALUES(441993, 5678, 4, 12, 1);

INSERT INTO StudentHasClass(Student_ID, Class_ID, Attempts, Grade, Passed)
VALUES(551994, 1234, 1, 10, 1);

INSERT INTO StudentHasClass(Student_ID, Class_ID, Attempts, Grade, Passed)
VALUES(551994, 3456, 1, 12, 1);

INSERT INTO StudentHasClass(Student_ID, Class_ID, Attempts, Grade, Passed)
VALUES(551994, 5678, 1, 10, 1);

--Assignment 3.1

SELECT Name, Student.Student_ID, StudentHasClass.Student_ID, Class_ID, Attempts, Grade
FROM StudentHasClass JOIN Student ON Student.Student_ID = StudentHasClass.Student_ID
WHERE Grade = 12

--Assignment 3.2

SELECT Student.Name
FROM Student, Education, StudentHasClass
WHERE Student.Student_ID = StudentHasClass.Student_ID
AND Attempts = 1
EXCEPT
SELECT Student.Name
FROM Student, StudentHasClass
WHERE Student.Student_ID = StudentHasClass.Student_ID
AND
Attempts > 1

--Assignment 3.3

SELECT AVG(Grade) AS 'Programming'
FROM StudentHasClass
WHERE Class_ID = 1234

SELECT AVG(Grade) AS 'System Development'
FROM StudentHasClass
WHERE Class_ID = 3456

SELECT AVG(Grade) AS 'Databases'
FROM StudentHasClass
WHERE Class_ID = 5678

--Assignment 4

DROP PROCEDURE PrintExams;

GO
CREATE PROCEDURE PrintExams
@Name VARCHAR(30)
AS
BEGIN
SELECT *
FROM StudentHasClass JOIN Student ON Student.Student_ID = StudentHasClass.Student_ID
WHERE StudentHasClass.Grade > 0
AND Student.Name = @Name
END

EXEC PrintExams 'Emma Finley'

--Assignment 5: Create a trigger that stops the insertion of a grade for a student in a class the student has already passed