· 7 years ago · Dec 19, 2018, 09:52 AM
1--/////////////////////////////////////////////////////////// ZAJECIA 1 ////////////////////////////////////////////////////////////
2
3/*CREATE TABLE IF NOT EXISTS TEST
4(
5ID INTEGER,
6NAZWA TEXT
7);*/
8
9--DROP TABLE IF EXISTS TEST;
10
11/*ALTER TABLE TEST
12RENAME TO TEST_1;*/
13
14/*ALTER TABLE TEST_1
15RENAME COLUMN ID TO NUMER;*/
16
17/*ALTER TABLE TEST_1
18ADD COLUMN PESEL TEXT;*/
19
20/*ALTER TABLE TEST_1
21DROP COLUMN IF EXISTS PESEL;*/
22
23-- warunki / walsciwosci do kolumn
24/*
25NOT NULL
26DEFAULT
27UNIQUE
28
29*/
30
31--/////////////////////////////////////////////////////////// ZAJECIA 2 ////////////////////////////////////////////////////////////
32
33
34/*CREATE TABLE IF NOT EXISTS TEST
35(
36ID INTEGER,
37NAZWA TEXT
38);*/
39
40--DROP TABLE IF EXISTS TEST;
41
42/*ALTER TABLE TEST
43RENAME TO TEST_1;*/
44
45/*ALTER TABLE TEST_1
46RENAME COLUMN ID TO NUMER;*/
47
48/*ALTER TABLE TEST_1
49ADD COLUMN PESEL TEXT;*/
50
51/*ALTER TABLE TEST_1
52DROP COLUMN IF EXISTS PESEL;*/
53
54-- warunki / walsciwosci do kolumn
55
56/*
57
58-------- CONSTRAINTY --------
59
60NOT NULL NULL
61UNIQUE - nie moze sie powtarzac
62PRIMARY KEY - klucz glowny
63FOREIGN KEY - klucz obcy
64DEFAULT
65
66
67*/
68
69CREATE TABLE tab_2
70(
71id INTEGER,
72name TEXT
73)
74
75CREATE TABLE tab_1
76(
77id INTEGER UNIQUE NOT NULL PRIMARY KEY,
78nazwa TEXT NOT NULL,
79tab2_id INT REFERENCES tab_2(id)
80)
81
82
83
84
85-- wprowadzanie danych dokladnie
86INSERT INTO tab_2(id,name)
87VALUES (1, 'abcd');
88
89
90
91-- wprowadzanie danych w domyslnej kolejnosci
92INSERT INTO tab_2
93VALUES (2, 'efgh');
94
95
96INSERT INTO tab_2
97VALUES (3);
98
99SELECT * FROM tab_2;
100
101INSERT INTO tab_2
102VALUES (4,'i'), (5,'j'), (6,'h');
103
104
105
106
107DROP TABLE IF EXISTS produkt;
108
109
110
111
112
113CREATE TABLE tab1
114(
115id SERIAL PRIMARY KEY,
116name TEXT DEFAULT 'imie'
117)
118
119SELECT * FROM tab1;
120
121INSERT INTO tab1(name)
122VALUES ('h');
123
124CREATE TABLE produkt
125(
126id SERIAL PRIMARY KEY,
127nazwa TEXT NOT NULL DEFAULT 'imie',
128cena NUMERIC(10,2) NOT NULL CHECK(cena>=0)
129)
130
131
132
133
134
135
136INSERT INTO produkt(nazwa,cena)
137VALUES ('malina',7.99);
138
139
140DROP TABLE IF EXISTS zamowienie;
141
142
143CREATE TABLE zamowienie
144(
145id SERIAL PRIMARY KEY,
146produkt_id INTEGER,
147ilosc INTEGER NOT NULL CHECK(ilosc>0),
148FOREIGN KEY(produkt_id) REFERENCES produkt(id)
149)
150
151INSERT INTO zamowienie(produkt_id,ilosc)
152VALUES (2,3), (3,5), (4,8), (5,1);
153
154
155
156DELETE FROM produkt
157WHERE id=2;
158
159
160UPDATE produkt
161SET cena='1000'
162WHERE id=3;
163
164SELECT * FROM produkt
165ORDER BY id;
166
167
168
169--/////////////////////////////////////////////////////////// ZAJECIA 3 ////////////////////////////////////////////////////////////
170
171
172
173/*CREATE TABLE IF NOT EXISTS TEST
174(
175ID INTEGER,
176NAZWA TEXT
177);*/
178
179--DROP TABLE IF EXISTS TEST;
180
181/*ALTER TABLE TEST
182RENAME TO TEST_1;*/
183
184/*ALTER TABLE TEST_1
185RENAME COLUMN ID TO NUMER;*/
186
187/*ALTER TABLE TEST_1
188ADD COLUMN PESEL TEXT;*/
189
190/*ALTER TABLE TEST_1
191DROP COLUMN IF EXISTS PESEL;*/
192
193-- warunki / walsciwosci do kolumn
194/*
195NOT NULL
196DEFAULT
197UNIQUE
198
199*/
200
201CREATE TABLE IF NOT EXISTS PRACOWNIK
202(
203PRACOWNIK_ID SERIAL PRIMARY KEY,
204IMIE TEXT NOT NULL,
205NAZWISKO TEXT NOT NULL,
206DRUGIE_IMIE TEXT,
207PLEC VARCHAR(1) NOT NULL
208 CHECK(PLEC IN ('K','M')),
209PESEL VARCHAR(11) NOT NULL
210 CHECK(LENGTH(PESEL)=11)
211)
212
213
214INSERT INTO PRACOWNIK (IMIE,NAZWISKO,PLEC,PESEL)
215 VALUES ('Mateusz','Motyliński','M','99118833774');
216
217SELECT *
218FROM PRACOWNIK;
219
220ALTER TABLE PRACOWNIK ADD UNIQUE(PESEL);
221
222DROP
223
224
225
226CREATE TABLE IF NOT EXISTS ZATRUDNIENIE
227(
228ZATRUDNIENIE_ID SERIAL PRIMARY KEY,
229PRACOWNIK_ID INTEGER REFERENCES PRACOWNIK(PRACOWNIK_ID) NOT NULL, -- utworzenie klucza obcego
230DATA_OD DATE NOT NULL,
231DATA_DO DATE,
232CZY_AKTYWNY VARCHAR(1) NOT NULL DEFAULT 'T'
233 CHECK(CZY_AKTYWNY IN ('T','N')),
234STANOWISKO TEXT NOT NULL,
235PENSJA NUMERIC(10,2) NOT NULL
236)
237
238SELECT *
239FROM ZATRUDNIENIE;
240
241
242
243
244--/////////////////////////////////////////////////////////// ZAJECIA 4 ////////////////////////////////////////////////////////////
245
246SELECT *
247FROM pracownik;
248
249
250CREATE FUNCTION wprowadz_pracownika(IN _imie TEXT, IN _nazwisko TEXT, IN _drugie_imie TEXT, IN _plec VARCHAR(1), IN _pesel VARCHAR(11)) returns BOOLEAN AS $$
251BEGIN
252
253 INSERT INTO pracownik (imie, nazwisko, drugie_imie, plec, pesel)
254 VALUES (_imie, _nazwisko, _drugie_imie, _plec, _pesel);
255
256 RETURN TRUE;
257
258END;
259$$ LANGUAGE plpgsql;
260
261
262
263
264
265
266CREATE FUNCTION aktualizuj_pracownika(IN _id INTEGER, IN _imie TEXT, IN _nazwisko TEXT, IN _drugie_imie TEXT, IN _plec VARCHAR(1), IN _pesel VARCHAR(11)) returns TEXT AS $$
267DECLARE ilosc INTEGER;
268BEGIN
269
270 SELECT count(*)
271 INTO ilosc
272 FROM pracownik
273 WHERE pracownik_id = _id;
274 IF ilosc > 0 THEN
275
276 UPDATE pracownik
277 SET imie = _imie,
278 nazwisko = _nazwisko,
279 drugie_imie = _drugie_imie,
280 plec = _plec,
281 pesel = _pesel
282 WHERE pracownik_id = _id;
283
284 RETURN 'Ok';
285
286 ELSE
287
288 RETURN 'Brak ID rownego =' || _id || 'w tabeli PRACOWNIK';
289
290 END IF;
291
292END;
293$$ LANGUAGE plpgsql;
294
295SELECT *
296FROM pracownik;
297
298SELECT *
299FROM zatrudnienie;
300
301UPDATE zatrudnienie
302SET pensja=15000, pracownik_id=4
303WHERE zatrudnienie_id=2;
304
305INSERT INTO zatrudnienie(pracownik_id, data_od, data_do, czy_aktywny, stanowisko, pensja)
306VALUES (5, '2018-01-01', '2020-01-02', 'N', 'barista', 1500);
307
308DROP FUNCTION wprowadz_pracownika(TEXT, TEXT, TEXT, VARCHAR(1), VARCHAR(11));
309DROP FUNCTION aktualizuj_pracownika(INTEGER, TEXT, TEXT, TEXT, VARCHAR(1), VARCHAR(11));
310
311SELECT wprowadz_pracownika('Kamil','Krecichwost','Kekus','K','13243546576');
312SELECT aktualizuj_pracownika(5,'Domino','Miarka','Maximus','M','16273849582');
313
314
315-- nazwatabeli% ROWTYPE - funckja sama pobiera typy kolumn
316
317
318--/////////////////////////////////////////////////////////// ZAJECIA 5 ////////////////////////////////////////////////////////////
319
320
321CREATE FUNCTION showZatrudnienia(IN _PRACOWNIK_ID INT)
322RETURNS TEXT
323AS
324$$
325DECLARE
326
327kursorek REFCURSOR
328_imie_ pracownicy.imie%TYPE
329_nazwisko_ pracownicy.nazwisko%TYPE
330_pesel_ pracownicy.pesel%TYPE
331_stanowisko_ zatrudnienie.stanowisko%TYPE
332_data_od_ zatrudnienie.data_od%TYPE
333_data_do_ zatrudnienie.data_do%TYPE
334
335BEGIN
336
337OPEN kursorek FOR
338SELECT imie, nazwisko, pesel, stanowisko, data_od, data_do
339FROM pracownik AS p
340JOIN ZATRUDNIENIE AS z ON p.pracownik_id = z.pracownik_id
341WHERE p.pracownik_id = _PRACOWNIK_ID
342
343CLOSE kursorek;
344END;
345$$ LANGUAGE plpgsql