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

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 ;