qy0htmbe

1DROP DATABASE IF EXISTS University;
2CREATE DATABASE University;
3USE University;
4
5CREATE TABLE Subject (
6    ID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
7    NAME VARCHAR(255) NOT NULL
8);
9
10CREATE TABLE Student (
11    ID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
12    First_name VARCHAR(255) NOT NULL,
13    Last_name VARCHAR(255) NOT NULL,
14    Subject_ID INTEGER NOT NULL, FOREIGN KEY(Subject_ID) REFERENCES Subject(ID)
15);
16
17CREATE TABLE Course (
18    ID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
19    Name VARCHAR(255) NOT NULL,
20    Description VARCHAR(255) NOT NULL, 
21    Credits INTEGER NOT NULL
22);
23
24CREATE TABLE Course_Student(
25    ID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
26    Student_Study INTEGER NOT NULL, FOREIGN KEY(Student_Study) REFERENCES Student(ID),
27    Course_ID INTEGER NOT NULL, FOREIGN KEY(Course_ID) REFERENCES Course(ID)
28);
29
30INSERT INTO Subject VALUES (NULL, 'Mathematics and Informatics'),
31                           (NULL, 'Physics'),
32                           (NULL, 'test_for_deletion');
33
34INSERT INTO Student VALUES (NULL, 'Alex', 'Tsvetanov', 1),
35                           (NULL, '100yo', '4CEO', 1),
36                           (NULL, 'Petar', 'Ivanov', 2),
37                           (NULL, 'Test_for_', 'deletion', 2);
38
39DELETE FROM Subject WHERE Subject.NAME = 'test_for_deletion';
40SELECT * FROM Subject;
41
42DELETE FROM Student WHERE Student.First_name = 'Test_for_';
43SELECT * FROM Student;
44SELECT * FROM Subject INNER JOIN Student ON Student.Subject_ID = Subject.ID WHERE Subject.NAME = 'Mathematics and Informatics';
45
46INSERT INTO Course VALUES (NULL, 'Data Structures and Algorithms', 'NP-full, Graphs, Dynamic programming, Genetic algortihms and others.', 12);
47INSERT INTO Course VALUES (NULL, 'Theoretical Physics', 'No idea what is this :D', 12);
48INSERT INTO Course VALUES (NULL, 'Test_for_', 'deletion', 12);
49DELETE FROM Course WHERE Course.Name = 'Test_for_';
50SELECT * FROM Course;
51
52INSERT INTO Course_Student VALUES (NULL, 1, 1);
53INSERT INTO Course_Student VALUES (NULL, 1, 2);
54INSERT INTO Course_Student VALUES (NULL, 2, 1);
55
56SELECT * FROM Course_Student INNER JOIN Course ON Course.ID = Course_Student.Course_ID INNER JOIN Student ON Student.ID = Course_Student.Student_Study WHERE Student.FIRST_NAME = 'Petar';
57
58DELETE Course_Student FROM Course_Student INNER JOIN Student ON Course_Student.Student_Study = Student.ID WHERE Student.Subject_ID = 1;
59DELETE FROM Student WHERE Student.Subject_ID = 1;
60DELETE FROM Subject WHERE Subject.ID = 1;
61
62SELECT * FROM Student;
63SELECT * FROM Subject;