4RduuxfK

1DROP TABLE IF EXISTS teacher;
2DROP TABLE IF EXISTS student;
3DROP TABLE IF EXISTS person;
4DROP TABLE IF EXISTS supervisor_school;
5DROP TABLE IF EXISTS school;
6DROP TABLE IF EXISTS supervisor;
7
8CREATE TABLE supervisor(
9  supervisor_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
10  supervisor_name VARCHAR(6) NOT NULL
11);
12
13CREATE TABLE school(
14  school_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
15  school_pathron VARCHAR(20) NOT NULL UNIQUE
16);
17
18CREATE TABLE supervisor_school(
19
20  supervisor_id INT(6) UNSIGNED,
21  school_id INT(6) UNSIGNED,
22  school_grade INT(6) NOT NULL,
23  PRIMARY KEY (school_id,supervisor_id,school_grade),
24  FOREIGN KEY (supervisor_id) REFERENCES  supervisor(supervisor_id),
25  FOREIGN KEY (school_id) REFERENCES  school(school_id)
26
27);
28
29
30
31CREATE TABLE person(
32  person_id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY ,
33  person_name VARCHAR(20) NOT NULL ,
34  person_surname VARCHAR(20) NOT NULL,
35  school_id INT(6) UNSIGNED,
36  FOREIGN KEY (school_id)
37    REFERENCES school(school_id)
38);
39
40#CHECK NIE JEST OBSLUGIWANY PRZEZ MYSQL ENABLE DLA CONSTRAINS ROWNIEZ NIE DZIALA
41CREATE TABLE student(
42  person_id INT(6) UNSIGNED PRIMARY KEY,
43  student_grade ENUM('2','3','4','5'),
44    FOREIGN KEY (person_id)
45    REFERENCES person(person_id)
46
47);
48
49CREATE TABLE teacher(
50  person_id INT(6) UNSIGNED PRIMARY KEY ,
51  teacher_field_of_study VARCHAR(20),
52  FOREIGN KEY (person_id)
53    REFERENCES person(person_id)
54);
55
56INSERT INTO school (school_pathron)
57    VALUE ('Patron1');
58
59INSERT INTO school (school_pathron)
60    VALUE ('Patron2');
61
62INSERT INTO school (school_pathron)
63    VALUE ('Patron3');
64
65INSERT INTO school (school_pathron)
66    VALUE ('Patron4');
67
68INSERT INTO person (person_name, person_surname, school_id)
69    VALUES ('Tomasz','Pierwszy','1');
70
71INSERT INTO person (person_name, person_surname, school_id)
72    VALUES ('Damian','Pierwszy','1');
73
74INSERT INTO person (person_name, person_surname, school_id)
75    VALUES ('Tomasz','Drugi','1');
76
77INSERT INTO person (person_name, person_surname, school_id)
78    VALUES ('Adam','Pierwszy','2');
79
80INSERT INTO person (person_name, person_surname, school_id)
81    VALUES ('Adrian','Pierwszy','2');
82
83INSERT INTO person (person_name, person_surname, school_id)
84    VALUES ('Adriam','Drugi','3');
85
86INSERT INTO person (person_name, person_surname, school_id)
87    VALUES ('Darek','Unikalny1','2');
88
89INSERT INTO person (person_name, person_surname, school_id)
90    VALUES ('Mirek','Unikalny2','2');
91
92
93INSERT INTO teacher (person_id, teacher_field_of_study)
94    VALUES('1','Matematyka');
95
96
97
98INSERT INTO student(person_id, student_grade)
99VALUES ('1','5');
100
101#WYBIERANIE WSZYSTKICH OSOB O IMIENIU TOMASZ
102SELECT person_name FROM person
103WHERE (person_name = 'Tomasz');
104
105#WSZYSTKIE OSOBY ZAWIERAJACE A NA POCZATKU
106SELECT person_name FROM person
107WHERE person_name LIKE 'A%';
108
109#WSZYSTKIE OSOBY Z ADRIA I ROZNIACA SIE TYLKO OSTATNIA LITERA
110SELECT person_name FROM person
111WHERE person_name LIKE 'Adria_';
112
113#WSZYTKIE OSOBY Z LITERA A W IMIENIU
114SELECT person_name FROM person
115WHERE person_name LIKE '%a%';
116
117#INNER JOIN TYLKO WSPOLNA CZESC
118SELECT p.person_name,p.person_surname,s.school_pathron
119FROM person p
120INNER JOIN  school s on p.school_id = s.school_id
121WHERE p.school_id = 1;
122
123#RIGHT JOIN TYLKO WSPOLNA CZESC
124SELECT p.person_name,p.person_surname,s.school_pathron
125FROM person p
126INNER JOIN  school s on p.school_id = s.school_id
127WHERE p.school_id = 1;
128
129# FULL JOIN BEZ DUPLIKATOW ( MYSQL NIE OBSUGUJE FULL JOIN)
130SELECT  p.person_name,p.person_surname,s.school_pathron
131FROM person p
132LEFT JOIN school s on p.school_id = s.school_id
133UNION
134SELECT  p.person_name,p.person_surname,s.school_pathron
135FROM person p
136RIGHT JOIN school s on p.school_id = s.school_id
137;
138
139# FULL JOIN Z DUPLIKATAMI (MYSQL NIE OBSLUGUJE FULL JOIN)
140SELECT  p.person_name,p.person_surname,s.school_pathron
141FROM person p
142LEFT JOIN school s on p.school_id = s.school_id
143UNION ALL
144SELECT  p.person_name,p.person_surname,s.school_pathron
145FROM person p
146RIGHT JOIN school s on p.school_id = s.school_id
147;