-- JAWABAN TTS BDL Genap 0910
-- --------------------------

-- 1
SELECT TO_CHAR(sysdate,'Day, MONTH DDTH YYYY, "Century" CC, "Time" HH12:MI:SS AM')
FROM dual;

-- 2
SELECT employee_id "Id#",
       last_name||', '||first_name "Nama",
       floor(months_between(sysdate,hire_date)/12)
           ||' tahun '|| 
           MOD(floor(months_between(sysdate,hire_date)),floor(months_between(sysdate,hire_date)/12))
           ||' bulan ' "Masa Kerja",
       TO_CHAR(salary * floor(months_between(sysdate,hire_date)),'$999,999,999,999.99') "Total Pendapatan"
FROM employees;

-- 3
SELECT employee_id "Id#",
       last_name||', '||first_name "Nama",
       floor(months_between(sysdate,hire_date)/12)
           ||' tahun '|| 
           MOD(floor(months_between(sysdate,hire_date)),floor(months_between(sysdate,hire_date)/12))
           ||' bulan ' "Masa Kerja",
       TO_CHAR(salary * floor(months_between(sysdate,hire_date)),'$999,999,999,999.99') "Total Pendapatan"
FROM employees
ORDER BY hire_date DESC;

-- 4
SELECT	employee_id, last_name, 
    	salary, commission_pct, salary+(salary*NVL(commission_pct,0)) AS "GAJI BERSIH"
FROM	employees;

-- 5a
SELECT d.department_id "Department ID",d.department_name "Department Name",
       NVL(m.last_name,'Tidak Ada Manager') "Manager Name",
       l.street_address||', '||l.city||', '||l.postal_code "Department Address"
FROM departments d, employees m, locations l
WHERE d.manager_id = m.employee_id (+)
      AND d.location_id = l.location_id;

-- atau --

SELECT d.department_id "Department ID",d.department_name "Department Name",
       NVL2(m.last_name,m.last_name||', '||m.first_name,'Tidak Ada Manager') "Manager Name",
       l.street_address||', '||l.city||', '||l.postal_code "Department Address"
FROM departments d, employees m, locations l
WHERE d.manager_id = m.employee_id (+)
      AND d.location_id = l.location_id;


-- 5b
SELECT first_name, salary "Old Salary", 
       CASE TO_CHAR(hire_date,'YYYY')
              WHEN '1996' THEN salary+(salary*0.4)
              WHEN '1997' THEN salary+(salary*0.3)
              WHEN '1998' THEN salary+(salary*0.2)
              ELSE salary+(salary*0.15) 
      END "New Salary"
FROM employees
WHERE TO_CHAR(hire_date,'YYYY') >= '1996';

-- 6
SELECT d.department_id "Department ID",
       d.department_name "Department Name",
       e.last_name||', '||e.first_name "Employee Name",
       e.salary "Salary"
FROM departments d, employees E
WHERE d.department_id = e.department_id;

-- 7
BREAK ON "Departmment ID" ACROSS "Department Name" SKIP 1 ON "Employee Name" SKIP 1 DUPLICATES
COMPUTE SUM OF Salary ON "Departmment ID"
SELECT d.department_id "Departmment ID",
       d.department_name "Department Name",
       e.last_name||', '||e.first_name "Employee Name",
       e.salary "Salary"
FROM departments d, employees E
WHERE d.department_id = e.department_id;

-- 8
BREAK ON "Departmment ID" ACROSS "Department Name" SKIP 1 ON "Employee Name" SKIP 1 DUPLICATES
COMPUTE SUM OF Salary ON "Departmment ID"
SELECT d.department_id "Departmment ID",
       d.department_name "Department Name",
       e.last_name||', '||e.first_name "Employee Name",
       e.salary "Salary"
FROM departments d, employees E
WHERE d.department_id = e.department_id
      AND d.department_id = &deptID;