PRAKTIKUM DESAIN BASIS DATA V AGREGASI SQL DAN VIEW

AGREGASI SQL DAN VIEW

A.    Landasan Teori

Fungsi Agregat (aggregate function) adalah fungsi di dalam SQL yang digunakan untuk melakukan perhitungan pada query. Pada umumnya penggunaannya dikombinasikan dengan klausa GROUP BY untuk menghasilkan rangkuman nilai yang dikelompokkan berdasarkan kolom tertentu. Berikut ini fungsi-fungsi yang termasuk ke dalam fungsi agregat :

a.       MIN ( )

Fungsi MIN ( ) berfungsi mengembalikan nilai minimal atau terkecil dari suatu kolom pada tabel tertentu. Sebagai contoh jika kita ingin mengetahui jumlah paling sedikit dari suatu tabel maka kita perlu menuliskan perintah :

select min (nama_kolom) from <nama_tabel>;

Fungsi MIN ( ) diatas diterapkan pada kolom numerik, bagaimana jika fungsi tersebut diaplikasikan untuk kolomyang bertipe STRING. Jawabannya adalah Fungsi MIN ( ) akan mengembalikan nilai terkecil dari STRING (berdasarkan urutan abjad).

b.      MAX ( )

Fungsi MAX ( ) merupakan kebalikan dari fungsi MIN ( ), yang berguna untuk memperoleh nilai maksimal atau nilai terbesar dari suatu kolom yang terdapat dalam tabel tertentu. Sama seperti MIN ( ), funfsi ini juga dapat diaplikasikan untuk kolom dengan tipe numerik maupun STRING. Berikut contoh penggunaan fungsi MAX ( ) :

select max (nama_kolom) from <nama_tabel>;

c.       SUM ( )

Fungsi SUM ( ) berguna untuk menjumlahkan nilai dari suatu kolom dalam tabel tertentu. Sebagai contoh untuk menghitung jumlah total dari suatu kolom. Dapat dituliskan perintah :

select sum (nama_kolom) from <nama_tabel>;

d.      COUNT ( )

Berbeda dengan SUM ( ), fungsi COUNT ( ) berfungsi untuk memperoleh banyaknya baris yang dihasilkan oleh suatu query. Contoh query dengan mengambil semua kolom :

select count (*) from <nama_tabel>;

contoh dengan mengambil beberapa kolom :

select count <nama_kolom1>,<nama_kolom2> from <nama_tabel>;

dengan kondisi tertentu :

select count (*) from <nama_tabel> where <kondisi>;

e.       AVG ( )

Fungsi AVG ( ) berguna untuk menghitung nilai rata-rata dari suatu kolom yang dihasilkan oleh query tertentu. Sebagai contoh dalam menghitung nilai rata-rata dari jumlah dalam isi tabel. Maka perintah SQL :

select avg (nama_kolom) from <nama_tabel> where <kondisi>;

f.       GROUP BY

Group By merupakan fungsi untuk melakukan pengelompokkan dari perintah SELECT. Group By diperlukan untuk menjalankan agregat menjadi kelompok dari hasil query. Perintah yang digunakan :

select <nama_kolom> from <nama_tabel> group by <nama_kolom>;

Untuk menampilkan data dengan yang dicari dan dihitung jumlahnya :

select <nama_kolom>, count(*) from <nama_tabel> group by <nama_kolom>;

Untuk menampilkan jumlah dari suatu data :

select <nama_kolom1>, sum <nama_kolom2> as <nama_penggantikolom2> from <nama_tabel> group by <nama_kolom1>;

g.      HAVING

HAVING terkait dengan Group BY, fungsinya untuk menentukan kondisi dari Group By, dimana kelompok yang memenuhi kondisi saja yang akan dihasilkan.

Perintah / struktur querynya adalah

jumlah dari data :

select <nama_kolom1>,sum (nama_kolom2) from <nama_tabel> group by <nama_kolom1> having sum (nama_kolom2) <kondisi>;

h.      CASE

Proses dari CASE dapat dengan bebas mengontrol data yang kembali dari query. Kata WHERE menggunakan perbandingan untuk mengontrol pemilihan data, sedangkan CASE perbandingan dalam bentuk output kolom. Dapat disimpulkan CASE akan membentuk operasi tersendiri berupa sebuah kolom baru dengan data dari operasi di dalamnya.

Struktur querynya :

Versi MySQL

CASE <ekspresi> when <nilai1> then <daftar_statement1>;

[when <nilai2> then < daftar_statement2>;]

. . . .

[else <daftar statement>]

end CASE

Jika ekspresi yang diperiksa memiliki nilai yang sama dengan nilai1, maka yang akan dieksekusi adalah daftar statement1. Jika ekspresi sama dengan nilai2, maka yang akan dieksekusi adalah daftar statement2, dan begitu seterusnya. Tetapi jika semua nilai konstan yang didefinisikan tidak ada yang sama dengan ekspresi, maka yang akan dieksekusi adalah daftar statement yang terdapat pada bgian else.

Versi Query PostgreSQL

Case when condition then result

[when...]

[else result] end

i.        VIEW

View adalah objek dalam database yang berisi kumpulan kolom yang dihasilkan dari perintah SELECT. Dengan kata lain yang lebih sederhana, view adalah objek yang menyimpan hasil query, baik dari satu tabel atau lebih. Dalam dunia database, view juga sering disebut 'tabel virtual'. Karena view sebenarnya tidak memiliki data . Data yang ditampilkan oleh sebuah view diambil dari tabel-tabel aktual yang disertakan dalam perintah SELECT. View juga dapat disebut tabel bayangan tetapi bukan temporary table, bukan juga merupakan  sebuah tabel asli.

Membuat View dengan query :

create view <nama_tabel> as <query>;

View juga memiliki manfaat bagi kita diantaranya

·         Akses data menjadi lebih mudah

o   View dapat digunakan untuk menampilkan rangkuman dari suatu perhitungan tertentu.

o   View dapat menampilakan beberapa kolom atau beberapa baris saja dari suatu tabel, tergantung dari kondisi yang didefinisikan.

o   View dapat digunakan untuk menampilkan data yang berasal dari dua tabel atau lebih.

·         View daat digunakan untuk menampilkan data yang berbeda untuk masing-masing user, sehingga setiap user hanya dapat melihat data yang sesuai dengan hak aksesnya.

B.     Tujuan

1.      Mahasiswa memahami operator agregasi SQL sehingga mempermudah dalam hal perhitungan data.

2.      Mahasiswa memahami cara penggunaan view.

C.     Hasil Praktikum

1)      Versi PostgreSQL

Berikut ini tampilan awal psql. Tekan "Enter" jika servernya "localhost

Tekan"enter" jika muncul "database [postgres] :"

Tekan "enter" jika nomor portnya 5432.

Tekan 'Enter' dengan mengisi username-nya 'postgres' lalu tekan enter.

Isikan password sesui dengan password saat instalasi.

koneksikan database dengan user menggunakan query "\c [nama database]". kemudian akan muncul seperti berikut ini.

Tambahkan kolom gender

Lihat data dalam tabel

o   Kemudian hitung jumlah baris dengan perintah COUNT

o   Dari tabel mahasiswa. Cari NIM yang paling kecil nilainya menggunakan  fungsi agregasi perintah MIN

o Cari NIM yang paling besar nilainya menggunakan  fungsi agregasi  perintah MAX

o   Cari rata-rata dari NIM dengan  fungsi agregasi perintah AVG

o   Tampilkan rata-rata id atau NIM mahasiswa yang data nimnya lebih dari 12.

o   Tampilkan jumlah mahasiswa berdasarkan fakultas. GROUP BY Sehingga hasilnya akan seperti ini:

Nama Fakultas	Count (*)
Saintek	2
Psikologi	1

o   Tampilkan seperti nomor 3 dengan persyaratan jumlah mahasiswa yang lebih dari sama dengan 2 saja yang ditampilkan. HAVING

o   Tampilkan data mahasiswa dengan persyaratan , jika jenis kelamin "L" maka tertulis laki-laki dan bila "P" maka tertulis perempuan. Dengan perintah CASE

o   Buatlah View untuk query penampilan data mahasiswa, fakultas. Ambil berdasarkan nim, nama mahasiswa, nama fakultas

Lihat tabel View

Urutkan data pada tabel view

2)      Versi MySQL Shell

Berikut ini tampilan awal mysql. Isi dengan query mysql -u root.

koneksikan dengan database menggunakan query use [nama database];

Tambahkan kolom gender pada tabel mahasiswa

Lihat data dalam tabel

o   Kemudian hitung jumlah baris dengan fungsi agregasi perintah COUNT

o   Dari tabel mahasiswa. Cari NIM yang paling kecil nilainya menggunakan fungsi agregasi perintah MIN

o   Cari NIM yang paling besar nilainya menggunakan fungsi agregasi perintah MAX

o   Cari rata-rata dari NIM dengan fungsi agregasi perintah AVG

o   Tampilkan rata-rata id atau NIM mahasiswa yang data nimnya lebih dari 12.

o   Tampilkan jumlah mahasiswa berdasarkan fakultas. GROUP BY Sehingga hasilnya akan seperti ini:

Nama Fakultas	Count (*)
Saintek	2
Psikologi	1

o   Tampilkan seperti nomor 3 dengan persyaratan jumlah mahasiswa yang lebih dari sama dengan 2 saja yang ditampilkan. HAVING

o   Tampilkan data mahasiswa dengan persyaratan , jika jenis kelamin "L" maka tertulis laki-laki dan bila "P" maka tertulis perempuan. Dengan perintah CASE

o   Buatlah View untuk query penampilan data mahasiswa, fakultas. Ambil berdasarkan nim, nama mahasiswa, nama fakultas

Lihat tabel View

Urutkan data pada tabel view

D.    Tugas Rumah

Evaluasi perbedaan kelebihan dan kemudahan dari DBMS PostgreSQL dan MySQL

1.      Persamaan

Dalam praktikum kali query dari tugas 1-6 semuanya sama.

2.      Perbedaan

Tidak ada perbedaan query dalam praktikum kali ini namun pada fungsi agregasi pada MySQL ,parsing sangat mempengaruhi hasil query dan bisa saja tidak dapat dieksekusi. Hal ini dapat memudahkan programmer dalam menulis query antara DBMS PostgreSQL dan MySQL

3.      Kelebihan

Dalam praktikum kali ini dapat diambil manfaat dengan dapat menggunakan fungsi agregat dan View  yang dapat dimodifikasi sesuai keinginan kita. Dengan perintah SELECT yang dikombinasikan dapat memanipulasi data.

E.     Evalusi Perbandingan PostgreSQL dan MySQL Shell

Berdasarkan query-querynya :

Instruksi	PostgreSQL	MySQL
(Menghitung Baris) Count	select count (*) from <nama_tabel>;	select count (*) from <nama_tabel>;
(Nilai terkecil) Min	select min (nama_kolom) from <nama_tabel>;	select min (nama_kolom) from <nama_tabel>;
(Nilai terbesar) MAX	select max (nama_kolom) from <nama_tabel>;	select max (nama_kolom) from <nama_tabel>;
Rata-rata (AVG)	select avg (nama_kolom) from <nama_tabel>;	select avg (nama_kolom) from <nama_tabel>;
Rata dengan Kondisi	select avg (nama_kolom) from <nama_tabel> where <kondisi>;	select avg (nama_kolom) from <nama_tabel> where <kondisi>;
Group By	select <nama_kolom>, count(*) from <nama_tabel> group by <nama_kolom>;	select <nama_kolom>, count(*) from <nama_tabel> group by <nama_kolom>;
Having	select <nama_kolom>, count(*) from <nama_tabel> group by <nama_kolom> having count (nama_kolom) <kondisi>;	select <nama_kolom>, count(*) from <nama_tabel> group by <nama_kolom> having count (nama_kolom) <kondisi>;
Case	CASE <ekspresi> when <nilai1> then <daftar_statement1>; [when <nilai2> then < daftar_statement2>;] . . . . [else <daftar statement>] end CASE	CASE <ekspresi> when <nilai1> then <daftar_statement1>; [when <nilai2> then < daftar_statement2>;] . . . . [else <daftar statement>] end CASE
Membuat tabel View	create view <nama_tabel> as <query>;	create view <nama_tabel> as <query>;

F.      Kesimpulan

Dapat disimpulkan bahwa praktikum kali ini dapat dipelajari tentang Fungsi agregat dan View. Yang mana fungsi agregat sendiri adalah fungsi didalam SQL yang digunakan untuk melaukna perhitungan pada query. Umumnya fungsi agregat penggunaannya dkombinasikan dengan klausa GROUP BY. Berikut fungsi-fungsi yang termasuk kedalam fungsi agregat. Pertama MIN ( ) yang digunakan mencari nilai terkecil dari data. Kedua MAX ( ) digunakan mencari nilai terbesar dalam tabel. Ketiga AVG ( ) yang digunakan mencari rata-rata nilai. Keempat SUM ( ) digunakan menghitung penjumlahan data. Kelima COUNT ( )  digunakan menghitung jumlah baris. Selain itu ada juga GROUP BY dan HAVING. Dan yang terakhir VIEW adalah objek yang menyimpan hasil query, baik dari satu tabel atau lebih. Dalam dunia database, view juga sering disebut 'tabel virtual'. Karena view sebenarnya tidak memiliki data .

Kritik

 Dalam praktikum kali ini kurang penjelasan dengan tugas yang terdapat dalam modul, sehingga praktikan mengalami kesulitan.

Saran

Lebih ditekankan dalam penjelasan yang terperinci

G.    Dafar Pustaka

o   Raharjo,Budi. Belajar Otodidak Membuat Database menggunakan MySQL.Informatika : Bandung.2011.

o  Utami,Ema.Relation Database Management System Using Ms SQL Server.Nirar.Net Publisher: Yogyakarta.2006.

o        http://web.unair.ac.id/admin/file/f_33720_SDP_5_SQL2.pdf

o         http://zakki.dosen.narotama.ac.id/files/2012/01/sofwan-mysqldanphpmyadmin.pdf