Pertemuan 3

SELECT concat('Good',' string') FROM dual;

SELECT substr('string',1,3) FROM dual;

SELECT length('string') FROM dual;

SELECT instr ('string','r') FROM dual;

SELECT LPAD (Salary,10,'*') FROM employees;

SELECT RPAD (Salary,10,'*') FROM employees;

SELECT employee_id, CONCAT(first_name, last_name) NAME,

               job_id, LENGTH (last_name),

               INSTR (last_name, 'a') "contains 'a'?"

FROM employees

WHERE SUBSTR (job_id, 4) = 'REP';

SELECT ROUND (45.923,2), ROUND (45.923),

                ROUND (45.923,-2)

FROM DUAL;

SELECT TRUNC (45.923,2), TRUNC (45.923),

                TRUNC (45.923,-2)

FROM DUAL;

SELECT last_name, salary, MOD(salary, 5000)

FROM employees

WHERE job_id = 'SA_REP';

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS

FROM employees

WHERE department_id = 90;

SELECT last_name,

               TO_CHAR(hire_date, 'fmDD Month YYYY')

               AS HIREDATE

FROM employees;

SELECT TO_CHAR(salary, '$99,999.00') SALARY

FROM employees

WHERE last_name = 'Ernst';

SELECT last_name, job_id, salary,

               CASE job_id WHEN 'IT_PROG' THEN 1.10*salary

                                     WHEN 'ST_CLERK' THEN 1.15*salary

                                     WHEN 'SA_REP' THEN 1.20*salary

                ELSE salary END      "REVISED_SALARY"

FROM employees;

SELECT last_name, job_id, salary,

                DECODE(job_id, 'IT_PROG', 1.10*salary,

                                             'ST_CLERK', 1.15*salary,

                                             'SA_REP', 1.20*salary,

                                  salary)

                 REVISED_SALARY

FROM employees;

LATIHAN PERTEMUAN 3

SELECT last_name, (SYSDATE-hire_date)/366 AS "Lama bekerja"

          FROM employees

WHERE department_id = 90

HAVING (SYSDATE-hire_date)/366 > 10

GROUP BY last_name, hire_date;