Sub Query dan Indeks

SUBQUERY DAN INDEKS

A. LANDASAN TEORI

     SubQuery adalah query nested atau sebuah query ada di dalam query. Subquery merupakan pernyataan SELECT yang merupakan bagian dari pernyataan INSERT, SELECT. Subquery digunakan untuk menangani masalah dalam query yang kompleks bahkan kita tidak tau nilai berapa yang akan diselect atau di insert. 

Beberapa kegunaan subquery :

- Sub Query digunakan untuk menyelesaikan persoalan dimana terdapat suatu nilai yang tidak diketahui.
- Mengcopy data dari satu tabel ke tabel lain,
- Menerima data dari inline view
- Mengambil data dari tabel lain untuk kemudian di update ke tabel yang dituju.
- Menghapus baris dari satu tabel berdasarkan dari tabel lain.
Bentuk umum dari sub query adalah :
SELECT SELECT_LIST FROM TABLE WHERE EKSPRESI_OPERATOR (SELECT SELECT_LIST FROM TABLE  WHERE EKSPRESI_OPERATOR VALUE');

Operator Exist 
     Merupakan jenis operator boolean, yang menghasilkan nilai benar (true) atau salah (false). Operator exist akan memberikan nilai benar (true) kalau sub query paling tidak menghasilkan paling tidak sebuah baris/record.
Berikut querynya :
select nama_kolom from nama_tabel where exist (select nama_tabel from nama_tabel where kondisi);

Operator Any 
     Operator any paling sama penggunaannya seperti exist. Tetapi operator relasi yang digunakan biasanya selain '=' (sama dengan). Hal tersebut disebabkan apabila operator relasi = digunakan, maka sebetulnya fungsi operator any (some) sama seperti operator IN, sehingga kondisi seperti itu tidak dianjurkan karena lebih mudah pemahamannya apabila menggunakan IN.
Berikut query nya:
select nama_kolom from nama_tabel where nama_kolom operator_any (select nama_kolom from nama_tabel);

Operator All
     Operator all digunakan untuk melakukan pembandingan dengan sub query. Kondisi dengan all menghasilkan nilai benar jika pembanding menghasilkan benar untuk setiap nilai dalam sub query.


1. SELECT INTO STATEMENT
       Merupakan query SQL yang digunakan untuk mengopi informasi tanpa harus membuat tabel sebelumnya.
Bentuk Query nya adalah:
-- select * into tabel_baru from tabel1;

2. TABEL INDEKS
       Sebuah indeks database adalah sebuah struktur data yang meningkatkan kecepatan operasi dalam sebuah tabel. Indeks dapat dibuat menggunakan satu atau lebih kolom, menyediakan dasar untuk kedua pencarian acak yang cepat dan efisien pemesanan akses ke catatan. Sementara untuk menciptakan indeks itu harus dipertimbangkan bahwa apa yang kolom yang akan digunakan untuk membuat query SQL dan membuat satu atau lebih indeks pada kolom tersebut.
      Praktis, indeks juga jenis tabel yang menjaga kunci primer atau bidang indeks dan pointer ke setiap record dalam ke meja yang sebenarnya. Para pengguna tidak dapat melihat indeks, mereka hanya digunakan untuk mempercepat query dan akan digunakan oleh database engine search untuk mencari catatatn sangat cepat.
        INSERT dan UPDATE laporan membutuhkan waktu lebih lama dimeja memiliki indeks dimana sebagai pernyataan SELECT menjadi cepat pada mereka tabel. Alasannya adalah bahwa saat melakukan insert atau update, database perlu insert atau memperbarui nilai indeks juga,

>> Indeks Sederhana dan Unik
Sebuah indeks yang unik berarti bahwa dua baris tidak dapat memiliki indeks yang sama.
Berikut adalah syntaknya:
-- create unique index nama_indeks on nama_tabel (kolom, kolom2,...);
Jika ingin membuat indeks nilai dalam sebuah kolom di urutan, Anda dapat menambahkan kata DESC dicadangkan setelah nama kolom.
-- create unique index author_index on nama_indeks (nama_kolom desc);

>> Perintah alter untuk menambah dan drop indeks.
ada empat jenis laporan untuk menambahkan indeks untuk tabel:

-- Alter table nama_tabel add primary key (daftar_kolom); pernyataan ini menambahkan primary key, yang berarti bahwa nilai-nilai diindeks harus unik dan tidak dapat menjadi null.
-- Alter table add unique nama_tabel nama_indeks (daftar_kolom); pernyataan ini membuat indeks yang nilai harus unik (dengan pengecualian nilai null yang muncul beberapa kali).
-- Alter table add index nama_indeks nama_tabel (daftar_kolom); ini menambahkan indeks biasa dimana nilai apapun mungkin muncul lebih dari sekali.
-- Alter table add full text nama_tabel nama_index (daftar_kolom); ini menciptakan indeks fulltext khusus yang digunakan untuk teks mencari tujuan.

>> Perintah alter untuk menambah dan drop primary key.

Anda dapat menambahkan kunci utama juga dalam cara yang sama. Tapi pastikan primary key bekerja pada kolom yang not null. Berikut ini adalah query nya :

pertama tambahkan dulu kolom not null dg query
-- alter table nama_tabel modify nama_kolom type_kolom not null;

lalu tambahkan primary key
-- alter table nama_tabel add primary key (nama_kolom);

>> Untuk menampilkan tabel indeks
bisa menggunakan query :

-- show index from nama_tabel; 

3. FUNGSI CHECK

    Check berfungsi untuk melakukan pembatasan nilai masukan dalam sebuah kolom. Dengan menggunakan check maka sebuah kolom hanya bisa diisi oleh data yang memenuhi kriteria check. 

4. FUNGSI 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 sebagainya. Apalagi pada banyak kasus, tipe data ini sangat diandalkan.

Berikut adalah cara trim :

Trim ([{both | leading | trailing} [x] from] y)

- Leading digunakan untuk membuang string x didepan string y. 

- Trailing digunakan untuk membuang string x dari belakang string y.

- both digunakan untuk membuang string x didepan dan belakang string y.

ada juga selain trim yakni :

RTRIM = Membuang semua spasi yang ada disebelah kanan string.

LTRIM = Membuang semua spasi yang ada disebelah kiri string.

TRIM  =  Membuang semua spasi yang ada disebelah kiri, kanan maupun tengah string.

B. TUJUAN 

- Mahasiswa memahami dan menggunakan sub query dan indeks dalam pengolahan database.

- Mahasiswa memahami cara penggunaan cluster, kolo unik dan trim.

C. HASIL PRAKTIKUM

Tugas praktikum kali ini masih menggunakan tabel pada praktikum sebelumnya.

1. Tampilkan nama fakultas dan jumlah mahasiswa yang mempunyai ketentuan nama fakultas yang dimunculkan dengan jumlah mahasiswanya terkecil.!

gunakan user kita

mysql -u visa

gunakan database kita

use visa13650065;

lihat data mahasiswa yang ada

jika ingin melihat jumlah terkecil mahasiswa pada fakultas

select nama_fak, count(*) as jumlah from mahasiswa m, fakultas f where m.id_fak = f.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 alamatnya tidak 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, fakultas where mahasiswa.id_fak = fakultas.id_fak and 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 memasukkan data yang sama!,

buat tabel index dulu dg query :

create index alamat on mahasiswa (alamat_mah);

lihat data mahasiswa sebelum dimasukkan data

dan setelah dimasukkan data, hasilnya meskipun dalam kolom alamat terdapat alamat malang namun masih bisa dimasukkan lagi alamat yang sama,

buat index yang bersifat unik.

create unique index fakultasdua on fakultas (nama_fak);

kemudian masukkan data dengan nama fakultas yang sama. hasilnya yidak bisa masuk karena indeks unik tidak bisa diduplikasi.

4. Buatlah kolom nama di mahasiswa  menjadi unik dan inputkan 2 data yang sama. Kemudian amati perbedaannya.!

buat tabel unik

create unique index nama on mahasiswa (nama_mah);

Kemudian cek dengan memasukkan 2 data yang sama, hasilnya data tidak bisa dimasukkan karena terdapat duplikasi namun hanya ada 1 data yang masuk yakni data pertama yang beralamatkan bali.

insert into mahasiswa values (21, 'ana', 'bali, 2, '08765433334', 'P), ('22', 'ana', 'makasar', 1, '0828879999', 'P');

5. Pindahkan data dari tabel mahasiswa, fakultas ambil kolom nim, nama mahasiswa, alamat, nama fakultas ke tabel baru yang dinamai 'tabel identitas'.

Buat tabel identitas

create table identitas select nim_mah, nama_mah, alamat_mah, no_telp, nama_fak from mahasiswa m, fakultas f where m.id_fak = f.id_fak;

lihat tabel identitasnya

6. Buatlah contoh penggunaan check pada sub bab pembahasan check. Kemudian masukkan beberapa data baik yang sesuai dengan kriteria check maupun yang bukan dan amati perbedaannya.

Pada mysql tidak bisa dibuat ngecek data.


7. Inputkan data di tabel mahasiswa dimana pada kolom nama sebelum inputan karakter dahulukan dengan spasi dan akhiri dengan tanda "+" seperti berikut ini : "   andi cahyono+++". kemudian munculkan seluruh data dengan hilangkan spasi depan.

Inpu data andi cahyono.

insert into mahasiswa (22, '   andi cahyono+++', 'lombok', 2, '0822023987', 'L');

lalu sekalian mengecek data mahasiswanya

Hilangkan spasi depan

select trim(nama_mah) from mahasiswa;

8. Munculkan data mahasiswa dengan hilangkan karakter "+" di akhir data dan karakter "a" diawal kata pada kolom nama!

select nim_mah trim(trailing "+" from nama_mah), trim (leading "a" from nama_mah) from mahasiswa;

D. PERBANDINGAN POSTGRE DAN MYSQL

-- Untuk mencari jumlah data terkecil
pada postgre

select nama_fak, count(*) as jumlah from mahasiswa m, fakultas f where m.id_fak = f.id_fak group by nama_fak order by count asc limit 1;

pada mysql

select nama_fak, count(*) as jumlah from mahasiswa m, fakultas f where m.id_fak = f.id_fak group by nama_fak order by nama_fak asc limit 1;

-- Untuk mencari kondisi dengan ketentuan soal nomer 2
query nya sama

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, fakultas where mahasiswa.id_fak = fakultas.id_fak and nama_mah = 'luki');

-- Untuk membuat tabel index
query nya sama

create index mahasiswa_index on mahasiswa(alamat_mah);

-- Untuk membuat index yang bersifat unik
query nya sama

create unique index fakultasdua on fakultas (nama_fak);

-- Memindahkan tabel seperti pada soal nomer 5
Pada postgre

select nim_mah, nama_mah, alamat_mah, nama_fak into identitas from mahasiswa m, fakultas f where m.id_fak = f.id_fak;

Pada mysql

create table identitas select nim_mah, nama_mah, alamat_mah, no_telp, nama_fak from mahasiswa m, fakultas f where m.id_fak = f.id_fak;

-- Untuk menghapus spasi didepan.
Pada postgre

select trim (both ' ' from nama_mah) from mahasiswa;

Pada mysql

select trim(nama_mah) from mahasiswa;

Pada mysql juga bisa menggunakan query postgre yakni :

select trim (both ' ' from nama_mah) from mahasiswa;

-- Untuk menghilangkan karakter (+) diakhir dan karakter 'a' diawal kata
Pada postgre

select trim (both ' a,+' from nama_mah) from mahasiswa;

Pada mysql

select nim_mah trim(trailing "+" from nama_mah), trim (leading "a" from nama_mah) from mahasiswa;

E.KESIMPULAN

        Pada bab kali ini membahas tabel-tabel index dan macam-macam sub query, dan ada juga fungsi trim. Sub query sendiri adalah penggunaan statement select didalam statement select yang lain. dan ada juga tabel indeks yakni tabel sebuah struktur data yang meningkatkan kecepatan operasi dalam sebuah tabel. Indeks yang bersifat unik sendiri adalah Sebuah indeks yang unik berarti bahwa dua baris tidak dapat memiliki indeks yang sama. Check untuk melakukan pembatasan nilai masukan dalam sebuah kolom. dan yang terakhir ada fungsi trim yang digunakan untuk Menghilangkan karakter pada suatu data atau menghilangkan beberapa karakter.


F. PENUTUP

     Tidak ada gading yang tak retak, kurang lebihnya mohon maaf. namanya juga belajar mohon dimaklumi dan mungkin ada beberapa saran yang mesti saya perbaiki mohon supaya dikasih sarannya.


G. DAFTAR PUSTAKA

http://codeoke.blogspot.com/2012/12/sub-query-mysql.html
http://beginner-sql-tutorial.com/id/sql-subquery.htm
http://tutorial.belajarweb.net/mysql/mysql-indexs.html
https://amahrizal.wordpress.com/2013/02/19/cara-cek-versi-mysql/
http://www.zainalhakim.web.id/posting/membuat-fungsi-cek-data-mysql-dengan-php.html
http://www.slideshare.net/achmatim/my-sql-perintah-query-untuk-mencari-record-yang-tidak-ada-di-tabel-lain-achmatimnet
http://tutorial.dumbstrack.org/menghilangkan-karakter-pada-suatu-data-di-mysql/
http://neonevz.blogspot.com/2012/06/fungsi-fungsi-pada-mysql.html