· 7 years ago · Oct 10, 2018, 11:58 AM
1
2--1
3--CREATE DATABASE s259913;
4
5--2
6DROP SCHEMA IF EXISTS firma CASCADE;
7CREATE SCHEMA firma;
8SET search_path TO firma;
9
10--3
11DROP ROLE IF EXISTS ksiegowosc;
12CREATE ROLE ksiegowosc;
13
14--GRANT SELECT ON s259913 TO ksiegowosc;
15
16--4a
17CREATE TABLE pracownicy (
18 id_pracownika INTEGER NOT NULL,
19 imie TEXT NOT NULL,
20 nazwisko TEXT NOT NULL,
21 adres TEXT NOT NULL,
22 telefon TEXT NOT NULL
23);
24
25CREATE TABLE godziny (
26 id_godziny INTEGER NOT NULL,
27 miesiac_rok TEXT NOT NULL,
28 miesiac DATE NOT NULL,
29 tydzien_roku DATE NOT NULL,
30 liczba_godzin INTEGER NOT NULL,
31 id_pracownika INTEGER NOT NULL
32);
33
34CREATE TABLE pensja_stanowisko (
35 id_pensji INTEGER NOT NULL,
36 stanowisko TEXT NOT NULL,
37 kwota FLOAT NOT NULL
38);
39
40CREATE TABLE premia (
41 id_premii INTEGER NOT NULL,
42 rodzaj TEXT NOT NULL,
43 kwota FLOAT NOT NULL
44);
45
46CREATE TABLE wynagrodzenie (
47 id_wynagrodzenia INTEGER NOT NULL,
48 data TEXT NOT NULL,
49 id_pracownika INTEGER NOT NULL,
50 id_godziny INTEGER NOT NULL,
51 id_pensji INTEGER NOT NULL,
52 id_premii INTEGER NOT NULL
53);
54
55--4b
56ALTER TABLE pracownicy ADD PRIMARY KEY (id_pracownika);
57ALTER TABLE godziny ADD PRIMARY KEY (id_godziny);
58ALTER TABLE pensja_stanowisko ADD PRIMARY KEY (id_pensji);
59ALTER TABLE premia ADD PRIMARY KEY (id_premii);
60ALTER TABLE wynagrodzenie ADD PRIMARY KEY (id_wynagrodzenia);
61
62--4c
63ALTER TABLE godziny ADD CONSTRAINT fk FOREIGN KEY (id_pracownika) REFERENCES pracownicy;
64ALTER TABLE wynagrodzenie ADD CONSTRAINT fk1 FOREIGN KEY (id_pracownika) REFERENCES pracownicy;
65ALTER TABLE wynagrodzenie ADD CONSTRAINT fk2 FOREIGN KEY (id_godziny) REFERENCES godziny;
66ALTER TABLE wynagrodzenie ADD CONSTRAINT fk3 FOREIGN KEY (id_pensji) REFERENCES pensja_stanowisko;
67ALTER TABLE wynagrodzenie ADD CONSTRAINT fk4 FOREIGN KEY (id_premii) REFERENCES premia;
68
69--4d
70CREATE INDEX nazwisko_idx ON pracownicy (nazwisko);
71CREATE INDEX id_pracownika_godziny_idx ON godziny (id_pracownika);
72CREATE INDEX id_pracownika_wynagrodzenie_idx ON wynagrodzenie (id_pracownika);
73
74--4e
75COMMENT ON TABLE pracownicy IS 'informacje o pracownikach';
76COMMENT ON TABLE godziny IS 'ilosc przepracowanych godzin przez pracownika w danym tygodniu';
77COMMENT ON TABLE pensja_stanowisko IS 'pensja na danym stanowisku';
78COMMENT ON TABLE premia IS 'lista dostepnych premii';
79COMMENT ON TABLE wynagrodzenie IS 'wyplata pracownika';
80
81--4f
82--NO ACTION IS DEFAULT
83
84--5abc
85INSERT INTO pracownicy VALUES (1, 'Remigiusz', 'Nowak', 'Lecha Kaczynskiego 1, Warszawa', '111222001');
86INSERT INTO pracownicy VALUES (2, 'Halina', 'Malina', 'Lecha Kaczynskiego 17, Poznan', '111222002');
87INSERT INTO pracownicy VALUES (3, 'Zbigniew', 'Kielbasa', 'Lecha Kaczynskiego 94, Kielce', '111222003');
88INSERT INTO pracownicy VALUES (4, 'Ryszard', 'Cebula', 'Lecha Kaczynskiego 97, Kielce', '111222004');
89INSERT INTO pracownicy VALUES (5, 'Telimena', 'Gotowala', 'Lecha Kaczynskiego 77, Krakow', '111222005');
90INSERT INTO pracownicy VALUES (6, 'Wladyslaw', 'Udany', 'Lecha Kaczynskiego 1144, Wygwizdow', '111222006');
91INSERT INTO pracownicy VALUES (7, 'Mieczyslaw', 'Ptak', 'Lecha Kaczynskiego 44, Gdynia', '111222007');
92INSERT INTO pracownicy VALUES (8, 'Janusz', 'Zurek', 'Lecha Kaczynskiego 0, Radom', '111222008');
93INSERT INTO pracownicy VALUES (9, 'Ryszarda', 'Knedel', 'Lecha Kaczynskiego 43, Sosnowiec', '111222009');
94INSERT INTO pracownicy VALUES (10, 'Grazyna', 'Gotowala-Zurek', 'Lecha Kaczynskiego 12, Sosnowiec', '111222000');
95
96INSERT INTO pensja_stanowisko VALUES (1, 'Menedzer', 6400);
97INSERT INTO pensja_stanowisko VALUES (2, 'Konsultant', 1250.01);
98INSERT INTO pensja_stanowisko VALUES (3, 'Ksiegowy', 1750.33);
99INSERT INTO pensja_stanowisko VALUES (4, 'Stazysta', 0);
100INSERT INTO pensja_stanowisko VALUES (5, 'Przedstawiciel Handlowy', 1900.13);
101INSERT INTO pensja_stanowisko VALUES (6, 'Konserwator powierzchni plaskich', 11500);
102INSERT INTO pensja_stanowisko VALUES (7, 'Ochrona', 1100);
103INSERT INTO pensja_stanowisko VALUES (8, 'Administrator komputerowy', 350);
104INSERT INTO pensja_stanowisko VALUES (9, 'Programista HTML', 450);
105INSERT INTO pensja_stanowisko VALUES (10, 'Kucharz', 3100.98);
106
107INSERT INTO premia VALUES (1, 'brak', 0);
108INSERT INTO premia VALUES (2, 'Super mala', 100);
109INSERT INTO premia VALUES (3, 'Bardzo mala', 200);
110INSERT INTO premia VALUES (4, 'Mala', 300);
111INSERT INTO premia VALUES (5, 'Zwyczajna', 400);
112INSERT INTO premia VALUES (6, 'Powiekszona', 500);
113INSERT INTO premia VALUES (7, 'Duza', 600);
114INSERT INTO premia VALUES (8, 'Bardzo duza', 700);
115INSERT INTO premia VALUES (9, 'Super duza', 800);
116INSERT INTO premia VALUES (10, 'Mega duza', 900);
117
118INSERT INTO godziny VALUES (1, '10/2018', '2018-10-30', '2018-10-30', 160, 1);
119INSERT INTO godziny VALUES (2, '10/2018', '2018-10-30', '2018-10-30', 170, 2);
120INSERT INTO godziny VALUES (3, '10/2018', '2018-10-30', '2018-10-30', 169, 3);
121INSERT INTO godziny VALUES (4, '10/2018', '2018-10-30', '2018-10-30', 180, 4);
122INSERT INTO godziny VALUES (5, '10/2018', '2018-10-30', '2018-10-30', 160, 5);
123INSERT INTO godziny VALUES (6, '10/2018', '2018-10-30', '2018-10-30', 165, 6);
124INSERT INTO godziny VALUES (7, '10/2018', '2018-10-30', '2018-10-30', 160, 7);
125INSERT INTO godziny VALUES (8, '10/2018', '2018-10-30', '2018-10-30', 160, 8);
126INSERT INTO godziny VALUES (9, '10/2018', '2018-10-30', '2018-10-30', 171, 9);
127INSERT INTO godziny VALUES (10, '10/2018', '2018-10-30', '2018-10-30', 160, 10);
128
129INSERT INTO wynagrodzenie VALUES (1, '30-10-2018', 1, 1, 8, 4);
130INSERT INTO wynagrodzenie VALUES (2, '30-10-2018', 2, 2, 9, 1);
131INSERT INTO wynagrodzenie VALUES (3, '30-10-2018', 3, 3, 4, 1);
132INSERT INTO wynagrodzenie VALUES (4, '30-10-2018', 4, 4, 4, 1);
133INSERT INTO wynagrodzenie VALUES (5, '30-10-2018', 5, 5, 6, 7);
134INSERT INTO wynagrodzenie VALUES (6, '30-10-2018', 6, 6, 3, 1);
135INSERT INTO wynagrodzenie VALUES (7, '30-10-2018', 7, 7, 3, 10);
136INSERT INTO wynagrodzenie VALUES (8, '30-10-2018', 8, 8, 3, 1);
137INSERT INTO wynagrodzenie VALUES (9, '30-10-2018', 9, 9, 1, 1);
138INSERT INTO wynagrodzenie VALUES (10, '30-10-2018', 10, 10, 9, 1);
139
140--6a
141SELECT id_pracownika, nazwisko FROM pracownicy;
142
143--6b
144SELECT id_pracownika
145FROM wynagrodzenie
146 JOIN pensja_stanowisko ON wynagrodzenie.id_pensji = pensja_stanowisko.id_pensji
147WHERE kwota > 1000;
148
149--6c
150SELECT id_pracownika
151FROM wynagrodzenie
152 JOIN pensja_stanowisko ON wynagrodzenie.id_pensji = pensja_stanowisko.id_pensji
153 JOIN premia ON wynagrodzenie.id_premii = premia.id_premii
154WHERE pensja_stanowisko.kwota > 2000 AND rodzaj LIKE 'brak';
155
156--6d
157SELECT *
158FROM pracownicy
159WHERE imie LIKE 'J%';
160
161--6e
162SELECT *
163FROM pracownicy
164WHERE nazwisko LIKE '%n%' AND imie LIKE '%a';
165
166--6f
167SELECT imie, nazwisko, liczba_godzin - 160 AS nadgodziny
168FROM wynagrodzenie
169 JOIN pracownicy ON wynagrodzenie.id_pracownika = pracownicy.id_pracownika
170 JOIN godziny ON wynagrodzenie.id_godziny = godziny.id_godziny;
171
172--6g
173SELECT imie, nazwisko
174FROM wynagrodzenie
175 JOIN pracownicy ON wynagrodzenie.id_pracownika = pracownicy.id_pracownika
176 JOIN pensja_stanowisko ON wynagrodzenie.id_pensji = pensja_stanowisko.id_pensji
177WHERE kwota BETWEEN 1500 AND 3000;
178
179--6h
180SELECT imie, nazwisko
181FROM wynagrodzenie
182 JOIN pracownicy ON wynagrodzenie.id_pracownika = pracownicy.id_pracownika
183 JOIN premia ON wynagrodzenie.id_premii = premia.id_premii
184 JOIN godziny ON wynagrodzenie.id_godziny = godziny.id_godziny
185WHERE liczba_godzin > 160 AND rodzaj LIKE 'brak';
186
187--7a
188SELECT imie, nazwisko, kwota
189FROM wynagrodzenie
190 JOIN pracownicy ON wynagrodzenie.id_pracownika = pracownicy.id_pracownika
191 JOIN pensja_stanowisko ON wynagrodzenie.id_pensji = pensja_stanowisko.id_pensji
192ORDER BY kwota DESC;
193
194--7b
195SELECT imie, nazwisko, pensja_stanowisko.kwota, premia.kwota
196FROM wynagrodzenie
197 JOIN pracownicy ON wynagrodzenie.id_pracownika = pracownicy.id_pracownika
198 JOIN pensja_stanowisko ON wynagrodzenie.id_pensji = pensja_stanowisko.id_pensji
199 JOIN premia ON wynagrodzenie.id_premii = premia.id_premii
200ORDER BY pensja_stanowisko.kwota, premia.kwota DESC;
201
202--7c
203SELECT stanowisko, COUNT(id_pracownika) AS ilosc
204FROM wynagrodzenie
205 JOIN pensja_stanowisko ON wynagrodzenie.id_pensji = pensja_stanowisko.id_pensji
206GROUP BY stanowisko;
207
208--7d
209SELECT MIN(pensja_stanowisko.kwota + premia.kwota) AS Minimalna_placa,
210 AVG(pensja_stanowisko.kwota + premia.kwota) AS Srednia_placa,
211 MAX(pensja_stanowisko.kwota + premia.kwota) AS Maksymalna_placa
212FROM wynagrodzenie
213 JOIN pensja_stanowisko ON wynagrodzenie.id_pensji = pensja_stanowisko.id_pensji
214 JOIN premia ON wynagrodzenie.id_premii = premia.id_premii
215WHERE stanowisko LIKE 'Ksiegowy';
216
217--7e
218SELECT SUM(pensja_stanowisko.kwota + premia.kwota) AS suma
219FROM wynagrodzenie
220 JOIN pensja_stanowisko ON wynagrodzenie.id_pensji = pensja_stanowisko.id_pensji
221 JOIN premia ON wynagrodzenie.id_premii = premia.id_premii;
222
223--7f
224SELECT stanowisko, SUM(pensja_stanowisko.kwota + premia.kwota) AS suma
225FROM wynagrodzenie
226 JOIN pensja_stanowisko ON wynagrodzenie.id_pensji = pensja_stanowisko.id_pensji
227 JOIN premia ON wynagrodzenie.id_premii = premia.id_premii
228GROUP BY stanowisko;
229
230--7g
231SELECT stanowisko, COUNT(premia) FILTER (WHERE premia.kwota > 0) AS suma
232FROM wynagrodzenie
233 JOIN pensja_stanowisko ON wynagrodzenie.id_pensji = pensja_stanowisko.id_pensji
234 JOIN premia ON wynagrodzenie.id_premii = premia.id_premii
235GROUP BY stanowisko;
236
237--7h
238-- *************** NO ACTION **************
239-- DELETE FROM pracownicy WHERE id_pracownika IN (
240-- SELECT id_pracownika
241-- FROM wynagrodzenie
242-- JOIN pensja_stanowisko ON wynagrodzenie.id_pensji = pensja_stanowisko.id_pensji
243-- WHERE kwota < 1200
244-- );
245--
246-- SELECT imie, nazwisko, pensja_stanowisko.kwota, premia.kwota
247-- FROM wynagrodzenie
248-- JOIN pracownicy ON wynagrodzenie.id_pracownika = pracownicy.id_pracownika
249-- JOIN pensja_stanowisko ON wynagrodzenie.id_pensji = pensja_stanowisko.id_pensji
250-- JOIN premia ON wynagrodzenie.id_premii = premia.id_premii;
251
252--8a
253UPDATE pracownicy SET telefon = '(+48) ' || telefon;
254SELECT * FROM pracownicy;
255
256--8b
257UPDATE pracownicy SET telefon = substring(telefon from 1 for 9) || '-' ||
258 substring(telefon from 10 for 3) || '-' ||
259 substring(telefon from 13 for 3);
260
261--8c
262SELECT * FROM pracownicy;
263
264SELECT upper(imie), upper(nazwisko)
265FROM pracownicy
266WHERE LENGTH(nazwisko) = (SELECT MAX(LENGTH(nazwisko)) FROM pracownicy);
267
268--8d
269SELECT MD5(imie) AS imie, MD5(nazwisko) AS nazwisko, MD5(''||kwota) AS pensja
270FROM wynagrodzenie
271 JOIN pensja_stanowisko ON wynagrodzenie.id_pensji = pensja_stanowisko.id_pensji
272 JOIN pracownicy ON wynagrodzenie.id_pracownika = pracownicy.id_pracownika;
273
274--9
275SELECT 'Pracownik ' || imie || ' ' || nazwisko || ', w dniu ' || data || ' otrzymal pensje calkowita na kwote ' ||
276 round(CAST(liczba_godzin/160.00 * pensja_stanowisko.kwota + premia.kwota AS NUMERIC ), 2) || ' zl, gdzie wynagrodzenie zasadnicze wynosilo: ' ||
277 pensja_stanowisko.kwota || 'zl, premia: ' || premia.kwota || ' zl, nadgodziny: ' ||
278 round(CAST((liczba_godzin/160.00 - 1.00) * (pensja_stanowisko.kwota) AS NUMERIC), 2) || ' zl.' AS raport
279FROM wynagrodzenie
280 JOIN pracownicy ON wynagrodzenie.id_pracownika = pracownicy.id_pracownika
281 JOIN pensja_stanowisko ON wynagrodzenie.id_pensji = pensja_stanowisko.id_pensji
282 JOIN premia ON wynagrodzenie.id_premii = premia.id_premii
283 JOIN godziny ON wynagrodzenie.id_godziny = godziny.id_godziny;