Penggunaan Subquery (Query Nested) dan Indeks dalam PostgreSQL

Penggunaan Subquery (Query Nested) dan Indeks dalam PostgreSQL - Subquery dan indeks merupakan 2 fungsi penting dalam pengelolaan database. Kali ini akan dipraktekkan cara menggunakan fungsi subquery dan indeks. Selain itu juga akan dibahas mengenai fungsi cluster, kolom unik (unique column) dan trim dalam manajemen database.

Definisi Subquery dan Indeks

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 = atau in,not in atau <>, < any, >, >=,<=.

Adapun fungsi indeks 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.

Indeks sebaiknya jangan digunakan pada tabel yang sangat jarang atau tidak pernah diakses. Selain untuk perintah SELECT, fungsi indeks pada postgreSQL juga bermanfaat untuk melakukan UPDATE dan DELETE yang menggunakan kondisi pencarian. 

Sedangkan Unique index secara fungsi hampir sama dengan indeks tetapi lebih digunakan untuk mencegah duplikasi nilai ataupun data yang terdapat dalam tabel. Jadi dengan adanya unique index berarti user tidak dapat meng-insert nilai yang sama dalam sebuah tabel.

Praktek Penggunaan Subquery dan Indeks

Sebagai studi kasus, disini telah dibangun database mahasiswa untuk mengimplementasikan beberapa fungsi yang akan kita pelajari kali ini. Langkah pertama kita koneksikan terlebih dahulu database mahasiswa dengan query \c mahasiswa. 

Menampilkan Fakultas dan Jumlah Mahasiswa

Setelah database mahasiswa telah terkoneksi, barulah dapat kita praktekkan fungsi subquery dan indeks pada postgreSQL. Pertama akan dicoba menampilkan nama fakultas dan jumlah mahasiswa dengan ketentuan, fakultas yang dimunculkan hanya yang memiliki jumlah mahasiswa terkecil. Kita dapat menggunakan query berikut:

“select fakultas.fak_nama as fakultas, count(*) as jumlah_mahasiswa from mahasiswa, fakultas where fakultas.fak_id = mahasiswa.fak_id group by fakultas.fak_nama order by count(*) asc limit 1;”

Menampilkan Nama Mahasiswa, Fakultas, dan Alamat

Kemudian akan menampilkan nama mahasiswa, nama fakultas, dan alamat. Ketentuannya nama fakultas sama dengan edi, sedangkan alamatnya tidak sama dengan luki. Query yang digunakan adalah:

“select mahasiswa.mah_nama, fakultas.fak_nama, mahasiswa.mah_alamat from mahasiswa, fakultas where fakultas.fak_id = mahasiswa.fak_id and fakultas.fak_nama = (select fakultas.fak_nama from mahasiswa, fakultas where fakultas.fak_id = mahasiswa.fak_id and mah_nama = ‘edi’) and mahasiswa.mah_alamat<> (select mahasiswa.mah_alamat from mahasiswa where mahasiswa.mah_nama = ‘luki’;”

Berdasarkan gambar diatas dapat dilihat bahwa tidak ada hasil yang ditampikan, hal ini dikarenakan tidak terpenuhinya syarat atau ketentuan berdasarkan query yang telah digunakan. Agar lebih dipahami, terlebih dahulu akan kita tambahkan data sesuai dengan ketentuan sebelumnya.

Setelah data ditambahkan pada tabel mahasiswa dan memenuhi syarat query, mari kita coba kembali memasukkan query yang sama dengan sebelumnya.

Keterangan: Terlihat pada gambar diatas, disana terdapat perbedaan dengan hasil yang diperoleh sebelumnya. Jika sebelumnya tidak menampilkan hasil apapun, pada hasil diatas menghasilkan 3 buah data mahasiswa. hal ini dikarenakan adanya penambahan data yang disesuaikan dengan syarat query.

Membuat Index dan Unique Index

Selanjutnya kita praktekkan cara membuat index pada tabel mahasiswa, tepatnya pada kolom alamat. Kemudian membuat lagi index yang bersifat unik pada tabel fakultas, tepatnya pada kolom fak_nama. Untuk membuat keduanya berikut query yang digunakan. Untuk index mahasiswa pada kolom alamat:

“create index index_mahasiswa on mahasiswa (mah_alamat);”

Keterangan: Terlihat pada query untuk menampilkan struktur tabel mahasiswa, memunculkan keterangan “Indexes -> mahasiswa_pkey PRIMARY KEY, btree (mah_id) ; index mahasiswa btree (mah_alamat).” Hal tersebut menandakan bahwa proses pembuatan index pada kolom mah_alamat telah berhasil dibuat.

Sedangkan untuk membuat unique index pada tabel fakultas (kolom fak_nama) dapat menggunakan query berikut:

“create unique index unique_fakultas on fakultas (fak_nama);”

Keterangan: Terlihat pada query untuk menampilkan struktur tabel fakultas, memunculkan keterangan “Indexes -> fakultas_pkey PRIMARY KEY, btree (fak_id) ; unique_fakultas UNIQUE, btree (fak_nama)).” Hal tersebut menandakan bahwa proses pembuatan index pada kolom fak_nama telah berhasil dibuat.

Untuk membuktikan kedua hal tersebut, dapat kita lakukan dengan menambahkan data pada masing-masing tabel.

Kita lihat pada gambar diatas, ketika memasukkan data pada tabel mahasiswa berhasil, namun sebaliknya ketika memasukkan pada tabel fakultas terdapat pesan kesalahan. Ini dikarenakan adanya nilai masukan yang sama pada tabel fakultas, khususnya pada kolom (fak_nama). Sebab kolom fak_nama telah bersifat unique index. Jadi tidak boleh memiliki nilai yang sama terhadap beberapa data yang ada pada kolom tersebut.

Kolom Unik

Selanjutnya akan kita coba untuk membuat kolom nama yang bersifat unik pada tabel mahasiswa, serta akan diinputkan 2 data yang sama untuk menguji query. Untuk membuat kolom unik, berikut query yang dapat digunakan:

“create unique index on mahasiswa (mah_nama);”

Kemudian mari kita coba untuk memasukkan 2 buah data kedalam tabel mahasiswa.

Seperti pada kasus sebelumnya, pada penginputan data pertama berhasil dilakukan, namun pada penginputan kedua tidak berhasil dan memunculkan pesan kesalahan. Hal tersebut dikarenakan adanya nilai yang sama pada kolom mah_nama. Karena kolom tersebut telah di ubah menjadi kolom yang bersifat unik. Jadi didalamnya tidak boleh mempunyai dua atau lebih data yang mempunyai nilai yang sama.

Memindahkan Data

Memindahkan data disini akan dicontohkan dengan mengambil data yang terdapat pada tabel mahasiswa, fakultas, dan jurusan dengan mengambil kolom NIM, nama mahasiswa, nomor telp, nama fakultas, serta nama jurusan kedalam tabel baru yang akan diberi nama tabel identitas. Untuk menyelesaikan hal ini cukup dengan menggunakan query berikut:

“select mahasiswa.mah_id, mahasiswa.mah_nama, mahasiswa.mah_telp, fakultas.fak_nama into table identitas from mahasiswa, fakultas where fakultas.fak_id = mahasiswa.fak_id;”

Penggunaan Check

Selanjutnya akan dicontohkan cara penggunaan fungsi check pada postgreSQL. Lalu akan diinputkan beberapa data, baik yang sesuai dengan kriteria check maupun yang tidak. Query yang digunakan yaitu:

“CREATE TABLE pelanggan (nama varchar(35),kode_area CHAR(10) CHECK(length(trim(kode_area)) = 2),umur INTEGER CHECK (umur >= 0), gender CHAR(1) CHECK (gender IN ('L', 'P')),ttl DATE CHECK (ttl BETWEEN '1998-01-01' AND CURRENT_DATE),CHECK (upper(trim(nama)) != 'nita' OR upper(trim(nama)) != 'jeki'));”

Terlihat bahwa implementasi fungsi check tersisipkan dalam struktur tabel pelanggan berhasil, selanjutnya mari kita uji query diatas dengan menginputkan sejumlah data yang sesuai maupun yang tidak sesuai dengan syarat query.

Terlihat pada gambar, penginputan pertama dapat dilakukan dan data berhasil tersimpan pada tabel pelanggan. Sedangkan pada penginputan kedua tidak berhasil, hal tersebut disebabkan karena data yang dimasukkan tidak sesuai dengan syarat pada query yang telah dibuat untuk struktur tabel pelanggan. Begitupun yang terjadi pada penginputan ketiga juga tidak berhasil, karena masih adanya satu elemen data yang tidak sesuai dengan syarat query pada tabel pelanggan. 

Jadi dapat ditarik kesimpulan disini, agar penginputan dapat berjalan dengan sukses seluruh elemen data haruslah memenuhi persyaratan, dengan menyesuaikan terhadap query yang telah dibuat.

Input Data dan Karakter

Kita lanjutkan dengan praktek menginputkan data dan karakter, sebagai contoh akan diinputkan data kedalam tabel mahasiswa. Ketentuannya adalah sebelum karakter harus didahului dengan spasi serta diakhiri dengan tanda “+”

Seperti contoh data berikut : “ Andini++++”. Lebih jelasnya, mari kita lihat gambar berikut

Selanjutnya akan dipraktekkan cara menghilangkan spasi dari data yang telah kita inputkan sebelumnya ( Andini++++), caranya adalah dengan menggunakan query berikut:

“select trim (mah_nama) from mahasiswa;”

Keterangan: Terlihat ada perbedaan yang ditampilkan pada gambar diatas jika dibandingkan dengan tabel sebelumnya. Pada gambar tersebut karakter spasi di awal nama andini telah hilang. Hal ini menandakan bahwa penggunaan fungsi trim berhasil digunakan.

Menghilangkan Karakter 

Terakhir akan dicontohkan cara menghilangkan karakter, dalam hal ini adalah tanda plus (+) yang terdapat pada tabel mahasiswa, tepatnya pada kolom mah_nama. Serupa dengan kasus sebelumnya, query yang digunakan untuk kasus ini tidaklah jauh berbeda dengan sebelumnya, hanya terdapat sedikit penambahan fungsi both. Seperti yang terlihat pada query berikut:

“select trim (both ‘ a+’ from mah_nama) from mahasiswa;”

Keterangan: Terlihat pada gambar diatas, terdapat perbedaan jika dibandingkan dengan tabel awal. Pada gambar tersebut karakter ”+” di akhir nama andini telah berhasil dihilangkan.

Berbagi :