gRRyf1a2

1DROP DATABASE IF EXISTS Test;
2CREATE DATABASE Test;
3
4USE Test;
5
6
7DROP TABLE IF EXISTS Student;
8CREATE TABLE Student(
9	Id INT UNIQUE AUTO_INCREMENT NOT NULL,
10    Name VARCHAR(20) NOT NULL,
11    Number TINYINT NOT NULL,
12    Class VARCHAR(3) NOT NULL,    
13    
14    PRIMARY KEY(Id)
15);
16
17
18DROP TABLE IF EXISTS Assignment;
19CREATE TABLE Assignment(
20	Id INT UNIQUE AUTO_INCREMENT NOT NULL,
21    Task VARCHAR(256),
22    
23    PRIMARY KEY(Id)
24);
25
26
27DROP TABLE IF EXISTS StudentAssignments;
28CREATE TABLE StudentAssignments(
29	Id INT AUTO_INCREMENT NOT NULL,
30    AssignmentId INT NOT NULL,
31	StudentId INT NOT NULL,
32    
33    PRIMARY KEY(Id),
34    FOREIGN KEY (AssignmentId) REFERENCES Assignment(Id)
35    ON DELETE CASCADE
36    ON UPDATE NO ACTION, -- reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT
37    FOREIGN KEY (StudentId) REFERENCES Student(Id)
38);
39
40
41START TRANSACTION;
42INSERT INTO Student SET Name="Ivan", Number=11, Class='12a';
43
44
45INSERT INTO Student (Name, Number, Class)
46VALUES 	("Sando", 1,'12a'),
47		("Naigi", 22,'12a');
48
49
50INSERT INTO Assignment (Task)
51VALUES 	("SUBD"), 
52		("Math"),
53        ("English"),
54        ("OOP"),
55        ("WebD"),
56        ("VMKS");
57
58
59INSERT INTO StudentAssignments(AssignmentId, StudentId)
60VALUE  	(1, 1),
61		(2, 1),
62        (3, 2),
63        (4, 2),
64        (5, 3),
65        (6, 3);
66
67
68UPDATE StudentAssignments
69SET AssignmentId = 4
70WHERE StudentId = 1 AND AssignmentId = 1;
71
72COMMIT;
73
74
75DELIMITER $
76
77CREATE PROCEDURE SelectAll ()
78BEGIN
79	SELECT st.Name AS 'Student Name',  st.Number AS 'Student Number', st.Class 'Student Class', assg.Task 'Task'
80    FROM Student as st
81    INNER JOIN StudentAssignments sa
82    ON st.Id = sa.StudentId
83    INNER JOIN Assignment assg
84    ON assg.Id = sa.AssignmentId;
85END $
86
87DELIMITER ;
88
89
90CALL SelectAll;
91
92
93CREATE FUNCTION concatNameClass(n VARCHAR(20), c VARCHAR(3))
94RETURNS VARCHAR(128) DETERMINISTIC
95RETURN CONCAT("Student: ", n, " From Class:" , c);
96
97
98DELIMITER //
99
100CREATE TRIGGER studentUpdateTrigger
101AFTER UPDATE
102ON Student
103FOR EACH ROW 
104BEGIN
105	INSERT INTO Student SET Name=old.Name, Number=old.Number, Class=old.Class;
106END //
107DELIMITER ;
108
109
110UPDATE Student
111SET Name = 'Mitko', Number=8
112WHERE Name='Sando';
113
114-- SELECT concatNameClass(Name, Class) FROM Student;