BAB V. Basis Data Darmawan Satyananda 74

BAB V SQL (STRUCTURED QUERY LANGUAGE) Query adalah permintaan untuk mendapatkan data dari basis data. SQL merupakan bahasa standar dalam basis data relasional untuk membuat basis data dan mendapatkan data dari basis data. SQL menyediakan interface bahasa deklaratif dan non prosedural, sehingga pengguna hanya menentukan hasil apa yang diinginkan, sedangkan bagaimana eksekusi dan optimisasi query dilakukan oleh DBMS. Dalam SQL terdapat sejumlah perintah yang bisa dibagi ke dalam 3 jenis: ·

Perintah DDL (Data Definition Language) untuk membuat, memodifikasi, dan menghapus basis data dan tabel.

·

Perintah DML (Data Manipulation Language) untuk memasukkan data, mengubah data, menghapus data dan query terhadap data dalam tabel. Banyak yang menganggap perintah DML sebagai inti dari perintah-perintah SQL

·

Perintah DCL (Data Control Language) untuk mengontrol basis data. Termasuk di dalamnya pengendalian transaksi basis data, integritas basis data, security, dan pemberian hak untuk melakukan akses terhadap basis data dan obyek dalam basis data Versi asli SQL bernama SEQUEL (Structured English Query Language) yang

dikembangkan di laboratorium IBM sekitar tahun 1970, dan dirancang sebagai interface untuk basis data relasional SYSTEM R. Saat ini SQL digunakan sebagai bahasa standar bagi kebanyakan basis data relasional komersial, seperti Oracle, Microsoft SQL Server, MySQL, postgreSQL, dan Microsoft Access. SQL juga telah diterapkan pada mainframe dan PC. Penerapan SQL memberikan beberapa keuntungan (Hoffer dkk, 2002): ·

Pengurangan biaya pelatihan karena cukup melatih satu jenis bahasa saja

·

Peningkatan produktivitas karena pengguna bisa mempelajari SQL secara lebih mendalam

·

Kemudahan dalam migrasi aplikasi dari satu jenis mesin ke mesin yang lain, selama sama-sama menggunakan SQL.

·

Masa pakai aplikasi yang lama karena tidak perlu mengubah bahasa

·

Mengurangi ketergantungan kepada salah satu vendor aplikasi tertentu

·

Memungkinkan komunikasi antar aplikasi dan DBMS pada sistem yang berbeda. Standarisasi SQL dilakukan oleh American National Standard Institute (ANSI).

Standar pertama adalah SQL-86 atau SQL1 pada tahun 1986, diikuti oleh SQL-92 (dikenal juga dengan SQL2) pada 1992. Standar terbaru SQL adalah SQL-1999 atau SQL3. Walau begitu tidak semua fitur SQL dalam standar tersebut direalisasikan oleh DBMS, sehingga Basis Data – Darmawan Satyananda

74

masih memunculkan ketidakkompatibelan perintah antar DBMS yang satu dengan yang lain. Masing-masing DBMS juga memunculkan fitur tersendiri yang mungkin tidak ada pada DBMS lain sehingga juga mengakibatkan ketidakkompatibelan perintah. Untuk

query yang kompleks, sering harus dilakukan perubahan bila berpindah ke DBMS lain. Dalam bab ini, perintah SQL yang digunakan telah diuji pada Microsoft Access 2000 dan XP, serta MySQL 5.0.33 yang dijalankan pada sistem operasi Windows. Beberapa hasil query akan ditunjukkan, terutama pada query DML. Bab ini tidak membahas bagaimana penggunaan SQL dalam Microsoft Access dan MySQL.

5.1. Penentuan Skema Tabel SQL menggunakan istilah tabel, baris, kolom, masing-masing untuk menyatakan relasi, tupel, dan atribut. Seperti yang telah disebutkan sebelumnya, perintah DDL dalam SQL berkaitan dengan pembuatan dan modifikasi tabel dan basis data. Walaupun pada bab I sudah dinyatakan bahwa basis data adalah kumpulan tabeltabel yang saling terkait, SQL tidak mengenal perintah untuk pembuatan basis data. Pembuatannya diserahkan implementasinya pada DBMS yang bersangkutan. Ada juga yang berkaitan dengan basis data yaitu schema (skema) yang mendaftar semua obyek dalam basis data semisal tabel, indeks, dan view. Pembuatan skema sifatnya juga opsional, sehingga bisa diabaikan. Ada 3 perintah yang digunakan untuk penentuan skema tabel yaitu CREATE TABLE, ALTER TABLE, dan DROP TABLE, masing-masing untuk membentuk tabel, mengubah tabel, dan menghapus tabel.

5.1.1. Membuat Tabel Untuk membentuk suatu tabel baru digunakan perintah CREATE TABLE, dengan menyebutkan nama tabel, atribut, serta sejumlah constraint (batasan) yang diperlukan. Perintah pembuatan tabel mempunyai bentuk umum: CREATE TABLE ( [NOT NULL] [UNIQUE] [DEFAULT ]) {, [NOT NULL] [UNIQUE] [DEFAULT ]} [,PRIMARY KEY ( {[,]})] [,UNIQUE ()] [{,FOREIGN KEY () REFERENCES () ON UPDATE CASCADE ON DELETE CASCADE}] [,CONSTRAINT <definisi constraint>] ); adalah nama tabel yang akan dibuat, adalah atribut tabel, adalah tipe data, dan NOT NULL adalah aturan yang menyatakan apakah suatu atribut bisa menerima nilai NULL atau tidak. Bila suatu atribut tidak diperbolehkan mempunyai nilai null maka tambahkan constraint NOT NULL, yang Basis Data – Darmawan Satyananda

75

umumnya berlaku untuk atribut kunci primer suatu tabel. Klausa UNIQUE menyatakan bahwa nilai suatu kolom harus unik, baik sebagai kunci primer atau kunci kandidat. DEFAULT menyatakan nilai yang diisikan ke dalam suatu atribut bila nilainya tidak ditentukan pada saat penyisipan baris baru, bukan pada saat modifikasi. Klausa PRIMARY KEY menyatakan satu atau lebih atribut yang digunakan sebagai kunci primer tabel. FOREIGN KEY digunakan untuk menyatakan referential integrity ke tabel lain. ON UPDATE CASCADE digunakan untuk meneruskan pengubahan atribut dari suatu tabel ke tabel lain yang mengacunya, sedangkan ON DELETE CASCADE digunakan untuk meneruskan penghapusan baris pada suatu tabel ke tabel lain yang mengacunya. CONSTRAINT adalah aturan yang diberikan ke suatu tabel. Setiap constraint memiliki nama (agar kemudian bisa dimodifikasi atau dihapus) dan definisi. Isinya berupa penentuan PRIMARY KEY, FOREIGN KEY, dan UNIQUE. Setiap atribut dikaitkan dengan tipe data tertentu. Tipe data yang digunakan dalam SQL2 untuk atribut adalah Numeric, Character-string, Bit-string, Date dan Time. Walaupun begitu, beberapa DBMS mengembangkan sendiri tipe data selain yang disebutkan, misalnya tipe data Auto-Number di Microsoft Access, serta OLE (Object

Linking and Embedding), BLOB (Binary Large Object), Boolean, Currency, dan lainnya. Tabel 5.1. Tipe data dalam SQL Kelompok Numeric

Characterstring Bit-string

Tipe INTEGER INT SMALLINT FLOAT(n) REAL DOUBLE PRECISION DECIMAL(i, j) DEC(i, j) NUMBER(i, j) CHAR(n) CHARACTER(n) VARCHAR(n) CHAR VARYING(n) BIT(n) BIT VARYING(n)

Date

DATE

Time

TIME TIME(i) TIME WITH ZONE TIMESTAMP INTERVAL

Timestamp

Basis Data – Darmawan Satyananda

Keterangan Bilangan bulat sampai 11 digit (positif dan negatif) Bilangan bulat sampai 6 digit Bilangan riil, n adalah digit presisi Bilangan terformat, i adalah banyaknya digit desimal dan j adalah banyaknya digit angka di belakang titik desimal. Panjang tetap, n menyatakan banyaknya karakter Panjang bervariasi, n menyatakan banyak karakter maksimum Panjang tetap, n menyatakan banyaknya bit Panjang bervariasi, n menyatakan banyak bit maksimum Data tanggal, terdiri dari YEAR (4 digit), MONTH (2 digit), DAY (2 digit) Data waktu, terdiri dari HOUR, MINUTE, SECOND i menyatakan fraksi desimal untuk detik Waktu yang dikaitkan dengan daerah tertentu Terdiri dari DATE dan TIME Menyatakan nilai relatif yang digunakan untuk menambah atau mengurangi nilai mutlak date, time, atau timestamp. 76

Contoh pendefinisian struktur tabel untuk basis data PERUSAHAAN adalah: CREATE TABLE KARYAWAN ( NamaKaryawan VARCHAR(20) NOT NULL, NIP CHAR(9) NOT NULL, TglLahir DATE, Alamat VARCHAR(30), JenisKlm CHAR, Gaji INT, NIP_Pengawas CHAR(9), KodeDep_Kar INT NOT NULL, PRIMARY KEY (NIP)); CREATE TABLE DEPARTEMEN ( NamaDepartemen VARCHAR(20) NOT NULL, KodeDepartemen INT NOT NULL, NIP_Manajer CHAR(9) NOT NULL, TglMulai_Manajer DATE, PRIMARY KEY (KodeDepartemen), UNIQUE (NamaDepartemen)); CREATE TABLE LOKASI_DEP ( KodeDep_Lok INT NOT NULL, Lokasi VARCHAR(15) NOT NULL, PRIMARY KEY (KodeDep_Lok, Lokasi)); CREATE TABLE PROYEK ( NamaProyek VARCHAR(15) NOT NULL, KodeProyek INT NOT NULL, LokasiProyek VARCHAR(15), KodeDep_Proyek INT NOT NULL, PRIMARY KEY (KodeProyek), UNIQUE (NamaProyek)); CREATE TABLE BEKERJA_DI ( NIP_Kar CHAR(9) NOT NULL, KodeProyek_Kar INT NOT NULL, JamKerja INT NOT NULL, PRIMARY KEY (NIP_Kar, KodeProyek_Kar)); CREATE TABLE TANGGUNGAN ( NIP_Kar CHAR(9) NOT NULL, NamaTanggungan VARCHAR(15) NOT NULL, JenisKlm CHAR NOT NULL, TglLahir DATE, Hubungan VARCHAR(8), PRIMARY KEY (NIP_Kar, NamaTanggungan)); Dalam Microsoft Access, pendefinisian struktur tabel bisa dilakukan dalam suatu

query editor seperti yang tampak pada gambar 5.1.

Gambar 5.1. Definisi struktur tabel di Microsoft Access. Basis Data – Darmawan Satyananda

77

Perhatikan deklarasi pembuatan tabel DEPARTEMEN, klausa UNIQUE menyatakan bahwa atribut NamaDepartemen nilainya tidak boleh sama dalam satu tabel. Yang mengikuti UNIQUE tidak harus satu atribut, bisa juga disebutkan beberapa atribut sekaligus. Selain dinyatakan dengan cara tersebut, UNIQUE bisa dinyatakan setiap pendefinisian atribut sebagai berikut: CREATE TABLE DEPARTEMEN ( NamaDepartemen VARCHAR(20) NOT NULL UNIQUE, KodeDepartemen INT NOT NULL, NIP_Manajer CHAR(9) NOT NULL, TglMulai_Manajer DATE, PRIMARY KEY (KodeDepartemen)); Bila suatu atribut dinyatakan sebagai kunci primer, maka sebenarnya aturan UNIQUE dan NOT NULL sudah berlaku walaupun tidak dituliskan karena sifat kunci yang tidak boleh kosong dan tidak boleh ganda. ON DELETE CASCADE dan ON UPDATE CASCADE menyatakan bahwa operasi penghapusan dan pengubahan suatu atribut yang menjadi kunci primer di suatu tabel akan diteruskan kepada tabel lain yang mengacunya, dengan demikian integritas referensial akan tetap terjaga. Pada Access, definisi kedua aturan ini hanya bisa dilakukan melalui pengaturan properti relationship seperti pada Gambar 5.2.

Gambar 5.2. Pengaturan Cascade Update dan Cascade Delete pada Microsoft Access Access juga tidak menerima klausa DEFAULT melalui query editor, tetapi menyediakan pilihan DEFAULT pada saat pendefinisian struktur tabel melalui interface yang tersedia. Pengacuan ke tabel lain melalui kunci tamu dilakukan dengan menggunakan klausa FOREIGN KEY. Syaratnya adalah tabel dan atribut yang diacu sudah harus ada. Sebagai contoh, atribut NIP_Manajer di tabel DEPARTEMEN mengacu ke atribut NIP di tabel KARYAWAN, kemudian setiap pengubahan NIP karyawan akan ikut mengubah nilai NIP_Manajer dan penghapusan data karyawan tertentu akan ditolak bila NIP masih diacu oleh tabel DEPARTEMEN. Pengacuan itu bisa dinyatakan sebagai berikut: Basis Data – Darmawan Satyananda

78

CREATE TABLE DEPARTEMEN ( NamaDepartemen VARCHAR(20) NOT NULL UNIQUE, KodeDepartemen INT NOT NULL, NIP_Manajer CHAR(9) NOT NULL, TglMulai_Manajer DATE, PRIMARY KEY (KodeDepartemen), FOREIGN KEY (Nip_Manajer) REFERENCES KARYAWAN(NIP) ON UPDATE CASCADE);

Constraints selain dinyatakan secara langsung seperti contoh tersebut, juga bisa dinyatakan sebagai klausa tersendiri, seperti contoh berikut: CREATE TABLE DEPARTEMEN ( NamaDepartemen VARCHAR(20) KodeDepartemen INT NIP_Manajer CHAR(9) TglMulai_Manajer DATE, PRIMARY KEY (KodeDepartemen), CONSTRAINT CONST1 FOREIGN KEY REFERENCES KARYAWAN(NIP) ON

NOT NULL, NOT NULL, NOT NULL, (Nip_Manajer) UPDATE CASCADE);

CONST1 adalah nama constraint yang nantinya bisa dimodifikasi atau dihapus. Tabel yang dideklarasikan dengan CREATE TABLE disebut sebagai tabel dasar (base table) atau relasi dasar (base relation). Tabel dan tupelnya akan disimpan sebagai file oleh DBMS, berbeda dengan relasi maya (virtual relation) yang dibentuk oleh CREATE VIEW yang mungkin tidak berhubungan dengan file fisik. Atribut dalam tabel dasar diurutkan sesuai dengan urutan atribut yang disebutkan pada CREATE TABLE, sedangkan tupelnya dalam keadaan tidak terurut.

5.1.2. Memodifikasi Skema Tabel Untuk menambahkan atribut baru atau menghapus atribut yang ada, digunakan perintah ALTER TABLE. Secara umum, bentuk perintahnya adalah: ALTER TABLE [ADD [NOT NULL]] | [DROP [RESTRICT | CASCADE]] | [MODIFY [NOT NULL]]; Klausa ADD digunakan untuk menambahkan atribut baru, klausa DROP digunakan untuk menghapus atribut dari tabel, dan klausa MODIFY untuk mengubah karakteristik atribut. Klausa NOT NULL pada ADD digunakan untuk menyatakan bahwa atribut tersebut tidak boleh

dikosongkan.

CASCADE

dimaksudkan

bahwa

penghapusan

atribut

akan

menghilangkan pengacuan ke atribut tersebut, sedangkan RESTRICT menyatakan bahwa penghapusan akan berhasil bila tidak ada pengacuan ke atribut tersebut. Sebagai contoh, untuk menambah atribut HOBI ke dalam tabel KARYAWAN perintah selengkapnya adalah: ALTER TABLE KARYAWAN ADD HOBI VARCHAR(12); Setelah perintah ini dijalankan, semua tupel dengan atribut tersebut akan bernilai NULL,


79

data harus ditambahkan secara tersendiri. Sebaiknya tidak menyertakan NOT NULL pada saat menambahkan atribut baru pada tabel yang sudah berisi data karena akan mengakibatkan pelanggaran integritas entitas pada baris yang ada. Ada DBMS yang akan mengeluarkan pesan kesalahan bila NOT NULL disertakan. Untuk mengatasinya, maka NOT NULL tidak disertakan, atribut baru diisi dengan data, baru kemudian NOT NULL ditambahkan. MODIFY digunakan untuk mengubah tipe data suatu atribut. Misalkan untuk mengubah tipe data atribut HOBI di tabel KARYAWAN, maka perintahnya adalah: ALTER TABLE KARYAWAN MODIFY (HOBI VARCHAR(20)); Sayangnya, MODIFY tidak didukung oleh Access sehingga pengguna harus menggunakan

table editor untuk mengubahnya. Untuk melakukan penghapusan atribut HOBI dari tabel KARYAWAN maka perintah yang diberikan adalah: ALTER TABLE KARYAWAN DROP HOBI; Gunakan CASCADE atau RESTRICT bila memang atribut tersebut diacu oleh atribut lain. Microsoft Access tidak mengijinkan penghapusan atribut yang menjadi kunci primer atau terlibat dalam relationship (diacu oleh tabel lain).

Constraint selain ditentukan pada saat pembuatan tabel bisa juga ditambahkan menyusul, atau dihilangkan dari tabel. Sintaks perintah penambahan atau penghapusan

constraint adalah: ALTER TABLE [ADD CONSTRAINT ] | [DROP CONSTRAINT ] [CASCADE | RESTRICT]; adalah nama pengenal dari constraint. Nama ini tetap akan digunakan bila constraint yang ada akan dihapus. Sebagai contoh, untuk menambahkan

constraint pengacuan pada semua tabel yang telah dibuat sebelumnya, maka perintah selengkapnya adalah: ALTER TABLE KARYAWAN ADD CONSTRAINT ref1 FOREIGN KEY (NIP_Pengawas) REFERENCES KARYAWAN(NIP), FOREIGN KEY (KodeDep_Kar) REFERENCES DEPARTEMEN(KodeDepartemen); ALTER TABLE DEPARTEMEN ADD CONSTRAINT ref2 FOREIGN KEY (NIP_Manajer) REFERENCES KARYAWAN(NIP); ALTER TABLE LOKASI_DEP ADD CONSTRAINT ref3 FOREIGN KEY (KodeDep_Lok) REFERENCES DEPARTEMEN(KodeDepartemen); ALTER TABLE PROYEK ADD CONSTRAINT ref4 FOREIGN KEY(KodeDep_Proyek) REFERENCES Basis Data – Darmawan Satyananda

80

DEPARTEMEN(KodeDepartemen); ALTER TABLE BEKERJA_DI ADD CONSTRAINT ref5 FOREIGN KEY (NIP_Kar) REFERENCES KARYAWAN(NIP), FOREIGN KEY (KodeProyek_Kar) REFERENCES PROYEK(KodeProyek); ALTER TABLE TANGGUNGAN ADD CONSTRAINT ref6 FOREIGN KEY (NIP_Kar) REFERENCES KARYAWAN(NIP); ref1, ref2, dan seterusnya adalah nama constraint. Nama ini juga digunakan untuk menghapus constraint. Misalnya ref1 dihapus, maka perintahnya adalah: ALTER TABLE TANGGUNGAN DROP CONSTRAINT ref1 RESTRICT; Dengan adanya aturan pengacuan, DBMS akan menentukan bahwa atribut yang menjadi kunci tamu tidak bisa diisi dengan sembarang nilai yang tidak ada di tabel yang diacu dan suatu baris di tabel yang diacu tidak bisa dihapus selama masih ada yang mengacunya.

5.1.3. Menghapus Tabel Bila suatu tabel sudah tidak diperlukan lagi, maka tabel tersebut dan definisinya dapat dihapus. Perintahnya secara umum: DROP TABLE [[CASCADE] | [RESTRICT]]; adalah nama tabel yang akan dihapus. CASCADE dan RESTRICT adalah

constraint pada saat dilakukan penghapusan. Bila RESTRICT digunakan maka penghapusan tabel akan dilakukan apabila tabel tersebut tidak diacu oleh tabel lain (melalui foreign key). Sedangkan bila CASCADE digunakan maka semua pengacuan ke tabel yang akan dihapus dihilangkan, selanjutnya barulah dilakukan penghapusan tabel (tabel yang mengacu tetap ada). Dengan demikian untuk menghapus tabel TANGGUNGAN, perintah yang harus diberikan adalah: DROP TABLE TANGGUNGAN CASCADE

5.2. Manipulasi tabel dengan SQL Manipulasi data dalam tabel meliputi memasukkan tupel baru, mengubah data dalam tupel, dan menghapus tupel.

5.2.1. Menambahkan Tupel Baru Perintah INSERT digunakan untuk menambahkan sebuah tupel baru ke dalam tabel. Nilai atribut tupel baru harus dituliskan dalam urutan yang sama dengan atribut yang didefinisikan dalam CREATE TABLE. Perintah selengkapnya: INSERT INTO [( {, })] VALUES ( {, }) |

81

SELECT>; Contoh pernyataan untuk menambahkan sebuah tupel baru ke dalam tabel KARYAWAN adalah: U1:

INSERT INTO KARYAWAN VALUES (‘Upik’, ’1560694’, ‘30-12-1967’, ‘Jl.Ambarawa 2 Malang’, ‘P’, 500000, ‘9879879’, 2);

Selain dengan menuliskan nilai atributnya secara lengkap, penyisipan juga bisa dilakukan dengan hanya menuliskan nilai beberapa atribut yang ditentukan saja. Atribut yang tidak disebutkan secara otomatis diisi dengan NULL. Contoh: U2:

INSERT INTO KARYAWAN (NamaKaryawan, NIP, TglLahir, KodeDep_Kar) VALUES (‘Bejo’, ’1233211’, ‘30-12-1967’, 1) Beberapa DBMS yang menggunakan SQL ada yang tidak memaksakan aturan

integritas, sehingga dimungkinkan untuk memasukkan nilai yang tidak sesuai dengan acuan seperti pada contoh berikut: U3:

INSERT INTO KARYAWAN (NamaKaryawan, NIP, TglLahir, KodeDep_Kar) VALUES ("Bambang", "1283723", "12-12-1977", 7);

Perintah di atas akan tetap dijalankan oleh SQL walaupun tidak ada departemen dengan nomor departemen bernilai 7. Hal seperti ini menjadi tanggung jawab pengguna untuk mengatasinya. Bila DBMS menerapkan pengecekan constraint maka query U3 akan ditolak. Penolakan DBMS yang lain dilakukan bila atribut yang mempunyai constraint NOT NULL tidak dimasuki nilai apapun (sehingga menjadi NULL). Contoh berikut akan ditolak karena nilai untuk NIP tidak disebutkan. U4:

INSERT INTO KARYAWAN (NamaKaryawan, Tgl_Lahir, KodeDep_Kar) VALUES (‘Joni’, ‘3-10-1966’, 1);

Perhatikan bahwa kita sebelumnya menentukan constraint untuk NIP sebagai NOT NULL sehingga U4 akan menyebabkan pelanggaran constraint. Perhatikan bahwa data dimasukkan sesuai dengan tipe data yang ditentukan pada saat pendefinisian tabel. Nilai bertipe numerik dimasukkan tanpa tanda petik, nilai bertipe text dimasukkan dengan menggunakan tanda petik, demikian pula data yang berupa tanggal. Untuk tanggal, DBMS menetapkan aturan yang berbeda. Access menggunakan pembatas berupa ‘#’ (sehingga data ditulis sebagai #3-10-1966#). DBMS lain (misalkan MySQL) menganggapnya sebagai string dengan format yyyy/mm/dd, contohnya ‘1966/10/3’. Selain dengan menuliskan nilai setiap atribut dalam klausa VALUES, penyisipan penyisipan bisa dilakukan dengan menggunakan query sehingga yang dimasukkan ke dalam tabel bisa banyak data sekaligus. Hal ini akan dipelajari pada sub bab 5.4.1.


82

5.2.2. Menghapus Tupel Perintah DELETE digunakan untuk menghapus tupel pada sebuah tabel. Perintah lengkap DELETE adalah: DELETE FROM WHERE ; digunakan untuk memilih tupel yang akan dihapus menurut atribut dan nilai tertentu. Yang diseleksi tidak harus kunci primer, sembarang atribut bisa digunakan. Penghapusan hanya bisa dilakukan untuk tupel pada satu tabel saja. Bila klausa WHERE tidak disebutkan maka semua tupel akan dihapus, dan tabel menjadi kosong. Contoh penghapusan pada tabel KARYAWAN untuk karyawan yang bernama ‘Bejo’ bisa dilihat pada U5. U5:

DELETE FROM KARYAWAN WHERE NamaKaryawan=’Bejo’; Banyaknya kondisi seleksi bisa beberapa dengan dihubungkan operator logika

AND, OR, NOT. Selain itu, kondisi seleksi bisa dikombinasikan dengan subquery, hal ini akan dipelajari pada sub bab 5.4.2.

5.2.3. Mengubah Nilai Atribut Nilai suatu atribut dari satu atau beberapa tupel dapat diubah dengan menggunakan perintah UPDATE. Bentuk perintahnya: UPDATE SET =<ekspresi nilai> {, =<ekspresi nilai>} [WHERE ]; Bila WHERE tidak dicantumkan maka yang terkena perubahan adalah semua tupel. Bila akan mengubah lokasi proyek nomor 1 menjadi ‘Medan’ dan nama proyeknya menjadi ‘Fly Over’ perintah yang diberikan adalah: U6:

UPDATE PROYEK SET LokasiProyek=’Medan’, NamaProyek=’Fly Over’ WHERE KodeProyek=1; Operator aritmatika juga bisa digunakan untuk update data, misalnya untuk

menaikkan gaji seluruh karyawan sebesar 10%, perintah yang diberikan adalah: U7:

UPDATE KARYAWAN SET Gaji=Gaji*1.1 Seperti halnya pada DELETE, banyaknya kondisi seleksi bisa beberapa dengan

dihubungkan operator logika AND, OR, NOT. Kondisi seleksi bisa dikombinasikan dengan subquery, hal ini akan dipelajari pada sub bab 5.4.3.

5.3. Query dalam SQL Untuk mencari informasi dalam suatu basis data, pengguna menuliskan querynya


83

dalam ekspresi SQL. Hasil query dalam SQL berbentuk tabel, yang mana sifatnya berbeda dengan relasi pada model relasional. Pada SQL suatu tabel dimungkinkan mempunyai dua atau lebih tupel yang nilai semua atributnya identik, sedangkan dalam aljabar relasional hal ini tidak dimungkinkan. SQL bukan merupakan himpunan tupel-tupel karena bisa mempunyai anggota yang sama (duplikasi), tetapi dapat diubah menjadi himpunan tupel dengan aturan tertentu.

5.3.1. Struktur Query SQL Bentuk umum pernyataan untuk mendapatkan informasi dari tabel adalah: SELECT FROM WHERE ; Pernyataan tersebut terdiri dari tiga klausa: 1. SELECT, yang digunakan untuk mendaftar satu atau lebih atribut yang digunakan dalam tabel hasil query. Daftar atribut dinyatakan dalam . Klausa ini sama dengan operasi proyeksi (PROJECT) dalam aljabar relasional. 2. FROM, digunakan untuk mendaftar tabel yang diperlukan untuk memproses query. Tabel yang dituliskan dalam bisa berupa tabel tunggal atau banyak tabel. Bila beberapa tabel yang dituliskan, maka semua relasi akan digabungkan menggunakan operasi Cartesian Product. 3. WHERE, yang berisi kondisi untuk syarat pencarian tupel dalam relasi yang tercantum di klausa FROM. Kondisi pencarian yang dituliskan dalam bisa berupa gabungan beberapa ekspresi yang dihubungkan dengan operator logika AND, OR, dan NOT. Klausa ini sama dengan operasi seleksi (SELECT) dalam aljabar relasional. SELECT dan FROM sifatnya wajib, sedangkan WHERE sifatnya opsional. Urutan penulisan juga harus dalam bentuk SELECT-FROM-WHERE. Sebagai contoh, untuk mencari nama dan alamat karyawan laki-laki dapat diberikan perintah seperti pada Q1. Hasilnya bila dijalankan di Microsoft Access bisa dilihat pada Gambar 5.3. Q1:

SELECT NamaKaryawan, Alamat FROM KARYAWAN WHERE JenisKlm=’L’;


84

(a)

(b)

Gambar 5.3. Hasil query Q1. (a) Dijalankan di Access, (b) Dijalankan di MySQL Query Q1 hanya melibatkan tabel KARYAWAN, melakukan seleksi tupel-tupel dalam KARYAWAN yang memenuhi kondisi di klausa WHERE, dan menampilkan hasilnya sebagai sebuah tabel dengan atribut yang disebutkan pada klausa SELECT. Q1 tersebut ekivalen dengan ekspresi berikut:

p

NamaKaryawan, Alamat (sJenisKlm=’L’

(KARYAWAN))

Query dengan relasi tunggal seperti Q1 serupa dengan operasi SELECT yang diikuti dengan PROJECT. Perbedaannya hanya bahwa dalam SQL bisa didapatkan tupeltupel yang sama kecuali diberikan aturan yang menyatakan bahwa suatu relasi adalah himpunan. Operator relasional yang bisa digunakan di WHERE adalah =, <> atau !=, <. <=, >, >=. Operator ini juga bisa dikombinasikan dengan NOT untuk mendapatkan negasinya. Misalkan untuk mencari karyawan yang tidak bekerja di Departemen dengan nomor departemen 5 bisa dituliskan dalam salah satu query berikut: Q2a:

SELECT NamaKaryawan, Alamat FROM KARYAWAN WHERE KodeDep_Kar <> 5;

Q2b:

SELECT NamaKaryawan, Alamat FROM Karyawan WHERE NOT (KodeDep_Kar = 5); Selain untuk data numerik, operator tersebut juga bisa digunakan untuk data teks

dan tanggal. Untuk data teks, pembandingan dilakukan per karakter, sehingga “Anik” akan lebih besar dari “Aliyudin” dan lebih kecil dari “Bob”. Sama halnya dengan “44” yang lebih kecil dari “5”. Untuk data tanggal, umumnya DBMS akan menganggapnya sebagai numerik sehingga tidak menimbulkan masalah dalam pembandingannya. Akan tetapi yang perlu diperhatikan adalah karakter apa yang mengapit data tanggal (karakter petik tunggal atau ‘#’) dan format data tanggal yang disimpan. Apabila klausa WHERE tidak dituliskan, maka berarti tidak ada kondisi yang Basis Data – Darmawan Satyananda

85

dikenakan terhadap semua tupel, sehingga semua tupel dari tabel di dalam FROM dipilih sebagai hasil query. Serupa dengan penghilangan klausa WHERE adalah pencantuman klausa WHERE TRUE yang maksudnya adalah semua baris dalam tabel adalah benar. Sebagai contoh untuk melihat daftar nama dan NIP semua karyawan, querynya bisa salah satu berikut ini: Q3a:

SELECT NamaKaryawan, NIP FROM KARYAWAN

Q3b:

SELECT NamaKaryawan, NIP FROM KARYAWAN WHERE TRUE Bila memerlukan data dari dua relasi atau lebih, maka dilakukan join dengan

menuliskan relasi yang akan dijoinkan pada klausa FROM dan menyebutkan kondisi joinnya pada klausa WHERE. Bentuk ini serupa dengan operasi SELECT–PROJECT–JOIN dalam Aljabar Relasional. Sebagai contoh, untuk mencari nama dan alamat semua karyawan yang berkerja pada departemen ‘Riset’, query yang harus diberikan adalah: Q4:

SELECT NamaKaryawan, Alamat FROM KARYAWAN, DEPARTEMEN WHERE NamaDepartemen=’Riset’ AND KodeDepartemen=KodeDep_Kar; Pada klausa WHERE query Q4, kondisi pertama adalah kondisi seleksi pada tabel

DEPARTEMEN (NamaDepartemen=’Riset’) dan kondisi kedua adalah kondisi join antara tabel DEPARTEMEN dan KARYAWAN (KodeDepartemen=KodeDep_Kar). Join dilakukan karena yang tercatat pada tabel KARYAWAN hanya kode departemen tempat karyawan bekerja, padahal yang diketahui di query adalah nama departemennya. Mulamula dicari tupel departemen ‘Riset’ untuk mengetahui kodenya. Selanjutnya tupel yang ditemukan lalu dijoinkan dengan tupel-tupel di KARYAWAN, hanya untuk tupel yang nilai atribut KodeDep_Kar sama dengan kode departemen ‘Riset’. Sembarang jumlah kondisi seleksi dan join dapat ditulis dalam sebuah query SQL. Bila banyaknya tabel adalah N, maka banyaknya kondisi join yang harus dituliskan adalah N-1 kondisi. Hasil dari join adalah sebuah tabel tunggal dengan atribut sesuai dengan atribut tabel yang dijoinkan (nama atribut dan banyaknya). Bila kondisi join tidak dituliskan maka tabel akan digabungkan dengan operasi Cartesian Product. Bila diinginkan untuk mendapatkan semua atribut dari tupel yang dipilih, maka gunakan ‘*’ (asterisk) pada klausa SELECT. Contohnya untuk mendapatkan semua atribut karyawan yang bekerja pada Departemen 5: Q5:

SELECT * FROM KARYAWAN WHERE KodeDep_Kar=5;

Bila yang ada di FROM lebih dari sebuah tabel, maka semua atribut dari tabel yang digunakan akan ditampilkan.


86

5.3.2. Tabel Pengenal dan Operator Alias Beberapa buah tabel mungkin menggunakan nama atribut yang sama. Untuk menghindari adanya kesalahan, maka di depan atribut bisa dituliskan nama tabel pengenalnya (identifier atau qualifier) dengan dipisah tanda titik. Misalkan query untuk mencari karyawan laki-laki yang mempunyai tanggungan laki-laki. Karena relasi KARYAWAN dan TANGGUNGAN sama-sama memiliki atribut JenisKlm, maka perlu dituliskan relasi pengenalnya seperti berikut ini: Q6:

SELECT NamaKaryawan, NamaTanggungan, KARYAWAN.JenisKlm AS Jenis_Kelamin FROM KARYAWAN, TANGGUNGAN WHERE NIP=NIP_Kar AND KARYAWAN.JenisKlm=’L’ AND TANGGUNGAN.JenisKlm=’L’; Pada dasarnya nama atribut dari tabel hasil query sama dengan nama atribut

pada tabel asalnya, tetapi dimungkinkan untuk memberi nama yang baru kepada suatu atribut (alias) dengan menggunakan operator AS. Pada Q6 atribut JenisKlm diberi alias ‘JenisKelamin’ untuk memudahkan pembacaan. Alias bukan merupakan keharusan. Alias juga bisa digunakan bila kolom yang ditampilkan adalah hasil dari ekspresi aritmatika. Ekspresi aritmatika dikenakan terhadap atribut yang bertipe numerik dengan menggunakan operator aritmatika ‘+’, ‘-‘, ‘*’, dan ‘/’. Misalkan untuk menampilkan bonus gaji 10% untuk karyawan yang bekerja di proyek ‘Komputerisasi’, querynya dapat ditulis sebagai berikut: Q7:

SELECT NamaKaryawan, Gaji, 1.1*Gaji AS Gaji_Baru FROM KARYAWAN, BEKERJA_DI, PROYEK WHERE NIP=NIP_Kar AND KodeProyek=KodeProyek_Kar AND NamaProyek=’Komputerisasi’;

Gambar 5.4 Tabel hasil query Q7. Penamaan ulang juga dapat dilakukan pada tabel, dengan maksud untuk menyingkat penulisan atau bila melibatkan tabel yang sama dalam relasi rekursif. Misalnya melakukan pencarian nama karyawan dan nama manajernya, query yang diberikan adalah: Q8:

SELECT K.NamaKaryawan, P.NamaKaryawan AS Nama_Pengawas FROM KARYAWAN AS K, KARYAWAN AS P WHERE K.NIP_Pengawas=P.NIP;

Karena seorang pengawas adalah juga merupakan karyawan, maka untuk mencari nama


87

pengawas harus dilakukan pengacuan ke tabel KARYAWAN. Relasi rekursif dalam SQL ditunjukkan dengan penggunaan salinan tabel yang sama, dengan perannya masingmasing. Pada Q8, tabel KARYAWAN mempunyai nama alias K dan P. Keduanya merupakan salinan dari relasi KARYAWAN, K dalam peran sebagai karyawan dan P sebagai pengawas, sehingga dapat dikenali oleh operasi JOIN (K dan P disebut juga sebagai variabel tupel). JOIN dilakukan terhadap tabel KARYAWAN dengan dirinya sendiri dengan mencocokkan tupel yang memenuhi kondisi JOIN. Contoh penamaan dengan maksud menyingkat penulisan, tanpa bergantung apakah relasi yang sama diacu lebih dari satu kali atau tidak, dapat dilihat pada query berikut yang merupakan bentuk lain dari query Q4: Q4a:

SELECT D.NamaDepartemen, K.NamaKaryawan, K.Alamat FROM KARYAWAN AS K, DEPARTEMEN AS D WHERE D.NamaDepartemen=’Riset’ AND D.KodeDepartemen=K.KodeDep_Kar;

5.3.3. Penghilangan Tupel Ganda Query SQL mungkin memberikan hasil tupel yang ganda. Untuk menghilangkan duplikasi, tambahkan DISTINCT ke dalam klausa SELECT yang berarti bahwa hanya ada tupel tunggal dalam relasi (tupel yang sama dihilangkan). Query berikut menunjukkan perbedaan antara query dengan DISTINCT dan tanpa DISTINCT. Q9:

SELECT Gaji FROM KARYAWAN;

Q9a:

SELECT DISTINCT Gaji FROM KARYAWAN;

(a)

(b)

Gambar 5.5. Relasi hasil query: (a) Q9, (b) Q9A. Lawan dari DISTINCT adalah ALL, yang artinya semua tupel akan ditampilkan. Tetapi karena secara default SQL memperbolehkan adanya duplikasi maka penambahan ALL dalam klausa SELECT dapat diabaikan, seperti terlihat pada Q8. Bila dituliskan maka klausa SELECT menjadi: SELECT ALL GAJI.


88

5.3.4. Operator Khusus untuk WHERE SQL menyediakan beberapa operator yang digunakan untuk WHERE yaitu BETWEEN, LIKE, IN, IS NULL, dan EXISTS. Khusus EXISTS akan dijelaskan pada saat pembahasan subquery. Operator BETWEEN digunakan untuk mencari apakah nilai suatu atribut dalam rentang nilai tertentu. Pencarian karyawan yang mempunyai gaji antara 1 juta sampai 2 juta (inklusif) dapat dilakukan dengan query: Q10:

SELECT NamaKaryawan, Gaji FROM KARYAWAN WHERE (Gaji BETWEEN 1000000 AND 2000000); LIKE adalah operator yang digunakan untuk mengetahui apakah nilai suatu

atribut bertipe string memenuhi pola string atau karakter tertentu. Operator LIKE dikombinasikan dengan wildcard ‘%’ (percent, yang mewakili sederetan karakter) dan ‘_’ (underscore, yang mewakili sebuah karakter). Contoh: untuk mencari semua karyawan yang alamatnya mengandung string ‘Malang’, query yang dituliskan adalah: Q11:

SELECT NamaKaryawan FROM KARYAWAN WHERE Alamat LIKE ‘%Malang%’;

Sedangkan untuk mencari karyawan yang digit ketiga NIP-nya adalah angka ‘3’, querynya bisa dalam bentuk: Q12:

SELECT NamaKaryawan FROM KARYAWAN WHERE NIP LIKE ‘__3____’;

Karakter ‘%’ digunakan bila karakter yang diwakilinya tidak tentu banyaknya, sedangkan karakter ‘_’ digunakan untuk mewakili karakter pada posisi tertentu. Microsoft Access menggunakan karakter ‘*’ sebagai ganti karakter ‘%’, dan karakter ‘?’ sebagai ganti karakter ‘_’. Operator IN digunakan untuk mencari apakah nilai suatu atribut ada di dalam himpunan nilai lain yang bertipe sama. Operator ini akan membandingkan nilai v dengan himpunan nilai V, dan akan bernilai TRUE bila v adalah salah satu elemen di V. Himpunan nilai bisa ditentukan secara eksplisit atau didapat dari subquery (akan dibahas pada subbab tentang subquery). Contohnya untuk mencari NIP semua karyawan yang bekerja di proyek nomor 1 atau 2 atau 3, query yang bisa diberikan adalah: Q13:

SELECT DISTINCT NIP_Kar FROM BEKERJA_DI WHERE (KodeProyek_Kar=1) OR (KodeProyek_Kar=2) OR (KodeProyek_Kar=3);

Q13a:

SELECT DISTINCT NIP_Kar FROM BEKERJA_DI WHERE KodeProyek_Kar IN (1, 2, 3); SQL juga mempunyai operator untuk menguji apakah suatu atribut bernilai NULL


89

atau tidak, yaitu operator IS NULL. Pengujian bukan dilakukan dengan = atau ¹ karena NULL bukanlah suatu nilai tetapi kondisi yang menyatakan bahwa suatu atribut tidak memiliki nilai. Contohnya untuk mencari nama semua karyawan yang tidak mempunyai pengawas, querynya bisa ditulis: Q14:

SELECT NamaKaryawan FROM KARYAWAN WHERE NIP_Pengawas IS NULL; Semua operator yang disebutkan di atas bisa dikombinasikan dengan NOT untuk

mendapatkan kondisi sebaliknya. Jadi anda bisa menggunakan NOT BETWEEN, NOT LIKE, NOT IN, dan IS NOT NULL.

5.3.5. Pengurutan Tupel Hasil query secara default tidak dalam keadaan terurut. Pengurutan tupel bisa dilakukan berdasar satu atau lebih atribut dengan menggunakan klausa ORDER BY. Misalkan untuk menampilkan daftar nama karyawan, nama departemen, dan nama proyek tempat karyawan bekerja yang diurutkan berdasar nama departemennya, query yang ditulis adalah: Q15:

SELECT FROM WHERE

NamaDepartemen, NamaKaryawan, NamaProyek DEPARTEMEN, KARYAWAN, BEKERJA_DI, PROYEK KodeDepartemen=KodeDep_Kar AND NIP=NIP_Kar AND KodeProyek=KodeProyek_Kar ORDER BY NamaDepartemen, NamaKaryawan;

Bila ada lebih dari satu atribut yang digunakan sebagai dasar pengurutan, maka pengurutan dilakukan berdasar atribut pertama. Bila ada data yang sama maka diurutkan berdasar atribut kedua, dan seterusnya. Jenis pengurutan default adalah ascending (urut naik), tetapi dapat diubah menjadi descending (urut turun) dengan menambahkan DESC pada klausa ORDER BY. Untuk ascending bisa juga ditambahkan ASC, tetapi bukan merupakan keharusan. Bila dari Q15 akan ditampilkan nama departemen dalam urutan menurun dan nama karyawan dalam urutan menaik, klausa ORDER BY dapat ditulis sebagai berikut: Q15a:

SELECT FROM WHERE

NamaDepartemen, NamaKaryawan, NamaProyek DEPARTEMEN, KARYAWAN, BEKERJA_DI, PROYEK KodeDepartemen=KodeDep_Kar AND NIP=NIP_Kar AND KodeProyek=KodeProyek_Kar ORDER BY NamaDepartemen DESC, NamaKaryawan ASC;

5.3.6. Tabel Sebagai Himpunan Tabel hasil query (baik yang mempunyai tupel ganda maupun tidak) dapat dikenai operasi himpunan UNION, EXCEPT, INTERSECT dengan tabel lainnya, dengan syarat bahwa keduanya harus union compatible (nama atributnya sama, tipe datanya sama atau kompatibel, dan muncul dengan urutan yang sama). Operasi ini sama dengan operasi È, -, Ç dalam aljabar relasional. Hasil dari operasi himpunan adalah tabel yang Basis Data – Darmawan Satyananda

90

menghilangkan tupel ganda (hanya menampilkan satu tupel dari tupel yang sama). Query untuk mencari nama semua karyawan berjenis kelamin laki-laki atau yang mempunyai tanggungan laki-laki adalah: Q16:

(SELECT FROM WHERE UNION (SELECT FROM WHERE

NamaKaryawan KARYAWAN JenisKlm=’L’) NamaKaryawan KARYAWAN, TANGGUNGAN NIP=NIP_Kar AND TANGGUNGAN.JenisKlm=’L’);

Query sebelum UNION bertujuan mencari karyawan yang laki-laki. Query setelah UNION bertujuan mencari anggota keluarganya (tanggungannya) yang laki-laki. Join yang terjadi pada query kedua adalah antara tabel KARYAWAN dan tabel TANGGUNGAN, untuk mencari siapa saja tanggungan yang berjenis kelamin laki-laki dari setiap karyawan. Selanjutnya keduanya digabungkan dengan UNION. Contoh penggunaan INTERSECT adalah pada permasalahan: mencari karyawan yang bekerja di proyek dengan kode 1 dan 3 sekaligus. Q17:

(SELECT NamaKaryawan FROM KARYAWAN, BEKERJA_DI WHERE NIP=NIP_Kar AND KodeProyek_Kar=1) INTERSECT (SELECT NamaKaryawan FROM KARYAWAN, BEKERJA_DI WHERE NIP=NIP_Kar AND KodeProyek_Kar=3); EXCEPT pada beberapa DBMS dikenal dengan MINUS. Contoh penggunaannya

adalah pada query untuk mencari karyawan yang bekerja di proyek dengan kode 1 tapi tidak bekerja di proyek 3. Q18:

(SELECT FROM WHERE MINUS (SELECT FROM WHERE

NamaKaryawan KARYAWAN, BEKERJA_DI NIP=NIP_Kar AND KodeProyek_Kar=1) NamaKaryawan KARYAWAN, BEKERJA_DI NIP=NIP_Kar AND KodeProyek_Kar=3);

Selain operasi di atas, juga bisa digunakan UNION ALL, INTERSECT ALL, dan EXCEPT ALL yang dapat menampilkan tupel yang mempunyai duplikat.

Catatan: Tidak semua DBMS mendukung operasi himpunan ini. Access dan MySQL hanya bisa menggunakan UNION, sehingga untuk INTERSECT dan EXCEPT harus digunakan alternatif lain dengan IN dan NOT IN. 5.3.7. SubQuery, Suatu relasi hasil query dapat dijadikan sebagai input untuk query yang lain, yang dituliskan dalam bentuk query tersarang (nested queries). Query yang memberikan input disebut query dalam (inner query) dan yang menerima input disebut query luar Basis Data – Darmawan Satyananda

91

(outer query). Query dalam disebut juga sebagai subquery dan bisa digunakan pada klausa WHERE, FROM, SELECT, dan HAVING, serta pada DML yaitu pada INSERT, UPDATE, dan DELETE. Berikut ini adalah contoh query yang mengandung subquery di dalamnya: Q4b:

SELECT NamaKaryawan, Alamat FROM KARYAWAN WHERE KodeDep_Kar=(SELECT KodeDepartemen FROM DEPARTEMEN WHERE NamaDepartemen=’Riset’);

Bagian yang diapit kurung adalah subquery atau query dalam, selain itu adalah query luar. Query dalam akan dieksekusi lebih dulu, dan hasilnya digunakan oleh query luar. Hal ini berbeda dengan query yang tidak melibatkan subquery, misalnya pada query untuk men-join-kan sejumlah relasi. Pada query semacam itu, sejumlah relasi akan diproses secara serentak dan baris yang memenuhi kondisi join saja yang ditampilkan. Pada pernyataan SQL yang mengandung subquery, suatu baris akan diproses dan ditampilkan berdasar hasil dari baris lain yang diproses lebih dulu.

5.3.7.1. Nilai yang Dihasilkan Subquery Subquery bisa menghasilkan kemungkinan nilai berikut (Rob, 2004: 336): ·

Satu nilai tunggal, subquery semacam ini digunakan pada pembandingan dengan atribut atau nilai tunggal yang lain (ingat bahwa suatu atribut hanya bisa berisi satu nilai tunggal). Umumnya menggunakan fungsi agregat (penjelasan mengenai fungsi agregat ada di sub bab selanjutnya).

·

Sekumpulan nilai, subquery semacam ini digunakan dengan operator IN untuk mengetahui apakah nilai suatu atribut ada dalam himpunan nilai yang dihasilkan subquery

·

Virtual table, subquery semacam ini biasanya digunakan pada klausa FROM, dan berfungsi sebagai tabel yang berisi baris data. Contoh subquery yang menghasilkan nilai tunggal adalah Q4b di atas yang

merupakan alternatif dari Q4. Pada Q4b, subquery hanya menghasilkan satu nilai kode departemen (satu baris dan satu kolom) dan selanjutnya dibandingkan dengan setiap tupel pada query luar, apakah kode departemen tempat karyawan bekerja sama dengan kode departemen yang dihasilkan oleh subquery. Bila kondisi ini terpenuhi maka tupel di query luar saat itu dijadikan sebagai output. Contoh query yang bisa memanfaatkan sekumpulan nilai yang dihasilkan subquery adalah query Q6. Alternatif penulisan query Q6 adalah sebagai berikut: Q6a:

SELECT DISTINCT NamaKaryawan FROM KARYAWAN WHERE JenisKlm=’L’ AND NIP IN (SELECT NIP_Kar


92

FROM WHERE

TANGGUNGAN JenisKlm=’L’);

Subquery pada Q6a menghasilkan sejumlah NIP yang memiliki tanggungan laki-laki. Selanjutnya untuk setiap tupel relasi pada query luar akan diperiksa apakah seorang karyawan adalah laki-laki atau NIPnya ada di dalam himpunan yang dihasilkan oleh subquery. Di sini digunakan operator perbandingan IN untuk membandingkan satu nilai dengan sekumpulan nilai. Perhatikan bahwa atribut sebelum IN (di query luar) harus semakna dengan atribut yang disebutkan setelah IN (di dalam subquery). Akan tetapi dengan cara ini atribut subquery tidak bisa ditampilkan sebagai output karena relasinya tidak terdaftar pada klausa FROM query luar. Q6a juga menunjukkan alternatif penulisan kondisi join pada Q6. Query Q6 juga bisa dijadikan contoh penulisan subquery pada klausa FROM. Perhatikan penulisan berikut: Q6b:

SELECT DISTINCT NamaKaryawan FROM (SELECT NIP,NamaKaryawan FROM KARYAWAN WHERE JenisKlm=’L’) AS KARLAKI, (SELECT NIP_Kar FROM TANGGUNGAN WHERE JenisKlm=’L’) AS TANGLAKI WHERE KARLAKI.NIP=TANGLAKI.NIP_Kar;

Subquery pada klausa FROM akan menghasilkan tabel maya (virtual table) yang berisi sejumlah tupel. Tabel ini diperlakukan sama dengan tabel dasar, dimana atributnya bisa digunakan pada WHERE. Atribut yang akan ditampilkan di SELECT query luar harus sudah disebutkan pada tabel maya.

5.3.7.2. Subquery yang Berkorelasi Contoh subquery sebelum ini adalah query yang dieksekusi secara berurutan. Mula-mula query dalam dieksekusi, lalu hasilnya dimanfaatkan query luar. Antara query dalam dan luar tidak ada hubungan langsung. Tetapi ada kasus dimana keduanya saling berhubungan, dalam bentuk atribut query dalam mengacu ke atribut query luar. Sebagai contoh adalah query untuk mencari nama setiap karyawan yang mempunyai jenis kelamin yang sama dengan tanggungannya. Q19:

SELECT K.NamaKaryawan FROM KARYAWAN AS K WHERE NIP IN (SELECT NIP_Kar FROM TANGGUNGAN WHERE NIP_Kar=NIP AND JenisKlm=K.JenisKlm);

Untuk mendapatkan hasilnya, satu tupel dari tabel pada query luar diambil, kemudian nilai atribut yang sesuai digunakan untuk mendapatkan hasil query dalam. Setelah mendapatkan hasil, maka barulah dilakukan pembandingan antara nilai atribut di klausa WHERE query luar dengan himpunan nilai dari query dalam. Query pada keadaan seperti ini (kondisi di klausa WHERE pada subquery mengacu ke atribut di query luar) dikatakan Basis Data – Darmawan Satyananda

93

berkorelasi (correlated). Dalam keadaan berkorelasi, relasi yang terlibat mungkin memiliki nama atribut yang sama. Untuk menghindari kesalahan, ada aturan yang menyatakan bahwa acuan ke atribut yang tidak disebutkan pengenalnya (unqualified attribute) akan mengacu kepada relasi yang dideklarasikan pada subquery atau query tersarang yang paling dalam (innermost nested query). Pengacuan kepada atribut di luar subquery dilakukan dengan menggunakan alias. Perhatikan Q19, kedua relasi mempunyai atribut yang sama yaitu JenisKlm. Atribut yang tidak disebutkan relasinya diarahkan kepada relasi TANGGUNGAN sebagai relasi yang paling dalam.

5.3.7.3. Subquery untuk Operasi Himpunan Seperti yang telah disebutkan, IN juga bisa digunakan sebagai alternatif dari INTERSECT sedangkan NOT IN sebagai alternatif dari MINUS. Query Q17 bila dituliskan menggunakan IN adalah sebagai berikut: Q17a:

SELECT NamaKaryawan FROM KARYAWAN, BEKERJA_DI WHERE NIP=NIP_Kar AND KodeProyek_Kar=1 AND NIP_Kar IN (SELECT NIP_Kar FROM BEKERJA_DI WHERE KodeProyek_Kar=3);

Perhatikan juga bahwa kita bisa tuliskan bentuk yang lebih panjang untuk menggantikan kondisi join: Q17b:

SELECT NamaKaryawan FROM KARYAWAN WHERE NIP IN (SELECT NIP_Kar FROM BEKERJA_DI WHERE KodeProyek_Kar=1) AND NIP IN (SELECT NIP_Kar FROM BEKERJA_DI WHERE KodeProyek_Kar=3); Sedangkan query Q18 untuk mencari karyawan yang hanya bekerja di proyek

nomor 1 tetapi tidak pada proyek nomor 3, bisa ditulis sebagai berikut: Q18a:

SELECT NamaKaryawan FROM KARYAWAN, BEKERJA_DI WHERE NIP=NIP_Kar AND KodeProyek_Kar=1 AND NIP_Kar NOT IN (SELECT NIP_Kar FROM BEKERJA_DI WHERE KodeProyek_Kar=3);

5.3.7.4. Operator ANY dan ALL Operator IN hanya mencari baris yang nilai atributnya sesuai (atau yang sama nilainya) setidaknya dengan satu nilai pada sekumpulan nilai yang lain. Operator ini tidak bisa digunakan untuk membandingkan nilai yang lebih besar atau lebih kecil.


94

Untuk keperluan itu, digunakan operator ANY (atau SOME) dan ALL. ANY akan bernilai true bila nilai sebuah atribut memenuhi kondisi tertentu terhadap beberapa (setidaknya satu) nilai dalam suatu kumpulan nilai. ALL akan bernilai true bila nilai sebuah atribut memenuhi kondisi tertentu terhadap semua nilai dalam suatu kumpulan nilai. Kondisi dinyatakan dengan operator perbandingan. Query untuk mencari karyawan dengan gaji tertinggi bisa dinyatakan dengan: Q20:

SELECT NamaKaryawan, Gaji FROM KARYAWAN WHERE Gaji >= ALL (SELECT Gaji FROM KARYAWAN );

Pada Q20, sebuah tupel akan dipilih bila nilai atribut Gaji pada tupel tersebut lebih besar atau sama dengan semua nilai yang dihasilkan oleh subquery. Bila ALL pada Q20 diganti dengan ANY maka tupel yang dihasilkan adalah semua tupel pada tabel KARYAWAN karena gaji seorang karyawan pasti lebih besar dari beberapa karyawan yang lain atau paling tidak sama dengan gajinya sendiri. Apa yang terjadi bila kondisi di WHERE adalah “Gaji > ALL”?

5.3.7.5. Operator EXISTS Operator

EXISTS

digunakan

untuk

menguji

apakah

suatu

subquery

menghasilkan tupel (tidak kosong). Bila ada tupel yang dihasilkan maka tupel pada query luar saat itu akan dijadikan sebagai output. Q19 bisa ditulis sebagai berikut: Q19a:

SELECT NamaKaryawan FROM KARYAWAN AS K WHERE EXISTS (SELECT * FROM TANGGUNGAN WHERE NIP_Kar=NIP AND JenisKlm=K.JenisKlm);

Urutan pengerjaannya sama dengan yang telah dijelaskan pada Q19. Operator EXISTS akan mengembalikan nilai TRUE bila terdapat paling tidak sebuah tupel sebagai hasil query Q, dan FALSE bila sebaliknya. Sepintas EXISTS mirip dengan IN, tetapi perhatikan bahwa yang dibandingkan pada IN adalah nilai yang sama (semakna) sedangkan EXISTS tidak membandingkan nilai tetapi hanya melihat apakah ada tupel yang dihasilkan oleh query dalam untuk setiap tupel query luar saat itu. Kebalikan dari EXISTS adalah NOT EXISTS, yang menghasilkan TRUE bila tidak ada tupel sebagai hasil subquery Q dan FALSE bila sebaliknya. Contoh query untuk mencari nama karyawan yang tidak mempunyai tanggungan: Q21:

SELECT NamaKaryawan FROM KARYAWAN WHERE NOT EXISTS (SELECT * FROM TANGGUNGAN WHERE NIP_Kar=NIP);

Q21 menampilkan nama seorang karyawan bila subquery yang berkorelasi dengan query


95

luar tidak menghasilkan tupel. EXISTS bisa saja digunakan pada subquery yang tidak berkorelasi tetapi tidak akan memberikan makna apapun karena semua tupel pada query luar akan ditampilkan (bila subquery memberikan hasil) atau tidak samasekali (bila subquery tidak memberikan hasil).

5.3.8. JOIN Secara umum, join beberapa tabel bisa dikelompokkan menjadi 3: (a) Cross join yang menghasilkan Cartesian product, (b) Inner join yang menghasilkan tupel yang memenuhi kondisi join, dan (c) Outer join yang menampilkan semua tupel, baik memenuhi kondisi join maupun tidak. Untuk semua jenis join tersebut akan disajikan operasi yang sesuai dengan standar ANSI SQL, tetapi tidak semuanya didukung oleh DBMS yang ada. Q4 menunjukkan cara “tradisional” menggabungkan dua tabel, yaitu dengan menyebutkan tabel yang akan digabungkan pada klausa FROM dan menyebutkan kondisi join pada klausa WHERE. Bila kondisi join tidak disebutkan, maka yang terjadi adalah Cartesian product.

5.3.8.1. Cross Join Seperti yang telah disebutkan, cross join menghasilkan Cartesian product. SQL menyediakan operator CROSS JOIN untuk keperluan ini. Sebagai contoh, dua query berikut ini adalah sama: Q22:

SELECT * FROM KARYAWAN, DEPARTEMEN;

Q22a:

SELECT * FROM KARYAWAN CROSS JOIN DEPARTEMEN;

5.3.8.2. Inner Join SQL menyediakan beberapa operator yang termasuk ke dalam kelompok ini, yaitu: JOIN … ON, JOIN ... USING, dan NATURAL JOIN. Operator pertama adalah JOIN … ON. Yang dituliskan setelah ON adalah kondisi joinnya, lengkap dengan qualifier (nama tabel pengenal). Selain dituliskan dengan JOIN … ON, bisa juga dituliskan sebagai INNER JOIN … ON. Access hanya mendukung cara penulisan yang terakhir, sedangkan MySQL mendukung semuanya. Q4 dapat ditulis ulang menjadi sebagai berikut: Q4c:

SELECT NamaKaryawan, Alamat FROM KARYAWAN JOIN DEPARTEMEN ON (DEPARTEMEN.KodeDepartemen= KARYAWAN.KodeDep_Kar) WHERE NamaDepartemen=’Riset’; Cara berikutnya adalah dengan menggunakan keyword USING. Query ini akan


96

mengembalikan baris tabel yang memiliki nilai yang sama untuk atribut yang disebutkan di USING. USING mensyaratkan tabel yang dijoinkan memiliki atribut dengan nama yang sama. Dari contoh basis data PERUSAHAAN kita, tabel BEKERJA_DI dan TANGGUNGAN memiliki atribut yang sama yaitu NIP_Kar. Bila querynya adalah untuk mencari karyawan yang bekerja di proyek dan memiliki tanggungan, maka bisa dituliskan sebagai berikut: Q23:

SELECT NIP_Kar, NamaTanggungan FROM BEKERJA_DI INNER JOIN TANGGUNGAN USING (NIP_Kar); Yang terakhir adalah NATURAL JOIN. Operator ini tidak menyebutkan kondisi

join, join dilakukan berdasar nama atribut yang sama (jadi mirip dengan JOIN … USING). Q23 dapat ditulis menjadi: Q23a:

SELECT NIP_Kar, NamaTanggungan FROM BEKERJA_DI NATURAL JOIN TANGGUNGAN; Join juga bisa dilakukan terhadap hasil join yang lain dengan menggunakan

berbagai operator join. Misalkan untuk mencari nomor proyek, departemen pengontrol dan nama manajer dari semua proyek yang berlokasi di Jakarta, querynya adalah sebagai berikut: Q24:

SELECT KodeProyek, KodeDep_Proyek, NamaKaryawan FROM ((PROYEK INNER JOIN DEPARTEMEN ON PROYEK.KodeDep_Proyek= DEPARTEMEN.KodeDepartemen) INNER JOIN KARYAWAN ON DEPARTEMEN.NIP_Manajer=KARYAWAN.NIP) WHERE LokasiProyek="Jakarta";

Pada Q24 mula-mula PROYEK dijoinkan dengan DEPARTEMEN untuk mengetahui nomor departemen pengontrol suatu proyek, lalu hasilnya dijoinkan dengan KARYAWAN untuk mengetahui nama manajernya.

5.3.8.3. Outer Join Operator yang disediakan oleh SQL dalam kelompok ini adalah LEFT JOIN, RIGHT JOIN, dan FULL JOIN (ketiganya bisa juga digunakan dengan menambahkan OUTER menjadi: LEFT OUTER JOIN, RIGHT OUTER JOIN, dan FULL OUTER JOIN). LEFT JOIN akan menampilkan semua tupel pada tabel pertama dan kedua yang sesuai dengan kondisi join. Bila ada yang tidak sesuai, maka tupel pada tabel pertama akan ditampilkan berpasangan dengan nilai NULL. RIGHT JOIN menampilkan semua tupel pada tabel kedua dan tabel pertama yang memenuhi kondisi join, serta memasangkan nilai NULL dengan tupel pada tabel kedua bila tidak memenuhi kondisi join. FULL JOIN menampilkan semua tupel dari tabel pertama dan kedua yang memenuhi kondisi join, serta memasangkan nilai NULL dengan tupel pada tabel pertama atau kedua bila tidak memenuhi kondisi join. Keuntungan penggunaan ketiga operator ini adalah tidak adanya informasi yang hilang karena semua tupel ditampilkan.


97

Contoh LEFT JOIN adalah untuk menampilkan NIP semua karyawan dan kode proyek tempatnya bekerja, termasuk karyawan yang tidak bekerja di proyek manapun. Q25:

SELECT NIP, NamaKaryawan, KodeProyek_Kar FROM KARYAWAN LEFT JOIN BEKERJA_DI ON KARYAWAN.NIP=BEKERJA_DI.NIP_Kar; Sebaliknya, untuk menampilkan daftar semua proyek yang ada dan nama

karyawan yang bekerja di proyek tersebut, bisa digunakan RIGHT JOIN sebagai berikut: Q26:

SELECT KodeProyek_Kar, NIP, NamaKaryawan FROM BEKERJA_DI RIGHT JOIN KARYAWAN ON KARYAWAN.NIP=BEKERJA_DI.NIP_Kar ORDER BY KodeProyek_Kar;

(a)

(b)

Gambar 5.6 Hasil query Q24 dan Q25 pada Microsot Access.

Catatan: Perhatikan benar JOIN seperti apa yang didukung oleh DBMS yang anda gunakan. Dari semua operator join yang disebutkan di atas, sampai tulisan ini dibuat, Access tidak mendukung CROSS JOIN, NATURAL JOIN, dan FULL JOIN. MySQL tidak mendukung FULL JOIN. 5.3.9. Fungsi Agregat dan Pengelompokan Konsep fungsi agregat yang ada di SQL sama dengan yang ada di Aljabar Relasional. Fungsi-fungsi tersebut digunakan untuk perhitungan matematis, dikenakan terhadap sekelompok data, dan dituliskan sebagai bagian dari klausa SELECT. Fungsifungsi ini juga tidak bisa digunakan bercampur dengan atribut biasa kecuali bila dikelompokkan menurut atribut tertentu.

5.3.9.1. Fungsi Agregat Fungsi agregat yang serupa dengan yang ada di aljabar relasional adalah COUNT, SUM, MAX, MIN, dan AVG. Fungsi ini digunakan untuk melakukan perhitungan terhadap sejumlah tupel dalam tabel, dan akan menghasilkan sebuah tupel Basis Data – Darmawan Satyananda

98

yang berisi hasil perhitungan. Output fungsi ini hanya satu tupel, bukan sekumpulan tupel. Contoh query untuk mencari banyaknya pekerja, jumlah gaji semua pekerja, gaji terbesar, gaji terkecil, dan rata-ratanya adalah: Q27:

SELECT COUNT (NIP), SUM(Gaji), MAX(Gaji), MIN(Gaji), AVG(Gaji) FROM KARYAWAN;

Gambar 5.7. Hasil query Q27 pada Microsot Access. Tampak pada gambar 5.7 judul kolom diawali dengan ‘Expr…’. Agar lebih komunikatif, maka sebaiknya judul kolom diberi nama alias dengan menggunakan AS. Contoh penggunaan COUNT yang lain, misalnya mencari banyaknya karyawan yang bekerja pada departemen ‘Riset’. Q28:

SELECT COUNT(*) FROM KARYAWAN, DEPARTEMEN WHERE KodeDep_Kar=KodeDepartemen AND NamaDepartemen="Riset";

Parameter COUNT bisa berupa atribut tabel atau tanda asterisk (*). Bila digunakan dengan atribut maka hanya akan menghitung tupel yang atributnya tersebut berisi nilai dan mengabaikan yang nilainya NULL, sedangkan asterisk digunakan untuk menghitung banyaknya tupel tanpa melihat atributnya dan tanpa melihat apakah ada nilai NULL di dalamnya.

Dengan

demikian

lebih

disarankan

menggunakan

COUNT

dengan

menyebutkan atribut untuk menghindari penghitungan yang tidak perlu karena adanya NULL. Asterisk hanya bisa digunakan di COUNT. Fungsi lainnya harus menyebutkan atributnya. Semua fungsi yang disebutkan di atas akan menghitung semua tupel termasuk yang ganda, sehingga untuk menghindari hasil yang kurang tepat, gunakan DISTINCT. Jadi query untuk mencari berapa orang karyawan yang menjadi pengawas adalah sebagai berikut: Q29:

SELECT COUNT(DISTINCT NIP_Pengawas) FROM KARYAWAN;

Hasilnya adalah 3 orang saja yang menjadi pengawas. Bila anda tuliskan “SELECT COUNT(NIP_Pengawas) FROM KARYAWAN” maka hasilnya adalah 5, yang justru menyatakan berapa orang karyawan yang diawasi. Hal ini terjadi karena seorang pengawas bisa mengawasi lebih dari 1 karyawan. Di Access, DISTINCT tidak bisa digunakan di dalam COUNT, sedangkan MySQL mendukungnya. Akibatnya kita harus buat subquery dengan menggunakan DISTINCT Basis Data – Darmawan Satyananda

99

dan NOT NULL. Query yang ekivalen dengan Q29 adalah: Q29a:

SELECT COUNT(*) FROM (SELECT DISTINCT NIP_Pengawas FROM KARYAWAN WHERE NIP_Pengawas IS NOT NULL); Berkaitan dengan tipe datanya, SUM dan AVG hanya bisa untuk data numerik,

sedangkan fungsi yang lain bisa untuk sembarang jenis data. Contohnya untuk mencari nama karyawan yang paling awal dan paling akhir muncul di urutan nama karyawan, bila kita anggap datanya terurut, adalah sebagai berikut: Q30:

SELECT MIN(NamaKaryawan) AS AWAL, MAX(NamaKaryawan) AS AKHIR FROM KARYAWAN

Gambar 5.8 Hasil dari MIN dan MAX pada data bertipe teks. Pada Gambar 5.8, judul kolom diberi nama ulang dengan ‘AWAL’ dan ‘AKHIR’. Bandingkan dengan Q27 dan Gambar 5.7. Fungsi juga dapat dikenakan pada kondisi seleksi. Query untuk mencari nama semua karyawan yang mempunyai 2 atau lebih tanggungan adalah sebagai berikut: Q31:

SELECT NamaKaryawan FROM KARYAWAN WHERE (SELECT COUNT(*) FROM TANGGUNGAN WHERE NIP_Kar=NIP) >= 2;

Subquery akan menghitung banyaknya tanggungan setiap karyawan sesuai dengan NIP dari tupel aktif di tabel karyawan saat itu. Bila nilainya lebih besar dari 2 maka tupel tersebut akan dipilih. Hal ini dimungkinkan karena subquery hanya menghasilkan satu tupel dan satu record saja sehingga bisa langsung dibandingkan. Pada sub bab 5.3.7 menganai subquery, disebutkan bahwa subquery bisa diletakkan di SELECT. Umumnya digunakan bersama fungsi agregat, misalnya mencari selisih gaji setiap karyawan dengan gaji yang tertinggi. Q32:

SELECT NamaKaryawan, (SELECT MAX(Gaji) FROM KARYAWAN) AS GajiTertinggi, Gaji, (SELECT MAX(Gaji) FROM KARYAWAN)-Gaji AS Selisih FROM KARYAWAN;

Pada SELECT kita tuliskan subquery untuk mencari berapa gaji tertinggi dan menghitung selisihnya dengan gaji setiap karyawan. Penghitungan selisih dilakukan dengan menuliskan ulang subquerynya. Akan tetapi di Access diperbolehkan bila dituliskan sebagai berikut: Basis Data – Darmawan Satyananda

100

Q32a:

SELECT NamaKaryawan, (SELECT MAX(Gaji) FROM KARYAWAN) AS GajiTertinggi, Gaji, GajiTertinggi-Gaji AS Selisih FROM KARYAWAN;

Q32a akan memunculkan pesan kesalahan bila dituliskan di MySQL.

5.3.9.2. Pengelompokan Fungsi-fungsi SUM, MIN, MAX, COUNT, AVG hanya menghasilkan masing-masing satu nilai sehingga penggunaannya tidak dapat digabungkan dengan atribut lain yang mungkin menghasilkan banyak nilai. Misal pada permasalahan menampilkan banyaknya karyawan yang ada di setiap departemen dan rata-rata gaji karyawan di departemen tersebut. Kita mungkin akan menuliskannya dalam bentuk: SELECT KodeDep_Kar, COUNT(*), AVG(Gaji) FROM KARYAWAN Query tesebut tidak akan bisa dieksekusi karena fungsi COUNT dan AVG masing-masing menghasilkan satu nilai, sedangkan atribut NODEP_KAR mungkin menghasilkan banyak nilai. Solusinya adalah dengan mengelompokkan data berdasarkan atribut tertentu sehingga hanya akan menghasilkan satu nilai juga. Dalam kasus seperti di atas kita lakukan pengelompokan tupel-tupel yang mempunyai nilai yang sama pada atribut tertentu. Kepada kelompok tupel ini selanjutnya kita bisa kenakan fungsi agregat. Untuk melakukan pengelompokan berdasar atribut tertentu SQL menggunakan klausa GROUP BY. Atribut di klausa SELECT harus muncul pada klausa GROUP BY, tetapi belum tentu sebaliknya. GROUP BY bisa berisi atribut apapun yang ada di tabel yang disebutkan di FROM. GROUP BY hanya bisa berisi atribut, bukan fungsi agregat. Permasalahan terakhir bisa kita betulkan menjadi: Q33:

SELECT KodeDep_Kar, COUNT(*) AS BYK, AVG(Gaji) AS RATA2 FROM KARYAWAN GROUP BY KodeDep_Kar; Tupel-tupel dalam relasi karyawan dibagi menjadi kelompok-kelompok, setiap

kelompok mempunyai nilai yang sama untuk atribut KodeDep_Kar. Fungsi COUNT dan AVG selanjutnya diterapkan kepada setiap kelompok tupel. Gambar 5.9 menunjukkan bagaimana pengelompokan dari Q33 dan hasilnya. Klausa GROUP BY juga dapat digunakan dengan kondisi JOIN. Untuk kondisi seperti ini, fungsi dan pengelompokan dijalankan setelah penggabungan 2 buah relasi dilakukan. Contohnya untuk setiap proyek, cari nomor proyek, nama proyek, dan banyaknya karyawan yang bekerja pada proyek tersebut.


101

NamaKarya wan Yasin Burhan Wahyu N. Gatot Dewi Nur Rahmat Nina

NIP

…

Gaji

1234567 3334455 6668844 4534534 9998877 8886655 9879879

... ... ... ... ... ... ...

400000 500000 375000 250000 350000 400000 400000

KodeDep_Kar 5 4 1 2

BYK 4 1 1 1

NIP_Pengawas

KodeDep_Kar

3334455 8886655 3334455 3334455 9879879 Null Null

5 5 5 5 4 1 2

RATA2 381250 350000 400000 400000

Gambar 5.9 Penggambaran GROUP BY untuk Q33. Q34:

SELECT KodeProyek, NamaProyek, COUNT(*) AS Byk FROM PROYEK, BEKERJA_DI WHERE KodeProyek=KodeProyek_Kar GROUP BY KodeProyek, NamaProyek;

Pada Q34 dilakukan join antara PROYEK dan BEKERJA_DI. Hasil join inilah yang selanjutnya dikelompokkan dan dihitung cacahnya. Perhatikan atribut selain fungsi agregat di SELECT juga muncul di GROUP BY. Fungsi agregat dapat dikenakan pada grup yang memenuhi kondisi tertentu. Misalnya Q34 dimodifikasi menjadi hanya proyek yang mempunyai karyawan yang lebih dari 2 yang ditampilkan sebagai hasil query. Di sini digunakan klausa HAVING, dengan syarat hanya bisa digunakan bersama GROUP BY. HAVING memberikan suatu kondisi terhadap hasil pengelompokan dan hanya grup yang memenuhi kondisi saja yang muncul sebagai hasil query. Selain berisi fungsi, HAVING juga bisa berisi subquery. Query untuk permasalahan tersebut bisa dilihat pada Q35: Q35:

SELECT KodeProyek, NamaProyek, COUNT(*) FROM PROYEK, BEKERJA_DI WHERE KodeProyek=KodeProyek_Kar GROUP BY KodeProyek, NamaProyek HAVING COUNT(*)>2; Perhatikan bahwa klausa WHERE membatasi tupel yang akan dikenai fungsi

(WHERE

dijalankan

sebelum

dikelompokkan),

sedangkan

HAVING

membatasi

keseluruhan grup (HAVING dijalankan setelah dikelompokkan). Apa yang dituliskan di HAVING juga harus muncul di SELECT. Karena fungsi bisa digunakan pada SELECT dan HAVING, diperlukan ketelitian untuk menentukan di mana fungsi diletakkan. Misalkan ingin menghitung total karyawan pada suatu departemen yang mempunyai gaji lebih dari 400000, tetapi penghitungan


102

dilakukan hanya untuk departemen yang mempunyai karyawan lebih dari 5. Misalkan querynya ditulis sebagai berikut: SELECT NamaDepartemen, COUNT(*) FROM DEPARTEMEN, KARYAWAN WHERE KodeDepartemen=KodeDep_Kar AND Gaji>400000 GROUP BY NamaDepartemen HAVING COUNT(*)>5; Query tersebut salah karena hanya mencari karyawan yang mempunyai gaji di atas 400000, selanjutnya mengelompokkannya berdasar nama departemennya, kemudian menampilkan nama departemen dan jumlah karyawan untuk setiap kelompok yang berisi lebih dari 5 karyawan (jadi pada saat akan dikelompokkan, tidak ada karyawan yang bergaji di bawah 400000). Hal ini tidak sesuai dengan permintaan semula karena yang diminta adalah untuk departemen yang mempunyai lebih dari 5 karyawan, berapapun gajinya. Query yang betul adalah: Q36:

SELECT NamaDepartemen, COUNT(*) FROM DEPARTEMEN, KARYAWAN WHERE KodeDepartemen=KodeDep_Kar AND Gaji>400000 AND KodeDep_Kar IN (SELECT KodeDep_Kar FROM KARYAWAN GROUP BY KodeDep_Kar HAVING COUNT(*)>5) GROUP BY NamaDepartemen;

Pada Q36 mula-mula dilihat apakah karyawan bergaji lebih dari 400000 dan apakah departemennya mempunyai lebih dari 5 karyawan. Bila terpenuhi maka tupel hasilnya dipilih dan pada akhirnya dikelompokkan berdasar nama departemennya. Contoh subquery yang diletakkandi HAVING adalah untuk menampilkan rata-rata jam kerja setiap karyawan yang di atas rata-rata jam kerja seluruh karyawan: Q37

SELECT NIP_Kar, AVG(JamKerja) FROM BEKERJA_DI GROUP BY NIP_Kar HAVING AVG(JamKerja)>(SELECT AVG(JamKerja) FROM BEKERJA_DI);

5.4. Manipulasi Data dengan Subquery Perintah untuk manipulasi basis data telah dijelaskan pada sub bab 5.2. Perintah tersebut juga dapat dikombinasikan dengan memasukkan subquery ke dalamnya.

5.4.1. Perintah INSERT Perintah INSERT yang lebih kompleks dari contoh sebelumnya adalah untuk menyisipkan beberapa tupel sekaligus ke dalam sebuah tabel dengan data hasil query dari tabel lain. Contoh: U8a:

CREATE TABLE INFO_DEP (NamaDepartemen VARCHAR(15), JumlahKaryawan INTEGER,


103

TotalGaji U8b:

INTEGER);

INSERT INTO INFO_DEP (NamaDepartemen, JumlahKaryawan, TotalGaji) SELECT NamaDepartemen, COUNT(*), SUM(Gaji) FROM DEPARTEMEN, KARYAWAN WHERE KodeDepartemen=KodeDep_Kar GROUP BY NamaDepartemen; Pada U8a dibentuk relasi baru dengan tiga buah atribut dan pada U8b, relasi

INFO_DEP diisi dengan tupel hasil query. Perhatikan bahwa tipe dan urutan atribut di SELECT harus sesuai dengan yang ada pada INFO_DEP. Apabila relasi DEPARTEMEN atau KARYAWAN diubah, maka pengubahannya tidak akan berpengaruh terhadap informasi yang sudah ada di INFO_DEP.

5.4.2. Perintah DELETE Contoh penghapusan dengan menggunakan SELECT adalah untuk menghapus karyawan yang bekerja pada departemen ‘Riset’. Querynya adalah: U9:

DELETE FROM KARYAWAN WHERE KodeDep_Kar IN (SELECT KodeDepartemen FROM DEPARTEMEN WHERE NamaDepartemen=’Riset’);

U9 akan menghapus tupel yang nilai KodeDep_Kar ada di tupel hasil subquery.

5.4.3. Perintah UPDATE UPDATE dengan menggunakan SELECT bisa dilakukan untuk memodifikasi beberapa tupel sekaligus. Contohnya adalah untuk menaikkan gaji karyawan yang bekerja di departemen ‘Riset’ sebesar 10%, perintahnya adalah: U10:

UPDATE KARYAWAN SET Gaji=Gaji*1.1 WHERE KodeDep_Kar IN (SELECT KodeDepartemen FROM DEPARTEMEN WHERE NamaDepartemen=’Riset’);

5.5. View Kebutuhan data seorang pengguna dengan yang lain terhadap sebuah basis data adalah berbeda. View adalah cara untuk menyediakan informasi yang diperlukan seorang pengguna yang berbeda dengan pengguna lainnya. Dalam bagian ini akan dibahas mengenai konsep view, bagaimana menyusun view, dan update view. Access tidak mendukung penggunaan VIEW secara langsung. Anda bisa mencobanya di DBMS lain, misalkan MySQL.

5.5.1. Konsep View View dalam pengertian SQL adalah tabel yang diturunkan dari tabel lain yang berupa tabel dasar atau view lain yang sudah ada sebelumnya. View tidak dalam bentuk Basis Data – Darmawan Satyananda

104

fisik sebagaimana table dasar, sehingga disebut sebagai tabel maya (virtual table). Data sebenarnya tetap tersimpan di tabel dasar. View adalah satu cara untuk membentuk suatu tabel yang sering diacu. Misalkan untuk mengacu ke satu atau beberapa relasi, bisa dibuat sebuah view yang berisi hasil join beberapa tabel dengan atribut yang diperlukan. Hasil dari view bisa dikenai operasi query seperti tabel biasa, sehingga pengaksesan cukup dilakukan terhadap satu view saja, bukan beberapa tabel dasar. Tabel sumber view disebut sebagai tabel pendefinisi (defining table) view.

5.5.2. Menspesifikasikan View Perintah untuk menspesifikasikan view adalah CREATE VIEW dengan bentuk: CREATE VIEW [( {, })] AS ; Di dalam perintah tersebut terdapat nama tabel maya, daftar nama atribut view, dan query untuk menentukan isi view. Bila daftar atribut tidak disebutkan maka view akan mempunyai atribut yang sama dengan tabel pendefinisi. Contoh: V1:

CREATE VIEW V_BEKERJA_DI AS SELECT NamaKaryawan, NamaProyek, JamKerja FROM KARYAWAN, PROYEK, BEKERJA_DI WHERE NIP=NIP_Kar AND KodeProyek=KodeProyek_Kar;

V2:

CREATE VIEW INFO_DEP (NAMA_DEP, JUM_KARY, TOT_GAJI) AS SELECT NamaDepartemen, COUNT(*), SUM(Gaji) FROM DEPARTEMEN, KARYAWAN WHERE KodeDepartemen=KodeDep_Kar GROUP BY NamaDepartemen;

Pada V1 tidak disebutkan nama atribut baru untuk V_BEKERJA_DI, sehingga view mempunyai nama atribut yang sama dengan tabel pendefinisinya. V2 menyebutkan nama atribut yang baru dengan urutan yang sama antara atribut yang ada di klausa CREATE VIEW dengan yang ada di klausa SELECT.

View bisa dikenai operasi sebagaimana tabel dasar seperti contoh berikut: menampilkan nama semua karyawan yang bekerja di proyek dan jam kerjanya. Querynya adalah: V3:

SELECT * FROM V_BEKERJA_DI; Bila view sudah tidak digunakan, view dapat dihapus dengan perintah DROP

VIEW, seperti contoh berikut: V4:

DROP VIEW V_BEKERJA_DI;


105

Gambar 5.10 Isi view dari V_BEKERJA_DI.

5.5.3. Update View View selalu dalam keadaan up to date, bila ada pengubahan tupel di tabel dasar yang berkaitan dengan view, maka view akan ikut berubah. Sebaliknya, pengubahan

view umumnya bisa mempengaruhi tabel dasarnya. Untuk melakukan update digunakan perintah UPDATE seperti halnya pada tabel dasar. Suatu view tidak bisa di-update bila pernyataan CREATE VIEW mengandung: ·

DISTINCT pada klausa SELECT.

·

ekspresi atau fungsi agregat pada klausa SELECT

·

operator himpunan seperti UNION, INTERSECT, MINUS

·

pengacuan ke view lain yang tidak bisa di-update pada klausa FROM

·

klausa GROUP BY atau HAVING Misalkan akan mengubah jam kerja karyawan yang bernama Yasin pada proyek

Produk X menjadi 20 jam, maka perintahnya adalah sebagai berikut: V5:

UPDATE V_BEKERJA_DI SET JamKerja=20 WHERE NamaKaryawan=’Yasin’ AND NamaProyek=’Produk X’;

Tampak pada Gambar 5.11 perubahan yang terjadi di V_BEKERJA_DI dan BEKERJA_DI sebagai tabel dasarnya.


106

Gambar 5.11 Perubahan pada view dan tabel dasar. Perhatikan definisi view berikut yang dibentuk dengan GROUP BY dan pengubahan yang terjadi: V6

CREATE VIEW V_JAMKERJA AS SELECT NIP_Kar, SUM(JamKerja) AS Total FROM BEKERJA_DI GROUP BY NIP_Kar;

V7:

UPDATE V_JAMKERJA SET Total=100 WHERE NIP_Kar="1234567";

Gambar 5.12 Contoh view yang tidak bisa di-update.


107

5.6. Pengindeksan Dalam SQL Indeks adalah cara untuk mempercepat pencarian data dalam suatu tabel menurut atribut tertentu. Atribut yang digunakan dalam pengindeksan disebut sebagai atribut pengindeks (indexing attribute). Indeks dalam SQL bisa dibuat dan dihapus secara dinamis dan pengindeksan dilakukan dalam tabel dasar. Untuk menentukan indeks digunakan perintah CREATE INDEX, dengan bentuk umum: CREATE [UNIQUE] INDEX ON ( [ASC|DESC] {[, [ASC|DESC]]}); Sebagai contoh, untuk membuat indeks untuk atribut NamaKaryawan dari relasi KARYAWAN, SQL perintah yang diberikan adalah: I1:

CREATE INDEX IDX_NAMA ON KARYAWAN(NamaKaryawan); Secara umum indeks tersusun dalam urutan naik (ascending). Untuk mengubah

ke urutan turun (descending) bisa ditambahkan DESC setelah nama atribut, sedangkan untuk urut naik bisa juga ditambahkan ASC walaupun bukan merupakan keharusan. Contoh: I2:

CREATE INDEX IDX_NAMA2 ON KARYAWAN(NamaKaryawan ASC, TglLahir DESC); Nilai atribut yang menjadi dasar indeks bisa ditentukan hanya muncul sekali saja

(unik) atau lebih dari sekali (duplikat). Atribut kunci yang digunakan sebagai indeks bisa dipastikan tidak bernilai duplikat. Atribut pengindeks (yang bukan atribut kunci) bisa ditentukan untuk bernilai unik dengan menggunakan UNIQUE yang dituliskan setelah perintah CREATE. Contohnya: I3:

CREATE UNIQUE INDEX IDX_NIP ON KARYAWAN(NIP);

Ini berarti bahwa tidak boleh ada NIP yang ganda dalam tabel KARYAWAN. Penentuan kunci sebaiknya dilakukan sebelum ada tupel yang dimasukkan ke dalam relasi sehingga sistem akan memaksakan aturan tersebut. Pembentukan indeks untuk tupel yang sudah ada dalam relasi akan gagal bila tupel tersebut tidak memenuhi aturan keunikan (unique

constraint) untuk atribut indeks (nilai atribut indeks harus tunggal, tidak ada yang menyamai). Dua buah nilai NULL akan dianggap sama untuk indeks yang unik sehingga paling banyak hanya sebuah tupel yang bisa mempunyai nilai NULL untuk atribut kunci. Atribut indeks bisa berupa atribut tunggal atau gabungan beberapa atribut (komposit). Daftar atribut yang digunakan sebagai atribut indeks dituliskan setelah klausa ON, misalkan query berikut: I4:

CREATE UNIQUE INDEX IDX_KERJA ON BEKERJA_DI(NIP_Kar, KodeProyek_Kar);


108

Untuk menghapus indeks, perintah yang digunakan adalah DROP INDEX. Alasan penghapusan adalah indeks memerlukan tempat untuk penyimpanan sehingga bila tidak digunakan lebih baik dihapus. Indeks yang menyatakan aturan kunci sebaiknya jangan dihapus selama diinginkan untuk terus menggunakan aturan unik. Contoh: I4:

DROP INDEX IDX_NIP; Di Microsoft Access, atribut yang menjadi primary key secara otomatis akan

menjadi atribut indeks. Tetapi atribut yang bukan kunci bisa diatur menjadi atribut indeks. Di MySQL, indeks ditentukan sendiri; kunci primer tidak secara otomatis menjadi indeks.

5.7. Rangkuman SQL (Structured Query Language) adalah satu bahasa khusus untuk basis data yang dimaksudkan sebagai antarmuka antara pengguna dan sistem basis data. SQL sudah menjadi standar query pada banyak basis data relasional. Fasilitas yang ada dalam SQL meliputi pendefinisian tabel, query, manipulasi tabel, index, dan view. Pada pendefinisian tabel dilakukan penentuan nama atribut, tipe data dan ukurannya, serta sejumlah aturan misalkan pengacuan ke atribut di tabel lain, kunci primer, NULL, dan sifat keunikan atribut. Aturan ini bisa juga ditambahkan, diubah, dan dihapus setelah tabel selesai disusun. Query adalah istilah umum untuk kegiatan pencarian data. Hasil query untuk berbentuk tabel, seperti tabel dasar (relasi dasar) dengan nama atribut yang bisa dispesifikasikan sendiri. Query bisa melibatkan sejumlah kondisi seleksi, kondisi join untuk menggabungkan lebih dari 1 tabel, pengurutan tupel menurut atribut tertentu, pengelompokan tupel menurut atribut tertentu, dan penggabungan tabel-tabel (join). Selain itu juga bisa menggunakan operator himpunan. Suatu query mungkin berisi subquery, baik yang berkorelasi ataupun tidak. Manipulasi tabel meliputi insert, update dan delete. Ketiganya bisa juga melibatkan subquery untuk mengatasi kondisi yang tidak bisa dituliskan langsung.

View adalah tabel yang diturunkan dari tabel yang lain sesuai dengan kebutuhan pengguna. View bisa berupa gabungan dari beberapa tabel. View disebut juga sebagai tabel maya karena tidak tersimpan secara fisik dalam file. Kegunaan view antara lain apabila ingin melihat data dari beberapa tabel yang saling berhubungan sekaligus. Tabel

view bisa dikenakan operasi seperti tabel asalnya dan bisa dijadikan acuan untuk operasi yang lain. Pengubahan data di tabel asal akan berpengaruh ke hasil view. Sebaliknya, mungkin saja operasi update dalam view bisa mempengaruhi tabel asal dalam kondisi tertentu. Karena sifatnya sementara view bisa dihapus apabila sudah tidak diperlukan. Indeks digunakan untuk mempercepat pencarian data. Indeks disusun dari satu


109

atau lebih atribut di tabel asal. Kunci primer berpeluang paling besar menjadi indeks karena sifatnya yang sudah unik, tetapi atribut lain juga bisa diatur menjadi atribut indeks. Atribut indeks bisa atribut tunggal atau komposit.

5.8. Soal Latihan 1. Mengapa SQL memperbolehkan adanya tupel yang duplikat di tabel atau relasi hasil query? 2. Tentukan perintah SQL yang sesuai untuk mendefinisikan skema pada soal latihan bab IV no 5 3. Bila diketahui skema relasi R = (A, B, C) S = (D, E, F) Dan dipunyai relasi r(R) dan s(S). Berikan ekspresi dalam SQL untuk query berikut: a. pA(r) b. sB=17(r) c.

pA(sB=17 AND C=25(r))

d. r ´ s e. pA,F(sC=D(r ´ s)) 4. Tentukan perintah SQL yang sesuai untuk soal pada Bab IV no 6 dan 7.


110

bila sebanyak 2 buah di depan angka 3 dan 4 buah di belakangnya. Maksudnya adalah dari deretan angka dan pemisah tanggal bulan tahun (mungkin berupa ‘-‘ atau ‘/’), angka ke 9 haruslah 6. Sisanya bebas asalkan banyaknya sesuai dengan wildcard yang ada. Bila tanggal dinyatakan sebagai data yang bertipe DATE (atau sejenisnya) maka querynya sedikit berbeda (tidak menggunakan tanda petik tunggal dan wildcard). Pada Microsoft Access, bila tanggal dinyatakan bertipe Date/Time, maka Q11 bisa dituliskan sebagai berikut:

Q11a:

SELECT NAMA FROM KARYAWAN WHERE TGL_LAHIR >= #01-01-1960# AND TGL_LAHIR <= #01-01-1969#

Seperti yang disebutkan sebelumnya bahwa teknik subquery bisa menjadi alternatif penulisan query menggantikan join. Query yang ditulis dengan subquery dan menggunakan operator = atau IN bisa ditulis sebagai query tunggal. Q15 bisa ditulis sebagai berikut: Q15a:

SELECT K.NAMA FROM KARYAWAN AS K, TANGGUNGAN AS T WHERE K.NIP=T.NIP_KAR AND K.JENIS_KLM=T.JENIS_KLM;

Q15a:

SELECT NamaKaryawan FROM KARYAWAN, BEKERJA_DI WHERE NIP=NIP_Kar AND KodeProyek_Kar IN ( SELECT KodeProyek FROM PROYEK WHERE Lokasi=”Jakarta”); UNIQUE digunakan untuk menguji apakah terdapat tupel yang duplikat pada

hasil subquery. UNIQUE akan bernilai TRUE bila subquery tidak mempunyai tupel yang duplikat dan sebaliknya. Kebalikan dari UNIQUE adalah NOT UNIQUE untuk menguji keberadaan tupel yang duplikat. Contohnya untuk mencari NIP dan nama karyawan yang hanya mempunyai seorang tanggungan, querynya dapat ditulis sebagai berikut: Q22:

SELECT NIP, NamaKaryawan FROM KARYAWAN WHERE UNIQUE (SELECT NIP_Kar FROM TANGGUNGAN, KARYAWAN WHERE NIP_Kar=NIP);

Q20 akan menampilkan NIP dan nama seorang karyawan bila subquery yang berkorelasi dengan query luar menghasilkan tupel yang tidak duplikat. Bila query aktif saat ini adalah Dewi Nur dengan NIP 9998877, maka Dewi Nur tidak akan menjadi hasil akhir Q20


111

karena subquery saat itu akan menghasilkan 2 tupel yang tidak unik (karena Dewi Nur mempunyai 2 anak).

Sebagai konsekuensinya, penamaan ulang atribut harus dilakukan bila atribut yang akan menjadi kondisi join mempunyai nama yang berbeda, misalnya join pada Q4 bisa dituliskan sebagai berikut: Q4d:

SELECT NamaKaryawan, Alamat FROM KARYAWAN NATURAL JOIN (DEPARTEMEN AS DEPT(NamaDepartemen, KodeDep_Kar, NIP_Manajer,TglMulai_Manajer)) WHERE NamaDepartemen=”Riset”;

Pada Q4d dilakukan penamaan ulang atribut KodeDepartemen pada tabel DEPT menjadi KodeDepartemen_Kar untuk menyamakan dengan yang ada di tabel KARYAWAN. DEPT sendiri adalah alias dari tabel DEPARTEMEN. Join dilakukan berdasar kondisi join implisit KARYAWAN.KodeDepartemen_Kar=DEPT.KodeDepartemen_Kar

(karena

hanya

dua

atribut ini yang bernama sama).

Update view merupakan masalah yang rumit, karena update tidak dapat dilakukan secara langsung pada view, selain itu view mungkin berasal dari beberapa relasi. Untuk view yang didefinisikan dari sebuah tabel updatenya dapat dipetakan ke update tabel dasar. Untuk view yang melibatkan join, operasi update dipetakan ke operasi update untuk relasi dasar dalam beberapa cara. Misalkan untuk merubah proyek tempat ‘Joni’ bekerja dari ‘Produk X’ ke ‘Produk Y’ perintah yang diberikan adalah: Query tersebut dapat dipetakan ke beberapa operasi update untuk relasi dasar, untuk menghasilkan operasi update yang dikehendaki. Dua kemungkinan operasinya adalah: V6a:

V6b:

UPDATE BEKERJA_DI SET KodeProyek_Kar = (SELECT KodeProyek FROM PROYEK WHERE NamaProyek=’Produk Y’) WHERE NIP_Kar IN (SELECT NIP FROM KARYAWAN WHERE NamaKaryawan=’Joni’) AND KodeProyek_Kar IN (SELECT KodeProyek FROM PROYEK WHERE NamaProyek=’Produk X’); UPDATE PROYEK SET NamaProyek=’Produk Y’ WHERE NamaProyek=’Produk X’;


112

Update pada V6a akan mengubah nomor proyek tempat Joni bekerja pada relasi BEKERJA_DI dan operasi V6b akan mengubah nama proyek dari ‘Produk X’ menjadi ‘Produk Y’ pada relasi PROYEK. Secara umum, tidak semua view dapat dilakukan operasi update dan belum tentu

update tersebut berhasil. Update view berhasil apabila hanya satu update pada tabel dasar yang dapat memenuhi efek update yang diinginkan. Bila update view dapat dipetakan ke lebih dari satu update di tabel dasar, harus dipunyai suatu prosedur untuk menentukan operasi mana yang dipilih.


113

BAB V. Basis Data Darmawan Satyananda 74

Recommend Documents