Sub Query dan Indeks
SUB QUERY DAN INDEKS
Dasar Teori
Sub Query
Pengertian subquery
akan dijelaskan melalui ilustrasi berikut ini :
Misal kita ingin
membuat suatu query untuk mencari gaji pegawai yang lebih besar dari gaji yang
dimiliki oleh pegawai bernama ‘JONES’. Untuk memecahkan persoalan ini, kita
membutuhkan dua query, satu query untuk mencari gaji yang dimiliki oleh JONES
dan query lain u ntuk mencari pegawai yang memiliki gaji lebih besar daripada
gaji JONES.
Inner query atau
subquery akan menghasilkan suatu nilai yang nantinya dipakai
oleh outer query atau main query. Sintak (cara penulisan) sub query :
oleh outer query atau main query. Sintak (cara penulisan) sub query :
Subquery dapat
ditempatkan dalam klausa SQL berikut :
-
Where,
-
Having
Example
:
-
From
Example :
Tipe-tipe
dari subquery :
-
Single row, nilai yang dikembalikan
misal : CLERK
Single row subquery memberikan hasil hanya
satu baris pada bagian subquery. Untuk single row subquery ini yang digunakan
adalah operator pembandingan : = , > , >= , < , <= , atau <>.
-
Multiple-row, nilai yang
dikembalikan misal : CLERK, MANAGER
Multiple
Row Subquery adalah subquery yang menghasilkan lebih dari satu baris. Untuk
multiple row subquery ini yang digunakan adalah operator pembandingan : IN, ANY
atau ALL.
-
Multiple-column, nilai yang
dikembalikan misal : CLERK 7900
MANAGER 7698
·
Indeks
Indeks dapat dibentuk secara manula maupun secara otomatis. secara manual
index terbentuk melalui printah Create Index dan secara otomatis, Index
terbentuk saat menentukan primary key dan unique terhadap field tententu.
Indeks dapat dibayangkan sebagai indeks buku, sehingga lelalui indeks buku tersebut dapat dicari letak item tertentu dalam buku dengan mudah. Keberadaan indkes dalam basis data antara lain adalah untuk mempercepat pencarian data berdasarkan kolom tertentu, misalnya diberikan perintah :
select * from pegawai where no_pegawai = '7902';
Jika tidak dijadikan indeks, pencarian data akan dilakukan terhadap seluruh tabel, sama seperti kalu akan mencari sesuatu dalam buku tetapi buku tersebut tidak dilengkapi dengan indeks. namun sekirannya indeks yang berkaitan dengan nip ada, maka sistem akan menemukanya dengan cepat.
Beberapa ha yang sangat terbantu dnegan adanya indeks adalah :
Indeks dapat dibayangkan sebagai indeks buku, sehingga lelalui indeks buku tersebut dapat dicari letak item tertentu dalam buku dengan mudah. Keberadaan indkes dalam basis data antara lain adalah untuk mempercepat pencarian data berdasarkan kolom tertentu, misalnya diberikan perintah :
select * from pegawai where no_pegawai = '7902';
Jika tidak dijadikan indeks, pencarian data akan dilakukan terhadap seluruh tabel, sama seperti kalu akan mencari sesuatu dalam buku tetapi buku tersebut tidak dilengkapi dengan indeks. namun sekirannya indeks yang berkaitan dengan nip ada, maka sistem akan menemukanya dengan cepat.
Beberapa ha yang sangat terbantu dnegan adanya indeks adalah :
-
Proses penggabungan
sejumlah tabel
-
Proses dengan ORDER BY
-
Proses fungsi agregat
seperti MIN dan MAX
-
Proses pencarian data
Menciptakan indeks
Untuk membuat file indeks digunakan perintah CREATE INDEX, dengan bentuk umum parintahnya adalah :
create index nama_index on nama_tabel (nama_field_index);
Keterangan :
nama_index adalah nama indeks yang dibuat
nama_tabel : adala nama tabel yang diindeks
nama_field_index yang berada di dalam tanda kurung menyatakan kolom / field / atribut yang diunakan untuk mengindeks.
Contoh 1 :
Apabila kita ingi mengindecx tabel pegawai berdasarkan field nama dengan nama index-nya adalah nama_idx, maka perintah index-nya adalah :
create index nama_idx on pegawai (nama);
Beberapa manfaat dari proses index.
Untuk membuat file indeks digunakan perintah CREATE INDEX, dengan bentuk umum parintahnya adalah :
create index nama_index on nama_tabel (nama_field_index);
Keterangan :
nama_index adalah nama indeks yang dibuat
nama_tabel : adala nama tabel yang diindeks
nama_field_index yang berada di dalam tanda kurung menyatakan kolom / field / atribut yang diunakan untuk mengindeks.
Contoh 1 :
Apabila kita ingi mengindecx tabel pegawai berdasarkan field nama dengan nama index-nya adalah nama_idx, maka perintah index-nya adalah :
create index nama_idx on pegawai (nama);
Beberapa manfaat dari proses index.
-
membantu mempercepat proses
pencarian data
apabila kita ingin menampilkan data pegawai yang namanya adalah 'James', maka perintah yang dituliskan adalah :
select*from pegawai where nama='James';
apabila kita ingin menampilkan data pegawai yang namanya adalah 'James', maka perintah yang dituliskan adalah :
select*from pegawai where nama='James';
-
Kemudian bandingkan dengan
proses pencarian pada data yang telah diindex :
create index nm_idx on pegawai (nama);
select*from pegawai from pegawai where nama = 'James';
Dari kedua proses pencarian di atas, dapat disumpulkan bahwa pencarian pada table yang sudah diindeks akan lebih cepat (perhatian lama waktu pencarian).
create index nm_idx on pegawai (nama);
select*from pegawai from pegawai where nama = 'James';
Dari kedua proses pencarian di atas, dapat disumpulkan bahwa pencarian pada table yang sudah diindeks akan lebih cepat (perhatian lama waktu pencarian).
-
Membantu mempercepat proses
pengitungan data denga fungsi agregasi.
Akan dihitung gaji terbesar untuk pegawai yang perkerjannya adalah 'SALESMAN', maka perintah yang dituliskan :
Akan dihitung gaji terbesar untuk pegawai yang perkerjannya adalah 'SALESMAN', maka perintah yang dituliskan :
-
select max(gaji) from
pegawai where pekerjaan = 'SALESMAN';
kemudian bandingkan dengan proses pencarian pada data yang telah diindex :
create index gaji_idx on pegawai (gaji);
select max(gaji) from pegawai where pekerjaan='SALESMAN';
Kesimpulannya sama dengan proses pencarian data di atas.
Menciptakan Indeks yang unik
Untuk menciptakan indeks yang unik maka harus ditambahkan kata UNIQUE diantara create dan index. Maksud Indeks yang unik adalah bahwa field yang dijadikan sebagai kunci index haruslah field yang berisi data yang unik.
Contoh1 :
create index pekerjaan_idx on pegawai (pekerjaan);
hasil query ok
Contoh2 :
create unique index pekerjaan_idx on pegawai (pekerjaan);
hasil ERROR
Keterangan :
Pada contoh kedua perintah index dianggap salah (error) yang disebabkan karena data yang tersimpan di field pekerjaan tidak unik.
Menciptakan Indeks Untuk Beberapa Field
Untuk menciptakan indeks dengan beberapa field sebagai kunci indeksnya, maka perlu penyebutan nama-nama field yang dimaksud sebagai berikut :
create unique index no_nama_idx on pegawai (no_pegawai, nama);
kemudian bandingkan dengan proses pencarian pada data yang telah diindex :
create index gaji_idx on pegawai (gaji);
select max(gaji) from pegawai where pekerjaan='SALESMAN';
Kesimpulannya sama dengan proses pencarian data di atas.
Menciptakan Indeks yang unik
Untuk menciptakan indeks yang unik maka harus ditambahkan kata UNIQUE diantara create dan index. Maksud Indeks yang unik adalah bahwa field yang dijadikan sebagai kunci index haruslah field yang berisi data yang unik.
Contoh1 :
create index pekerjaan_idx on pegawai (pekerjaan);
hasil query ok
Contoh2 :
create unique index pekerjaan_idx on pegawai (pekerjaan);
hasil ERROR
Keterangan :
Pada contoh kedua perintah index dianggap salah (error) yang disebabkan karena data yang tersimpan di field pekerjaan tidak unik.
Menciptakan Indeks Untuk Beberapa Field
Untuk menciptakan indeks dengan beberapa field sebagai kunci indeksnya, maka perlu penyebutan nama-nama field yang dimaksud sebagai berikut :
create unique index no_nama_idx on pegawai (no_pegawai, nama);
·
Kolom Unik
Unique berfungsi
untuk menjaga agar tidak terjadinya duplikasi nilai (kesamaan
data) dalam sebuah kolom, hal ini dapat ditangani dengan membuat sebuah indeks unik
atau fungsi unik sendiri pada kolom yang dimaksud. Unique ini sering digunakan dalam
pembuatan bukan primary key namun membutuhkan cek dupikasi agar tidak ada yang
sama, karena dalam primary key sudah otomatis mempunyai sifat unik. Berikut Struktur
SQL saat pembuatan tabel baru :
CREATE TABLE nama_tabel (nama_kolom tipe_data unique);
data) dalam sebuah kolom, hal ini dapat ditangani dengan membuat sebuah indeks unik
atau fungsi unik sendiri pada kolom yang dimaksud. Unique ini sering digunakan dalam
pembuatan bukan primary key namun membutuhkan cek dupikasi agar tidak ada yang
sama, karena dalam primary key sudah otomatis mempunyai sifat unik. Berikut Struktur
SQL saat pembuatan tabel baru :
CREATE TABLE nama_tabel (nama_kolom tipe_data unique);
Ketika tabel sudah
ada kita bisa menggunakan cara seperti pada BAB. 2 berikut
struktur SQL nya :
ALTER TABLE nama_tabel ADD UNIQUE (nama_kolom);
Untuk menghapus unique berikut caranya :
ALTER TABLE nama_table DROP CONSTRAINT NAMA_CONSTRAIN
struktur SQL nya :
ALTER TABLE nama_tabel ADD UNIQUE (nama_kolom);
Untuk menghapus unique berikut caranya :
ALTER TABLE nama_table DROP CONSTRAINT NAMA_CONSTRAIN
·
Check
CHECK Constraint: memastikan data dalam kolom
memenuhi kriteria yang ditentukan.
CREATE TABLE Pelanggan
(Kode integer CHECK (Kode > 0),
Nama varchar (30),
Alamat varchar(30));
Pada contoh di sini kriteria field Kode harus lebih besar dari 0, jika data dientry lebih kecil dari 0 akan terjadi error dan data tidak akan dapat disimpan ke table.
CREATE TABLE Pelanggan
(Kode integer CHECK (Kode > 0),
Nama varchar (30),
Alamat varchar(30));
Pada contoh di sini kriteria field Kode harus lebih besar dari 0, jika data dientry lebih kecil dari 0 akan terjadi error dan data tidak akan dapat disimpan ke table.
·
Trim
· Menghilangkan karakter pada suatu data atau menghilangkan
beberapa karakter di MySQL memang terkadang sangat diperlukan. Terutama dalam
mengupdate data dalam bentuk karakter/teks yang bertipe varchar, text dan
sebangsanya. Apalagi pada banyak kasus, tipe data ini sangat diandalkan
Contoh simplenya misalnya menghilangkan tanda tertentu seperti menghilangkan tanda koma di belakang kata “Rabu” pada ” Senin,Selasa,Rabu,”
Function built in MySQL yang dapat kita manfaatkan adalah TRIM. Berikut cara penggunaannya…
Syntax of TRIM():
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)
Contohnya:
SELECT TRIM(‘ tudabit ku’); –> ada spasi didepan tudabitku
akan menghasilkan kata tudabit ku dengan spasi yang sudah dihilangkan (default fungsi TRIM)
SELECT TRIM(LEADING ‘s’ FROM ‘sssbarrsss’);
akan menghasilkan kata barrsss dimana s didepan dihilangkan
SELECT TRIM(BOTH ‘x’ FROM ‘xxxbarxxx’);
akan menghasilkan kata bar dimana huruf x dihilangkan baik yg didepan maupun dibelakang
SELECT TRIM(TRAILING ‘,’ FROM ‘Senin,Selasa,Rabu,’);
akan menghasilkan kata Senin,Selasa,Rabu dimana tanda ( , ) dibelakang Rabu dihilangkan
Fungsi lainnya yang bisa dimanfaatkan adalah REPLACE, Contoh:
SELECT REPLACE(‘ tudabit ku’,’ ‘,”)
akan menghasilkan kata tudabitku dengan spasi yang sudah dihilangkan.
Contoh simplenya misalnya menghilangkan tanda tertentu seperti menghilangkan tanda koma di belakang kata “Rabu” pada ” Senin,Selasa,Rabu,”
Function built in MySQL yang dapat kita manfaatkan adalah TRIM. Berikut cara penggunaannya…
Syntax of TRIM():
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)
Contohnya:
SELECT TRIM(‘ tudabit ku’); –> ada spasi didepan tudabitku
akan menghasilkan kata tudabit ku dengan spasi yang sudah dihilangkan (default fungsi TRIM)
SELECT TRIM(LEADING ‘s’ FROM ‘sssbarrsss’);
akan menghasilkan kata barrsss dimana s didepan dihilangkan
SELECT TRIM(BOTH ‘x’ FROM ‘xxxbarxxx’);
akan menghasilkan kata bar dimana huruf x dihilangkan baik yg didepan maupun dibelakang
SELECT TRIM(TRAILING ‘,’ FROM ‘Senin,Selasa,Rabu,’);
akan menghasilkan kata Senin,Selasa,Rabu dimana tanda ( , ) dibelakang Rabu dihilangkan
Fungsi lainnya yang bisa dimanfaatkan adalah REPLACE, Contoh:
SELECT REPLACE(‘ tudabit ku’,’ ‘,”)
akan menghasilkan kata tudabitku dengan spasi yang sudah dihilangkan.
Hasil Praktikum
teman-teman sobat blogger.. dibawah ini adalah hasil praktikum ane tentang sub query dan indeks minggu ini,,
disimak ya??..hehe
1.
Tampilkan nama fakultas dan jumlah mahasiswa yang mampunyai
ketentuan nama fakultas yang dimunculkan dengan jumlah mahasiswanya terkecil!
2.
Tampilkan nama mahasiswa, nama fakultas, alamat dengan syarat nama
fakultas sama dengan edi dan alamatnya tidak sama dengan luki!
3.
Buatlah index di tabel mahasiswa(alamat). Kemudian buat lagi index
yang bersifat unik pada tabel fakultas(fak_nama) kemudian amati perbedaannya
ketika memasukkan data yang sama!
membuat tabel index dulu pada tabel mahasiswa,,
4.
Buat kolom nama di mahasiswa menjadi unik dan inputkan 2 data yang
sama. Kemudian amati perbedaannya !
lalu setelah terbuat unik indeks, coba diinputkan beberapa data,,
dan terlihat hasil seperti dibawah ini..
5.
Pindahkan data dari tabel mahasiswa, fakultas ambil kolom nim,
nama mahasiswa, alamat, nama fakultas ke tabel baru yang dinamai „tabel
identitas‟.
6.
Buatlah contoh penggunaan check pada sub bab pembahasan CHECK.
Kemudian masukkan beberapa data baik yang sesuai dengan criteria check maupun
yang bukan dan amati perbedaannya.
7.
Inputkan data di tabel mahasiswa dimana pada kolom nama sebelum
inputkan karakter dahulukan dengan spasi dan di akhiri dengan tanda “+” seperti
berikut : “ andi cahyono++++”. kemudian munculkan seluruh data dan hilangkan
spasi didepan!
langkah pertama, menginputkan data yang mengandng spasi dan juga tanda +++++ pada kolom nama
8.
Munculkan data mahasiswa dengan hilangkan karakter “+” di akhir
data dan karakter “a” di awal kata pada kolom nama!
Tugas Rumah
Soal pertama, menampilkan nama fakultas, dengan jumlah mahasiswa yang ada dalam fakultas tersebut yang paling sedikit. Dapat diselesaikan dengan bantuan sub query group by, order by, serta limit.
Soal pertama, menampilkan nama fakultas, dengan jumlah mahasiswa yang ada dalam fakultas tersebut yang paling sedikit. Dapat diselesaikan dengan bantuan sub query group by, order by, serta limit.
Soal kedua, menampilkan nama mahasiswa, nama fakultas dan alamat mahasiswa dengan ketentuan nama fakultas sama dengan edi dan alamta mahasiswa tidak sama dengan luki.
Soal ketiga, membuat index di tabel mahasiswa, khususnya kolom alamat,, lalu indeks yang bersifat unik pada kolom nama_fakultas pada tabel fakultas.
membuat indeks pada tabel mahasiswa..
ternyata perbedaan antara indeks biasa dan unik indeks terletak pada saat diinputkan data yang sama,, alamat yang sama(indeks) tetap bisa masuk dalam tabel. tetapi ketika menginputkan nama_fakultas yang sama, yang sudah ada pada tabel.. data tersebut tidak dapat diinputkan lagi(error)
Soal keempat, membuat kolom nama pada tabel mahasiswa menjadi kolom unik, agar ketika menginputkan dua data dengan nama yang sama, otomatis akan tidak dapat terinput.
Soal kelima, memindahkan semua data yang ada pada tabel mahasiswa ke dalam tabel baru,, tabel identitas.
Soal keenam, membuat contoh penggunaan check..
Soal ketujuh, menginputkan data di tabel mahasiswa dimana pada kolom nama sebelum karakter, disisipi spasi dan juga setelah karakter ditambah dengan tanda "+". lalu memunulkan seluruh data dengan menghilangkan spasi didepan.
Soal kedelapan, memunculkan data mahasiswa dengan menghilangkan karakter "+" diakhir data dan karakter "a" di awal kata pada kolom nama.
Evaluasi MySQL dan PostgreSQL
Kesimpulan :
- SQL subquery adalah query dalam query yang digunakan untuk menghapus baris dari satu tabel berdasarkan baris tabel lain, menyelesaikan pesoaln dimana terdapat suatu nilai yang tidak diketahui
- indeks digunakan untuk meng-efisienkan pencarian data
- kolom unique, digunakan agar data yang diinputkan tidak boleh sama dalam satu kolom
- check berfungsi untuk melakukan pembatasan nilai masukan dalam sebuah kolom (memberikan syarat-syarat tertentu dalam sebuah kolom).
- trim digunakan untuk menghapus spasi yang tidak diperlukan
Keinginan soal sulit dipahami..
Mohon saran dari sobat blogger,, agar menuliskan kritik sarannya tentang postingan ini di comment ya..
Manfaat :
Semoga postingan ini dapat bermanfaat bagi penulis, pembaca,,,dan orang-orang yang bersangkutan..
aamiin :-)
Daftar Pustaka:
Kadir, Abdul. 1998. Konsep & Tuntunan
Praktis Basis Data. Yogyakarta: Andi.
Fathansyah. 2002. Buku Teks Ilmu Komputer:
Basis Data. Bandung: Penerbit Informatika.
http://www.ilmusoftware.com/2010/11/sql-constraint.html
Komentar
Posting Komentar