pQrvFn4t

1--GRUPA 1
2
3/*1*/
4
5select departments.department_id, departments.department_name, count(*) as liczba
6from departments, employees
7where employees.department_id = departments.department_id
8group by departments.department_id, departments.department_name
9having count(*) = (select top 1 count(departments.department_id) from departments, employees where employees.department_id = departments.department_id group by departments.department_id)
10
11/*2*/
12
13select jobs.job_title
14from employees, jobs
15where employees.job_id = jobs.job_id
16and employees.hire_date = 
17		(select min(employees.hire_date)
18		from employees)
19
20--3. Utworz blok z zastosowaniem kursora, w ktorym:
21--   a) wypisane zostana nazwiska wszystkich pracownikow z wybranego dzialu, podawanego w definicji kursora,
22--   zarabiajacych minimalna stawke dla swojego stanowiska
23--   b) wypisujacy na koniec komunikat: Minimnalna place ma _ pracownikow lub
24--   wszyscy zarabiajawiecej niz minimum
25declare @cursor cursor, @numerDzialu int, @licznik int, @numerPracownika int
26
27set @numerDzialu = 50
28set @licznik = 0
29set @cursor = cursor for
30
31select employees.employee_id
32from employees, jobs, departments
33where departments.department_id = @numerDzialu and
34employees.department_id = departments.department_id and
35employees.salary = (select jobs.min_salary where employees.job_id = jobs.job_id)
36
37open @cursor
38fetch next from @cursor into @numerPracownika
39while @@FETCH_STATUS = 0
40begin
41	set @licznik = @licznik + 1
42	declare @nazwisko varchar(25)
43	set @nazwisko = (select employees.last_name from employees where employees.employee_id = @numerPracownika)
44	print @nazwisko
45	fetch next from @cursor into @numerPracownika
46end
47if (@licznik > 0 )
48	print 'Minimalna place ma ' + convert(varchar(20), @licznik) + ' pracownikow'
49else
50	print 'Wszyscy pracownicy dzialu zarabiaja wiecej niz minimum'
51close @cursor
52deallocate @cursor
53
54/*4*/
55
56drop function percentage_size
57go
58create function percentage_size(@numerKraju char(2)) returns float
59begin
60	declare @procent float
61	declare @wszystkieOddzialy float
62	declare @oddzialy float
63	set @wszystkieOddzialy= (select count(*) from departments)
64	set @oddzialy = (select count(departments.department_id)
65	from countries, departments, locations
66	where departments.location_id = locations.location_id and
67	locations.country_id = countries.country_id and
68	countries.country_id = @numerKraju
69	group by countries.country_id)
70	set @procent = 100 * @oddzialy / @wszystkieOddzialy
71	return @procent
72end
73
74select countries.country_name, dbo.percentage_size(countries.country_id) from countries
75group by countries.country_name, countries.country_id
76
77--5. Utworz wyzlacz, ktory po zmodyfikowaniu placy minimalnej zmieni place kazdemu pracownikowi o taka wartosc,
78--   o jaka zmienila sie placa minimalna dla jego stanowiska. Napisz rowniez polecenie uruchamiajace wyzwalacz
79drop trigger wyzwalacz2
80go
81create trigger wyzwalacz2
82	   on jobs
83	   instead of update
84as
85	declare @aktualnaMinimalna int = (select jobs.min_salary from jobs where jobs.job_id = (select job_id from inserted))
86	declare @nowaMinimalna int = (select min_salary from inserted)
87	declare @delta int = abs(@aktualnaMinimalna - @nowaMinimalna)
88	declare @stanowisko varchar(10) = (select job_id from inserted)
89	update employees
90	set salary = salary + @delta
91	where employees.job_id = @stanowisko
92	update jobs
93	set min_salary = @nowaMinimalna
94	where job_id = @stanowisko
95go
96
97select jobs.job_id, employees.salary, jobs.min_salary from employees, jobs
98where employees.job_id = jobs.job_id and jobs.min_salary = 5000
99update jobs
100set min_salary = 5000
101where min_salary = 4400
102
103--GRUPA 2
104
105use HR
106
107--1. Wyswietl identyfikatory oddzialow(department_id), nazwy oddzialow (department_ame) i liczbe pracownikow, 
108--   dla tych oddzialow, w ktorych pracuje mniej niz 3 pracownikow
109
110select departments.department_id, departments.department_name, count(*) as liczba
111from departments, employees
112where employees.department_id = departments.department_id
113group by departments.department_id, departments.department_name
114having count(*) < 3
115
116--2. Wyswietl nazwy oddzialow (department_name) oraz srednie wynagrodzenie (salary) w tych oddzialach.
117--   Wyniki posortuj malejaco wzgledem kwoty i wykonaj jej zaokraglenie do 1 miejsca po przecinku.
118
119select departments.department_name, round(avg(employees.salary), 1)
120from departments, employees
121where departments.department_id = employees.employee_id
122group by departments.department_name
123
124--3. Utworz blok z zastosowaniem kursora, w ktorym:
125--   a) wypisane zostana stanowiska (job_id) wszystkich pracownikow (tabela employees) zarabiajacyh powyzej okreslonej stawki (podanej w definicji kursora)
126--   b) wypisuje na koniec komunikat: Wiecej zarabia sie na [liczba] stanowiskach lub na zadnym nie zarabia sie tak duzo
127
128declare @kursor1 cursor, @stawka money = 300000.00, @stanowisko varchar(10), @licznik int;
129
130set @licznik = 0;
131set @kursor1 = cursor for
132select employees.job_id from employees
133where employees.salary > @stawka
134open @kursor1
135fetch next from @kursor1 into @stanowisko
136while @@FETCH_STATUS = 0
137begin
138	set @licznik = @licznik + 1;
139	print @stanowisko
140	fetch next from @kursor1 into @stanowisko
141end
142if (@licznik < 1)
143print 'Na zadnym stanowisku nie zarabias sie tak duzo'
144else
145print 'Wiecej zarabia sie na ' + convert(varchar(10), @licznik) + ' stanowiskach'
146close @kursor1
147deallocate @kursor1
148
149select * from employees
150
151--4. Napisz funkcje podajaca dla kazdego krajue, ile procent wszystkich oddzialow znajduje sie  wtym kraju,
152--   wywolaj ja wewnatrz zapytania dajacego wynik w postacie dwoch kolumn: country name, nazwa_funkcji
153
154--drop function funkcja1
155drop function funkcja1
156go
157create function funkcja1(@numerKraju char(2)) returns varchar(20)
158begin
159	declare @procent float
160	declare @wszystkieOddzialy float
161	declare @oddzialy float
162	set @wszystkieOddzialy= (select count(*) from departments)
163	set @oddzialy = (select count(departments.department_id)
164	from countries, departments, locations
165	where departments.location_id = locations.location_id and
166	locations.country_id = countries.country_id and
167	countries.country_id = @numerKraju
168	group by countries.country_id)
169	set @procent = 100 * @oddzialy / @wszystkieOddzialy
170	return convert(varchar(20), @procent)
171end
172
173select countries.country_name, dbo.funkcja1(countries.country_id) from countries
174group by countries.country_name, countries.country_id
175
176--5. Utworz wyzwalacz, ktory przy stawianiu nowego rekordu do tabeli countries, jesli uzytkownik nie poda nazwy panstwa (country_name),
177--	 bedzie wpisywal pod nazwe panstwa wartosc 'Nowa nazwa'. Napisz rowniez polecenie uruchamiajace wyzwalacz
178
179go
180create trigger triger1
181	on countries
182	for insert
183as
184	declare @numerKraju char(2)
185	set @numerKraju = (select country_id from inserted)
186	if (select countries.country_name from countries where countries.country_id = @numerKraju) is null
187	begin
188		update countries
189		set country_name = 'Nowa nazwa'
190		where countries.country_id = @numerKraju
191	end
192go
193
194select * from countries
195insert into countries values('PL', 'Poland', 1)
196insert into countries(country_id, region_id) values('SW', 1)
197
198--GRUPA 3
199use hr
200
201--1. Wyswietl nazwiska pracownikow, ktorych wspolpracownicy (przynajmniej jeden) z dzialu pracuja krocej i zarabiaja wiecej
202select e1.last_name, count(*)
203from employees e1, employees e2, departments
204where e1.employee_id <> e2.employee_id
205and e1.department_id = e2.department_id
206and e1.salary < e2.salary
207and e1.hire_date < e2.hire_date
208group by e1.last_name
209having count(*) > 1
210
211--2. Jakie stanowisko zajmowal pierwszy zatrudniony pracownik (na podstawie tabeli employees)
212select top 1 jobs.job_title, employees.job_id, employees.hire_date 
213from employees, jobs
214where employees.job_id = jobs.job_id
215order by employees.hire_date asc
216
217--3. Utworz blok, w ktorym:
218--	 a) wypisane zostana nazwiska wszystkich pracownikow z dzialu podawanego jako parametr w bloku,
219--	 zarabiajacych minimalna stawke dla swojego stanowiska
220--	 b) wypisuje na koniec komunikat w oddzialu _ minimalna place ma _ pracownikow lub wszyscy pracownicy dzialu zarabiaja wiecej niz minimum
221
222declare @kursor1 cursor, @numerOddzialu int, @pracownik int, @licznik2 int, @nazwisko varchar(25)
223
224set @numerOddzialu = 30
225set @licznik2 = 0
226set @kursor1 = cursor for
227select employees.employee_id from employees, jobs
228where employees.department_id = @numerOddzialu and
229	employees.job_id = jobs.job_id and
230	employees.salary = jobs.min_salary
231open @kursor1
232fetch next from @kursor1 into @pracownik
233while @@FETCH_STATUS = 0
234begin
235	set @licznik2 = @licznik2 + 1
236	set @nazwisko = (select employees.last_name from employees
237	where employees.employee_id = @pracownik)
238	print @nazwisko
239	fetch next from @kursor1 into @pracownik
240end
241if (@licznik2 = 0)
242	print 'Wszyscy pracownicy zarabiaja wiecej niz minimum'
243else
244	print 'W oddziale ' + convert(varchar(10), @numerOddzialu) + ' minimalna place ma ' + convert(varchar(10), @licznik2)
245close @kursor1
246deallocate @kursor1
247
248select employees.department_id from
249employees, jobs
250where employees.job_id = jobs.job_id and
251employees.salary = jobs.min_salary
252
253--4. Napisz funkcje podajaca dla kazdego kraju, ile procent wszystkich oddzialow znajduje sie  wtym kraju,
254--   wywolaj ja wewnatrz zapytania dajacego wynik w postaci dwoch kolumn: country_name, nazwa_funkcji
255drop function funkcja1
256go
257create function funkcja1(@numerKraju char(2)) returns varchar(20)
258begin
259	declare @procent float
260	declare @wszystkieOddzialy float
261	declare @oddzialy float
262	set @wszystkieOddzialy= (select count(*) from departments)
263	set @oddzialy = (select count(departments.department_id)
264	from countries, departments, locations
265	where departments.location_id = locations.location_id and
266	locations.country_id = countries.country_id and
267	countries.country_id = @numerKraju
268	group by countries.country_id)
269	set @procent = 100 * @oddzialy / @wszystkieOddzialy
270	return convert(varchar(20), @procent)
271end
272
273select countries.country_name, dbo.funkcja1(countries.country_id) from countries
274group by countries.country_name, countries.country_id
275
276--GRUPA 3 - jakieÅ› inne rozwiÄ…zania
277
278use HR
279--zadanie 1
280select distinct employees.last_name,employees.salary,department_name 
281from employees,departments, employees as e
282where departments.department_id=employees.department_id
283and e.department_id=departments.department_id
284and e.salary > employees.salary
285and DATEDIFF(day,employees.hire_date,GETDATE()) > DATEDIFF(day,e.hire_date,GETDATE())
286
287--zadanie 2
288select top 1 job_title from jobs,employees
289where employees.job_id=jobs.job_id
290order by hire_date asc
291
292
293--zadanie 3
294--drop procedure pierwsza
295go
296create procedure pierwsza @id int
297as begin
298declare @kursor1 cursor, @nazwisko varchar(25), @licznik int = 0
299set @kursor1 = cursor for
300select last_name from employees,jobs
301where employees.job_id=jobs.job_id
302and employees.salary=jobs.min_salary
303and employees.department_id=@id
304
305open @kursor1;
306fetch next from @kursor1 into @nazwisko;
307while @@FETCH_STATUS=0
308begin
309	print @nazwisko
310	fetch next from @kursor1 into @nazwisko
311	set @licznik = @licznik + 1
312end
313close @kursor1;
314deallocate @kursor1;
315if(@licznik >0)
316print 
317'W oddziale ' 
318+ convert(varchar(25),@id) 
319+ ' minimalna prace ma ' 
320+ convert(varchar(25),@licznik)
321else print 'Wszyscy pracownicy dzialu zarabiaja wiecej niz mimimum'
322end
323go
324exec pierwsza 90
325
326
327--zadanie 4
328
329go
330create function funkcja() returns table
331return
332((select count(countries.country_id)/count(*) from departments,locations,countries 
333where departments.location_id=locations.location_id
334and countries.country_id=locations.country_id
335group by countries.country_id))
336go
337
338
339--dzisiejsze kolos
340
341-- grupa 1 
342--1
343SELECT DISTINCT employees.employee_id, first_name, last_name
344FROM employees, job_history
345WHERE employees.employee_id = job_history.employee_id;
346
347----2
348SELECT DISTINCT employees.job_id 
349FROM jobs, employees
350WHERE hire_date = (SELECT MIN(hire_date) FROM employees);
351
352--3
353declare @kursor1 cursor, @stawka money = 8000.00, @stanowisko varchar(10), @licznik int;
354
355set @licznik = 0;
356set @kursor1 = cursor for
357  	select employees.job_id from employees
358	where employees.salary > @stawka
359open @kursor1
360fetch next from @kursor1 into @stanowisko
361while @@FETCH_STATUS = 0u
362begin
363	set @licznik = @licznik + 1;
364	print @stanowisko
365	fetch next from @kursor1 into @stanowisko
366end
367if (@licznik < 1)
368print 'Na zadnym stanowisku nie zarabia sie tak duzo'
369else
370print 'Wiecej zarabia sie na ' + convert(varchar(10), @licznik) + ' stanowiskach'
371close @kursor1
372deallocate @kursor1
373
374----------4
375IF EXISTS (SELECT 1 FROM sys.objects WHERE TYPE = 'F' And NAME = 'PROCENT')
376DROP FUNCTION PROCENT
377GO
378
379CREATE FUNCTION PROCENT(@oddzial INT)
380RETURNS DECIMAL(10, 2)
381AS
382BEGIN
383	DECLARE @wszyscyPracownicy FLOAT;
384	DECLARE @wybranyDzial FLOAT;
385	DECLARE @procent DECIMAL(10, 2);
386
387	SET @wszyscyPracownicy = (SELECT COUNT(employees.employee_id)
388							  FROM employees);
389	SET @wybranyDzial = (SELECT COUNT(employees.employee_id)
390						FROM employees
391						WHERE department_id = @oddzial)
392	SET @procent = (@wybranyDzial / @wszyscyPracownicy) * 100;
393
394	RETURN @procent
395END
396GO
397--DROP FUNCTION PROCENT
398
399SELECT DISTINCT employees.department_id, DBO.PROCENT(department_id) AS PROCENT
400FROM employees
401WHERE department_id IS NOT NULL
402
403
404
405--ZAD5 CORRECT?--
406go
407create trigger wyzwalacz
408	   on employees
409	   for update
410as
411	declare @Minimalna int = (select jobs.min_salary from jobs where jobs.job_id = (select job_id from inserted))
412	declare @Maxymalna int = (select jobs.max_salary from jobs where jobs.job_id = (select job_id from inserted))
413
414	declare @nowapensja int = (select salary from inserted)
415	declare @id_osoby int = (select employee_id from inserted)
416
417	declare @stanowisko varchar(10) = (select job_id from inserted)
418
419	if(@nowapensja > @Maxymalna)
420	begin
421		update employees
422		set salary = @Maxymalna
423		where job_id = @stanowisko
424
425		print('Zmodyfikowano wynagrodzenie do wartości maksymalnej dla pracownika ' + cast(@id_osoby as varchar(5)))
426	end
427	else if(@nowapensja < @Minimalna)
428	begin
429		update employees
430		set salary = @Minimalna
431		where job_id = @stanowisko
432		print('Zmodyfikowano wynagrodzenie do wartości minimalnej dla pracownika ' + cast(@id_osoby as varchar(5)))
433	end
434go