PENGANTAR DASAR DATABASE

PENGANTAR DASAR DATABASE

DEFINISI

Basis data adalah penyimpanan kumpulan informasi secara sistematik dalam sebuah komputer sehingga dapat diperiksa menggunakan suatu program komputer untuk memperoleh informasi dari basis data tersebut. Perangkat lunak yang digunakan untuk mengelola dan memanggil kueri (query) basis data disebut sistem manajemen basis data (database management system, DBMS). Sedangkan sistem adalah sebuah tatanan yang terdiri atas sejumlah komponen fungsional yang saling berhubungan dan secara bersama-sama bertujuan untuk memenuhi suatu proses pekerjaan. Sehingga bisa dikatakan bahwa sistem basis data adalah sistem yang terdiri atas kumpulan file-file yang saling berhubungan dan sekumpulan program (DBMS) yang memungkinkan beberapa pemakai dan atau program lain untuk mengakses dan memanipulasi data tersebut.

Komponen Sistem Basis Data

         Komponen-komponen pada sebuah sistem basis data antara lain:

·   Perangkat keras

·   Sistem operasi

·   Basis data

·   DBMS (Database Management System)

·   Pemakai

·   Aplikasi lain

Sistem Basis Data dan Sistem File

Pada sebuah institusi, data merupakan salah satu hal yang sangat penting. Setiap bagian/divisi dari institusi memiliki data sendiri-sendiri. Tapi setiap bagian pun membutuhkan sebagian data dari bagian yang lain. Hal ini yang biasa dikenal sebagai “shared data”. Setiap divisi memiliki aplikasi sendiri-sendiri dalam melakukan manipulasi dan pengambilan data tersebut. Setiap

aplikasi memiliki file-file dalam sistem operasi yang digunakan untuk menyimpan data-data. Seiring dengan berkembangnya institusi, bertambahnya bagian/divisi, bertambah pula data dan aplikasi yang digunakan. Bertambahnya aplikasi, bertambah pula file-file yang dibuat.

Gaya sistem pemrosesan-file tersebut menyebabkan setiap data disimpan dalam bentuk record dalam berbagai macam file, dan diperlukan aplikasi yang berbeda dalam melakukan pengambilan record dari, dan penambahan record ke dalam file. Hal ini berlaku pada masa sebelum adanya Sistem Basis Data (DBMS).

Menyimpan data dalam bentuk file yang berbeda-beda, memiliki kekurangan-kekurangan:

·   Data redundancy dan inconsistency.

Dikarenakan programer yang berbeda membuat file dan aplikasi masing-masing, menyebabkan beragam format dan aplikasi yang dibuat. Bahkan, aplikasi pun dibuat menggunakan bahasa pemrograman yang berbeda-beda. Lebih jauh lagi, data atau informasi yang sama bisa terdapat dalam beberapa file yang berbeda. Ini yang disebut dengan redundancy. Redundancy data ini lama kelamaan akan menyebabkan inconsystency dari data.

·   Kesulitan dalam pengaksesan data.

Dikarenakan setiap aplikasi memiliki file tersendiri untuk penyimpanan dan pengambilan data, maka jika suatu bagian dari institusi membutuhkan data dari bagian lain, akan menemui kesulitan. Hal ini dikarenakan aplikasi yang dimiliki bagian tersebut, tidak dapat membaca file yang terdapat di bagian lain.

·   Isolasi data.

Dikarenakan data tersebar dalam berbagai macam file, dan file tersebut dalam beragam format, pembuatan aplikasi baru akan terasa sulit ketika harus membaca format dari masing-masing file tersebut.

·   Masalah integritas.

Data yang disimpan harus memenuhi hal yang dinamakan dengan consistency constraint. Jika sebuah constraint berubah, maka seluruh aplikasi yang digunakan harus mengakomodasinya. Masalah akan muncul, jika constraint melibatkan beberapa data dari file yang berbeda-beda.

·   Masalah keamanan.

Tidak semua pengguna dari basis data dapat mengakses semua data. Hal ini akan sulit dilakukan jika menggunakan gaya penyimpanan data dalam file.

Abstraksi Data

Tujuan utama dari sistem basis data adalah untuk menyediakan fasilitas untuk view data secara abstrak bagi penggunanya. Namun bagaimana sistem menyimpan dan mengelola data tersebut, hanya diketahui oleh sistem itu sendiri. Abstraksi data merupakan level dalam bagaimana melihat data dalam sebuah sistem basis data. Berikut ini tiga level abstraksi data:

1.     Level fisik

Merupakan level terendah pada abstraksi data yang menunjukkan bagaimana sesungguhnya data disimpan. Pada level ini pemakai melihat data sebagai gabungan dari struktur dan datanya sendiri.

2.     Level lojik

Merupakan level berikutnya pada abstraksi data, menggambarkan data apa yang disimpan pada basis data dan hubungan apa saja yang ada di antara data tersebut.

3.     Level view

Merupakan level tertinggi dari abstraksi data yang hanya menunjukkan sebagian dari basis data. Banyak user dalam sistem basis data tidak akan terlibat dengan semua data atau informasi yang ada atau yang disimpan. Para user umumnya hanya membutuhkan sebagian data atau informasi dalam basis data yang kemunculannya di mata user diatur oleh aplikasi end user.

Database Administrator

Salah satu alasan dari pemanfaatan DBMS adalah untuk mendapatkan kontrol yang terpusat atas data dan program yang mengakses data tersebut. Orang yang memiliki kontrol atas sistem seperti yang telah disebutkan tersebut adalah database administrator (DBA).

Fungsi-fungsi DBA antara lain:

·   Definisi skema

·   Definisi struktur penyimpanan dan metode akses

·   Modifikasi skema dan organisasi fisik

·   Memberikan otorisasi untuk akese data

·   Spesifikasi integritas constraint

Struktur Sistem

Sebuah sistem basis data terbagi ke dalam beberapa modul yang masing-masing mempunyai tanggung jawab dalam membentuk struktur sistem secara keseluruhan. Beberapa fungsi dari sistem basis data mungkin telah diberikan oleh sistem operasi. Tetapi dalam sebagian besar kondisi, sistem operasi hanya menyediakan servis-servis dasar dan sistem basis data harus dibangun di atasnya.

Komponen-komponen fungsional dari sebuah sistem basis data dibagi menjadi dua, yaitu komponen query processor dan komponen storage manager.

Query processor terdiri atas:

·         DML compiler, menerjemahkan perintah DML pada suatu query ke dalam instruksi level rendah yang dimengerti oleh mesin evaluasi query. Dengan kata lain, DML compiler berusaha mengubah suatu permintaan user menjadi bentuk yang ekivalen namun lebih efisien sekaligus juga menentukan sebuah strategi yang tepat untuk mengeksekusi query tersebut.

·         Embedded DML precompiler, mengkonversi perintah DML yang embed pada sebuah program aplikasi ke procedure call yang normal dalam bahasa host. Precompiler ini harus nerinteraksi dengan DML compiler untuk menghasilkjan kode yang sesuai.

·         DDL interpreter, menginterpretasikan perintah DDL dan mencatatnya dalam sekumpulan tabel yang mengandung metadata.

·         Query evaluation engine, mengeksekusi instruksi level rendah yang dihasilkan oleh DML compiler.

Komponen storage manager menyediakan antarmuka antara data level rendah yang tersimpan dalam basis data dan program aplikasi serta query yang diberikan ke sistem. Yang termasuk komponen ini adalah:

·         Manajer otorisasi dan integritas, menguji integritas dari constraint yang ada serta otoritas user untuk mengakses data.

·         Manajer transaksi, meyakinkan basis data tetap pada kondisi konsisten (benar) saat terjadi kegagalan sistem serta meyakinkan bahawa eksekusi dari transaksi yang konkuren (terjadi “bersamaan”) berlangsung tanpa adanya konflik.

·         Manajer file, mengelola alokasi ruang pada disk penyimpanan struktur data yang digunakan untuk mewakili informasi yang disimpan pada disk.

·         Manajer buffer, bertanggung jawab untuk mengambil data dari disk penyimpanan ke dalam memori utama serta menetukan data yang mana yang akan ditempatkan di memory

CREATE TABLE nama_tabel (  nama_field_1  tipe_data,    nama_field_2  tipe_data,    ………,    [ CONSTRAINT   nama_constraint PRIMARY KEY                     (nama_field)    ]    [ CONSTRAINT   nama_constraint FOREIGN KEY      (nama_field) REFERENCE nama_table (nama_field)     ON DELETE CASCADE        ] );

 

 

Keterangan :

nama_field1, nama_field2 adalah nama kolom (field) yang akan ada di dalam tabel.

tipe_data adalah tipe data yang bisa ditampung oleh field.

tipe_constraint adalah batasan yang menentukan suatu field.

Tanda [ ] menyatakan bahwa yang didalamnya boleh ada atau tidak.

Macam – macam tipe constraint yang mungkin adalah :

·   NULL atau NOT NULL

Constraint NOT NULL ini berfungsi untuk menjamin field harus diisi sedangkan constraint NULL memperbolehkan suatu field untuk dikosongi

·   UNIQUE

Constraint ini berfungsi untuk menjamin bahwa nilai pada setiap record di dalam sebuah tabel adalah unik

·   PRIMARY KEY

·   FOREIGN KEY

·   CHECK

Constraint ini berfungsi untuk menjamin bahwa nilai yang akan dimasukkan ke dalam sebuah field sudah sesuai dengan aturan yang dibuat

Contoh     : pembuatan tabel mahasiswa dengan atribut nim bertipe char(9) dan nama bertipe varchar(20) yang tidak boleh kosong.

Sintaks      : CREATE TABLE mahasiswa

                (

                Nim char(9),

                Nama varchar(20) not null,

                CONSTRAINT pk_mhs PRIMARY KEY (Nim)

                );

 

·         Memanipulasi tabel (ALTER TABLE)

Sebuah struktur tabel yang sudah didefinisikan dapat dimodifikasi dengan menggunakan perintah ALTER TABLE.

Ada beberapa jenis modifikasi pada tabel :

1.    Menambahkan constraint baru untuk sebuah tabel

   Query :

                        ALTER TABLE nama_tabel

                        ADD CONSTRAINT nama_constraint_baru

(definisi_constraint);

   Contoh :  (misal pada tabel mahasiswa belum ada primary key)

               SQL >   ALTER TABLE mahasiswa

                           ADD CONSTRAINT pk_mhs PRIMARY KEY (Nim);

2.    Menghapus sebuah constraint di dalam tabel.

               Query :

                        ALTER TABLE nama_tabel

                        DROP CONSTRAINT nama_constraint;

Contoh :

SQL >   ALTER TABLE mahasiswa

                           DROP CONSTRAINT pk_ mhs;

3.    Manambahkan field baru pada sebuah tabel.

Query :

         ALTER TABLE nama_tabel

                        ADD(nama_field  tipe_data  [definisi_konstraint]);

Contoh :

         SQL > ALTER TABLE mahasiswa

                        ADD(alamat varchar(50) NOT NULL);

4.    Mengubah definisi field yang telah ada pada sebuah tabel.

Query :

         ALTER TABLE nama_tabel

                        MODIFY(nama_field  tipe_field  definisi_konstraint);

 

INSERT INTO nama_tabel [(nama_field1, nama_field2, ….)] VALUES (nilai1, nilai2, …);

         Perlu diperhatikan bahwa nama_field1, nama_field2, … dapat dihilangkan. Hal ini dapat dilakukan bila nilai yang dimasukkan user, urutan sesuai dengan urutan field di dalam tabel tersebut, dan jumlah nilai yang dimasukkan juga harus sama dengan jumlah field pada tabel. Selain itu, proses penyisipan juga harus mematuhi aturan constraint yang telah didefinisikan pada sebuah tabel

Contoh :  

insert into mahasiswa 

values('30108001', 'paijo', 'skp');

atau

insert into mahasiswa (nim,nama,alamat) 

values('30108001', 'paijo', 'skp');

·         UPDATE RECORD

Setiap nilai record yang telah disimpan di dalam tabel dapat dimodifikasi kembali berdasarkan kondisi tertentu. Jika kondisi tidak didefinisikan, maka nilai semua record akan terupdate

Query :

UPDATE nama_tabel        SET    nama_field1 = nilai_baru1,               nama_field2 = nilai_baru2,               ….        [WHERE kondisi];

           

Contoh :

SQL >   update mahasiswa

set nama         = 'paimin',

      alamat      = 'skb'

where nim       = '30108001';

·         DELETE RECORD

Record yang telah disimpan di dalam sebuah tabel dapat dihapus berdasarkan kondisi tertentu. Jika kondisi untuk menghapus tidak didefinisikan maka seluruh record pada tabel tersebut akan dihapus.

Query :

DELETE nama_tabel [WHERE kondisi];

Contoh :

SQL >   delete mahasiswa

where nim = '30108001';

(menghapus sebuah record pada tabel mahasiswa dimana nimnya  ‘30108001’)

SQL >   delete mahasiswa;

(menghapus semua record pada table mahasiswa)

Select Statement

Statement SELECT digunakan untuk mengambil record dari sebuah tabel atau lebih. Record yang diambil dengan SELECT dapat disaring dengan menggunakan kondisi yang terdefinisi.

Statement SELECT mempunyai format sebagai berikut :

SELECT [DISTINCT | ALL]  * | nama_field 1, nama_field 2, ……        FROM  nama_tabel        [WHERE kondisi1]        [GROUP BY nama_field] [HAVING kondisi2]        [ORDER BY nama_field [ASC | DSC]];

DISTINCT digunakan untuk mengambil record yang nilainya tidak ganda.

FROM digunakan untuk mendefinisikan tabel yang menjadi sumber data dari suatu perintah SELECT.

WHERE           digunakan untuk mendefinisikan kondisi pengambilan data pada tabel yang disebutkan di klausa FROM.

GROUP BY digunakan untuk mengelompokkan baris – baris data berdasarkan ekspresi group – group tertentu yaitu untuk field tertentu.

HAVING digunakan untuk memilih / mendefinisikan kriteria kelompok group yang akan ditampilkan berdasarkan group yang akan dibuat. Penggunaa klausa HAVING dipakai sebagai pengganti klausa WHERE pada GROUP BY.

ORDER BY digunakan untuk mengurutkan seleksi berdasarkan kondisi yang diinginkan.

ASC (ascending) berarti pengurutan menaik – 1, 2, 3, 4 ….

DSC (descending) berarti pengurutan menurun – 10, 7, 4, 2, ….

Untuk mengambil semua field pada setiap record, maka ganti semua nama field menjadi tanda bintang ‘*’ (tanpa tanda kutip)

            SELECT *

            FROM nama_tabel

            [WHERE kondisi];

Contoh : SELECT * FROM mahasiswa;

Untuk mengambil record yang memenuhi kondisi tertentu maka statement SELECT digabung dengan klausa WHERE. Format WHERE :

            WHERE  nama_field operator_kondisional nilai

Operator kondisional yang diganakan adalah :

Operator	Arti
=	Sama dengan
<>, !=, ^=	Tidak sama dengan
>	Lebih besar
<	Lebih kecil
>=	Lebih besar atau sama dengan
<=	Lebih kecil atau sama dengan
BETWEEN … AND …	Mengambil nilai yang berada pada antara 2 buah bilangan.
IN	Mengetes nilai – nilai pada list yang disepsifikasikan
LIKE	Menyocokkan sebuah pola karekter
IS NULL	Apakah sebuah nilai null?

Untuk menggabungkan beberapa kondisi didalam klausa WHERE, maka digunakan operator boolean AND, OR, dan NOT.

AND     :  apabila setiap kondisi harus dipenuhi.

OR       :  apabila cukup salah satu kondisi yang terpenuhi.

NOT     : apabila merupakan kebalikan / negasi dari kondisi yang dipenuhi.

Contoh :

Select * from mahasiswa where nama not ‘paimen’;

BETWEEN … AND …

Untuk mengambil record dengan nilai field berada pada batas tertentu, maka digunakan statement BETWEEN atau NOT BETWEEN.

Statement ini bergabung menjadi satu dengan klausa WHERE.

WHERE  nama_field BETWEEN nilai_awal AND nilai_akhir WHERE nama_field NOT BETWEEN nilai_awal AND nilai_akhir

IN dan NOT IN

Untuk menampilkan record yang nilai fieldnya berada pada suatu kelompok nilai tertentu maka gunakan statement : IN atau NOT IN yang dipakai bersama klausa WHERE

WHERE nama_field IN (nilai1, nilai2, nilai3, …) WHERE nama_field NOT IN (nilai1, nilai2, nilai3, …)

LIKE dan NOT LIKE

WHERE nama_field LIKE pola; WHERE nama_field NOT LIKE pola;

Untuk menampilkan record yang nilai fieldnya mengandung nilai tertentu, digunakan statement LIKE atau NOT LIKE yang diletakkan pada klausa WHERE.

Pola adalah karekter atau string, yang dapat dipadukan dengan dua macam wild character, yaitu :

%          : mewakili 0,1 atau beberapa karakter

_          : mewakili tempat sebuah karakter.

Contoh :

1.    %JO%

String yang sesuai dengan %JO% adalah : JOHAN, IJO, PAIJO, JOJON

2.    _ELO_

String yang sesuai dengan _ELO_ adalah : BELOK, KELOK, BELON

Field yang dapat menggunakan statement LIKE atau NOT LIKE adalah field yang bertipe Char, Varchar, atau Varchar2.

IS NULL dan IS NOT NULL

WHERE nama_field IS NULL; WHERE nama_field IS NOT NULL;

Salah satu kegunaan nilai NULL adalah untuk merepresentasikan sebuah nilai yang belum ada. Dalam hal ini nilai field untuk sebuah record belum terisi. Sebuah nilai NULL tidak idektik dengan spasi atau 0.

Untuk menampilkan record yang mempunyai nilai field NULL atau tidak, gunakan statement IS NULL atau IS NOT NULL yang dimasukkan dalam statement WHERE

OPERATOR ARITMATIKA

Ada kemungkinan, pada saat menampilkan nilai, kita ingin memodifikasi nilai yang ingin ditampilkan, misalnya kita ingin menampilkan harga barang, namun harga barang tersebut ditambah 500.

Untuk memodifikasi nilai tersebut kita gunakan operator aritmatika yang diletakkan pada klausa SELECT.

SELECT nama_field1 operator nilai_tambahan, …        FROM nama_tabel        [WHERE kondisi];

            Operator yang digunakan adalah : *, /, +, dan –

Field yang bisa menggunakan operator ini harus bertipe numerik, date?

Contoh :

            SQL >   SELECT nim, spp + 50000

                        FROM keuangan;

PENGANTAR DASAR DATABASE 2

Fungsi-fungsi pada SQL

DUAL

DUAL adalah sebuah built-in tabel yang disediakan oleh Oracle untuk memproses ekspresi aritmatika, logika, tanggal dan lain sebagainya. 

Contoh :

SQL > SELECT 10 * 12 – 2 FROM DUAL;

Menampilkan hasil operasi aritmatika 10 * 12 – 2

SQL > SELECT sysdate FROM DUAL;

Menampilkan tanggal hari ini

GROUP BY … HAVING ….

GROUP BY …. HAVING … digunakan untuk menyeleksi himpunan yang dihitung berdasarkan sebuah fungsi agregasi (agregate function) sesuai dengan kondisi yang didefinisikan setelah HAVING.

  

SELECT  fungsi_agregasi, ..

FROM nama_tabel

GROUP BY nama_field

[HAVING kondisi];

Aggregate Function   

AVG (Average / rata - rata)

AVG berfungsi untuk mengembalikan nilai rata – rata dari sekumpulan nilai yang terdapat pada sekumpulan nilai record pada sebuah field.

Contoh     :

SQL > SELECT AVG(donasi) Donasi FROM keuangan;

Donasi    : merupakan alias dari hasil fungsi AVG(donasi)

Queri diatas digunakan untuk menampilkan donasi rata – rata  mahasiswa yang terdapat di dalam tabel keuangan.

MAX (Maximum)

MAX berfungsi untuk mengembalikan nilai maksimum yang terdapat pada sekumpulan nilai pada sebuah field.

Contoh :

SQL >   SELECT MAX(donasi) “Donasi Terbesar”

         FROM keuangan;

Donasi Terbesar        : merupakan alias dari hasil fungsi    MAX(donasi)

Queri diatas digunakan untuk menampilkan nilai donasi yang paling besar di dalam tabel keuangan.

MIN (Minimum)

MIN berfungsi untuk mengambil nilai minimum yang terdapat pada sekumpulan nilai pada sebuah field.

Contoh :

SQL >   SELECT MIN(donasi) “Donasi Terkecil” 

            FROM keuangan;

Donasi Terkecil      : merupakan alias dari hasil fungsi MIN(donasi)

Queri diatas digunakan untuk menampilkan nilai donasi yang paling kecil di dalam tabel keuangan.

SUM (jumlah / sigma)

SUM berfungsi untuk menghitung jumlah total nilai pada sebuah kolom tertentu.

Contoh :

SQL > SELECT SUM(donasi) “Total donasi” FROM keuangan;

Queri diatas digunakan untuk menampilkan total jumlah donasi untuk seluruh mahasiswa pada tabel keuangan.

COUNT (banyak record)
COUNT berfungsi untuk menghitung jumlah record yang memenuhi kondisi tertentu.

Contoh :

SQL >   SELECT COUNT(*) “Donasi Manajemen Informatika”

FROM keuangan

WHERE nim like ‘3010%’;

Queri diatas digunakan untuk menampilkan jumlah mahasiswa  Manajemen Informatika yang memberikan donasi.

Built-In Function Oracle

Built-In Function adalah fungsi yang disediakan oleh Oracle agar dapat digunakan di dalam SQL *Plus maupun oleh program eksternal.

Fungsi ini terdiri dari beberapa kelompok, yaitu :

Fungsi Aritmatika

Fungsi	Kegunaan	Contoh
ABS	Mengembalikan nilai absolute	ABS(-10) = 10
CEIL	Mengembalikan bilangan bulat terbesar	CEIL(4.2) = 5 CEIL(-4.2) = -4
EXP	Mengembalikan nilai pemangkatan bilangan natural (ex)	EXP(0) = 1 EXP(1) = 2.718
FLOOR	Mengembalikan bilangan bulat terkecil	FLOOR(4.2) = 4 FLOOR(-4.2) = -5
LN	Mengembalikan nilai logaritma natural	LN(2) = 0.693147181
LOG	Mengembalikan nilai logaritma	LOG(10,10) =1
MOD	Menghasilkan sisa pembagian	MOD(4,2) = 0 MOD(7,2) = 1
POWER	Mengembalikan nilai pangkat	POWER(2,3) = 8
ROUND	Mengembalikan bilangan pembulatan	ROUND(5.56,1) = 5.6
SIGN	Mengembalikan nilai positif, negative, atau nol. Nilai balikan sign ada tiga yaitu 1 jika x>0 0 jika x=0 -1 jika x<0	SIGN(-6) = -1 SIGN(3) = 1
SIN, COS, TAN, SINH, COSH,TANH	Fungsi trigonometri	SIN(0) = 0 COS(90) = 0
SQRT	Mengembalikan nilai akar	SQRT(16) = 4
TRUNC	Mengembalikan nilai yang telah dipotong	TRUNC(5.671,2) = 5.67

Fungsi Karakter / String

Fungsi	Kegunaan	Contoh
ASCII(karakter)	Memgembalikan nilai ASCII dari karakter	ASCI(‘A’) = 65
CHR(nilai_ASCII)	Memgembalikan karakter dari sebuah nilai ASCII	CHR(65) = ‘A’
CONCAT(teks1, teks2)	Menggabungkan teks1 dan teks2	CONCAT (“sate”, ”ayam”) = “sate ayam”
DECODE(field, kode_decode)	Menggantikan nilai yang terdapat di dalam field dengan nilai lain.	DECODE(bulan,1, ‘Januari’, 2,’Februari’, 3,’Maret’, 4,’April’, 5,’Mei’, 6,’Juni’, ‘Bulan Lain’ ) bulan
GREATEST(nilai1, nilai2, nilai3, …)	Mengembalikan nilai terbesar dari sederetan nilai	GREATEST (3,10,15,4) = 15
INITCAP(String)	Mengembalikan string yang terdiri dari huruf kapital pada setiap kata.	INITCAP(‘pOLItekNik tElkoM’) = Politeknik Telkom
INSTR (teks1, teks2) INSTR(teks1, teks2, i) INSTR(teks1, teks2, 1, n)	Mencari posisi teks2 yang terdapat di dalam teks1. Pencarian dapat dimulai dari posisi ke-i. n menyatakan pengulangan yang ke-n kali dari teks2 yang terdapat di dalam teks1.	INSTR (’SQL n PL/SQL’,  ’SQL’) = 1 INSTR (’SQL n PL/SQL’,  ’SQL’, 4) = 10 INSTR (’SQL n PL/SQL’,  ’SQL’,1,2) = 10
LEAST(nila1, nilai2, nilai3,…)	Mengembalikan nilai terkecil dari sederetan nilai	LEAST(2,2,5,1,6) = 1
LENGTH(String)	Mengembalikan panjang String	LENGTH(“1233”) = 4
LOWER(String)	Mengubah String menjadi huruf kecil semuanya	LOWER (“PoLTeK”) = poltek
LPAD (teks1, n , teks2)	Menyisipkan karakter teks2 ke dalam teks1 untuk karakter kosong sepanjang n disebelah kiri teks1	LPAD (‘Oracle’, 10 , ‘/’) = ////Oracle
LTRIM (teks1, teks2)	Menghapus karakter-karakter pada bagian kiri teks1 sehingga tidak diawali dengan sembarang karakter pada teks2. Default teks2 adalah spasi	LTRIM (‘xxxOracle’,’x’) = Oracle
REPLACE (teks1, teks2, teks3)	Menggantikan kemunculan karakter teks2 di dalam teks1 dengan teks3. Bila teks3 tidak disebutkan maka teks2 pada teks1 akan dihapus karena default teks3 null	REPLACE (‘siswa’,’a’,’i’) = siswi
RPAD (teks1, n, teks2)	Menyisipkan karakter teks2 ke dalam teks1 untuk karakter kosong sepanjang n disebelah kanan teks1	RPAD (‘Oracle’, 10 , ‘/’) = Oracle////
RTRIM (teks1, teks2)	Menghapus karakter-karakter pada bagian kanan teks1 sehingga tidak diakhiri dengan sembarang karakter pada teks2. Default teks2 adalah spasi	RTRIM ('123000', '0') = 123
SUBSTR(Teks,i) SUBSTR(Teks, i, n)	Mengambil karakter pada string teks dimulai dari posisi ke-i (dari kiri ke kanan) sebanyak n buah. Jika i bernilai negatif, maka posisi ke-i dimulai dari kanan ke kiri	SUBSTR(“ABCD”, 2) = “BCD” SUBSTR (“ABCD”, -3, 2 ) = “BC”
TRANSLATE (teks1, teks2, teks3)	Menggantikan kemunculan karakter teks2 di dalam teks1 dengan teks3. perbedaannya dengan REPLACE adalah karakter yang digantikan dilakukan secara individual
UPER(String)	Mengubah String menjadi huruf besar semuanya	UPER(“PoLTeK”) = POLTEK

Fungsi Tanggal

Fungsi	Kegunaan	Contoh
ADD_MONTHS (tanggal, n)	Menambah atau mengurangi tanggal terhadap n	sysdate =06-JUL-05 ADD_MONTHS (sysdate, 2) = 08-JUL-05 ADD_MONTHS (sysdate, -2) = 04-JUL-05
LAST_DAY (tanggal)	Menghasilkan tanggal terakhir pada sebuah bulan	LAST_DAY (sysdate) = 31-JUL-05
MONTHS_BETWEEN (tanggal 2, tanggal1)	Mengahsilkan selisih tanggal2 dan tanggal1 dalam satuan bulan	MONTHS_BETWEEN (‘01-JUL-05’, ‘14-MAR-05’) = 3.58064516129032
NEXT_DAY()	Mengembalikan tanggal berikutnya	NEXT_DAY() = 07-JUL-05
TO_CHAR (tanggal, format)	Mengubah tanggal menjadi bentuk karakter  sesuai dengan format. Sehingga dapat ditampilkan sebagai string	TO_CHAR (sysdate, ‘DD-MM-YYYY’) = 06-07-2006

Join Tabel

JOIN adalah menggabungkan beberapa tabel dangan cara mengakses setiap tabel secara individu berdasarkan kondisi yang diberikan, kemudian hasilnya digabungkan.

Syarat penggunaan JOIN adalah harus terdapat hubungan / keterkaitan diantara tabel – tabel yang dijadikan sumber dari kolom – kolom yang hendak di join dan ditampilkan. Keterkaitan diatara tabel – tabel berupa kolom – kolom yang memiliki nilai dan tipe data yang sama.

Join dimungkinkan dikarenakan oleh model relasional, begitu juga sebaliknya, join dibutuhkan dikarenakan model relational. Join sebetulnya secara umum terbagi dalam 3 jenis: cartesian product, join condition dan outer join.

Cartessian Product

Cartessian product merupakan himpunan dari hasil kombinasi yang memungkinkan dari baris-baris data dari 2 tabel atau lebih. Cartessian product merupakan join tanpa menggunakan join condition. Dengan demikian, sebuah baris pada tabel yang satu akan digabungkan dengan semua baris pada tabel yang lain, seterusnya sehingga jumlah baris hasil query menjadi ( n₁* n₂ *...*n_n ) dengan n_i  adalah jumlah baris pada tabel ke-i. Jumlah baris hasil query yang merupakan hasil perkalian dari jumlah baris dari tabel-tabel inilah yang disebut dengan produk kartesian (cartesian product).

Contoh :  Misalkan ilustrasi sebagai berikut

Tabel A                                        Tabel B                                                                                                                   

                                                                                        

A1	A2
1	10
2	100

B1	B2
2	4
3	9

Jika kita melakukan operasi seperti ini:

SELECT * FROM A, B;

maka akan dihasilkan Cartesian Product.

Tabel Produk Cartesian

A1	A2	B1	B2
1	10	2	4
1	10	3	9
2	100	2	4
2	100	3	9

Untuk menghindari hal seperti ini, paling tidak terdapat sebuah join condition pada query yang melakukan join terhadap kedua tabel tersebut dengan catatan join condition tersebut valid.

Join Condition

Join condition menspesifikasikan kondisi join dari beberapa tabel. Seperti telah disinggung sebelumnya, join biasanya selalu melibatkan kolom-kolom yang terdapat pada tabel yang terlibat join yang memiliki kesamaan dan keserupaan maksud representasi dari kolom tersebut.

Equjoin/simple join/inner join adalah join yang menggunakan operator sama dengan (=) pada join condition-nya.

Contoh :  Misalnya jika kita melakukan operasi

SELECT * FROM A, B WHERE A.A1 = B.B1

Maka akan dihasilkan sbb:

A1	A2	B1	B2
2	100	2	4

Dari hasil operasi join tersebut dapat terlihat bahwa hanya nilai-nilai dari field A1 pada tabel A dan nilai-nilai field B1 pada tabel B yang bersesuaian yang ditampilkan. Dalam skenario ini adalah A1=B1=2.

Non-Equjoin adalah sebuah join yang menggunakan join condition yang berisi operator selain sama dengan (=) misalnya operator BETWEEN...AND

Self Join adalah query yang menggabungkan sebuah tabel dengan dirinya sendiri. Tabel tersebut muncul dua kali pada klausa from dan masing-masing harus diikuti dengan nama aliasnya. Penggunaan tabel alias ini wajib dilakukan untuk menghindari ambiguous karena semua nama kolom pada tabel pertama ada juga pada tabel kedua.

Contoh:

Misalkan terdapat tabel C sebagai berikut

Tabel C

C1	C2	C3	C4
1	10	2	4
1	10	3	9
2	100	2	4
2	100	3	9

Jika dilakukan perintah:

SELECT a.C1,a.C2,a.C3,a.C4 FROM C a, C b WHERE a.C1=b.C3

Dari skenario ini dapat dibayangkan kita memiliki dua buah tabel yang identik yaitu tabel alias a dan tabel alias b. Dimana operasi yang dilakukan dapat digambarkan sebagai berikut:

Tabel C

C1	C2	C3	C4
1	10	2	4
1	10	3	9
2	100	2	4
2	100	3	9

Tabel Alias b

Tabel Alias a

C1	C2	C3	C4
1	10	2	4
1	10	3	9
2	100	2	4
2	100	3	9

                                                                                    

C1	C2	C3	C4
1	10	2	4
1	10	3	9
2	100	2	4
2	100	3	9

Maka akan dihasilkan seperti ini

C1	C2	C3	C4
2	100	2	4
2	100	3	9
2	100	2	4
2	100	3	9

 

Outer Join

Outer join biasanya digunakan ketika kita ingin baris data dari tabel yang pertama, atau dari tabel yang kedua atau dari keduanya tetap ditampilkan meskipun tidak terdapat pasangan baris data tersebut dari tabel lain pada kondisi join-nya. Misalkan tabel A di-join dengan tabel B. Semua baris data pada tabel A ingin ditampilkan walaupun tidak memenuhi join condition. Untuk itu, operator outer join (+) pada join condition harus disertakan setelah kolom-kolom tabel B. Penulisan operator join dapat ditulis sebagai berikut.

A.col_name = B.col_name(+)

Dalam hal ini, tabel B akan memberikan nilai NULL untuk setiap baris pada tabel A jika join condition tidak terpenuhi.

Contoh :

Misalnya jika kita melakukan operasi SELECT * FROM A, B WHERE A.A1 = B.B1(+) maka akan dihasilkan

A1	A2	B1	B2
1	10	NULL	NULL
2	100	2	4

Pada outer join ada beberapa aturan yang perlu diperhatikan :

·   Operator “+” hanya boleh ada pada klausa WHERE dan hanya dapat dikenakan pada kolom dari tabel atau view.

·   Jika terdapat lebih dari satu join condition, operator  “+” harus dikenakan pada semua join condition tersebut. Jika tidak, hasilnya dengan inner join.

·   Jika ada kondisi yang mengandung kolom tabel B dan kolom tersebut dibandingkan dengan sebuah nilai konstan, operator “+” harus dikenakan pada kolom tersebut. Misalkan:

SELECT * FROM A, B WHERE A.A1 = B.B1(+) and B.B1(+) > 0;

·   Kondisi yang mengandung operator “+” tidak boleh dikombinasikan dengan logika OR dan IN.

·   Kolom dengan operator “+” tidak boleh dibandingkan dengan hasil subquery. Misalkan:

SELECT * FROM A, B WHERE A.A1 = B.B1(+) and B.B1(+) > (SELECT C4 FROM C WHERE C4=2);

·   Untuk query yang menggunakan outer join dan melibatkan lebih dari dua tabel, misalnya tabel A, B, dan C, maka tabel yang satu hanya dapat men-generate tabel NULL untuk satu tabel. Dengan demikian, operator “+” tidak dapat dikenakan pada kolom tabel B untuk dua buah join condition: A join B dan C join B. Misalkan:

SELECT * FROM A, B WHERE A.A1 = B.B1(+) and C.C1=B.B1(+) ;

Kesimpulan: Operasi join digunakan untuk menampilkan data dari beberapa tabel. Ada beberapa macam operasi join, antara lain :

Operator Join	Deskripsi	Contoh
Cartesian Product	Menampilkan data dari beberapa tabel tanpa kondisi tertentu.	SELECT  NAMA_PEGAWAI, NAMA_DEPARTEMEN FROM PEGAWAI P, DEPARTEMEN  D
Join Condition	Menampilkan data dari beberapa tabel dengan kondisi tertentu	SELECT NAMA_PEGAWAI, NAMA_DEPARTEMEN FROM PEGAWAI P, DEPARTEMEN  D WHERE  P.NO_DEP= D.NO_DEPARTEMEN;
Outer Join	Menampilkan data dari beberapa tabel dimana kolom yang diacu tidak memiliki anggota	SELECT P. NAMA_PEGAWAI, M. NAMA_PEGAWAI AS MANAJER FROM PEGAWAI P, PEGAWAI M WHERE  P.NO_MANAJER = M.NO_PEGAWAI(+);

Query Bersarang

Pada kondisi tertentu, terkadang beberapa query membutuhkan nilai yang dihasilkannya dan digunakan sebagai kondisi pembandingan (dalam klausa WHERE sebagai contoh). Query tersebut dapat diformulasikan menggunakan nested query (query bersarang). Query bersarang akan melibatkan query dan subquery.

Subquery merupakan statement SELECT yang bersarang didalam klausa WHERE dari statement SELECT yang lain. Meskipun jarang, tetapi subquery juga bisa terdapat pada statement DML yang lain seperti INSERT, UPDATE ataupun DELETE.

Secara umum, sintaks dari NESTED QUERY adalah:

SELECT [DISTINCT] select_list FROM table1, table_2 [,table_3 …] WHERE {expression {[NOT] IN | comparison operator} | [NOT] EXIST } (           SELECT [DISTINCT] subquery_select_list             FROM table_list             WHERE search_conditions )

Yang dilakukan oleh subquery adalah menetapkan kondisi pencarian pada klausa WHERE dalam beberapa cara:

Menghasilkan list untuk klausa IN

Mengembalikan sebuah nilai yang dapat digunakan oleh operator

Mengembalikan nilai boolean (true atau false)

Didalam sebuah SELECT statement, subquery boleh berada pada klausa-klausa berikut ini:

·   Klausa WHERE

·   Klausa HAVING

·   Klausa FROM

·   Klausa START WITH (pada query hirarki)

Sebagai catatan, subquery pada klausa WHERE sering disebut sebagai nested subquery. Sedangkan subquery pada klausa FROM sering disebut dengan istilah inline view.

Banyak permasalahan yang dapat dipecahkan dengan menggunakan subquery. Daftar berikut ini menunjukkan beberapa kegunaan subquery:

·   Memberikan nilai sebagai kondisi di dalam klausa WHERE, HAVING, dan START WITH (subquery pada SELECT, UPDATE, dan INSERT statement).

·   Menentukan sekumpulan baris data untuk kebutuhan membuat view (subquery pada CREATE VIEW statement).

·   Mendefinisikan sebuah tabel (inline view) yang digunakan oleh query utama (subquery pada klausa FROM dari sebuah SELECT statement).

·   Menentukan sekumpulan baris data yang dimasukkan ke dalam tabel tujuan, baik ke dalam tabel yang sudah ada (subquery pada INSERT statement) maupun pada saat yang bersamaan dengan pembuatan tabel (subquery pada CREATE TABLE statement).

·   Mendapatkan satu nilai atau lebhih yang digunakan untuk mengubah data yang sudah ada (subquery pada UPDATE statement).

Klausa IN

Subquery yang dilakukan menggunakan klausa IN akan dilakukan pengecekan apakah suatu nilai ada atau tidak dalam hasil dari subquery.

Contoh :

SELECT A1 FROM A WHERE A1 IN (SELECT C3 FROM C)

Hasilnya sbb:

A1	A2
2	100

Klausa ALL

Membandingkan nilai dengan setiap nilai yang dikembalikan oleh query tersarang. Kondisi perbandingan akan bernilai benar jika semua nilai yang dikembalikan oleh subquery memenuhi kondisi tersebut.

Contoh :

SELECT * FROM A WHERE A1 < ALL(SELECT C3 FROM C)

Hasilnya sbb:

A1	A2
1	10

Klausa ANY/SOME

Membandingkan nilai dengan salah satu nilai yang dikembalikan oleh query tersarang. Kondisi perbandingan akan bernilai benar jika salah satu nilai yang dikembalikan oleh subquery memenuhi kondisi tersebut dan akan bernilai salah atau tidak terpenuhi jika subquery tidak mengembalikan nilai apapun (tabel kosong).

Contoh :

SELECT * FROM A WHERE A1 < ANY(SELECT C3 FROM C)

Hasilnya sbb:

A1	A2
1	10
2	100

Klausa [NOT] EXISTS

Klausa EXISTS digunakan untuk melakukan pengecekan apakah hasil dari nested query yang berkorelasi menghasilkan baris data atau tidak. Operator exists akan menghasilkan nilai “TRUE” jika subquery yang mengikutinya menghasilkan paling tidak satu baris data.

Contoh :

SELECT * FROM A WHERE EXISTS (SELECT C3 FROM C)

Hasilnya sbb:

A1	A2
1	10
2	100