tLRZicGH

1-- Rafał Ciesnowski
2-- 253982
3
4-- 1.1
5select * from pracownik;
6-- 1.2
7select imie from pracownik;
8-- 1.3
9select imie, nazwisko, dzial from pracownik;
10-- 2.1
11select imie, nazwisko, pensja from pracownik order by pensja desc;
12-- 2.2
13select imie, nazwisko from pracownik order by nazwisko, imie asc;
14-- 2.3
15select nazwisko, dzial, stanowisko from pracownik order by dzial asc, stanowisko desc;
16-- 3.1
17select distinct dzial from pracownik;
18-- 3.2
19select distinct dzial, stanowisko from pracownik;
20-- 3.3
21select distinct dzial, stanowisko from pracownik order by dzial, stanowisko desc;
22-- 4.1
23select imie, nazwisko from pracownik where imie='Jan';
24-- 4.2
25select imie, nazwisko from pracownik where stanowisko='sprzedawca';
26-- 4.3
27select imie, nazwisko, pensja from pracownik where pensja>1500 order by pensja desc;
28-- 5.1
29select imie, nazwisko, dzial, stanowisko from pracownik where dzial='obsluga klienta' and stanowisko='sprzedawca'
30-- 5.2
31select imie, nazwisko, dzial, stanowisko from pracownik where dzial='techniczny' and (stanowisko='kierownik' or stanowisko='sprzedawca');
32-- 5.3
33select * from samochod where marka!='Fiat' and marka!='Ford';
34-- 6.1
35select * from samochod where marka in ('Mercedes','Seat','Opel'); 
36-- 6.2
37select imie, nazwisko, data_zatr from pracownik where imie in ('Anna', 'Marzena', 'Alicja');
38-- 6.3
39select imie, nazwisko, miasto from klient where miasto not in ('Warszawa', 'Wrocław');
40-- 7.1
41select imie, nazwisko from klient where nazwisko like '%k%';
42-- 7.2
43select imie, nazwisko from klient where nazwisko like 'D%ski';
44-- 7.3
45select imie, nazwisko from klient where nazwisko like '_o%' or nazwisko like '_a%';
46-- 8.1
47select * from samochod where poj_silnika between 1100 and 1600;
48-- 8.2
49select * from pracownicy where zatrudnienie between '1997-01-01' and '1997-12-31';
50-- 8.3
51select * from samochod where przebieg between 10000 and 20000 or przebieg between 30000 and 40000;
52-- 9.1
53select * from pracownik where dodatek is null;
54-- 9.2
55select * from klient where nr_karty_kredyt is not null;
56-- 9.3
57select imie, nazwisko, coalesce(dodatek, 0) from pracownik;
58-- 10.1
59select imie, nazwisko, pensja, coalesce(dodatek,0), pensja+coalesce(dodatek,0) as do_zaplaty from pracownik;
60-- 10.2
61select imie, nazwisko, 1.5*coalesce(pensja,0) as nowa_pensja from pracownik;
62-- 10.3
63select imie, nazwisko, 0.01*(pensja+coalesce(dodatek,0)) as jeden_procent from pracownik order by jeden_procent asc;
64-- 11.1
65select top 1 imie, nazwisko from pracownik order by data_zatr asc;
66-- 11.2
67select top 4 nazwisko, imie from pracownik order by nazwisko, imie asc;
68-- 11.3
69select top 1 * from wypozyczenie order by data_wyp desc;
70-- 12.1
71select imie, nazwisko, data_zatr from pracownik where month(data_zatr)=5 order by nazwisko asc, imie asc;
72-- 12.2
73select imie, nazwisko, datediff(day, data_zatr, getdate()) as staz from pracownik order by staz desc;
74-- 12.3
75select marka, typ, datediff(year, data_prod, getdate()) as wiek from samochod order by wiek desc;
76-- 13.1
77select imie, nazwisko, left(imie,1)+'.'+left(nazwisko,1)+'.' as inicjaly from klient order by inicjaly, nazwisko, imie;
78-- 13.2
79select upper(left(imie,1))+lower(right(imie, len(imie)-1)) as imie,
80upper(left(nazwisko,1))+lower(right(nazwisko, len(nazwisko)-1)) as nazwisko
81from pracownik;
82-- 13.3
83select imie, nazwisko, stuff(nr_karty_kredyt, 6, 6, 'xxxxxx')  from klient;
84-- 14.1
85update pracownik set dodatek=50 where dodatek is null;
86-- 14.2
87update klient set imie='Jerzy', nazwisko='Nowak' where id_klient=10;
88-- 14.3
89update pracownik set dodatek+=100 where pensja<1500;
90-- 15.1
91delete from klient where id_klient=17;
92-- 15.2
93delete from wypozyczenie where id_klient=17;
94-- 15.3
95delete from samochod where przebieg>60000;
96-- 16.1
97insert into klient (id_klient, imie, nazwisko, ulica, numer, kod, miasto, telefon)
98values(121, 'Adam', 'Cichy', 'korzenna', '12', '00-950', 'Warszawa', '123-454-321');
99-- 16.2
100insert into samochod (id_samochod, marka, typ, data_prod, kolor, poj_silnika, przebieg)
101values (50, 'Skoda', 'Octavia', '2012-09-01 00:00:00.000', 'srebrny', 1896, 5000);
102-- 16.3
103insert into miejsce (id_miejsce, ulica, numer, miasto, kod, telefon, uwagi)
104values (6, 'Lewartowskiego', 12, 'warszawa', '00-950', '501-501-501', null);
105insert into pracownik (id_pracownik, imie, nazwisko, data_zatr, dzial, stanowisko, pensja, dodatek, id_miejsce, telefon)
106values (6, 'Alojzy', 'Mikos', '2010-08-11 00:00:00.000', 'zaopatrzenie', 'magazynier', 3000, 50, 5, '501-501-501'); 
107--17.1
108select s.id_samochod, s.marka, s.typ, w.data_wyp, w.data_odd
109from samochod s inner join wypozyczenie w on s.id_samochod=w.id_samochod
110where w.data_odd is null;
111--17.2
112select k.imie, k.nazwisko, w.id_samochod, w.data_wyp
113from klient k inner join wypozyczenie w on k.id_klient=w.id_klient
114where w.data_odd is null
115order by k.nazwisko, k.imie;
116--17.3
117select k.imie, k.nazwisko, w.data_wyp, w.kaucja
118from klient k inner join wypozyczenie w on k.id_klient=w.id_klient
119where w.kaucja is not null;
120--18.1 
121select k.imie, k.nazwisko, w.data_wyp, s.marka, s.typ
122from klient k inner join wypozyczenie w on k.id_klient=w.id_klient
123inner join samochod s on w.id_samochod=s.id_samochod
124order by k.nazwisko, k.imie, s.marka, s.typ;
125--18.2
126select m.ulica, m.numer, s.marka, s.typ
127from miejsce m inner join wypozyczenie w on m.id_miejsce=w.id_miejsca_wyp
128inner join samochod s on w.id_samochod=s.id_samochod
129order by m.ulica, m.numer, s.marka, s.typ;
130--18.3
131select s.id_samochod, k.nazwisko, k.imie
132from samochod s inner join wypozyczenie w on s.id_samochod=w.id_samochod
133inner join klient k on k.id_klient=w.id_klient
134order by s.id_samochod, k.nazwisko, k.imie;
135--19.1
136select max(pensja) from pracownik; 
137--19.2
138select avg(pensja) from pracownik;
139--19.3
140select min(data_prod) from samochod;
141--20.1 
142select k.imie, k.nazwisko, count(w.id_klient) as ilosc_wypozyczen
143from klient k left join wypozyczenie w on k.id_klient=w.id_klient
144group by k.imie, k.nazwisko, k.id_klient
145order by count(w.id_klient) desc;
146--20.2
147select s.id_samochod, s.marka, s.typ, count(w.id_samochod) as ilosc_wypozyczen
148from samochod s left join wypozyczenie w on s.id_samochod=w.id_samochod
149group by s.id_samochod, s.marka, s.typ
150order by count(w.id_samochod);
151--20.3
152select p.imie, p.nazwisko, count(w.id_pracow_wyp) as ilosc_wypozyczen
153from pracownik p left join wypozyczenie w on p.id_pracownik=w.id_pracow_wyp
154group by p.imie, p.nazwisko, p.id_pracownik
155order by ilosc_wypozyczen;
156--21.1 
157select k.imie, k.nazwisko, count(w.id_klient) as ilosc_wypozyczen
158from klient k inner join wypozyczenie w on k.id_klient=w.id_klient
159group by k.imie, k.nazwisko, k.id_klient
160having count(w.id_klient)>=2
161order by nazwisko asc, imie asc;
162--21.2
163select s.id_samochod, s.marka, s.typ, count(w.id_samochod) as ilosc_wypozyczen
164from samochod s left join wypozyczenie w on s.id_samochod=w.id_samochod
165group by s.id_samochod, s.marka, s.typ
166having count(w.id_samochod)>=5
167order by s.marka, s.typ;
168--21.3
169select p.imie, p.nazwisko, count(w.id_pracow_wyp) as ilosc_wypozyczen
170from pracownik p left join wypozyczenie w on p.id_pracownik=w.id_pracow_wyp
171group by p.imie, p.nazwisko, p.id_pracownik
172having count(w.id_pracow_wyp)<=20
173order by ilosc_wypozyczen;
174--22.1
175select imie, nazwisko, pensja from pracownik where pensja=(select max(pensja) from pracownik)
176--22.2
177select imie, nazwisko, pensja from pracownik where pensja>(select avg(pensja) from pracownik)
178--22.3
179select marka, typ, data_prod from samochod where data_prod=(select min(data_prod) from samochod)
180--23.1
181select marka, typ, data_prod from samochod where id_samochod not in (select distinct id_samochod from wypozyczenie)
182--23.2
183select imie, nazwisko from klient where id_klient not in (select distinct id_klient from wypozyczenie)
184--23.3
185select imie, nazwisko from pracownik where id_pracownik not in (select distinct id_pracownik from wypozyczenie)
186--24.1
187select s.id_samochod, s.marka, s.typ
188from samochod s join wypozyczenie w on s.id_samochod=w.id_samochod
189group by s.id_samochod, s.marka, s.typ
190having count(w.id_samochod) = 
191(
192select top 1 count(w.id_samochod) as ilosc
193from wypozyczenie w
194group by w.id_samochod
195order by ilosc desc
196)
197order by s.marka asc, s.typ asc
198--24.2
199select k.id_klient, k.imie, k.nazwisko
200from klient k join wypozyczenie w on k.id_klient=w.id_klient
201group by k.id_klient, k.imie, k.nazwisko
202having count(w.id_klient) = (
203select top 1 count(w.id_klient) as ilosc
204from wypozyczenie w
205group by w.id_klient
206order by ilosc asc
207)
208order by k.imie asc, k.nazwisko asc
209--24.3
210select p.id_pracownik, p.imie, p.nazwisko
211from pracownik p join wypozyczenie w on p.id_pracownik=w.id_pracow_wyp
212group by p.id_pracownik, p.imie, p.nazwisko
213having count(p.id_pracownik) = (
214select top 1 count(p.id_pracownik) as ilosc
215from wypozyczenie w
216group by w.id_pracow_wyp
217order by ilosc desc
218)
219order by p.imie asc, p.nazwisko asc
220--25.1
221update pracownik set pensja=1.1*pensja where pensja < (select avg(pensja) from pracownik)
222--25.2
223update pracownik set dodatek=10+coalesce(dodatek, 0)
224where id_pracownik in (
225select p.id_pracownik from pracownik p
226join wypozyczenie w on p.id_pracownik=w.id_pracow_wyp
227where month(data_wyp) = 5
228)
229--25.3
230update pracownik set pensja=pensja*0.95
231where id_pracownik not in (
232select p.id_pracownik from pracownik p
233join wypozyczenie w on p.id_pracownik=w.id_pracow_wyp
234where year(data_wyp) = 1999
235)
236--26.1
237delete from klient where id_klient not in (select distinct id_klient from wypozyczenie)
238--26.2
239delete from samochod where id_samochod not in (select distinct id_samochod from wypozyczenie)
240--26.3
241delete from pracownik where id_pracownik not in (select distinct id_pracow_wyp from wypozyczenie)
242--27.1.1
243select imie, nazwisko from klient
244union
245select imie, nazwisko from pracownik order by 2, 1
246--27.1.2
247select imie, nazwisko from klient
248union all
249select imie, nazwisko from pracownik
250order by 2,1
251--27.2
252select imie from klient
253intersect
254select imie from pracownik
255union
256select nazwisko from klient
257intersect
258select nazwisko from pracownik
259--27.3
260select imie, nazwisko from klient
261except
262select imie, nazwisko from pracownik
263order by imie, nazwisko
264--28.1
265create table pracownik2 (
266id_pracownik int identity(1,1) primary key,
267imie varchar(16) not null,
268nazwisko varchar(32) not null,
269pesel char(11) unique,
270data_zatr date default getdate(),
271pensja money check(pensja>=1000)
272)
273--28.2
274create table naprawa2 (
275id_naprawa int identity(1,1) primary key,
276data_przyjecia date check(data_przyjecia<=getdate()),
277opis nvarchar(256) check(len(opis)>10),
278zaliczka money check((zaliczka>=100) and (zaliczka<=1000))
279)
280--28.3
281create table wykonane_naprawy2 (
282id_pracownik int references pracownik2(id_pracownik),
283id_naprawa int references naprawa2(id_naprawa),
284data_naprawy date default getdate(),
285opis_naprawy varchar(256) not null,
286cena money
287)
288--29.1
289alter table student2 alter column nazwisko varchar(20) not null;
290alter table student2 add constraint unikatowa unique(nr_indeksu);
291alter table student2 add constraint wysokosc check(stypendium>=1000);
292alter table student2 add imie varchar(12) not null
293--29.2
294alter table dostawca2 add constraint unikat unique(nazwa);
295alter table towar2 add nazwa varchar(20) not null;
296alter table towar2 add constraint unikat unique(kod_kreskowy);
297alter table towar2 add constraint id_dostawca foreign key (id_dostawca) references dostawca2(id_dostawca);
298--29.3
299alter table kraj2 alter column nazwa varchar(30) not null;
300alter table gatunek2 alter column nazwa varchar(30) not null;
301alter table zwierze2 add constraint id_gatunek foreign key (id_gatunek) references gatunek2(id_gatunek);
302alter table zwierze2 add constraint id_kraj foreign key (id_kraj) references kraj2(id_kraj);
303--30.1
304drop table if exists kategoria2;
305drop table if exists przedmiot2;
306--30.2
307alter table osoba2 drop column imie2;
308--30.3
309alter table uczen2 drop constraint uczen_nazwisko_unique;
310--31.1
311create table wlasciciel2 (
312id_wlasciciel int identity(1,1) primary key,
313imie varchar(15) not null check(len(imie)>2),
314nazwisko varchar(15) not null check(len(nazwisko)>2),
315data_ur date not null default getdate(),
316ulica varchar(50),
317numer varchar(8),
318kod char(6) not null check(len(kod)=6),
319miejscowosc varchar(30) not null check(len(miejscowosc)>1)
320);
321create table zwierze2(
322id_zwierze int identity(1,1) primary key,
323id_wlasciciel int references wlasciciel2(id_wlasciciel) on delete set null,  
324rasa varchar(30) not null check(len(rasa)>2),
325data_ur date not null default getdate(),
326imie varchar(15) not null check(len(imie)>2)
327)
328--31.2
329alter table film2_gatunek2 add constraint id_film foreign key (id_film) references film2(id_film) on delete cascade;
330alter table film2_gatunek2 add constraint id_gatunek foreign key (id_gatunek) references gatunek2(id_gatunek) on delete cascade;
331--31.3
332alter table pracownik2 add constraint konstrejnt foreign key (id_stanowisko) references stanowisko2(id_stanowisko) on delete set null on update cascade;
333--32.1
334drop procedure if exists wypisz_samochody
335go
336create procedure wypisz_samochody @marka varchar(20) as
337select * from samochod where marka=@marka;
338go
339execute wypisz_samochody 'opel'
340--32.2
341drop procedure if exists zwieksz_pensje
342go
343create procedure zwieksz_pensje @id int, @kwota int as
344update pracownik2 set pensja=pensja+@kwota where id_pracownik=@id;
345go
346execute zwieksz_pensje 1,1000
347--32.3
348drop procedure if exists dodaj_klienta;
349go
350create procedure dodaj_klienta @id int, @imie varchar(20), @nazwisko varchar(20), @nr_karty int, @firma varchar(20), @ulica varchar(20), @numer varchar(20), @miasto varchar(20), @kod char(6), @nip varchar(20), @telefon varchar(20) as
351insert into klient values (@id, @imie, @nazwisko, @nr_karty, @firma, @ulica, @numer, @miasto, @kod, @nip, @telefon);
352go
353execute dodaj_klienta 98, 'Adam', 'Borawski', null, null, 'Alba', 4, 'Belgrad', '23-000', null, '2093-20493'
354select * from klient
355--33.1
356drop function if exists dbo.aktywnosc_klienta
357go
358create function dbo.aktywnosc_klienta(@id_klient int) returns int begin
359	return (select COUNT(*) from wypozyczenie where id_klient = @id_klient)
360end;
361go
362select dbo.aktywnosc_klienta(3) as ile_wyp
363--33.2
364drop function if exists dbo.ile_wypozyczen
365go
366create function dbo.ile_wypozyczen(@data_od date, @data_do date) returns int begin
367	return (select COUNT(*) from wypozyczenie where (data_wyp > @data_od and data_wyp < @data_do))
368end;
369go
370select dbo.ile_wypozyczen('2000-01-01', '2000-12-31') as ile_wypozyczen;
371--33.3
372drop function if exists dbo.roznica_pensji
373go
374create function dbo.roznica_pensji() returns money begin
375	return ((select MAX(pensja) from pracownik) - (select MIN(pensja) from pracownik))
376end;
377go
378select dbo.roznica_pensji() as roznica_pensji
379--34.1
380drop view if exists klient_raport
381go
382create view klient_raport as
383select k.id_klient, k.imie, k.nazwisko, count(w.id_klient) as 'ilosc_wyp'
384from klient k left join wypozyczenie w on k.id_klient = w.id_klient
385group by k.id_klient, k.imie, k.nazwisko
386go
387select * from klient_raport where ilosc_wyp>1
388--34.2
389drop view if exists samochod_raport
390go
391create view samochod_raport as
392select s.id_samochod, s.marka, s.typ, COUNT(w.id_samochod) as 'ilosc_wyp'
393from samochod s left join wypozyczenie w on s.id_samochod = w.id_samochod
394group by s.id_samochod, s.marka, s.typ
395go
396select * from samochod_raport order by ilosc_wyp desc;
397--34.3
398drop view if exists pracownik_raport
399go
400create view pracownik_raport as
401select p.id_pracownik, p.imie, p.nazwisko, COUNT(w.id_pracow_wyp) as 'ilosc_wyp'
402from pracownik p left join wypozyczenie w on p.id_pracownik = w.id_pracow_wyp
403group by p.id_pracownik, p.imie, p.nazwisko
404go
405select * from pracownik_raport where ilosc_wyp > (select AVG(ilosc_wyp) from pracownik_raport)
406--35.1
407drop index if exists klient.klient_telefon
408create unique index klient_telefon on klient(telefon);
409go
410--35.2
411drop index if exists klient.nazwisko_imie
412create clustered index nazwisko_imie on klient(nazwisko, imie);
413go
414--35.3
415drop index if exists samochod.marka_typ
416create index marka_typ on samochod(marka, typ)
417go
418--36.1
419drop trigger if exists anuluj_usuwanie_klienta
420go
421create trigger anuluj_usuwanie_klienta on klient for delete as
422raiserror('zabronione jest usuwanie klientow', 1, 2) rollback
423go
424delete from klient;
425--36.2
426drop trigger if exists mala_pensja
427go
428create trigger mala_pensja on pracownik for insert as begin
429	declare @pensja money, @dodatek money
430	set @pensja = '-1'
431	set @dodatek = '-1'
432	select @pensja = pensja from inserted
433	select @dodatek = dodatek from inserted
434	if (@pensja = 0 or @dodatek = 0 or @pensja = null or @dodatek = null) begin
435		raiserror('za mala pensja', 1, 2) rollback
436	end
437end
438go
439--36.3 ??
440drop trigger if exists duplikat_miejsce
441go
442create trigger duplikat_miejsce on miejsce for insert as
443if exists (select * from miejsce m inner join inserted n on
444m.ulica = n.ulica and m.numer = n.numer and m.miasto = n.miasto and m.kod = n.kod)
445begin
446	raiserror ('adres istnieje', 11, 1) rollback
447end
448go
449--37.1
450alter table samochod add usuniety bit default 0
451drop trigger if exists usuniety_samochod
452go
453create trigger usuniety_samochod on samochod instead of delete as begin
454	update samochod
455	set usuniety=1 where id_samochod in (select id_samochod from deleted)
456end
457go
458delete from samochod where id_samochod=3;
459select * from samochod;
460--37.2
461drop trigger if exists usun_miejsce_i_wypozyczenia
462go
463create trigger usun_miejsce_i_wypozyczenia on miejsce instead of delete as begin
464	if @@rowcount > 1 begin
465		print 'nie mozna usunac kilku';
466	end
467	else begin
468		delete from wypozyczenie where id_miejsca_wyp in(select id_miejsce from deleted)
469		or id_miejsca_odd in(select id_miejsce from deleted);
470		delete from miejsce where id_miejsce in(select id_miejsce from deleted)
471	end
472end
473go
474-- 37.3
475drop trigger if exists samochod_blokada
476go
477create trigger samochod_blokada on samochod instead of delete, update, insert as begin
478	declare @cnt int = @@rowcount
479	while @cnt > 0 begin
480		raiserror('blad', 1, 2) rollback
481		set @cnt=@cnt-1
482	end
483end
484go
485--38.1
486drop trigger if exists usunieci_pracownicy
487go
488create trigger usunieci_pracownicy on pracownik for delete as begin
489	declare kursor_deleted cursor for select imie, nazwisko from deleted;
490	open kursor_deleted
491	declare @imie varchar(15), @nazwisko varchar(20)
492	fetch next from kursor_deleted into @imie, @nazwisko
493	while @@fetch_status = 0 begin
494		print 'usunięto: '+@imie+' '+ @nazwisko
495		fetch next from kursor_deleted into @imie, @nazwisko
496	end
497	close kursor_deleted
498	deallocate kursor_deleted
499end
500go
501delete from pracownik
502--38.2
503create table usuniete_samochody (
504id_samochod int primary key,
505marka varchar(20) not null,
506typ varchar(16) not null,
507data_prod datetime not null,
508kolor varchar(16) not null,
509poj_silnika smallint not null,
510przebieg integer not null
511);
512drop trigger if exists przenies_samochod
513go
514create trigger samochod_przenies on samochod for delete as begin
515	declare kursor_przenies cursor for select * from deleted;
516	open kursor_przenies
517	declare @id int, @marka varchar(20), @typ varchar(16), @data_prod datetime, @kolor varchar(16), @poj_silnika smallint, @przebieg integer
518	fetch next from kursor_przenies into @id, @marka, @typ, @data_prod, @kolor, @poj_silnika, @przebieg
519	while @@fetch_status=0 begin
520		insert into usuniete_samochody(id_samochod, marka, typ, data_prod, kolor, poj_silnika, przebieg) values
521		(@id, @marka, @typ, @data_prod, @kolor, @poj_silnika, @przebieg);
522		fetch next from kursor_przenies into @id, @marka, @typ, @data_prod, @kolor, @poj_silnika, @przebieg
523	end
524	close kursor_przenies
525	deallocate kursor_przenies
526end
527go
528--38.3
529drop trigger if exists zerowanie_dodatku
530go
531create trigger zero_dodatku on pracownik after update as begin
532	declare @id int, @pensja decimal(8,2)
533	declare kursor_pensja cursor for select id_pracownik, pensja from inserted;
534	open kursor_pensja
535	fetch next from kursor_pensja into @id, @pensja
536	while @@fetch_status=0 begin
537		if @pensja > (select pensja from deleted where id_pracownik=@id) begin
538			update pracownik
539			set dodatek=0 where id_pracownik=@id;
540		end
541		fetch next from kursor_pensja into @id, @pensja
542	end
543	close kursor_pensja
544	deallocate kursor_pensja
545end
546go
547--39.1
548drop view if exists ocena_klienta
549go
550create view ocena_klienta as
551select k.id_klient, k.imie, k.nazwisko, case
552	when count(w.id_klient)>=2 then 'tak'
553	else 'nie' end as staly_klient
554from klient k left join wypozyczenie w on k.id_klient=w.id_klient
555group by k.id_klient, k.imie, k.nazwisko;
556go
557select * from ocena_klienta where staly_klient='nie' order by nazwisko, imie;
558--39.2
559drop view if exists pensja_pracownika
560go
561create view pensja_pracownika as
562select id_pracownik, imie, nazwisko, case
563	when pensja<1500 then 'mała'
564	when pensja>=1500 and pensja <3000 then 'średnia'
565	when pensja>=3000 then 'duża' end as zarobki
566from pracownik
567go
568select * from pensja_pracownika where zarobki='mała'
569--39.3
570drop view if exists pracownik_informacje
571go
572create view pracownik_informacje as
573select p.id_pracownik, p.imie, p.nazwisko, datediff(year, p.data_zatr, getdate()) as staz_pracy, count(w.id_pracow_wyp) as wypozyczenia, case
574	when p.dodatek>0 then 'tak'
575	when p.dodatek=0 then 'nie'
576	else 'brak' end as dodatek
577from pracownik p left join wypozyczenie w on p.id_pracownik=w.id_pracow_wyp
578group by p.id_pracownik,p.imie,p.nazwisko, p.data_zatr, p.dodatek
579go
580select * from pracownik_informacje where dodatek='tak'
581select * from pracownik_informacje where id_pracownik=1
582--40.1
583drop procedure if exists usun_widoki
584go
585create procedure usun_widoki as begin
586	declare @sql nvarchar(200);
587	while exists (select top 1 1 from information_schema.tables
588	where table_catalog=db_name() and table_type = 'view') begin
589		select @sql = 'drop view ' + table_name from information_schema.tables
590		where table_catalog = db_name() and table_type='view';
591		exec sp_executesql @sql
592	end
593end
594go
595exec usun_widoki;
596-- 40.2
597drop procedure if exists usun_klucze_obce
598go
599create procedure usun_klucze_obce as begin
600	while(exists(select 1 from information_schema.table_constraints where constraint_type='foreign key')) begin
601		declare @sql nvarchar(2000)
602		select top 1 @sql=('alter table ' + table_schema + '.[' + table_name + '] drop constraint [' + constraint_name + ']')
603		from information_schema.table_constraints where constraint_type = 'foreign key'
604        exec (@sql)
605    end
606end
607exec usun_klucze_obce
608select * from information_schema.table_constraints;
609go
610-- 40.3
611drop procedure if exists klient_dodaj_kolumne_rabat
612go
613create procedure klient_dodaj_kolumne_rabat as begin
614	if col_length('klient','rabat') is null begin
615		alter table klient add rabat int default 0
616		insert into klient(rabat) values (0)
617	end
618end
619exec klient_dodaj_kolumne_rabat