Laporan Praktikum DBD Bab V

AGREGASI SQL DAN VIEW

1. Dasar Teori

          Dalam Desain basis data memiliki banyak sekali perintah yang kita gunakan untuk mendesain basis data ini. Nah intinya banyak yang Perlu kita pelajari dalam mendesain basis data agar basis data yang kita buat lebih mudah untuk kita akses lagi. Di sini kita Ian membahas sedikit tentang apa sih itu Agregasi SQL dan View.

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. Adapun fungsi agregate yang disediakan oleh PostgreSQL dapat dilihat pada tabel berikut :

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;
hasil : 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

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 :

==> Untuk menampilkan informasi nama pengarang :

        Select pengarang_buk from buku group by pengarang_buk;

        

        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; 

       

==> Untuk menampilkan informasi buku tiap tahunnya :

        Select tahun_buk, count(*) from buku group by tahun_buk;

==> Untuk menampilkan informasi jumlah total uang tiap tahunnya :

        Select tahun_buk, sum(harga_buk) as total from buku group by tahun_buk;

HAVING

           Pemakaian HAVING terkait dengan GROUP BY, kegunaanya adalah untuk menentukan kondisi bagi GROUP  BY, dimana kelompok yang memenuhi kondisi saja yang akan dihasilkan.

Berikut Struktur yang digunakan :

Kita akan menggunakan tabel “pesanan”

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 :

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 ;

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”

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 :

2. TUGAS PRAKTIKUM

        Menggunakan MySQL

     Nim : 12

     Nama : edi

     Alamat : malang

     Fakultas : SAINTEK

     Nim : 13

     Nama : sinta

     Alamat : jogja

     Fakultas : SAINTEK

     Nim : 14

     Nama : luki

     Alamat : ponorogo

     Fakultas : PSIKOLOGI

1. Dari tabel mahasiswa yang telah dibuat tambahkan 3 data lagi, tambahkan kolom gender kemudian     update datanya dan tampilkan banyaknya data mahasiswa yang telah di inputkan. 

    

Kemudian cari nim atau id mahasiswa yang paling  kecil, paling besar dan rata-ratanya.

            ==> Data id_mahasiswa paling kecil

           ==> Data id_mahasiswa paling besar 

          ==> Data id_mahasiswa rata-ratanya.

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

3. Tampilkan jumlah mahasiswa berdasarkan fakultas. Sehingga hasilnya seperti berikut :

Query nya :

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

5. Tampilkan data 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.

        Menggunakan 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 di inputkan. 

       Kemudian cari nim atau id mahasiswa 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 berdasarkan fakultas. Sehingga hasilnya seperti berikut :

Query nya :

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

5. Tampilkan data 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.

3. Evaluasi perbedaan kelebihan dan kemudahan dari kedua DBMS

    Setelah saya mempraktikan atau melakukan kedua DBMS, Untuk perbandingan antara query dalam MySQL dan PostgreSQL dalam fungsi agregasi dan view, antara PostgreSQL dan MySQL keduanya memiliki query yang sama. Jadi untuk perbandingan kali ini tidak ada baik kelebihan maupun kekurangannya karena query keduanya sama tidak ada bedanya.

4. KESIMPULAN

    Dalam penelitian saya setelah melakukan percobaan dapat disimpulkan bahwa, 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. Agregasi dalam SQL merupakan proses untuk mendapatkan nilai dari sekumpulan data yang telah dikelompokkan. Pengelompokan data didasarkan pada kolom yang dipilih.

SARAN dan KRITIK

Dipraktikum kali ini tidak memberatkan karena soalnya cuma sedikit. Tapi di materi ini tidak sulit hanya membingungkan saja. Saya harap dipraktikum selanjutnya nggak banyak-banyak soalnya dan tidak lagi membingungkan materi selanjutnya :D

Manfaat Bagi Pembaca

Semoga apa yang saya tulis diblog ini tentang laporan yang berjudul "AGREGASI SQL dan VIEW" dapat menambah ilmu dan dapat mengetahui query-quey antara dua DBMS dan dapat menerapkannya. Amin.

DAFTAR PUSTAKA

• Nugroho, Adi. “Perancangan dan Implementasi Sistem Basis Data”, Yogyakarta : C.V Andi Offset, 2011.
• “Seri Panduan Praktis : Pengolahan Database dengan MySQL”, Semarang : C.V Andi Offset, 2006.
• Modul Praktikum desain basis data 2014 Bab V
• http://pribadiwidianto.blogspot.com/2011/12/pengelompokan-data-dan-agregasi-fungsi.html

Laporan Praktikum DBD BAB IV

PENGENALAN OPERATOR DASAR

1. Dasar Teori

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 namakolom pengganti from namatabel;

            Contoh : 

AND dan OR 

               Pada bagian sebelumnya kita menggunakan anak kalimat atau sintal-sintal 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.SQL menggunakan standar logika booleanthree-valued seperti pada tabel berikut:

Berikut struktur SQL untuk penampilan data :

select  *  fromnamatabelwherenamakolom  =  ‘pencarian  1’  and namakolom = 
‘pencarian 2’;

Atau

select * fromnamatabelwherenamakolom = ‘pencarian 1’ ornamakolom = ‘pencarian 2’;

Contoh :

BETWEEN

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  *  fromnama_tabelwherenama_kolombetween  ‘nilai_awal’  and ‘nilai_akhir’;

            Contoh : Memunculkan data pegawai dimana tanggal lahirnya antara tanggal 9-9-1999 sampai 1-1-2001.

 

IN dan NOT IN

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

Select  *  fromnama_tabelwherenama_kolomin  (kata_kunci1, kata_kunci2, kata_kunci13, kata_kunci14);

Contoh : menampilkan data pegawai yang memiliki ip 1,2, dan 3

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 kasuspenggunaan LIKE :

 Berikut struktur SQL untuk pencarian data menggunakan LIKE :

        select * fromnama_tabelwherenama_kolomlike ‘operator’;

        Contoh : Mencari data pegawai yang mempunyai nama dengan huruf awalan r.

 

         REGEXP

                        RegularExpression atau Regex, merupakan sebuah teknik atau cara untuk mencari persamaan-presamaanstring atau data dan memanipulasinya. Biasanya lebih sering digunakan untuk string. DidalamPostgreSQL disimbolkan dengan “~”. Berikut tabel daftar simbol pada reguler expression:

 

 

                      Berikut struktur SQL untuk pencarian data menggunakan REGEXP :

                      select  *  fromnama_tabelwherenama_kolom  ~  ‘operator_regexp  dan patern’;

                     Contoh :

                    

Hal ini juga berlaku jika data yang ada dalam bentuk angka. Berikut struktur perintah

dasar SQL :

selectdistinctnama_kolomfromnama_tabel;

Contoh :

LIMIT

LIMIT dan OFFSET digunakan untuk membatasi jumlah output dari queryberdasarkan 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 O FFSET. Berikut struktur perintah dasar SQL :

select * fromnama_tabel limit 2;

Atau

select * fromnama_tabeloffset 2;

Contoh :

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 :

selectnama_kolomfromnama_tabel  union  selectnama_kolomfromnama_tabel;

atau

selectnama_kolomfromnama_tabel  union  allselectnama_kolomfromnama_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 :

selectnama_kolomfromnama_tabelexceptselectnama_kolomfromnama_tabel;

Perintah INTERSECT hanya akan menampilkan seluruh isi dari data yang memiliki kesamandiantara hasil kedua query tersebut. Berikut strukturquery penggunaan INTERSECT :

selectnama_kolomfromnama_tabelexceptselectnama_kolomfromnama_tabel;

 

Sedangan gambar kedua menyatakan EXCEPT dimanaobject yang diambil hanya yang  kuning. Sehingga object yang merah atau object kuning yang sama dengan object merah tidak diambil.

Gambar ke tiga menyatakan INTERSECT yaitu mengambil object yang berwarna hijau atau object memiliki kandungan dari object kuning dan merah.

Contoh :

2.  TUGAS RUMAH

            Persiapan praktek  :  Gunakan tabel mahasiswa dan fakultas pada pertemuan 3.  Tambahkan kolom gender di tabel mahasiswa kemudian update data mahasiswa. Insertdata mahasiswa hingga 7 baris.

Nah Terlebih dahulunya harus lengkap seperti ini ya :

 

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

2. 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. *Boleh dengan awalan dan ciri yang lain namun karakteristiknya harus sesuai soal.

10. Buatlah tabel dengan nama organ_dalam dan organ_luar sebagai berikut

    

Sebelum itu kita terlebih dahulu membuat tabel

Tampilkan  data  dari  kedua  tabel  tersebut  menggunakan  UNION,  EXCEPT  dan INTERSECT.

3. Perbandingan PostgreSQL dan MySQL

            Dalam penggunaan query untuk operasi dasar dalam PostgreSQL dan MySQL memiliki beberapa berbedaan yakni dalam penggunaan perintah REGEXP dan EXCEPT, INTERSECT. 

Dalam PostgreSQL, REGEXP disimbolkan dengan "~". Dengan simbol-simol ini :

                      Sedangkan dalam MySQL, REGEXP tidak disimbolkan tetapi dalam penulisan sedangkan dalam tulisan REGEXP dengan simbol-simbol tambahan sebagai berikut :

4. KESIMPULAN

            Berdasarkan praktikum yang telah saya lakukan dalam diambil kesimpulan dimana operasi dasar SQL berguna untuk operasi standar dan manipulasi data. Operator-operator tersebut adalah sebagai berikut :

• As yang digunakan menampilkan label kolom dengan menggunakan nama lain.
• AND and OR berguna untuk mencari data dengan beberapa ketentuan.
•  BETWEEN yang berguna untuk menentukan abar nilai dari tertinggi dan terendah yang bisa juga menggunakanoperator AND and OR dengan menggunakan operator pembanding seperti labih dari “>” maupun kurang dari “<” dan operator pembanding lain,
• IN dan NOT IN yang berguna untuk melakukan pencocokan dengan salah satu yang ada suatu daftar nilai, operator ini juga sama fungsinya dengan INTERSECT dan EXCEPT.
• LIKE digunakan ketika kita ingin mencari sebuah data yang hanya diwakili oleh salah satu atau lebih hurufnya saja.
• REGEXP yang berguna untuk mencari  DISTINCT yang berguna untuk menampilkan data agar data terjadi penggandaan atau duplikasi
•  LIMIT dan OFFSET yang berguna untuk menampilkan data dengan batasan jumlah output berdasarkan Dow dan bukan berdasarkan where.
• UNION yang berguna untuk menampilkan data dengan dikombinasikan (gabungan) dengan data dari tabel lain.

       KRITIK DAN SARAN

            Dalam penggunaan perintah dasar SQL perlu diperhatiin operator dan fungsinya. Perlu juga diperhatikan penggunaannya dalam DBMS PostgreSQL atau MySQL karena kedua DBMS tersebut memiliki beberapa query yang berbeda untuk fungsi yang sama.

MANFAAT BAGI PEMBACA

            Dengan adanya postingan ini, semoga dapat bermanfaat bagi pembacanya mulai dari mengetahui, mengenal, dan menerapakan perintah dasar SQL dengan menggunakan PostgreSQL maupun MySQL.

DAFTAR PUSTAKA

• Nugroho, Adi. “Perancangan dan Implementasi Sistem Basis Data”, Yogyakarta : C.V Andi Offset, 2011.
• “Seri Panduan Praktis : Pengolahan Database dengan MySQL”, Semarang : C.V Andi Offset, 2006.
• Modul Praktikum desain basis data 2014 Bab IV