· 8 years ago · Dec 02, 2017, 07:26 PM
1-- Zadanie 1
2
3DROP TABLE IF EXISTS Ludzie;
4CREATE TABLE Ludzie(
5 PESEL CHAR(11) NOT NULL PRIMARY KEY CHECK(CHAR_LENGTH(PESEL)=11), -- AND PESEL RLIKE '%[^0-9]%'),
6 imiÄ™ VARCHAR(30) NOT NULL,
7 nazwisko VARCHAR(30) NOT NULL,
8 data_urodzenia DATE NOT NULL,
9 wzrost FLOAT UNSIGNED,
10 waga FLOAT UNSIGNED,
11 rozmiar_buta INT UNSIGNED
12);
13insert into Ludzie (PESEL, imiÄ™, nazwisko, data_urodzenia) values ('79091247500', 'dasd', 'dadasf', '19790912');
14insert into Ludzie (PESEL, imiÄ™, nazwisko, data_urodzenia) values ('qqqqqteiqqq', 'dasd', 'dadasf', '680912');
15
16DROP TABLE IF EXISTS Pracownicy;
17CREATE TABLE Pracownicy(
18 PESEL CHAR(11) NOT NULL PRIMARY KEY,
19 zawod VARCHAR(50) NOT NULL,
20 pensja FLOAT UNSIGNED NOT NULL,
21 FOREIGN KEY (PESEL) REFERENCES Ludzie (PESEL)
22);
23
24DROP TRIGGER IF EXISTS ludzie_before_insert;
25DELIMITER //
26CREATE TRIGGER ludzie_before_insert BEFORE INSERT ON Ludzie
27FOR EACH ROW
28BEGIN
29DECLARE tmpYear CHAR(4);
30DECLARE tmpMonth CHAR(2);
31DECLARE tmpDay CHAR(2);
32DECLARE tmpDate DATE;
33 IF ((NEW.PESEL REGEXP '^[0-9]+$') = 0) THEN
34 SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO = 30001, MESSAGE_TEXT = 'PESEL może zawierać wyłącznie cyfry';
35 END IF;
36
37 SET tmpYear = SUBSTRING(NEW.PESEL, 1, 2);
38 SET tmpMonth = SUBSTRING(NEW.PESEL, 3, 2);
39 SET tmpDay = SUBSTRING(NEW.PESEL, 5, 2);
40 IF (CONVERT(tmpYear, UNSIGNED) < 30) THEN
41 SET tmpYear = CONCAT('20', tmpYear);
42 ELSE
43 SET tmpYear = CONCAT('19', tmpYear);
44 END IF;
45 SET tmpDate = STR_TO_DATE(CONCAT(tmpYear, ',', tmpMonth, ',', tmpDay), '%Y,%m,%d');
46
47 IF (tmpDate <> NEW.data_urodzenia) THEN
48 SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO = 30001, MESSAGE_TEXT = 'Nieodpowiedni PESEL dla daty urodzenia';
49 END IF;
50
51END //
52DELIMITER ;
53
54DROP TRIGGER IF EXISTS pracownicy_before_insert;
55DELIMITER //
56CREATE TRIGGER pracownicy_before_insert BEFORE INSERT ON Pracownicy
57FOR EACH ROW
58BEGIN
59DECLARE tmpYear CHAR(4);
60DECLARE tmpMonth CHAR(2);
61DECLARE tmpDay CHAR(2);
62DECLARE tmpDate DATE;
63
64 SET tmpYear = SUBSTRING(NEW.PESEL, 1, 2);
65 SET tmpMonth = SUBSTRING(NEW.PESEL, 3, 2);
66 SET tmpDay = SUBSTRING(NEW.PESEL, 5, 2);
67 IF (CONVERT(tmpYear, UNSIGNED) < 30) THEN
68 SET tmpYear = CONCAT('20', tmpYear);
69 ELSE
70 SET tmpYear = CONCAT('19', tmpYear);
71 END IF;
72 SET tmpDate = STR_TO_DATE(CONCAT(tmpYear, ',', tmpMonth, ',', tmpDay), '%Y,%m,%d');
73
74 IF ((TIMESTAMPDIFF(YEAR, tmpDate, CURDATE()) < 18)) THEN
75 SIGNAL SQLSTATE '45000' SET MYSQL_ERRNO = 30001, MESSAGE_TEXT = 'Pracownik musi być pełnoletni';
76 END IF;
77
78END //
79DELIMITER ;
80
81insert into Ludzie (PESEL, imiÄ™, nazwisko, data_urodzenia) values ('00071247500', 'darsd', 'dadasef', '20000712');
82Insert into Pracownicy (PESEL, zawod, pensja) values ('00071247500', 'piekarz', 2500.2)
83
84-- Dodawanie informacji o pracownikach:
85DROP PROCEDURE IF EXISTS addFootballers;
86DELIMITER //
87CREATE PROCEDURE addFootballers()
88BEGIN
89DECLARE tmpYear CHAR(4);
90DECLARE tmpMonth CHAR(2);
91DECLARE tmpDay CHAR(2);
92DECLARE tmpDate DATE;
93
94
95WHILE i <= 1200 DO
96 INSERT INTO laptopy (model, producent, procesor, matryca, typ) VALUES (CONCAT('MODEL', i), ((i MOD 15) + 1), ((i MOD 15)+3000), ((i MOD 15) + 1), ((i MOD 4) + 1));
97 SET i = i + 1;
98END WHILE;
99
100END //
101DELIMITER ;
102
103CALL generateRows();