Laporan Praktikum Desain Basis Data “SUB QUERY DAN INDEKS”

      1.      Materi/ Teori

A.    SUB QUERY

Sub Query adalah query nested atau sebuah query yang berada di dalam query. sub query merupakan pernyataan SELECT yang merupakan bagian dari pernyataan INSERT dan SELECT. Sub Query digunakan untuk menangani masalah dalam query yang kompleks, contohnya ketika Anda tahu bagaimana untuk mencari nilai menggunakan pernyataan SELECT, tetapi tidak tahu nilai yang tepat dalam database.

adapun kegunaan sub query yang lain adalah:

A.    Meng-copy data dari satu tabel ke tabel lain

B.     Menerima data dari inline view

C.     Mengambil data dari tabel lain untuk kemudian di update ke tabel yang dituju

D.    Menghapus baris dari satu tabel berdasarkan baris dari tabel lain

Sub Query dapat ditempatkan  bersama klausa where, having, dan from bersama dengan operator perbandingan =, in, not, in, <>, <any>, >=, dan <=. penggunaannya dapat diterapkan pada pernyataan SELECT, UPDATE, DELETE, dan INSERT.

Select nama_kolom from nama_tabel where nama_kolom  operator (subquery);

B.     SELECT INTO Statement

Merupakan query SQL yang digunakan untuk menduplikasi informasi dari tabel satu ke tabel yang lain tanpa membuat tabel sebelumnya

SELECT * INTO newtable FROM table1;

C.    INDEKS

Indeks berguna untuk mepersingkat pencarian data dalam database dengan meng-indeks (memberi keyword unik) pada kolom tertentu. sehingga proses pencarian lebih effisien. untuk database skala kecil penggunaan indeks, dampaknya belum terasa. jika menggunakan database skala besar, penggunaan indeks akan sangat membantu sekali dalam proses pencarian data.  indeks sebaiknya jangan digunakan pada tabel atau kolom yang sangat jarang atau tidak pernah diakses.

cara membuat indeks sebagai berikut:

CREATE INDEX nama_index ON nama_tabel (nama kolom);

Ada pula query UNIQUE INDEKS,  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.

CREATE UNIQUE INDEX nama_index ON nama_tabel (nama kolom);

D.    CHECK

Query check berfungsi untuk membatasi inputan agar sesuai dengan ketentuan admin. 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.

alter table nama_tabel add check (nama_kolom in (‘kondisi’,’kondisi’));

E.     TRIM

Trim digunakan apabila dalam suatu data ada spasi yang tidak diperlukan. dengan query trim, kita dapat membersihkan spasi yang tidak digunakan tersebut. ada tiga jenis fungsi trim:

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

Select trim(nama_kolom) from nama_tabel;

Untuk penggunaan query trim, kita dapat menentukan karakter yang akan dihapus, bukan hanya spasi saja. dengan menambahkan opsi tersebut pada struktur query trim:

LEADING : merupakan opsi untuk menghilangkan karakter terpilih yang ada disebelah kiri. Parameter Leading diartikan sebagai sufik dari karakter yang ada.

TRAILING : merupakan opsi untuk menghilangkan karakter terpilih yang ada disebelah kanan String. Parameter Trailing diartikan sebagai sufik dari karakter yang ada.

BOTH : merupakan opsi yang dapat menangani parameter Leading maupun Trailing.

berikut struktur query nya:

Select trim(LEADING ‘karakter, misal : -’ from nama_kolom) from nama_tabel; 

    2.      Hasil Praktikum

A.    Hasil Praktikum PostgreSQL

1.      Menampilkan nama fakultas dan jumlah mahasiswa yang mempunyai ketentuan nama fakultas yang dimunculkan dengan jumlah mahasiswanya terkecil. Querynya sebagai berikut:

“SELECT nama_fak, count(*) as jumlah_mahasiswa from mahasiswa m, fakultas f where m.id_fak=f.id_fak GROUP BY f.nama_fak ORDER BY 2 ASC LIMIT 1;”.

2.      Selanjutnya menampilkan nama mahasiswa, nama fakultas, alamat dengan ketentuan nama fakultas sama dengan edi dan alamatnya tidak sama dengan luki.

Querynya sebagai berikut:

“SELECT nama_mah, nama_fak, alamat_mah from mahasiswa m, fakultas f where m.id_fak=f.id_fak and nama_fak in (SELECT nama_fak from fakultas f, mahasiswa m where m.id_fak=f.id_fak and nama_mah=’edi’) and alamat_mah <> SELECT alamat_mah from mahasiswa where nama_mah=’luki’);”

3.      Membuat index di tabel mahasiswa dalam kolom alamat, kemudian buat index lagi yang bersifat unik pada tabel fakultas dalam kolom fak_nama. Kemudian coba masukkan data yang sama.

Querynya seperti di bawah ini:

“CREATE INDEX ALAMAT_INDEX ON MAHASISWA (alamat_mah);

CREATE UNIQUE INDEX NAMA_FAK_INDEX ON FAKULTAS (nama_fak);”

4.      Buat kolom nama pada tabel mahasiswa menjadi unik, dan coba inputkan 2 data yang sama.

Querynya sebagai berikut:

“ALTER TABLE MAHASISWA ADD UNIQUE (nama_mah);”

Coba inputkan 2 nama yang sama.

5.      Pindah data dari tabel mahasiswa, fakultas ambil kolom nim, nama mahasiswa, alamat, dan nama fakultas ke tabel baru yang dinamai ‘identitas’.

Querynya sebagai berikut:

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

6.      Coba buat contoh pembuatan check. Kita akan coba langsung menambahkan pada tabel yang ada.

Querynya sebagai berikut:

“ALTER TABLE mahasiswa ADD CHECK (gender in (‘L’,’P’));”

Coba inputkan data mahasiswa yang gendernya sesuai dan tidak sesuai syarat.

7.      Inputkan data di tabel mahasiswa dimana sebelum karakter dahulukan spasi dan diakhiri dengan tanda ‘+’. Misalnya ‘    linta++++’. Lalu munculkan seluruh data dan hilangkan spasi di depan.

Querynya sebagai berikut:

“SELECT TRIM (LEADING ‘ ‘ from nama_mah) FROM mahasiswa;”.

8.      Lalu coba munculkan seluruh data mahasiswa dengan menghilangkan karakter di akhir data dan karakter ‘a’ di awal pada kolom nama.

Querynya sebagai berikut:

“SELECT TRIM (both ‘a,+’ from nama_mah) FROM mahasiswa;”.

B.     Hasil Praktikum MySql

1.      Menampilkan nama fakultas dan jumlah mahasiswa yang mempunyai ketentuan nama fakultas yang dimunculkan dengan jumlah mahasiswanya terkecil. Querynya sebagai berikut:

“SELECT nama_fak, count(*) as jumlah_mahasiswa from mahasiswa m, fakultas f where m.id_fak=f.id_fak GROUP BY f.nama_fak ORDER BY 2 ASC LIMIT 1;”.

2.      Selanjutnya menampilkan nama mahasiswa, nama fakultas, alamat dengan ketentuan nama fakultas sama dengan edi dan alamatnya tidak sama dengan luki.

Querynya sebagai berikut:

“SELECT nama_mah, nama_fak, alamat_mah from mahasiswa m, fakultas f where m.id_fak=f.id_fak and nama_fak in (SELECT nama_fak from fakultas f, mahasiswa m where m.id_fak=f.id_fak and nama_mah=’edi’) and alamat_mah <> SELECT alamat_mah from mahasiswa where nama_mah=’luki’);” atau tanda ‘<>’ bisa diganti ‘not in’.

3.      Membuat index di tabel mahasiswa dalam kolom alamat, kemudian buat index lagi yang bersifat unik pada tabel fakultas dalam kolom fak_nama. Kemudian coba masukkan data yang sama.

Querynya seperti di bawah ini:

“CREATE INDEX alamat_index2 ON MAHASISWA (alamat_mah);

CREATE UNIQUE INDEX nama_fak_index2 ON FAKULTAS (nama_fak);”

4.      Buat kolom nama pada tabel mahasiswa menjadi unik, dan coba inputkan 2 data yang sama.

Querynya sebagai berikut:

“ALTER TABLE MAHASISWA ADD UNIQUE (nama_mah);”

Coba inputkan 2 nama yang sama.

5.      Pindah data dari tabel mahasiswa, fakultas ambil kolom nim, nama mahasiswa, alamat, dan nama fakultas ke tabel baru yang dinamai ‘identitas’.

Querynya sebagai berikut:

Buat tabelnya dulu

“CREATE TABLE identitas (nim_mah integer not null, nama_mah varchar(20), alamat_mah varchar(15), nama_fak varchar(15));”

Pindah data

“INSERT INTO identitas SELECT nim_mah, nama_mah, alamat_mah, nama_fak from mahasiswa m, fakultas f where m.id_fak=f.id_fak;”.

6.      Coba buat contoh pembuatan check. Kita akan coba langsung menambahkan pada tabel yang ada.

Querynya sebagai berikut:

“ALTER TABLE mahasiswa ADD CHECK (gender in (‘L’,’P’));”

Coba inputkan data mahasiswa yang gendernya sesuai dan tidak sesuai syarat.

7.      Inputkan data di tabel mahasiswa dimana sebelum karakter dahulukan spasi dan diakhiri dengan tanda ‘+’. Misalnya ‘    linta++++’. Lalu munculkan seluruh data dan hilangkan spasi di depan.

Querynya sebagai berikut:

“SELECT TRIM (LEADING ‘ ‘ from nama_mah) FROM mahasiswa;”.

8.      Lalu coba munculkan seluruh data mahasiswa dengan menghilangkan karakter di akhir data dan karakter ‘a’ di awal pada kolom nama.

Querynya sebagai berikut:

“SELECT TRIM(trailing ‘+’ from nama_mah) FROM mahasiswa;”

“SELECT TRIM(leading ‘a’ from nama_mah) FROM mahasiswa;”.

      3.      Evaluasi Perbandingan

Pada bagian TRIM, di PostgreSQL boleh ada spasi setelah query TRIM, namun pada MySQL tidak boleh ada spasi. Kemudian pada check juga ada perbedaan, dalam MySQL untuk menggunakan fungsi CHECK harus menggunakan lain cara. Lalu pada TRIM di bagian BOTH juga terjadi perbedaan, kalau POstgreSQL boleh depan belakang, tapi kalai Mysql harus depannya atau belakangnya.

 

       4.      Kesimpulan, kritik, saran, manfaat

Kebanyakan querynya sama, hanya dalam beberapa bagian saja terdapat perbedaan, namun semuanya mudah untuk dilakukan dan dipelajari. Semoga lebih dilengkapi saja modulnya agar tidak terjadi kebingungan, semoga dapat bermanfaat bagi pembaca yang ignin memerdalam pengetahuannya tentang sub query da indeks.

      5.      Daftar Pustaka

modul praktikum desain basis data 2013 Teknik Informatika UIN malang

codeoke.blogspot.com/2012/12/sub-query-mysql.html 
http://risnotes.com/2011/12/subqueries/ 

http://beginner-sql-tutorial.com/id/sql-index.htm

Ebook : Dwi Apri Styorini S.Kom, Pemrograman Basis Data Lanjutan MySQL