Praktikum 5: AGREGASI SQL DAN VIEW

          Assalamu’alaikum sadayana, kumaha damang ?  aku harap pada sehat-sehat yaa :D

Kita jumpa lagi dalam pembahasan laporan praktikum ku. Kali ini praktikumku membahas tentang “AGREGASI SQL DAN VIEW”. Eits, jangan beranjak dulu, semuanya akan kita bahas bareng kok. Tetep semangat belajar ya temen-temen :D:D:D

Yang pertama, apasih yang dimaksud agregasi, view dan apa saja yang termasuk di dalamnya? Yuk mari cekidooot ------------

      A. Landasan Teori

1.    Agregasi

Fungsi agregasi digunakan untuk melakukan operasi pada kelompok-kelompok baris data. Agregasi dapat digabungkan dengan sebuah parameter seperti WHERE untuk menghasilkan suatu hasil yang lebih kompleks lagi.

Yang termasuk fungsi-fungsi agregasi adalah :

Ø  Average (AVG): digunakan untuk memperoleh nilai rata-rata dari seluruh nilai pada suatu kolom. Pemakaian fungsi Avg: SELECT AVG nama_kolom FROM nama_tabel;

Ø  Minimum (MIN): dugunakan untuk memperoleh nilai minimum (yang terkecil) dari suatu kolom). Pemakaian fungsi Min : SELECT MIN nama_kolom FROM nama_tabel;

Ø  Maximum (MAX): digunakan untuk memperoleh nilai maksimum (yang beesar) dari suatu kolom. Pemakaian fungsi max: SELECT MAX nama_kolom FROM nama_tabel;

Ø  Total (SUM): digunakan untuk memperoleh nilai penjumlahan seluruh baris pada suatu kolom. Pemakaian fungsi Sum: SELECT SUM nama_kolom FROM nama_tabel;

Ø  Count (COUNT): sering digunakan untuk menghitung jumlah tuple dalam suatu relasi. Pemakaian fungsi Count: SELECT COUNT nama_kolom FROM nama_tabel;

2.    Grup By

Group By merupakan fungsi yang digunakan untuk melakukan pengelompokan dari perintah SELECT. Group by seringkali diperlukan untuk menjalankan agregasi menjadi sebuah kelompok dari hasil Query. Berikut struktur SQL untuk penampilan data: SELECT nama_kolom FROM nama_tabel GROUP BY nama_kolom;

3.    Having

Jika suatu fungsi diterapkan pada grup by yang telah dibentuk, maka digunakan kalusa having setelah group by dan kelompok yang memenuhi kondisi saja yang akan di hasilkan. Query nya adalah : SELECT nama_kolom FROM nama_tabel GROUP BY nama_kolom HAVING kondisi;

4.    Case

Dalam prosesnya, SQL dapat bebas mengontrol data yang kembali dari query, kata WHERE menggunakan perbandingan untuk mengontrol pemilihan data, sedangkan CASE perbandingan dalam bentuk output kolom. Jadi penggunaan CASE akan membentuk output berupa sebuah kolom baru dengan data dari operasi yang di dalamnya. Struktur query dalam select sepertiberikut: CASE WHEN kondisi THEN hasil END;

5.    View

View dapat disebut tabel banyangan tetapi bukan temporary table,  bukan juga merupakan tabel yang asli. Suatu view adalah suatu relasi virtual yang tidak perlu ada database tetapi dapat dibuat atas permintaan pemakai. View juga dapat menyimpan perintah query, dan dapat mewakili sebuah subset dari tabel asli dan memilih kolom atau row tertentu dari tabel biasa. Berikut struktur query nya: CREATE VIEW nama_tabel_view as query;

      B.  Hasil Praktikum

          Oke fix, sekarang cuss ke soalnya yaa.. kali ini langsung aja kita bahas soalnya dan disertai gambar dari DBMS Potgre dan MySql.

1.    Dari tabel mahasiswa yang telah dibuat tampilkan banyaknya data mahasiswa yang telah di inputkan. Kemudian cari nim atau id mahasiswa yang paling kecil, paling besar dan rata-ratanya.

Untuk menampilkan banyaknya data mahasiswa:

 SELECT count(*) from mahasiswa;

Untuk menampilkan nim mahasiswa yang paling kecil:

SELECT min(nim_mah) from mahasiswa;

Utnuk menampilkan nim mahasiswa yang paling besar:

 SELECT max(nim_mah) from mahasiswa;

Untuk menampilkan rata-rata dari nim mahasiswa:

SELECT avg(nim_mah) from mahasiswa;

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

SELECT avg(nim_mah) from mahasiswa where nim_mah > 14;

3.    Tampilkan jumlah mahasiswa berdasarkan fakultas:

SELECT nama_fak, count(*) from mahasiswa, fakultas where mahasiswa.id_fak=fakultas.id_fak group by nama_fak;

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

SELECT nama_fak, count(*) from mahasiswa, fakultas where mahasiswa.id_fak=fakultas.id_fak group by nama_fak having count(nama_mah) > 3;

5.    Tampilkan jenis kelamin “L” menjadi “Laki-laki” dan “P” menjadi “Perempuan” pada tabel mahasiswa:

SELECT nim_mah, nama_mah, alamat_mah, id_fak, no_telp, gender, case when gender=’L’ then ‘Laki-laki’ else ‘Perempuan’ end as jenis_kelamin from mahasiswa;

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

CREATE VIEW mahasiswa_baru as select nim_mah, nama_mah, nama_fak from mahasiswa, fakultas where mahasiswa.id_fak=fakultas.id_fak;

 C. Perbandingan dan Evaluasi 2 DBMS

Setelah menjelaskan teori dan membahas semua soalnya, sekarang kita sampai pada perbandingan kedua DBMS yaitu PostgreSQL dan MySql. Apasih yang berbeda dari keduanya? Hmm Alhamdulillah, kali ini keduanya cukup rukun, tidak ada perbedaan query dan perintah, semua query nya sama dari awal sampai akhir.

      D. Kesimpulan dan Penutup

Oke aku udah selesai nih, ga susah kan membuat AGREGASI SQL DAN VIEW? Mudah banget kok, ingat-ingat lagi yaa

Count: untuk menjumlahkan baris

Min: untuk mencari nilai minimum

Max: untuk mencari nilai maksimum

Avg: untuk mencari rata-rata

Group by: mengelompokkan suatu kolom

Having: group by yang diberikan suatu kondisi, jadi kolom tertentu saja yang ditampilkan

Case: menampilkan kolom baru, yang dibentuk dari kolom yang sudah ada

View: menampilkan tabel baru, yang bukan temporary table dan bisa ditampilkan kembali.

          Baiklah, sampai sini dulu yaa, kita jumpa lagi di sesi selanjutnya. Simak dan kunjungi terus blog ku buat nambah-nambah ilmu, silakan juga komentar atau bertanya apabila ada yang kurang jelas. Wassalamu’alaykum :)

      E.  Daftar Pustaka

http://agusheryantino.files.wordpress.com/2011/06/sistem-basis-data-i-bag9b-sql2.pdf

Musthafa, Aziz. 2014. Modul Praktikum Desain Basis Data. Fakultas Sains Dan Teknologi UIN Malang.

Solichin, Achmad. 2010. MySQL dari Pemula Hingga Mahir diakses melalui http://achmatim.net/download/21/

[ Read More ]

Praktikum 4: PENGENALAN OPERATOR DASAR

          Assalamu’alaykum saudara, bagaimana akhir pekan kalian ? Jangan tanya balik, karna tentu saja aku sibuk buat laporan praktikum ini :D

Baiklah pada pertemuan kali ini, aku bakalan bahas praktikum ku yang kemarin, tentang “PENGENALAN OPERATOR DASAR”, udah tau belum apa aja sih operator dasar yang sering digunakan pada saat kita membuat database di mysql ?

Gampang kok, langsung aja kita simak pembahasan ku yaa (^_^)

A.   Landasan Teori

1.     Operator AS

As atau alias biasa digunakan untuk menampilkan label kolom dengan nama lain sehingga akan muncul dalam query bukan nama asli kolom, tetapi nama yang mungkin lebih sesuai dan mudah dimengerti. Jadi yang akan terlihat seperti nama sementara yang dimunculkan, tanpa mengubah nama asli dari kolom. Nama panggilan kolom beserta nama kolom tersebut sama-sama dapat diakses. Berikut query nya:

Select nama_kolom as nama_kolom_pengganti from nama_tabel;

2.    Operator AND dan OR

AND digunakan untuk melakukan pencarian dan menampilkan data  yang lebih akurat. Dengan operator AND akan ditampikan data yang memenuhi kedua syarat yang ditentukan. Sedangkan operator OR hanya menampilkan data yang hanya memenuhi salah satu dari kedua syarat yang ditentukan.  SQL menggunakan standar logika boolean three-valued seperti pada tabel berikut ;

Berikut struktur SQL nya

Select * from nama_tabel where nama_kolom = ’pencarian1’ and nama_kolom = ’pencarian2’;

Select * from nama_tabel where nama_kolom=’pencarian1’ or nama_kolom = ’pencarian2’;

3.    Operator Between

Dalam operator ini, terjadi pembatasan pada data yang ditampilkan. Data diberi batasan pada nilai terendah dan tertinggi. Pada between menggunakan operator pembanding seperti 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.    Operator IN dan NOT IN

Operator ini berguna melakukan pencocokan dengan salah satu yang ada pada suatu daftar nilai. Berikut query nya

Select * from nama-tabel where nama_kolom in (kata_kunci1, kata_kunci2, kata_kunci3);

5.    Operator LIKE

Operator ini menggunakan metode pencarian berdasarkan huruf awal atau huruf akhir atau kombinasi kedua data yang diinginkan. Berikut adalah tabel contoh kasus penggunaan LIKE:

Dan berikut sintax nya:

select * from nama_tabel where nama_kolom like ‘operator’;

6.    Operator REGEXP

Regular Expresion atau Regex, merupakan sebuah teknik atau cara untuk mencari pwrsamaan-persamaan string atau data dan memanipulasinya. Biasanya sering digunakan string. Di dalam postgreSQL disimbolkan dengan “~”, sedangkan di mysql ditulis “regexp”. Berikut daftar symbol pada regular expressions:

Berikut struktur SQL untuk pencarian data menggunakan REGEXP :

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

7.    Operator DISTINCT

Operator ini digunakan untuk menampilkan data tanpa duplikasi data pada suatu kolom, jika terdapat duplikas data maka yang ditampilkan hanya satu data saja. Berikut struktur perintah dasar SQL :

select distinct nama_kolom from nama_tabel;

8.    Operator LIMIT dan OFFSET

LIMIT digunakan untuk membatasi jumlah data yang boleh ditampilkan, sedangkan OFFSET kebalikannya, dia akan menghilangkan jumlah data yang akan ditampilkan dan menampilkan sisa dari data yang dihilangkan. Berikut sintax nya:

Select * from nama_tabel limit jumlah_data_yang_ditampilkan;

Select * from nama_tabel offset jumlah_data_yang_ditampilkan;

9.    Operator UNION, EXCEPT dan INTERSECT

Hasil dari dua buah query dapat dikombinasikan dengan menggunakan union, except dan intersect. Union digunakan untuk menggabungkan hasil dari dua buah query menjadi satu kolom. Berikut struktur query nya:

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 query nya:

select nama_kolom from nama_tabel except select nama_kolom from nama_tabel;

          Perintah intersect hanya akan menampilkan seluruh isi dari data yang memiliki kesamaan diantara hasil kedua query tersebut. Berikut struktur query nya:

select nama_kolom from nama_tabel except select nama_kolom from nama_tabel;

B.  Hasil Praktikum

Tugas Praktikum

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

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 :

a. Tabel organ_dalam

b. Tabel organ_luar 

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

Berikut ini adalah hasil praktikum ku, yaitu jawaban dari pertanyaan-pertanyaan di atas. Dalam hasil praktikum nanti, aku memakai 2 DBMS yang berbeda, yaitu sql shell dari postrgreSQL dan xampp shell dari mysql. Oke simak yaa J

Yang pertama mengunakan postgresql, dan diteruskan dengan mysql

1.     Berdasarkan tabel sebelumnya yaitu bab 3, tambahkan dulu kolom “gender” 

kemudian update data mahasiswa, input kan 7 data mahasiswa di tabel tersebut, dengan menuliskan query update mahasiswa set gender=’L’ where nim_mah=14;

Kemudian kita tampilkan data mahasiswa dengan nama dari kolomnya berubah menjadi nomor_identitas, nama, alamat,nama_fakultas tanpa harus merubah nama kolom secara permanen, menggunakan operator AS , yaitu select nim_mah as nomor_identitas, nama_mah as nama, alamat_mah as alamat, nama_fak as nama_fakultas from mahasiswa, fakultas where mahasiswa.id_fak=fakultas.id_fak;

2.    Untuk menampilkan nama mahasiswa fakultas saintek yang berjenis kelamin laki-laki digunakan sintax select * from mahasiswa where id_fak=65 and gender='L';

Kemudian nama mahasiswa fakultas saintek atau berjenis kelamin laki-laki menggunakan sintax select * from mahasiswa where id_fak=65 or gender='L';

3.    Menampilkan data mahasiswa dari nomor 3 sampai dengan 5, menggunakan between select * from mahasiswa where nim_mah between 14 and 16; dan bukan between select * from mahasiswa where nim_mah>=14 and nim_mah<=16;

Menampilkan data mahasiswa yang bukan dari nomor 3 sampai dengan menggunakan not between select * from mahasiswa where nim_mah not between 14 and16; dan bukan not between select * from mahasiswa where nim-mah <14 or nim_mah>16;

4.    Menampilkan mahasiswa dengan nomor 2,3,5 menggunakan operator IN, query nya adalah  select * from mahasiswa where nim_mah in (15,16,18);

5.    Menampilkan semua data mahasiswa yang namanya mempunyai awalan huruf “a”, dengan menggunakan sintax select * from mahasiswa where nama_mah like ‘%a’;

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

select * from mahasiswa where nama_mah like ‘__n%a’;

7.    Menampilkan nomor fakultas di tabel mahasiswa dengan nomor harus tidak ada yang sama dari baris pertama sampai akhir, pada soal ini menggunakan query seperti berikut

Select distinct id_fak from mahasiswa;

8.    Menampilkan data mahasiswa 3 baris saja, menggunakan query :

Select * from mahasiswa limit 3;

9.    Menampilkan 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, pada postgreSQL menggunakan query

Select * from mahasiswa where nama ~’a[dt]’;

Query yang digunakan pada postgreSQL berbeda dengan pada mysql, yaitu

Select * from mahasiswa where nama_mah regexp ‘a[dt]’;

10. Membuat tabel organ_dalam

Create table organ_dalam (nomor integer not null, nama varchar(15);

 

Kemudian masukkan data pada tabel organ_dalam

Insert into organ_dalam values (1,’jantung’),(2,’paru-paru), (3,’tenggorokan’);

Buat tabel organ_luar, dan masukkan datanya

create table organ_luar (nomor integer not null, nama varchar (15));

insert into organ_luar values (1,’hidung’), (2,’mata’), (3,’tenggorokan’);

Union

Select * from organ_dalam union select * from organ_luar;

Except

Select * from organ_dalam except select * from organ_luar;

Terdapat perbedaan query pada mysql , yaitu

Select * from organ_dalam where (nomor, nama) not in ( select* from organ_luar);

Intersect

Select * from organ_dalam intersect select * from organ_luar;

Berbeda pula query di mysql, menggunakan

Select * from organ_dalam where (nomor, nama) in (select * from organ_luar);

C.  Perbandingan dan Evaluasi 2 DBMS

Setelah melakukan percobaan tadi pada 2 DBMS yang berbeda, dapat kita ketahui bahwa hampir semua query yang ada pada postgreSQL sama dengan query tang digunakan di mysql. Hanya saja ada beberapa yang berbeda, seperti

1.     Pada operator regexp, di postgresql, regexp disimbolkan dengan tanda “~”

Select * from mahasiswa where nama ~’a[dt]’;

Sedangkan pada mysql tidak dapat membaca tanda tersebut, jadi digunakan query seperti berikut:

Select * from mahasiswa where nama_mah regexp ‘a[dt]’;

2.    pada operator except, di postgresql dituliskan query nya menggunakan “except”

Select * from organ_dalam except select * from organ_luar;

Dan pada mysql menggunakan query “not in”

Select * from organ_dalam where (nomor, nama) not in ( select* from organ_luar);

3.    pada operator intersect, di postgresql dituliskan query nya menggunakan “intersect”

Select * from organ_dalam intersect select * from organ_luar;

Dan pada mysql menggunakan query “in”

Select * from organ_dalam where (nomor, nama) in ( select* from organ_luar);

D.  Kesimpulan dan Penutup

Operator AS : untuk menampilkan label kolom dengan nama lain.

Operator BETWEEN : untuk menentukan banyaknya data dengan rentang nilai tertentu

Operator LIKE  : digunakan untuk mencari suatu data yang terdiri dari satu karakteristik satu huruf atau lebih

Operator DISTINCT : untuk menampilkan data, dan menghindarkan dari munculnya data yang lebih dari satu

Operator LIMIT dan OFFSET : limit untuk membatasi tampilnya data, sedangkan offset untuk menampilkan sisa dari limit.

Operator UNION : untuk menggabungkan hasil dua buah query menjadi satu kolom

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

Operator INTERSECT hanya akan menampilkan seluruh isi dari data yang memiliki kesaman diantara hasil kedua query tersebut.

Semoga apa yang telah ditulis di blog ini, menjadi bermanfaat buat pembaca pada umumnya, dan penulis khususnya. Banyak sekali kekurangan dalam pembuatan dan penyelesain laporan prakitkum ini, untuk kritik dan saran yang membangun, kami tunggu dari kalian semua agar menjadi lebih baik lagi. Baiklah sampai di sini dulu pembahasan kita tentang “PENGENALAN OPERATOR DASAR”, nantikan laporan berikutnya yang tentunya tak kalah menarik dari ini. Wassalamu’alaykum J

E.  Daftar Pustaka

Musthafa, Aziz. 2014. Modul Praktikum Desain Basis Data. Fakultas Sains Dan Teknologi UIN Malang.

http://faculty.utpa.edu/lianx/CSCI4333_2014fall/MySQL-set-operators.pdf

http://uyunoshigoto.wordpress.com/2012/12/29/pengenalan-sql/

[ Read More ]