Zadania z ostatniego koła z Baz danych
oto rozwiązanie niektórych zadań:




--Zadanie 1

select d.department_id,d.department_name,count(e.employee_id)from departments d, employees e
where e.department_id=d.department_id
group by d.department_id,d.department_name

select count(e.employee_id) from employees e


--Zadanie 2

select d.department_name , round(avg(e.salary),1) as srednia_placa from departments d, employees e
where d.department_id=e.department_id
group by d.department_name
order by avg(e.salary) desc

--Zadanie 3

begin
declare @job_id varchar(25);
declare @stawka int ;
declare @licznik int;

set @stawka=500;
set @licznik=0;
declare kursor cursor for select distinct j.job_id from jobs j, employees e where j.job_id=e.job_id and e.salary>@stawka;
open kursor;

fetch next from kursor into @job_id
while @@FETCH_STATUS=0
        begin
            print @job_id;
            fetch next from kursor into @job_id;
            set @licznik=@licznik+1;
        end
        if(@licznik>0)
        print('Wiecej zarabia sie na '+cast(@licznik as varchar)+' stanowiskach');
        else
        print('Na zadnym stanowisku nie zabrabia sie tak duzo');
close kursor;
deallocate kursor;
end

--Zadanie 4

create function funkcja(@nazwa_kraju varchar(30)) returns float
as
begin
    declare @wszystkie float;
    declare @w_kraju float;

    set @wszystkie = ( select count(d.department_id) from departments d);   
    set @w_kraju = (select count(d.department_id)from departments d, locations l, countries c
                    where l.location_id=d.location_id and c.country_id=l.country_id and c.country_name like @nazwa_kraju)
   
    return (@w_kraju/@wszystkie)*100

end

go
select country_name,cast(round(dbo.funkcja(country_name),1) as varchar(20))+ '%' as procent_wszystkich from countries  group by country_name