· last year · Dec 07, 2023, 05:45 PM
1-- 1.
2/*
3----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4------------------------------------------------------------------------------ PRACA NIESAMODZIELNA ------------------------------------------------------------------------------
5----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
6*/
7
8CREATE DATABASE IF NOT EXISTS praca;
9USE praca;
10
11
12CREATE TABLE IF NOT EXISTS Ludzie (
13 id INT NOT NULL AUTO_INCREMENT,
14 PESEL CHAR(11),
15 imie VARCHAR(30),
16 nazwisko VARCHAR(30),
17 data_urodzenia DATE,
18 plec ENUM('K', 'M'),
19 PRIMARY KEY (id)
20);
21
22CREATE TABLE IF NOT EXISTS Zawody (
23 zawod_id INT NOT NULL AUTO_INCREMENT,
24 nazwa VARCHAR(50),
25 pensja_min FLOAT CHECK(pensja_min >= 0),
26 pensja_max FLOAT CHECK(pensja_max >= 0 AND pensja_max > pensja_min),
27 PRIMARY KEY (zawod_id)
28);
29
30CREATE TABLE IF NOT EXISTS Pracownicy (
31 id INT NOT NULL AUTO_INCREMENT,
32 PESEL CHAR(11),
33 zawod_id INT,
34 pensja FLOAT CHECK(pensja >= 0),
35 FOREIGN KEY (PESEL) REFERENCES Ludzie(PESEL),
36 FOREIGN KEY (zawod_id) REFERENCES Zawody(zawod_id),
37 PRIMARY KEY (id, zawod_id)
38);
39
40-- Sprawdzenie poprawności PESELa
41CREATE TRIGGER IF NOT EXISTS on_insert_ludzie BEFORE INSERT, UPDATE ON Ludzie
42FOR EACH ROW
43BEGIN
44 DECLARE gender_checker INT;
45 DECLARE month_adjuster INT;
46 DECLARE control_number INT;
47
48 SET gender_checker = CASE WHEN NEW.plec = 'M' THEN 1 ELSE 0 END;
49 SET month_adjuster = CASE WHEN (YEAR(NEW.data_urodzenia) BETWEEN 2000 AND 2099)
50 THEN 20 ELSE 0 END;
51
52 SET control_number =
53 MOD(
54 MOD(1 * CAST(SUBSTRING(NEW.PESEL, 1, 1) AS UNSIGNED), 10) +
55 MOD(3 * CAST(SUBSTRING(NEW.PESEL, 2, 1) AS UNSIGNED), 10) +
56 MOD(7 * CAST(SUBSTRING(NEW.PESEL, 3, 1) AS UNSIGNED), 10) +
57 MOD(9 * CAST(SUBSTRING(NEW.PESEL, 4, 1) AS UNSIGNED), 10) +
58 MOD(1 * CAST(SUBSTRING(NEW.PESEL, 5, 1) AS UNSIGNED), 10) +
59 MOD(3 * CAST(SUBSTRING(NEW.PESEL, 6, 1) AS UNSIGNED), 10) +
60 MOD(7 * CAST(SUBSTRING(NEW.PESEL, 7, 1) AS UNSIGNED), 10) +
61 MOD(9 * CAST(SUBSTRING(NEW.PESEL, 8, 1) AS UNSIGNED), 10) +
62 MOD(1 * CAST(SUBSTRING(NEW.PESEL, 9, 1) AS UNSIGNED), 10) +
63 MOD(3 * CAST(SUBSTRING(NEW.PESEL, 10, 1) AS UNSIGNED), 10)
64 , 10
65 );
66 IF (
67 LENGTH(NEW.PESEL) <> 11 OR
68 SUBSTRING(NEW.PESEL, 1, 2) <> SUBSTRING(YEAR(NEW.data_urodzenia), 3, 2) OR
69 SUBSTRING(NEW.PESEL, 3, 2) <> (MONTH(NEW.data_urodzenia) + month_adjuster) OR
70 SUBSTRING(NEW.PESEL, 5, 2) <> DAYOFMONTH(NEW.data_urodzenia) OR
71 MOD((CAST(SUBSTRING(NEW.PESEL, 10, 1) AS UNSIGNED)), 2) <> gender_checker OR
72 control_number <> SUBSTRING(NEW.PESEL, 11, 1)
73 ) THEN
74 SIGNAL SQLSTATE '45000'
75 SET MESSAGE_TEXT = "Niepoprawny numer PESEL!";
76 END IF;
77END;
78
79-- Generowanie poprawnego PESELa
80CREATE FUNCTION IF NOT EXISTS generuj_pesel(data_urodzenia DATE, plec CHAR)
81RETURNS CHAR(11)
82BEGIN
83 DECLARE pesel CHAR(11);
84 DECLARE gender_digit INT;
85 DECLARE month_adjuster INT;
86
87 SET gender_digit = CASE WHEN plec = 'K' THEN 2 ELSE 1 END;
88 SET month_adjuster = CASE WHEN YEAR(data_urodzenia) >= 2000 THEN 20 ELSE 0 END;
89
90 SET pesel = CONCAT(
91 SUBSTRING(YEAR(data_urodzenia), 3, 2),
92 LPAD((MONTH(data_urodzenia) + month_adjuster), 2, '0'),
93 LPAD(DAYOFMONTH(data_urodzenia), 2, '0'),
94 FLOOR(RAND()*10),
95 FLOOR(RAND()*10),
96 FLOOR(RAND()*10),
97 gender_digit
98 );
99 SET pesel = CONCAT(
100 pesel,
101 MOD(
102 MOD(1 * CAST(SUBSTRING(pesel, 1, 1) AS UNSIGNED), 10) +
103 MOD(3 * CAST(SUBSTRING(pesel, 2, 1) AS UNSIGNED), 10) +
104 MOD(7 * CAST(SUBSTRING(pesel, 3, 1) AS UNSIGNED), 10) +
105 MOD(9 * CAST(SUBSTRING(pesel, 4, 1) AS UNSIGNED), 10) +
106 MOD(1 * CAST(SUBSTRING(pesel, 5, 1) AS UNSIGNED), 10) +
107 MOD(3 * CAST(SUBSTRING(pesel, 6, 1) AS UNSIGNED), 10) +
108 MOD(7 * CAST(SUBSTRING(pesel, 7, 1) AS UNSIGNED), 10) +
109 MOD(9 * CAST(SUBSTRING(pesel, 8, 1) AS UNSIGNED), 10) +
110 MOD(1 * CAST(SUBSTRING(pesel, 9, 1) AS UNSIGNED), 10) +
111 MOD(3 * CAST(SUBSTRING(pesel, 10, 1) AS UNSIGNED), 10),
112 10
113 )
114 );
115
116 RETURN pesel;
117END;
118
119-- Wstawianie informacji o 5 osobach niepełnoletnich
120INSERT INTO Ludzie (PESEL, imie, nazwisko, data_urodzenia, plec)
121VALUES
122 (generuj_pesel('2008-01-01', 'K'), 'Anna', 'Nowak', '2008-01-01', 'K'),
123 (generuj_pesel('2007-02-15', 'M'), 'Jan', 'Kowalski', '2007-02-15', 'M'),
124 (generuj_pesel('2009-05-20', 'K'), 'Katarzyna', 'Wiśniewska', '2009-05-20', 'K'),
125 (generuj_pesel('2010-09-10', 'M'), 'Piotr', 'Lis', '2010-09-10', 'M'),
126 (generuj_pesel('2011-11-30', 'K'), 'Małgorzata', 'Dąbrowska', '2011-11-30', 'K');
127
128
129-- Wstawianie informacji o 5 osobach w wieku co najmniej 60 lat
130INSERT INTO Ludzie (PESEL, imie, nazwisko, data_urodzenia, plec)
131VALUES
132 (generuj_pesel('1960-01-01', 'K'), 'Barbara', 'Kowalczyk', '1960-01-01', 'K'),
133 (generuj_pesel('1958-04-15', 'M'), 'Stanisław', 'Nowak', '1958-04-15', 'M'),
134 (generuj_pesel('1956-07-20', 'K'), 'Zofia', 'Lis', '1956-07-20', 'K'),
135 (generuj_pesel('1955-09-10', 'M'), 'Tadeusz', 'Szymański', '1955-09-10', 'M'),
136 (generuj_pesel('1954-11-30', 'K'), 'Irena', 'Duda', '1954-11-30', 'K');
137
138-- Wstawianie informacji o 45 osobach dorosłych w wieku poniżej 60 lat
139INSERT INTO Ludzie (PESEL, imie, nazwisko, data_urodzenia, plec)
140VALUES
141 (generuj_pesel('1965-05-15', 'M'), 'Jan', 'Kowalski', '1965-05-15', 'M'),
142 (generuj_pesel('1966-08-22', 'K'), 'Anna', 'Nowak', '1966-08-22', 'K'),
143 (generuj_pesel('1967-12-10', 'M'), 'Piotr', 'Wiśniewski', '1967-12-10', 'M'),
144 (generuj_pesel('1968-03-05', 'K'), 'Katarzyna', 'Dąbrowska', '1968-03-05', 'K'),
145 (generuj_pesel('1969-07-30', 'M'), 'Marcin', 'Lewandowski', '1969-07-30', 'M'),
146 (generuj_pesel('1970-02-18', 'K'), 'Mateusz', 'Kozłowski', '1970-02-18', 'K'),
147 (generuj_pesel('1971-11-03', 'K'), 'Magdalena', 'Jankowska', '1971-11-03', 'K'),
148 (generuj_pesel('1972-09-20', 'M'), 'Grzegorz', 'Wójcik', '1972-09-20', 'M'),
149 (generuj_pesel('1973-04-12', 'K'), 'Natalia', 'Kaczmarek', '1973-04-12', 'K'),
150 (generuj_pesel('1974-06-25', 'M'), 'Adam', 'Mazur', '1974-06-25', 'M'),
151 (generuj_pesel('1975-10-08', 'K'), 'Ewa', 'Adamczyk', '1975-10-08', 'K'),
152 (generuj_pesel('1976-01-29', 'M'), 'Tomasz', 'Kwiatkowski', '1976-01-29', 'M'),
153 (generuj_pesel('1977-07-15', 'K'), 'Izabela', 'Krawczyk', '1977-07-15', 'K'),
154 (generuj_pesel('1978-12-04', 'M'), 'Dawid', 'Piotrowski', '1978-12-04', 'M'),
155 (generuj_pesel('1979-09-17', 'K'), 'Patrycja', 'Grabowska', '1979-09-17', 'K'),
156 (generuj_pesel('1980-03-08', 'M'), 'Kamil', 'Nowakowski', '1980-03-08', 'M'),
157 (generuj_pesel('1981-05-23', 'K'), 'Monika', 'Zielińska', '1981-05-23', 'K'),
158 (generuj_pesel('1982-08-31', 'M'), 'Rafał', 'Szymański', '1982-08-31', 'M'),
159 (generuj_pesel('1983-06-10', 'K'), 'Alicja', 'Jabłońska', '1983-06-10', 'K'),
160 (generuj_pesel('1984-02-14', 'M'), 'Michał', 'Olszewski', '1984-02-14', 'M'),
161 (generuj_pesel('1985-05-15', 'K'), 'Agnieszka', 'Witkowska', '1985-05-15', 'K'),
162 (generuj_pesel('1986-08-22', 'M'), 'Bartłomiej', 'Kubiak', '1986-08-22', 'M'),
163 (generuj_pesel('1987-12-10', 'K'), 'Karolina', 'Sikora', '1987-12-10', 'K'),
164 (generuj_pesel('1988-03-05', 'M'), 'Łukasz', 'Ostrowski', '1988-03-05', 'M'),
165 (generuj_pesel('1989-07-30', 'M'), 'Dariusz', 'Zając', '1989-07-30', 'M'),
166 (generuj_pesel('1990-02-18', 'K'), 'Marcelina', 'Rutkowska', '1990-02-18', 'K'),
167 (generuj_pesel('1991-11-03', 'M'), 'Marek', 'Baran', '1991-11-03', 'M'),
168 (generuj_pesel('1992-09-20', 'K'), 'Kamila', 'Lis', '1992-09-20', 'K'),
169 (generuj_pesel('1993-04-12', 'M'), 'Radosław', 'Głowacki', '1993-04-12', 'M'),
170 (generuj_pesel('1994-06-25', 'K'), 'Dominika', 'Pawłowska', '1994-06-25', 'K'),
171 (generuj_pesel('1995-10-08', 'M'), 'Hubert', 'Witkowski', '1995-10-08', 'M'),
172 (generuj_pesel('1977-01-29', 'K'), 'Ewelina', 'Jóźwiak', '1977-01-29', 'K'),
173 (generuj_pesel('1998-07-15', 'M'), 'Szymon', 'Sawicki', '1998-07-15', 'M'),
174 (generuj_pesel('1984-12-04', 'K'), 'Nikola', 'Majewska', '1984-12-04', 'K'),
175 (generuj_pesel('1993-09-17', 'M'), 'Artur', 'Czarnecki', '1993-09-17', 'M'),
176 (generuj_pesel('1979-03-08', 'K'), 'Adrianna', 'Tomaszewska', '1979-03-08', 'K'),
177 (generuj_pesel('1991-05-23', 'M'), 'Krzysztof', 'Włodarczyk', '1991-05-23', 'M'),
178 (generuj_pesel('1981-08-31', 'K'), 'Patrycja', 'Piotrowska', '1981-08-31', 'K'),
179 (generuj_pesel('1997-06-10', 'M'), 'Daniel', 'Kaczmarczyk', '1997-06-10', 'M'),
180 (generuj_pesel('1987-02-14', 'K'), 'Oliwia', 'Borkowska', '1987-02-14', 'K'),
181 (generuj_pesel('1980-05-15', 'M'), 'Adrian', 'Sokołowski', '1980-05-15', 'M'),
182 (generuj_pesel('1982-08-22', 'K'), 'Justyna', 'Kaczorowska', '1982-08-22', 'K'),
183 (generuj_pesel('1975-12-10', 'M'), 'Łukasz', 'Gajewski', '1975-12-10', 'M'),
184 (generuj_pesel('1990-03-05', 'K'), 'Natalia', 'Pawlak', '1990-03-05', 'K'),
185 (generuj_pesel('1965-07-30', 'M'), 'Mikołaj', 'Zalewski', '1965-07-30', 'M');
186
187INSERT INTO Zawody(nazwa, pensja_min, pensja_max)
188VALUES
189 ("Polityk", 10000, 50000),
190 ("Nauczyciel", 5000, 8000),
191 ("Informatyk", 3000, 50000),
192 ("Lekarz", 5000, 60000);
193
194-- Wstawianie do tabeli Pracownicy przy użyciu kursora
195CREATE PROCEDURE IF NOT EXISTS insert_pracownicy()
196BEGIN
197 DECLARE done INT DEFAULT FALSE;
198 DECLARE pesel_var CHAR(11);
199 DECLARE imie_var VARCHAR(30);
200 DECLARE nazwisko_var VARCHAR(30);
201 DECLARE data_urodzenia_var DATE;
202 DECLARE plec_var CHAR;
203 DECLARE zawod_id_var INT;
204 DECLARE pensja_var FLOAT;
205 DECLARE pensja_min_var FLOAT;
206
207 DECLARE pelnoletni_cursor CURSOR FOR
208 SELECT PESEL, imie, nazwisko, data_urodzenia, plec
209 FROM Ludzie
210 WHERE YEAR(CURDATE()) - YEAR(data_urodzenia) >= 18;
211 DECLARE CONTINUE HANDLER FOR NOT FOUND
212 SET done = TRUE;
213
214 OPEN pelnoletni_cursor;
215
216 r: LOOP
217 FETCH pelnoletni_cursor INTO pesel_var, imie_var, nazwisko_var, data_urodzenia_var, plec_var;
218 IF done THEN
219 LEAVE r;
220 END IF;
221
222 IF plec_var = 'M' AND YEAR(CURDATE()) - YEAR(data_urodzenia_var) <= 65 OR
223 plec_var = 'K' AND YEAR(CURDATE()) - YEAR(data_urodzenia_var) <= 60 THEN
224 SET zawod_id_var = FLOOR(RAND() * 4) + 1; -- Z lekarzami
225 SET pensja_var = ROUND(RAND() * (SELECT (pensja_max + 1) FROM Zawody WHERE zawod_id = zawod_id_var), 2);
226 SET pensja_min_var = (SELECT pensja_min FROM Zawody WHERE zawod_id = zawod_id_var);
227 IF pensja_var < pensja_min_var THEN
228 SET pensja_var = pensja_min_var;
229 END IF;
230 ELSE
231 SET zawod_id_var = FLOOR(RAND() * 3) + 1; -- Bez lekarzy
232 SET pensja_var = ROUND(RAND() * (SELECT (pensja_max + 1) FROM Zawody WHERE zawod_id = zawod_id_var), 2);
233 SET pensja_min_var = (SELECT pensja_min FROM Zawody WHERE zawod_id = zawod_id_var);
234 IF pensja_var < pensja_min_var THEN
235 SET pensja_var = pensja_min_var;
236 END IF;
237 END IF;
238
239 INSERT INTO Pracownicy (PESEL, zawod_id, pensja)
240 VALUES (pesel_var, zawod_id_var, pensja_var);
241 END LOOP;
242
243 CLOSE pelnoletni_cursor;
244END;
245
246CALL insert_pracownicy;
247
248-- 2.
249CREATE INDEX ludzie_idx ON Ludzie(plec, imie);
250CREATE INDEX pracownicy_idx ON Pracownicy(pensja);
251
252EXPLAIN SELECT * FROM Ludzie WHERE plec = 'K' AND imie LIKE 'A%'; -- indeks użyty
253EXPLAIN SELECT * FROM Ludzie WHERE plec = 'K';
254EXPLAIN SELECT * FROM Ludzie WHERE imie LIKE 'K%';
255EXPLAIN SELECT * FROM Pracownicy WHERE pensja < 2000; -- indeks użyty
256EXPLAIN SELECT * FROM
257Pracownicy
258INNER JOIN Zawody
259ON Pracownicy.zawod_id = Zawody.zawod_id
260INNER JOIN Ludzie
261ON Pracownicy.PESEL = Ludzie.PESEL
262WHERE nazwa = 'Informatyk' AND plec = 'M' AND pensja > 10000; -- indeks użyty
263
264SHOW INDEX FROM Ludzie;
265SHOW INDEX FROM Pracownicy;
266
267-- 3.
268CREATE PROCEDURE IF NOT EXISTS daj_podwyzke(nazwa_zawodu VARCHAR(50))
269daj_podwyzke_label: BEGIN
270 DECLARE pensja_maksymalna FLOAT;
271 DECLARE faktyczna_najwieksza_pensja FLOAT;
272
273 SET pensja_maksymalna = (
274 SELECT pensja_max FROM
275 Pracownicy
276 INNER JOIN Zawody
277 ON Pracownicy.zawod_id = Zawody.zawod_id
278 WHERE Zawody.nazwa = nazwa_zawodu
279 LIMIT 1
280 );
281
282 SET faktyczna_najwieksza_pensja = (
283 SELECT MAX(Pracownicy.pensja) FROM
284 Pracownicy
285 INNER JOIN Zawody
286 ON Pracownicy.zawod_id = Zawody.zawod_id
287 WHERE Zawody.nazwa = nazwa_zawodu
288 );
289
290 IF faktyczna_najwieksza_pensja > pensja_maksymalna THEN
291 LEAVE daj_podwyzke_label;
292 END IF;
293
294 UPDATE Pracownicy
295 INNER JOIN Zawody ON Pracownicy.zawod_id = Zawody.zawod_id
296 SET Pracownicy.pensja = Pracownicy.pensja * 1.05
297 WHERE Zawody.nazwa = nazwa_zawodu;
298
299END daj_podwyzke_label;
300
301-- 4.
302SET @sql_query = "
303 SELECT COUNT(*) FROM
304 Pracownicy
305 INNER JOIN Ludzie ON
306 Pracownicy.PESEL = Ludzie.PESEL
307 INNER JOIN Zawody ON
308 Pracownicy.zawod_id = Zawody.zawod_id
309 WHERE Ludzie.plec = 'K'
310 AND Zawody.nazwa = ?
311";
312PREPARE stmt_zlicz_kobiety_w_zawodzie FROM @sql_query;
313
314EXECUTE stmt_zlicz_kobiety_w_zawodzie USING 'Informatyk';
315