BAB V
AGREGASI SQL DAN
VIEW
A.
LANDASAN TEORI
1.
AGGREGATE OPERATOR
Fungsi Aggregate
adalah fungsi yang diterapkan terhadap sekumpulan data pada sebuah field dan
menghasilkan nilai tunggal. Fungsi aggregate atau disebut fungsi ringkasan
digunakan untuk melakukan penghitungan menjadi sebuah nilai dari beberapa nilai
input. Aggregate dapat digabungkan dengan sebuah parameter seperti WHERE
untuk menghasilkan suatu hasil yang lebih kompleks lagi. Beberapa fungsi aggregate
adalah :
|
Fungsi
|
Keterangan
|
|
AVG(Nama_Kolom)
|
Fungsi
ini digunakan untuk mencari nilai rata-rata pada sebuah field. Data yang ada
pada field tersebut harus numerik.
|
|
MAX(Nama_Kolom)
|
Fungsi ini
digunakan untuk mencari nilai terbesar diantara data yang ada pada suatu
field. Fungsi ini dapat mencari nilai terbesar dari field numerik, string,dan
datetime.
|
|
MIN(Nama_Kolom)
|
Fungsi
ini gunanya hamper sama dengan fungsi MAX, namun fungsi ini mencari nilai
terkecil pada field yang bersangkutan.
|
|
SUM(Nama_Kolom)
|
Fungsi ini
digunakan untuk melakukan penjumlahan terhadap sebuah field yang tipe datanya
numerik.
|
|
COUNT(*)
|
Fungsi
ini menghitung semua nilai tertentu pada sebuah field. Fungsi ini akan
mengembalikan nilai berupa jumlah baris yang memenuhi kriteria perhitungan
tanpa terpengaruh nilai dari field tersebut ada yang NULL atau tidak.
|
|
COUNT_BIG(*)
|
Fungsi ini serupa
dengan fungsi di atas, tetapi hasilnya bertipe data bigint.
|
Berikut contoh aggregate query
dari suatu tabel pegawai :
|
Id_peg
|
Nama_peg
|
Alamat_peg
|
Telp_peg
|
Jabatan_peg
|
|
1
|
Hendro
|
Solo
|
08264783990
|
Teknisi
|
|
2
|
Tika
|
Semarang
|
08567283660
|
Sekretaris
|
|
3
|
Wijaya
|
Jogjakarta
|
09931888883
|
Kepala
|
|
4
|
Dodi
|
Banyuwangi
|
08999789883
|
Staf
|
Untuk pencarian banyaknya pegawai
kita bisa menggunakan query berikut :
select count(*) from
pegawai;
hasil : 4
untuk pencarian nilai terbesar
berdasarkan ID :
select max(Id_peg) from
pegawai;
hasi : 4
untuk pencarian nilai terkecil :
select min(Id_peg) from
pegawai;
hasil : 1
untuk pencarian rata-rata :
select avg(Id_peg) from
pegawai;
hasil : 2.5000
2.
GROUP 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. Berikut
struktur SQL untuk penampilan data :
select nama_kolom from
nama_tabel group by nama_kolom;
Contoh :
|
Kode_buk
|
Judul_Buk
|
Pengarang_Buk
|
Penerbit_Buk
|
Tahun_buk
|
Resensi_Buk
|
Harga_Buk
|
|
11
|
Harry
Potter
|
JK.Rowling
|
British
|
2002
|
Fiksi
|
50000
|
|
12
|
Sistem Basis Data
|
Abdul Kadir
|
Andi
|
2002
|
Teks
|
30000
|
|
13
|
Pemrograman
|
Abdul
Kadir
|
Andi
|
2004
|
Teks
|
60000
|
-
Untuk menampilkan informasi nama pengarang :
Select pengarang_buk from buku
group by pengarang_buk;
Hasil :
|
Pengarang
|
|
JK.Rowling
|
|
Abdul
Kadir
|
Pada hasil query terlihat
pengarang muncul hanya sekali.
-
Untuk menampilkan informasi
nama pengarang beserta
jumlah buku yang dikarang :
Select pengarang_buk, count(*)
from buku group by pengarang_buk;
Hasil :
|
Pengarang
|
Count(*)
|
|
JK.
Rowling
|
1
|
|
Abdul
Kadir
|
2
|
-
Untuk menampilkan informasi buku tiap tahunnya :
Select
tahun_buk, count(*) from buku group by tahun_buk;
Hasil :
|
Tahun
|
Count(*)
|
|
2004
|
1
|
|
2002
|
2
|
-
Untu menampilkan informasi jumlah total uang tiap tahunnya :
Select tahun_buk, sum(harga_buk) as total from buku
group by tahun_buk;
Hasil :
|
Tahun
|
Total
|
|
2004
|
60000
|
|
2002
|
80000
|
3.
HAVING
Pemakaian HAVING
terkait dengan GROUP
BY, kegunaanya adalah
untuk menentukan kondisi bagi
GROUP BY, dimana
kelompok yang memenuhi
kondisi saja yang akan di hasilkan.
Berikut Struktur yang digunakan :
Kita akan menggunakan tabel
“pesanan”
|
No
|
Tanggal Order
|
Harga Order
|
Pelanggan
|
|
1
|
2011/11/12
|
1000
|
Ratih
|
|
2
|
2011/10/23
|
1600
|
Anita
|
|
3
|
2011/09/02
|
700
|
Ratih
|
|
4
|
2011/09/03
|
300
|
Ratih
|
|
5
|
2011/08/30
|
2000
|
Teguh
|
|
6
|
2011/10/04
|
100
|
Anita
|
-
Sekarang jika kita ingin mencari pelanggan yang memiliki total order
kurang dari 2000.
Maka, kita dapat menggunakan
statement :
select pelanggan, sum
(hargaorder) from pesanan group by pelanggan having sum (hargaorder) <2000;
Maka, hasilnya :
|
Pelanggan
|
SUM (Harga Order)
|
|
Anita
|
1700
|
4.
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 :
CASE WHEN condition THEN
result
[WHEN ...]
[ELSE result] 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 ;
Hasil :
|
Txt_NamaDepan
|
Txt_NamaAkhir
|
Dt_TglLahir
|
Umur
|
|
Andhie
|
Lala
|
1960-08-08
|
Dewasa
|
|
Ade
|
Fajar
|
1986-11-01
|
Balita
|
|
Panuju
|
Sasongko
|
1970-09-12
|
Dewasa
|
|
Dudy
|
Rudianto
|
1973-12-11
|
Dewasa
|
|
Ana
|
Hidayati
|
1988-10-01
|
Balita
|
5.
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.
Berikut adalah tabel contoh kasus
penggunaan VIEW :
Kita akan menggunakan tabel
“pesanan”
|
No
|
Tanggal Order
|
Harga Order
|
Pelanggan
|
|
1
|
2011/11/12
|
1000
|
Ratih
|
|
2
|
2011/10/23
|
1600
|
Anita
|
|
3
|
2011/09/02
|
700
|
Ratih
|
|
4
|
2011/09/03
|
300
|
Ratih
|
|
5
|
2011/08/30
|
2000
|
Teguh
|
|
6
|
2011/10/04
|
100
|
Anita
|
-
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)
from pesanan group by pelanggan;
Untuk melihat hasil kita bisa
melakukan query select sebagai berikut :
Select * from total_pelanggan;
Hasil :
|
Pelanggan
|
SUM (HargaOrder)
|
|
Anita
|
1700
|
|
Ratih
|
2000
|
|
Teguh
|
2000
|
B.
HASIL PRAKTIKUM
- MySQL
Dari
table mahasiswa yang telah kita buat pada pertemuan sebelumnya, tambahkan
beberapa data yang akan kita gunakan untuk pertemuan kali ini, tambahkan kolom
gender kemudian update datanya.
Eitss jangan
lupa ya,.. sebelum kita melakukan percobaan pada MySQL, kita harus masuk dulu
ke MySQL_nya dengan mengetikkan “mysql –u root” dan memilih
database yang akan kita pakai dengan “use nama_database”.
1.
Tampilkan banyaknya data
mahasiswa yang telah di inputkan. Kemudian cari nim atau id mahasiswa yang
paling kecil, paling besar dan rata-ratanya.
Berikut Syntaxnya :
SELECT * from mahasiswa;
// untuk menampilkan seluruh data mahasiswa
SELECT MAX(nim_mah) from mahasisswa; // untuk menampilkan nim_mahasiswa yang paling besar.
SELECT MIN(nim_mah) from mahasiswa; // untuk menampilkan nim_mahasiswa yang paling kecil.
SELECT AVG(nim_mah)from mahasiswa; // untuk menampilkan rata-rata nim_mahasiswa
2.
Tampilkan rata-rata id atau
nim mahasiswa yang data nimnya lebih dari 12.
Berikut Syntaksnya :
SELECT AVG(nim_mah) from mahasiswa where
nim_mah>12;
3.
Tampilkan jumlah mahasiswa
berdasarkan fakultas. Sehingga hasilnya adalah column nama fakultas &
count(*).
Berikut syntaksnya :
SELECT nama_fak, COUNT(*) from mahasiswa m, fakultas
f WHERE m.id_fak=f.id_fak GROUP BY nama_fak;
Catatan : ( mahasiswa m, fakultas f ) m dan f adalah
sebuah inisialisasi dari table mahasiswa dan fakultas. dengan begitu, kita bisa
menuliskan inisial m dan f untuk perintah selanjutnya.
4.
Tampilkan seperti nomor 3
dengan persyaratan jumlah mahasiswa yang lebih dari sama dengan 2 saja yang
ditampilkan.
berikut Syntaksnya :
SELECT nama_fak, COUNT(*) from mahasiswa m,
fakultas f WHERE m.id_fak=f.id_fak GROUP BY nama_fak HAVING COUNT(*)>=2;
5.
Tampilkan data mahasiswa
dengan persyaratan, jika jenis kelaminnya ‘L’ maka tertulis ‘Laki-laki’ dan
sebaliknya.
Berikut Syntaksnya :
SELECT *, CASE WHEN gender = ‘L’ THEN ‘Laki-laki’
ELSE ‘Perempuan’ END AS ket from mahasiswa;
6.
Buatlah view untuk query
penampilan data mahasiswa, fakultas. Ambil berdasarkan nim, nama mahasiswa,
nama fakultas.
Berikut Syntaksnya :
CREATE VIEW Data_Mahasiswa AS SELECT nim_mah,
nama_mah, nama_fak from mahasiswa, fakultas WHERE
mahasiswa.id_fak=fakultas.id_fak; // untuk
membuat VIEW
SELECT * from Data_Mahasiswa; // untuk menampilkan hasil VIEW
- PostgreSQL
Dari table mahasiswa yang telah kita buat pada pertemuan sebelumnya,
tambahkan beberapa data yang akan kita gunakan untuk pertemuan kali ini,
tambahkan kolom gender kemudian update datanya. J
1. Tampilkan banyaknya data mahasiswa yang telah di inputkan. Kemudian
cari nim atau id
mahasiswa yang paling kecil, paling besar dan rata-ratanya.
Berikut
Syntaksnya :
SELECT *
from mahasiswa; // untuk menampilkan
seluruh data mahasiswa
SELECT
MAX(nim_mah) from mahasiswa; // untuk
menampilkan nim_mahasiswa yang paling besar.
SELECT
MIN(nim_mah) from mahasiswa; // untuk
menampilkan nim_mahasiswa yang paling kecil.
SELECT
AVG(nim_mah)from mahasiswa; // untuk
menampilkan rata-rata nim_mahasiswa
2. Tampilkan rata-rata id atau nim mahasiswa yang data nimnya lebih
dari 12.
Berikut
Syntaksnya :
SELECT
AVG(nim_mah) from mahasiswa where nim_mah>12;
3. Tampilkan jumlah mahasiswa berdasarkan fakultas. Sehingga
hasilnya adalah column nama
fakultas & count(*).
Berikut
syntaksnya :
SELECT
nama_fak, COUNT(*) from mahasiswa m, fakultas f WHERE m.id_fak=f.id_fak GROUP
BY nama_fak;
Catatan : ( mahasiswa m,
fakultas f ) m dan f adalah sebuah inisialisasi dari table mahasiswa . Dengan
begitu, kita bisa menuliskan inisial m dan f untuk perintah selanjutnya.
4. Tampilkan seperti nomor 3 dengan persyaratan jumlah mahasiswa
yang lebih dari sama dengan
2 saja yang ditampilkan.
berikut Syntaksnya
:
SELECT
nama_fak, COUNT(*) from mahasiswa m, fakultas f WHERE m.id_fak=f.id_fak GROUP
BY nama_fak HAVING COUNT(*)>=2;
5. Tampilkan data mahasiswa dengan persyaratan, jika jenis
kelaminnya ‘L’ maka tertulis
‘Laki-laki’ dan sebaliknya.
Berikut Syntaksnya
:
SELECT *,
CASE WHEN gender = ‘L’ THEN ‘Laki-laki’ ELSE ‘Perempuan’ END AS ket from
mahasiswa;
6. Buatlah view untuk query penampilan data mahasiswa, fakultas.
Ambil berdasarkan nim, nama
mahasiswa, nama fakultas.
Berikut Syntaksnya
:
CREATE
VIEW Data_Mahasiswa AS SELECT nim_mah, nama_mah, nama_fak from mahasiswa,
fakultas WHERE mahasiswa.id_fak=fakultas.id_fak; // untuk membuat VIEW
SELECT *
from Data_Mahasiswa; // untuk menampilkan
hasil VIEW
C. EVALUASI PERBANDINGAN DBMS
MySQL dengan DBMS PostgreSQL
Dari hasil praktikum dan percobaan yang telah dilakukan, dapat di lihat
bahwa antara Postgre SQL dengan MySQL mempunyai sedikit perbandingan yang
terletak pada operator “COUNT, MAX, MIN, dan AVG”. pada MySQL kita harus
menuliskan operator tersebut TANPA SPASI, misalnya : “COUNT(*)”. Sedangkan pada
PostgreSQL, adanya spasi atau tidak adanya spasi dalam penulisan operator
tersebut tidaklah berpengaruh, misalnya : “COUNT (*)” atau “COUNT(*)”.
D. KESIMPULAN, KRITIK, SARAN, MANFAAT
berdasarkan dari hasil percobaan dan hasil praktikum, maka dapat
disimpulkan bahwa fungsi agregasi pada kedua DBMS ini mempunyai banyak kesamaan
walaupun ada sedikit perbedaan pada penggunaan ‘SPASI’ saat mengoperasikan
fungsi “COUNT, MAX, MIN, dan AVG”. menurut saya, penggunaan fungsi agregasi
yang sudah dilakukan pada saat praktikum sudah jelas, namun ada sedikit menurut
saya yang harus lebih di perjelas lagi yaitu pada penggunaan HAVING, seharusnya
pada modul yang dipakai bisa ditambah contohnya, tidak hanya penggunaan HAVING
SUM saja tapi ditambahkan penggunaan
HAVING COUNT juga. Dan yang terakhir saya berharap semoga sedikit tulisan di
blog saya ini bisa bermanfaat bagi pembaca.
E.
DAFTAR PUSTAKA
ANDI OFFSET, 2010, Shotcourse SQL
SERVER 2008 Express, Yogyakarta
