· 8 years ago · Feb 13, 2018, 01:18 AM
1#acl andre:admin,read,write,revert s416135:read,write Known: All:
2
3<<TableOfContents(1)>>
4
5= Lab. 1 =
6
7=== Zad. 1 ===
8
9{{{
10 select nazwisko as nazwisko , (ISNULL(dod_funkc, 0) + placa)*12 as dochod from Pracownicy
11}}}
12
13=== Zad. 2 ===
14
15{{{
16 select top 1 nazwa, dataRozp, kierownik from Projekty ORDER BY dataRozp desc
17
18}}}
19
20=== Zad. 3 ===
21
22{{{
23 select nazwa as nazwa, DATEDIFF(m,dataRozp,ISNULL(dataZakonczFakt,GETDATE())) as czas_trwania from Projekty
24
25}}}
26
27=== Zad. 4 ===
28
29{{{
30 select nazwisko, placa, stanowisko from Pracownicy where placa > 1500 and stanowisko in ('adiunkt', 'doktorant')
31}}}
32
33=== Zad. 5 ===
34
35{{{
36 select nazwisko from Pracownicy where szef is null
37}}}
38
39=== Zad. 6 ===
40
41{{{
42 select nazwa from Projekty where nazwa like '%web%'
43
44}}}
45
46=== Zad. 7 ===
47
48{{{
49 select CAST(CAST(2/4 AS numeric(6,4)) AS decimal(6,2)) as wynik
50}}}
51
52= Lab. 2 =
53
54=== Zad. 1 ===
55
56{{{
57SELECT p.nazwisko,
58 p.placa,
59 p.stanowisko,
60 r.placa_min,
61 r.placa_max
62 FROM Pracownicy p
63
64 JOIN Stanowiska r
65 ON p.stanowisko = r.nazwa
66}}}
67
68=== Zad. 2 ===
69
70{{{
71SELECT p.nazwisko,
72 p.placa,
73 p.stanowisko,
74 r.placa_min,
75 r.placa_max
76 FROM Pracownicy p
77
78 JOIN Stanowiska r
79 ON p.stanowisko = r.nazwa
80 WHERE (p.placa > r.placa_max or p.placa < r.placa_min) and p.stanowisko = 'doktorant'
81}}}
82
83=== Zad. 3 ===
84
85{{{
86SELECT * FROM Pracownicy
87SELECT * FROM Projekty
88SELECT * FROM Realizacje
89
90SELECT p.nazwisko,
91 o.nazwa [projekt]
92
93 FROM Pracownicy p
94
95 JOIN Realizacje r
96 ON p.id = r.idPrac
97 JOIN Projekty o
98 ON r.idProj = o.id
99 ORDER BY p.nazwisko
100}}}
101
102=== Zad. 4 ===
103
104{{{
105--SELECT * FROM Pracownicy
106
107--SELECT p1.nazwisko, p2.nazwisko [szef]
108-- FROM Pracownicy P1
109
110-- JOIN Pracownicy P2
111-- ON P1.szef = P2.szef
112-- AND P1.id > P2.id
113
114-- ORDER BY p1.nazwisko
115
116}}}
117
118=== Zad. 5 ===
119
120{{{
121SELECT P.nazwisko[pracownik],
122 P2.nazwisko[szef]
123 FROM Pracownicy P
124
125 LEFT OUTER JOIN Pracownicy P2
126}}}
127
128=== Zad. 6 ===
129
130{{{
131SELECT nazwisko
132 FROM Pracownicy p
133
134 LEFT OUTER JOIN Projekty r
135 ON p.id = r.kierownik
136
137WHERE r.kierownik IS NULL
138}}}
139
140=== Zad. 7 ===
141
142{{{
143SELECT P.nazwisko
144 FROM Pracownicy P
145
146 LEFT OUTER JOIN Realizacje R
147 ON P.id = R.idPrac
148 AND R.idProj = 10
149
150 WHERE R.idProj IS NULL;
151}}}
152
153=== Zad. 8 ===
154
155{{{
156
157SELECT p1.nazwisko, p2.nazwisko
158 FROM Pracownicy p1
159 JOIN Pracownicy p2
160 ON p1.nazwisko = p2.nazwisko
161 AND p1.id != p2.id
162----------------Zapytanie zwróci wartość jeśli są osoby o tym samym nazwisku, jeśłi nie nie zwraca nic----------
163}}}
164
165=== Zad. 9 ===
166
167{{{
168SELECT t1.placa_min AS 'adiunkt',
169t2.placa_min AS 'doktorant',
170t3.placa_min AS 'dziekan',
171t4.placa_min AS 'profesor'
172FROM (SELECT placa_min FROM Stanowiska WHERE nazwa = 'adiunkt') AS t1
173CROSS JOIN (SELECT placa_min FROM Stanowiska WHERE nazwa = 'doktorant') AS t2
174CROSS JOIN (SELECT placa_min FROM Stanowiska WHERE nazwa = 'dziekan') AS t3
175CROSS JOIN (SELECT placa_min FROM Stanowiska WHERE nazwa = 'profesor') AS t4;
176}}}
177
178=== Zad. 10 ===
179
180{{{
181SELECT DISTINCT P.nazwisko
182 FROM Projekty Proj
183
184 LEFT OUTER JOIN Pracownicy P
185 ON Proj.kierownik = P.id
186
187 LEFT OUTER JOIN Realizacje R
188 ON Proj.id = R.idProj
189 AND R.idPrac = P.id
190 WHERE R.idPrac IS NULL;
191}}}
192
193= Lab. 3 =
194
195=== Zad. 1 ===
196
197{{{
198SELECT nazwisko FROM Pracownicy
199WHERE placa > (SELECT placa FROM Pracownicy WHERE nazwisko = 'Różycka')
200
201}}}
202
203=== Zad. 2 ===
204
205{{{
206SELECT nazwisko FROM Pracownicy
207WHERE id not in(SELECT kierownik FROM Projekty)
208
209}}}
210
211
212=== Zad. 3 ===
213
214{{{
215SELECT nazwisko FROM Pracownicy
216WHERE id not in (SELECT idPrac FROM Realizacje WHERE idProj = 10)
217}}}
218
219
220=== Zad. 4 ===
221
222{{{
223SELECT nazwisko FROM Pracownicy
224WHERE id in (SELECT idPrac FROM Realizacje WHERE idProj =(SELECT id FROM Projekty WHERE nazwa = 'e-learning'))
225}}}
226
227
228=== Zad. 5 ===
229
230{{{
231SELECT nazwisko , placa FROM Pracownicy
232WHERE placa >= ALL (SELECT placa FROM Pracownicy)
233}}}
234
235
236=== Zad. 6 ===
237
238{{{
239SELECT DISTINCT P.nazwisko
240 FROM Projekty Proj
241
242 LEFT OUTER JOIN Pracownicy P
243 ON Proj.kierownik = P.id
244
245 LEFT OUTER JOIN Realizacje R
246 ON Proj.id = R.idProj
247 AND R.idPrac = P.id
248 WHERE R.idPrac IS NULL;
249}}}
250
251
252=== Zad. 7 ===
253
254{{{
255SELECT nazwisko FROM Pracownicy
256WHERE not exists (SELECT kierownik FROM Projekty WHERE Pracownicy.id = Projekty.kierownik)
257}}}
258
259
260=== Zad. 8 ===
261
262{{{
263SELECT DISTINCT nazwisko
264 FROM Pracownicy AS PS1
265 WHERE not EXISTS
266 (SELECT id
267 FROM Projekty AS PS3
268 WHERE not EXISTS
269 (SELECT *
270 FROM Realizacje AS PS2
271 WHERE (PS1.id = PS2.idPrac)
272 AND (PS2.idProj = PS3.id)));
273
274
275}}}
276
277= Lab. 4 =
278
279=== Zad. 4 ===
280
281{{{
282SELECT stanowisko,nazwisko, placa as Placa FROM Pracownicy
283WHERE placa in (SELECT max(placa) FROM Pracownicy GROUP BY Stanowisko)
284}}}
285=== Zad. 5 ===
286
287{{{
288SELECT P.nazwisko, count(P. nazwisko ) as licz_proj FROM Pracownicy P JOIN Realizacje R ON P.id = R.idPrac
289WHERE P.stanowisko <> 'profesor'
290GROUP BY p.nazwisko
291HAVING count(P.nazwisko) > 1
292}}}
293
294=== Zad. 6 ===
295
296{{{
297SELECT p.nazwisko, count(idProj) 'liczba projektów'
298FROM Pracownicy p
299LEFT OUTER JOIN Realizacje r
300ON p.id = r.idPrac
301Group by nazwisko
302HAVING COUNT(r.idProj) >= ALL (SELECT COUNT(re.idProj)
303FROM Pracownicy pr
304LEFT JOIN Realizacje re
305ON pr.id=re.idPrac
306GROUP BY pr.nazwisko )
307}}}
308
309=== Zad. 7 ===
310
311{{{
312SELECT nazwisko, placa FROM Pracownicy P1WHERE @n> (SELECT COUNT(DISTINCT (P2.placa) )
313FROM Pracownicy P2 WHERE P2.placa > P1.placa)
314}}}
315
316=== Zad. 8 ===
317{{{
318SELECT nazwisko FROM Pracownicy
319GROUP BY nazwisko
320HAVING count(nazwisko)>1
321}}}
322
323=== Zad. 9 ===
324{{{
325SELECT nazwa, dataZakonczPlan, (SELECT 'projekt zakonczony') as status FROM Projekty
326WHERE dataZakonczFakt IS NOT NULL
327uniON all
328SELECT nazwa,dataZakonczPlan, (SELECT 'projekt trwa') FROM Projekty
329WHERE dataZakonczFakt IS NULL
330}}}
331
332=== Zad. 11 ===
333{{{
334SELECT nazwisko, placa/(SELECT AVG(placa) FROM Pracownicy)*100
335FROM Pracownicy
336}}}
337
338= Lab. 6 =
339=== 6.1 ===
340
341{{{
342
343CREATE TABLE Uczestnicy(
344PESEL varchar(11) PRIMARY KEY,
345nazwisko varchar(20) not null,
346miasto varchar(50) default 'POZNAN',
347);
348
349CREATE TABLE Kursy(
350Kod int IDENTITY(1,1) primary key,
351nazwa varchar(30) unique,
352liczba_dni int CONSTRAINT spr_dni CHECK (liczba_dni in (1,2,3,4,5)),
353cena as (liczba_dni * 1000),
354);
355
356CREATE TABLE Udzial(
357uczestnik varchar(11) REFERENCES Uczestnicy(PESEL),
358kurs int REFERENCES Kursy(Kod),
359data_od DATETIME,
360data_do DATETIME,
361check(DATEDIFF(dd,data_od,data_do)>1),
362status varchar(10) CHECK(status in ('w trakcie', 'ukonczony', 'nie ukonczony')),
363);
364}}}
365
366
367=== 6.3 ===
368
369{{{
370
371ALTER TABLE Uczestnicy
372ADD CONSTRAINT spr_dlugosc CHECK(LEN(PESEL)=11);
373
374ALTER TABLE Udzial
375ADD id int IDENTITY(1,1) primary key;
376
377ALTER TABLE Kursy
378DROP CONSTRAINT spr_dni;
379
380}}}
381
382=== 6.4 ===
383{{{
384
385CREATE SEQUENCE:
386
387CREATE SCHEMA Kursy ;
388GO
389
390CREATE SEQUENCE Kursy.klucz
391 AS int
392 START WITH 1
393 INCREMENT BY 1 ;
394
395CREATE TABLE Kursy(
396Kod int primary key,
397(...)
398
399przy insertach:
400INSERT Kursy.Kursy (...)
401 VALUES (NEXT VALUE FOR Kursky.klucz, (...)
402
403
404
405IDENTITY:
406
407Kod int IDENTITY(1,1) primary key
408
409przy insertach pomijamy wartość Kod, wartosc zostaje sama dodana i zinkrementowana
410
411}}}
412
413=== 6.5 ===
414{{{
415
416IF OBJECT_ID('Uczestnicy', 'U') IS NOT NULL
417 DROP TABLE Uczestnicy
418
419IF OBJECT_ID('Udzial', 'U') IS NOT NULL
420 DROP TABLE Udzial
421
422IF OBJECT_ID('Nazwa_tabeli', 'U') IS NOT NULL
423 DROP TABLE Kursy
424}}}
425
426= Lab. 9 =
427zad dom 9.
428
4299.1
430[attachment:9.1.PNG]
431
4329.2
433[attachment:9.2.PNG]
434
435= Projekt =
436
437PROJEKT
438[[BAD-Projekt-Wstep-HerwartNiepsuj.sql]]
439[[BAD opracowanie projektu_v2.pdf]]
440[[bad.sql]]
441
442
443
444= Lab. 10 =
445
446=== 10.3 ===
447{{{
448
449CREATE PROCEDURE Podwyzka
450 @proc TINYINT = 10,
451 @kwota MONEY OUTPUT
452AS
453BEGIN
454
455 SET @kwota = (SELECT SUM(placa) FROM Pracownicy WHERE placa <2000)*0.1
456 UPDATE Pracownicy SET placa=placa + placa*0.1 WHERE placa <2000
457 END;
458}}}
459
460= Lab. 11 =
461
462=== Zad. 1 ===
463{{{
464CREATE FUNCTION LiczLata
465(
466 @zatrudniony DATETIME
467)
468 RETURNS int
469AS
470BEGIN
471 RETURN DATEDIFF (yy, @zatrudniony,GETDATE())
472END;
473
474SELECT nazwisko, dbo.LiczLata(zatrudniony) AS 'ile pracuje' FROM Pracownicy;
475}}}
476
477=== Zad. 2 ===
478{{{
479CREATE FUNCTION StazPracy
480(
481 @x int
482)
483 RETURNS TABLE
484AS
485 RETURN SELECT nazwisko
486 FROM Pracownicy
487 WHERE dbo.LiczLata(zatrudniony) > @x;
488}}}
489
490= Lab. 14 =
491
492
493=== 14.1 ===
494
4951. Dirty read
4962. Non repeatable read, Fantom
4973. Fantom
498
499
500=== 14.2 ===
501{{{
502
503SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
504BEGIN TRAN T1;
505UPDATE Wycieczki SET cena = 500 WHERE cel = 'Ateny';
506UPDATE Bilety SET cena = 500 WHERE klient = 'Kowalski';
507
508SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
509BEGIN TRAN T2;
510UPDATE Bilety SET cena = 5000 WHERE klient = 'Kowalski';
511UPDATE Wycieczki SET cena = 1000 WHERE cel = 'Ateny';
512
513*raz jedno raz drugie*
514
515-----------------------------
516
517SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
518BEGIN TRAN T1;
519UPDATE Wycieczki SET cena = 200 where cel = 'Bangkok';
520SELECT klient FROM Bilety where klient='Kowalski';
521
522SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
523BEGIN TRAN T2;
524UPDATE Bilety SET cena = 50 where klient = 'Kowalski';
525UPDATE Wycieczki SET cena = 500 where cel = 'Bangkok';
526
527*raz jedno raz drugie*
528----------------------------
529
530SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
531BEGIN TRAN T1;
532SELECT cena FROM Bilety where klient = 'Kowalski';
533UPDATE Bilety SET cel = 'Majorka' WHERE klient = 'Nowak'
534
535SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
536BEGIN TRAN T2;
537UPDATE Bilety SET cena = 1000 where klient = 'Kowalski';
538
539*raz jedno raz drugie*
540
541----------------------------
542
543SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
544BEGIN TRAN T1;
545UPDATE Bilety SET cel = 'Kuba' where klient = 'Kowalski';
546SELECT cena FROM Wycieczki where cel = 'Bangkok'
547
548
549SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
550BEGIN TRAN T2;
551UPDATE Wycieczki SET cena = 100 WHERE cel = 'Bangkok';
552SELECT cel FROM Bilety where klient = 'Kowalski';
553
554*raz jedno raz drugie*
555}}}