07 - Ushtrime





Funksionet e Grupit


Ushtrim 1

Duke u bazuar ne historikun e punesimit
Afishoni kodin e punonjesve  dhe daten kur kane filluar pune per here te pare ne kompani

Zgjidhje

select employee_id,min(start_date) 
from job_history group by employee_id
order by employee_id;



Ushtrim 2
Afishoni kodin e cdo departamenti dhe numrin e punonjesve qe ka ai departament.

Zgjidhje

select department_id,count(*) as punonjes
from employees group by department_id;



Ushtrim 3
Afishoni kodin e cdo pozicioni pune dhe numrin e punonjesve qe kane aktualisht ate pozicion pune.

Zgjidhje

select job_id,count(*) as punonjes
from employees group by job_id ;



Ushtrim 4
Afishoni emrin e qytetit dhe numrin e zyrave qe ndodhen ne ate qytet

zgjidhje
select city,count(*) as zyra
from locations group by city;



Ushtrim 5
Afishoni kodin e shtetit, emrin e qytetit dhe numrin e zyrave qe ndodhen ne ate qytet te atij shteti.

zgjidhje
select country_id,city,count(*) as zyra
from locations group by country_id, city;


JOIN


Ushtrim
Afishoni punonjesit qe kane te njejten vjetersi pune ne vite perbri njeri tjetrit si me poshte:



Punonjesi nuk duhet te asociohet me vetveten

zgjidhje

select 
e1.employee_id,e1.first_name,e1.last_name,
trunc(months_between(sysdate,e1.hire_date)/12) as V1,
e2.employee_id,e2.first_name,e2.last_name,
trunc(months_between(sysdate,e2.hire_date)/12) as V2
from employees e1 join employees e2
on trunc(months_between(sysdate,e1.hire_date)/12)=trunc(months_between(sysdate,e2.hire_date)/12)
where e1.employee_id<>e2.employee_id;


Ushtrim 2
Per te gjithe punonjesit afishoni: 
  1. kodin e tij, 
  2. kodin e pozicionit te punes qe ka aktualisht dhe 
  3. kodin e pozicionit te punes qe ka patur me pare

 Kodi Pozicioni aktual (kodi) Pozicioni meparshem
(kodi)
   

zgjidhje

select e.employee_id,e.hire_date,e.job_id,
jh.start_date,jh.job_id
from employees e  join job_history jh
on e.employee_id=jh.employee_id;


Te afishohen te gjithe punonjesit.

select e.employee_id,e.hire_date,e.job_id,
jh.start_date,jh.job_id
from employees e left outer  join job_history jh
on e.employee_id=jh.employee_id;




Funksionet e grupit dhe JOIN

Ushtrim 1
a)
Afishoni emrin e cdo departamenti dhe numrin e punonjesve qe ka ai departament

Zgjidhje

select d.department_id,d.department_name, 
count(*) as punonjes
from 
  employees e
  join 
  departments d
  on (e.department_id=d.department_id) 
  group by d.department_id, d.department_name
order by d.department_name;


b)
Afishoni vetem ato departamente te cilet kane me shume se sa 4 punonjes
Zgjidhje

select d.department_id,d.department_name, 
count(*) as punonjes
from 
  employees e
  join 
  departments d
  on (e.department_id=d.department_id) 
  group by d.department_id, d.department_name
having count(*)>4
order by d.department_name;


c)
Afishoni vetem ato departamente te cilet ndodhen ne USA (ku USA ti jepet si argument ne momentin e ekzekutimit te komandes )

Zgjidhje

select d.department_id,d.department_name, 
count(*) as punonjes
from 
  employees e
  join 
  departments d
  on (e.department_id=d.department_id) 
  join locations loc
  on (d.location_id=loc.location_id)
  where loc.country_id='&kodi_shtetit'
  group by d.department_id, d.department_name
having count(*)>4
order by d.department_name;


d)
Afishoni gjithashtu edhe emrin e shtetit ne te cilin ndodhet ky departament

zgjidhje

select d.department_id,d.department_name,ct.country_name, 
count(*) as punonjes
from 
  employees e
  join 
  departments d
  on (e.department_id=d.department_id) 
  join locations loc
  on (d.location_id=loc.location_id)
  join countries ct
  on (ct.country_id=loc.country_id)
  where loc.country_id='&kodi_shtetit'
  group by d.department_id, d.department_name, ct.country_name
having count(*)>4
order by d.department_name;



Ushtrim 2
Afishoni emrin e cdo pozicioni pune dhe numrin e punonjesve qe kane ate pozicion pune

zgjidhje






Ushtrim 3
Ndertoni nje komande qe afishon sa punonjes ka ne sejcilin departament sipas pozicioneve te punes qe ata kane si me poshte:
Te afishohen vetem ato rreshta ku numri i punonjesve eshte me i madh se 4.




Zgjidhje


select
Department_name,job_title,count(*) as Nr
from employees inner  join departments
on employees.department_id=departments.department_id
inner join jobs on employees.job_id=jobs.job_id
group by Department_name, job_title
having count(*)>4;



Ushtrim 4
Afishoni per cdo shtet dhe qytet numrin e departamenteve te ndodhen ne to:




Zgjidhje


select country_name,city,Count(*) as offices
from departments inner join locations on departments.location_id=locations.location_id
inner join countries on locations.country_id=countries.country_id 
group by country_name, city;


Ushtrim ***
Afishoni per cdo shtet numrin e punonjesve qe punojne ne ate shtet si me poshte:




zgjidhje

select ct.country_name,count(e.employee_id)  as no_employees
from employees e join departments d on e.department_id=d.department_id
right outer join locations l on l.location_id=d.location_id
join countries ct on l.country_id=ct.country_id
group by ct.country_name
order by count(e.employee_id) desc;

Self - Join

Ushtrim 1
Per cdo punonjes afishoni te dhenat e tij dhe numrin e punonjesve qe raportojne drejtperdrejt tek ai, pra jane vartesit e tij te drejtperdrejte

zgjidhje

select shefa.employee_id,shefa.first_name,shefa.last_name,
count(*) as vartes
from employees shefa
join employees vartes
on shefa.employee_id =vartes.manager_id 
group by shefa.employee_id, shefa.first_name, shefa.last_name;


(Non Equi Join)

Ushtrim 1

Te afishojme per cdo punonjes emrin,mbiemrin,pagen, si edhe emrin,mbiemrin,pagen e punonjesit qe ka page me te larte.




Zgjidhje

select keqpaguar.first_name,keqpaguar.last_name,keqpaguar.salary,
mirepaguar.first_name,mirepaguar.last_name,mirepaguar.salary
from 
employees keqpaguar
join 
employees mirepaguar
on (keqpaguar.salary<mirepaguar.salary)
order by keqpaguar.salary ;