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.


Kelas Informatika - Penggunaan Subquery (Query Nested) dan Indeks dalam PostgreSQL



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

Kelas Informatika - Koneksi ke Database 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;”

Kelas Informatika - Menghitung Jumlah Mahasiswa

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’;”

Kelas Informatika - Menampilkan Data Mahasiswa, Fakultas, dan Alamat dengan Syarat

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.

Kelas Informatika - Insert Data Mahasiswa

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

Kelas Informatika - Menampilkan Data Mahasiswa, Fakultas, dan Alamat yang Memenuhi Syarat

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);”

Kelas Informatika - Membuat Index pada PostgreSQL

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);”

Kelas Informatika - Membuat Unique Index pada PostgreSQL

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.

Kelas Informatika - Membuktikan Unique Index dengan Melakukan Insert Data

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);”

Kelas Informatika - Membuat Unique Index dalam Kolom pada PostgreSQL

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

Kelas Informatika - Membuktikan Unique Index pada Kolom dengan Melakukan Insert Data

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;”

Kelas Informatika - Memindahkan Data ke dalam Tabel Baru pada PostgreSQL

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'));”

Kelas Informatika - Penggunaan Fungsi Check pada Tabel dalam PostgreSQL

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.

Kelas Informatika - Pembuktian Fungsi Check dengan Melakukan Insert Data

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

Kelas Informatika - Input Data dan Karakter pada PostgreSQL

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;”

Kelas Informatika - Menghapus Spasi dengan Fungsi Trim pada PostgreSQL

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;”

Kelas Informatika - Menghilangkan Karakter dengan Fungsi Trim pada PostgreSQL

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

Belum ada Komentar untuk "Penggunaan Subquery (Query Nested) dan Indeks dalam PostgreSQL"

Posting Komentar

Iklan Atas Artikel

Iklan Tengah Artikel 1

Iklan Tengah Artikel 2

Iklan Bawah Artikel