SUB QUERY DAN INDEKS
SELECT INTO
STATEMENT
Query select into
statement merupakan query SQL yang
digunakan untuk
Berikut struktur query penggunaan select into statement untuk mengopi semua
data dari tabel1 ke tabel yang baru :
SELECT * INTO
newtable FROM table1;
Berikut struktur query penggunaan select into statement
untuk mengopi data berdasarkan
kolom tertentu dari tabel1 ke tabel yang baru :
SELECT column_name(s)
INTO newtable [IN externaldb] FROM table1;
Contoh :
- mengopi semua data
dari tabel mahasiswa ke tabel baru yang dinamakan „identitas
1‟:
Select * into identitas1 from mahasiswa;
- mengopi data
pada kolom nim dan nama dari
tabel mahasiswa ke tabel baru yang
dinamakan „identitas 2‟:
Select nim_mhs,
nama_mhs into identitas2 from mahasiswa;
SUB QUERY
Subquery atau query
Nested merupakan bentuk query yang terdapat dalam query
yang lain.
Subquery dapat ditempatkan dalam klausa where, having,
from bersama dengan operator perbandingan
seperti = untuk baris tunggal dan untuk
baris berganda
menggunakan
in, not in atau <>, < any, >, >=,<=. Penggunaan sub query dapat
diterapkan pada pernyataan SELECT, UPDATE, DELETE, dan INSERT. Bentuk
penggunaannya sebagai berikut :
Select nama_kolom
from nama_tabel where nama_kolom operator
(subquery);
Berikut contoh dari subquery menggunakan data pegawai :
Id_peg Nama_peg Alamat_peg
Telp_peg Jabatan_peg Gaji_peg
1 Hendro Solo
081223300 Teknisi 900000
2 Tika Semarang
0897735357 Sekretaris 2000000
3 Wijaya Jogjakarta
0865433225 Kepala 3000000
4 Dodi Banyuwangi
076544677 Teknisi 1000000
- Mencari nama pegawai
yang memiliki jabatan yang sama dengan pak hendro bisa
menggunakan query sebagai berikut :
Select
nama_peg,jabatan_peg from pegawai where
jabatan_peg in (select
jabatan_peg from
pegawai where nama_peg=’Hendro’);
Hasil :
NAMA JABATAN
Hendro Teknisi
Dodi Teknisi
- Mencari nama
pegawai yang gajinya lebih besar dari pegawai dengan nama Dodi bisa
menggunakan query sebagai berikut :
select
nama_peg,gaji_peg from pegawai where gaji_peg > any (select gaji_peg
from pegawai where
nama_peg =’Dodi’);
Hasil :
NAMA Gaji
Tika 2000000
Wijaya 3000000
- Mencari nama
pegawai yang gajinya lebih besar dari
950000 dan jabatannya bukan
seperti jabatan pak hendro bisa menggunakan query sebagai
berikut :
select nama_peg,
jabatan_peg, gaji_peg from pegawai where
gaji_peg >= 950000
and jabatan_peg <>
(select jabatan_peg from pegawai
where
nama_peg=’Hendro’);
Hasil :
NAMA JABATAN Gaji
Tika Sekretaris 2000000
Wijaya Kepala 3000000
INDEKS
Indeks disini berguna dalam suatu pencarian nilai atau data
dalam database.
Dalam suatu kasus ketika mengakses sebuah tabel biasanya
DBMS akan membaca
seluruh tabel baris perbaris hingga selesai. Ketika baris
sangat banyak dan hasil dari
query hanya sedikit, maka hal ini sangat tidak efisien.
Seperti halnya ketika kita membaca
sebuah buku dan ingin mencari kata atau istilah tertentu
dalam buku maka biasanya akan
di cari dengan membuka setiap halaman dari awal sampai
akhir. Dengan adanya indeks
buku maka kita cukup dengan membuka indeks, sehingga akan
cepat dalam pencarian
kata tersebut.
PostgreSQL tidak bisa membuat indeks dengan otomatis, sehingga user
dapat membuat indeks tersebut untuk sering kali digunakan kolom, biasanya dalam
clause WHERE. Berikut struktur SQL :
CREATE INDEX
nama_index ON nama_tabel (nama kolom);
Contoh :
- Pada tabel pegawai kita berikan index pada kolom gaji
untuk query sebagai berikut :
Create index gaji_index on pegawai(gaji_peg);
Hasil :
indeks sebaiknya
jangan digunakan pada tabel atau kolom yang sangat jarang atau
tidak pernah diakses.
Selain untuk perintah SELECT Indeks juga
bermanfaat untuk
UPDATE dan DELETE yang menggunakan kondisi pencarian.
Sedangkan Unique index
mirip dengan indeks tetapi lebih digunakan untuk mencegah
duplikasi nilai yang terdapat
dalam tabel. Jadi dengan adanya unique index berarti pembaca tidak dapat
meng-insert
nilai yang sama dalam sebuah tabel. Berikut struktur SQL nya
:
CREATE UNIQUE INDEX
nama_index ON nama_tabel (nama kolom);
Untuk menghapus
index berikut strukturnya :
DROP INDEX
Nama_index;
- Pada tabel pegawai kita berikan index yang bersifat unik
pada kolom nama, untuk query
sebagai berikut :
Create unique index unama_index on
pegawai(nama_peg);
Apabila kita
memasukkan dengan nama yang sudah terdapat pada data terdahulu
akan terdapat error.
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);
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
Contoh :
Check
Check berfungsi untuk melakukan pembatasan nilai masukan
dalam sebuah
kolom, sebagai contoh
misalkan kita ingin agar kolom
gender yang terdiri dari satu
karakter hanya memiliki dua pilihan karakter yaitu M (male) atau F
(Fimale) ini dapat
kita seting dengan menggunakan CHECK. Dengan menggunakan
CHECK maka sebuah
kolom hanya bisa diisi dengan data yang memenuhi kriteria dalam CHECK. Berikut
query contoh pengunaan check :
db_contoh=> CREATE
TABLE pelanggan (
db_contoh(> nama
varchar(35), db_contoh(> kode_area CHAR(10) CHECK
(length(trim(kode_area))
= 2),
db_contoh(> umur
INTEGER CHECK (umur >= 0),
db_contoh(> gender
CHAR(1) CHECK (gender IN ('L', 'P')),
db_contoh(> ttl
DATE CHECK (ttl BETWEEN '1998-01-01' AND
CURRENT_DATE),
db_contoh(> CHECK
(upper(trim(nama)) != 'nita' OR
db_contoh(>
upper(trim(nama)) != 'jeki')
db_contoh(> );
CREATE
Penggunaan TRIM
Suatu ketika pasti
akan memiliki data yang di dalamnya terdapat spasi kosong
yang tidak diperlukan, misalnya spasi ganda. Jika ada
masalah seperti ini, kita dapat
membersihkan
spasi-spasi kosong yang tidak diperlukan menggunakan fungsi TRIM,
RTRIM, dan LTRIM. Ketiga fungsi ini memiliki bentuk
penggunaan sebagai berikut :
- RTRIM : digunakan
untuk membersihkan spasi kosong yang ada di bagian kanan
(Right) String.
- LTRIM : digunakan
untuk membersihkan spasi kosong yang ada di bagian kiri (Left)
String.
- TRIM : digunakan
untuk membersihkan spasi kosong yang ada di bagian kiri, kanan,
maupun tengah String
Berikut Struktur SQL nya :
Select
trim(nama_kolom) from nama_tabel;
Dalam penggunaannya, fungsi TRIM memiliki tiga opsi. Ketiga
opsi ini dapat
digunakan untuk menentukan karakter apa yang akan dihapus
dari suatu String. Jadi,
fungsi TRIM juga dapat menghilangkan karakter tertentu
(bukan spasi kosong saja) dari
suatu string. Opsinya sebagai berikut :
- LEADING : merupakan opsi untuk menghilangkan karakter terpilih yang ada di
sebelah kiri. Parameter Leading diartikan sebagai sufik dari
karakter yang ada.
- TRAILING : merupakan opsi untuk menghilangkan karakter
terpilih yang ada di
sebelah kanan String. Parameter Trailing diartikan sebagai
sufik dari karakter yang
ada.
- BOTH : merupakan
opsi yang dapat menangani parameter Leading maupun Trailing.
Berikut Struktur SQL nya :
Select trim(LEADING
‘karakter, misal : -’ from nama_kolom) from nama_tabel;
Tugas Praktikum
Tugas praktikum kali ini masih menggunakan tabel pada
praktikum sebelumnya :
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!
4. Buat kolom nama di mahasiswa menjadi unik dan
inputkan 2 data yang sama. Kemudian
amati perbedaannya !
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!
8. Munculkan data
mahasiswa dengan hilangkan karakter “+” di akhir data dan karakter “a”
di awal kata pada kolom nama!
Tugas Rumah
1. Buat laporan
praktikum menggunakan DBMS mysql untuk mengerjakan tugas praktikum
1-8 dan letakkan di blog.
Jawaban
1. tampilkan nama fakultas dan jumlah mahasiswa yang mempunyai ketentuan nama fakultas yang dimunculkan dengan jumlah mahasiswa terkecil.
select nama_fak, count(*) from mahasiswa, fakultas where mahasiswa.id_fak=fakultas.id_fak group by nama_fak order by nama_fak asc limit 1;
2.tampilkan nama mahasiswa, nama fakultas, alamat dengan syarat nama fakultas sama dengan edi dan alamat nya tidak boleh sama dengan luki.
select nama_mah, nama_fak, alamat_mah from mahasiswa, fakultas where mahasiswa.id_fak=fakultas.id_fak and nama_fak in (select nama_fak from mahasiswa, fakultas where mahasiswa.id_fak=fakultas.id_fak and nama_mah='edi' and alamat_mah <> (select alamat_mah from mahasiswa where nama_mah='luki'));
3. buatlah index di tabel mahasiswa(alamat). kemudian buat lagi index yang bersifat unik pada tabel fakultas (fak_nama) kemudian amati perbedaannya ketika memasuan data yang sama.
create index alamat_index on mahasiswa(alamat_mah);
create unique index fak_index on fakultas (nama_fak);
4.buat kolm nama mahasiswa menjadi unik dan inputkan 2 data yang sama.
alter table mahasiswa add unique (nama_mah);
5. pindahkan data dari tabel mahasiswa, fakultas ambil kolom nim , nama mahasiswa, alamat, nama fakultas ke tabel naru yang dinamai tabel identitas.
select nm_mah, nama_mah, nama_fak into tabel_identitas from mahasiswa, fakultas where mahasiswa.id_fak=fakultas.id_fak;
6.
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+++".
insert into mahasiswa values (7,'sule +++','malang',2,'08343846246','P');
8.munculkan data mahasiswa dengan hilangkan karakter "+" di akhir data dan karakter "a" di awal kolom nama.
select nim_mah, trim(both'+a' from nama_mah), alamat_mah, id_fak, no_telp, gender from mahasiswa;
Perbedaan
Pada pembahasan sub query
dan indeks kali ini, bisa kita lihat beberapa perbedaan antara kedua DBMS
yang kita pakai buat percobaan yaitu DBMS Postgresql dan DBMS MySQL. Yang
pertama kedua DBMS ini memiliki perbedaan yang sudah kita pernah bahas sebelum-sebelumnya,
yaitu ketika koneksi database yang kita pakai missal pada Mysql kita bisa
menggunakan “USE nama_database” sedangkan pada Postgresql kita
bisa menggunakan “\c nama_database”. yang kedua, yaitu pada
Postgresql kita bisa melakukan perintah CHECK seperti yang di contohkan pada
modul sedangkan pada Mysql kita bisa mebuat CHECK namun CHECK yang sudah kita
buat tidak ada pengaruhnya, karena pada Mysql tidak ada perintah CHECK,
sehingga yang terjadi pada mysql adalah ketika kita memasukkan data yang tidak
sesuai dengan CHECK, data tersebut akan tetap masuk (terInsert).
Kesimpulan, Kritik dan Saran
Sub Query merupakan query yang digunakan untuk menspesifikasikan perintah
yang memiliki kondisi tertentu dengan cara memberikan perintah SELECT di dalam
SELECT. Perintah tersebut akan sangat membantu kita apabila memiliki banyak
sekali data, sehingga kita bisa menemukan data tersebut dengan cepat. Tetapi
kita juga harus melihat suatu DBMS tersebut macam-macam query yang variatif
atau tidak, karena apabila suatu DBMS tersebut terdapat satu SQL yang tidak ada
sementara pada DBMS lain ada maka kita akan kekurangan satu fungsi perintah
untuk mendapatkan data yang tepat. Jadi juga perhatikan DBMS yang digunakan
agar kita bisa mendapatkan data yang tepat untuk di tampilkan
Training Scriptcase di Indonesia, kunjungi www.OwenSolution.com
BalasHapus