panduan sql

Laboratorium Database of 55

Panduan Praktikum

Basis Data &

Sistem Basis Data

( ORACLE SQL*Plus 3.3 )

By David 1


Daftar isi……………………………..

Daftar isi ………………………………………………………….. 2

Konsep Dasar Basis DataPengertian SQL, perintah – perintah umum SQL…………..3

ORACLE SQL*Plus 3.3 SELECT …………………………………………… 4 Operasi select berdasar kondisi tertentu…………… 11 Nesting function…………………………………… 14 Query dengan multi table ( join ) ……………………19 Group function……………………………………… 22 Subquery…………………………………………… 25 Menspesifikasikan variabel pada saat Runtime …… 28 CREATE TABLE………………………………… 33 Oracle data dictionary……………………………… 41 Memanipulasi data dengan DML ……………………43 Alter table dan Constraint…………………………… 48

By David 2


Panduan Praktikum Basis Data & Sistem Basis Data

Konsep Dasar Basis Data

Basis Data / Database :Adalah kumpulan dari data/table yang terelasi atau saling berhubungan, dimana

database tersebut dapat terdiri dari satu table atau lebih. Table / entity

Sekelompok record data, terdiri dari data/informasi yang sejenis Baris / Record / Tuple / row

Kumpulan dari beberapa item/field yang memberikan informasi yang bermanfaat.

Kolom / Field / atribut / columnSebuah item data yang menyusun record / baris / tuple

Primary keyAdalah suatu atribut atau satu set minimal atribut yang mengidentifikasikan secara unique antra record satu dengan record yang lainnya dan juga merupakan suatu atribut yang mewakili setiap kejadian dari entity

Foreign keyAdalah suatu atribut atau satu set atribut yang melengkapi suatu relationship dimana atribut tersebut mengacu ke sebuah primary key pada base table ( Induk )

ConstraintAdalah suatu aturan – aturan (batasan) yang berlaku terhadap suatu atribut atau entity

DBMS (Database Manajemen System) :Adalah sistem software yang multiguna, yang menyediakan fasilitas untuk

mendefinisikan, membangun, dan memanipulasi basis data untuk aplikasi yang beraneka ragam.

Pengertian SQLAdalah bahasa standar yang digunakan untuk memanipulasi database relational

Perintah – Perintah SQL secara umumDDL ( Data Definitiom Language )

Digunakan untuk menspesifikasikan skema konseptualContoh : Create Drop Alter

By David 3


DML ( Data Manipulation Language )Digunakan untuk melakukan manipulasi data ( setelah dilakukan proses kompilasi

skema konseptual), contoh : Insert Update Delete

ORACLE / SQL *Plus 3.3Adalah tool yang dimiliki oleh Oracle yang digunakan untuk melakukan /

mengeksekusi statement SQL dan PL / SQL

PERINTAH SELECT

Penggunaan perintah SELECT sederhana

Syntax

SELECT [ DISTINCT ] { *, Column [ Alias ], … }FROM [ Table ];

Contoh

1. Menampilkan semua baris dan column (semua data)

Dapatkan semua data yang terdapat dalam table s_dept

SQL>SELECT * 1 FROM s_dept ;

Hasilnya

ID NAME REGION_ID

10 Finance 131 Sales 132 Sales 241 Operations 150 Administration 1

By David 4


Untuk menampilkan “Struktur table” digunakan perintah sebagai berikut :

SQL>DESCRIBE Table_name ;

Atau

SQL>DESC Table_name ;

Contoh

Tampilkan struktur table dari Table s_dept ;

SQL>DESCRIBE s_dept;AtauSQL>DESC s_dept ;

Hasilnya

Name Null? Type .

ID NOT NULL NUMBER(7)NAME NOT NULL VARCHAR2(25)REGION_ID NUMBER(7)

2. Menampilkan semua baris dengan memilih kolom yang spesifik / khusus

Contoh

Dapatkan semua nomor departemen (dept_id), nama pegawai (last_name), dan kode manager (manager_id) dari table s_dept !

SQL>SELECT dept_id , last_name , manager_id 1 FROM s_emp ;

DEPT_ID LAST_NAME MANAGER_ID

50 Velasquez41 Ngao 131 Nagayama 110 Quick-To-See 150 Ropeburn 141 Urguhart 2…

By David 5


3. Melakukan operasi aritmatika pada column

Operasi aritmatika dapat dilakukan untuk colum yang memiliki type data numerik. Operator yang dapat digunakan untuk melakukan operasi tersebut adalah “ +, -, *, / “

Contoh

SQL>SELECT last_name, salary, 12*salary+100 1 FROM s_emp ;

Hasil dari query tersebut akan menampilkan nama pegawai, gaji, dan colum gaji yang telah dikenai operasi aritmatika dimana data yang ditampilkan adalah gaji pegawai dikalikan 12 lalu ditambahkan dengan 100, sehingga hasil dari query tersebut dapat dilihat sebagai berikut :

LAST_NAME SALARY 12 * SALARY + 100

Velasquez 2500 30100 Ngao 1450 17500 Nagayama 1400 16900 …

4. Menggunakan Alias untuk mengganti nama column pada saat query dijalankan

Secara default, nama column yang ditampilkan pada saat kita melakukan operasi SELECT akan sama dengan apa yang kita tuliskan dalam query, jika diinginkan untuk merubah tampilan nama column agar lebih mudah dipahami, maka column dapat ditampilkan dengan menggunakan nama lain (alias) begitu juga pada table.

Contoh

SQL>SELECT Em.last_name,Em. salary, 2 12*Em.salary+100 AS Annual_Salary

3 FROM s_emp Em ;

atau

SQL>SELECT Em.last_name, Em.salary, 12*Em.salary+100 Annual_Salary 2 FROM s_emp Em;

LAST_NAME SALARY Annual_Salary

By David 6


Velasquez 2500 30100 Ngao 1450 17500 Nagayama 1400 16900 …

Perhatikan bahwa pada column yang menampilkan 12*salary+100 akan ditampilkan dengan nama alias Annual_Salary seprti pada hasil query di atas, sedangkan untuk nama table memiliki nama alias Em. coba bandingkan dengan contoh yang sama sebelumnya.

Jika nama alias yang akan ditampilkan terdiri dari beberapa kata, maka harus ditulis dengan diapit oleh tanda “”

ContohSQL>SELECT last_name, salary, 12*salary+100 AS “Annual Salary” 2 FROM s_emp ;

5. Operator penggabungan ( | | )

Operator penggabungan digunakan untuk menggabungkan beberapa column menjadi sebuah column. Penggabungan ini menggunakan operator | |

Contoh

SQL>SELECT first_name | | ‘ ‘ | | last_name | | ‘, ‘ | | title Pegawai, 2 salary Gaji

3 FROM s_emp ;

PEGAWAI GAJI

Carmen Velasquez, President 2500LaDoris Ngao, VP, Operations 1450Midori Nagayama, VP, Sales 1400Mark Quick-To-See, VP, Finance 1450…

6. Menangani column yang memiliki nilai NULL

Jika suatu column tidak memilik nilai, maka column tersebut disebut sebagai NULL, atau column yang mengandung NULL. NULL tidak sama dengan 0, dan juga tidak sama dengan spasi karena 0 adalah angka dan spasi adalah karakter.

Column NULL tersebut juga dapat dikenai operasi aritmatika seperti layaknya column yang memiliki nilai numerik

By David 7


SQL>SELECT last_name, title, salary, salary* commission_pct / 100 Comm 2 FROM s_emp ;

LAST_NAME TITLE COMM

…Havel Warehouse Manager Magee Sales Representative 140Giljum Sales Representative 186.25Sedeghi Sales Representative 151.5Nguyen Sales Representative 228.75Dumas Sales Representative 253.75Madoro Stock Clerk…

Fungsi NVL()Fungsi NVL() digunakan untuk merubah column yang mengandung nilai

NULL sesuai dengan argumen yang dikehendaki. Perubahan ini hanya bersifat sementaradan tidak permanen, dengan kata lain perubahan ini hanya tampak pada saat query dijalankan.

Contoh

SQL>SELECT last_name, salary, salary* NVL(commission_pct,0) / 100 2 COMM 3 FROM s_emp ;

LAST_NAME TITLE COMM

…Havel Warehouse Manager 0Magee Sales Representative 140Giljum Sales Representative 186.25Sedeghi Sales Representative 151.5Nguyen Sales Representative 228.75Dumas Sales Representative 253.75Madoro Stock Clerk 0…

Numerik NVL(number,9)Date NVL(date_column,’01-JAN-98’)Char / Varchar2 NVL(character_column,’Unavailable’)

By David 8


7. Penggunanan keyword DISTINCT ( Data ditampilkan secara Unique )

SQL>SELECT salary 2 FROM s_emp ;

SALARY

2500 1450 1400 1450

…

Secara default jika kita menjalankan query di atas, maka data dari gaji (salary) akan ditampilkan semuanya ( ditampilkan dengan tidak unique ). Tetapi jika diinginkan untuk ditampilkan secara unique, maka dapat dilakukan dengan menggunakan perintah DISTINCT seperti pada contoh di bawah ini

SQL>SELECT DISTINC salary

2 FROM s_emp ;

SALARY

750 795 800 850

…

Perintah DISTINCT juga otomatis akan mengurutkan data yang ditampilkan secara Ascending

DISTINCT dengan multi columnUntuk penggunaan DISTINCT yang melibatkan banyak column, maka

data yang ditampilkan adalah data unique dari kombinasi column – column yang terdapat dalam query

Contoh:SQL>SELECT DISTINC dept_id, title

By David 9


2 FROM s_emp ;

DEPT_ID TITLE

…41 Stock Clerk41 VP, Operations41 Warehouse Manager42 Stock Clerk42 Warehouse Manager43 Stock Clerk

…

8. SQL *Plus Editing Commands

A[PPEND] : Menambahkan text di akhir baris dari suatu query (SQL Buffer) L[IST] : Menampilkan semua query yang aktif di SQL Buffer R[UN] : Menjalankan query yang aktif di SQL Buffer

9. SQL * Plus File Command

SAVE : Digunakan untuk menyimpan query ke dalam Disk ( MediaPenyimpanan)

GET : Mengambil query yang ada di media penyimpanan dan mengaktifkannya kedalam SQL Buffer

START : Menjalankan query yang ada di media penyimpanan @file_name : Sama dengan perintah START EDIT : Mengoperasikan text editor SQL Buffer EXIT : Keluar dari SQL

10. Mengatur format tampilan dengan COLUMNSintax

COL[UMN] [{column_name|alias} {option …}]

Option – option yang tersedia CLE[AR] : Menghilangkan efek dari Column Format FOR[MAT] : Merubah fornat tampilan suatu column HEA[DING] text : Mengatur column heading JUS[TIFY] : Mengatur justify dari column heading NOPRI[NT] : Menghide column NUL[L] text : Mengganti dengan text pada column yang NULL PRI[N] : Menampilkan column

By David 10


Contoh

COLUMN last_name HEADING ‘Emloyee | Name’ FORMAT A15COLUMN salary JUSTIFY LEFT FORMAT $99,999.99COLUMN start_date FORMAT A9 NULL ‘Not hired’

Operasi SELECT dengan menggunakan batasan / kondisi tertentu

1. Operasi SELECT dengan menggunakan ORDER BYPenggunaan ORDER BY pada operasi SELECT akan mengakibatkan data

yang ditampilkan akan berurut sesuai dengan column yang ditentukan. Berbeda dengan perintah DISTINCT dimana dalam penggunaan ORDER BY semua data akan ditampilkan walaupun data tersebut sudah pernah ditampilkan sebelumnya

SyntaxSELECT column_nameFROM table_name[ORDER BY {column,expr} [ASC|DESC]];

Contoh

SELECT last_name, dept_id, start_dateFROM s_empORDER BY last_name;

Hasilnya

LAST_NAME DEPT_ID START_DATE Biri 43 07-APR-90Catchpole 44 09-FEB-92Chang 44 30-NOV-90Dancs 45 17-MAR-91Dumas 35 09-OCT-91Giljum 32 18-JAN-92Havel 45 27-FEB-91

Secara default data yang ditampilkan dengan ORDER BY akan terurut secara ASCENDING, jika diinginkan untuk menampilkan data yang terurut secara DESCENDING maka dapat digunakan perintah DESC mengikuti perintah ORDER BY seperti yang dapat dilihat pada syntax di atas

Seperti juga pada DISTINCT, ORDER BY juga dapat menampilkan data secara terurut berdasarkan lebih dari satu column seperti yang dapat dilihat pada contoh di bawah ini

By David 11


SELECT last_name, dept_id, salaryFROM s_empORDER BY dept_id, salary;

Hasil

LAST_NAME DEPT_ID SALARY Quick-To-See 10 1450Nagayama 31 1400Magee 31 1400Giljum 32 1490Sedeghi 33 1515Nguyen 34 1525Patel 34 795…

2. Operasi SELECT dengan menggunakan WHEREPenggunaan WHERE dalam operasi SELECT akan mengakibatkan data

yang ditampilkan adalah hanya data – data tertentu yang memenuhi kondisi yang disyaratkan dalam WHERE.

Contoh

SELECT first_name, last_name, titleFROM s_empWHERE last_name = ‘Magee’;

Hasil

FIRST_NAME LAST_NAME TITLE Colin Magee Sales Representative

Operator – operator yang digunakan = : WHERE dept_id = 41; < : WHERE dept_id < 41; > : WHERE dept_id > 41; <= / >=

SQL operator BETWEEN … AND …

Digunakan untuk menampilkan data yang memiliki kondisi column yang nilainya berada diantara range yang ditentukanContoh

By David 12


SELECT * FROM s_emp WHERE dept_id BETWEEN 34 AND 41;

INDigunakan untuk menapilkan data yang nilai columnnya berada dalam

daftar / list yang ditentukanContohSELECT * FROM s_emp WHERE region_id IN (1,3);

LIKEDigunakan untuk menampilkan data yang nilai columnya memiliki

karakter/kata/bagian yang menyerupai kondisi yang ditetapkan.ContohSELECT last_name FROM s_empWHERE last_name LIKE ‘M%’;

Maksud dari query di atas adalah ingin ditampilkan data Employee yang memiliki nama depan yamg huruf depannya adalah ‘M’ dengan huruf lainnya sembarang ( tanda % mewakili sederetan karakter sedangkan tanda _ mewakili sebuah karakter )

IS NULLDigunakan untuk menampilkan data – data yang memiliki column yang

nilainya adalah NULLContohSELECT * FROM s_empWHERE commission_pct IS NULL;

Logical Operator AND

Jika kedua kondisi terpenuhi, maka data tersebut akan ditampilkanContohSELECT last_name, salary, dept_id, titleFROM s_empWHERE dept_id = 41AND title = ‘Stock Clerk’;

ORJika salah satu kondisi dipenuhi, maka data tersebut akan ditampilkan

ContohSELECT last_name, salary, dept_id, titleFROM s_empWHERE dept_id = 41OR title = ‘Stock Clerk’;

NOTAkan menampilkan data yang nilainya tidak sama dengan kondisi yang

diinginkanContohSELECT last_name, salary, dept_id, title

By David 13


FROM s_empWHERE NOT dept_id = 41

Operasi SELECT yang menggunakan Manipulation Function

1. Character Function LOWER

Fungsi ini digunakan untuk merubah nilai dari column menjadi huruf kecil (lowercase)

UPPERFungsi ini digunakan untuk merubah nilai dari column menjadi huruf

kapital (Uppercase) INITCAP

Fungsi ini digunakan untuk merubah huruf hanya awal dari nilai column menjadi huruf kapital

Contoh

SELECT LOWER(first_name | | ‘ ‘ | | last_name) VP,INITCAP(userid) USERID,UPPER(title) TITLE

FROM s_empWHERE UPPER(title) LIKE ‘VP%’;

Hasil

VP USERID TITLE ladoris ngao Lngao VP, OPERATIONSmidori nagayama Mnagayam VP, SALESmark quick-to-see Mquickto VP, FINANCEaudry ropeburn Aropebur VP, ADMINISTRATOR

CONCATDigunakan untuk menggabungkan nilai dari dua buah column

SUBSTRDigunakan untuk mengambil beberapa karakter tertentu dari suatu colum

value LENGTH

Digunakan untuk menghitung panjang dari suatu karakter

ContohSELECT CONCAT(name, country) Customer,

LENGTH(name) Length_Name,SUBSTR(name,1,3) Kode

By David 14


FROM s_customerWHERE credit_rating = ‘GOOD’;

CUSTOMER LENGTH_NAME KODE Delhi SportsIndia 12 DelSweet RockNigeria 10 Swe

2. Numeric Function ROUND

Digunakan untuk membulatkan suatu nilai pecahan, dimana aturan pembulatannya adalah jika nilai kurang dari lima maka akan dibulatkan ke bawah, jika nilai lebih atau sama dengan lima maka akan dibulatkan ke atas

TRUNCDigunakan untuk membulatan suatu nilai pecahan, dimana aturan

pembulatannya adalah selalu dilakukan pembulatan ke bawah. MOD

Digunakan untuk menampilkan sisa dari suatu proses pembagian

ContohSELECT ROUND(45.923,2), ROUND(45.293,0), ROUND(45.293,-1),

TRUNC(45.293,2), TRUNC(45.293), TRUNC(45.293,-1),MOD(1600,300)

FROM SYS.DUAL;Hasil

ROUND(45.293,2) ROUND(45.293,0) ROUND(45.293,-1)45.92 46 50

TRUNC(45.293,2) TRUNC(45.293) TRUNC(45.293,-1) MOD(1600,300)45.92 45 40 100

SYS.DUAL adalah sebuah table dummy, yaitu sebuah table yang dimiliki oleh SYS yang hanya terdiri dari satu column dengan nilai ‘X’. Dual ini berguna pada saat kita menginginkan pengembalian suatu nilai yang tidak terdapat dalam data user.

3. Date format Menampilkan tanggal system

ContohSELECT SYSDATE FROM SYS.DUAL;

SYSDATE adalah sebuah fungsi yang digunakan untuk mengambil tanggal dan jam (waktu) sistem

Melakukan operasi matematika terhadap tanggal

By David 15


Operasi – operasi matematika dapat juga ,dilakukan terhadap data yang bertype tanggal

ContohSELECT last_name, (sysdate – start_date) / 7 WeeksFROM s_empWHERE dept_id = 43;

Hasil

LAST_NAME WEEKS Biri 297.226498Markarian 238.083641Newman 230.083641

4. Date Function MONTHS_BETWEEN(date1, date2)

Menampilkan jumlah bulan yang berada dalam selang waktu yang ditentukan.

ADD_MONTHS(date, n)Menjumlahkan tanggal dengan sebuah angka n, dimana n dapat bernilai

positif dan atau n dapat bernilai negatif

ContohSELECT id, start_date,

MONTHS_BETWEEN(sysdate, start_date) Tenure,ADD_MONTHS(start_date, 6) Review

FROM s_empWHERE MONTHS_BETWEEN(sysdate, start_date) < 48;

NEXT_DAY(date,’char’)Mendapatkan tanggal dari hari yang dinginkan dimana tanggal yang

ditampilkan adalah berdasarkan hari spesifik yang pertama kali ditemukan setelah hari ini

LAST_DAY(date)Digunakan untuk mendapatkan tanggal dari hari terakhir pada bulan yang

diinginkan berdasarkan inputan tanggal (date)

Contoh

SELECT product_id,

By David 16


NEXT_DAY(restock_date,’FRIDAY’),LAST_DAY(restock_date)

FROM s_inventoryWHERE restock_date IS NOT NULLORDER BY restock_date;

Hasil

PRODUCT_ID NEXT_DAY( LASTDAY( 30433 11-SEP-92 30-SEP-9240422 12-FEB-93 28-FEB-9350532 16-APR-93 30-APR-93

ROUND(date,’fmt’)Digunakan untuk membulatkan tanggal berdasarkan format BULAN atau

TAHUN, aturan pembulatannya sama dengan aturan pembulatan pada Numerik value

TRUNC(date,’fmt’)Digunakan untuk membulatkan tanggal berdasarkan format BULAN atau

ANGKA, aturan pembulatan selalu dilakukan dengan membulatkan ke bawah.

ContohSELECT ROUND(‘25-MAY-95’,’MONTH’),

ROUND(‘25-MAY-95’,’MONTH’),TRUNC(‘25-MAY-95’,’MONTH’),TRUNC(‘25-MAY-95’,’MONTH’)

FROM sys.dual;

HasilROUND(’2 ROUND(‘2 TRUNC(‘25 TRUNC(‘2 01-JUN-95 01-JAN-95 01-MAY-95 01-JAN-95

5. Convesion Function TO_CHAR(number/date, [‘fmt’])

Mengkonversi data numerik atau date menjadi data karakter (VARCHAR2) dengan format model ‘fmt’.

ContohSELECT id, TO_CHAR(date_ordered,’MM/YY’)FROM s_ordWHERE sales_rep_id = 11;

Untuk format penulisan tanggal selain format seperti pada contoh di atas juga terdapat format – format sebagai berikut :

SCC atau CC : Menampilkan abad dari tanggal yang diinputkan

By David 17


YYYY : Menampilkan tahun dari tanggal yang diinputkan sebanyak 4 digit

YY : Sama dengan YYYY tetapi ditampilkan dengan 2 digit YEAR : Menampilkan ejaan tahun dari tanggal yang diinputkan Q : Menyatakan qurtal dari tanggal yang diinputkan MM : Menampilkan bulan dari tanggal yang diinputkan MONTH : Menampilkan ejaan dari nama bulan MON : Sama dengan MONTH tetapi hanya ditampilkan 3 huruf RM : Menampilkan bulan dalam bentuk Romawi WW : Menampilkan minggu yang ke sekian dari total bulan

dalam satu tahun W : Menampilkan minggu ke sekian dari total minggu dalam

satu bulan DDD : Menampilkan hari yang ke sekian dari total hari dalam

satu tahun DD : Menampilkan hari yang ke sekian dari total hari dalam satu bulan D : Menampilkan hari yang ke sekian dari total hari dalam

satu minggu DAY : Menampilkan nama hari dari tanggal yang diinputkan DY : Sama dengan DAY bedanya adalah nama hari

ditampilkan dalam 3 huruf HH24:MI:SS AM : Menampilkan waktu sistem DDSPTH : Menampilkan ejaan dari tanggal yang diinputkan

$99999 : Untuk menampilkan numerik dengan format $99999 L99999 : Sama dengan $99999

TO_NUMBERMengkonversikan data yang bertype string menjadi data yang bertype

number ( numerik )

ContohSELECT TO_NUMBER(‘000101’)FROM SYS.DUAL;

TO_DATEMengkonversikan data yang bertype string menjadi data yang bertype date

ContohSELECT id, total, date_orderedFROM s_ordWHERE date_ordered =

TO_DATE(‘September 7, 1992’,’Month dd, YYYY’);

By David 18


6. Nesting Single Row Function

Contoh :SELECT CONCAT( UPPER(last_name) , SUBSTR(title,3) ) “Vice President”

FROM s_empWHERE title LIKE ‘VP%’;

Operasi SELECT dengan melibatkan banyak table

Apakan JOIN ituJoin digunakan dalam operasi query untuk mendapatkan data yang berasal dari

banyak table

1. Cartesian ProductKetika operasi join antara dua table atau lebih tidak dikenakan suatu kondisi

tertentu yang merefrensikan kedua table tersebut, maka hasil dari operasi join tesebut dinamakan Cartesian Product, dimana data yang akan ditampilkan adalah kombinasi dari data kedua table atau lebih

ContohSELECT s_dept.name, s_emp.last_nameFROM s_dept, s_emp;

2. EquijoinUntuk mendapatkan nama dari departemen dimana employee bekerja, dapat

dilakukan operasi penggabungan pada table s_emp dan table s_dept dimana untuk operasi JOIN dilakukan dengan cara menyamakan nilai dari column dept_id pada table s_emp dengan column id pada table s_dept.

Hubungan yang terjadi antara table s_emp dengan s_dept pada contoh di atas disebut dengan equijoin.

ContohSELECT s_dept.id “Kode Departemen”, s_dept.name Departemen,

S_region.id “Kode Wilayah”, s_region.name AS WilayahFROM s_dept, s_regionWHERE s_dept.regionid = s_region.idORDER BY s_dept.id;

HasilKODE DEPARTEMEN DEPARTEMEN KODE WILAYAH WILAYAH 10 Finance 1 North America

By David 19


31 Sales 1 North America…12 rows selected

ContohDapatkan nama employee, kode departemen dan nama departemen dari

employee yang bernama ‘Menchu’

SELECT Em.last_name, Em.dept_id, De.nameFROM s_emp Em, s_dept DeWHERE Em.dept_id = De.idAND INITCAP(Em.last_name) = ‘Menchu’;

HasilLAST_NAME DEPT_ID NAME Menchu 42 Operations

3. Non EquijoinJika penggabungan antara 2 buah table (table s_emp dan salgrade) dimana

tidak terdapat column pada table pertama (table s_emp) yang berkoresponden secara langsung terhadap column pada table kedua (table salgrade), maka hubungan tersebut dapat disebut dengan Non Equijoin

ContohDapatkan nama employee, gaji dan grade dari masing – masing employee

dimana data yang ditampilkan adalah terurut berdasarkan nama employee.

SELECT e.last_name, e.salary, s.gradeFROM s_emp e, salgrade sWHERE e.salary BETWEEN losal AND hisalORDER BY e.last_name;

Hasil

LAST_NAME SALARY GRADEBiri 1100 1Catchpole 1300 2Chang 800 1Dancs 860 1Dumas 1450 3…

25 rows selected4. Outer Join

Dalam penggabungan antara 2 tabel, dimana salah satu atau lebih columnya terhubung satu sama lain, tetapi pada salah satu column table yang terelasi

By David 20


tersebut terdapat nilai yang tidak terefrensikan ke column table lainnya ( column bernilai NULL ), maka kejadian tersebut dalam operasi penggabungan disebut Outer Join

ContohDapatkan nama employee, id, nama customer dimana data yang ditampilkan

terurut berdasarkan id

SELECT e.last_name, e.id, c.nameFROM s_emp e, s_customer cWHERE e.id ( + ) = c.sales_rept_idORDER BY e.id;

Hasil

LAST_NAME ID NAME…Dumas 15 Kam's Sporting GoodsDumas 15 SportiqueDumas 15 Muench SportsDumas 15 Sporta RussiaDumas 15 Kuhn's Sports Sweet Rock Sports…

15 rows selected

Penggunaan tanda (+) akan menyebabkan baris yang tidak memiliki nilai yang terefrensi akan ditampilkan apa adanya ( kosong ) walaupun pada kenyataannya yang ditampilkan adalah baris yang pada salah satu column tidak memiliki nilai. Jika tanda tersebut tidak digunakan maka baris tersebut tidak ditampilkan.

5. Self JoinOperasi penggabungan juga dapat dilakukan dengan cara menggabungkan

sebuah table dengan table itu sendiri. Dalam hal ini diasumsikan bahwa table yang di gabungkan terdiri atas 2 table yang berbeda (tetapi pada kenyataannya adalah sama). Hal ini dimungkinkan dengan cara memberikan nama alis yang berbeda terhadap kedua table tersebut.

ContohDapatkan id employee, nama employee, id manager serta nama manager dari

table employee

SELECT Em.id, Em.last_name, Em.manager_id, Mgr.last_nameFROM s_emp Em, s_emp MgrWHERE Em.manager_id = Mgr.id;

By David 21


HasilID LAST_NAME MANAGER_ID LAST_NAME 2 Ngao 1 Velasquez 3 Nagayama 1 Velasquez 4 Quick-To-See 1 Velasquez 5 Ropeburn 1 Velasquez 6 Urguhart 2 Ngao 10 Havel 2 Ngao …

24 rows selected

Operasi SELECT dengan melibatkan banyak table dengan mengunakan Group Function serta penggunaan ORDER BY & HAVING

Dalam melakukan suatu query dapat dilakukan terhadap sekelompok data yang memiliki minimal sebuah kondisi yang sama dengan cara menggunakan Group Function.Group Function yang terdapat dalam SQL*Plus 3.3 adalah

AVG() : Menghitung rata – rata nilai dari sekelompok data MAX() : Menampilkan nilai terbesar dari sekelompok data MIN() : Menampilkan nilai terkecil dari sekelompok data

SUM() : Menghitung total nilai column tertentu dari sekelompok data

ContohDapatkan rata – rata gaji, gaji terkecil, gaji terbesar dan total gaji dari data gaji

employee

SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)FROM s_emp WHERE UPPER(title) LIKE ‘SALES%’;

Hasil

AVG(SALARY) MAX(SALARY) MIN(SALARY) SUM(SALARY) 1476 1525 1400 7380

Contoh Dapatkan nama employee dengan urutan abjad terdepan ( terkecil ) dan nama

employee dengan urutan abjad terbelakang ( terbesar ) dari data employee

SELECT MIN(last_name), MAX(last_name)

By David 22


FROM s_emp;

Hasil

MIN(LAST_NAME) MAX(LAST_NAME) Biri Velasquez

COUNT() : Mendapatkan jumlah baris / data yang dihasilkan oleh query STDDEV() : Menghitung standard deviasi dari sekelompuk data dengan

mengabaikan nilai NULL VARIANCE() : Mendapatkan varian dari sekelompok data

ContohDapatkan total employee yang bekerja pada departemen 31

SELECT COUNT(*)FROM s_empWHERE dept_id = 31;

Hasil

COUNT(*) 2

ContohDapatkan kode departemen, nama departemen dan jumlah employee yang

bekerja pada departemen tersebut

SELECT dept_id Departemen, d.name Nama, COUNT(e.id) JumlahFROM s_emp e, s_dept dWHERE e.dept_id = d.idGROUP BY e.dept_id, d.name;

HasilDEPARTEMEN NAME JUMLAH

10 Finance 1 31 Sales 2 32 Sales 1 33 Sales 1 34 Sales 2 35 Sales 1 41 Operations 4 42 Operations 3 43 Operations 3 44 Operations 2

By David 23


45 Operations 3 50 Administration 2

12 rows selected.

ContohDapatkan kode departemen, nama departemen dan total gaji, varian dan

standard deviasi dari employee yang bekerja pada masing – masing departemen tersebut

SELECT e.dept_id, d.name, SUM(e.salary), VARIANCE(e.salary), STDDEV(e.salary)

FROM s_emp e, s_dept dWHERE e.dept_id = d.idGROUP BY e.dept_id, d.name ;

HasilKODE NAMA TOTAL VARIAN DEVIASI

10 Finance 1450 0 0 31 Sales 2800 0 0 32 Sales 1490 0 0 33 Sales 1515 0 0 34 Sales 2320 266450 516.18795 35 Sales 1450 0 0 41 Operations 4990 53691.667 231.71462 42 Operations 3245 62258.333 249.5162 43 Operations 2700 32500 180.27756 44 Operations 2100 125000 353.55339 45 Operations 3267 50043 223.70293 50 Administration 4050 451250 671.75144

12 rows selected.

ContohDapatkan data dari departemen (id, dan rata – rata gaji ) dari employee yang

rata – rata gaji employe yang bekerja di departemen tersebut > 2000

SELECT dept_id, AVG(salary)FROM s_empGROUP BY dept_idHAVING AVG(salary) > 2000;

Hasil

By David 24


DEPT_ID AVG(SALARY) 50 2025

1 row selected.

Sintak operasi SELECT secara lengkap adalah

SELECT column, group_functionFROM tables [alias][WHERE conditions][GROUP BY group_by_expression][HAVING group_condition][ORDER BY column];

Subqueries

Yang dimaksud dengan subquery adalah sebuah query (operasi SELECT) yang mengandung query lain di dalamnya.Subquery sangat berguna untuk menghasilkan query yang nilai dari kondisinya merupakan nilai yang belum diketahui

ContohDapatkan data id, last_name dan title dari employee yang bekerja didalam

departemen dimana employee ‘BIRI’ bekerja

SELECT id, last_name, titleFROM s_empWHERE dept_id =

( SELECT dept_id FROM s_emp WHERE UPPER(last_name) = ‘BIRI’);

Hasil ID LAST_NAME TITLE

8 Biri Warehouse Manager 20 Newman Stock Clerk 21 Markarian Stock Clerk

ContohDapatkan data id, last_name, title, salary dari employee yang memiliki salary

berada di bawah gaji rata – rata

SELECT id, last_name, title, salaryFROM s_emp

By David 25


WHERE salary < ( SELECT AVG(salary) FROM s_emp);

Hasil

ID LAST_NAME TITLE SALARY 6 Urguhart Warehouse Manager 1200 7 Menchu Warehouse Manager 1250 8 Biri Warehouse Manager 110017 Smith Stock Clerk 94018 Nozaki Stock Clerk 120019 Patel Stock Clerk 79520 Newman Stock Clerk 75021 Markarian Stock Clerk 85022 Chang Stock Clerk 80023 Patel Stock Clerk 79524 Dancs Stock Clerk 86025 Schwartz Stock Clerk 1100

12 rows selected.

ContohDapatkan data employee yang bekerja pada departemen yang bernama

‘Finance’ atau departemen yang memiliki region_id = 2

SELECT last_name, first_name, titleFROM s_empWHERE dept_id IN

( SELECT id FROM s_dept WHERE name = ‘Finance’ OR region_id = 2);

HasilLAST_NAME FIRST_NAME TITLE Quick-To-See Mark VP, FinanceMenchu Roberta Warehouse ManagerGiljum Henry Sales RepresentativNozaki Akira Stock ClerkPatel Vikram Stock Clerk

5 rows selected.

Contoh

By David 26


Dapatkan data dept_id dan rata – rata salary dari masing masing departemen yang lebih besar dari rata – rata salary employee yang bekerja pada departemen yang memiliki dept_id = 32

SELECT dept_id, AVG(salary)FROM s_empGROUP BY dept_id HAVING AVG(salary) >

( SELECT AVG(salary) FROM s_emp WHERE dept_id = 32);

HasilDEPT_ID AVG(SALARY) 33 1515 50 2025

2 rows selected.

ContohDapatkan data pekerjaan (title dan rata – rata gaji) yang memiliki rata – rata

gaji terendah dari semua title yang ada.

SELECT title, AVG(salary)FROM s_empGROUP BY titleHAVING AVG(salary) <

( SELECT MIN(AVG(salary)) FROM s_emp GROUP BY tite );

TITLE AVG(SALARY)Stock Clerk 949

1 row selected.

Menspesifikasikan variabel pada saat eksekusi

By David 27


Dalam menjalankan sebuah query, kita dapat memasukkan kondisi – kondisi yang kita inginkan pada saat query di jalankan ( dieksekusi ), dengan kata lain kita dapat melakukan interaksi secara langsung terhadap data – data yang kita inginkan walaupun query sudah dibuat terlebih dahulu.

Hal tersebut di atas dimungkinkan karena dalam pembuatan query kita dapat menspesifikasikan beberapa variabel sehingga pada saat query dijalankan kita tinggal memberikan ( mengisikan ) data – data yang kita butuhkan kedalam variabel tersebut sehingga query yang dihasilkan bersifat dinamis

1. Dengan menggunakan single Ampersand (karakter &)Dengan menggunakan variabel yang diawali dengan karakter &, user

dapat menampilkan data tertentu secara dinamis. Pada saat query dijalankan user akan mengisikan data yang dibutuhkan pada prompt yang disediakan,

ContohBuatlah sebuah query yang akan menampilkan id, last_name dan salary

dari employee yang bekerja pada departemen tertentu. Kode departemen tersebut dapat diisi langsung oleh user pada saat query dijalankan

SELECT id, last_name, salaryFROM s_empWHERE dept_id = &Nomor_Departemen;

Hasil

Enter value for Nomor_Departemen : 31

ID LAST_NAME SALARY 3 Nagayama 1400

11 Magee 1400

Perhatikan query tersebut, dalam query tersebut digunakan sebuah variabel Nomor_Departemen dimana pada saat query dieksekusi sql akan menampilkan suatu perintah agar user menginputkan data yang dibutuhkan untuk mengisi nilai dari variabel Nomor_Departemen.

Dan perhatikan hasil dari query setelah user menginputkan sebuah nilai untuk variabel Nomor_Departemen ( yaitu 31 ), maka data yang ditampilkan adalah data employee yang berada dalam departemen yang bernomor sama dengan nilai dari variabel Nomor_departemen yaitu 31.

Penggunaan Ampersand (karakter &) untuk data bertype string atau Date

By David 28


Dalam query di sebelumnya, data yang diinputkan untuk variabel Nomor_departemen adalah bertype numerik. Jika data yang akan diinputkan bertype selain numerik ( string atau date ) maka penggunana variabel selain diawali dengan karakter & juga harus diapit oleh tanda petik tunggal (‘&nama_variabel’)

ContohDapatkan data – data untuk id, last_name, salary dari employeeyang

memiliki title tertentu dimana untuk title dapat diinputkan melalui keyboard

SELECT id, last_name, salaryFROM s_empWHERE title = ‘&Jabatan’;

Hasil

Enter value for jabatan: Presidentold 3: WHERE title = '&Jabatan'new 3: WHERE title = 'President'

ID LAST_NAME SALARY 1 Velasquez 2500

Jika pada query di atas variabel Jabatan tidak diapit oleh tanda petik tunggal, maka yang harus diingat pada saat menginputkan data adalah , data harus diapit oleh tanda petik tunggal

ContohSama dengan sool di atas

SELECT id, last_name, salaryFROM s_empWHERE title = &Jabatan;

Hasil

Enter value for jabatan: ‘President’old 3: WHERE title = &Jabatannew 3: WHERE title = 'President'


By David 29


Menspesifikasikan column name, expresi dan text pada saat eksekusi

ContohTampilkan struktur dari table s_ord, setelah itu tampilkan kode (id) dan

sembarang column lain dari table s_order dimana data yang ditampilkan kondisinya berdasarkan keinginan user ( user dapat memilih kondisi sesuai dengan keinginan user )

SELECT id, &culomn_nameFROM s_ordWHERE &condition;

Hasil

Enter value for culomn_name: total AS Jumlah_Barangold 1: SELECT id, &culomn_namenew 1: SELECT id, total AS Jumlah_BarangEnter value for condition: UPPER(payment_type) = 'CASH'old 3: WHERE &conditionnew 3: WHERE UPPER(payment_type) = 'CASH'

ID JUMLAH_BARANG 103 377 110 1539.13 111 2770 98 595

2. Menggunakan command DEFINE / UNDEFINESelain dengan menggunakan ampersand dapat juga meenggunakan cara

lain dengan cara mendefinisikan sebuah variabel beserta typenya serta inisialisasinya dan kemudian variabel yang didefinisikan tersebut digunakan dalam operasi query

ContohSQL>DEFINE dname = sales

Jika diinginkan untuk melihat hasil dari variabel yang telah didefinisikan di dalam memori maka dapat digunakan perintah di bawah ini

SQL>DEFINE dname

HasilDEFINE dname = “sales” (CHAR)

Penggunaan variabel yang telah didefinisikan dalam operasi query dapat dilihat pada query di bawah ini

By David 30


SELECT nameFROM s_deptWHERE lower(name) = ‘&dname’

Hasilold 3: WHERE lower(name) = '&dname'new 3: WHERE lower(name) = 'sales'

NAMESalesSalesSalesSalesSales

Untuk menghilangkan variabel yang sudah didefinisikan dari memori maka dapat digunakan perintah UNDEFINE

SQL>UNDEFINE dname

3. Menggunakan command ACCEPT (script)

Penggunaan ACCEPT hampir sama dengan penggunaan karakter ampersand perbedaan yang mendasar adalah bahwa dalam penggunaan ACCEPT variabel yang didefinisikan dapat diikuti oleh format tertentu. ACCEPT banyak digunakan dalam pembuatan SCRIPT (query yang disimpan dalam file)

ContohSebelum query di bawah ini dijalankan, simpan dengan nama script1

Dan setelah itu jalankan query dengan cara

SQL>START SCRIPT1

ACCEPT vcolumn PROMPT ‘Inputkan Column yang diinginkan : ’ACCEPT vkondisi PROMPT ‘Inputkan type pembayaran : ‘SELECT &vcolumnFROM s_ordWHERE &vkondisi;

Hasil

SQL>START SCRIPT1SQL> ACCEPT vcolumn PROMPT 'Inputkan Column yang diinginkan : '

By David 31


Inputkan Column yang diinginkan : id, totalSQL> ACCEPT vkondisi PROMPT 'Inputkan type pembayaran : 'Inputkan type pembayaran : payment_type = 'CASH'SQL> SELECT &vcolumn 2 FROM s_ord 3 WHERE &vkondisi 4 /old 1: SELECT &vcolumnnew 1: SELECT id, totalold 3: WHERE &vkondisinew 3: WHERE payment_type = 'CASH'

ID TOTAL 103 377 110 1539.13 111 2770 98 595

4. Mempassing nilai / parameter ke dalam scriptSebuah atau beberapa nilai dapat di passing ke dalam sebuah script pada

saat sebuah script dijalankan. Nilai – nilai tersebut merupakan parameter – parameter yang akan dioperasikan bersama sama dengan query yang terkandung dalam script tersebut.

Penggunaan script dengan menggunakan passing parameter hampir sama dengan pembuatan file – file .BAT yang kita kenal dalam DOS, yaitu dengan menggunakan notasi & diikuti dengan nomor

ContohSimpanlah script ini dengan nama script2

SET ECHO OFFSELECT id, last_name, salaryFROM s_empWHERE title = ‘&1’/SET ECHO ON

HasilSQL> start script2 Presidentold 3: WHERE title = '&1'new 3: WHERE title = 'President'


By David 32


Dalam script di atas (President) adalah parameter yang dipassingkan ke dalam script pada saat script tersebut dijalankan

PEMBUATAN TABLE

Dalam pembuatan table yang harus diingat adalah perlunya untuk mendesain database sebaik mungkin, karena baik tidaknya sistem yang dibuat tergantung dari database yang didesain ( pelajari kembali tentang database yang pernah anda dapatkan pada matakuliah Basis Data )

Hal – hal yang perlu diperhatikan dalam pembuatan table adalah bentuk relation type –nya. Dari bentuk ini dapat kita tentukan hubungan antara table dan setelah melalui proses normalisasi ( jika dianggap perlu ), barulah dapat ditentukan integrity constraint dari masing – masing table tersebutIntegrity constraint mencakup : Entity integrity constraint

Yaitu tidak adanya nilai dari primary key yang NULL dan nilai yang dikandung haruslah UNIQUE

Referential integrity constraintNilai dari foreign key ( kunci tamu pada table child ) harus sama dengan nilai dari

primary key pada base table yang menjadi acuan, atau diset menjadi NULL ( tidak mengacu ke sebuah nilai )

Column integrity constraintNilai dari column harus sama dengan type data pada saat column tersebut

didefinisikan User-defined integrity constraint

Merupakan nilai yang disesuaikan dengan kebutuhan / aturan yang berlaku, contoh dalam sebuah kasus dimana untuk data employee yang baru diterima dan belum memiliki departemen tempat bekerja, maka defaultnya adalah untuk dept_id diisi nilai = 1

Penjelasan tentang Primary key dan Foreign key dapat anda lihat pada halaman 1 dari buku panduan praktikum ini

Pembuatan Table

By David 33


Secara umum syntax dari pembuatan table adalah sebagai berikut

CREATE TABLE [schema.]table_name( column_name data_type [DEFAULT expr]

[column _constraint] , … [table_constraint] );

penjelasan

schema : sama dengan nama dari pemilik (owner’s name)table_name : Nama dari tableDEFAULT expr : Nilai default dari column yang harus dipenuhi saat

operasi insert dilakukancolumn_name : Adalah nama dari columncolumn_constraint : Adalah integrity constraint yang merupakan

bagian dari pendefinisian sebuah columntable_constraint : Adalah integrity constraint yang merupakan bagian

dari pendefinisian sebuah table ( constraint table )

Aturan penamaan Harus diawali dengan huruf Panjangnya max 30 karakter Karakter – karakter yang dapat digunakan A-Z, a-z, 0-9, _, $, dan # Nama dari table / column haruslah belum pernah didefinisikan sebelumnya Bukan merupakan reserved word dari ORACLE 7.3

Type – type data dalam ORACLE 7

VARCHAR2(size) : Variable yang menampung string dimana panjangnya berkisar 1 – 2000 karaktaer

CHAR(size) : Variabel yang menampung string yang panjangnya berkisar 1 – 255

Perbedaan utama dari VARCHAR2 dan CHAR adalah1. Panjang VARCHAR2 adalah max 2000 karakter, CHAR adalah 255

Karakter2. Jika sebuah column didefinisikan terhadap masing – masing type data

(VARCHAR2 dan CHAR) dengan panjang 20 karakter, jika diinputkan data yang panjangnya 10, maka untuk column yang type datanya VARCHAR2, data isimpan denganpanjang 10 karakter, sedangkan

By David 34


untuk type data CHAR, data disimpan sepanjang 20 karakter dengan 10 karakter sisanya adalah blank.

NUMBER : Variabel yang menampung data numerik tanpa desimal

NUMBER(p,s) : Variabel yang menampung data numerik yang panjangnya p dengan s digit desimal

DATE : Variabel yang menampung data date dan jam LONG : Variabel yang digunakan untuk menampung data

yang memiliki nilai panjang karakter diatas 2 gigabyte. Hanya satu column setiap tablenya yang diperbolehkan menggunakan type data ini

RAW and LONGROW : Hampir sama dengan VARCHAR2 dan LONG, tetapi digunakan untuk disimpan dalam dengan berorientasi byte atau data biner dimana tidak diinterpretasikan oleh Oracle 7 Server

Data Integrity Constraint NOT NULL : Menyatakan bahwa column tersebut tidak boleh

bernilai NULL UNIQUE : Menyatakan bahwa nilai yang terkandung dalam

column tersebut harus unique (berbeda) terhadap column yang sama pada baris di lain dalam sebuah table yang sama

PRIMARY KEY : Menyatakan bahwa column tersebut merupakan primary key dimana nilai yang dikandungnya tidak boleh NULL dan harus unique

FOREIGN KEY : Menyatakan bahwa column tersebut mengacu terhadap column yang merupakan primary key pada table yang lain

CHECK : Memberikan ketentuan nilai pada column diman nilai yang boleh diinputkan adalah sesuai dengan yang dikehendaki

Secara umum jika kita mendefinisikan sebuah constraint, dan kita tidak memberikan nama terhadap constraint tersebut maka secara otomatis Oracle akan memberikan nama terhadap constraint yang kita definisikanDalam pendefinisian constraint ada dua cara yang dapat dilakukan, yaitu dengan langsung didefinisikan pada saat pembuatan sebuah table atau setelah table di buat, selain constraint dapat dikenakan column, pemberian constraint dapat juga dilakukan terhadap table

Contoh NOT NULL constraint

By David 35


CREATE TABLE friend(

last_name VARCHAR2(25)CONSTRAINT friend_last_name_NN NOT NULL,

phone VARCHAR2(7) NOT NULL);

Perhatikan kedua column yang dideklarasikan dalam table friend tersebut, untuk column last_name dideklarasikan dengan cara memberikan nama constraint yaitu friend_last_name_nn pada column last_name sedangkan untuk column phone langsung ditetapkan bahwa column tersebut memiliki constraint NOT NULL, dan untuk nama secara otomatis diberikan oleh Oracle

SYNTAX CONSTRAINT

Column_name [CONSTRAINT constraint_name] constraint_type

Atau

Column_name column_type[CONSTRAINT constraint_name] constraint_type(column, …) ,

Contoh UNIQUE constraint

CREATE TABLE friend(

last_name VARCHAR2(25) CONSTRAINT friend_last_name_UK UNIQUE,

phone VARCHAR2(7) UNIQUE)

Contoh PRIMARY KEY constraint

CREATE TABLE karyawan(

id NUMBER(4)CONSTRAINT karyawan_id_PK PRIMARY KEY,

last_name VARCHAR2(25) CONSTRAINT friend_last_name_NN NOT NULL,

phone VARCHAR2(7)

By David 36


)

Untuk lebih jelasnya perhatikan cara pengimplementasian struktur table di bawah ini menjadi sebuah table lengkap dengan constraint – constraint yang telah disebutkan terlebih dahulu

Nama Table : WILAYAH

Nama column : KODE NAMAKey type : PKNN / UK : NN,UK NNFK table : FK column : Type data : Number CharLength : 7 25

Cara pengimplemantasian

CREATE TABLE wilayah(

Kode NUMBER(7)CONSTRAINT wilayah_Kode_PK PRIMARY KEY,

Nama CHAR(25)CONSTRAINT wilayah_Nama_NN NOT NULL

);

Nama Table : DEPARTEMEN

Nama column : ID NAMA KDWILKey type : PKNN / UK : NN,UK1 NN,UK2 UK2FK table : WILAYAHFK column : KODEType data : Number Char NumberLength : 7 25 7

Cara pengimplemantasian

By David 37


CREATE TABLE departemen(

Id NUMBER(7)CONSTRAINT departemen_Id_PK PRIMARY KEY,

Nama CHAR(25)CONSTRAINT departemen_Nama_NN NOT NULL,

KdWil NUMBER(7)CONSTRAINT departemen_KdWil_FK REFERENCESS

Wilayah (Kode),CONSTRAINT departemen_Nama_KdWil_UK UNIQUE

(Nama, KdWil));

Keterangan :

Perhatikan contoh di atas ! Pada contoh di atas telah melibatkan hubunga antara 2 table yaitu table wilayah dan table departemen itu sendiri. Hubungan antara kedua table tersebut diwakili oleh column KdWil (yang merupakan FOREIGN KEY) pada table departemen yang direferensikan dengan column Kode (yang merupakan PRIMARY KEY) pada table wilayah. Perhatikan cara mereferensikannya.

Selain itu pada contoh di atas juga telah ditentukan sebuah constraint untuk table departemen ( constraint table ), dimana constraint tersebut menyatakan bahwa kombinasi untuk column Nama dan KdWil pada table tersebut haruslah UNIQUE

Nama Table : KARYAWAN

Nama column : NIP Nama TglMasuk Gaji NoDep KomisiKey type : PKDefault : sysdateNN / UK : NN,UKFK table : DepartemenFK column : IdType data : Number Char Number Number Number NumberLength : 7 25 7 11,2 7 4,2

Nama column : UserIdKey type : Default : NN / UK : NN, UKFK table :FK column : Type data : Varchar2 Length : 8

By David 38


CREATE TABLE karyawan(

NIP NUMBER(7)CONSTRAINT karyawan_NIP_PK PRIMARY KEY ,

Nama CHAR(25) ,UserId VARCHAR(2)

CONSTRAINT karyawan_UserId_NN NOT NULLCONSTRAINT karyawan_UserId_UK UNIQUE ,

TglMasuk DATE DEFAULT SYSDATE,Gaji NUMBER(11,2) ,NoDep NUMBER(7)

CONSTRAINT karyawan_NoDep_FK REFERENCES Departemen (Id) ,

Komisi NUMBER(4,2)CONSTRAINT karyawan_Komisi_CK CHECK ( Komisi IN(10, 12.5, 15, 17.5, 20))

);

Keterangan :

Contoh di atas merupakan sebuah contoh yang cukup lengkap, Perhatikan contoh di atas

Column NIP memiliki constraint PRIMARY KEY, sedangkan column Nama tidak memiliki constraint. Untuk column UserId memiliki dua buah constraint yaitu NOT NULL dan UNIQUE

Column TglMasuk memiliki default SYSDATE ( tanggal sistem pada saat data karyawan dimasukkan ) yang maksudnya adalah jika dalam menginputkan data karyawan dimana untuk column TglMasuk tidak ikut diinputkan, maka secara default TglMasuk akan diisi dengan tanggal sistem

Column NoDep bereferensi dengan column Id pada table DEPARTEMEN, sedangkan untuk column Komisi data yang diinputkan untuk column ini hanya boleh diisi dengan ketentuan nilai harus diantara salah satu nilai berikut yaitu 10, 12.5, 15, 17.5 dan 20. Dengan kata lain jika pada saat data diinputkan dan pada column Komisi nilai yang diinputkan diluar dari ketentuan maka secara otomatis proses penginputan data karyawan akan ditolak

By David 39


Penggunaan ON DELETE CASCADE pada Constraint FOREIGN KEY

Dalam pendeklaraian sebuah column yang memiliki constraint FOREIGN KEY dapat ditambahkan command ON DELETE CASCADE yang maksudnya adalah jika data yang menjadi acuan (column pada base table yang menjadi acuan) dihapus , maka secara otomatis data pada table yang mengacu base table tersebut akan dihapus

Contoh Pada soal di atas untuk column NoDep akan ditambahkan command ON

DELETE CASCADE

…NoDep NUMBER(7)

CONSTRAINT karyawan_NoDep_FK REFERENCES Departemen (Id) ON DELETE CASCADE ,

…

Membuat table dengan menggunakan subqueryDalam pembuatan table, selain dengan cara di atas, dapat juga dilakukan dengan

cara melakukan subquery. Maksudnya adalah kita membuat table berdasarkan table yang sudah pernah ada.

Syntax

CREATE TABLE table_nameASSELECT column_nameFROM table_nameWHERE condition;

ContohBuatlah sebuah table pegawai yang merupakan subquery dari table karyawan

CREATE TABLE pegawaiASSELECT * FROM karyawan;

Hasil yang didapatkan dari Create table di atas adalah sebuah table pegawai dengan struktur yang sama dengan table karyawan dan data data yang ada di dalam table pegawai sama persis dengan data pada table karyawan

By David 40


ContohBuatlah sebuah table dengan nama s_emp41 yang merupakan subquery dari table

s_emp dimana hanya column id, last_name, userid, dan start_date saja yang diambil dari column table s_emp dan data pegawai yang diambil hanya yang ber dept_id = 41

CREATE TABLE s_emp41ASSELECT id, last_name, user_id, start_dateFROM s_empWHERE dept_id = 41;

Hasil dari create table di atas adalah sebuah table s_emp41 dengan struktur table id, last_name, userid dan start_date sedangkan datanya adalah employee yang memiliki dept_id = 41 ( sesuai dengan kondisi yang ditentukan )

ORACLE DATA DICTIONARY

Oracle data dictionary adalah salah satu komponen yang penting dari Oracle 7 Server, dimana dalam komponen ini terdapat sekumpulan table dan view yang menampilkan referensi secara read-only terhadap database yang berupa

User tables- Merupakan sekumpulan table yang telah terbentuk dan di maintenance

oleh user- Mengandung informasi tentang user

Data dictionary- Sekumpulan dari table yang telah dibentuk dan di maintenance oleh

Oracle 7 Server- Mengandung informasi tenteng database

Gambaran tentang data dictionary

Dibuat pada saat database dibuat Di update dan di meintenance oleh Oracle 7 Server Mengandung data dictionary views Informasi yang terkandung dalam dictionary

Nama – nama dari Oracle 7 Server user Database object names Table constraint Auditing information

By David 41


Contoh 1Menampilkan semua views data dictionary yang dapat diakses oleh user

SELECT *FROM DICTIONARY;

Contoh 2Menampilkan struktur dari User_Objecs View

DESCRIBE user_objects

Contoh 3Menampilkan semua object_name dan object_type dari user_object (owner)

SELECT object_name, object_typeFROM user_objectsContoh 4Menampilkan constraint dari sebuah table

SELECT constraint_name, constraint_type, search_condition r_constraint_name

FROM user_constraintsWHERE table_name =’S_EMP’;

Contoh 5Menampilkan column constraint pada sebuah table

SELECT constraint_name, column_nameFROM user_cons_columnsWHERE table_name = ‘S_EMP’;

MEMANIPULASI DATA ( DML )

Dalam pembahasan kali ini adalah tentang memanipulasi data data dari table yang pernah dibuat.Perintah perintah yang akan dibahas untuk memanipulasi data (DML) dalam SQL diantaranya adalah

By David 42


Melakukan Insert data (baris) ke dalam table ( Operasi Insert )

Untuk menambah atau menyisipkan sebuah baris ke dalam sabuah table digunakan perintah INSERT. Dengan menggunakan syntax ini, hanya sebuah baris yang dapat disisipkan kedalam table.

Syantax

INSERT INTO table_name [ (column [ , column … ] ) ]VALUES ( value [ , value … ] );

Dalam melakukan insert ke dalam sebuah table, sebelumnya kita harus mengetahui struktur tablenya terlebih dahulu. Selain itu kita juga harus mengetahui constraint – constraint yang berlaku baik constraint pada table maupun constraint pada column

Khusus untuk data yang bertype string ( Char & Varchar2 ) dan tanggal (Date), dalam melakukan proses insert harys diapit oleh tanda ‘ ’ (petik tunggal)

Contoh 1Menyisipkan data dengan semua column - columnnya ( lengkap )

INSERT INTO s_deptVALUES ( 11, ‘Finance’, 2);

Contoh 2Menyisipkan baris yang mengandung NULL

INSERT INTO s_deptVALUES ( 12, ‘MIS’, NULL)

Atau

INSERT INTO s_dept (id, name)VALUES (12, ‘MIS’)

Contoh 3Menyisipkan dengan hanya beberapa column saja

INSERT INTO s_emp (id, last_name, userid)VALUES (26, ‘Donna’, ‘SFCL26’)

By David 43


Pada contoh 3 di atas dapat juga dilakukan operasi penyisipan dimana nilai yang disisipkan adalah nilai dari beberapa column dari table

Contoh 4Menyisipkan dengan menggunakan nilai khusus ( special values )

INSERT INTO s_emp (id, last_name, first_name, userid, salary, start_date)VALUES (26, ‘Donna’, ‘Smith’, USER, NULL, SYSDATE);

Perhatikan contoh 4 untuk nilai dari column userid, salary dan start_date dimana nilai yang dimasukkan adalah masing – masing nama user, NULL dan tanggal system

Contoh 5Menyisipkan spesific Date dan Time

INSERT INTO s_emp (id, last_name, first_name, userid, salary, start_date)VALUES (26, ‘Donna’, ‘Smith’, USER, NULL,

TO_DATE(‘01-JAN-96 08:00’ , ‘DD-MM-YY HH:MI’ );

Contoh 6Menyisipkan nilai dengan cara langsung diinputkan oleh user pada saat dieksekusi ( menggunakan variabel )

INSERT INTO s_deptVALUES (&Kode_Departemen, ‘&Nama_departemen’, &Kode_Wilayah);

Contoh 7Menyisipkan baris dengan menggunakan script

ACCEPT No_Dep PROMPT ‘Inputkan Kode Departemen : ’ACCEPT Nama_Dep PROMPT ‘Inputkan Nama Departemen : ’ACCEPT Wil_Dep PROMPT ‘Inputkan Kode Wilayah Departemen : ’INSERT INTO s_dept(id, name, region_id)VALUES (&No_Dep, ‘&Nama_Dep’, &Wil_Dep);

Contoh 8Menyisipkan baris dengan cara mengambil nilai dari table lain (dengan menggunakan subquery)

INSERT INTO history(id, last_name, salary, title, start_date)SELECT id, last_name, salary, title, start_date)FROM s_emp

By David 44


WHERE start_date < ‘01-JAN-94’; Mengupdate data pada sebuah table

Memodifikasi data yang ada di dalam table dapat menggunakan perintah UPDATE, yang memiliki syntax

UPDATE table_nameSET column = value [ , column = value][ WHERE condition ];

Dalam melakukan operasi update juga harus diperhatikan constraint – constraint baik constraint yang menyertai table maupun constraint yang menyertai column

Contoh 1Mengupdate sebuah column pada baris tertentu

UPDATE s_empSET dept_id = 10WHERE id = 2;

Contoh 2Mengupdate beberapa column pada baris tertentu

UPDATE s_empSET dept_id = 32, salary = 2250WHERE id = 1;

Contoh 3Mengupdate semua baris pada table

UPDATE s_empSET commission_pct = 10;

Menghapus data

Penghapusan baris pada sebuah table dapat dilakukan dengan menggunakan perintah DELETE, Syntax

By David 45


DELETE table-name[ WHERE condition ];

Contoh 1Menghapus data tertentu

DELETE s_empWHERE id = 13;

Contoh 2Menghapus semua baris pada table

DELETE s_emp;dalam melakukan penghapusan data tersebut, yang harus diperhatikan adalah

untuk column yang diacu oleh table lain (base table). Maksudnya adalah jika data pada baris tersebut direlasikan ke table lain, dan data pada base table tersebut dihapus, maka otomatis akan disalahkan oleh Oracle karena data pada Base Table tetrsebut masih diacu oleh table lain

Misal data pada departen untuk column id dengan nilai 41 dihapus, maka otomatis akan disalahkan. Mengapa ? karena baris data tersebut diacu oleh table karyawan dimana ada data employee yang memiliki NoDep = 41, Kecuali pada saat pembuatan table dimana untuk column yang mengacu ke table lain menggunakan perintah ON DELETE CASCADE maka jika data pada base table dihapus, maka secara otomatis data yang mengacu juga akan dihapus

Contoh

DELETE s_deptWHERE id = 41;

By David 46


Penggunaan COMMIT, ROLLBACK dan SAVEPOINT

COMMIT

INSERTSAVEPOINTMaker A

UPDATESAVEPOINTMaker B

INSERT

DELETE

ROLLBACK ROLLBACK ROLLBACK to A ROLLBACK to B

Dalam Oracle 7 Server kita diberikan keleluasaan yang lebih fleksibel dalam hal melakukan manipulasi terhadap data

COMMITDalam melakukan query, perubahan data – data yang terjadi akibat dari operasi

query sebenarnya tidak langsung disimpan ke dalam media penyimpanan, tetapi ditampung di dalam buffer.Jika diketikkan perintah COMMIT, maka semua hasil dari perubahan yang terjadi dalam operasi query yang pernah dilakukan akan disimpan kedalam media penyimpanan.

Khusus untuk perintah – perintah DDL seperti CREATE TABLE, DROP TABLE, ALTER TABLE dimana hasil dari operasi tersebut akan langsung disimpan kedalam media penyimpanan ( otomatis commit dijalankan )

SAVEPOINT

By David 47


Digunakan untuk memberikan suatu batasan ( tanda ) terhadap query – query yang kita lakukan ( untuk lebih jelasnya perhatikan gambar di atas ) jika kita telah menandai sebuah area, dan setelah itu kita melanjutkan melakukan query, dan setelah itu kita tidak menginginkan perubahan yang telah kita pada query yang letaknya di bawah query yang kita lakukan di simpan kedalam media penyimpanan maka kita dapat kembali kepada area ( titik ) yang telah ditandai sebelumnya dan melakukan commit sehingga perubahan perubahan yang tidak kita ingini dapat dibatalkan

ROLLBACK TO …Sudah dijelaskan di atas bahwa kita dapat kembali pada area yang kita tandai,

maka untuk kembali ke area tersebut digunakan ROLLBACK TO area_name, jika hanya dituliskan ROLLBACK saja, secara default akan kembali ke awal ( pada saat login ) atau pada COMMIT terakhir yang pernah dilakukan.

Merubah Struktur Tabel dan Constraint (Altering Table And Constraints)

Setelah membuat tabel – tabel, mungkin Anda perlu mengubah struktur dari tabel, yang mungkin diakibatkan karena kebutuhan user yang berubahsehingga diperlukan penambahan sebuah kolom atau lebih, definisi kolom perlu diubah atau Anda ingin mengaktifkan / menon-aktifkan Constraint. Pada bab ini akan menjelaskan bagaimana Anda dapat merubah struktur tabel, juga menambah dan menghapus Constraint dengan perintah ALTER TABLE.

Di akhir bab ini Anda akan mampu untuk :

Menambah dan memodifikasi kolom Menambah, mengaktifkan, atau menghapus constraint Menghapus Sebuah Tabel Menghapus semua baris dari tabel / menghapus semua isi tabel ? Mengubah nama dari obyek (tabel,…) ? Menambah komentar ke obyek dan menampilkan komentar dari kamus data Perintah ALTER TABEL dapat dipakai untuk :

- Menambah dan memodifikasi kolom- Menambah dan menghapus Constraint- Mengaktifkan / menon-aktifkan Constraint

Perintah DROP TABEL menghapus semua tabel dan struktur tabel Perintah lain yang digunakan untuk mengubah sebuah tabel antara lain RENAME,

TRUNCATE, COMMENT Sebuah commit otomatis akan terjadi ketika Anda menjalankan perintah DDL.

Jika Anda ingin menghapus sebuah tabel, dalam artian menghapus semua isi tabel dan strukturnya, dapat digunakan perintah DROP TABEL. Perintah – perintah lain yang dapat dipakai untuk memanipulasi sebuah tabel yang dibahas dalam bab ini : RENAME, perintah untuk mengubah nama tabel TRUNCATE, perintah untuk menghapus semua isi dari tabel

By David 48


COMMENT, perintah untuk menambah sebuah komentar tentang sebuah tabel pada kamus data

Semua perintah di atas adalah perintah Data Definition Language (DDL). Ketika Anda menjalankan salah satu perintah di atas maka sebuah commit otomatis terjadi, Anda tidak dapat mellakukan ROLL BACK terhadap perintah tersebut. Maka Anda harus berhati-hati dalam menggunakan perintah – perintah di atas.

Menambah Sebuah Kolom

Menambah sebuah kolom baru Mendefinisikan default value untuk kolom baru tersebut Menkondisikan kolom harus berisi sebuah nilai atau tidak boleh kosong

Sintak :ALTER TABEL tabel_nameADD ( column type [DEFAULT expr] [NOT NULL] [ , column type] …);keterangan : - Tabel_name adalah nama dari tabel- column adalah nama dari kolom yang baru- type adalah tipe data dan panjang dari kolom yang baru- DEFAULT expr adalah default value yang spesifik untuk kolom baru- NOT NULL adalah menambah sebuah constraint NOT NULL untuk kolom baru

Column tidak dapat dihapus

Contoh :- Menambah sebuah kolom komentar untuk tabel S_REGION

SQL> ALTER TABEL s_region 2 ADD comments VARCHAR2 (255)

CONSTRAINT s_region_comments_nn NOT NULL;

Memodifikasi Sebuah Kolom

Mengubah tipe data, ukuran, default value, dan constraint NOT NULL dari sebuah kolom

Syntax :

ALTER TABEL tabel_nameMODIFY ( column type[DEFAULT expr] [NOT NULL]

By David 49


[, column type]…);

keterangan :

- Tabel_name adalah nama dari tabel- column adalah nama dari kolom yang baru- type adalah tipe data dan panjang dari kolom yang baru- DEFAULT expr adalah default value yang spesifik untuk kolom baru

NOT NULL adalah menambah sebuah constraint NOT NULL untuk kolom baru

Kegunaan : - Menambah lebar atau presisi dari sebuah kolom numerik- Mengurangi lebar dari sebuah kolom jika kolom kosong atau jika tabelnya kosong- Mengubah tipe data jika kolom kosong- Mengkonversi sebuah kolom bertipe CHAR ke tipe VARCHAR2 atau meng-

konversi dari kolom bertipe VARCHAR2 ke tipe data CHAR jika kolom kosong atau Anda tidak mengubah ukurannya

- Sebuah perubahan pada default value dari sebuah kolom hanya berpengaruh pada subsequent insertions ke tabel

- Penambahan sebuah constraint NOT NULL hanya dapat dilakukan jika tidak ada baris dalam kolom tersebut yang kosong

Contoh :- Menambah panjang maximum dari kolom TITLE dalam tabel S_EMP menjadi 50

karakter

SQL> ALTER TABEL s_emp 2 MODIFY title VARCHAR2 (50) ;

Anda dapat mengubah definisi dari sebuah kolom dengan menggunakan perintah ALTER TABEL digabung dengan klausa MODIFY. Modifikasi terhadap kolom dapat dilakukan meliputi tipe data dari kolom, ukuran, default value, and constraint NOT NULL dari kolom.

Menambah Constraint

Menambah atau menghapus, tapi bukan untuk memodifikasi sebuah constraint Mengaktifkan atau menon-aktifkan constraint Menambah sebuah constraint NOT NULL dengan menggunakan klausa MODIFY

Syntax :

By David 50


ALTER TABLE tabel_nameADD [CONSTRAINT constraint_name] type ( column );

keterangan :

- tabel_name adalah nama tabel- constraint_name adalah nama dari constraint- type adalah tipe dari constraint- column adalah adalah nama dari kolom yang akan menggunakan constraint yang dibuat

Menambah sebuah constraint foreign key ke dalam tabel yang mengindikasikan bahwa seorang manajer harus sudah ada seperti validasi employee dalam tabel S_EMP.

Contoh :

SQL> ALTER TABLE s_emp2 ADD CONSTRAINT s_emp_manager_id_fk3 FOREIGN KEY (manager_id)4 REFERENCES s_emp(id);

Menghapus Constraint

Contoh : Menghapus constraint dari manager dari tabel S_EMP

SQL> ALTER TABLE s_emp2 DROP CONSTRAINT s_emp_manager_id_fk;

Menghapus constraint PRIMARY KEY pada tabel S_DEPT dan menghapus hubungan constraint PRIMARY KEY pada kolom S_EMP>DEPT_ID

SQL> ALTER TABLE s_dept2 DROP PRIMARY KEY CASCADE;

Untuk menghapus sebuah constraint Anda dapat mengidentifikasi nama constraint dari USER_CONSTRAINTS data dictionary view dan USER_CONS_COLUMNS. Kemudian menggunakan perintah ALTER TABEL dengan klausa DROP. Option CASCADE dari klausa DROP menyebabkan constraint yang berhubungan ( tergantung pada ) constraint yang dihapus juga ikut dihapus.

SQL> ALTER TABLE tabel_name 2 DROP PRIMARY KEY | UNIQUE (column) | 3 CONSTRAINT constraint [ CASCADE ] ;

By David 51


keterangan :

- tabel_name adalah nama tabel- constraint adalah nama dari constraint- column adalah adalah nama dari kolom yang akan menggunakan constraint yang dibuat

Menon-aktifkan dan Mengaktifkan Constraint

Syntax :

SQL> ALTER TABLE s_emp 2 DISABLE | ENABLE CONSTRAINT constraint_name [CASCADE] ;

Menon-aktifkan Constraint Meneksekusi klausa DISABLE dari perintah ALTER TABLE untuk mendeaktifkan

sebuah integrity constraint Memakai CASCADE option untuk menon-aktifkan dependent integrity constraints

SQL> ALTER TABLE s_emp2 DISABLE CONSTRAINT s_emp_id_pk CASCADE ;

Mengaktifkan Constraint

Mengaktifkan sebuah constraint yang sedang didisable dalam sebuah definisi tabel dengan menggunakan klausa ENABLE

SQL> ALTER TABLE s_emp 2 ENABLE CONSTRAINT s_emp_id_pk ;

Menghapus Table

Ketika diinginkan untuk mengapus table yang telah dibuat, maka kita dapat menggunakan perintah DROP TABLE, syntax-nya adalah

DROP TABLE table_name [CASCADE CONSTRAINT]

ContohMenghapus table karyawan

By David 52


DELETE TABLE karyawan;

Mengganti nama Object ( Table )

Jika diinginkan untuk merubah nama dari table ( object ), digunakan perintah RENAME, syntaxnyaadalah

RENAME old_name TO new_name;

ContohMerubah s_ord menjadi s_order

RENAME s_ord TO s_order;

Perintah TRUNCATE

Perintsh TRUNCATE digunakan untuk menghapus semua data ( baris ) di dalam sebuah table

Contoh

TRUNCATE TABLE s_item;

Memberikan Comments pada table dan column

Comment dapat diberikan terhadap sebuah table maupun column. Comment ini digunakan untuk memberikan keterangan terhadap object tertentu ( table atau column )

Pemberian suatu comment pada sebuah table dapat dilakukan dengan menggunakan perintah :

COMMENT ON TABLE table_name IS comments_name;

Contoh

COMMENT ON TABLE s_empIS ‘Employee Information’;

By David 53


Pemberian comment pada sebuah column dapat menggunakan perintah :

COMMENT ON COLUMN s_emp.last_nameIS ‘Employee name’;

Menampilkan Comments

Comments yang bisa ditampilkan meliputi ALL_COL_COMMENTS

Digunakan untuk menampilkan semua column comments yang ada pada table tertentu

Contoh

SELECT * FROM ALL_COL_COMMENTSWHERE table_name = ‘S_EMP’;

USER_COL_COMMETSDigunakan untuk menampilkan user column comments yang ada table tertentu

Contoh

SELECT * FROM USER_COL_COLUMNWHERE table_name = ‘S_EMP’;

ALL_TAB_COMMENTSDigunakan untuk menampilkan semua table comments yang ada

Contoh

SELECT * FROM ALL_TAB_COMMENTSWHERE table_name = ‘S_EMP’;

USER_TAB_COMMETSDigunakan untuk menampilkan user table comments yang ada

Contoh

SELECT * FROM USER_TAB_COMMENTSWHERE table_name = ‘S_EMP’;

By David 54


*********** (Aha) ***********

By David 55

panduan sql

Documents

table contoh

id dari table s

sqldescribe table

atau sqldesc table

query dengan multi table

laboratorium database

memanipulasi data dengan

contoh sqlselect em