Tugas SQL Oracle

NAMA : Ali Zainal Abidin
NIM : 07.41010.0199
==============================================================
PERTANYAAN
Tampilkan nama pegawai, nama department serta nama job nya de-
ngan menggunakan tipe - tipe join yang telah anda pelajari ?

JAWABAN
Untuk menampilkan nama pegawai,nama department serta nama job
nya kita bisa menggunakan beberapa tipe join antara lain:

1.NATURAL JOIN
Perintah atau query yang dapat kita gunakan untuk menampil-
kan field - field tersebut adalah seperti di bawah ini:

-SQL>SELECT FIRST_NAME || ' ' || LAST_NAME NAMA_PEGAWAI
, DEPARTMENT_NAME BAGIAN, JOB_TITLE NAMA_JOB
FROM EMPLOYEES
NATURAL JOIN DEPARTMENTS, JOBS;

oUTPUT NYA :

NAMA_PEGAWAI BAGIAN NAMA_JOB
------------ ----------- ------------------------------
Pat Fay Marketing President
Pat Fay Marketing Administration Vice President
Pat Fay Marketing Administration Assistant
Pat Fay Marketing Finance Manager
Pat Fay Marketing Accountant
Pat Fay Marketing Accounting Manager
Pat Fay Marketing Public Accountant
Pat Fay Marketing Sales Manager
Pat Fay Marketing Sales Representative
Pat Fay Marketing Purchasing Manager
Pat Fay Marketing Purchasing Clerk
Pat Fay Marketing Stock Manager
Pat Fay Marketing Stock Clerk
Pat Fay Marketing Shipping Clerk
Pat Fay Marketing Programmer
Pat Fay Marketing Marketing Manager
Pat Fay Marketing Marketing Representative
Pat Fay Marketing Human Resources Representative
Pat Fay Marketing Public Relations Representative

2889 rows selected.

Perhatikan Tipe NATURAL JOIN diatas hanya akan bekerja optimal
untuk menggabungkan 2 tabel saja,hal ini dapat terlihat apabila
perintah
NATURAL JOIN DEPARTMENTS, JOBS;
kita ganti dengan perintah sebagai berikut:
NATURAL JOIN JOBS, DEPARTMENTS;
maka output yang dihasilkan adalah sebagai berikut:

NAMA_PEGAWAI BAGIAN NAMA_JOB
------------ -------------------- ------------------------
Pat Fay Administration Marketing Representative
Pat Fay Marketing Marketing Representative
Pat Fay Purchasing Marketing Representative
Pat Fay Human Resources Marketing Representative
Pat Fay Shipping Marketing Representative
Pat Fay IT Marketing Representative
Pat Fay Public Relations Marketing Representative
Pat Fay Sales Marketing Representative
Pat Fay Executive Marketing Representative
Pat Fay Finance Marketing Representative
Pat Fay Accounting Marketing Representative
Pat Fay Treasury Marketing Representative
Pat Fay Corporate Tax Marketing Representative
Pat Fay Control And Credit Marketing Representative
Pat Fay Shareholder Services Marketing Representative
Pat Fay Benefits Marketing Representative
Pat Fay Manufacturing Marketing Representative
Pat Fay Construction Marketing Representative
Pat Fay Contracting Marketing Representative
Pat Fay Operations Marketing Representative
Pat Fay IT Support Marketing Representative
Pat Fay NOC Marketing Representative
Pat Fay IT Helpdesk Marketing Representative
Pat Fay Government Sales Marketing Representative
Pat Fay Retail Sales Marketing Representative
Pat Fay Recruiting Marketing Representative
Pat Fay Payroll Marketing Representative

2889 rows selected.

Perintah NATURAL JOIN lain yang dapat kita gunakan adalah seba-
gai berikut :

-SQL>SELECT FIRST_NAME || ' ' || LAST_NAME NAMA_PEGAWAI
, DEPARTMENT_NAME BAGIAN, JOB_TITLE NAMA_JOB
FROM EMPLOYEES
NATURAL JOIN DEPARTMENTS
NATURAL JOIN JOBS;

Output yang dihasilkan dari perintah diatas hanya 32 baris,hal
ini tidak sesuai dengan kenyataan yang kita harapkan. Maka da-
ri itu kita tidak dapat menggunakan NATURAL JOIN untuk meng-
gabungkan 3 Tabel sekaligus.

2. KLAUSA USING
Perintah atau query yang dapat kita gunakan untuk menampil-
kan field - field tersebut adalah seperti di bawah ini:

-SQL>SELECT a.FIRST_NAME || ' ' || a.LAST_NAME NAMA_PEGAWAI
, b.DEPARTMENT_NAME BAGIAN, c.JOB_TITLE NAMA_JOB
FROM EMPLOYEES a
JOIN DEPARTMENTS b
USING (DEPARTMENT_ID)
JOIN JOBS c
USING (JOB_ID);

Perintah di atas apabila kita jalankan akan menghasilkan tabel
sebagai berikut:

NAMA_PEGAWAI BAGIAN NAMA_JOB
--------------- ------------- ------------------
Kelly Chung Shipping Shipping Clerk
Jennifer Dilly Shipping Shipping Clerk
Timothy Gates Shipping Shipping Clerk
Randall Perkins Shipping Shipping Clerk
Sarah Bell Shipping Shipping Clerk
Britney Everett Shipping Shipping Clerk
Samuel McCain Shipping Shipping Clerk
Vance Jones Shipping Shipping Clerk
Alana Walsh Shipping Shipping Clerk
Kevin Feeney Shipping Shipping Clerk

106 rows selected.

3. KLAUSA ON
Perintah atau query yang dapat kita gunakan untuk menampil-
kan field - field tersebut adalah seperti di bawah ini:

-SQL>SELECT a.FIRST_NAME || ' ' || a.LAST_NAME NAMA_PEGAWAI
, b.DEPARTMENT_NAME BAGIAN, c.JOB_TITLE NAMA_JOB
FROM EMPLOYEES a
JOIN DEPARTMENTS b
ON (a.DEPARTMENT_ID=b.DEPARTMENT_ID)
JOIN JOBS c
ON (a.JOB_ID=c.JOB_ID);


NAMA_PEGAWAI BAGIAN NAMA_JOB
--------------- ---------------- -------------------------
Pat Fay Marketing Marketing Representative
Susan Mavris Human Resources Human Resources Representative
Hermann Baer Public Relations Public Relations Representative
Shelley Higgins Accounting Accounting Manager
William Gietz Accounting Public Accountant
Steven King Executive President
Neena Kochhar Executive Administration Vice President
Lex De Haan Executive Administration Vice President
Alexander Hunold IT Programmer
Bruce Ernst IT Programmer
David Austin IT Programmer
Valli Pataballa IT Programmer

106 rows selected.

4. ALIAS NAMA TABEL
Perintah atau query yang dapat kita gunakan untuk menampil-
kan field - field tersebut adalah seperti di bawah ini:

-SQL>SELECT E.FIRST_NAME || ' ' || E.LAST_NAME NAMA_PEGAWAI
, D.DEPARTMENT_NAME BAGIAN, J.JOB_TITLE NAMA_JOB
FROM EMPLOYEES E, DEPARTMENTS D, JOBS J
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.JOB_ID = J.JOB_ID);

Output nya adalah sebagai berikut:

NAMA_PEGAWAI BAGIAN NAMA_JOB
--------------- ------------- ------------------
Kelly Chung Shipping Shipping Clerk
Jennifer Dilly Shipping Shipping Clerk
Timothy Gates Shipping Shipping Clerk
Randall Perkins Shipping Shipping Clerk
Sarah Bell Shipping Shipping Clerk
Britney Everett Shipping Shipping Clerk
Samuel McCain Shipping Shipping Clerk
Vance Jones Shipping Shipping Clerk
Alana Walsh Shipping Shipping Clerk
Kevin Feeney Shipping Shipping Clerk

106 rows selected.