Fungsi Agregasi dan View

AGREGASI SQL DAN VIEW

Dasar Teori

FUNCTION

Function atau fungsi dalam perintah SQL digunakan untuk menghasilkan sebuah nilai atau output. Ada berbagai macam fungsi yang ada pada SQL.

Fungsi Agregasi/Agregates
Fungsi yang dihasilkan dari sebuah group data. 

1.        SUM : Untuk menjumlah suatu kolom tertentu yang telah definisikan dalam perintah select.

       Sintak : SELECT SUM (nama_kolom) FROM nama_tabel
Ex: SELECT SUM (SKS) FROM Mata_Kuliah

2.        COUNT : Untuk menghitung jumlah baris yang telah diinputkan dalam sebuah tabel

       Sintak : SELECT COUNT (nama_kolom) FROM nama_tabel

       Ex:  SELECT COUNT (*) FROM mahasiswa

       SELECT COUNT (SKS) FROM Mata_Kuliah

3.        AVG : Untuk menghitung nilai rata – rata dari suatu kolom tertentu yang telah definisikan dalam perintah select

       Sintak : SELECT AVG (nama_kolom) FROM nama_tabel

       Ex: SELECT AVG (SKS) FROM Mata_Kuliah

4.        MIN : Untuk mengetahui nilai terkecil dari sebuah kolom tertentu dalam perintah select

       Sintak : SELECT MIN (nama_kolom) FROM nama_tabel

       Ex:  SELECT MIN (SKS) FROM Mata_Kuliah

5.        MAX : Untuk mengetahui nilai terbesar dari sebuah kolom tertentu dalam perintah select

       Sintak : SELECT MAX (nama_kolom) FROM nama_tabel

       Ex: SELECT MAX (SKS) FROM Mata_Kuliah

Group By

-          Modifier Group By

Klausa group by yang diikuti oleh modefier with rollup menyebabkan tambahan baris pada hasil keluaran.

Ex:

Buat tabel bernama penjualan yang mempunyai kolom-kolom berupa tahun, negara, produk, dan profit berdasarkan data hasil penjualan.

Tabel bisa ditampilkan menurut tahun dengan perintah group by sederhana seperti berikut ini:

Select year, sum (profit) from penjualan group by tahun;

Anda dapat menggunakan rollup yang menganalisis data pada kolom tersebut dengan hanya menggunakan sebuah query. Dengan penambahan perintah rollup, baris tabel akan bertambah dengan jumlah total dari profit penjualan.

-          Group by menggunakan field tersembunyi

Mysql menambah penggunaan group by sehingga anda dapat menggunakan kolom atau perhitungan dari daftar select yang tidak tampak pada klausa group by. Hal ini berlaku untuk nilai yang mungkin untuk group ini. Anda dapat menggunakan perintah ini untuk meningkatkan performance dengan menghindari sorting dan grouping dari data-data yang tidak diperlukan.

Ex:

Anda tidak perlu mengelompokkan pelanggan.nama pada query dibawah ini:

            Mysql> SELECT order.custid, customer.name, MAX(payments)

è FROM order, customer

è WHERE order.custid=customer.custid

è GROUP BY order.custid;

Having

Memiliki klausa digunakan untuk menyaring data berdasarkan fungsi kelompok. Hal ini mirip dengan kondisi WHERE tapi digunakan dengan fungsi kelompok. Fungsi kelompok tidak dapat digunakan dalam MANA Klausul tetapi dapat digunakan dalam klausa HAVING.

Ex :

Jika Anda ingin memilih departemen yang jumlah gaji yang dibayarkan untuk karyawan lebih dari 25000, query sql akan seperti;

SELECT dept, SUM (salary)
FROM employee
GROUP BY dept
HAVING SUM (salary) > 25000

Outputnya akan seperti:

GAMBAR

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

Akhirnya, kondisi pada fungsi kelompok dalam klausa HAVING diterapkan pada baris dikelompokkan sebelum hasil akhir akan ditampilkan. 

Case
Perintah CASE sering digunakan untuk menampilkan nilai tertentu dari beberapa barisan data dengan syarat-syarat atau kondisi yang kita berikan.
Kalau kita belajar bahasa pemrograman tentu kita kenal dengan perintah IF Condition, fungsinya CASE hampir sama. Bedanya perintah CASE digunakan untuk beberapa kondisi sekaligus, dan didalam SQL perintah CASE tidak bisa berdiri sendiri melainkan harus disisipkan dalam perintah SELECT. Jadi berbeda dengan perintah IF yang lebih bebas, akan tetapi perintah IF hanya berguna apabila dimasukan dalam STORE PROCEDURE, FUNCTION dan TRIGGER. Perbedaannya dalam SQL sangat jelas sekali, karena perintah CASE disisipkan dalam perintah SELECT jadi yang dihasilkan langsung berupa DATASET dan langsung bisa dilihat hasilnya.
Perintah CASE ini mempunyai dua format :
1. Simple Case function :
Maksudnya adalah perintah CASE sederhana.
Bentuk Sintaknya
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.
Sintaknya sebagai berikut.
CASE
WHEN Boolean_expression THEN result_expression
[ ...n ]
[
ELSE else_result_expression
]
END

 

View

SQL View adalah tabel virtual (bukan tabel sebenarnya) yang dibuat dari beberapa tabel atau view lain. SQL View tidak memiliki data sendiri, tetapi data-datanya berasal dari tabel-tabel atau view lain. SQL View dibuat atau dibangun dengan SELECT statement. Di dalam query SELECT tersebut kita bias menambahkan ORDER BY statement untuk mengurutkan data atau tidak sama sekali. SQL View dapat digunakan untuk memudahkan atau menyederhanakan data yang ditampilkan,  mencegah menampilkan data yang penting dengan menyembunyikan nama kolomnya.
- Kegunaan VIEW :

• Menyembunyikan Kolom atau Baris
• Menampilkan hasil dari penghitungan
• Menyembunyikan Sintak SQL yang rumit
• Fungsi Built-in Layer
• Menyediakan level isolasi antara data tabel dan View data pengguna
• Memberikan proses permission yang berbeda untuk view yang berbeda dari tabel yang sama
• Memberikan trigger berbeda pada view yang berbeda dari tabel yang sama

- Membuat VIEW

Ex :
CREATE VIEW CustomerNameView AS
SELECT [Name] AS CustomerName
FROM CUSTOMER;

Menggunakan VIEW :

SELECT *
FROM CustomerNameView

CREATE VIEW [nama_view] AS
[select_statement]

VIEW untuk hide column/row
Contoh, misalkan pengguna aplikasi View Ridge ingin menyederhanakan daftar customer, sehingga yang tampil hanya nama dan nomor telepon saja.

CREATE VIEW BasicCustomerData AS
SELECT[Name], AreaCode, PhoneNumber
FROM CUSTOMER;

Contoh lain, jika pihak manajemen ingin menyembunyikan kolom AcquisitionPrice dan SalesPrice, maka dapat membuat view dengan tanpa mengikutsertakan kolom-kolom tersebut.
 

View juga dapat digunakan untuk menyembunyikan baris data tertentu. Yaitu dengan menambahkan klausa WHERE pada query-nya.

- VIEW untuk SQL yang complex
Dengan menggunakan views, programmer (developer) tidak perlu memasukan SQL statement yang kompleks ketika mereka membutuhkan suatu data.
Misalkan view berikut menghasilkan data ketertarikan customer terhadap artist yang ada.

CREATE VIEW CustomerInterests AS
SELECTC.Name as Customer, A.Name as Artist
FROM CUSTOMER C
JOIN CUSTOMER_ARTIST_INT CI
ON C.CustomerID = CI.CustomerID
JOIN ARTIST A
ON CI.ArtistID = A.ArtistID;

- Mengubah VIEW

Ex :

ALTER VIEW CustomerNameView AS

SELECT [Name] AS NamaPelanggan

FROM CUSTOMER;

ALTER VIEW [nama_view] AS

[select_statement]

- Menghapus VIEW

Ex :

DROP VIEW CustomerNameView;

DROP VIEW [nama_view]

Persiapan : Buatlah tabel yang berisi field-field seperti dibawah ini.

berikut prtsc dari perintah soal diatas:


Soal Praktikum (Penyelesaian menggunakan DBMS 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 diinputkan. Kemudian cari nim mahasiswa yang paling kecil, yang paling besar, dan rata-ratanya.

^-^pertama-tama yang perlu dilakukan adalah menginsertkan data pada tabel fakultas.

 

^-^setelah tabel fakultas kelar, dilanjut insert data pada tabel mahasiswa.

^-^lalu  menambahkan  kolom gender pada tabel mahasiswa dan meng_update datanya dengan menambahkan gender pada tiap-tiap nama mahasiswa.

^-^setelah semua data ter_update, kita bisa menampilkan jumlah baris yang telah diinputkan seperti sintak pada gambar dibawah ini:

 

^-^menampilkan nim mahasiswa terendah dengan sintak seperti dibawah ini:

 ^-^menampilkan nim mahasiswa tertinggi dengan sintak seperti dibawah ini:

^-^menampilkan rata-rata dari nim mahasiswa dengan sintak seperti dibawah ini:

 

2.      Tampilkan rata-rata nim mahasiswa yang data nimnya lebih besar dari 12.

 ^-^ menampilkan rata-rata dari nim mahasiswa yang lebih besar dari 12 dengan menggunakan agregasi AVG

3.      Tampilkan jumlah mahasiswa berdasarkan fakultas.

^-^ menampilkan jumlah mahasiswa berdasarkan tabel fakultas menggunakan fungsi  

GROUP BY

4.      Tampilkan seperti nomor 3 dengan persyaratan jumlah mahasiswa yang lebih dari sama dengan dua saja yang ditampilkan.

^-^ menampilkan jumlah mahasiswa berdasarkan tabel fakultas tetapi yang jumlahnya lebih dari sama dengan 2 dengan menggunakan fungsi HAVING.

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

^-^menampilkan data mahasiswa yang pada kolom gendernya menggunakan "Laki-Laki" untuk "l" dan "Perempuan" untuk "p". Fungsi yang digunakan adalah fungsi CASE.

 

opsi jawaban kedua untuk perintah soal diatas.

 

6.      Buatlah view untuk query penampilan data mahasiswa, fakultas. Ambil berdasarkan nim, nama mahasiswa, dan nama fakultas.

 ^-^membuat view table yang terdiri dari field nim mahasiswa, nama mahasiswa, dan nama fakultas.

Tugas Rumah

Buat laporan praktikum menggunakan DBMS mysql untuk mengerjakan tugas praktikum 1-5 dan letakkan di blog.

^-^ membuat table fakultas dan mahasiswa pada DBMS MySQL.

 ^-^ menginsertkan data kedalam kedua tabel tersebut bedasarkan perintah soal diatas.

^-^ menambahkan kolom gender pada tabel mahasiswa.

  

^-^ meng_update data mahasiswa sesuai jenis kelamin/ gendernya.

^-^  menghitung jumlah baris yang telah diinputkan kedalam tabel mahasiswa.

 

^-^ mencari nilai NIM mahasiswa terkecil dari data dalam tabel mahasiswa.

 ^-^ mencari nilai NIM mahasiswa terbesar dari data dalam tabel mahasiswa.

^-^ mencari nilai rata-rata NIM mahasiswa dari data dalam tabel mahasiswa.

 

 

^-^ mencari nilai rata-rata NIM mahasiswa dari data dalam tabel mahasiswa yang lebih dari 12.

^-^ menampilkan jumlah mahasiswa yang berada pada tiap-tiap nama fakultas yang ditinjau berdasarkan tabel fakultas.

^-^ menampilkan jumlah mahasiswa yang berada pada tiap-tiap nama fakultas yang ditinjau berdasarkan tabel fakultas yang jumlah@fakultasnya lebih dari sama dengan 2 mahasiswa.

  

^-^ menampilkan pada kolom gender mahasiswa "Laki-lai" untuk gender "l" dan "Perempuan" untuk mahasiswa yang bergender "p". ada beberapa opsi jawaban yang dapat saya tampilkan dari perintah ini, diantaranya:

opsi pertama : menampilkan semua data mahasiswa dengan mengunakan "select *" sehingga field gender yang masih berupa l dan p tetap ada di dalam tabel.

opsi kedua : menampilkan semua data didalam tabel mahasiswa dengan menyebutkan field-field yang ada di dalam tabel mahasiswa satu persatu tanpa menyebutkan field gender sehingga akan menghasilkan tampilan data mahasiswa yang pada kolom gendernya sudah berganti menjadi "Laki-laki" untuk "l" dan "Perempuan" untuk "p".

2.        opsi ketiga : menampilkan data mahasiswa yang terdiri  dari nim mahasiswa, nama mahasiswa, alamat mahasiswa, nama fakultas dan gender. di dalam sintaknya saya menggunakan relasi antara tabel mahasiswa dan fakultas.

 ^-^ membuat tabel view yang terdiri dari nim mahasiswa, nama mahasiswa dan nama fakultas.

Evaluasi perbedaan kelebihan dan kemudahan dari kedua DBMS.

Evaluasi Perbedaan MySQL dan PostgreSQL
Pada dasarnya tidak ada perbedaan yang yang besar pada query-query agregasi, group by, having, case,  maupun view. Perbedaannya hanya saja terletak pada pemberian spasi pada syntax di PostgreSQL dan MySQL. Pada MySQL pemberian spasi sangat berpengaruh, sedangkan pada PostgreSQL tidak memiliki pengaruh apapun.
Untuk lebih meyakinkan, perhatikan gambar dibawah ini. 
Fungsi Agregasi COUNT pada PostgreSQL

Fungsi Agregasi COUNT pada MySQL

dan untuk lebih rinci nya silahkan perhatikan tabel dibawah ini.

selain perbedaan-perbedaan diatas, terdapat satu lagi perbedaan..
yaitu pada tampilan data saat tampilan view tabel mahasiswa. berikut perbedaannya:
pada PostgreSQL : nim mahasiswa tidak secara otomatis terurut.

pada MySQL : nim mahasiswa tidak secara otomatis terurut

Kesimpulan

Dari kegiatan menyelesaikan perintah-perintah diatas dapat disimpulkan:

·          Agregasi adalah fungsi yang dihasilkan dari sebuah kelompok data. Agregasi meliputi min(terkecil), max(terbesar), avg(rata-rata), sum(jumlah perhitungan dari suatu kolom), dan Count(mencari jumlah baris yang telah diinputkan).

·          Group by adalah fungsi yang digunakan untuk pengelompokkan perintah select. Biasanya group by membantu/ bekerja sama dengan fungsi agregate untuk menampilkan suatu data.

·           Having dipakai terkait dengan group by khususnya dalam menentukan kondisi bagi group by.

·           Case dalam prosesnya dapat dengan bebas mengontrol data yang kembali dari query. Biasanya digunakan untuk penggantian isi data sesuai dengan kondisi yang ada. Seperti penggantian data “l” menjadi “Laki-laki” pada kolom gender pada tabel mahasiswa.

·           View, kembarannya temporary table. Hanya saja kalau view tetap tersimpan di dalam database. Sedangkan temporary, tabel yang telah dibuat akan otomatis hilang ketika DBMS nya ditutup.

·           Kesimpulan dari evaluasi kedua DBMS terletak pada kesensitifin pemberian spasi pada sintax yang ada pada fungsi agegate.

Saran bagi pembaca:

dimohon ketika pembaca menemukan sintax yang kurang tepat, agar menuliskan

komentar di bawah postingan ini disertai dengan bagian sintak yang salah dan juga

pembetulannya, ini supaya antara pemosting dan pembaca sama-sama dapat belajar dan

berbagi ilmu.. thanks.

Daftar Pustaka

Wahana, komputer. 2006. Pengolahan Database dengan MySQL. Yogyakarta : ANDI.

Raghu Ramakrishnan, "Database Manajament System" (E-Book) 

http://mysql.phi-integration.com/

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