5th Task - Agregasi SQL dan View

Teori

1. Aggregate 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 dapat dilihat pada tabel berikut :

Berikut contoh aggregate 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;

hasil : 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 

2. Group By

Group By merupakan fungsi yang digunakan untuk melakukan pengelompokan dari perintah SELECT. Group by seringkali diperlukan untuk menjalankan agregate 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 : 

3. 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 : 

4. 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 : 

5. 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 : 

Tugas Praktikum

using PostgreSQL

       6 | rim      | malang     |      2 | 073462847355  | P
       1 | wong     | pasuruan   |      1 | 08672536745   | L
       5 | rom      | kediri     |      1 | 034667646655  | L
       7 | rus      | maling     |      1 | 077463894865  | P
       3 | jatno    | madura     |      1 | 0346567678545 | L
       2 | mugiono  | surabaya   |      1 | 086235235     | L
(7 rows)


db_muzakki13650026=> insert into mahasiswa values (12,'edi','malang','1','089736
354637','L');
INSERT 0 1
db_muzakki13650026=> insert into mahasiswa values (13,'sinta','jogja','1','08564
88938465','P');
INSERT 0 1
db_muzakki13650026=> update mahasiswa set nim_mah='4' where nama_mah=luki;
ERROR:  column "luki" does not exist
LINE 1: update mahasiswa set nim_mah='4' where nama_mah=luki;
                                                        ^
db_muzakki13650026=> select* from mahasiswa;
 nim_mah | nama_mah | alamat_mah | id_fak |     no_hp     | gender
---------+----------+------------+--------+---------------+--------
       4 | luki     | ponorogo   |      2 | 82576568798   | P
       6 | rim      | malang     |      2 | 073462847355  | P
       1 | wong     | pasuruan   |      1 | 08672536745   | L
       5 | rom      | kediri     |      1 | 034667646655  | L
       7 | rus      | maling     |      1 | 077463894865  | P
       3 | jatno    | madura     |      1 | 0346567678545 | L
       2 | mugiono  | surabaya   |      1 | 086235235     | L
      12 | edi      | malang     |      1 | 089736354637  | L
      13 | sinta    | jogja      |      1 | 0856488938465 | P
(9 rows)


db_muzakki13650026=> update mahasiswa set nim_mah='14' where nama_mah=luki;
ERROR:  column "luki" does not exist
LINE 1: update mahasiswa set nim_mah='14' where nama_mah=luki;
                                                         ^
db_muzakki13650026=> update mahasiswa set nim_mah='14' where nim_mah=4;
UPDATE 1
db_muzakki13650026=> update mahasiswa set nim_mah='10' where nim_mah=1;
UPDATE 1
db_muzakki13650026=> select* from mahasiswa;
 nim_mah | nama_mah | alamat_mah | id_fak |     no_hp     | gender
---------+----------+------------+--------+---------------+--------
       6 | rim      | malang     |      2 | 073462847355  | P
       5 | rom      | kediri     |      1 | 034667646655  | L
       7 | rus      | maling     |      1 | 077463894865  | P
       3 | jatno    | madura     |      1 | 0346567678545 | L
       2 | mugiono  | surabaya   |      1 | 086235235     | L
      12 | edi      | malang     |      1 | 089736354637  | L
      13 | sinta    | jogja      |      1 | 0856488938465 | P
      14 | luki     | ponorogo   |      2 | 82576568798   | P
      10 | wong     | pasuruan   |      1 | 08672536745   | L
(9 rows)


db_muzakki13650026=> update mahasiswa set nim_mah='15' where nim_mah=5;
UPDATE 1
db_muzakki13650026=> update mahasiswa set nim_mah='16' where nim_mah=6;
UPDATE 1
db_muzakki13650026=> update mahasiswa set nim_mah='17' where nim_mah=7;
UPDATE 1
db_muzakki13650026=> select* from mahasiswa;
 nim_mah | nama_mah | alamat_mah | id_fak |     no_hp     | gender
---------+----------+------------+--------+---------------+--------
       3 | jatno    | madura     |      1 | 0346567678545 | L
       2 | mugiono  | surabaya   |      1 | 086235235     | L
      12 | edi      | malang     |      1 | 089736354637  | L
      13 | sinta    | jogja      |      1 | 0856488938465 | P
      14 | luki     | ponorogo   |      2 | 82576568798   | P
      10 | wong     | pasuruan   |      1 | 08672536745   | L
      15 | rom      | kediri     |      1 | 034667646655  | L
      16 | rim      | malang     |      2 | 073462847355  | P
      17 | rus      | maling     |      1 | 077463894865  | P
(9 rows)


db_muzakki13650026=> update mahasiswa set nim_mah='11' where nim_mah=2;
UPDATE 1
db_muzakki13650026=> update mahasiswa set nim_mah='18' where nim_mah=3;
UPDATE 1
db_muzakki13650026=> select * from mahasiswa;
 nim_mah | nama_mah | alamat_mah | id_fak |     no_hp     | gender
---------+----------+------------+--------+---------------+--------
      12 | edi      | malang     |      1 | 089736354637  | L
      13 | sinta    | jogja      |      1 | 0856488938465 | P
      14 | luki     | ponorogo   |      2 | 82576568798   | P
      10 | wong     | pasuruan   |      1 | 08672536745   | L
      15 | rom      | kediri     |      1 | 034667646655  | L
      16 | rim      | malang     |      2 | 073462847355  | P
      17 | rus      | maling     |      1 | 077463894865  | P
      11 | mugiono  | surabaya   |      1 | 086235235     | L
      18 | jatno    | madura     |      1 | 0346567678545 | L
(9 rows)


db_muzakki13650026=> select * from mahasiswa order by nim_mah;
 nim_mah | nama_mah | alamat_mah | id_fak |     no_hp     | gender
---------+----------+------------+--------+---------------+--------
      10 | wong     | pasuruan   |      1 | 08672536745   | L
      11 | mugiono  | surabaya   |      1 | 086235235     | L
      12 | edi      | malang     |      1 | 089736354637  | L
      13 | sinta    | jogja      |      1 | 0856488938465 | P
      14 | luki     | ponorogo   |      2 | 82576568798   | P
      15 | rom      | kediri     |      1 | 034667646655  | L
      16 | rim      | malang     |      2 | 073462847355  | P
      17 | rus      | maling     |      1 | 077463894865  | P
      18 | jatno    | madura     |      1 | 0346567678545 | L
(9 rows)


db_muzakki13650026=> select count(8) from mahasiswa;
 count
-------
     9
(1 row)


db_muzakki13650026=> select count(*) from mahasiswa;
 count
-------
     9
(1 row)


db_muzakki13650026=> select max(nim_mah) from mahasiswa;
 max
-----
  18
(1 row)


db_muzakki13650026=> select min(nim_mah) from mahasiswa;
 min
-----
  10
(1 row)


db_muzakki13650026=> select avg(nim_mah) from mahasiswa;
         avg
---------------------
 14.0000000000000000
(1 row)


db_muzakki13650026=> select avg(nim_mah) from mahasiswa where nim_mah > 12;
         avg
---------------------
 15.5000000000000000
(1 row)


db_muzakki13650026=> select nim_mah, count(*) from mahasiswa group by id_fak;
ERROR:  column "mahasiswa.nim_mah" must appear in the GROUP BY clause or be used
 in an aggregate function
LINE 1: select nim_mah, count(*) from mahasiswa group by id_fak;
               ^
db_muzakki13650026=> select nama_mah, count(*) from mahasiswa group by id_fak;
ERROR:  column "mahasiswa.nama_mah" must appear in the GROUP BY clause or be use
d in an aggregate function
LINE 1: select nama_mah, count(*) from mahasiswa group by id_fak;
               ^
db_muzakki13650026=> select id_fak, count(*) from mahasiswa group by id_fak;
 id_fak | count
--------+-------
      1 |     7
      2 |     2
(2 rows)


db_muzakki13650026=> select gender, count(*) from mahasiswa group by gender;
 gender | count
--------+-------
 L      |     5
 P      |     4
(2 rows)


db_muzakki13650026=> select nama_fak, count(*) from mahasiswa, fakultas where ma
hasiswa.id_fak=fakultas.id_fak and gender ='p' group by nama_fak;
 nama_fak | count
----------+-------
(0 rows)


db_muzakki13650026=> select nama_fak, count(*) from mahasiswa, where mahasiswa.i
d_fak=fakultas.id_fak and gender ='p' group by nama_fak;
ERROR:  syntax error at or near "where"
LINE 1: select nama_fak, count(*) from mahasiswa, where mahasiswa.id...
                                                  ^
db_muzakki13650026=> select * from mahasiswa;
 nim_mah | nama_mah | alamat_mah | id_fak |     no_hp     | gender
---------+----------+------------+--------+---------------+--------
      12 | edi      | malang     |      1 | 089736354637  | L
      13 | sinta    | jogja      |      1 | 0856488938465 | P
      14 | luki     | ponorogo   |      2 | 82576568798   | P
      10 | wong     | pasuruan   |      1 | 08672536745   | L
      15 | rom      | kediri     |      1 | 034667646655  | L
      16 | rim      | malang     |      2 | 073462847355  | P
      17 | rus      | maling     |      1 | 077463894865  | P
      11 | mugiono  | surabaya   |      1 | 086235235     | L
      18 | jatno    | madura     |      1 | 0346567678545 | L
(9 rows)


db_muzakki13650026=> select nama_fak, count(*) from mahasiswa, where mahasiswa.i
d_fak=fakultas.id_fak and gender ='P' group by nama_fak;
ERROR:  syntax error at or near "where"
LINE 1: select nama_fak, count(*) from mahasiswa, where mahasiswa.id...
                                                  ^
db_muzakki13650026=> select nama_fak, count(*) from mahasiswa, fakultas where ma
hasiswa.id_fak=fakultas.id_fak and gender ='P' group by nama_fak;
 nama_fak  | count
-----------+-------
 saintek   |     2
 psikologi |     2
(2 rows)


db_muzakki13650026=> select nama_fak, count(*) from mahasiswa, fakultas where ma
hasiswa.id_fak=fakultas.id_fak and gender ='L' group by nama_fak;
 nama_fak | count
----------+-------
 saintek  |     5
(1 row)


db_muzakki13650026=> select nama_fak, count(*) from mahasiswa, fakultas where ma
hasiswa.id_fak=fakultas.id_fak and gender ='L' group by nama_fak having count(*)
 >=2;
 nama_fak | count
----------+-------
 saintek  |     5
(1 row)


db_muzakki13650026=> select nama_fak, count(*) from mahasiswa, fakultas where ma
hasiswa.id_fak=fakultas.id_fak and gender ='P' group by nama_fak having count(*)
 >=2;
 nama_fak  | count
-----------+-------
 saintek   |     2
 psikologi |     2
(2 rows)


db_muzakki13650026=> select nama_mah, case when gender < 'L' then 'pria' else 'w
anita' end as gender from mahasiswa;
 nama_mah | gender
----------+--------
 edi      | wanita
 sinta    | wanita
 luki     | wanita
 wong     | wanita
 rom      | wanita
 rim      | wanita
 rus      | wanita
 mugiono  | wanita
 jatno    | wanita
(9 rows)


db_muzakki13650026=> select nama_mah, case when gender = 'L' then 'pria' else 'w
anita' end as gender from mahasiswa;
 nama_mah | gender
----------+--------
 edi      | pria
 sinta    | wanita
 luki     | wanita
 wong     | pria
 rom      | pria
 rim      | wanita
 rus      | wanita
 mugiono  | pria
 jatno    | pria
(9 rows)


db_muzakki13650026=> create view data_mahasiswa as select nama_mah, nim_mah, nam
a_fak,count(*) from mahasiswa,fakultas where mahasiswa.id_fak=fakultas.id_fak gr
oup by nama_mah, nim_mah, nama_fak;
CREATE VIEW
db_muzakki13650026=> select * from data_mahasiswa;
 nama_mah | nim_mah | nama_fak  | count
----------+---------+-----------+-------
 edi      |      12 | saintek   |     1
 luki     |      14 | psikologi |     1
 wong     |      10 | saintek   |     1
 rom      |      15 | saintek   |     1
 sinta    |      13 | saintek   |     1
 jatno    |      18 | saintek   |     1
 rus      |      17 | saintek   |     1
 rim      |      16 | psikologi |     1
 mugiono  |      11 | saintek   |     1
(9 rows)

using MySQL (SQLyog)

MySQL vs PostgreSQL

Sama seperti posting tugas yang sebelum2nya. Menurut saya keduanya sama saja. Jika querry.nya di PostgreSQL bisa jalan, sudah pasti di MySQL juga bisa jalan. dan jika kebetulan ada querry yang error, mungkin itu hanya kesalahan dalam menuliskan karakter saja.

Kesimpulan

Kali ini kita belajar tentang Agregasi dan View atau kumpulan querry yang diperlukan untuk menampilkan sebuah atau sebagian data dari database yang kita perlukan. dengan 5 sub bab yang ada (lebih jelasnya scroll ke atas). Untuk mempelajari bab ini saya rasa kita harus terlebih dahulu paham tentang bab bab sebelumnya, karena sengat berhbungan dan berkesinambungan.

Daftar Pustaka

• Musthfa, Aziz. 2014.  Modul Praktikum Desain Basis Data
• http://muzakifuzaki.blogspot.com