LHAA3v6Q

1--- wybrać tylko te stanowiska na ktorych sredni zarobek wynosi 2000 lub wiecej
2
3Select c.zarobki_srednie, c.Stanowisko from
4(Select AVG(zarobki) as zarobki_srednie, b.stanowisko from Pracownicy b group by b.stanowisko ) as c
5where c.zarobki_srednie >= 2000
6
7-- wybór stanowisk i srednich zarobków na danym stanowisku
8Select AVG(zarobki) as zarobki_srednie, b.stanowisko from Pracownicy b group by b.stanowisko
9
10-- Maksymalne zarobki pracownikow w tabeli Pracownicy dla kazdej grupy Zarobkowej
11select MAX(a.Zarobki) as Zarobek, a.IDGrupa from Pracownicy a
12group by a.IDGrupa 
13
14--Napisac zapytanie generujace liste pracownikow i ich stanowisko z gwiazdka w wierszu pracownika, ktory
15--jest kierownikiem, Kolumne z gwiazdka zatytuluj Kierownik
16
17Select * from (
18SELECT '' as Kierownik, a.IDPracownika, a.Nazwisko, a.Stanowisko FROM Pracownicy a
19WHERE NOT EXISTS (SELECT IDPracownika FROM Pracownicy b
20			  WHERE b.IDKierownika=a.IDPracownika)
21UNION
22--lista kierowników
23SELECT '*' as Kierownik, a.IDPracownika, a.Nazwisko, a.Stanowisko FROM Pracownicy a
24WHERE EXISTS (SELECT IDPracownika FROM Pracownicy b
25			  WHERE b.IDKierownika=a.IDPracownika)
26			  )as test order by test.IDPracownika
27
28-- znalezc pracownikow zarabiajacych minimalna place na ich stanowisku , uporzÄ…dkowac malejaco wg zarobkow
29-- to jest raczej zle bo pewnie chodzi o porownanie do tabeli grupy, ale patrzac na tabele grupy to nikt nie zarabia tyle
30select b.nazwisko, b.ImiÄ™, b.Zarobki, b.Stanowisko from Pracownicy b
31where b.Zarobki IN (select MIN(a.Zarobki) from Pracownicy a
32								group by a.Stanowisko) order by Zarobki desc
33
34
35-- stosujac operator ANY stweirdzic czy zarobki ktoregokolwiek z pracownikow firmy sa wieksze niz najnizsze zarobki 
36-- pracownika z dzialu 10
37SELECT Nazwisko, Zarobki From Pracownicy
38WHERE ( Select min(Zarobki) From Pracownicy where IDDzialu=10) < ANY ( Select Zarobki From Pracownicy )
39
40
41-- stosujac funkcje wbudowana DATEDIFF() napisz funkcje ile_lat, ktora jako parametr
42-- ottrzyma date zatrudnienia a w wyniku zwraca ilosc lat przepracowanych (Staz pracy)
43--select DATEDIFF (YY, DataZatr, GETDATE()) AS 'LATA Pracy', *  from pracownicy
44
45IF EXISTS (SELECT * FROM SysOBjects WHERE Name='ile_lat' AND Type='FN')
46	DROP FUNCTION ile_lat
47GO
48CREATE FUNCTION ile_lat (@DataZATRU date) returns int
49AS
50BEGIN
51RETURN (
52		select DATEDIFF (MM, @DataZATRU, GETDATE()) AS 'LATA Pracy'
53		)
54		END
55---- pełne lata - porównujemy miesiące !! dlatego w wywolaniu dizelimy przez 12
56SELECT ([Z506_06].[ile_lat]('1950-12-17')) /12
57
58IF EXISTS (SELECT * FROM SysOBjects WHERE Name='ile_lat' AND Type='FN')
59	DROP FUNCTION ile_lat
60GO
61CREATE FUNCTION ile_lat (@DataZATRU date) returns int
62AS
63BEGIN
64RETURN (
65		select DATEDIFF (MM, @DataZATRU, GETDATE()) AS 'LATA Pracy'
66		)
67		END
68
69SELECT ([Z506_06].[ile_lat]('1950-12-17')) /12
70
71
72
73
74----- tutaj podalem stanmagazynu, ale mozna to zmienic na p.IlośćZamówiona nie wiem o ktore chodzi :) niby ile dostawca dostarczyl, ale
75----- nie wiem jaki byl zamysl 
76IF EXISTS (SELECT * FROM SysOBjects WHERE Name='dostawcyf' AND Type='FN')
77	DROP FUNCTION dostawcyf
78GO
79
80CREATE FUNCTION dostawcyf (@dos int) returns table
81as 
82Return
83( select p.NazwaProduktu, p.StanMagazynu from produkty p, Dostawcy d
84where p.IDdostawcy=d.IDdostawcy
85and d.IDdostawcy=@dos
86and p.IlośćZamówiona <> 0 )
87
88SELECT * FROM [Z506_06].dostawcyf('1')
89
90select * from produkty
91
92
93
94-- zadanie 20 ze zdjecia
95	create table Test1
96	(
97      	ID int identity primary key,
98     	 Value1 nvarchar(10),
99      	Value2 nvarchar(10)
100	)
101	select * from test1
102	insert into Test1 (Value1,Value2) values ( 'AAAA','BBBBB');
103	insert into Test1 (Value1,Value2) values ( 'AAAA','BBBBB');
104	insert into Test1 (Value1,Value2) values ( 'AAAA','BBBBB');
105
106
107IF EXISTS (SELECT * FROM SysOBjects WHERE Name='P1A' AND Type='P')
108	DROP PROCEDURE P1A
109GO
110
111CREATE PROCEDURE P1A @ID int, @Value1 nvarchar(10), @Value2 nvarchar(10)
112AS
113BEGIN
114	IF EXISTS ( Select * FROM test1 where id=@ID)
115			BEGIN
116				UPDATE Test1 set Value1 = @Value1, Value2 = @Value2 where id=@ID
117			END
118	ELSE
119			BEGIN
120				INSERT INTO Test1 (Value1, Value2) VALUES (@Value1, @Value2)
121			END
122END
123--testowanie procedur
124EXEC  P1A 20,'nie', 'tak' -- BRAK ID w TABELI TEST1
125EXEC P1A 1,'EEEE','FFFF' -- ID Istnieje na bazie
126
127--- W zwiazku ze sÄ… wiezy integralnosci na tabeli produkty i opiszam
128-- tworze nowe tabele na podstawie tych dwoch
129SELECT *INTO Produkty2 FROM Produkty
130select * into opiszam2 from opiszam
131--tworze trigger
132IF EXISTS (SELECT * FROM SysOBjects WHERE Name='produkt_delete' AND Type='TR')
133	DROP trigger produkt_delete
134GO
135CREATE TRIGGER produkt_delete ON Produkty2
136for delete
137as
138begin
139if exists ( select * from deleted d, opiszam2 o where d.idproduktu=o.idproduktu  )
140BEGIN
141			ROLLBACK
142			RAISERROR('Nie mozna usunac produktu bo jest w tabeli opiszam!',16,1)
143	END
144END;
145--testuje wyzwalacz
146DELETE FROM Produkty2 WHERE idproduktu=11