MODUL 8 STRUCTURED QUERY LANGUAGE (BAGIAN 2)
STRUKTUR DASAR 1.
Klausa Select Perintah SQL untuk NIM dan nama mahasiswa yang ada di tabel Mahasiswa, maka perintah SQL berikut ini : Select nim, nama_mhs From mahasiswa Sementara, perintah berikut ini : Select nama_mhs From mahasiswa
Apa bentuk tampilan dari klausa dibawah : Select nim, nama_mhs as nama Alamat_mhs as alamat From mahasiswa Apa bentuk tampilan dari klausa dibawah : Select * From mahasiswa 2. Klausa Where Berikut ini adalah contoh untuk menampilkan semua atribut untuk mahasiswa dengan NIM=’980002’: Select * From mahsiswa Where nim = ‘980002’ Apa tampilan dari perintah dibawah ini :
1
Select * From kuliah Where semester between 3 and 5 3. Klausa Form Ekspresi berikut menghasilkan table seperti apa : select * from kuliah, dosen where kuliah. kode_dos = dosen.kode_dos
Ekspresi berikut menghasilkan table seperti apa : apa ? : select * from kuliah k, dosen d where k.kode_dos = d.kode_dos Ekspresi berikut menghasilkan table seperti apa : select k.kode_kul, k.nama_kul, d.nama_dos from kuliah k, dosen d where k.kode_dos = d.kode_dos Ekspresi berikut menghasilkan table seperti apa : select k.kode_kul as kode, k.nama_kul as kuliah, d.nama_dos as dosen from kuliah k, dosen d where k.kode_dos = d.kode_dos and k.semester = 3 Ekspresi berikut menghasilkan table seperti apa : select n.nim, m.nama_mhs k.nama_kul, n.indeks_nilai from nilai n, mahasiswa m, kuliah k
2
where n.nim = m.nim and n.kode_kul = k.kode_kul F. Latihan 1. Buatlah tabel-tabel dibawah ini dengan menggunakan Mysql dan simpan dalam database DataMaster Tabel Mahasiswa Mhs
Nim
T_lahir
Jones
61521
05/12/77
Dendi
61300
28/04/78
Tomy
61425
01/11/75
Paula
61230
14/06/77
Tabel Mhs_Kul Nim
Kode_mk
Nilai
61521
Mat 231
B
61521
Eko 220
A
61521
Hst 211
B
61300
Hst 211
A
61425
Eng 202
C
61425
Mat 231
B
61230
Mat 231
D
61230
Eng 202
C
Tabel Matakuliah M_kuliah
Kode_mk
sks
Matematika
Mat 231
3
Ekonomi
Eko 220
3
Sejarah
Hst 211
2
b.inggris
Eng 202
2
Tabel Mutu Nilai
Bobot
3
A
4
B
3
C
2
D
1
E
0
a. Cari semua nim dan nama mahasiswa yang tidak lahir tgl 28/04/78 b. Tampilkan nilai & nim dari mahasiswa yang memperoleh nilai “D” c. Tampilkan nilai mahasiswa yang bobotnya diatas “dua” d. tampilkan mahasiswa yang mengambil matakuliah "Matematika" e. ganti mahasiwa yang bernama "Tomy" menjadi "Candra"
#Perintah INSERT Perintah INSERT digunakan untuk menambah data pada tabel. Sebenarnya ada berbagai macam kombinasi dari perintah insert ini. 3 macam model, yang sekiranya cukup bagi kita untuk berkiprah di dunia SQL. A. Model 1. Disini INSERT yang dipakai merupakan perintah standard Misalnya pada tabel tb_brg yang telah kita bikin pada pertemuan sebelumnya : insert into tb_brg(kode_brg, nama_brg) values(’1001′, ‘Buku’) insert into tb_brg(kode_brg, nama_brg) values(’1002′, ‘Pencil’) insert into tb_brg(kode_brg, nama_brg) values(’1003′, ‘Penggaris’) Jalankan ketiga perintah diatas maka tb_brg akan terisi dari nilai2 yang ada pada values. Untuk dapat melihat hasilnya bisa menjalankan perintah di bawah ini
4
select* from tb_brg maka akan terlihat tabel dibawah ini :
B. Model 2. Perintah INSERT disini lebih ringkas, tapi butuh hapal susunan field tabelnya kalau tidak bisa ketukar informasi yang diberikan antara kode_brg dengan nama_brg. Perintah INSERT ini juga bisa dengan model dibawah ini insert into tb_brg values(’1004′, ‘Pulpen’) Perintah seperti ini semua field harus terisi oleh values, tidak boleh tidak. C. Model 3. Perintah ini dikombinasikan dengan SELECT, bisa dengan kriteria yang ingin kita tentukan. Biasanya perintah ini untuk memindah data dari tabel1 ke tabel2 Mari kita coba : Buat tabel kedua seperti pada pertemuan sebelumnya create table tb_brg2(kode_brg char(15) primary key, nama_brg char(50)) maka terbentuk tabel dengan nama tb_brg2, kemudian isi tabel tb_brg2 kita isikan dengan tabel tb_brg dengan kriteria kode_brg >= ‘1002′, seperti yang dituliskan dibawah ini : insert into tb_brg2 select* from tb_brg where kode_brg >= ‘1002′
5
hasilnya dapat dilihat pakai perintah select* from tb_brg2.
Disini terlihat isi data tb_brg2 akan terisi dengan kode_brg >= ‘1002′ Perintah Model ke-3 ini sangat praktis apabila kita mengelola data dengan jumlah ribuan bahkan jutaan record. Akan diberikan beberapa contoh perintah SQL yang diimplementasikan pada multi tabel. Adapun perintah SQL untuk manipulasi data pada satu tabel, hal ini cukup mudah bagi Anda. Dalam contoh ini akan diambil studi kasus tentang pengambilan matakuliah mahasiswa. Tabel-tabel yang dibuat pada studi kasus ini cukup sederhana saja untuk memudahkan pemahaman. Adapun tabel-tabel tersebut adalah: mhs ( nim varchar(3), namaMhs varchar(30), primary key(nim) ) mk ( kodeMK varchar(3), namaMK varchar(30), sks integer, primary key(kodeMK) ) ambilMK ( nim varchar(3), kodeMK varchar(3), nilai integer,
6
primary key(nim, kodeMK) ) Dalam hal ini, field nim dan kodeMK pada tabel ambilMK merupakan foreign key. Apabila diperhatikan, tabel mhs dengan tabel ambilMK saling berelasi karena nim dalam tabel ambilMK berasal dari nim dalam tabel mhs (master tabel). Demikian pula antara tabel mk dengan ambilMK. Kedua tabel ini juga berelasi karena kodeMK dalam tabel ambilMK berasal dari kodeMK dalam tabel mk. Untuk record masing-masing tabel, misalkan diberikan berikut ini: Tabel : mhs nim
namaMhs
001
Joko
002
Amir
003
Budi
Tabel : mk kodeMK
namaMK
A01
Kalkulus
A02
Geometri
A03
Aljabar
sks 3 2 3
Tabel : ambilMK nim
kodeMK
nilai
001
A01
3
001
A02
4
001
A03
2
002
A02
3
002
A03
2
003
A01
4
003
A03
3
7
Selanjutnya misalkan akan dicari data-data sbb: 1. Tampilkan nim dan nama mahasiswa yang mengambil Kalkulus (kodeMK = A01) 2. Tampilkan nim, nama mahasiswa dan jumlah SKS matakuliah yang diambil untuk setiap mahasiswa 3. Berapakah IPK mahasiswa bernama Budi (NIM: 003) 4. Tampilkan nim, nama mahasiswa, dan IPK setiap mahasiswa Untuk menjawab no. 1, kita harus cari dulu tabel mana yang terkait dengan query tersebut. Apabila kita akan mencari query tersebut berdasarkan nama matakuliah ‘Kalkulus’ maka tabel yang terkait adalah mhs (untuk menampilkan nim dan nama mahasiswa), mk (karena dalam tabel ini terdapat nama matakuliah), serta tabel ambilMK (karena tabel ini berisi data pengambilan matakuliah oleh mahasiswa). Setelah menentukan tabel mana yang terkait dengan query, selanjutnya dapat dibuat statement SQL nya, yaitu SELECT mhs.nim, mhs.namaMhs FROM mhs, mk, ambilMK WHERE mhs.nim = ambilMK.nim AND mk.kodeMK = ambilMK.kodeMK AND mk.namaMK = 'Kalkulus'; Maksud mhs.nim, maksudnya adalah menampilkan data nim yang berasal dari tabel mhs. Dapat pula Anda menuliskan SQL nya seperti ini SELECT ambilMK.nim, mhs.namaMhs FROM mhs, mk, ambilMK WHERE mhs.nim = ambilMK.nim AND mk.kodeMK = ambilMK.kodeMK AND mk.namaMK = 'Kalkulus'; Hal ini dikarenakan untuk menampilkan nim dapat pula berasal dari tabel ambilMK. Perhatikan bagian FROM dari kedua statement SQL di atas. Nama-nama tabel yang terkait dengan query dituliskan pada bagian FROM ini.
8
Selanjutnya apa maksud dari perintah mhs.nim = ambilMK.nim? Perintah ini digunakan untuk merelasikan tabel mhs dan ambilMK, dimana kedua tabel direlasikan melalui field nim di kedua tabel. Hal yang sama juga berlaku untuk perintah mk.kodeMK = ambilMK.kodeMK. Sedangkan perintah mk.namaMK = ‘Kalkulus’ digunakan sebagai syarat pencarian data (menampilkan data mahasiswa yang mengambil matakuliah Kalkulus). Apabila pencarian data mahasiswa yang mengambil Kalkulus ini berdasarkan kode matakuliah (A01), maka Anda tidak perlu menggunakan tabel mk, tapi cukup tabel mhs dan ambilMK saja. Hal ini dikarenakan kodeMK dapat diketahui dari tabel ambilMK. Sehingga perintah SQL nya SELECT mhs.nim, mhs.namaMhs FROM mhs, ambilMK WHERE mhs.nim = ambilMK.nim AND ambilMK.kodeMK = 'A01'; Ketiga statement SQL di atas akan menghasilkan hasil yang sama yaitu NIM
namaMhs
001
Joko
003
Budi
Selanjutnya akan dijawab pertanyaan no. 2. Seperti halnya langkah penyelesaian pertanyaan no. 1, langkah pertama harus kita tentukan dulu tabel apa saja yang terkait dengan query. Dalam hal ini kita akan menggunakan tabel mhs, mk dan ambilMK. Tabel mhs untuk menampilkan nim dan nama mahasiswa. Tabel mk digunakan karena di dalamnya terdapat data SKS. Sedangkan tabel ambilMK digunakan karena berisi data yang menunjukkan pengambilan matakuliah mahasiswa. Perintah SQL untuk pertanyaan no. 2 adalah SELECT mhs.nim, mhs.namaMhs, sum(mk.sks) as jumlahSKS FROM mhs, mk, ambilMK WHERE mhs.nim = ambilMK.nim AND mk.kodeMK = ambilMK.kodeMK GROUP BY ambilMK.nim
9
atau SELECT mhs.nim, mhs.namaMhs, sum(mk.sks) as jumlahSKS FROM mhs, mk, ambilMK WHERE mhs.nim = ambilMK.nim AND mk.kodeMK = ambilMK.kodeMK GROUP BY mhs.nim Untuk mencari jumlah SKS setiap mahasiswa, kita menggunakan perintah sum(mk.sks). Supaya perintah ini bisa jalan, maka harus ditambahkan perintah GROUP BY mhs.nim atau GROUP BY ambilMK.nim. Hal ini dikarenakan proses penjumlahan sks harus dilakukan
pada
setiap
kelompok
data.
Maksudnya
apa
ya?
Perhatikan perintah SQL berikut ini SELECT mhs.nim, mhs.namaMhs, mk.sks FROM mhs, mk, ambilMK WHERE mhs.nim = ambilMK.nim AND mk.kodeMK = ambilMK.kodeMK Perintah di atas akan menampilkan nim, nama mahasiswa serta sks setiap mata kuliah yang diambil. Hasilnya adalah NIM
namaMhs
SKS
001
Joko
3
001
Joko
2
001
Joko
3
002
Amir
2
002
Amir
3
003
Budi
3
003
Budi
3
Untuk pertanyaan no. 2 ini, seharusnya akan tampil hasil berikut NIM
namaMhs
jumlahSKS
001
Joko
8
002
Amir
5
003
Budi
6
10
Sehingga supaya mendapatkan hasil seperti di atas, kita akan menjumlahkan setiap SKS yang diambil mahasiswa, berdasarkan kelompok mahasiswa, dalam hal ini dikelompokkan berdasarkan NIM. Mengapa tidak dikelompokkan berdasarkan nama mahasiswa? Wah bisa gawat kalau ini terjadi, karena ada kemungkinan
nama
mahasiswa yang sama. Oleh karena itu harus ada perintah GROUP BY mhs.nim Adapun pertanyaan no. 3 adalah bagaimana perintah SQL untuk menampilkan IPK dari mahasiswa berNIM ‘003′? Untuk membuat SQL dari query di atas, lagi-lagi langkah pertama adalah menentukan di tabel mana kita akan bekerja. Apabila Anda perhatikan, maka tabel yang dipilih adalah mk dan ambilMK. Mengapa demikian? Tabel mk digunakan untuk mengambil informasi terkait dengan sks matakuliah yang diambil mahasiswa dan tabel ambilMK berisi informasi daftar matakuliah yang diambil mahasiswa. Nah… , setelah Anda menentukan tabel yang digunakan, selanjutnya Anda harus tahu formula untuk menghitung IPK. Apa rumusnya? yaitu jumlah dari perkalian sks dan nilai yang diambil mahasiswa dibagi dengan jumlah sks yang diambil mahasiswa. OK begitu Anda tahu rumusnya, langsung dapat diimplementasikan ke SQL, yaitu SELECT sum(ambilMK.nilai * mk.sks)/sum(mk.sks) as IPK FROM ambilMK, mk WHERE ambilMK.kodeMK = mk.kodeMK AND ambilMK.nim = '003'; Dari SQL di atas, Anda akan memperoleh hasil IPK mhs bernim ‘003′ adalah 3.5 Hikmah dari contoh kasus pencarian IPK ini adalah, apabila Anda membuat sistem untuk pencatatan nilai siswa seperti sistem informasi akademik dan sejenisnya, sebaiknya data IP atau IPK jangan disimpan dalam tabel, melainkan melalui proses query. Apabila data IP atau IPK tersimpan ke dalam tabel, maka kemungkinan terjadi ketidakkonsistenan data semakin besar. Bisa jadi IP atau IPK yang tersimpan di tabel berbeda dengan kenyataan berdasarkan nilai-nilai matakuliah yang diperoleh siswa. Lagipula jika data-data IP dan IPK disimpan dalam tabel, maka hanya akan menambah penuh database Anda. Selanjutnya akan dibahas untuk pertanyaan no. 4, yaitu
11
bagaimana statement SQL untuk menampilkan nim, nama dan nilai IPK semua mahasiswa. Kita harus menggunakan semua tabel, yaitu mhs, mk dan ambilMK.Tabel mhs diperlukan karena untuk menampilkan nama mahasiswa, bisa juga untuk nim. Tapi nim juga dapat ditampilkan melalui tabel ambilMK. Bagaimana statement SQL nya? SELECT mhs.nim, mhs.namaMhs, sum(ambilMK.nilai * mk.sks)/sum(mk.sks) as IPK FROM mhs, ambilMK, mk WHERE mhs.nim = ambilMK.nim AND ambilMK.kodeMK = mk.kodeMK GROUP BY mhs.nim Mengapa harus ada perintah GROUP BY mhs.nim? Hal ini karena perhitungan IPK dapat dilakukan setelah data dikelompokkan berdasarkan nim, mengingat seorang mahasiswa dapat mengambil matakuliah lebih lebih dari satu.
===================
12
