Contents
select employee_id,min(start_date)
from job_history group by employee_id
order by employee_id;
select department_id,count(*) as punonjes
from employees group by department_id;
select job_id,count(*) as punonjes
from employees group by job_id ;
select city,count(*) as zyra
from locations group by city;
select country_id,city,count(*) as zyra
from locations group by country_id, city;
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;
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;
from employees e left outer join job_history jh
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;
having count(*)>4
join locations loc
on (d.location_id=loc.location_id)
where loc.country_id='&kodi_shtetit'
select d.department_id,d.department_name,ct.country_name,
join countries ct
on (ct.country_id=loc.country_id)
group by d.department_id, d.department_name, ct.country_name
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;
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;
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;
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;
select keqpaguar.first_name,keqpaguar.last_name,keqpaguar.salary,
mirepaguar.first_name,mirepaguar.last_name,mirepaguar.salary
employees keqpaguar
employees mirepaguar
on (keqpaguar.salary<mirepaguar.salary)
order by keqpaguar.salary
;