Oracle Database 11g Fundamental SQL (Chapter 8)

Set Operator

SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;

SELECT employee_id, job_id, department_id
FROM employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM job_history
ORDER BY employee_id;

SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;


SELECT employee_id
FROM employees
MINUS
SELECT employee_id
FROM job_history;


SELECT location_id, department_name "Department",
TO_CHAR(NULL) "Warehouse location"
FROM departments
UNION
SELECT location_id, TO_CHAR(NULL) "Department",
state_province
FROM locations;


SELECT employee_id, job_id,salary
FROM employees
UNION
SELECT employee_id, job_id,0
FROM job_history;


SELECT employee_id, job_id,salary
FROM employees
UNION
SELECT employee_id, job_id,0
FROM job_history
ORDER BY 2;

Oracle Database 11g Fundamental SQL (Chapter 7)

SubQuery

Penggunaan subquery single row

SELECT last_name, salary
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE last_name = 'Abel');

Penggunaan subquery multiple row

SELECT last_name, job_id
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 141);

Penggunaan single row sub query
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE last_name = ‘Taylor’)
AND salary > (SELECT salary FROM employees WHERE last_name = ‘Taylor’);

Penggunaaan subquery dengan function

SELECT last_name, job_id, salary
FROM employees WHERE salary = (SELECT MIN(salary) FROM employees);

SELECT department_id, MIN(salary) FROM employees GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50);

SELECT job_id, AVG(salary) FROM employees GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
FROM employees
GROUP BY job_id);

Kasus jika tidak ada data yang dihasilkan dari inner query

SELECT last_name, job_id
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE last_name = 'Haas');


Multiple row subquery
SELECT last_name, salary, department_id
FROM employees
WHERE salary IN (SELECT MIN(salary)
FROM employees
GROUP BY department_id);

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';


SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL (SELECT salary FROM employees WHERE job_id = 'IT_PROG') AND job_id <> 'IT_PROG';

SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id NOT IN
(SELECT mgr.manager_id
FROM employees mgr);

SELECT emp.last_name
FROM employees emp
WHERE emp.employee_id IN
(SELECT mgr.manager_id
FROM employees mgr);

SELECT last_name FROM employees
WHERE employee_id NOT IN
(SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL);

Oracle Database 11g Fundamental SQL (Chapter 6)

Natural join

SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations ;

SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations
WHERE department_id IN (20, 50);

Join table menggunakan using clause

SELECT employee_id, last_name,
location_id, department_id
FROM employees JOIN departments
USING (department_id) ;

Join table dengan menggunakan on clause

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);

SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
AND e.manager_id = 149 ;

SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
WHERE e.manager_id = 149 ;

Self join

SELECT worker.last_name emp, manager.last_name mgr
FROM employees worker JOIN employees manager
ON (worker.manager_id = manager.employee_id);

Nonequijoin

SELECT e.last_name, e.salary, j.grade_level
FROM employees e JOIN job_grades j
ON e.salary
BETWEEN j.lowest_sal AND j.highest_sal;

Left join

SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

Right join

SELECT e.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

Full outer join

SELECT e.last_name, d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

Cross join

SELECT last_name, department_name
FROM employees
CROSS JOIN departments ;

Oracle Database 11g Fundamental SQL (Chapter 5)

Group function

SELECT AVG(salary), MAX(salary),
MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE '%REP%';

SELECT MIN(hire_date), MAX(hire_date)
FROM employees;

SELECT COUNT(*)
FROM employees
WHERE department_id = 50;

SELECT COUNT(commission_pct)
FROM employees
WHERE department_id = 80;

SELECT COUNT(DISTINCT department_id)
FROM employees;

SELECT AVG(commission_pct)
FROM employees;

SELECT AVG(NVL(commission_pct, 0))
FROM employees;

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];

SELECT AVG(salary)
FROM employees
GROUP BY department_id ;

SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id
ORDER BY department_id;

SELECT department_id, COUNT(last_name)
FROM employees;

SELECT department_id, job_id, COUNT(last_name)
FROM employees
GROUP BY department_id;

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000;


SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);

SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;

Oracle Database 11g Fundamental SQL (Chapter 4)

Data conversion
Character ---> number menggunakan to_number
Number ---> character menggunakan to_char

Character ---> date menggunakan to_date
Date ---> character menggunakan to_chat

Rumusnya:
To_char(number,'format_model')
To_char(date, 'format_model')

Merubah format tanggal sesuai dengan yang kita inginkan

SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired
FROM employees
WHERE last_name = 'Higgins';

SELECT last_name,
TO_CHAR(hire_date, 'fmDD Month YYYY')
AS HIREDATE
FROM employees;

Merubah format number sesuai dengan yang kita inginkan

SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM employees
WHERE last_name = 'Ernst';

Merubah format tanggal dalam kalkulasi matematika

SELECT last_name, TO_CHAR(hire_date, 'DD-Mon-YYYY')
FROM employees
WHERE hire_date < TO_DATE('01-Jan-90','DD-Mon-RR');

Penggunaan nesting function

SELECT last_name,
UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US'))
FROM employees
WHERE department_id = 60;

Penggunaan NVL untuk menggantikan nilai NULL dengan suatu karakter / number

SELECT last_name, salary, NVL(commission_pct, 0),
(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
FROM employees;

SELECT last_name, salary, commission_pct,
(salary*12) + (salary*12*commission_pct) AN_SAL
FROM employees;

SELECT last_name, salary, commission_pct,
NVL2(commission_pct,
'SAL+COMM', 'SAL') income
FROM employees WHERE department_id IN (50, 80);

Penggunaan NULLIF, untuk membandingkah dua nilai.
Bila sama akan null dan bisa tidak sama akan di tampilakan
nilai sesuai dgn ekspresi

SELECT first_name, LENGTH(first_name) "expr1",
last_name, LENGTH(last_name) "expr2",
NULLIF(LENGTH(first_name), LENGTH(last_name)) result
FROM employees;

Penggunaan coalesce, untuk membandingkan dua nilai

SELECT last_name, employee_id,
COALESCE(TO_CHAR(commission_pct),TO_CHAR(manager_id),
'No commission and no manager')
FROM employees;

SELECT last_name, salary, commission_pct,
COALESCE((salary+(commission_pct*salary)), salary+2000, salary) "New
Salary"
FROM employees;

Penggunaan kondisi

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;

Oracle Database 11g Fundamental SQL (Chapter 3)

Untuk menampilkan data dengan karakter menjadi lowercase / huruf kecil

SELECT employee_id, last_name, department_id
FROM employees
WHERE LOWER(last_name) = 'higgins';

*** LOWER bisa di ganti dengan UPPER (huruf besar) dan INITCAP(hurud awal kapital dan selanjutnya lowercase)

Untuk menampilkan data dengan karakter tertentu

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';

*** SUBSTR untuk menampikan karatker dr letak huruf dalam kalimat x sampai dengan huruf ke y.
INSTR untuk menghitung letak kedudukan suatu huruf dalam suatu kata
CONCAT untuk menggabungkan 2 kata menjadi 1.
LENGTH untuk menghitung banyaknya hurif dalam satu kata
LPAD untuk menggantilkan null value dengan satu karakter sampai jumlah karakter yang di tentukan (dari kiri)
RPAD untuk menggantilkan null value dengan satu karakter sampai jumlah karakter yang di tentukan (dari kanan)
REPLACE untuk menggantikan satu atau beberapa karakter dengan karakter lain
TRIM untuk menghilangkan satu huruf dari satu kata.

Untuk pembulatan perhitungan

SELECT ROUND(45.923,2), ROUND(45.923,0),
ROUND(45.923,-1)
FROM DUAL;

SELECT TRUNC(45.923,2), TRUNC(45.923),
TRUNC(45.923,-1)
FROM DUAL;

SELECT last_name, salary, MOD(salary, 5000)
FROM employees
WHERE job_id = 'SA_REP';

SELECT last_name, hire_date
FROM employees
WHERE hire_date < '01-FEB-88';

Untuk menampilkan tanggal

SELECT sysdate
FROM dual;

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;

SELECT employee_id, hire_date,
MONTHS_BETWEEN (SYSDATE, hire_date) TENURE,
ADD_MONTHS (hire_date, 6) REVIEW,
NEXT_DAY (hire_date, 'FRIDAY'), LAST_DAY(hire_date)
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 100;

SELECT employee_id, hire_date,
ROUND(hire_date, 'MONTH'), TRUNC(hire_date, 'MONTH')
FROM employees
WHERE hire_date LIKE '%97';

Oracle Database 11g Fundamental SQL (Chapter 2)

Chapter 2

Hanya menampilkan data sesuai dengan kriteria tertentu

SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE department_id = 90 ;

SELECT last_name, job_id, department_id
FROM employees
WHERE last_name = 'Whalen' ;

SELECT last_name
FROM employees
WHERE hire_date = '17-FEB-96' ;

SELECT last_name, salary
FROM employees
WHERE salary <= 3000 ; SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500 ; SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201) ; SELECT first_name FROM employees WHERE first_name LIKE 'S%' ; SELECT last_name FROM employees WHERE last_name LIKE '_o%' ; SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL ; SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000
AND job_id LIKE '%MAN%' ;

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
OR job_id LIKE '%MAN%' ;

SELECT last_name, job_id
FROM employees
WHERE job_id
NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;

SELECT last_name, job_id, salary
FROM employees
WHERE job_id = 'SA_REP'
OR job_id = 'AD_PRES'
AND salary > 15000;

SELECT last_name, job_id, salary
FROM employees
WHERE (job_id = 'SA_REP'
OR job_id = 'AD_PRES')
AND salary > 15000;

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date ;

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY hire_date DESC ;

SELECT employee_id, last_name, salary*12 annsal
FROM employees
ORDER BY annsal ;

SELECT last_name, job_id, department_id, hire_date
FROM employees
ORDER BY 3;

SELECT last_name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;


Hanya menampilkan data tertentu dengan kriteria yang bisa di tentukan secara dinamis

SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num ;


SELECT last_name, department_id, salary*12
FROM employees
WHERE job_id = '&job_title' ;


SELECT employee_id, last_name, job_id,&column_name
FROM employees
WHERE &condition
ORDER BY &order_column ;


SELECT employee_id, last_name, job_id, &&column_name
FROM employees
ORDER BY &column_name ;

Menggunakan parameter verify

SET VERIFY ON
SELECT employee_id, last_name, salary
FROM employees
WHERE employee_id = &employee_num;

SET VERIFY ON
SELECT employee_id, last_name, salary
FROM employees
WHERE employee_id = &employee_num;

Oracle Database 11g Fundamental SQL (Chapter 1)

Jenis-jenis perintah dalam SQL

Data manipulation language (DML)
selec, insert, update, delete merge

Data definition language (DDL)
create, alter, drop, rename, truncate, comment

Priviledge
grant, revoke,

Management perubahan dari pernyataan DML
commit, rollback, savepoint

Dasar-dasar SQL

Untuk melihat kolom dari suatu table

--->DESC[RIBE] tablename

Untuk melihat isi data dari suatu table

--->SELECT * FROM departments;

Untuk melihat data sesuai dengan kolom yang kita inginkan

--->SELECT department_id, location_id FROM departments;
--->SELECT last_name, hire_date, salary FROM employees;

Untuk melihat data sesuai dengan hasil manipulasi kolom

--->SELECT last_name, salary, salary + 300 FROM employees;
--->SELECT last_name, salary, 12*salary+100 FROM employees;
--->SELECT last_name, salary, 12*(salary+100) FROM employees;
--->SELECT last_name, 12*salary*commission_pct FROM employees;

Untuk melihat data dengan nama kolom alias

--->SELECT last_name AS name, commission_pct comm FROM employees;

Untuk melihat data yang telah dimanipulasi dengan nama kolom alias

--->SELECT last_name "Name" , salary*12 "Annual Salary" FROM employees;

Untuk menggabungkan dua kolom menjadi satu kolom dengan nama kolom alias

--->SELECT last_name||job_id AS "Employees" FROM employees;

--->SELECT last_name ||' is a '||job_id AS "Employee Details" FROM employees;

--->SELECT last_name ||': 1 Month salary = '||salary Monthly FROM employees;

--->SELECT department_name || ' Department' ||q'['s Manager Id: ]'|| manager_id AS "Department nd Manager" FROM departments;

Untuk mensortir hanya menampilkan satu data yang berbeda
--->SELECT DISTINCT department_id FROM employees;