Laporan Praktikum V : Agregasi SQL dan VIEW

BAB I

Landasan Teori
Operator Agregasi
Fungsi agregasi atau disebut fungsi ringkasan digunakan untuk melakukan penghitungan menjadi sebuah nilai dari beberapa nilai input. Agregasi dapat digabungkan dengan sebuah parameter seperti WHERE untuk menghasilkan suatu hasil yang lebih kompleks lagi. Fungsi agregasi juga merupakan fungsi yang bekerja terhadap suatu group, dimana fungsi-fungsi tersebut terdiri dari:

Count
fungsinya untuk menghitung record/baris dari suatu tabel
ex: untuk menampilkan banyaknya record mahasiswa:

select count (*) from mahasiswa;

Sum
Fungsinya untuk menghitung jumlah dari sekumpulan kolom dari suatu tabel
ex: untuk menampilkan total sks untuk kuliah di semester 2:
select sum (sks) from kuliah where semester = 2;

Avg
Fungsinya untuk mendapatkan nilai rata-rata nilai kolom tertentu pada suatu tabel
ex: untuk menampilkan rata-rata sks untuk semua mata kuliah
select avg (sks) from kuliah;

Max
Fungsinya untuk mendapatkan nilai terbesar dari sekumpulan nilai kolom dari suatu tabel
ex: untuk menampilkan nilai terbesar yang diperoleh mahasiswa
select max (nilai) from nilai where kode_kul ='IF-110';

Min
Fungsinya untuk mendapatkan nilai terkecil dari sekumpulan nilai kolom dari suatu tabel
ex: untuk mendapatkan nilai terkecil dari sekumpulan nilai kolom dari suatu tabel mahasiswa
select min (tgl_lahir) from mahasiswa;

Berikut contoh agregasi 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
Group By merupakan fungsi yang digunakan untuk melakukan pengelompokan dari perintah SELECT. Akhirnya, kondisi pada fungsi kelompok dalam klausa HAVING diterapkan pada baris dikelompokkan sebelum hasil akhir akan ditampilkan. Group by seringkali diperlukan untuk menjalankan agregasi menjadi sebuah kelompok dari hasil Query. Berikut struktur SQL 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 :

Untuk menampilkan informasi jumlah total uang tiap tahunnya :
Select tahun_buk, sum(harga_buk) as total from buku group by tahun_buk;
Hasil :

Having
Having digunakan untuk menyaring data berdasarkan fungsi kelompok. Hal ini mirip dengan kondisi WHERE tapi digunakan dengan fungsi kelompok. Fungsi kelompok tidak dapat digunakan dalam query Where tetapi dapat digunakan dalam query HAVING. Having Merupakan pasangan dari GROUP BY, digunakan untuk membatasi kelompok yang ditampilkan.
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
Perintah CASE digunakan untuk menampilkan nilai tertentu dari beberapa barisan data dengan syarat-syarat atau kondisi yang kita berikan. 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
View 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 :

BAB II

Hasil Praktikum

Nim : 12
Nama : edi
Alamat : malang
Fakultas : SAINTEK

Nim : 13
Nama : sinta
Alamat : jogja
Fakultas : SAINTEK

Nim : 14
Nama : luki
Alamat : ponorogo
Fakultas : PSIKOLOGI

1. Dari tabel mahasiswa yang telah dibuat tambahkan 3 data lagi , tambahkan kolom gender kemudian update datanya dan tampilkan banyaknya data mahasiswa yang telah di inputkan. Kemudian cari nim atau id mahasiswa yang paling kecil, paling besar dan rata-ratanya.
2. Tampilkan rata-rata id atau nim mahasiswa yang data nimnya lebih dari 12. 
3. Tampilkan jumlah mahasiswa berdasarkan fakultas. Sehingga hasilnya seperti berikut : 
4. Tampilkan seperti nomor 3 dengan persyaratan jumlah mahasiswa yang lebih dari sama dengan 2 saja yang ditampilkan.
5. Tampilkan data mahasiswa dengan persyaratan, jika jenis kelaminnya “L” maka tertulis laki-laki dan bila “P” maka tertulis perempuan.
6. Buatlah view untuk query penampilan data mahasiswa, fakultas. Ambil berdasarkan nim, nama mahasiswa, nama fakultas.

Nomor 1 :

Pertama kita tambahkan terlebih dahulu tiga data diatas tapi karena kita telah memiliki data 'luki' maka cukup kita tambah dua data di atas, berikut hasil dari penambahan :

Kemudian kita cari nilai terbesar dari nim_mah menggunakan query berikut :
SELECT MAX(nim_mah) FROM mahasiswa;
Hasil :

Kemudian untuk mencari nilai terkecil dari nim_mah, kita dapat menggunakan query :
SELECT MIN(nim_mah) FROM mahasiswa;
Hasil :

Terakhir, untuk memunculkan rata-rata dari nim_mah, query yang kita gunakan adalah :
SELECT AVG(nim_mah) FROM mahasiswa;
Hasil :

Nomor 2 :
Untuk menhitung rata-rata nim_mah yang nilainya lebih dari sama dengan 12, kita gunakan query :
SELECT AVG(nim_mah) FROM mahasiswa WHERE nim_mah >= 12
Hasil :

Nomor 3 :
Jika kita ingin menampilkan seperti contoh, maka query yang digunakan adalah :
SELECT nama_fak,COUNT(*) FROM mahasiswa, fakultas WHERE mahasiswa.id_fak=fakultas.id_fak GROUP BY nama_fak;
Hasil :

Nomor 4 :
Sedangkan jika yang kita ingin kita munculkan memiliki nilai lebih dari sama dengan 2, maka query-nya seperti berikut :
SELECT nama_fak,COUNT(*) FROM mahasiswa, fakultas WHERE mahasiswa.id_fak=fakultas.id_fak GROUP BY nama_fak HAVING COUNT(*) >=2;
Hasil :

Nomor 5 :

Sebelumnya kita telah mengisi kolom gender dengan 'L' dan 'P', sedang kan untuk mengubah isi kolom menjadi 'L = LAKI-LAKI' dan 'P = PEREMPUAN', kita dapat memakai query ini :
SELECT nim_mah, nama_mah, alamat_mah, id_fak, no_telp, CASE WHEN gender = 'L' THEN 'LAKI-LAKI' ELSE 'PEREMPUAN' END AS GENDER FROM mahasiswa;
Hasil :

Nomor 6 :
Terakhir kita akan membuat VIEW dengan menampilkan data tabel mahasiswa dan fakultas berdasarkan nim_mah, nama_mah, dan nama_fak dengan query berikut :
CREATE VIEW data AS SELECT nim_mah, nama_mah, nama_fak FROM mahasiswa, fakultas WHERE mahasiswa.id_fak=mahasiswa.id_fak;
Hasil :

Kemudian tampilkan hasil dari tabel 'data'.
SELECT * FROM data;
Hasil :

BAB III

Perbandingan PostgreSQL dan MySQL

Ketika WHERE, GROUP BY dan HAVING klausa digunakan bersama-sama dalam sebuah pernyataan SELECT, klausa WHERE diproses terlebih dahulu, kemudian baris yang dikembalikan setelah klausa WHERE dijalankan dikelompokkan berdasarkan klausa GROUP BY.
Lalu bagaimana solusinya jika tidak bisa menggunakan WHERE ? MySQL (dan juga database yang lainnya) memiliki struktur kondisi khusus terkait fungsi agregat yaitu HAVING. Jika query diatas diperbaiki dan menggunakan HAVING, maka hasilnya sebagai berikut:
SELECT nim, nama, AVG(nilai) as rata_rata FROM nilai GROUP BY nim HAVING AVG(nilai)>80;

+----------------+------+-----------+
 |       nim         | nama | rata_rata|
+----------------+------+-----------+
 | 0911500101 | ADI  | 82.0000  |
 | 0911500102 | IDA  | 81.0000  |
 | 0911500103 | EDI   | 80.6000 |
 | 0911500105 | ANI  | 84.4000  |
+---------------+-------+-----------+

Jika menggunakan HAVING, maka pembatasan dilakukan setelah hasil dikelompokkan dalam GROUP BY.
Jika menggunakan WHERE, maka pembatasan dilakukan sebelum hasil dikelompokkan dalam GROUP BY.
Field(-field) yang disebut di HAVING harus ada di GROUP BY, atau berupa aggregate functions.

Kesimpulan
Terdapat beberapa perbedaan antara penggunaan Group By dan Where seperti yang sudah dibahas di atas, sehingga dalam penggunaanya kita perlu menentukan penempatan yang benar.
 
 Kritik
 Jangan tergesah-gesah dalam penulisan query karena kesalahan sekecil apapun maka query tidak akan bisa di run.

Saran
Sering-sering mengetik ulang jika terjadi kesalahan agar kita bisa lebih hafal dengan query yang panjang dan ribet.

Harapan
Semoga pembaca mendapat ilmu dan bisa memilih DBMS yang sesuai dengan karakter pembaca.

BAB IV

Daftar Pustaka

http://achmatim.net/2012/04/11/fungsi-group-by-dan-having-untuk-pengelompokkan-data-di-mysql/
http://dev.mysql.com/doc/refman/4.1/en/group-by-hidden-columns.html
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html
http://dikawebid.blogspot.com/2011/06/fungsi-agregat-numerik-string-tanggal.html