Laporan Praktikum Desain Basis Data “Agregasi SQL dan View”

A.    Materi/ Teori

Dalam pembahasan BAB kali ini, antara PostgreSQL dengan MySQL, keduanya mempunyai perintah query yang sama, hanya saja ada perbedaan dalam karakter spasinya.

AGREGATE OPERATOR

            Fungsi aggregate atau disebut fungsi ringkasan digunakan untuk melakukan penghitungan menjadi sebuah nilai dari beberapa nilai input. Aggregate dapat digabungkan dengan sebuah parameter seperti WHERE untuk menghasilkan suatu hasil yang lebih kompleks lagi. Adapun fungsi agregate yang disediakan oleh PostgreSQL dan MySQL:

Berikut contoh agregate query dari suatu tabel pegawai:

Untuk pencarian banyaknya pegawai kita bisa menggunakan query berikut :

  select count(*) from pegawai;

  hasil : 4

untuk pencarian nilai terbesar berdasarkan ID :

select max(Id_peg) from pegawai;

hasi : 4

untuk pencarian nilai terkecil :

select min(Id_peg) from pegawai;

hasil : 1

untuk pencarian rata-rata :

select avg(Id_peg) from pegawai;

hasil : 2.5000

GROUP BY

GroupBymerupakanfungsiyangdigunakanuntukmelakukanpengelompokan dari perintah SELECT.Group by seringkali diperlukan untukmenjalankan agregate   menjadi sebuah kelompok dari hasil Query. Berikut strukturSQL untuk penampilan   data :

select nama_kolom from nama_tabel group by nama_kolom;

Contoh:

Untuk menampilkan informasi nama pengarang :

Select pengarang_buk from buku group by pengarang_buk;

Hasil :

Pada hasil query terlihat pengarang muncul hanya sekali.

Untuk menampilkan informasi nama pengarang beserta jumlah buku yang dikarang :

Select pengarang_buk, count(*) from buku group by pengarang_buk;

Hasil:

Untuk menampilkan informasi buku tiap tahunnya :

Select tahun_buk, count(*) from buku group by tahun_buk;

Hasil:

Untu menampilkan informasi jumlah total uang tiap tahunnya :

Select tahun_buk, sum(harga_buk) as total from buku group by tahun_buk;

Hasil:

           

            HAVING

Pemakaian HAVING terkait dengan GROUP BY, kegunaanya adalah untuk menentukan kondisi bagi GROUP BY, dimana kelompok yang memenuhi kondisi saja yang akan di hasilkan. Berikut Struktur yang digunakan :

Kita akan menggunakan tabel “pesanan”:

Sekarang jika kita ingin mencari pelanggan yang memiliki total order kurang dari 2000. Maka, kita dapat menggunakan statement :

select pelanggan, sum (hargaorder) from pesanan group by pelanggan having sum (hargaorder) < 2000;

Maka hasilnya:

           

CASE

Meskipun SQL bukan merupakan sebuah prosedur bahasa perograman, namun dalam prosesnya dapat dengan bebas mengontrol data yang kembali dari query. Kata WHERE menggunakan perbandingan untuk mengontrol pemilihan data, sedangkan CASE perbandingan dalam bentuk output kolom. Jadi intinya penggunaan CASE akan membentuk output tersendiri berupa sebuah kolom baru dengan data dari operasi yang di dalamnya.Struktur didalam select seperti berikut :

CASE WHEN condition THEN result

[WHEN ...]

[ELSE result] END

Berikut contoh query penggunaan case, penentuan umur jika umurnya

dibawah 1986-01-01 dianggap dewasa dan lebih dari itu dianggap remaja :

select txt_namadepan, txt_namaakhir, dt_tgllahir, case when dt_tgllahir <

'1986-01-01' then 'dewasa' else 'balita' end as umur from pegawai ;

Hasil:

          

VIEW

Views dapat juga disebut tabel bayangan tetapi bukan temporary table, bukan juga merupakan sebuah tabel yang asli. Suatu view adalah suatu relasi virtual yang tidak perlu ada database tetapi dapat diproduksi atas permintaan oleh pemakai tertentu, pada ketika permintaan. Satu lagi kelebihan yang dimiliki oleh view yaitu dapat menyimpan perintah query, dan dapat mewakili sebuah subset dari tabel asli dan memilih kolom atau row tertentu dari tabel biasa.

create view nama_tabel_view as query;

Catatan     : Query diatas     merupakan query untuk      menampilkan      data

menggunakan query sql select. Berikut adalah tabel contoh kasus penggunaan VIEW :

Kita akan menggunakan tabel “pesanan”:

Kita akan membuat view dari tabel diatas dengan ketentuan harga dikumpulkan berdasarkan nama pelanggannya. Sebagai berikut :

create view total_pelanggan as select pelanggan, sum (hargaorder) from

pesanan group by pelanggan;

Untuk melihat hasil kita bisa melakukan query select sebagai berikut :

Select * from total_pelanggan;

Hasil:

        B.     Hasil Praktikum

a.       Hasil Praktikum PostgreSQL

1.      Tambahkan data mahasiswa sesuai yang diperintahkan, jika sudah, coba tampilkan isi tabelnya “SELECT * FROM mahasiswa;”. Tampilkan banyak data mahasiswa yang diinputkan “SELECT COUNT (*) FROM mahasiswa;”. Kemudian cari nim mahasiswa paling kecil “SELECT MIN (nim_mah) FROM mahasiswa;”, paling besar “SELECT MAX (nim_mah) FROM mahasiswa;”, rata-rata nim “SELECT AVG (nim_mah) FROM mahasiswa;”.

2.      Lalu, coba tampilkan rata-rata nim mahasiswa yang nimnya lebih dari 12 “SELECT AVG (nim_mah) FROM mahasiswa WHERE nim_mah > 12;”.

3.      Lalu tampilkan jumlah mahasiswa berdasarkan fakultas “SELECT nama_fak, COUNT (*) FROM mahasiswa m, fakultas f  WHERE m.id_fak=f.id_fak GROUP BY f.nama_fak;”.

4.      Tampilkan seperti di atas, namun hanya yang berjumlah lebih dari 2 mahasiswanya “SELECT nama_fak, COUNT (*) FROM mahasiswa m, fakultas f  WHERE m.id_fak=f.id_fak GROUP BY f.nama_fak HAVING COUNT (m.id_fak) >= 2;”.

5.      Tampilkan data mahasiswa, dengan ketentuan jika jika jenis kelaminnya “L” maka laki-laki, jika “P” maka perempuan “SELECT nim_mah, nama_mah, gender, CASE WHEN gender=’L’ THEN ‘LAKI-LAKI’ ELSE ‘PEREMPUAN’ END AS jenis_kelamin FROM mahasiswa;”.

6.      Sekarang buat tabel VIEW penampilkan tabel mahasiswa dan fakultas. Ambil berdasarkan nim_mah, nama_mah, dan nama_fak “CREATE VIEW data_mahasiswa AS SELECT nim_mah, nama_mah, nama_fak FROM mahasiswa, fakultas WHERE mahasiswa.id_fak=fakultas.id_fak;”. Lalu, tampilkan view yang terlah dibuat “SELECT * FROM data_mahasiswa ORDER BY nim_mah ASC;”.

b.      Hasil Praktikum MySQL

1.      Tambahkan data mahasiswa sesuai yang diperintahkan, jika sudah, coba tampilkan isi tabelnya “SELECT * FROM mahasiswa;”. Tampilkan banyak data mahasiswa yang diinputkan “SELECT COUNT(*) FROM mahasiswa;”. Kemudian cari nim mahasiswa paling kecil “SELECT MIN(nim_mah) FROM mahasiswa;”, paling besar “SELECT MAX(nim_mah) FROM mahasiswa;”, rata-rata nim “SELECT AVG(nim_mah) FROM mahasiswa;”.

2.      Lalu, coba tampilkan rata-rata nim mahasiswa yang nimnya lebih dari 12 “SELECT AVG(nim_mah) FROM mahasiswa WHERE nim_mah > 12;”.

3. Lalu tampilkan jumlah mahasiswa berdasarkan fakultas “SELECT nama_fak as Nama_fakultas, COUNT(*) FROM mahasiswa m, fakultas f  WHERE m.id_fak=f.id_fak GROUP BY f.nama_fak;”.

4.      Tampilkan seperti di atas, namun hanya yang berjumlah lebih dari 2 mahasiswanya “SELECT nama_fak, COUNT (*) FROM mahasiswa m, fakultas f  WHERE m.id_fak=f.id_fak GROUP BY f.nama_fak HAVING COUNT (m.id_fak) >= 2;”.

5.      Tampilkan data mahasiswa, dengan ketentuan jika jika jenis kelaminnya “L” maka laki-laki, jika “P” maka perempuan “SELECT nim_mah, nama_mah, gender, CASE WHEN gender=’L’ THEN ‘LAKI-LAKI’ ELSE ‘PEREMPUAN’ END AS jenis_kelamin FROM mahasiswa;”.

6.      Sekarang buat tabel VIEW penampilkan tabel mahasiswa dan fakultas. Ambil berdasarkan nim_mah, nama_mah, dan nama_fak “CREATE VIEW data_mahasiswa AS SELECT nim_mah, nama_mah, nama_fak FROM mahasiswa m, fakultas f WHERE m.id_fak=f.id_fak;”. Lalu, coba tampilkan tabelnya yang telah dibuat dengan urutan ascending sesuai nim “SELECT * FROM data_mahasiswa ORDER BY nim_mah ASC;”.

        C.     Evaluasi Perbandingan DBMS PostgreSQL dan MySQL (Tugas Rumah)

Perbedaan antara PostgreSQL dengan MySQL dalam pembahasan BAB ini, adalah karakter spasinya memengaruhi, yakni pada COUNT, MAX, MIN, AVG. misalnya pada PostgreSQL boleh Count (*), dalam MySQL harus Count(*) tidak boleh ada spasi.

         D.    Kesimpulan, Kritik, Saran, dan Manfaat

Praktikum bab pembahasan sekarang tidak ada perbedaan dalam querynya, hanya perbedaan karakter spasi di MySQL. Fungsi perhitungan ringkasan (agresiasi) sudah sesuai, namun dalam praktikum tidak ada relasi, cenderung menggunakan satu tabel tertentu saja.  Jika mengagresiasi dari banyak tabel pasti akan lebih kompleks. Juga contohnya kurang, seperti pada query having. Pada contoh hanya diberikan having sum, padahal di sana bisa juga diisi misalnya having count, contoh penggunaan average. Semoga pembahasan kali ini dapat bermanfaat bagi pembaca yang ingin mengetahui agregasi dan view dalam PostgreSQL dan MySQL.

         E.    Daftar Pustaka

Fatansyah. 1999. Buku Teks Ilmu Komputer Basis Data. Bandung: Informatika.

http://bintech.wordpress.com/2010/06/13/view-pada-sql/

http://muizalba.blogspot.com/2011/11/agregasi-sql-dan-view/

http://m24klik.wordpress.com/2013/01/10/sql-functiongroup-by-having/