Agregasi SQL Dan View Pada Desain Basis Data

   A.  LANDASAN TEORI

AGREGASI

Atau juga disebut fungsi ringkasan pada SQL digunakan untuk melakukan penghitungan menjadi sebuah nilai dari beberapa nilai input atau juga untuk mendapatkan data dari sekumpulan data yang sudah terkelompokkan. Aggregate dapat digabungkan dengan sebuah parameter seperti WHERE untuk menghasilkan suatu hasil yang lebih kompleks lagi.

1.      COUNT (select count(*) from nama_tabel;)

Fungsinya untuk menghitung record/baris dari suatu tabel. Ketika kita diahadapkan pada tabel pegawai untuk menampilkan record maka query nya :

Select count(*) from pegawai;

2.      SUM (select sum (nama_kolom) from nama_tabel;).

Fungsinya untuk menghitung jumlah dari sekumpulan kolom dari suatu tabel. Contoh kita ingin mendapatkan jumlah data yaitu jumlah pegawai maka query nya :

Select sum (id_pegawai) from pegawai;

3.      MIN (select min (nama_kolom) fom nama_tabel;).

Fungsinya untuk mendapatkan nilai terkecil dari sekumpulan nilai kolom dari suatu tabel. Apabila diterapkan pada tabel pegawai maka qury nya :

Select min (id_pegwai) from pegawai;

4.      MAX (select max (nama_kolom) from nama_tabel;).

Fungsinya untuk mendapatkan nilai terbesar dari sekumpulan nilai kolom dari suatu tabel. Apabila kita ingin mencari nilai terberbesar pada tabel pegawai maka query nya :

Select max (id_pegawai) from pegawai;

5.      AVG (select avg (nama_kolom) from nama_tabel;).

Fungsinya untuk mendapatkan nilai rata-rata nilai kolom tertentu pada suatu tabel.

Apabila kita ingin mendapatkan rata-rata dari tabel pegawai maka query nya :

Select avg (id_pegawai) from pegawai;

STANDART ISO

GROUB 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. Maka struktur query nya :

Select nama_kolom from nama_tabel group by nama_kolom;

           

            Contoh :

           

            Untuk menampilkan informasi pengarang :

            Select pengarang_buk from buku group by pengarang_buk;

            

            Untuk menampilkan informasi nama pengarang beserta jumlah buku yang

dikarang :

select pengarag_buk, count(*) from buku group by pengarang_buk;

Untuk menampilkan informasi buku tiap tahunnya :

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

Untuk menampilkan informasi jumlah total uang tiap tahunnya :

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

HAVING

Pemakaian HAVING terkait dengan GROUP BY, kegunaanya adalah untuk menentukan kondisi bagi GROUP BY, dimana kelompok yang memenuhi kondisisaja yang akan di hasilkan. Atau juga klausa digunakan untuk menyaring data berdasarkan fungsi kelompok. Hal ini mirip dengan kondisi WHERE tapi digunakan dengan fungsi kelompok.

           

            Apabila disediakan tabel pelanggang seperti dibawah ini :

           

Sekarang kita ingin mencari pelanggan yang memiliki total order kurang dari 2000, maka struktur query nya :

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.

Perintah case mempunyai dua bentuk :

1.      SIMPLE CASE FUNCTION

Maksudnya adalah case dengan struktur query yang sederhana.

CASE input_expression 
WHEN when_expression THEN result_expression 
[...n ]
[
ELSE else_result_expression 
]
END

            2.SEARCHED CASE FUNCTION

Maksudnya perintah CASE ini diikuti dengan syarat kondisi boolean, seperti >, <, >=, <= , dan lain-lain.

CASE
WHEN Boolean_expression THEN result_expression 
[...n ]
[
ELSE else_result_expression 
]
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 ;

            ---------gbr 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.

            Kegunaan view yang lai adalah :

·         Menyembunyikan kolom atau baris.

·         Menampilkan hasil perhitungan.

·         Menyembunyikan syntax yang rumit.

·         Fungsi built in layers.

·         Dll

            Query view :

            create view nama_tabel_view as query;

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) frompesanan group by pelanggan;

MENGUBAH VIEW

Seperti pada sebelumnya untuk mengubah kita menggunakan perintah alter berikut sample nya :

ALTER VIEW CustomerNameView AS

SELECT [Name] AS NamaPelanggan

FROM CUSTOMER;

ALTER VIEW [nama_view] AS

[select_statement]

           

MENHAPUS VIEW

Untuk menghapus view kita menggunakan query drop :

Drop view

  B.   HASIL PERCOBAAN

Pada soal nomer 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


Pada soal nomer 2
Tampilkan rata-rata id atau nim mahasiswa yang data nimnya lebih dari 12.

Pada soal nomer 3
Tampilkan jumlah mahasiswa berdasarkan fakultas.

Pada soal nomer 4
Tampilkan seperti nomor 3 dengan persyaratan jumlah mahasiswa yang lebih dari
sama dengan 2 saja yang ditampilkan.

Pada soal nomer 5
Tampilkan data mahasiswa dengan persyaratan, jika jenis kelaminnya “L” maka
tertulis laki-laki dan bila “P” maka tertulis perempuan.

Pada soal nomer 6
Buatlah view untuk query penampilan data mahasiswa, fakultas. Ambil berdasarkan
nim, nama mahasiswa, nama fakultas.

  C.  PERBANDINGAN

     Perbandingan pada kedua dbms tidalah banyak, yang saya tau sesuai dengan pengalaman saya mengerjakan prakikum adalah spasi pada agregasi min dan max pada mysql akan error apabila terdapat sepasi namun berbeda pada postgresql tidak akan berpengaruh. Pada mysql select min(nim_mah) from mahasiswa; apabila ditulis seperti ini select min (nim_mah) from mahasiswa; akan terjadi error namun tidak begitu pada postgresql. Lalu perbedaan antara select count(*) from nama_tabel; akan berbeda dengan select sum(nama_kolom) from nama_tabel; apabila count(*) digunakan lebih kepada jumlah baris namun sum lebih kepada jumlah data yang ada.

  D.  KESIMPULAN
       

     Kesimpulan yang bisa di ambil dari praktikum agregasi SQL dan view ini masih sama seperti bab-bab sebelumnya yakni perbedaan dari kedua dbms yakni postgresql dan MySQL tidak jauh berbeda, mungkin yang terdeteksi oleh saya hanyalah spasi setelah operasi agregasi min dan max karena dbms tersebut mendeteksi bahwa operator tesebut adalah sebuah field dari tabel yang dieksekusi maka akan error apabila di enter (run itilah saya). Sedangkan penggunaan spasi tidak berpengaruh pada postgres.

  E.   DAFTAR PUSTAKA
      http://dev.mysql.com/doc/refman/4.1/en/group-by-functions.html

       http://fairuzelsaid.wordpress.com/category/bahan-ajar-modul-ti/sistem-basis-data/

       http://erroz.wordpress.com/2009/07/25/penggunaan-case-when-dalam-query/

      http://beginner-sql-tutorial.com/id/sql-having-clause.html

      Modul praktikum dbd V.pdf