· 8 years ago · Jun 11, 2018, 04:22 PM
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','Powtarzalny','2');
79
80INSERT INTO person (person_name, person_surname, school_id)
81 VALUES ('Adam','Powtarzalny','2');
82
83INSERT INTO person (person_name, person_surname, school_id)
84 VALUES ('Tomasz','Pierwszy','2');
85
86INSERT INTO person (person_name, person_surname, school_id)
87 VALUES ('Adriam','Drugi','3');
88
89INSERT INTO person (person_name, person_surname, school_id)
90 VALUES ('Darek','Unikalny1','2');
91
92INSERT INTO person (person_name, person_surname, school_id)
93 VALUES ('Mirek','Unikalny2','2');
94
95
96INSERT INTO teacher (person_id, teacher_field_of_study)
97 VALUES('1','Matematyka');
98
99
100
101INSERT INTO student(person_id, student_grade)
102VALUES ('1','5');
103
104INSERT INTO student(person_id, student_grade)
105VALUES ('2','3');
106
107INSERT INTO student(person_id, student_grade)
108VALUES ('3','4');
109
110INSERT INTO student(person_id, student_grade)
111VALUES ('4','5');
112
113INSERT INTO student(person_id, student_grade)
114VALUES ('5','5');
115
116INSERT INTO student(person_id, student_grade)
117VALUES ('6','2');
118
119
120# SELECTY
121
122#WYBIERANIE WSZYSTKICH OSOB O IMIENIU TOMASZ
123SELECT person_name FROM person
124WHERE (person_name = 'Tomasz');
125
126#WSZYSTKIE OSOBY ZAWIERAJACE A NA POCZATKU
127SELECT person_name FROM person
128WHERE person_name LIKE 'A%';
129
130#WSZYSTKIE OSOBY Z ADRIA I ROZNIACA SIE TYLKO OSTATNIA LITERA
131SELECT person_name FROM person
132WHERE person_name LIKE 'Adria_';
133
134#WSZYTKIE OSOBY Z LITERA A W IMIENIU
135SELECT person_name FROM person
136WHERE person_name LIKE '%a%';
137
138
139# JOINY
140
141#INNER JOIN TYLKO WSPOLNA CZESC
142SELECT p.person_name,p.person_surname,s.school_pathron
143FROM person p
144INNER JOIN school s on p.school_id = s.school_id
145WHERE p.school_id = 1;
146
147#RIGHT JOIN TYLKO WSPOLNA CZESC
148SELECT p.person_name,p.person_surname,s.school_pathron
149FROM person p
150INNER JOIN school s on p.school_id = s.school_id
151WHERE p.school_id = 1;
152
153# FULL JOIN BEZ DUPLIKATOW ( MYSQL NIE OBSUGUJE FULL JOIN)
154SELECT p.person_name,p.person_surname,s.school_pathron
155FROM person p
156LEFT JOIN school s on p.school_id = s.school_id
157UNION
158SELECT p.person_name,p.person_surname,s.school_pathron
159FROM person p
160RIGHT JOIN school s on p.school_id = s.school_id
161;
162
163# FULL JOIN Z DUPLIKATAMI (MYSQL NIE OBSLUGUJE FULL JOIN)
164SELECT p.person_name,p.person_surname,s.school_pathron
165FROM person p
166LEFT JOIN school s on p.school_id = s.school_id
167UNION ALL
168SELECT p.person_name,p.person_surname,s.school_pathron
169FROM person p
170RIGHT JOIN school s on p.school_id = s.school_id
171;
172
173#INTERSECT (NIE ISTNIEJE W MYSQL ZASTOSUJEMY KLAZURE IN)
174SELECT p.person_name,p.person_surname,p.school_id
175FROM person p
176WHERE p.person_name = 'Tomasz'
177AND p.school_id
178IN (SELECT s.school_id
179 FROM school s
180 WHERE school_pathron ='Patron1');
181
182#EXCEPT (NIE ISTNIEJE W MYSQL ZASTOSUJEMY KLAZURE NOT IN)
183SELECT p.person_name,p.person_surname,p.school_id
184FROM person p
185WHERE p.person_name = 'Tomasz'
186AND p.school_id
187NOT IN (SELECT s.school_id
188 FROM school s
189 WHERE school_pathron ='Patron1');
190
191
192
193#EXISTS WYBIERA WSZYSKIE DANE Z PERSON ID JEZELI ISTNIEJE SZKOLA O PODANYM ID
194 SELECT *
195FROM person p
196WHERE EXISTS(
197 SELECT *
198 FROM school s
199 WHERE s.school_id = 3
200);
201
202#EXISTS WYBIERA WSZYSKIE DANE Z PERSON ID POZA TYMI Z PODNYM ID SZKOLY (NIE WIADOMO CZY POPRAWNIE DZIALA)
203 SELECT *
204FROM person p
205WHERE NOT EXISTS (
206 SELECT *
207 FROM school s
208 WHERE p.school_id = 2
209);
210
211# 1 COUNT Zlicza ilość rekordów, spełniających podane warunki
212# 2 SUM Wylicza sumę wartości liczbowych
213# 3 MIN Wartość minimalna w grupie
214# 4 MAX Wartość maksymalna w grupie
215# 5 AVG Oblicza średnią w grupie
216# 6 STDDEV Oblicza odchylenie standardowe w grupie
217
218
219#COUNT
220SELECT COUNT(person_id) AS students_with_grade_5 FROM student
221WHERE student_grade = '5';
222
223#SUM (SUMUJE NUMERY KOMOREK ENUMA A NIE ICH WARTOSCI)
224SELECT student_grade, SUM(student_grade) AS sum_of_students_grades FROM student
225GROUP BY student_grade
226HAVING student_grade>1;