S041Byq7

1/*TASK 3*/
2CREATE DATABASE IF NOT EXISTS mtm;
3USE mtm;
4CREATE TABLE IF  NOT EXISTS students
5(
6	student_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
7    `name` VARCHAR(50) NOT NULL
8);
9CREATE TABLE IF  NOT EXISTS exams
10(
11	exam_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
12    `name` VARCHAR(50) NOT NULL
13);
14ALTER TABLE exam AUTO_INCREMENT = 101;
15CREATE TABLE IF  NOT EXISTS students_exams
16(
17	student_id INT NOT NULL,
18    exam_id INT NOT NULL,
19    PRIMARY KEY (student_id,exam_id),
20    FOREIGN KEY (student_id) REFERENCES students(student_id),
21    FOREIGN KEY (exam_id) REFERENCES exams(exam_id)
22);
23INSERT INTO students (`name`) VALUES ("Mila");
24INSERT INTO students (`name`) VALUES ("Tony");
25INSERT INTO students (`name`) VALUES ("Ron");
26   
27INSERT INTO exams (/*exam_id,*/`name`) VALUES (/*101,*/"Spring MVC");
28INSERT INTO exams (/*exam_id,*/`name`) VALUES (/*102,*/"Neo4j");
29INSERT INTO exams (/*exam_id,*/`name`) VALUES (/*103,*/"Oracle 11g");
30
31INSERT INTO students_exams(student_id,exam_id) VALUES (1,101);
32INSERT INTO students_exams(student_id,exam_id) VALUES (1,102);
33INSERT INTO students_exams(student_id,exam_id) VALUES (2,101);
34INSERT INTO students_exams(student_id,exam_id) VALUES (3,103);
35INSERT INTO students_exams(student_id,exam_id) VALUES (2,102);
36INSERT INTO students_exams(student_id,exam_id) VALUES (2,103);
37
38/*TASK 4*/
39/*DROP DATABASE con;*/
40CREATE DATABASE IF NOT EXISTS con;
41USE con;
42CREATE TABLE IF NOT EXISTS teacher
43(
44	teacher_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
45    `name` VARCHAR(50) NOT NULL,
46    manager_id INT NULL,
47    FOREIGN KEY (manager_id) REFERENCES teacher(teacher_id) 
48);
49
50ALTER TABLE teacher AUTO_INCREMENT = 101;
51
52INSERT INTO teacher (`name`) VALUES ("John");
53INSERT INTO teacher (`name`) VALUES ("Maya");
54INSERT INTO teacher (`name`) VALUES ("Silvia");
55INSERT INTO teacher (`name`) VALUES ("Ted");
56INSERT INTO teacher (`name`) VALUES ("Mark");
57INSERT INTO teacher (`name`) VALUES ("Greata");
58
59UPDATE teacher SET manager_id = 106 WHERE teacher_id=102;
60UPDATE teacher SET manager_id = 106 WHERE teacher_id=103;
61UPDATE teacher SET manager_id = 105 WHERE teacher_id=104;
62UPDATE teacher SET manager_id = 101 WHERE teacher_id=105;
63UPDATE teacher SET manager_id = 101 WHERE teacher_id=106;
64
65SELECT * FROM teacher;