modul V

AGREGASI SQL DAN VIEW

Bahasan dan Sasaran

Ø Bahasan

- Agregasi sql yang diperlukan dalam perhitungan data

- View yang merupakan tabel bayangan yang berisi query baik dari satu tabel atau berbagai tabel

Ø Sasaran

- Mahasiswa memahami operator agregasi sql sehingga mempermudah dalam hal perhitungan data.

-  Mahasiswa memahami cara penggunaan view

 Materi

A.    AGREGATE OPERATOR

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 untuk agregasi adalah:

MAX :Mencari data terbesardarisekelompok data

MIN :Mencari data terkecildarisekelompok data

COUNT :Mencaricacah data

SUM :Mencarijumlahdarisekumpulan data numeris

AVG :Mencarinilai rata-rata darisekumpulan data numeris

B.     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;

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

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

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

Hasil Praktikum

Dari hasil praktikum kemarin yaitu:

1.    Tabel mahasiswa yang telah dibuat pada praktikum sebelumnya, cari nim atau id mahasiswa yang paling kecil, paling besar dan rata-ratanya.

Menampilkan data mahasiswa:

 

-->  Menampilkan nim yang paling kecil, paling besar dan rata-ratanya.

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

3.    Tampilkan jumlah mahasiswa laki-laki dan perempuan
Mahasiswa laki-laki

 Mahasiswa Perempuan

4.    Tampilkan seperti no.3 dengan persyaratan, jumlah mahasiswa yang lebih dari sama dengan 2 saja yang ditampilkan

Mahasiswa perempuan

Mahasiswa laki-laki

5.    Tampilkan tabel mahasiswa dengan persyaratan jika jenis kelaminnya ‘L’ maka tertulis laki-laki, dan bila ‘P’ maka tertulis perempuan.

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

Setelah kita buat d postge sql, sekarang kita coba menggunakan DBMS Mysql

Menampilkan data mahasiswa yang pernah di buat pada praktikum sebelumnya:

Menampilkan nim yang terendah dari tabel tersebut

Kemudian menampilkan nim yang tertinggi

Selanjutnya kita tampilkan  rata-rata nim tersebut

Menampilkan rata-rata nim mahasiswa dengan syarat nim >12

Menampilkan jumlah  Mahasiswa laki-laki:

Menampilkan jumlah  Mahasiswa perempuan:

Menampilkan tabel mahasiswa dengan persyaratan jika jenis kelaminnya ‘L’ maka tertulis laki-laki, dan bila ‘P’ maka tertulis perempuan.

Membuat view untuk query penampilan data mahasiswa,fakultas. Ambil berdasarkan nim, nama mahasiswa, nama fakultas

EVALUASI

Perbedaan secara umum dari postgre dan mysql, cukup banyak Namun jika dilihat dari hasil praktikum di atas untuk masalah penulisan query (syntax) hampir semua d postgre dan DBMS mysql(phpMyAdmin) sama..sehingga kita tidak perlu bingung-bingung menghafal..

 disini penulis menemukan satu perbedaan yaitu pada soal no 5. pada postgre having count(*), jika pada phpMyAdmin menggunakan having saja tanpa di tambah count(*) ataupun menggunakan count(*) dijalankan seperti gambar berikut:

 

postgre

Kesimpulan:

Pada praktikum kali ini membahas tentang agregasi sql dan view.
 disini kita dapat mengetahui bashwa agregasi merupakan proses untuk mendapatkan nilai dari sekumpulan data yang telah kita kelompokkan, sedangkan view tidak memiliki data sendiri , view ini memiliki data tapi berasal dari tabel-tabel lain.
fungsi agregasi :

MAX :Mencari data terbesardarisekelompok data

MIN :Mencari data terkecildarisekelompok data

COUNT :Mencaricacah data

SUM :Mencarijumlahdarisekumpulan data numeris

AVG :Mencarinilai rata-rata darisekumpulan data numeris

manfaat pembaca:

-          Menambah wawasan dalam menjalankan operator agregasi sql sehingga memudahkan menghitung data, dan bisa memahami cara penggunaan view.

-          Agar mengetahui perbedaan syntax dalam menggunakan postgre SQL  dan DBMS MySQL

 Daftar pustaka

-          Materi praktikum Basis Data 2014.pdf

       http://pribadiwidianto.blogspot.com/2011/12/pengelompokan-data-dan-agregasi-fungsi.html

laporan praktikum modul IV

BAB IV

PENGENALAN OPERATOR DASAR

Bahasan dan Sasaran

Bahasan

- Operator dasar dalam standar query sql

- Operator dasar yang berhubungan dengan manipulasi data.

Sasaran

1. Mahasiswa memahami operator dasar dalam standar query sql.

2. Mahasiswa memahami cara penggunaan operator dasar dalam hal manipulasi data.

 Materi

1)      AS

AS biasa digunakan untuk menampilkan label kolom dengan nama lain sehingga yang akan muncul dalam hasil query bukan nama asli kolom, tetapi nama yang mungkin lebih sesuai dan mudah dimengerti. AS digunakan setelah nama kolom yang akan diganti yang kemudian diikuti dengan nama penggantinya. Berikut struktur querynya :

select namakolom as namakolompengganti from namatabel;

2)      AND dan OR

Pada bagian sebelumnya kita menggunakan anak kalimat atau sintaks WHERE hanya pada konteks yang sederhana. Berikut ini kita akan mencoba menggunakan WHERE untuk konteks yang lebih kompleks lagi, anak kalimat where yang kompleks akan bekerja dengan baik dengan menggunakan kata AND dan OR.

Operator And menampilkan baris jika kondisi 1 dan kondisi 2 benar, Berikut struktur SQL untuk penampilan data : select * from namatabel where namakolom = ‘pencarian 1’ and namakolom = ‘pencarian 2’;

Operator And menampilkan baris jika kondisi 1 dan kondisi 2 benar, Berikut struktur SQL untuk penampilan data : select * from namatabel where namakolom = ‘pencarian 1’ or namakolom = ‘pencarian 2’;

3)      BETWEEN digunakan untuk menentukan lebar nilai dari nilai terendah dan nilai tertinggi. Pada BETWEEN menggunakan operator pembanding seperti pada tabel berikut;

Logika operator BETWEEN sebagai berikut :

Ø  Nilai a dalam formula “a BETWEEN x AND y” indentik dengan “a >= x AND a <= y“

Ø  “a NOT BETWEEN x AND y “ identik dengan “a < x OR a > y”

Berikut Struktur yang digunakan :

select * from nama_tabel where nama_kolom between ‘nilai_awal’ and ‘nilai_akhir’;

4)      IN dan NOT IN

Operator IN berguna melakukan pencocokan dengan salah satu yang ada pada suatu daftar nilai. Berikut Struktur yang digunakan

Select * from nama_tabel where nama_kolom in (kata_kunci1, kata_kunci2, kata_kunci13, kata_kunci14);

5)      LIKE

LIKE digunakan ketika kita ingin mencari sebuah data yang hanya diwakili oleh salah satu atau lebih hurufnya saja. Misalkan kita ingin mencari nama yang huruf awalnya R, maka kita harus menggunakan LIKE. Berikut adalah tabel contoh kasus penggunaan LIKE :

6)      REGEXP

Regular Expression atau Regex, merupakan sebuah teknik atau cara untuk mencari persamaan - peresamaan string atau data dan memanipulasinya. Biasanya lebih sering digunakan untuk string. Didalam PostgreSQL disimbolkan dengan “~”.

Berikut struktur SQL untuk pencarian data menggunakan REGEXP :

select * from nama_tabel where nama_kolom ~ ‘operator_regexp dan patern’;

1)      DISTINCT

DISTINCT sering kali diperlukan untuk mengembalikan hasil dari sebuah query dengan tidak terdapat duplikasi, artinya pada hasil outputnya tidak terjadi kesamaan data meskipun pada data sesungguhnya sangat mungkin banyak duplikasi..Hal ini juga berlaku jika data yang ada dalam bentuk angka. Berikut struktur perintah dasar SQL :

select distinct nama_kolom from nama_tabel;

2)      LIMIT

LIMIT dan OFFSET digunakan untuk membatasi jumlah output dari query berdasarkan jumlah row bukan karena kondisi seperti WHERE. Bisa dikatakan LIMIT adalah untuk menentukan jumlah baris yang akan ditampilkan yang dihitung dari baris pertama, sedangkan OFFSET digunakan untuk menghilangkan baris sesuai dengan jumlah yang diberikan pada OFFSET. Berikut struktur perintah dasar SQL :

select * from nama_tabel limit 2;

Atau

select * from nama_tabel offset 2;

3)      UNION, EXCEPT dan INTERSECT

Hasil dari dua buah query dapat dikombinasikan dengan menggunakan UNION, EXCEPT atau INTERSECT. UNION digunakan untuk menggabungkan hasil dua buah query menjadi satu kolom. Berikut struktur query untuk UNION :

select nama_kolom from nama_tabel union select nama_kolom from nama_tabel;

atau

select nama_kolom from nama_tabel union all select nama_kolom from nama_tabel;

EXCEPT digunakan untuk menampilkan hanya query pertama saja, sedangkan hasil query kedua dan yang sama dengan hasil query kedua tidak akan ditampilkan. Berikut struktur query penggunaan EXCEPT :

select nama_kolom from nama_tabel except select nama_kolom from nama_tabel;

Perintah INTERSECT hanya akan menampilkan seluruh isi dari data yangmemiliki kesaman diantara hasil kedua query tersebut. Berikut struktur query penggunaan INTERSECT :

select nama_kolom from nama_tabel except select nama_kolom from nama_tabel;

HASIL PRAKTIKUM

Sebelum kita melanjutkan praktikum ini, kita buka dulu tabel mahasiswa dan fakultas pada pertemuan 3 kemarin. Kemudian kita tambahkan kolom gender di tabel mahasiswa selanjutnya update data mahasiswa. Insert data mahasiswa hingga 7 baris.

 Berikut ini kita adalah query dan hasilnya ;

  Menambahkan kolom gender dengan cara

Setelah itu baru kita input hingga 7 baris

dan berikut ini hasilnya:

Berdasarkan tabel sebelumnya yaitu bab 3, tampilkan data mahasiswa dengan nama dari kolomnya berubah menjadi nomor_identitas, nama, alamat,nama_fakultas tanpa harus merubah nama kolom secara permanen. 

Tampilkan nama mahasiswa fakultas saintek yang berjenis kelamin laki-laki. 

Kemudian nama mahasiswa fakultas saintek atau berjenis kelamin laki-laki

3. Suatu tabel mahasiswa terdapat 7 baris data mahasiswa. Tampilkan data mahasiswa dari nomor 3 sampai dengan 5, kemudian tampilkan juga data mahasiswa yang bukan dari nomor 3 sampai dengan 5 menggunakan between dan tanpa menggunakan between. 

4. Tampilkan mahasiswa dengan nomor 2,3,5 menggunakan operator IN. 
 

5. Tampilkan semua data mahasiswa yang namanya mempunyai awalan huruf “a”. 

6. Tampilkan semua data mahasiswa yang namanya mempunyai akhiran “a” dan huruf ke-3 dari nama tersebut terdapat huruf “n”.

7. Tampilkan nomor fakultas di tabel mahasiswa dengan nomor harus tidak ada yang sama dari baris pertama sampai akhir. 

8. Tampilkan data mahasiswa 3 baris saja. 

9. Tampilkan data mahasiswa dengan ciri-ciri memiliki nama awalan a kemudian kata selanjutnya boleh r atau boleh t atau boleh d. misalkan yang memenuhi kriteria ciri tersebut seperti adi, atik, arif, adam dll. 

10. Buatlah tabel dengan nama organ_dalam dan organ_luar sebagai berikut :

menampilkan data dari kedua tabel tersebut menggunakan UNION, EXCEPT dan INTERSECT

Nah, kan udah tau carnya menggunakan postgree SQL sekarang kita coba membuat d

DBMS MYSQL :

menampilkan data mahasiswa dengan nama dari kolomnya berubah menjadi nomor_identitas, nama, alamat,nama_fakultas tanpa harus merubah nama kolom secara permanen. 

menampilkan nama mahasiswa fakultas saintek yang berjenis kelamin laki-laki. Kemudian nama mahasiswa fakultas saintek atau berjenis kelamin laki-laki

Suatu tabel mahasiswa terdapat 7 baris data mahasiswa. Tampilkan data mahasiswa dari nomor 3 sampai dengan 5, kemudian tampilkan juga data mahasiswa yang bukan dari nomor 3 sampai dengan 5 menggunakan between 

dan tanpa menggunakan between.

Tampilkan mahasiswa dengan nomor 2,3,5 menggunakan operator IN.

Tampilkan semua data mahasiswa yang namanya mempunyai awalan huruf “a”. 

Tampilkan semua data mahasiswa yang namanya mempunyai akhiran “a” dan huruf ke-3 dari nama tersebut terdapat huruf “n”.

Tampilkan nomor fakultas di tabel mahasiswa dengan nomor harus tidak ada yang sama dari baris pertama sampai akhir.

Tampilkan data mahasiswa 3 baris saja.

Tampilkan data mahasiswa dengan ciri-ciri memiliki nama awalan a kemudian kata selanjutnya boleh s atau boleh r atau boleh u.

menampilkan data dari kedua tabel tersebut menggunakan UNION,

menampilkan data dari kedua tabel tersebut menggunakan EXCEPT

menampilkan data dari kedua tabel tersebut menggunakan INTERSECT

Evaluasi:

 Dari hasil praktikum tersebut terdapat perbedaan dalam nenuliskan query, misalnya pada Regexp .

Kesimpulan dari praktikum diatas…
Pengenalan Operator Dasar pada sql meliputi :As, And dan Or, Between, In dan Not In, Regexp, Distinct,Limit, Union, Except,Intersect.

 banyak kemiripan dalam penulisan query pada posgree sql maupun mysql, namun menurut saya membuat table dan database lebih cepat menggunakan mysql, daripada postgree.. :)