AGREGASI SQL DAN VIEW

A.LANDASAN TEORI

Query, Subquery, dan View

-          Query

               Query adalah statement dalam sql yang berfungsi untuk mendapatkan atau mengambil data dari database (satu atau beberapa tabel/view) berdasarkan kriteria-kriteria tertentu yang diberikan. Melakukan seleksi dari satu tabel, sehingga hanya melibatkan field yang ada pada table itu saja. Contoh :

SELECT field-1, …, field-n, fungsi_agregasi

FROM tabel

WHERE kondisi

GROUP BY field-1, …, field-n

HAVING kriteria_fungsi_agregasi

ORDER BY field

Melakukan seleksi lebih dari satu tabel, sehingga dapat melibatkan beberapa field dari tabel yang berbeda asalkan tabel-tabel tersebut saling berelasi. Contoh :

SELECT field-1, …, field-n, fungsi_agregasi

FROM table-1, …, table-n

WHERE kondisi

GROUP BY field-1, …, field-n

HAVING kriteria_fungsi_agregasi

ORDER BY field

-       Subquery

Subquery adalah query dalam query. Artinya kita dapat membuat beberapa subquery dalam 1 query. Subquery dapat diletakkan pada WHERE clause, FROM clause, dan SELECT clause.

·          WHERE clause

WHERE clause adalah subquery yang paling umum digunakan dan disebut nested subquery, contoh:

SELECT * from all_tables tabs

where tabs.table_name in(select cols.table_name from all_tab_columns cols where cols.column_name = 'id_suplier');

·         FROM clause

Subquery pada FROM clause disebut inline views, contoh:

SELECT suplier.nama, subquery1.total_amt from suplier,

(select suplier_id, Sum(orders.amount) as total_amt from orders) subquery1,

where subquery1.supplier_id = suplier.id_suplier;

·         SELECT clause

Subquery yang diletakkan pada SELECT clause harus bersifat single value. Oleh karena itu fungsi agregasi seperti SUM, COUNT, MIN, MAX biasanya digunakan pada subquery jenis ini, contoh:

SELECT tbls.owner, tbls.table_name,

(select count(column_name) as total_columns from all_tab_columns cols where cols.owner = tbls.owner) subquery2

from all_tables tbls;

-       View

View hanyalah tabel semu yang merupakan representasi dari sebuah pernyataan SQL yang disimpan dalam memori sehingga mudah dapat digunakan kembali.

CREATE VIEW nama_view AS

SELECT field-1, …, field-n, fungsi_agregasi

FROM table-1, …, table-n

WHERE kondisi

GROUP BY field-1, …, field-n

HAVING kriteria_fungsi_agregasi

ORDER BY field

Membuat view data_pembelian, contoh :

CREATE VIEW data_pembelian AS

SELECT P.kode_pembelian, B.nama_barang, K.nama_konsumen

FROM pembelian P, barang B, konsumen K

WHERE B.kode_barang = P.kode_barang and

K.kode_konsumen = P.kode_konsumen;

Agregasi SQL

Agregasi dalam SQL merupakan proses untuk mendapatkan nilai dari sekumpulan data yang telah dikelompokkan. Pengelompokan data didasarkan pada kolom atau kombinasi kolom yang dipilih.

Beberapa fungsi untuk agregasi adalah:

1. MAX : mencari data terbesar dari sekelompok data

2. MIN : mencari data terkecil dari sekelompok data

3. COUNT : mencari cacah data (data NULL tidak akan dimasukkan dalam perhitungan,

kecuali disebutkan secara khusus)

4. SUM : mencari jumlah dari sekumpulan data numeris

5. AVG : mencari rerata dari sekumpulan data numeris

Sintaks SQL untuk melakukan pengelompokan adalah:

SELECT [daftar-kolom-non-agregasi,] daftar-kolom-agregasi

FROM daftar-tabel

[WHERE kondisi-record-dalam-tabel]

[GROUP BY daftar-kolom-agregasi]

[HAVING kodisi-filter-hasil-agregasi]

[ORDER BY daftar-kolom-pengurutan]

B.LAPORAN HASIL PRAKTIKUM

           Untuk laporan hasil praktikum, kali ini memakai data yang sudah dibuat pada laporan praktikum yang ke 4, dari tabel mahasiswa dengan menambah atau mengupdate 3 data lagi , dengan menambahkan kolom gender kemudian update datanya dengan menampilkan banyaknya data mahasiswa yang telah di inputkan. Berikut querynya:

SELECT * FROM MAHASISWA;

Update:

Kemudian mencari nim atau id mahasiswa yang paling kecil,

Berikut querynya:

SELECT MIN(nim_mah) FROM MAHASISWA;

Lalu mencari nim atau id mahasiswa paling besar

Berikut querynya:

SELECT MAX(nim_mah) FROM MAHASISWA;

dan rata-ratanya

berikut querynya:

SELECT AVG(nim_mah) FROM MAHASISWA;

            Untuk yang kedua, hampir sama dengan pencarian rata-rata nim atau mahasiswa hanya saja dalam hal ini harus mencari rata-rat nim atau id mahasiswa yang  lebih dari 12.

Berikut querinya:

SELECT AVG(nim_mah) FROM MAHASISWA WHERE nim_mah > 12;

            Dalam penggunaan fungsi GROUP BY biasanya sering kali diperlukan untuk menjalankan agregate menjadi sebuah kelompok dari hasil Query. Jadi GROUP BY sendiri merupakan fungsi yang digunakan untuk melakukan pengelompokan dari perintah SELECT. Dalam laporan ini pda fungsi group ialah menampilkan jumlah mahasiswa berdasarkan fakultas yang ada pada tabel mahasiswa.

Berikut querynya:

SELECT nama_fak, COUNT(*) FROM mahasiswa, fakultas WHERE mahasiswa.id_fak=fakultas.id_fakultas GROUP BY fakultas.nama_fak;

            Selanjutnya tetap pada fungsi GROUP BY hanya saja menampilkan jumlah mahasiswa berdasarkan fakultas dengan syarat jumlah mahasiswa yang lebih dari sama dengan 2 saja yang ditampilkan, dengan menggunakan having count.

Berikut querynya:

SELECT nama_fak, COUNT(*) FROM MAHASISWA, fakultas WHERE mahasiswa.id

_fak=fakultas.id_fakultas GROUP BY nama_fak HAVING COUNT(mahasiswa.id_fak) >=2;

            Pada tahap berikutnya yaitu menampilkan data mahasiswa dengan persyaratan, jika jenis kelaminnya “L” maka tertulis laki-laki dan bila “P” maka tertulis perempuan.

Berikut querynya:

SELECT nama_mah,id_fak,gender,nim_mah,case WHEN gender ='1' THEN 'laki-laki' ELSE 'perempuan' END AS gender FROM mahasiswa;

            Pada tahap yang terakhir membuat view untuk query penampilan data mahasiswa, fakultas. Ambil berdasarkan nim, nama mahasiswa, nama fakultas.

-       Untuk yang pertama kita run dulu fungsi VIEW, dengan membuat VIEW mahasiswa2

Berikut querynya:

CREATE VIEW mahasiswa2 AS SELECT nama_mah , nim_mah, nama_fak FROM mahasiswa, fakultas;

-       Untuk yang terakhir buat VIEW mahasiswa3

Berikut querynya:

CREATE VIEW mahasiswa3 AS SELECT nama_mah , nim_mah, nama_fak from mahasiswa, fakultas WHERE mahasiswa.id_fak=fakultas.id_fakultas;

C.PERBANDINGAN POSTGRESQL DAN MYSQL

            Pada sesi persamaan postgresql dengan mysql sama persis dengan cara penulisannya mungkin tidak ada perbedaan, tentang perbedaan hanya saja pada penulisan COUNT, untuk mysql sendiri tidak boleh adanya spasi di query COUNT dengan (*), tapi pada postgresql mungkin di perbolehkan penggunaan spasi dalam query COUNT.

D.KESIMPULAN

            Dari praktikum yang telah saya lakukan dapat ditarik kesimpulan bahwa postgre maupun mysql query yang digunakan untuk perintah SELECT yaitu menampilkan data seperti data mahasiswa nim terkecil sampai rata-rata dan dan penggunaan VIEW dalam penyelarasan data yang ditampilkan.

E.DAFTAR PUSTAKA

http://pribadiwidianto.blogspot.com/2011/12/query-subquery-dan-view.html
arifnazarp.net63.net/web_documents/agregasi.pdf

Modul praktikum desain basis data 2014