Praktikum 5 - Agregasi SQL dan View

BAB I

MAKSUD DAN TUJUAN PRAKTIKUM

I.1 Maksud Praktikum

1. Membahas operator dasar dalam query sql.

2. Membahas operator dasar yang berhubungan dengan manipulasi data

I.2 Tujuan Praktikum

1. Memahami operator dasar dalam query sql.

2. Memahami operator dasar yang berhubungan dengan manipulasi data

BAB II

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

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:

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.

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

http://pribadiwidianto.blogspot.com/2011/12/pengelompokan-data-dan-agregasi-fungsi.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

Menggunakan PostgreSQL

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:

Nama fakultas Count(*)

Saintek                 2

Psikologi              1

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.

Menggunakan Xampp

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:

Nama fakultas Count(*)

Saintek                 2

Psikologi              1

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.

Evaluasi Perbandingan

            Pada praktikum kali ini, semua query pada PostgreSQL dan MYSQL adalah sama. Namun hanya ada satu catatan dari saya yakni pada saat MIN, MAX, AVG dan COUNT dengan tanda dalam kurung “()” tidak bisa dengan spasi, namun harus digabung dengan kalimat yang saya sebutkan sebelumnya.

Kesimpulan

            Setelah melakukan praktikum kali ini, ternyata fungsi Agregasi dalam SQL adalah proses untuk mendapatkan nilai-nilai dari kumpulan data yang telah dikelompokkan. Pengelompokan itu berdasarkan COUNT, MIN, MAX, SUM, dan AVG. serta fungsi group by, having dan case. Untuk view itu sendiri adalah merupakan perintah select yang tersimpan sehingga kapanpun kita butuhkan maka kita dapat langsung memanggilnya tanpa perlu mengetikkan select kembali.

Saran

            Perlu dalam meningkatkan belajar atau refensi yang lebih terutama latihan dalam DBMS PostgreSQL dan MYSQL agar kalian dapat menguasai kedua DBMS tersebut.

Kritik

            Dalam laporan ini masih banyak terdapat kesalahan dan kekurangan karena terbatasnya praktikan dalam penguasaan 2 DBMS ini.

Manfaat

            Dalam praktikum kali ini kita dapat mengetahui struktur query dalam fungsi agregasi dan operator-operatornya yang dapat digunakan dalam database. Dan juga fungsi dari View dalam suatu database. Tak lupa dapat kita lihat bersama dimana letak perbedaan dan persamaan dari kedua DBMS tersebut

Salam semangat J