..::Rozaq D' aCeweLL™::..

Tampilkan postingan dengan label SQL. Tampilkan semua postingan

Senin, 13 Juni 2011

Cara Mengubah Struktur Tabel dengan Query SQL

Dalam artikel ini, saya akan coba bahas dalam blog ini mengenai bagaimana cara mengubah struktur tabel menggunakan query SQL. Meskipun untuk mengubah struktur tabel, khususnya pada MySQL ini, dapat kita lakukan dengan mudah menggunakan tool atau interface seperti PhpMyAdmin atau Navicat atau yang lain, namun… sejenak kita lupakan tool-tool tersebut. Mengapa demikian? yap…. kita mungkin tidak akan menduga, ketika di suatu saat kita dipaksa tidak boleh menggunakan tool seperti di atas dan mau tidak mau harus menggunakan SQL command untuk mengatur administrasi database. Boleh saja kita menggunakan tool, namun basic commandsnya jangan sampai diabaikan. Bukan begitu mbak Elis? (salah satu member saya)

Kebetulan beliau ini sedang bingung karena ada tugas dari dosennya. Mudah-mudahan dari postingan artikel ini menjadi tidak bingung lagi

OK… lanjut…

Ada beberapa proses yang termasuk dalam pengubahan struktur dalam tabel, yaitu :

menambah field baru dalam suatu tabel,
mengubah nama field,
mengubah tipe data field,
membuat primary key
mengubah properti field,
menghapus primary key,

Nah.. akan kita bahas satu persatu proses di atas. Mmm… tapi enaknya kita buat dalam studi kasus saja ya.

Andaikan kita sebelumnya telah memiliki tabel dengan struktur sebagai berikut:

CREATE TABLE mhs
(
  nim varchar(8),
  nama varchar(30),
  tgllahir varchar(10)
)

Trus… nantinya kita akan ubah struktur tabel yang telah kita miliki di atas.

Menambah Field Baru

Sebagai contoh misalkan kita akan menambahkan field baru dengan nama ‘asalSekolah’ bertipe Varchar(20) sebagai field terakhir dari tabel, perintah SQL nya adalah

1.ALTER TABLE mhs ADD asalSekolah VARCHAR(20);

Anda juga dapat menyisipkan field ‘asalSekolah’ tersebut sebagai field pertama dalam tabel ‘mhs’ dengan perintah SQL sbb:

1.ALTER TABLE mhs ADD asalSekolah VARCHAR(20) FIRST;

Bagaimana bila kita ingin field ‘asalSekolah’ ini diletakkan setelah field ‘nama’? Gampang sekali caranya, cukup berikan perintah berikut ini

1.ALTER TABLE mhs ADD asalSekolah VARCHAR(20) AFTER nama;

Mengubah Nama Field

Sebagai contoh misalkan kita akan mengubah nama field ‘nama’ menjadi ‘namaMhs’. Perhatikan perintah SQL berikut ini

1.ALTER TABLE mhs CHANGE nama namaMhs VARCHAR(8);

Mengubah Tipe Data Field

Sebagai contoh misalkan kita akan mengubah tipe data dari field ‘tgllahir’ menjadi DATE, maka perintah SQL nya:

1.ALTER TABLE mhs CHANGE tgllahir tgllahir DATE;

Catatan:
Perhatikan perintah di atas bahwa field ‘tgllahir’ ditulis 2 kali. Hal ini dilakukan apabila tidak ada perubahan nama field.

Membuat Primary Key

Misalkan kita akan membuat field ‘nim’ sebagai kunci atau primary key nya. Maka perintahnya adalah:

1.ALTER TABLE mhs ADD PRIMARY KEY (nim);

Bagaimana bila kita ingin lebih dari satu field sebagai kuncinya sekaligus? Ya… mudah saja tinggal berikan perintah SQL seperti ini

1.ALTER TABLE namatabel ADD PRIMARY KEY (field1, field2, ...);

Mengubah Properti Field

Yang dimaksud mengubah properti field, misalnya kita menambahkan auto increment pada suatu field atau kita mengatur suatu field tidak boleh NULL (berisi data kosong). Sebagai contoh misalnya kita akan memberikan auto increment pada field ‘nim’ (meskipun NIM menggunakan auto increment itu tidak boleh). Ingat ya… untuk auto increment ini hanya dapat diberikan pada field yang bertipe INTEGER saja. Oleh karena itu ketika akan memberikan auto increment, maka sekaligus kita ubah tipe data ‘nim’ menjadi INTEGER. Satu lagi yang penting bahwa field yang akan diberikan auto increment haruslah berupa kunci (key). Oleh karena itu kita sebelumnya harus sudah memilih ‘nim’ sebagai kunci nya. Berikut ini adalah perintah SQL untuk memberikan auto increment pada ‘nim’ dengan asumsi field ini sudah menjadi kunci.

1.ALTER TABLE mhs CHANGE nim nim INT AUTO_INCREMENT;

Sedangkan berikut ini perintah SQL untuk menghapus auto increment dari yang sudah kita set di atas

1.ALTER TABLE mhs CHANGE nim nim INT;

Kemudian, bagaimana bila kita ingin supaya suatu field tidak boleh kosong (NULL) datanya atau dengan kata lain, data untuk field tersebut harus terisi? Mudah saja, kita hanya tambahkan properti NOT NULL pada field yang kita inginkan. Misalkan kita set supaya field ‘tgllahir’ tidak boleh kosong, perintah SQL nya adalah:

1.ALTER TABLE mhs CHANGE tgllahir tgllahir DATE NOT NULL;

Kebalikannya, bagaimana bila kita ingin mengembalikan ‘tgllahir’ ini memperbolehkan data kosong? Ya.. cukup ubah dari NOT NULL menjadi NULL.

1.ALTER TABLE mhs CHANGE tgllahir tgllahir DATE NULL;

Menghapus Primary Key

Mmm.. untuk menghapus primary key, kita hanya bisa menghapus semuanya (tidak satu persatu) apabila terdapat lebih dari satu primary key. Adapun perintah untuk menghapus primary key yang ada dalam tabel ‘mhs’ adalah:

1.ALTER TABLE mhs DROP PRIMARY KEY;

OK… mudah-mudahan artikel ini berguna bagi Anda.

Download Disini

Cara Membuat Foreign Key, Relasi Tabel dan Referential Integrity di phpMyAdmin

Menyambung artikel sebelumnya tentang perintah SQL untuk membuat foreign key, relasi tabel dan referential integrity di MySQL, pada artikel kali ini saya akan mencoba memaparkan bagaimana cara membuat ketiga hal tersebut langsung di phpMyAdmin tanpa menggunakan perintah SQL.

Sebagai contoh misalkan kita akan membuat database ‘perpustakaan’ yang di dalamnya terdapat 2 buah tabel yaitu ‘buku’ dan ‘kategori’.

Di dalam tabel ‘buku’ terdapat field: ‘id’ untuk menunjukkan nomor identitas buku, ‘title’ yang menunjukkan judul buku, ‘author’ sebagai pengarang, dan ‘idcategory’ yang menunjukkan nomor id kategori dari buku. ID Category buku ini nanti akan direlasikan dengan ID kategori yang terdapat dalam tabel ‘kategori’.

Adapun field yang ada dalam tabel ‘kategori’ adalah: ‘idcategory’ dan ‘namacategory’.

Dalam perancangan database, field ‘idcategory’ yang terdapat dalam tabel ‘buku’ dinamakan foreign key atau kunci tamu. Selain itu pada kedua tabel di atas juga terdapat relasi yaitu antara field ‘idcategory’ yang ada pada kedua tabel tersebut.

Nah… sekarang kita akan membuat foreign key, relasi tabel dan referential integrity untuk kedua tabel di atas langsung menggunakan phpMyAdmin.

Pertama, kita buat database ‘perpustakaan’ terlebih dahulu

Kedua, setelah database sudah dibuat, selanjutnya kita bisa membuat tabelnya. Kita akan membuat tabel ‘kategori’ terlebih dahulu

Berikutnya kita membuat field pada tabel ‘kategori’ Pada contoh ini, dimisalkan untuk field ‘idcategory’ kita buat sebagai auto increment.

Satu hal yang terpenting, pastikan bahwa tabel yang Anda buat ini harus bertipe ‘INNODB’. Karena hanya dengan tipe INNODB lah, kita bisa membuat foreign key, relasi dan referential integrity. Untuk membuat tabel bertipe INNODB, silakan pilih pada bagian ‘Storage Engines’ yang terdapat di bagian bawah panel membuat tabel.

Setelah diset field dan storage engine nya sebagai INNODB, barulah Anda bisa simpan tabel ‘kategori’ nya.

Tabel ‘kategori’ sudah sukses kita buat, selanjutnya kita bisa membuat tabel ‘buku’ nya. Adapun caranya hampir sama dengan sebelumnya.

Field-field tabel kita buat juga seperti tabel sebelumnya. Di sini diasumsikan bahwa field ‘idbuku’ kita gunakan auto increment).

Karena field ‘idcategory’ dalam tabel ‘buku’ akan kita jadikan foreign key, maka kita harus buat field tersebut sebagai INDEX. Perhatikan tanda lingkaran merah pada gambar berikut ini

Oya… jangan lupa jadikan tabel ‘buku’ sebagai INNODB dengan memilihnya pada bagian ‘Storage Engine’.

Langkah berikutnya adalah, kita mengeset relasi antara kedua tabel yang dihubungkan dengan field ‘idcategory’. Adapun caranya adalah, klik pada tabel ‘buku’ seperti gambar di bawah ini

Lalu, klik pada bagian ‘Relation View’ seperti tampak pada gambar berikut ini

Karena field ‘idcategory’ dalam tabel ‘buku’ ini akan direlasikan terhadap field ‘idcategory’ pada tabel ‘kategori’ (sebagai tabel master) maka selanjutnya kita set relasinya seperti gambar di bawah ini.

OK deh… selesai sudah cara membuat foreign key, relasi tabel dan referential integrity MySQL dengan phpMyAdmin. Dengan demikian, Anda tidak perlu susah-susah menghapalkan perintah SQL untuk membuat ketiga hal tersebut.

Silakan dicoba sendiri ya. Mudah-mudahan bermanfaat.

Download Disini

Cara Membuat Query SQL Untuk Relasi Tabel

Ngabuburit gini emang enak buat ngeblog, itung-itung sambil beramal dengan sharing ilmu

OK.. kali ini saya akan mencoba membahas bagaimana cara membuat query SQL yang melibatkan tabel lebih dari satu (multi tabel) dimana saling berelasi satu sama yang lain. Dalam hal ini, pembahasan hanya akan berfokus pada query berbentuk SELECT saja.

Untuk lebih jelasnya, saya berikan sampel 3 buah tabel yang saling berelasi satu sama lain beserta sampel datanya. Tabel ini merupakan studi kasus dari data perkuliahan mahasiswa.

CREATE TABLE `ambilmk` (
  `nim` varchar(5),
  `kodemk` varchar(4),
  `nilai` float,
  `smt` varchar(10),
  `thajar` varchar(10),
  PRIMARY KEY (`nim`,`kodemk`)
);

CREATE TABLE `mhs` (
  `nim` varchar(5),
  `namamhs` varchar(20),
  `alamat` text,
  `tgllhr` date,
  PRIMARY KEY (`nim`)
);

CREATE TABLE `mk` (
  `kodemk` varchar(4),
  `namamk` varchar(20),
  `sks` int(11),
  `smt` varchar(10),
  PRIMARY KEY (`kodemk`)
);

dan… berikut ini adalah sampel datanya

INSERT INTO `ambilmk` VALUES ('M001', 'MK01', '3', 'GANJIL', '2001-2002');
INSERT INTO `ambilmk` VALUES ('M001', 'MK02', '2', 'GANJIL', '2001-2002');
INSERT INTO `ambilmk` VALUES ('M002', 'MK01', '4', 'GANJIL', '2001-2002');
INSERT INTO `ambilmk` VALUES ('M003', 'MK03', '4', 'GENAP', '2001-2002');

INSERT INTO `mhs` VALUES ('M001', 'XXX', 'SOLO', '1990-10-01');
INSERT INTO `mhs` VALUES ('M002', 'YYY', 'SEMARANG', '1992-08-11');
INSERT INTO `mhs` VALUES ('M003', 'ZZZ', 'SOLO', '1991-04-15');

INSERT INTO `mk` VALUES ('MK01', 'KALKULUS I', '3', 'GANJIL');
INSERT INTO `mk` VALUES ('MK02', 'GEOMETRI', '2', 'GANJIL');
INSERT INTO `mk` VALUES ('MK03', 'KALKULUS II', '3', 'GENAP');

Berdasarkan ketiga tabel tersebut, kita bisa melihat relasi antar ketiga tabel tersebut sebagaimana tergambar pada gambar berikut ini

Nah… selanjutnya, misalkan kita ingin menampilkan data mahasiswa (nim, nama) dari mahasiswa yang pernah mengambil matakuliah ‘MK01′. Bagaimana cara membuat query SQL nya?

Sebelum kita mulai membuat query SQL nya, terlebih dahulu harus kita tentukan ‘Di tabel manakah kita akan bekerja‘ dari ketiga tabel tersebut. OK… untuk menampilkan nim dan nama, kita bisa menggunakan tabel ‘mhs’. Sedangkan untuk prasyarat dari query yaitu ‘mahasiswa yang pernah mengambil matakuliah MK01 kita menggunakan tabel ‘ambilmk’. Dengan demikian query yang akan kita buat hanya melibatkan kedua tabel tersebut, yaitu ‘mhs’ dan ‘ambilmk’.

Eh… masih ada lagi hal yang harus diperhatikan, yaitu relasi kedua tabel tersebut. Bila Anda lihat gambar relasi di atas atau hubungan antar ketiga tabel, tabel ‘ambilmk’ dan tabel ‘mhs’ direlasikan melalui field apa? Dari gambar, kita tahu bahwa kedua tabel terhubung melalui field ‘nim’ yang ada di tabel ‘mhs’ dan field ‘nim’ yang ada di tabel ‘ambilmk’. Oleh karena itu di dalam query SQL terutama pada bagian klausa WHERE perlu adanya perintah untuk menggambarkan hubungan tersebut.

Query SQL untuk menampilkan data mahasiswa (nim, nama) dari mahasiswa yang pernah mengambil matakuliah ‘MK01′ adalah sbb:

SELECT mhs.nim, mhs.namamhs
FROM mhs, ambilmk
WHERE mhs.nim = ambilmk.nim AND ambilmk.kodemk = 'MK01';

Perhatikan perintah “mhs.nim = ambilmk.nim” pada query di atas. Perintah tersebut digunakan untuk merelasikan tabel ‘mhs’ dengan ‘ambilmk’ dimana kedua tabel terhubung oleh field ‘nim’ yang ada di masing-masing tabel.

Sehingga hasil dari query SQL di atas adalah

Selanjutnya misalkan kita ingin menampilkan data mahasiswa (nim, nama) beserta nilainya dari mahasiswa yang pernah mengambil matakuliah bernama ‘KALKULUS I’. Bagaimana query SQL nya?

OK… langkah pertama, kita tentukan dulu tabel mana saja yang kita pilih. Untuk menampilkan nim dan nama, kita butuh tabel ‘mhs’. Nilai, kita butuh tabel ‘ambilmk’. Sedangkan nama matakuliah ‘KALKULUS I’ yang digunakan sebagai syarat, kita dapatkan dari tabel ‘mk’. Jadi untuk query ini, kita butuh ketiga tabel semuanya.

Setelah itu, kembali lagi kita lihat gambar relasi ketiga tabel di atas. Tabel ‘mhs’ dan ‘ambilmk’ dihubungkan dengan field ‘nim’ yang ada pada keduanya. Sedangkan tabel ‘mk’ dan ‘ambilmk’ terhubung oleh field ‘kodemk’ yang juga ada pada keduanya. Nah… selanjutnya kita bisa membuat query SQL nya

SELECT mhs.nim, mhs.namamhs, ambilmk.nilai
FROM mhs, ambilmk, mk
WHERE mhs.nim = ambilmk.nim AND mk.kodemk = ambilmk.kodemk AND mk.namamk = 'KALKULUS I';

Perhatikan query SQL di atas, perintah “mhs.nim = ambilmk.nim AND mk.kodemk = ambilmk.kodemk” menunjukkan relasi ketiga tabel. Gunakan operator AND bila relasi tabelnya lebih dari 1 buah. Dan… kedua perintah relasi bisa dibolak-balik (“mk.kodemk = ambilmk.kodemk AND mhs.nim = ambilmk.nim”) karena sifat operasi AND adalah komutatif.

Hasil dari query tersebut adalah

Trus… mungkin ada yang bertanya lagi… bagaimana jika query relasi tabelnya dijalankan di script PHP? Ya… caranya sama saja, Anda tinggal jalankan query SQL untuk relasi tabel di dalam script PHP nya. Dalam hal ini relasi tabel tetap ditentukan oleh query SQL nya.

Sebagai contoh, script PHP di bawah ini akan menampilkan hasil dari query terakhir di atas.

<?php
 
mysql_connect("dbhost", "dbuser", "dbpass");
mysql_select_db("dbname");
 
$query = "SELECT mhs.nim, mhs.namamhs, ambilmk.nilai
          FROM mhs, ambilmk, mk
          WHERE mhs.nim = ambilmk.nim AND mk.kodemk = ambilmk.kodemk
                AND mk.namamk = 'KALKULUS I'";
$hasil = mysql_query($query);
 
echo "<table border='1'>";
echo "<tr><td>NIM</td><td>Nama Mhs</td><td>Nilai</td></tr>";
while ($data = mysql_fetch_array($hasil))
{
   echo "<tr><td>".$data['nim']."</td><td>".$data['namamhs']."</td><td>".$data['nilai']."</td></tr>";
}
echo "</table>";
 
?>

dan hasilnya…

Dengan demikian, saya ulangi lagi bahwa dalam hal ini relasi tabel dilakukan di dalam query SQL nya dan tidak di PHP nya. Script PHP hanya menjalankan query, memfetching data hasil query dan kemudian menampilkannya di browser.

OK… mudah-mudahan artikel ini bermanfaat bagi Anda yang sedang belajar merelasikan tabel dengan query SQL sekaligus menjalankannya di PHP.

Download Disini

Fakta Seputar Query INSERT di SQL

Kita tentu telah mengenal bentuk query yang satu ini. Ya… query INSERT dalam database yang mendukung SQL digunakan untuk menyisipkan record atau data ke dalam tabel. Saya kira mengenal saja tidak cukup, tapi lebih dari itu kita harus bisa menggunakannya dengan baik dan benar, seperti halnya berbahasa Indonesia

(gak nyambung ya…)

Mungkin ada di antara Anda yang protes pada saya, ngapain sih kok nulis artikel ini. Kan query INSERT itu mudah sekali. Iya sih.. mudah bagi Anda yang sudah expert tapi hal baru bagi para newbie. Toh… Anda dulu (yang expert) pernah jadi newbie juga

Alasan ditulisnya artikel ini tak lain adalah sering saya jumpai keluhan baik itu rekan2 sesama programmer pemula, mahasiswa maupun pengunjung blog ini yang sering gagal dalam menjalankan query INSERT ini. Salah satunya adalah pengunjung blog saya ini. Mereka menganggap query SQL yang diberikan sudah benar, tapi mengapa data atau record tak jua tersimpan dalam tabel. Mudah-mudahan dengan artikel ini bisa sedikit menjawab semua kesulitan yg dihadapi tsb.

OK… untuk studi kasus, misalkan saya berikan sebuah tabel sebagai berikut

CREATE TABLE sampel (
  field1 int(11) auto_increment,
  field2 varchar(10),
  field3 date,
  field4 float,
  field5 text,
  PRIMARY KEY  (field1)
);

Dalam tabel tersebut saya sengaja memberikan beberapa variasi tipe data pada setiap fieldnya, dengan Primary Key nya adalah ‘field1′ dengan tipe data integer dan auto increment.

Nah.. secara umum, bila kita ingin menyisipkan sebuah record ke dalam tabel menggunakan query SQL maka menggunakan sintaks sbb:

1.INSERT INTO namaTabel (field1, field2, field3, ..., fieldn)

2. VALUES ('value1', 'value2', 'value3', ..., 'valuen');

Sintaks tersebut memiliki makna kita akan menyisipkan data atau record dengan ‘value1′ merupakan nilai yang bersesuaian dengan ‘field1′, ‘value2′ bersesuaian dengan ‘field2′, dst.

Namun… yang menjadi pertanyaan adalah, Apakah susunan urutan penulisan field dalam query INSERT tersebut harus selalu sama dengan urutan field dalam tabel? Jawabnya adalah TIDAK. Anda bisa saja membolak-balik urutan field dalam penulisan query nya, asal… valuenya juga menyesuaikan. Contohnya:

1.INSERT INTO namaTabel (field2, field1, field3, ..., fieldn)

2. VALUES ('value2', 'value1', 'value3', ..., 'valuen');

Trus.. ada pertanyaan lagi yang lain, Apakah dalam menuliskan query INSERT ini semua field dalam tabel harus dituliskan setelah nama tabel? Jawabnya adalah TIDAK. Anda dapat menyisipkan record hanya pada field-field tertentu saja. Contohnya kita akan menyisipkan record pada tabel studi kasus di atas hanya pada ‘field2′, dan ‘field3′ saja

1.INSERT INTO sampel (field2, field3)

2. VALUES ('Hallo', '2009-10-12');

Catatan:
khusus untuk tipe data DATE, format value yang bisa disimpan adalah dalam bentuk ‘yyyy-mm-dd’.

Mungkin Anda bertanya lagi, Bagaimana bila tabel tersebut memuat field yang merupakan ‘AUTO INCREMENT’? value dalam query INSERT nya diisi dengan apa?

Ya… karena dia auto increment, nama field dan value tidak usah dituliskan, karena nanti akan diisi secara otomatis oleh sistem. Sebagai contoh adalah query berikut ini yang menyisipkan record pada tabel ‘sampel’ di atas pada semua field kecuali ‘field1′ yang merupakan auto increment.

1.INSERT INTO sampel (field2, field3, field4, field5)

2. VALUES ('Hallo', '2009-10-12', '2.89', 'Ini Sebuah Text');

Perhatikan contoh query di atas, bahwa untuk semua value, tidak peduli tipe data fieldnya apa, diapit dengan tanda petik.

Oya.. harap hati-hati dengan penggunaan bentuk query ini, karena field-field yang merupakan PRIMARY KEY (namun bukan AUTO INCREMENT) harus diisikan valuenya.

Sebagai contoh, misalkan suatu tabel terdapat 3 field ‘field1′, ‘field2′, dan ‘field3′ dimana ‘field1′ nya merupakan PRIMARY KEY (bukan auto increment).

Jika kita berikan query INSERT sbb:

1.INSERT INTO namaTabel (field2, field3)

2. VALUES ('value2', 'value3');

maka akan terjadi ERROR, karena ‘field1′ nantinya akan kosong (tidak ada valuenya).

Selain bentuk atau sintaks query yang disebutkan di atas, ada pula query INSERT dengan bentuk sbb:

1.INSERT INTO namaTabel

2. VALUES ('value1', 'value2', ..., 'valuen');

Perbedaan yang kentara dari query ini dengan sebelumnya hanyalah tidak menyebutkan nama-nama field pada query.

Nah… apa perbedaan makna penulisan query tersebut dengan sebelumnya?

Ya.. perbedaannya adalah, bahwa dengan bentuk query tersebut, Anda harus urut dalam menuliskan valuenya (sesuai urutan field dalam tabel) dan juga jumlah value yang Anda tuliskan harus sama dengan jumlah field yang ada dalam tabel tersebut, atau dengan kata lain bentuk query INSERT tersebut akan mengisi record untuk semua field yang ada dalam tabel.

Sebagai contoh misalkan dalam suatu tabel terdapat field dengan urutan sbb:

field1, field2, field3

dan kemudian Anda memberikan query INSERT sbb:

1.INSERT INTO namaTabel

2. VALUES ('value1', 'value2', 'value3');

maka ‘value1′ ini akan mengisi ‘field1′, ‘value2′ mengisi ‘field2′ dan ‘value3′ mengisi ‘field3′ (sesuai urutan field dalam tabel)

Sedangkan jika Anda menuliskan query INSERT sbb:

1.INSERT INTO namaTabel

2. VALUES ('value1', 'value2');

maka akan terjadi error, karena jumlah field dalam tabel tidak sama dengan jumlah valuenya.

Trus… mungkin ada pertanyaan lagi, jadi… bisakah kita gunakan bentuk query INSERT terakhir ini untuk menyisipkan record ke dalam tabel yang di dalamnya terdapat auto increment? Jawabnya adalah.. sebaiknya JANGAN, karena field auto increment ini akan diisi otomatis oleh sistem, jadi.. sebaiknya gunakan bentuk sintaks INSERT pertama di atas.

OK… mudah-mudahan artikel ini bermanfaat bagi Anda, terutama yang masih sering salah dalam menyisipkan record ke dalam tabel.

Download Disini

Perintah SQL (SELECT) untuk Multi Tabel (Bag. 1)

Pada artikel ini akan diberikan beberapa contoh perintah SQL yang diimplementasikan pada multi tabel. Adapun perintah SQL untuk manipulasi data pada satu tabel, saya yakin 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,
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        sks
A01       Kalkulus      3
A02       Geometri      2
A03       Aljabar       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

Selanjutnya misalkan akan dicari data-data sbb:

Tampilkan nim dan nama mahasiswa yang mengambil Kalkulus (kodeMK = A01)
Tampilkan nim, nama mahasiswa dan jumlah SKS matakuliah yang diambil untuk setiap mahasiswa
Berapakah IPK mahasiswa bernama Budi (NIM: 003)
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.

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

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

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

OK… selanjutnya akan kita bahas untuk pertanyaan no. 4, yaitu bagaimana statement SQL untuk menampilkan nim, nama dan nilai IPK semua mahasiswa.

Nah… kalau pertanyaan ini, 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.

Download Disini

Senin, 13 Juni 2011

Cara Mengubah Struktur Tabel dengan Query SQL

Cara Membuat Foreign Key, Relasi Tabel dan Referential Integrity di phpMyAdmin

Cara Membuat Query SQL Untuk Relasi Tabel

Fakta Seputar Query INSERT di SQL

Perintah SQL (SELECT) untuk Multi Tabel (Bag. 1)

_Labels

_Populer

_Archive

_Statistic