--tut3

--1
select sysdate,user
from dual

--2
select e.employee_id,e.first_name,e.last_name,e.salary,to_number(TO_CHAR(e.salary*1.25,'99,999.99'),'99,999.99')
from employees e
--3
select e.employee_id,e.first_name,e.last_name,e.salary,round(e.salary*1.25412,2),SUBSTR(cast(round(e.salary*1.25412,2) as numeric(7,2)),LENGTH(round(e.salary*1.25412,2))-1,LENGTH(round(e.salary*1.25412,2)))
from employees e
--4
select concat(e.first_name,e.last_name),e.hire_date,to_char(next_day(ADD_MONTHS(e.hire_date, 6),'MONDAY'),'dd-mon,yyyy') as datum
from employees e

--5
select concat(concat(e.first_name,' '),e.last_name) as "Ime",e.department_id,r.region_name,floor(MONTHS_BETWEEN(sysdate,e.hire_date)) as "Broj mjeseci zaposljenja"
from employees e,regions r,locations l,countries c,departments d
where e.department_id=d.department_id and d.location_id=l.location_id and l.country_id=c.country_id and c.region_id=r.region_id
order by e.department_id asc

--6
select e.first_name,e.last_name,e.salary,(e.salary*(1+NVL(e.commission_pct,0)))*4.5
from employees e
where e.department_id in(10,30,50)

--7
select LPAD(e.first_name||' '||e.last_name||' + '||e.salary, 50,'$')
from employees e

--8
select lower(substr(e.first_name||' '||e.last_name,1,1))||upper(substr(e.first_name||' '||e.last_name,2)),LENGTH(e.first_name||' '||e.last_name)
from employees e
where e.first_name like 'A%' or e.first_name like 'M%' or e.first_name like 'J%' or e.first_name like '%S'

--9
select e.first_name,e.hire_date,TO_CHAR(e.hire_date,'day')
from employees e
order by  (next_day(e.hire_date,'MONDAY')-e.hire_date) desc

--10
select e.first_name,e.last_name ,l.city,nvl(to_char(e.commission_pct),'nema dodatak na platu')
from employees e,departments d,locations l
where e.department_id=d.department_id and d.location_id=l.location_id

--11
select e.first_name,e.last_name,e.salary,rpad('*',round(e.salary/1000),'*')
from employees e

--12
select e.first_name,e.last_name,j.job_title,
decode(job_title,'President','A','Manager','B','X')
from employees e,jobs j
where e.job_id=j.job_id

--tut4

--1
select sum(e.commission_pct),count(e.commission_pct),count(*)
from employees e

--2
select j.job_title,d.department_name,count(*)
from employees e,departments d,jobs j
where e.department_id=d.department_id and e.job_id=j.job_id
group by j.job_title,d.department_name

--3
select max(e.salary),min(e.salary),sum(e.salary),round(avg(e.salary),6)
from employees e

--4
select j.job_title,max(e.salary),min(e.salary),sum(e.salary),round(avg(e.salary),6)
from employees e,jobs j
where e.job_id=j.job_id
GROUP BY j.job_title

--5
select j.job_title,count(*)
from employees e,jobs j
where e.job_id=j.job_id
GROUP BY j.job_title

--6
select j.job_title,count(*)
from employees e,jobs j
where e.job_id=j.job_id and j.job_title like '%Manager'
GROUP BY j.job_title

--7
select DISTINCT m.first_name||' '||m.last_name,m.salary
from employees e,employees m,departments d
where e.manager_id=m.employee_id and m.department_id=d.department_id
and m.salary<(SELECT min(m1.salary) from employees e1,employees m1
where m1.employee_id=e1.manager_id and m1.department_id<>m.department_id)

--8
select d.department_name,l.city,count(e.employee_id),round(avg(e.salary))
from employees e,locations l,departments d
where e.department_id=d.department_id and d.location_id=l.location_id
group by d.department_name,l.city

--9
select sum(decode(TO_NUMBER(to_char(e.hire_date,'yyyy')),2002,count(e.employee_id),0))  as "2002g",
sum(decode(TO_NUMBER(to_char(e.hire_date,'yyyy')),2003,count(e.employee_id),0))  as "2003g",
sum(decode(TO_NUMBER(to_char(e.hire_date,'yyyy')),2004,count(e.employee_id),0))  as "2004g",
sum(decode(TO_NUMBER(to_char(e.hire_date,'yyyy')),2005,count(e.employee_id),0))  as "2005g",
sum(decode(TO_NUMBER(to_char(e.hire_date,'yyyy')),2002,count(e.employee_id),
																									2003,count(e.employee_id),
																									2004,count(e.employee_id),
																									2005,count(e.employee_id),0)) as "ukupno"

from employees e
group by e.hire_date

--10
select j.job_title,
decode(e.department_id,10,sum(e.salary),0) as "Odjel 10",
decode(e.department_id,30,sum(e.salary),0) as "Odjel 30",
decode(e.department_id,50,sum(e.salary),0) as "Odjel 50",
decode(e.department_id,90,sum(e.salary),0) as "Odjel 90",
decode(e.department_id,10,sum(e.salary),
											 30,sum(e.salary),
											 50,sum(e.salary),
											 90,sum(e.salary),0) as "Ukupno"
from employees e,jobs j
where e.job_id=j.job_id
GROUP by j.job_title,e.department_id