Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE ZESPOLY
- (ID_ZESP NUMERIC(2) PRIMARY KEY,
- NAZWA CHARACTER VARYING(20),
- ADRES CHARACTER VARYING(20) );
- CREATE TABLE ETATY
- ( NAZWA CHARACTER VARYING(15) PRIMARY KEY,
- PLACA_OD NUMERIC(6,2),
- PLACA_DO NUMERIC(6,2));
- CREATE TABLE PRACOWNICY
- (ID_PRAC NUMERIC(4) PRIMARY KEY,
- NAZWISKO CHARACTER VARYING(15),
- IMIE CHARACTER VARYING(15),
- ETAT CHARACTER VARYING(15),
- ID_SZEFA NUMERIC(4),
- ZATRUDNIONY DATE,
- PLACA_POD NUMERIC(6,2),
- PLACA_DOD NUMERIC(6,2),
- ID_ZESP NUMERIC(2),
- CHECK(PLACA_POD>100),
- FOREIGN KEY (ID_SZEFA) REFERENCES PRACOWNICY(ID_PRAC),
- FOREIGN KEY (ETAT) REFERENCES ETATY(NAZWA),
- FOREIGN KEY (ID_ZESP) REFERENCES ZESPOLY(ID_ZESP));
- INSERT INTO ZESPOLY VALUES (10,'ADMINISTRACJA', 'PIOTROWO 2');
- INSERT INTO ZESPOLY VALUES (20,'SYSTEMY ROZPROSZONE','PIOTROWO 3A');
- INSERT INTO ZESPOLY VALUES (30,'SYSTEMY EKSPERCKIE', 'STRZELECKA 14');
- INSERT INTO ZESPOLY VALUES (40,'ALGORYTMY', 'WIENIAWSKIEGO 16');
- INSERT INTO ZESPOLY VALUES (50,'BADANIA OPERACYJNE', 'MIELZYNSKIEGO 30');
- INSERT INTO ETATY VALUES ('PROFESOR' ,3000.00, 4000.00);
- INSERT INTO ETATY VALUES ('ADIUNKT' ,2510.00, 3000.00);
- INSERT INTO ETATY VALUES ('ASYSTENT' ,1500.00, 2100.00);
- INSERT INTO ETATY VALUES ('DOKTORANT' ,800.00, 1000.00);
- INSERT INTO ETATY VALUES ('SEKRETARKA',1470.00, 1650.00);
- INSERT INTO ETATY VALUES ('DYREKTOR' ,4280.00,5100.00);
- INSERT INTO PRACOWNICY VALUES (100,'Marecki','Jan' ,'DYREKTOR' ,NULL, '1968-01-01',4730.00,980.50,10);
- INSERT INTO PRACOWNICY VALUES (110,'Janicki','Karol' ,'PROFESOR' ,100 , '1973-05-01',3350.00,610.00,40);
- INSERT INTO PRACOWNICY VALUES (120,'Nowicki','Pawel' ,'PROFESOR' ,100 , '1977-09-01',3070.00, NULL,30);
- INSERT INTO PRACOWNICY VALUES (130,'Nowak','Piotr' ,'PROFESOR' ,100 , '1968-07-01', 3960.00, NULL,20);
- INSERT INTO PRACOWNICY VALUES (140,'Kowalski','Krzysztof','PROFESOR' ,130, '1969-07-05', 3230.00,805.00,20);
- INSERT INTO PRACOWNICY VALUES (150,'Grzybowska','Maria','ADIUNKT' ,130 , '1977-09-01', 2845.50, NULL,20);
- INSERT INTO PRACOWNICY VALUES (160,'Krakowska','Joanna', 'SEKRETARKA' ,130 ,'1985-03-01', 1590.00, NULL,20);
- INSERT INTO PRACOWNICY VALUES (170,'Opolski','Roman' ,'ASYSTENT' ,130 , '1992-10-01', 1839.70, 480.50,20);
- INSERT INTO PRACOWNICY VALUES (190,'Kotarski','Konrad', 'ASYSTENT' ,140 , '1993-09-01', 1971.00, NULL,20);
- INSERT INTO PRACOWNICY VALUES (180,'Makowski', 'Marek', 'ADIUNKT',100 , '1985-09-01', 2610.20, NULL,10);
- INSERT INTO PRACOWNICY VALUES (200,'Przywarek','Leon' ,'DOKTORANT' ,140 , '1995-08-01', 900.00, NULL,30);
- INSERT INTO PRACOWNICY VALUES (210,'Kotlarczyk','Stefan','DOKTORANT' ,130 , '1993-12-01', 900.00,570.60,30);
- INSERT INTO PRACOWNICY VALUES (220,'Siekierski', 'Mateusz','ASYSTENT' ,110 , '1993-10-01', 1889.00, NULL,20);
- INSERT INTO PRACOWNICY VALUES (230,'Dolny', 'Tomasz' ,'ASYSTENT' ,120 , '1992-09-01', 1850.00, 390.00,NULL);
- DELIMITER $$
- CREATE FUNCTION rep_lace(in_string varchar(1000), previous varchar(64), new varchar(64))
- RETURNS varchar(1000)
- DETERMINISTIC
- BEGIN
- DECLARE res varchar(1000);
- DECLARE counter int;
- DECLARE i int;
- SET res = in_string;
- SET counter = 1;
- WHILE(counter <= char_length(res)) DO
- SET i = 1;
- myloop: WHILE(i <= char_length(previous)) DO
- IF SUBSTRING(res, counter, 1) = SUBSTRING(previous, i, 1) THEN
- SET res = REPLACE(res, SUBSTRING(res, counter, 1), SUBSTRING(new, i, 1));
- LEAVE myloop;
- ELSE
- SET i = i + 1;
- END IF;
- END WHILE myloop;
- SET counter = counter + 1;
- END WHILE;
- RETURN res;
- END$$
- SELECT IMIE, rep_lace(NAZWISKO, 'MLKmlk', 'XXXXXX');
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement