SUB QUERY DAN INDEX
a.
DASAR TEORI
Query select into
Query
select into statement merupakan query SQL yang digunakan untuk mengopi
informasi dari tabel ke tabel yang lain tanpa membuat tabel sebelumnya. 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;
SQL Sub Query
Sub
Query adalah suatu query yang menjadi bagian dari suatu query. Sub Query
digunakan untuk menangani masalah yang kompleks yang mungkin sulit untuk
dilakukan hanya dengan sebuah query dan juga Menyediakan cara alternatif untuk
melakukan operasi yang membutuhkan join atau union yang rumit.
Subquery
atau Batin permintaan atau Query bersarang adalah query dalam query. SQL
subquery biasanya ditambahkan dalam MANA Klausul pernyataan SQL. Sebagian besar
waktu, subquery digunakan ketika kita tahu bagaimana untuk mencari nilai
menggunakan pernyataan SELECT, tetapi tidak tahu nilai yang tepat dalam
database.
ATURAN SUB QUERY
• Dalam sebuah query
boleh memiliki lebih dari 1 sub query.
• Sebuah sub query
boleh memiliki sub query lagi.
• Operator perbandingan yang
dapat digunakan adalah =, >, <, >=, <=, <>, !=, <=>,
IN, ANY, SOME, ALL, EXISTS, NOT EXISTS
OPERATOR ANY, SOME, ALL DALAM SUB
QUERY
•
Operator ANY, SOME dan ALL, harus
diawali dengan penggunaan operator perbandingan =, >, <, >=, <=,
<>, !=, <=>.
•
Operator ANY akan memeriksa apakah suatu
nilai dari outer query sesuai dengan SALAH SATU anggota dari hasil sebuah sub
query. Kondisi sesuai ditentukan oleh operator yang ditulis sebelumnya.
• Operator SOME adalah alias dari ANY
•
Operator ALL akan memeriksa apakah suatu
nilai dari outer query sesuai dengan SEMUA anggota dari hasil sebuah sub query.
• Sub query boleh memiliki data
lebih dari 1 baris.
OPERATOR IN DALAM SUB QUERY
•
Operator IN akan memeriksa apakah suatu nilai di outer query ada dalam sebuah
hasil sub query.
• Operator IN bisa
disamakan dengan operator “= ANY”
• Lawan hasil dari
operasi IN adalah NOT IN.
• Operator NOT IN
bisa disamakan dengan “<> ALL”
• Sub query boleh memiliki data
lebih dari 1 baris.
EXISTS DAN NOT EXISTS
• EXISTS digunakan untuk
memeriksa apakah subquery memiliki baris atau tidak. Jika minimal ada 1 baris
(walaupun hanya berisi NULL), maka akan bernilai TRUE
• NOT EXISTS adalah kebalikan
dari EXISTS.
Penggunaan sub query dapat diterapkan pada
pernyataan SELECT, UPDATE, DELETE, dan INSERT.
Berikut
Penjelasan tentang masing- masing di atas.
1. Pernyataan
SQL SELECT
Perintah
SQL yang paling umum digunakan adalah pernyataan SELECT. Pernyataan SQL SELECT
digunakan untuk query atau mengambil data dari tabel dalam database. Sebuah
query dapat mengambil informasi dari kolom tertentu atau dari semua kolom dalam
tabel. Untuk membuat SQL SELECT Pernyataan sederhana, kita harus menentukan
kolom (s) nama dan nama tabel. Seluruh query disebut SQL SELECT Pernyataan.
Sintaks
dari SQL SELECT Statement:
SELECT column_list
FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];
table-name
adalah nama tabel dari mana informasi tersebut diambil.
column_list
mencakup satu atau lebih kolom dari data yang diambil.
Kode
dalam kurung adalah opsional.
2. Pernyataan
INSERT SQL
Pernyataan
INSERT digunakan untuk menambahkan baris data yang baru ke meja.
Kita bisa memasukkan
data ke sebuah meja dengan dua cara,
1)
Memasukkan data secara langsung ke sebuah meja.
Sintaks
untuk SQL INSERT adalah:
INSERT INTO TABLE_NAME
[ (col1, col2,
col3,...colN)]
VALUES (value1,
value2, value3,...valueN);
col1,
col2, ... Coln - nama-nama kolom dalam tabel di mana kita ingin menyisipkan
data.
Saat
memasukkan berturut-turut, jika kita menambahkan nilai untuk semua kolom dari
tabel kita tidak perlu menentukan kolom (s) nama dalam query sql. Tapi kita
harus memastikan urutan nilai-nilai dalam urutan yang sama seperti kolom dalam
tabel. Query sql insert adalah sebagai berikut
INSERT INTO TABLE_NAME
VALUES (value1,
value2, value3,...valueN);
2)
Memasukkan data ke tabel melalui pernyataan pilih.
Sintaks
untuk SQL INSERT adalah:
INSERT INTO table_name
[(column1, column2,
... columnN)]
SELECT column1,
column2, ...columnN
FROM table_name [WHERE
condition];
~
Ketika menambahkan baris baru, kita
harus memastikan datatype nilai dan kolom sesuai
3)
Pernyataan UPDATE SQL
Pernyataan
UPDATE digunakan untuk memodifikasi baris yang ada dalam sebuah tabel.
Sintaks
untuk SQL UPDATE Command adalah:
UPDATE table_name
SET column_name1 =
value1,
column_name2 = value2,
...
[WHERE condition]
table_name
- nama tabel yang harus diperbarui.
column_name1,
column_name2 .. - Kolom yang akan diubah.
value1,
value2 ... - Adalah nilai-nilai baru.
CATATAN:Dalam
pernyataan Update, klausa WHERE mengidentifikasi baris yang terpengaruh. Jika Kita
tidak menyertakan klausa WHERE, nilai kolom untuk semua baris terpengaruh.
4)
SQL Hapus Pernyataan
Pernyataan
DELETE digunakan untuk menghapus baris dari tabel.
Sintaks
dari SQL DELETE Statement:
DELETE FROM table_name
[WHERE condition];
table_name
- nama tabel yang harus diperbarui.
CATATAN:
dimana klausul dalam perintah menghapus
sql adalah opsional dan mengidentifikasi baris dalam kolom yang akan dihapus.
Jika kita tidak menyertakan klausa WHERE semua baris dalam tabel akan dihapus,
jadi hati-hati saat menulis query DELETE tanpa klausa WHERE.
Pengertian Index
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
hal yang sangat terbantu dnegan adanya indeks adalah :
A. Proses penggabungan sejumlah tabel
B. Proses dengan ORDER BY
C. Proses fungsi agregat seperti MIN dan MAX
D. 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);
Disini
akan ditunjukan beberapa manfaat dari proses index.
1. 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';
2. 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).
3. Membantu mempercepat proses pengitungan data
denga fungsi agregasi.
Akan
dihitung gaji terbesar untuk pegawai yang perkerjannya adalah 'SALESMAN', maka
perintah yang dituliskan :
4. 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);
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
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
TRIM
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.
B.
PEMBAHASAN
Setelah
kita membaca teori tentang Sub Query di atas, maka sekarang kita akan
mempraktekan bahasan Sub Query dan indeks tersebut . Untuk melatih pemahaman
kita tentang sub query dan indeks, gak ada salahnya kan kalau kita menjawab
soal di bawah ini , oke deh , oh iya ..
Berikut
hasil praktikum saya, baik menggunakan mysql maupun postgresql
1.
Tampilkan nama fakultas dan jumlah mahasiswa
yang mampunyai ketentuan nama fakultas yang dimunculkan dengan jumlah
mahasiswanya terkecil
MYSQL
POSTGRESQL
2.
Tampilkan nama mahasiswa, nama fakultas, alamat
dengan syarat nama fakultas sama dengan edi dan alamatnya tidak sama dengan
luki!
MYSQL
POSTGRESQL
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!
MYSQL
POSTGRESQL
Pada saat kita merubah
nama menjadi index , maka ketika kita memasukkan data yang sama pada kolom
nama, maka tetap berjalan , tidak terjadi error.
Nahh sekarang kita ubah
kolom nama_fak menjadi index yang unik.
Pada saat kita
memasukkan data yang sama pada kolom nama_fak,maka akan terjadi eror, karena
nama_fak telah bersifat unik, sehingga datanya tidak boleh ada yang sama.
4.
Buat kolom nama di mahasiswa menjadi unik dan
inputkan 2 data yang sama. Kemudian amati perbedaannya !
MYSQL
Pada saat kita meng
input kan data yang sama pada kolom nama maka akan terjadi eror, karena kolom
nama telah berubah menjadi kolom unik.
POSTGRESQL
5.
Pindahkan data dari tabel mahasiswa, fakultas
ambil kolom nim, nama mahasiswa, alamat, nama fakultas ke tabel baru yang
dinamai „tabel identitas‟.
MYSQ
POSTGRESQL
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
MYSQL
Postgree
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!
MYSQL
POSTGRESQL
Cara untuk penghapusan
spasi
8. Munculkan
data mahasiswa dengan hilangkan karakter “+” di akhir data dan karakter “a” di
awal kata pada kolom nama!
MYSQL
POSTGRESQL
c. Evaluasi atau Perbandingan
Ditinjau dari hasil praktikum
minggu ini, adapun perbandingan sub
query diantara MySQL dan PostgreSQL ,
tidak terjadi perbedaan yang terlalu signifikan diantara keduanya , namun hanya
saja dalam hal konteks indeks, backup data, dan trim terdapat sedikit perbedaan
.berikut penjelasannya:
Jadi, dalam hal indeks,
perbedaannya terletak pada drop indexnya. Ketika menggunakan postgreSQL, syntax
yang kita gunakan adalah
Drop index nama_index;
Sedangkan pada MySQL, jika kita
menggunakan syntax tersebut maka perintah penghapusannya tidak akan berjalan.
Jadi kita menggunakan syntax :
Alter table nama_table_dari_Index drop
index nama_index;
Ketika memindahkan data dari
tabel mahasiswa di PostgreSQL cukup memberikan perintah kolom mana yang ingin
ditampilkan kemudian langsung memasukkan kedalam tabel identitas seperti
berikut :
Select nim_mah, nama_mah, alamat_mah,
nama_fak into identitas from mahasiswa,fakultas WHERE
mahasiswa.id_fak=fakultas.id_fak;
sedangkan di MySQL harus membuat
tabel setelah itu baru memasukkan kolom ke dalam tabel identitas,perintahnya
sebagai berikut :
CREATE TABLE identitas SELECT nim_mah,
nama_mah, alamat_mah, nama_fak FROM mahasiswa,fakultas WHERE
mahasiswa.id_fak=fakultas.id_fak;
Dan selanjutnya adalah pada trim.
Trim yang dipisahkan dengan spasi dengan kolom yang akan di eksekusi tidak
berpengaruh pada PostgreSQL, sedangkan pada MySQL, jika kita menyisipkan spasi
antara perintah trim dengan kolom yang akan di – trim , maka akan terjadi
kesalahan. Karena disini trim akan terdeteksi sebagai nama fungsi. Jadi,
penggunaan trim tidak boleh dipisahkan dengan nama kolom yang akan dieksekusi.
D. Kesimpulan
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.
Ø
Query select into statement merupakan query SQL yang digunakan untuk mengopi informasi
dari tabel ke tabel yang lain tanpa membuat tabel sebelumnya.
Ø
Subquery atau query Nested merupakan bentuk query yang
terdapat dalam query yang lain.
Ø
Indeks disini berguna dalam suatu pencarian nilai atau data
dalam database.
Ø
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.
Ø
Check berfungsi untuk melakukan pembatasan nilai masukan
dalam sebuah kolom
Perintah TRIM dibagi menjadi 3 yaitu :
1.
RTRIM : digunakan untuk membersihkan spasi kosong yang ada
di bagian kanan (Right) String.
2.
LTRIM : digunakan untuk membersihkan spasi kosong yang ada
di bagian kiri (Left) String.
3.
TRIM : digunakan untuk membersihkan spasi kosong yang ada di
bagian kiri, kanan, maupun tengah String
Kritik dan Saran
Nahhh,, gimana nih
pemahaman kalian tentang subquery dan index , kurang lebih sudah begitu paham
ya . biar lebih di luar kepala banyak berlatih dan mencoba ya. Oiya jangan
terlalu berpacu dengan hasil praktikum di kelas, karena query nya itu kadang
sama persis tapi kadang juga beda banget,jadi waspada ya,, hhe semangatt..
Oiya Buat sobat yang sekarang duduk di bangku kuliah ni,,
kalau ada tugas supaya langsung dikerjakan ya, karena kalau tidak maka kita kan
mengalami stress, atau bahkan sampai sakit gara- gara memikirkan tugas yang gak
kelar-kelar, hhe lebay ya,, gak kok karena itu pengalaman saya, jadi rasanya
ituu gak enak banget,
Manfaat
Semoga dengan postingan
kali ini banyak memberikan manfaat untuk sobat semuanya,walaupun jauh dari
sempurna . setidaknya sudah memberikan ilmu baru bagi sobat semua. mohon maaf bila ada kekeurangan . Makasihh
Daftar Rujukan
modul
praktikum 7
Kadir,Abdul.2002.Penuntun
Praktis Belajar Sql.Yogyakarta.Andi
Tidak ada komentar:
Posting Komentar