Study IT: Oktober 2014

Praktikum Desain Basis data 5

Agregasi SQL dan View

SQL Shell Vs CMD mysql Pada XAMPP

Landasan Teori

Pada pembahasan kali ini, kita akan membahas entang Agregasi SQL dan View. apa itu? Berikut sedikit cuplikannya.

Agregasi data adalah proses di mana informasi yang dikumpulkan dan disajikan dalam bentuk ringkasan, untuk tujuan seperti analisis statistik. Sebuah agregasi tujuan umum adalah untuk mendapatkan informasi lebih lanjut tentang kelompok-kelompok tertentu berdasarkan variabel tertentu.

(http://searchsqlserver.techtarget.com/definition/data-aggregation)

Dalam manajemen database fungsi agregat adalah fungsi di mana nilai-nilai beberapa baris dikelompokkan bersama sebagai masukan kriteria tertentu untuk membentuk nilai tunggal makna lebih signifikan atau pengukuran seperti set , sebuah as atau daftar .

Fungsi agregat umum meliputi:

-Rata-rata ()
-Count ()
-Maksimum ()
-Median ()
-Minimum ()
-Mode ()
-Sum ()

Fungsi agregat yang umum digunakan pada beberapa bahasa pemrograman , spreadsheet , dan aljabar relasional.
(http://en.wikipedia.org/wiki/Aggregate_function)

Fungsi agregat itu seperti COUNT, SUM dan AVG. Fungsi agregat mengambil banyak nilai dan memberikan hanya satu nilai. Misalnya fungsi SUM akan agregat nilai-nilai 2, 4 dan 5 untuk memberikan nilai tunggal 11.
(http://sqlzoo.net/wiki/SUM_and_COUNT)

Fungsi agregat sering membutuhkan tambahan pernyataan GROUP BY.
Pernyataan GROUP BY digunakan dalam hubungannya dengan fungsi agregat untuk kelompok hasil-set oleh satu atau lebih kolom tersebut.
(http://www.w3schools.com/sql/sql_groupby.asp)

Dalam teori database , view adalah hasil set dari yang tersimpan permintaan pada data yang , yang basis data pengguna dapat query seperti mereka akan dalam sebuah objek koleksi database yang gigih. Perintah query yang telah ditetapkan ini disimpan dalam kamus basis data. Tidak seperti tabel dasar biasa dalam database relasional , view tidak membentuk bagian dari skema fisik : sebagai hasilnya ditetapkan, itu adalah tabel virtual dihitung atau disusun secara dinamis dari data dalam database ketika akses ke tampilan yang diminta. Perubahan diterapkan pada data dalam tabel yang mendasari relevan tercermin dalam data yang ditunjukkan dari view. Dalam beberapa NoSQL database, view adalah satu-satunya cara untuk query data.

View dapat memberikan keuntungan lebih dari tabel:

-View dapat mewakili subset dari data yang terdapat dalam tabel. Akibatnya, View dapat membatasi tingkat eksposur dari tabel yang mendasari ke dunia luar: pengguna tertentu mungkin memiliki izin untuk query view, sementara menolak akses ke seluruh tabel dasar.
-View dapat bergabung dan menyederhanakan beberapa tabel ke tabel virtual tunggal.
-View dapat bertindak tabel sebagai agregat, dimana mesin database Data agregat ( sum , average , dll) dan menyajikan hasil dihitung sebagai bagian dari data.

-view dapat menyembunyikan kompleksitas data. Sebagai contoh, view bisa muncul sebagai Sales2000 atau Sales2001 transparan partisi tabel yang mendasari sebenarnya.
-Views mengambil sangat sedikit ruang untuk menyimpan; database hanya berisi definisi pandangan, bukan salinan semua data yang menyajikan.
-Tergantung pada SQL engine yang digunakan, view dapat memberikan keamanan ekstra. Sama seperti fungsi (dalam pemrograman) dapat memberikan abstraksi , juga bisa view database. Secara paralel lain dengan fungsi, pengguna database dapat memanipulasi bersarang dilihat, sehingga satu view dapat agregat data dari view lain. Tanpa menggunakan view, normalisasi database di atas bentuk normal kedua akan menjadi jauh lebih sulit. View dapat membuat lebih mudah untuk menciptakan lossless bergabung dekomposisi.
(http://en.wikipedia.org/wiki/View_SQL)

Setelah membaca sedikit cuplikan di atas, maka berikut adalah hasil praktikum menggunakan PostgreSQL dan hasil pekerjaan rumah mengunakan CMD mySQL serta perbandingan antar kedua DBMS tersebut.

Hasil praktikum

Menggunakan SQL Shell (PSQL)

Server [localhost]:

Database [postgres]:

Port [5432]:

Username [postgres]: ayu

Password for user ayu:

psql (9.3.5)

WARNING: Console code page (850) differs from Windows code page (1252)

8-bit characters might not work correctly. See psql reference

page "Notes for Windows users" for details.

Type "help" for help.

postgres=> \c ayu13650039

WARNING: Console code page (850) differs from Windows code page (1252)

8-bit characters might not work correctly. See psql reference

page "Notes for Windows users" for details.

You are now connected to database "ayu13650039" as user "ayu".

ayu13650039=> \z

Access privileges

Schema | Name | Type | Access privileges | Column access privileg

--------+-------------------+-------+-------------------+--------------------------

public | fakultas | table | |

public | gaji | table | |

public | identitas13650039 | table | ayu=arwdDxt/ayu |

public | mahasiswa | table | |

public | organ_dalam | table | |

public | organ_luar | table | |

public | penitipan | table | |

public | penjaga | table | |

public | spd_motor | table | |

(9 rows)

ayu13650039=> select * from mahasiswa;

---------+----------+------------+--------+--------------+--------

14 | luki | ponorogo | 2 | 08257656879 | L

11 | Sinta | malang | 1 | 0856789012 | P

12 | Binta | mojokerto | 3 | 985245678213 | P

13 | Wana | kediri | 4 | 085345673645 | P

15 | adi | malang | 1 | 089873654353 | L

16 | Egi | mojokerto | 1 | 085765432123 | P

17 | runi | bali | 1 | 0857789012 | P

(7 rows)

1. Dari tabel mahasiswa yang telah dibuat tambahkan 3 data lagi (Sinta, edi, luki), 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.

ayu13650039=> update mahasiswa set nama_mah = 'edi' where nim_mah = 12;

UPDATE 1

ayu13650039=> select * from mahasiswa;

---------+----------+------------+--------+--------------+--------

14 | luki | ponorogo | 2 | 08257656879 | L

11 | Sinta | malang | 1 | 0856789012 | P

13 | Wana | kediri | 4 | 085345673645 | P

15 | adi | malang | 1 | 089873654353 | L

16 | Egi | mojokerto | 1 | 085765432123 | P

17 | runi | bali | 1 | 0857789012 | P

12 | edi | mojokerto | 3 | 985245678213 | P

(7 rows)

ayu13650039=> select count (*) from mahasiswa;

count

-------

(1 row)

ayu13650039=> select max (nim_mah) from mahasiswa;

max

-----

(1 row)

ayu13650039=> select min (nim_mah) from mahasiswa;

min

-----

(1 row)

ayu13650039=> select avg (nim_mah) from mahasiswa;

avg

---------------------

14.0000000000000000

(1 row)

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

ayu13650039=> select avg (nim_mah) from mahasiswa where nim_mah > 12;

avg

---------------------

15.0000000000000000

(1 row)

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

ayu13650039=> select nama_fak, count (*) from fakultas f, mahasiswa m where f.id

_fak=m.id_fak group by nama_fak;

nama_fak | count

-----------+-------

Psikologi | 1

Tarbiyah | 1

SAINTEK | 4

humaniora | 1

(4 rows)

4. Tampilkan seperti nomor 3 dengan persyaratan jumlah mahasiswa yang lebih dari

sama dengan 2 saja yang ditampilkan.

ayu13650039=> select nama_fak, count (*) from fakultas f, mahasiswa m where f.id

_fak=m.id_fak group by nama_fak having count (nama_fak) >= 2;

nama_fak | count

----------+-------

SAINTEK | 4

(1 row)

5. Tampilkan data mahasiswa dengan persyaratan, jika jenis kelaminnya “L” maka

tertulis laki-laki dan bila “P” maka tertulis perempuan.

ayu13650039=> select *, case when gender = 'L' then 'Laki-laki' else 'Perempuan'

end as jenis_Kelamin from mahasiswa;

---------+----------+------------+--------+--------------+--------+-------------

14 | luki | ponorogo | 2 | 08257656879 | L | Laki-laki

11 | Sinta | malang | 1 | 0856789012 | P | Perempuan

13 | Wana | kediri | 4 | 085345673645 | P | Perempuan

15 | adi | malang | 1 | 089873654353 | L | Laki-laki

16 | Egi | mojokerto | 1 | 085765432123 | P | Perempuan

17 | runi | bali | 1 | 0857789012 | P | Perempuan

12 | edi | mojokerto | 3 | 985245678213 | P | Perempuan

(7 rows)

ayu13650039=> select nim_mah, nama_mah, alamat_mah, id_fak, no_tlp, case when ge

nder = 'L' then 'Laki-laki' else 'Perempuan' end as jenis_Kelamin from mahasiswa

;

---------+----------+------------+--------+--------------+---------------

14 | luki | ponorogo | 2 | 08257656879 | Laki-laki

11 | Sinta | malang | 1 | 0856789012 | Perempuan

13 | Wana | kediri | 4 | 085345673645 | Perempuan

15 | adi | malang | 1 | 089873654353 | Laki-laki

16 | Egi | mojokerto | 1 | 085765432123 | Perempuan

17 | runi | bali | 1 | 0857789012 | Perempuan

12 | edi | mojokerto | 3 | 985245678213 | Perempuan

(7 rows)

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

nim, nama mahasiswa, nama fakultas.

ayu13650039=> create view mahFak as select nim_mah, nama_mah, nama_fak from maha

siswa m, fakultas f where f.id_fak=m.id_fak;

CREATE VIEW

ayu13650039=> select * from mahFak;

nim_mah | nama_mah | nama_fak

---------+----------+-----------

14 | luki | Psikologi

11 | Sinta | SAINTEK

13 | Wana | Tarbiyah

15 | adi | SAINTEK

16 | Egi | SAINTEK

17 | runi | SAINTEK

12 | edi | humaniora

(7 rows)

ayu13650039=> select * from mahFak order by

nim_mah asc;

nim_mah | nama_mah | nama_fak

---------+----------+-----------

11 | Sinta | SAINTEK

12 | edi | humaniora

13 | Wana | Tarbiyah

14 | luki | Psikologi

15 | adi | SAINTEK

16 | Egi | SAINTEK

17 | runi | SAINTEK

(7 rows)

ayu13650039=>

Pekerjaan rumah

Menggunakan CMD mysql pada XAMPP

Microsoft Windows [Version 6.1.7600]

C:\Users\Ayu>cd..

C:\Users>cd..

C:\>cd xampp\x\mysql\bin

C:\xampp\x\mysql\bin>mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1

Server version: 5.6.20 MySQL Community Server (GPL)

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use ayu13650039;

Database changed

mysql> select * from mahasiswa;

+---------+----------+------------+--------+--------------+--------+

+---------+----------+------------+--------+--------------+--------+

| 11 | Sinta | malang | 1 | 085672837462 | P |

| 12 | binta | mojokerto | 3 | 092837480912 | P |

| 13 | wana | kediri | 4 | 098765432123 | P |

| 14 | luki | ponorogo | 2 | 08257656879 | L |

| 15 | adi | malang | 1 | 053647586978 | L |

| 16 | Egi | mojokerto | 1 | 085726354766 | P |

| 17 | runi | bali | 1 | 087736452623 | L |

+---------+----------+------------+--------+--------------+--------+

7 rows in set (0.16 sec)

mysql> update mahasiswa set nama_mah = 'edi' where nim_mah = 12;

Query OK, 1 row affected (0.13 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from mahasiswa;

+---------+----------+------------+--------+--------------+--------+

+---------+----------+------------+--------+--------------+--------+

| 11 | Sinta | malang | 1 | 085672837462 | P |

| 12 | edi | mojokerto | 3 | 092837480912 | P |

| 13 | wana | kediri | 4 | 098765432123 | P |

| 14 | luki | ponorogo | 2 | 08257656879 | L |

| 15 | adi | malang | 1 | 053647586978 | L |

| 16 | Egi | mojokerto | 1 | 085726354766 | P |

| 17 | runi | bali | 1 | 087736452623 | L |

+---------+----------+------------+--------+--------------+--------+

7 rows in set (0.00 sec)

mysql> select count (*) from mahasiswa;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near '*) fr

om mahasiswa' at line 1

mysql> Select count(*) from mahasiswa;

+----------+

| count(*) |

+----------+

| 7 |

+----------+

1 row in set (0.06 sec)

mysql> Select max (nim_mah) from mahasiswa;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near '*) fr

om mahasiswa' at line 1

mysql> Select max(nim_mah) from mahasiswa;

+--------------+

| max(nim_mah) |

+--------------+

| 17 |

+--------------+

1 row in set (0.00 sec)

mysql> Select min(nim_mah) from mahasiswa;

+--------------+

| min(nim_mah) |

+--------------+

| 11 |

+--------------+

1 row in set (0.00 sec)

mysql> Select avg(nim_mah) from mahasiswa;

+--------------+

| avg(nim_mah) |

+--------------+

| 14.0000 |

+--------------+

1 row in set (0.00 sec)

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

mysql> Select avg(nim_mah) from mahasiswa where nim_mah > 12;

+--------------+

| avg(nim_mah) |

+--------------+

| 15.0000 |

+--------------+

1 row in set (0.00 sec)

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

mysql> Select nama_fak, count(*) from fakultas f, mahasiswa m where f.id_fak = m

.id_fak group by nama_fak;

+-----------+----------+

| nama_fak | count(*) |

+-----------+----------+

| Humaniora | 1 |

| Psikologi | 1 |

| SAINTEK | 4 |

| Tarbiyah | 1 |

+-----------+----------+

4 rows in set (0.15 sec)

4. Tampilkan seperti nomor 3 dengan persyaratan jumlah mahasiswa yang lebih dari

sama dengan 2 saja yang ditampilkan.

mysql> Select nama_fak, count(*) from fakultas f, mahasiswa m where f.id_fak = m

.id_fak group by nama_fak having count(nama_fak) >= 2;

+----------+----------+

| nama_fak | count(*) |

+----------+----------+

| SAINTEK | 4 |

+----------+----------+

1 row in set (0.06 sec)

5. Tampilkan data mahasiswa dengan persyaratan, jika jenis kelaminnya “L” maka

tertulis laki-laki dan bila “P” maka tertulis perempuan.

mysql> select *, case when gender = 'L' then 'Laki-laki' else 'Perempuan' end as

jenis_kelamin from mahasiswa;

+---------+----------+------------+--------+--------------+--------+---------------+

in |

+---------+----------+------------+--------+--------------+--------+---------------+

| 11 | Sinta | malang | 1 | 085672837462 | P | Perempuan|

| 12 | edi | mojokerto | 3 | 092837480912 | P | Perempuan|

| 13 | wana | kediri | 4 | 098765432123 | P | Perempuan|

| 14 | luki | ponorogo | 2 | 08257656879 | L | Laki-laki|

| 15 | adi | malang | 1 | 053647586978 | L | Laki-laki|

| 16 | Egi | mojokerto | 1 | 085726354766 | P | Perempuan|

| 17 | runi | bali | 1 | 087736452623 | L | Laki-laki|

+---------+----------+------------+--------+--------------+--------+---------------+

7 rows in set (0.01 sec)

mysql> select nim_mah, nama_mah, alamat_mah, id_fak, no_tlp, case when gender =

'L' then 'Laki-laki' else 'Perempuan' end as jenis_kelamin from mahasiswa;

+---------+----------+------------+--------+--------------+---------------+

+---------+----------+------------+--------+--------------+---------------+

| 11 | Sinta | malang | 1 | 085672837462 | Perempuan |

| 12 | edi | mojokerto | 3 | 092837480912 | Perempuan |

| 13 | wana | kediri | 4 | 098765432123 | Perempuan |

| 14 | luki | ponorogo | 2 | 08257656879 | Laki-laki |

| 15 | adi | malang | 1 | 053647586978 | Laki-laki |

| 16 | Egi | mojokerto | 1 | 085726354766 | Perempuan |

| 17 | runi | bali | 1 | 087736452623 | Laki-laki |

+---------+----------+------------+--------+--------------+---------------+

7 rows in set (0.00 sec)

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

nim, nama mahasiswa, nama fakultas.

mysql> create view mahFak as select nim_mah, nama_mah, nama_fak from mahasiswa m

, fakultas f where f.id_fak=m.id_fak;

Query OK, 0 rows affected (0.24 sec)

mysql> select * from mahFak;

+---------+----------+-----------+

| nim_mah | nama_mah | nama_fak |

+---------+----------+-----------+

| 11 | Sinta | SAINTEK |

| 15 | adi | SAINTEK |

| 16 | Egi | SAINTEK |

| 17 | runi | SAINTEK |

| 14 | luki | Psikologi |

| 12 | edi | Humaniora |

| 13 | wana | Tarbiyah |

+---------+----------+-----------+

7 rows in set (0.06 sec)

mysql> select * from mahFak order by nim_mah asc;

+---------+----------+-----------+

| nim_mah | nama_mah | nama_fak |

+---------+----------+-----------+

| 11 | Sinta | SAINTEK |

| 12 | edi | Humaniora |

| 13 | wana | Tarbiyah |

| 14 | luki | Psikologi |

| 15 | adi | SAINTEK |

| 16 | Egi | SAINTEK |

| 17 | runi | SAINTEK |

+---------+----------+-----------+

7 rows in set (0.03 sec)

mysql>

Perbandingan Antara SQL Shell (psql) dan CMD mysql pada XAMPP

(Evaluasi perbedaan kelebihan dan kemudahan dari kedua DBMS)

Pada perbandingan kali ini, setelah melakukan praktium dan mengamati dari hasil praktikum menggunakan Postgress SQL dan pada hasil pekerjaan rumah menggunakan CMD mysql XAMPP, semua query yang ada pada PSQL dan CMD Mysql itu sama, mulai dari mengetahui banyak data menggunakan COUNT (*), mencari nilai paling kecil menggunakan MIN ( ), mencari nilai paling besar menggunakan MAX ( ), mencari rata-rata menggunakan avg ( ), menampilkan data dengan syarat tertentu menggunakan where, group by, having, case dan view.

Tetapi kecil sekali perbedaan pada query COUNT(*), MIN(), MAX() dan AVG(). Perbedaannya sangat remeh tapi saya tegaskan lagi itu akan menjadikan Error. Pada PSQL, query bisa menggunakan ‘space’ atau spasi untuk memisahkan perintah dan tanda kurung buka dan kurung tutup -()-, tetapi pada CMD Mysql jika ada space maka akan Error. Berikut buktinya :

Pada PostgreSQL

ayu13650039=> select count (*) from mahasiswa;

count

-------

(1 row)

ayu13650039=> select max (nim_mah) from mahasiswa;

max

-----

(1 row)

ayu13650039=> select min (nim_mah) from mahasiswa;

min

-----

(1 row)

ayu13650039=> select avg (nim_mah) from mahasiswa;

avg

---------------------

14.0000000000000000

(1 row)

Pada CMD Mysql XAMPP

mysql> select count (*) from mahasiswa;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near '*) from mahasiswa' at line 1

mysql> Select count(*) from mahasiswa;

+----------+

| count(*) |

+----------+

| 7 |

+----------+

1 row in set (0.06 sec)

mysql> Select max (nim_mah) from mahasiswa;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near '*) from mahasiswa' at line 1

mysql> Select max(nim_mah) from mahasiswa;

+--------------+

| max(nim_mah) |

+--------------+

| 17 |

+--------------+

1 row in set (0.00 sec)

mysql> Select min (nim_mah) from mahasiswa;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near '*) from mahasiswa' at line 1

mysql> Select min(nim_mah) from mahasiswa;

+--------------+

| min(nim_mah) |

+--------------+

| 11 |

+--------------+

1 row in set (0.00 sec)

mysql> Select avg (nim_mah) from mahasiswa;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that

corresponds to your MySQL server version for the right syntax to use near '*) from mahasiswa' at line 1

mysql> Select avg(nim_mah) from mahasiswa;

+--------------+

| avg(nim_mah) |

+--------------+

| 14.0000 |

+--------------+

1 row in set (0.00 sec)

Kesimpulan

Kesimpulan dari hasil praktikum dengan PSQL dan hasil pekerjaan rumah dengan CMD Mysql kali ini yaitu : semua query pada materi Agregasi SQL dan view antara PSQL dan CMD Mysql adalah sama. Yang sedikit membedakan adalah ‘space/spasi’ pada query agregasi/ operator agregasi (count (*), min(), max(), avg()) yang mana jika pada PSQL menggunakan space tersebut tidak akan Error dan jika pada CMD Mysql menggunakan space maka akan Error.

Kritik /Saran

Pada laporan kali ini saya menyadari bahwa masih banyak kekurangannya, maka dari itu saya pribadi memohon kritik dan saran dari para pembaca untuk membuat blog ini semakin berkembang.

Sedikit saran bagi para pembaca untuk lebih teliti saat mengetik query karena kesalahan kecil itu tidak bisa diremehkan karena akan membuat query tersebut Error. Tak lupa dan tidak akan pernah bosan saya mengingatkan untuk berdoa dahulu setelah dan sebelum mengerjaka supaya berhasil, sukses dan selamat.

Terakhir, Semoga bermanfaat.. ^_^

Music

Study IT

Selasa, 21 Oktober 2014

PRAKTIKUM DESAIN BASIS DATA 5