Laporan Praktikum Desain Basis Data V : Agregasi & View

TEORI

a. MIN ( )

   Digunakan untuk mencari nilai terkecil dari sekumpulan record.
Contoh:
SELECT MIN(saldo) FROM rekening;

   Bisa dibatasi dengan WHERE clause sehingga hanya record (-record) tertentu yang ditelusuri:
SELECT MIN(saldo) FROM rekening WHERE kode_cabang = ‘BRUS’;

b. MAX ( )
   Digunakan untuk mencari nilai terbesar dari sekumpulan record.
Contoh:
SELECT MAX(saldo) FROM rekening;

   Juga bisa dibatasi dengan WHERE clause:
SELECT MAX(saldo) FROM rekening WHERE kode_cabang = ‘BRUS’;

c. COUNT ( )
   Digunakan untuk menghitung banyaknya record.
Contoh:
a. SELECT COUNT(*) FROM nasabah;
b. SELECT COUNT(nama_nasabah) FROM nasabah;
c. SELECT COUNT(alamat_nasabah) FROM nasabah;

   Juga bisa dibatasi dengan WHERE clause.

Jika kita ingin menghitung banyaknya record yang unik (tidak ada pengulangan), gunakan DISTINCT:
SELECT COUNT(DISTINCT alamat_nasabah) FROM nasabah;

select count(sks) as jmlsks from mtkul where sks=4;

d. SUM ( )
   Digunakan untuk menjumlahkan nilai-nilai dari sekumpulan record.
Contoh:
SELECT SUM(saldo) FROM rekening;

   Bisa dibatasi dengan WHERE clause:
SELECT SUM(saldo) FROM rekening WHERE kode_cabang = ‘BRUS’;

e) AVG ( )
   Digunakan untuk menghitung rata-rata nilai dari sekumpulan record.
Contoh:
SELECT AVG(saldo) FROM rekening;

   Bisa dibatasi dengan WHERE clause:
SELECT AVG(saldo) FROM rekening WHERE kode_cabang = ‘BRUS’;

   Beberapa aggregate functions bisa digabungkan dalam satu perintah SQL:
– SELECT MIN(saldo), MAX(saldo), AVG(saldo) FROM rekening;

   Bisa menambahkan ekspresi aritmetika:
– SELECT SUM(saldo + 1000) FROM rekening;
– SELECT SUM(saldo) + 1000 FROM rekening;
– SELECT MAX(saldo) - MIN(saldo) FROM rekening;

   Bisa menggunakan column alias (AS) untuk membuat tampilan lebih profesional.

GROUP BY
   Digunakan untuk mengelompokkan sekumpulan record berdasarkan kolom(-kolom) tertentu.

Contoh:
• SELECT jenis_transaksi FROM transaksi GROUP BY jenis_transaksi;
• SELECT jenis_transaksi, tanggal FROM transaksi GROUP BY jenis_transaksi, tanggal;

   Hasil yang sama bisa didapatkan dengan menggunakan DISTINCT:
• SELECT DISTINCT jenis_transaksi, tanggal FROM transaksi;

   Yang harus diperhatikan ketika menggunakan GROUP BY

• Jika menggunakan GROUP BY, semua field yang ingin ditampilkan dalam SELECT harus tercantum di GROUP BY.

• Contoh yang salah:
– SELECT jenis_transaksi, tanggal FROM transaksi GROUP BY jenis_transaksi;
– SELECT jenis_transaksi, tanggal FROM transaksi GROUP BY tanggal;

• Contoh yang benar:
– SELECT jenis_transaksi, tanggal FROM transaksi GROUP BY jenis_transaksi, tanggal;

HAVING
   Merupakan pasangan dari GROUP BY, digunakan untuk membatasi kelompok yang ditampilkan:
• SELECT jenis_transaksi, tanggal FROM transaksi GROUP BY jenis_transaksi, tanggal HAVING jenis_transaksi = ‘kredit’;

   Hasil yang sama bisa didapatkan dengan:
• SELECT jenis_transaksi, tanggal FROM transaksi WHERE jenis_transaksi = ‘kredit’ GROUP BY jenis_transaksi, tanggal;

   Yang harus diperhatikan :
• 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.

   Contoh implementasi
• Contoh yang salah:
– SELECT jenis_transaksi, tanggal FROM transaksi GROUP BY jenis_transaksi, tanggal HAVING jumlah = 50000;
• Contoh yang benar:
– SELECT jenis_transaksi, tanggal FROM transaksi WHERE jumlah = 50000 GROUP BY jenis_transaksi, tanggal;

Penggabungan GROUP BY dengan Aggregate
   GROUP BY sangat cocok untuk aggregate functions. Dengan menggunakan GROUP BY, kita bisa mengelompokkan record-record dan menghitung min, max, count, sum, dan avg untuk masing-masing kelompok.
Contoh:
• SELECT kode_cabang, MIN(saldo), MAX(saldo), COUNT(*), SUM(saldo), AVG(saldo) FROM rekening GROUP BY kode_cabang;

• Bisa digabungkan dengan tabel join dan ORDER BY:
o SELECT nama_cabang, SUM(saldo) FROM rekening NATURAL JOIN cabang_bank GROUP BY nama_cabang ORDER BY nama_cabang;

• Hasil di atas menampilkan total saldo untuk masing-masing cabang_bank.

• Perintah SQL di bawah menampilkan banyaknya nasabah yang dilayani oleh masing-masing cabang bank:
o SELECT nama_cabang, COUNT(DISTINCT id_nasabah) FROM cabang_bank NATURAL JOIN rekening NATURAL JOIN nasabah_has_rekening GROUP BY nama_cabang ORDER BY nama_cabang;

• Contoh dengan HAVING:
o SELECT kode_cabang, SUM(saldo), COUNT(*) FROM rekening GROUP BY kode_cabang HAVING SUM(saldo) >= 5000000 ORDER BY kode_cabang;

• Karena SUM(saldo) hanya bisa dihitung setelah hasil dikelompokkan dengan GROUP BY, maka kita harus menggunakan HAVING untuk membatasi hasil berdasarkan SUM(saldo) >= 5000000. Kita tidak bisa menggunakan WHERE.

Source >> http://wandono.blog.unair.ac.id/query-pada-database-tingkat-lanjut.html

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

Query yang digunakan untuk simple CASE adalah:

SELECT CASE ("column_name")
  WHEN "value1" THEN "result1"
  WHEN "value2" THEN "result2"
  ...
  [ELSE "resultN"]
  END
FROM "table_name";

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.

Contoh:
 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; 

Source >>   Mustafa, Aziz.Modul Desain Basis Data.pdf

Hasil Praktikum & Tugas

Server [localhost]:
Database [postgres]:
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (9.3.5)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=# \z
                             Access privileges
 Schema |    Name    | Type  | Access privileges | Column access privileges
--------+------------+-------+-------------------+--------------------------
 public | fakultas   | table |                   |
 public | fakultas1  | table |                   |
 public | identitas  | table |                   |
 public | mahasiswa  | table |                   |
 public | mahasiswa1 | table |                   |
 public | pekerjanim | table |                   |
(6 rows)


postgres=# \l
                                                    List of databases
      Name       |  Owner   | Encoding |          Collate           |
Ctype            |   Access privileges
-----------------+----------+----------+----------------------------+-----------
-----------------+-----------------------
 Coba            | postgres | UTF8     | English_United States.1252 | English_Un
ited States.1252 |
 alfan10650046   | postgres | UTF8     | English_United States.1252 | English_Un
ited States.1252 |
 badz10650116    | postgres | UTF8     | English_United States.1252 | English_Un
ited States.1252 |
 fairuz13650100  | postgres | UTF8     | English_United States.1252 | English_Un
ited States.1252 |
 innamul13650019 | postgres | UTF8     | English_United States.1252 | English_Un
ited States.1252 |
 ismail13650047  | postgres | UTF8     | English_United States.1252 | English_Un
ited States.1252 |
 mahdi13650082   | postgres | UTF8     | English_United States.1252 | English_Un
ited States.1252 |
 postgres        | postgres | UTF8     | English_United States.1252 | English_Un
ited States.1252 |
 rofiqo13650060  | postgres | UTF8     | English_United States.1252 | English_Un
ited States.1252 |
 sahrul13650106  | postgres | UTF8     | English_United States.1252 | English_Un
ited States.1252 |
 template0       | postgres | UTF8     | English_United States.1252 | English_Un
ited States.1252 | =c/postgres          +
                 |          |          |                            |
                 | postgres=CTc/postgres
 template1       | postgres | UTF8     | English_United States.1252 | English_Un
ited States.1252 | =c/postgres          +
                 |          |          |                            |
                 | postgres=CTc/postgres
(12 rows)


postgres=# \c innamul13650019;
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
You are now connected to database "innamul13650019" as user "postgres".
innamul13650019=# \z
                                  Access privileges
 Schema |     Name     | Type  |     Access privileges     | Column access privi
leges
--------+--------------+-------+---------------------------+--------------------
------
 public | fakultas     | table |                           |
 public | identitasnim | table | postgres=arwdDxt/postgres+|
        |              |       | innamul=arwdDxt/postgres  |
 public | ini_dia      | view  |                           |
 public | mahasiswa    | table |                           |
 public | organ_dalam  | table |                           |
 public | organ_luar   | table |                           |
(6 rows)


innamul13650019=# select * from mahasiswa;
 nim_mah | nama_mah | alamat_mah | id_fak | gender
---------+----------+------------+--------+--------
       1 | innamul  | aceh       |      1 | L
       2 | suci     | riau       |      2 | P
       3 | jarjit   | melayu     |      1 | L
       4 | upin     | serawak    |      2 | L
       5 | ipin     | serawak    |      2 | L
       6 | mei-mei  | hongkong   |      1 | P
       7 | ihsan    | brunei     |      1 | L
(7 rows)


innamul13650019=# select min (nim_mah) from mahasiswa;
 min
-----
   1
(1 row)


innamul13650019=# select max (nim_mah) from mahasiswa;
 max
-----
   7
(1 row)


innamul13650019=# select avg (nim_mah) from mahasiswa;
        avg
--------------------
 4.0000000000000000
(1 row)


innamul13650019=# select avg (nim_mah) from mahasiswa where nim_mah > 4;
        avg
--------------------
 6.0000000000000000
(1 row)


innamul13650019=# select nama_fak, count (*) from mahasiswa, fakultas where maha
siswa.id_fak = fakultas.id_fak and gender = 'L' group by nama_fak;
 nama_fak  | count
-----------+-------
 PSIKOLOGI |     2
 SAINTEK   |     3
(2 rows)


innamul13650019=# select nama_fak, count (*) from mahasiswa, fakultas where maha
siswa.id_fak = fakultas.id_fak and gender = 'P' group by nama_fak;
 nama_fak  | count
-----------+-------
 PSIKOLOGI |     1
 SAINTEK   |     1
(2 rows)


innamul13650019=# select nama_fak, count (*) from mahasiswa, fakultas where maha
siswa.id_fak = fakultas.id_fak and gender = 'L' group by nama_fak having count (
*) =3;
 nama_fak | count
----------+-------
 SAINTEK  |     3
(1 row)


innamul13650019=# select nama_fak, count (*) from mahasiswa, fakultas where maha
siswa.id_fak = fakultas.id_fak and gender = 'L' group by nama_fak having count (
*) <= 3;
 nama_fak  | count
-----------+-------
 PSIKOLOGI |     2
 SAINTEK   |     3
(2 rows)


innamul13650019=# select nama_fak, count (*) from mahasiswa, fakultas where maha
siswa.id_fak = fakultas.id_fak and gender = 'P' group by nama_fak having count (
*) <= 3;
 nama_fak  | count
-----------+-------
 PSIKOLOGI |     1
 SAINTEK   |     1
(2 rows)


innamul13650019=# select *, case when gender = 'L' then 'Lelaki' else 'Wanita' e
nd as Jenis_Kelamin from mahasiswa;
 nim_mah | nama_mah | alamat_mah | id_fak | gender | jenis_kelamin
---------+----------+------------+--------+--------+---------------
       1 | innamul  | aceh       |      1 | L      | Lelaki
       2 | suci     | riau       |      2 | P      | Wanita
       3 | jarjit   | melayu     |      1 | L      | Lelaki
       4 | upin     | serawak    |      2 | L      | Lelaki
       5 | ipin     | serawak    |      2 | L      | Lelaki
       6 | mei-mei  | hongkong   |      1 | P      | Wanita
       7 | ihsan    | brunei     |      1 | L      | Lelaki
(7 rows)


innamul13650019=# create view coba_lagi as select nim_mah,nama_mah, nama_fak, co
unt(*) from mahasiswa, fakultas where mahasiswa.id_fak=fakultas.id_fak group by
count(*);
ERROR:  aggregate functions are not allowed in GROUP BY
LINE 1: ...as where mahasiswa.id_fak=fakultas.id_fak group by count(*);
                                                              ^
innamul13650019=# create view coba_lagi as select nim_mah,nama_mah, nama_fak, co
unt(*) from mahasiswa, fakultas where mahasiswa.id_fak=fakultas.id_fak group by
nama_mah;
ERROR:  column "mahasiswa.nim_mah" must appear in the GROUP BY clause or be used
 in an aggregate function
LINE 1: create view coba_lagi as select nim_mah,nama_mah, nama_fak, ...
                                        ^
innamul13650019=# create view coba_lagi as select nim_mah,nama_mah, nama_fak fro
m mahasiswa, fakultas where mahasiswa.id_fak=fakultas.id_fak group by nama_mah;
ERROR:  column "mahasiswa.nim_mah" must appear in the GROUP BY clause or be used
 in an aggregate function
LINE 1: create view coba_lagi as select nim_mah,nama_mah, nama_fak f...
                                        ^
innamul13650019=# create view coba_lagi as select nama_mah,nim_mah, nama_fak, co
unt(*) from mahasiswa, fakultas where mahasiswa.id_fak=fakultas.id_fak group by
nama_mah, nim_mah, nama_fak;
CREATE VIEW
innamul13650019=# select * from coba_lagi;
 nama_mah | nim_mah | nama_fak  | count
----------+---------+-----------+-------
 jarjit   |       3 | SAINTEK   |     1
 ihsan    |       7 | SAINTEK   |     1
 upin     |       4 | PSIKOLOGI |     1
 innamul  |       1 | SAINTEK   |     1
 mei-mei  |       6 | SAINTEK   |     1
 ipin     |       5 | PSIKOLOGI |     1
 suci     |       2 | PSIKOLOGI |     1
(7 rows)


innamul13650019=#

Evaluasi Perbandingan DBMS
   Beda dari modul-modul yang sebelumnya, pada pertemuan kali ini ada yang sempat bikin frustasi dan yang sama sekali ngga disangka-sangka, pas ngerjain soal yang pake "COUNT", "MAX, "MIN", itu sama sekali ngga bisa, aku cari-cari di internet ngga ada solusi dan akhirnya dikasih tau sama temen kalo sebenarnya itu ngga boleh pake spasi waktu di query "COUNT[no space]()", dan sebelumnya aku pake spasi dan its not work mulai jam 3 pagi aku kerjain sampe sore baru bisa -_-. 

   Dan saran aku kalo kalian pake SQLYog, ataupun MySQL jangan sekali-kali pake spasi diantara query diatas dengan tanda kurung yang dipake setelahnya, jangan samakan dengan PostgreSQL yang ngga masalah dengan spasi. Agak ironis sih soalnya biasanya PostgreSQL yang ngga boleh ketinggalan tanda ";" dan MySQL ngga masalah, pada query kali ini malah sebaliknya.

Kesimpulan
   Setiap query yang bisa digunakan berfungsi untuk mempermudah programmer dalam pengkodingan maupun dalam mempersingkat waktu, seperti contoh pada penggunaan VIEW diatas misalnya, dengan adanya VIEW maka kita tidak perlu menulis query yang sama untuk beberapa kali sesi presentasi, cukup dipaketkan dan dijalankan dengan query yang lebih singkat. Terimakasih atas kunjungannya dan tunggu postingan selanjutnya setiap hari minggu perminggunya, tapi kayaknya bakal puasa satu kali (minggu depan) soalnya ntar ada ujian, jadi kemungkinan ngga bakal terbit dah -_-