INDEX

Setiap orang yang menggeluti SQL pasti akan bertemu dengan INDEX. Apa dan bagaimana yang dimaksud dengan INDEX? Dari hasil googling dengan keyword "define:index" (tanpa tanda petik) akan langsung ketemu banyak definisi dari INDEX yang tersebar di internet (catatan: "define:" adalah kata kunci spesial yang dapat digunakan untuk mencari berbagai definisi menggunakan google), namun dari hasil yang banyak tersebut saya memilih definisi yang berikut ini:

A special database object that lets you quickly locate particular records based on key column values. Indexes are essential for good database performance.
www.orafaq.com/glossary/faqglosi.htm

Ada 3 cara untuk membuat INDEX didalam database MySQL:

1. Pada saat CREATE TABLE
   Kita dapat langsung menyisipkan syntax untuk membuat index, misalnya:

   CREATE TABLE person (
      nomor_id int(10) unsigned NOT NULL default '0',
      nama char(100) NOT NULL default '',
      INDEX nomor_id_idx (nomor_id)
   );

2. Dengan perintah CREATE INDEX
   Dengan cara ini, tabel pada mulanya di-CREATE tanpa INDEX, kemudian index dibuat belakangan dengan perintah CREATE INDEX:

   CREATE INDEX nomor_id_idx ON person (nomor_id);

   Syntax-nya secara empiris dapat ditulis:

   CREATE INDEX nama_index ON nama_tabel (nama_kolom, ...);

3. Dengan perintah ALTER TABLE
   Hampir mirip dengan CREATE INDEX, index ini dibuat belakangan, dengan perintah:

   ALTER TABLE person ADD INDEX nomor_id_idx (nomor_id);

   Dan bentuk empirisnya:

   ALTER TABLE nama_tabel ADD INDEX nama_index (nama_kolom, ...);

Dari ketiga cara di atas, terserah kepada kita untuk menggunakan yang mana, semuanya sama saja.

Sedangkan untuk menghapus index menggunakan perintah:

ALTER TABLE nama_tabel DROP INDEX nama_index;

Setelah index dibuat, lalu bagaimana cara menggunakannya? Penggunaan index didalam query biasanya terjadi secara otomatis oleh database engine-nya, namun otomatisasi tersebut dapat kita arahkan atau kendalikan, yaitu dengan panduan atau patokan sebagai berikut: Yaitu index akan digunakan:

• Pada klausa WHERE
  Maka kemungkinan kita perlu untuk membuat index pada setiap kolom yang tersebut didalam klausa WHERE. Misalnya:

  SELECT * FROM person WHERE nama = 'John';

  Jika jumlah record hanya sedikit, INDEX tidak begitu berpengaruh. Namun jika record sudah mencapai puluhan ribu bahkan ratusan ribu, maka kita perlu membuat INDEX untuk kolom nama:

  CREATE INDEX nama_idx ON person (nama(10));

  Perintah diatas akan membuat index untuk kolom nama namun hanya menggunakan 10 karakter pertama saja. Dengan hanya 10 karakter saja, index menjadi lebih ramping dan dapat diakses lebih cepat. Teknik ini cukup optimal dibandingkan dengan membuat index seluruh panjang data kolom (100 karakter) yang mengakibatkan ukuran index menjadi sangat besar dan lambat. Namun untuk mengetahui ukuran optimalnya, apakah 10 atau 15 atau 50 karakter yang akan di-index, kita harus melihat karakteristik datanya, misalnya untuk nama orang - 10 karakter sudah cukup menjadi pembeda.


• Pada klausa JOIN:

  SELECT a.id_pasien,b.nama
  FROM pasien a
  LEFT JOIN person b ON b.nomor_id = a.nomor_id

  Maka kita perlu membuat index untuk kolom nomor_id pada tabel person (sudah kita buat untuk contoh awal). Mengapa yang perlu dibuat adalah index untuk kolom nomor_id pada tabel person, bukan pada tabel pasien? Karena pencarian berdasarkan kolom nomor_id hanya akan dilakukan pada tabel person saja, dan pencarian inilah yang akan menggunakan index (jika ada).


• Pada klausa ORDER BY

  SELECT * FROM person ORDER BY nama;

  Query tersebut akan mengurutkan hasil query berdasarkan nama. Sedangkan sifat alamiah dari index ialah berurutan, maka query tersebut akan sangat terbantu jika sudah ada index pada kolom tersebut.


• Pada klausa GROUP BY

  SELECT COUNT(*) FROM person GROUP BY usia;

  Hampir sama dengan ORDER BY, jika ada index pada kolom usia, maka proses query akan menggunakannya.

Dengan memahami karakteristik tersebut diatas maka barulah kita dapat merancang query yang optimal dan efisien. Terutama saya, pada query dengan JOIN, kadang kala saya lupa untuk membuat index, setelah query bertambah rumit baru dirasa lambat sekali. Kemudian setelah dibuat index untuk semua kolom yang tersebut didalam klausa JOIN, barulah query dapat selesai dalam waktu sekejap.

Query dengan JOIN

Untuk apa kita perlu query join? Query join berfungsi untuk menggabungkan beberapa tabel menjadi sebuah tabel gabungan. Teknik menggabung tersebut adalah keunggulan dari basis data relasional (RDBMS: Relational Database Management System), dimana data dapat disimpan dalam beberapa tabel kecil yang terpisah, bukannya satu tabel besar dengan banyak kolom. Pada dunia nyata pemrograman, query join sangat banyak dan hampir selalu digunakan, karena dengan itu sebenarnya kunci pengolahan data dengan SQL dan dari situ dapat dihasilkan informasi-informasi yang lebih bermakna dibandingkan dengan query satu tabel saja.

Cara kerja join ialah dengan mencari kecocokan kolom tertentu pada sebuah tabel dengan kolom tertentu pada tabel lain, kemudian menampilkan hasilnya sebagai satu set data gabungan. Secara garis besar, terdapat 3 macam join, yaitu INNER JOIN, LEFT JOIN dan RIGHT JOIN. Namun, selain dari 3 macam tersebut, terdapat tambahan beberapa varian tergantung dari RDBMS yang digunakan.

Sebagai contoh, kita akan men-join tabel pasien yang menyimpan informasi biodata pasien-pasien, dengan tabel symptom yang menyimpan data-data symptom yang diderita oleh pasien. Kedua tabel tersebut akan di-join berdasarkan kolom yang berisi nomor identitas pasien (id) yang terdapat pada kedua tabel.

CREATE TABLE pasien (id int(10), nama char(30));
INSERT INTO pasien (id,nama) VALUES ('1','John');
INSERT INTO pasien (id,nama) VALUES ('2','Jane');
INSERT INTO pasien (id,nama) VALUES ('3','Mike');
INSERT INTO pasien (id,nama) VALUES ('4','Doel');

CREATE TABLE symptom (id int(10), symptom char(30));
INSERT INTO symptom (id,symptom) VALUES ('1','Demam');
INSERT INTO symptom (id,symptom) VALUES ('2','Pusing');
INSERT INTO symptom (id,symptom) VALUES ('3','Mual');

INNER JOIN

Dengan INNER JOIN hanya akan ditampilkan baris-baris yang satu sama lain memiliki kecocokan.

Contoh:

SELECT a.nama, b.symptom
FROM pasien a 
INNER JOIN symptom b
ON b.id = a.id;
+------+---------+
| nama | symptom |
+------+---------+
| John | Demam   | 
| Jane | Pusing  | 
| Mike | Mual    | 
+------+---------+

LEFT JOIN

Sebuah query dengan LEFT JOIN akan menampilkan semua baris dari tabel sebelah kiri (pertama/utama atau pasien) dengan gabungan dari tabel sebelah kanan (kedua atau symptom).

Contoh:

SELECT a.nama, b.symptom
FROM pasien a
LEFT JOIN symptom b
ON b.id = a.id;
+------+---------+
| nama | symptom |
+------+---------+
| John | Demam   | 
| Jane | Pusing  | 
| Mike | Mual    | 
| Doel | NULL    | 
+------+---------+

Perbedaannya dengan INNER JOIN ialah, pada LEFT JOIN - seluruh baris dari tabel pertama akan ditampilkan meskipun tidak ada kecocokan baris pada tabel kedua. Lihat contoh diatas, pasien Doel tidak memiliki data symptom sehingga tertampil NULL (NULL disini menyatakan bahwa tidak ada data pada kolom tersebut). Selain itu, posisi tabel jadi mempunyai makna, yaitu tabel kiri atau pertama (ialah tabel sebelum 'LEFT JOIN') akan menjadi tabel utama yang diquery terlebih dahulu, baru kemudian dicarikan kecocokannya dengan tabel kanan atau kedua (ialah tabel setelah 'LEFT JOIN').


RIGHT JOIN

Query RIGHT JOIN hampir sama dengan LEFT JOIN, hanya saja posisinya terbalik, yaitu akan menampilkan semua baris dari tabel kanan (kedua) meskipun tidak ada kecocokan pada tabel kiri (pertama).

Contoh:

SELECT a.nama, b.symptom
FROM pasien a
RIGHT JOIN symptom b
ON b.id = a.id;
+------+---------+
| nama | symptom |
+------+---------+
| John | Demam   | 
| Jane | Pusing  | 
| Mike | Mual    | 
+------+---------+

Tips dan Pengalaman Pribadi

Jika ada yang bertanya dengan sebuah kasus, harus menggunakan join yang mana? Maka kita perlu melihat keperluan terlebih dahulu, selain itu optimasi juga perlu menjadi pertimbangan, intinya ialah ketiga macam join dapat digunakan. Namun, saya pribadi paling sering menggunakan LEFT JOIN dibanding jenis JOIN yang lain. Alasan saya ialah, query LEFT JOIN lebih fleksibel dan lebih mudah dioptimasi. Mari kita ambil contoh query dengan INNER JOIN diatas, dapat juga dihasilkan dengan query LEFT JOIN, dengan menambahkan klausa WHERE:

SELECT a.nama, b.symptom
FROM pasien a
LEFT JOIN symptom b
ON b.id = a.id
WHERE b.symptom IS NOT NULL;

Atau pun query RIGHT JOIN diatas dihasilkan dengan model LEFT JOIN, hanya dengan membalik urutan tabel:

SELECT a.nama, b.symptom
FROM symptom b
LEFT JOIN pasien a
ON b.id = a.id;

Kemudian salah satu pertimbangan kecenderungan saya menggunakan LEFT JOIN ialah untuk optimasi. Contoh, misalnya kita dihadapkan pada tabel pasien dan tabel symptom, jika ada pertanyaan untuk menampilkan pasien dengan sebuah symptom tertentu, misalnya demam, maka saya akan menempatkan tabel symptom sebagai tabel utama, baru kemudian tabel pasien sebagai tabel kedua:

SELECT a.id,b.nama
FROM symptom a
LEFT JOIN pasien b
ON b.id = a.id
WHERE a.symptom = 'Demam';

Bagaimana query tersebut lebih optimal? Penjelasannya begini: query tersebut akan mencari record-record dari tabel symptom yang memenuhi kriteria symptom = 'Demam', hal ini akan menghindari full table scan (dengan catatan jika ada indeks berdasarkan symptom), selanjutnya akan dicari padanan id di tabel pasien. Sebagai catatan: full tabel scan akan memakan sumber daya memori dan tenaga proses di CPU, membuat query menjadi sangat lambat terutama jika tabel memiliki jutaan record. Mengenai indeks pada tabel akan saya jelaskan pada kesempatan lain.

Lalu bagaimana dengan RIGHT JOIN? Sebenarnya RIGHT JOIN dapat pula didayagunakan seperti LEFT JOIN, hanya masalah kebiasaan saja. Kadang-kadang saya juga menggunakan RIGHT JOIN untuk kasus tertentu demi alasan code readability atau simplicity.

Tutorial SQL Bagian 4

Mengawali tutorial bagian ke empat ini kita akan mengenal satu perintah SQL yaitu DELETE. Fungsi dari perintah DELETE mungkin langsung bisa ditebak, yaitu untuk menghapus record pada tabel. Contoh:

DELETE FROM pasien WHERE kelamin = 'L';

maksudnya: hapuslah record-record pada tabel pasien yang mempunyai atribut kelamin = 'L' alias laki-laki.

Atau perintah:

DELETE FROM pasien;

maksudnya: hapuslah semua record pada tabel pasien.

Sebaiknya statement DELETE tidak dicoba dahulu karena data yang ada masih akan dibutuhkan untuk materi tutorial selanjutnya. Selain itu juga perlu dicatat, bahwa perintah DELETE tidak bisa di-undo atau dikembalikan, karena menilik dari nature-nya bahwa database ada pada tingkat bawah (low level) atau dasar dari aplikasi yang lebih kompleks, maka fasilitas undo adalah tanggung jawab programmer aplikasi.

Kita akan melanjutkan mengenai formulir laporan suspect flu burung (lihat Tutorial SQL Bagian 3, Case Report Form). Formulir tersebut akan diwajibkan untuk diisi dengan data dari pasien yang memiliki salah satu atau lebih gejala/tanda berikut:

• suhu tinggi
• batuk
• radang tenggorok
• nafas tersengal
• paparan langsung dengan unggas
• paparan wilayah bekas tempat pemeliharaan unggas

Setelah formulir diisi lengkap, selanjutnya dimasukkan kedalam database (proses ini sering disebut data entry). Pada praktek sesungguhnya untuk memasukkan data kedalam database biasanya digunakan aplikasi yang dibuat khusus untuk memasukkan data dalam jumlah banyak dengan cepat. Setelah data dianggap cukup banyak, baru kita bisa menggali informasi darinya. Tahap penggalian informasi ini adalah tahap yang sangat menarik dan merupakan tahap terakhir dari sebuah surveillance. Metode penggalian lebih menarik jika dimulai dengan membuat sebuah pertanyaan, baru kemudian kita berpikir untuk menghasilkan query yang tepat untuk dapat menjawab pertanyaan itu. Mari kita coba pertanyaan-pertanyaan berikut ini:

Pertama, kita akan mencari tahu berapa pasien yang memenuhi beberapa kriteria gejala, misalnya memiliki jawaban Ya pada gejala: suhu tinggi, batuk, radang tenggorok dan nafas tersengal.

SELECT COUNT(*) as 'jumlah kasus' FROM pasien
   WHERE suhu_tinggi = 'a' 
   AND batuk = 'a'
   AND radang_tenggorok = 'a'
   AND nafas_tersengal = 'a';

hasilnya:

+--------------+
| jumlah kasus |
+--------------+
|            1 |
+--------------+

Berikutnya, berapa kasus yang mengalami kematian?

SELECT COUNT(*) FROM pasien WHERE status_keluar = 'b';

Bagaimana penyebaran wilayah untuk kasus yang mengalami paparan langsung?

SELECT b.nama_wilayah,COUNT(*) as 'jumlah kasus'
   FROM pasien a
   LEFT JOIN wilayah b USING(kode_wilayah)
   WHERE a.paparan_langsung = 'a'
   GROUP BY b.kode_wilayah;

Pada query diatas terdapat kata USING. Kita dapat menggunakan USING dalam JOIN jika nama kolom antara kedua tabel sama persis. Pada contoh tersebut, kolom kode_wilayah pada tabel pasien sama persis dengan kolom kode_wilayah pada tabel wilayah, sehingga penulisan perintahnya menjadi lebih sederhana dan mudah dipahami. Kemudahan tersebut seyogyanya dapat menjadi salah satu pertimbangan dalam desain-desain tabel selanjutnya.

Dimana dan berapa terjadi kasus kematian?

SELECT b.nama_wilayah,COUNT(*) as 'jumlah kasus'
   FROM pasien a
   LEFT JOIN wilayah b USING(kode_wilayah)
   WHERE a.status_keluar = 'b'
   GROUP BY b.kode_wilayah;

Berapa kasus yang terjadi pada bulan September 2006?

SELECT COUNT(*) as 'jumlah kasus'
   FROM pasien
   WHERE DATE(gejala_dttm) >= '2006-09-01'
   AND DATE(gejala_dttm) <= '2006-09-30';

Ada yang baru yaitu fungsi DATE(). Fungsi ini gunanya untuk mengekstrak informasi tanggal dari sebuah field atau input. Sebenarnya gejala_dttm adalah bertipe DATETIME, maka isinya adalah tanggal dan jam, sementara kita hanya memerlukan informasi tanggalnya saja untuk dijadikan operand dalam WHERE criteria.

Misalkan umur dibagi 2 segmen yaitu usia 30 tahun keatas dan usia dibawah 30 tahun, maka segmen mana yang memiliki jumlah kasus terbanyak?

SELECT COUNT(*) FROM pasien WHERE umur >= 30;
SELECT COUNT(*) FROM pasien WHERE umur < 30;

Atau dengan cara:

(SELECT 'segmen 1',
        COUNT(*) as 'jumlah kasus'
     FROM pasien WHERE umur >= 30)
  UNION
(SELECT 'segmen 2',
        COUNT(*) as 'jumlah kasus'
     FROM pasien WHERE umur < 30)
  ORDER BY 'jumlah kasus' DESC;

Ada kalanya kita tidak mungkin menjawab sebuah pertanyaan dengan satu query, maka solusinya dengan membuat beberapa query yang terpisah atau dapat digabung hasilnya dengan operator UNION. Perlu diperhatikan bahwa UNION mengharuskan jumlah kolom dan tipe kolom adalah sama antara kedua query yang digabung.

Format untuk UNION adalah: (SELECT ...) UNION (SELECT ...).

Manakah gejala yang mungkin menjadi penyebab utama kematian?

Ups!!

Pertanyaan ini agak susah untuk dijawab dengan SQL, namun bukan tidak mungkin. Caranya tidak akan kami jelaskan disini karena bisa sangat panjang, lagipula datanya kurang dan tidak realistis jika digunakan sebagai basis untuk menarik sebuah kesimpulan besar semacam itu. Garis besarnya sebenarnya sama dengan yang digunakan dalam aplikasi-aplikasi pengolahan data statistik seperti SPSS, S-Plus, R, atau yang lain, yaitu kita harus melakukan uji masing-masing variabel gejala yang menunjukkan korelasi dengan variabel kematian.

Dan seterusnya.

Masih banyak pertanyaan yang mungkin untuk diajukan. Kaitannya dengan misi utama sebuah surveillance, maka pertanyaan tidak akan cukup sampai disini, terlebih ilmu dan kasus yang terjadi terus berkembang. Salah satu pertanyaan yang cukup populer saat ini ialah yang berkaitan dengan sistem informasi geografis mengenai penyebaran penyakit dan timbulnya wabah. Tidak berlebihan kiranya jika kami berharap untuk bisa membahasnya dengan lebih detil pada kesempatan lain. Harapan kami, semoga tutorial ini dapat sedikit membantu. Terima kasih.

Tips: untuk menguasai sebuah bahasa pemrograman, apakah itu C/C++, Javascript, dll, termasuk SQL, saya pribadi memiliki sebuah cara khusus, yaitu dengan sering meng-analisa contoh program. Cara ini bagi saya sangat efektif dan cepat, cukup dengan duduk beberapa jam saja. Banyak contoh program yang tersebar di internet dan bisa dilihat query SQL-nya. Selain contoh program, kita juga bisa menemukan contoh query di milis-milis yang berhubungan dengan database yang menggunakan SQL, misalnya Oracle, MySQL, Ms SQL, dll. Sedangkan metode analisa yang saya gunakan ialah dengan memecah query menjadi bagian-bagian frase terkecil untuk di-peta-kan didalam pikiran. Contoh:

SELECT a.nama,b.nama_wilayah
   FROM pasien a
   LEFT JOIN wilayah b USING(kode_wilayah)
   ORDER BY a.nama,b.nama_wilayah;

Akan saya baca (peta-kan):

1. SELECT: adalah perintah untuk mengambil data (select the data, jika kita analogikan dalam bahasa Inggris).

2. a.nama: ini adalah kolom/atribut 'nama' dari tabel 'a'. Manakah tabel 'a'? Kita cari di klausul FROM atau JOIN yang nama/aliasnya 'a', yaitu tabel 'pasien' (FROM pasien a: maksudnya tabel 'pasien', diberi alias 'a').

3. b.nama_wilayah: ini adalah kolom 'nama_wilayah' dari tabel alias 'b', yaitu tabel 'wilayah' (lihat aliasnya di bagian JOIN).

4. FROM pasien a: artinya SELECT ini akan diambil data dari tabel 'pasien' dan kita beri alias 'a'.
5. LEFT JOIN wilayah b USING(kode_wilayah): artinya ialah gabungkan (join) tabel sebelumnya (tabel 'pasien') dengan tabel 'wilayah', dengan kolom gabungan yaitu 'kode_wilayah'. Proses LEFT JOIN bisa diilustrasikan begini: setelah SELECT mendapatkan data dari tabel 'pasien', maka kemudian dicari padanan data dari tabel 'wilayah' berdasarkan kesamaan pada kolom 'kode_wilayah'.

6. ORDER BY a.nama,b.nama_wilayah: maksudnya ialah, urutkan (order) hasil query berdasarkan urutan 'a.nama', kemudian urutan 'b.nama_wilayah'.

Query tersebut sama hasilnya dengan (perhatikan perbedaannya):

SELECT pasien.nama,wilayah.nama_wilayah
   FROM pasien
   LEFT JOIN wilayah USING(kode_wilayah)
   ORDER BY pasien.nama, wilayah.nama_wilayah;

sama dengan:

SELECT pasien.nama,wilayah.nama_wilayah
   FROM pasien, wilayah
   WHERE wilayah.kode_wilayah = pasien.kode_wilayah
   ORDER BY pasien.nama,wilayah.nama_wilayah;

(query yang kedua ini sering disebut STRAIGHT JOIN).

Catatan akhir: dari semua itu, yang terpenting ialah bagaimana kita membaca sebuah query lalu melogikanya, mencari titik-titik logisnya, akhirnya kita memahami, yang kemudian berhasil kepada bertambahnya kemampuan kita menggunakan bahasa SQL.

Sekian.

Tutorial SQL Bagian 3

Sebelum melanjutkan tutorial ini, sangat dianjurkan untuk menggunakan aplikasi database yang lebih serius. Kami menganjurkan untuk mendownload MySQL 5.0 beserta MySQL GUI Tools dari situs dev.mysql.com. Silakan ikuti petunjuknya, pilih yang sesuai dengan sistem operasi komputer Anda. Untuk seterusnya, perintah yang dicontohkan disini akan compatible dengan perintah MySQL.

Pada tutorial SQL bagian ketiga ini kita akan melanjutkan pembuatan tabel untuk proyek surveillance of disease yang mengambil contoh kasus flu burung. Formulir dibawah ini adalah contoh Case Report Form yang diambil dari www.hpa.org.uk, namun telah sangat disederhanakan supaya lebih memudahkan.

1. Sosio Demografi Pasien

   a. Jenis Kelamin:

      [] Laki       [] Perempuan   [] Tidak diketahui

   b. Umur:                        c. Tanggal Lahir:

      __ tahun __ bulan               __ - __ - ____  ( dd - mm - yyyy )

   d. Alamat:

      ___________________________________________________

      ____________________ kota/kabupaten: ______________



2. Gejala/Tanda:

   a. Tanggal muncul gejala/tanda:

      __ - __ - ____ ( dd - mm - yyyy )

   b. Suhu tinggi, lebih dari 38 derajat celcius:
    
      [] Ya     [] Tidak   [] Tidak diketahui

   c. Batuk:

      [] Ya     [] Tidak   [] Tidak diketahui

   d. Radang tenggorokan:

      [] Ya     [] Tidak   [] Tidak diketahui

   e. Nafas tersengal:

      [] Ya     [] Tidak   [] Tidak diketahui



3. Sejarah terpapar hewan/unggas:

   Dalam 7 hari sebelum muncul gejala:

      a. Apakah pasien pernah terpapar langsung dengan
         hewan/unggas:

         [] Ya     [] Tidak   [] Tidak diketahui

      b. Pernahkah memasuki wilayah dimana hewan/unggas
         dipiara atau pernah dipiara:

         [] Ya     [] Tidak   [] Tidak diketahui



4. Status Keluar

   [] Sembuh    [] Meninggal  [] Tanpa tindak lanjut

Kita akan mencoba mendesain tabel untuk item nomor 2,3 dan 4, yaitu dengan cara memodifikasi tabel pasien, menggunakan perintah ALTER TABLE. Untuk tipe data tanggal, kita menggunakan DATETIME. Sedangkan tipe data pilihan cukup menggunakan CHAR(1) saja, yang isinya satu huruf (a, b atau c) akan kita sepakati dengan a = Ya, b = Tidak dan c = tidak diketahui. Begitu pula untuk status keluar, kita sepakati dengan a = Sembuh, b = Meninggal dan c = Tanpa tindak lanjut. Maka kita modifikasi tabel dengan perintah berikut:

ALTER TABLE pasien ADD COLUMN gejala_dttm DATETIME;
ALTER TABLE pasien ADD COLUMN suhu_tinggi CHAR(1);
ALTER TABLE pasien ADD COLUMN batuk CHAR(1);
ALTER TABLE pasien ADD COLUMN radang_tenggorok CHAR(1);
ALTER TABLE pasien ADD COLUMN nafas_tersengal CHAR(1);
ALTER TABLE pasien ADD COLUMN paparan_langsung CHAR(1);
ALTER TABLE pasien ADD COLUMN paparan_wilayah CHAR(1);
ALTER TABLE pasien ADD COLUMN status_keluar CHAR(1);

Catatan: Tipe CHAR(1) sama dengan Tipe CHARACTER, panjang maksimum 1 karakter. Namun sebenarnya ada tipe data yang lebih cocok yaitu ENUM, tetapi untuk sementara ini kita menggunakan CHAR saja dulu.

Perintah ALTER TABLE tersebut akan menambah kolom pada tabel pasien. Akan kita cek dengan perintah DESCRIBE:

DESCRIBE pasien;

hasilnya:

+------------------+-------------+
| Field            | Type        |
+------------------+-------------+
| nama             | text        |
| alamat           | text        |
| kode_wilayah     | text        |
| kelamin          | char(1)     |
| umur             | smallint(6) |
| gejala_dttm      | datetime    |
| suhu_tinggi      | char(1)     |
| batuk            | char(1)     |
| radang_tenggorok | char(1)     |
| nafas_tersengal  | char(1)     |
| paparan_langsung | char(1)     |
| paparan_wilayah  | char(1)     |
| status_keluar    | char(1)     |
+------------------+-------------+

Jika kita perhatikan pada kolom Type, ada perbedaan tipe untuk kelamin yaitu char(1) sedangkan pada tutorial pertama tertulis CHARACTER. Perbedaan ini tidak menjadi masalah, dikarenakan perbedaan dialek SQL antar aplikasi database.

Sebagai tambahan, perintah ALTER TABLE juga dapat digunakan untuk menghapus kolom/field. Akan kita coba, sebelumnya kita tambahkan satu kolom dummy yang nanti bakal dihapus:

ALTER TABLE pasien ADD COLUMN test_hapus CHAR(1);

hasilnya (dengan perintah DESCRIBE):

+------------------+-------------+
| Field            | Type        |
+------------------+-------------+
| nama             | text        |
| alamat           | text        |
| kode_wilayah     | text        |
| kelamin          | char(1)     |
| umur             | smallint(6) |
| gejala_dttm      | datetime    |
| suhu_tinggi      | char(1)     |
| batuk            | char(1)     |
| radang_tenggorok | char(1)     |
| nafas_tersengal  | char(1)     |
| paparan_langsung | char(1)     |
| paparan_wilayah  | char(1)     |
| status_keluar    | char(1)     |
| test_hapus       | char(1)     |   <--- ini akan kita hapus
+------------------+-------------+

Caranya menghapus kolom:

ALTER TABLE pasien DROP COLUMN test_hapus;

hasilnya:

+------------------+-------------+
| Field            | Type        |
+------------------+-------------+
| nama             | text        |
| alamat           | text        |
| kode_wilayah     | text        |
| kelamin          | char(1)     |
| umur             | smallint(6) |
| gejala_dttm      | datetime    |
| suhu_tinggi      | char(1)     |
| batuk            | char(1)     |
| radang_tenggorok | char(1)     |
| nafas_tersengal  | char(1)     |
| paparan_langsung | char(1)     |
| paparan_wilayah  | char(1)     |
| status_keluar    | char(1)     |
+------------------+-------------+

Foila!!, sudah hilang lagi.


Berikutnya, kita akan menggunakan statement baru yaitu UPDATE. Dengan perintah UPDATE kita akan mengubah data pada record. Sebelumnya, akan kita cek dulu isi field yang telah kita tambahkan dengan perintah:

SELECT nama,umur,gejala_dttm,suhu_tinggi
FROM pasien;

hasilnya:

+--------+------+-------------+-------------+
| nama   | umur | gejala_dttm | suhu_tinggi |
+--------+------+-------------+-------------+
| John   |   35 | NULL        | NULL        |
| Jane   |   30 | NULL        | NULL        |
| George |   30 | NULL        | NULL        |
| Silvy  |   20 | NULL        | NULL        |
| Tommy  |   25 | NULL        | NULL        |
+--------+------+-------------+-------------+

Kemudian akan kita coba perintah UPDATE berikut:

UPDATE pasien SET gejala_dttm = '2006-09-19 08:00:00', suhu_tinggi = 'a';

akan mengubah gejala_dttm = '2006-09-19 08:00:00' (jam 8 pagi, tanggal 19 September 2006) dan suhu_tinggi = 'a' (alias Ya, atau muncul suhu tinggi), dan perubahannya akan terjadi pada semua record pada tabel pasien (karena tidak ada WHERE criteria).

Untuk memasukkan tanggal digunakan format seperti contoh, yaitu: tahun-bulan-tanggal<spasi>jam:menit:detik. Format ini bagi kita kurang biasa, namun bagi komputer akan sangat memudahkan untuk dibandingkan dan diurutkan dengan kecepatan sangat tinggi.

Akan kita coba melihat hasilnya dengan perintah:

SELECT nama,umur,gejala_dttm,suhu_tinggi
FROM pasien;

hasilnya:

+--------+------+---------------------+-------------+
| nama   | umur | gejala_dttm         | suhu_tinggi |
+--------+------+---------------------+-------------+
| John   |   35 | 2006-09-19 08:00:00 | a           |
| Jane   |   30 | 2006-09-19 08:00:00 | a           |
| George |   30 | 2006-09-19 08:00:00 | a           |
| Silvy  |   20 | 2006-09-19 08:00:00 | a           |
| Tommy  |   25 | 2006-09-19 08:00:00 | a           |
+--------+------+---------------------+-------------+

Selanjutnya kita akan mencoba beberapa statement UPDATE dibawah ini. Silakan untuk mencobanya dan melihat hasilnya setiap selesai satu UPDATE.

UPDATE pasien SET
   batuk = 'a',
   radang_tenggorok = 'a',
   nafas_tersengal = 'b',
   paparan_langsung = 'a',
   paparan_wilayah = 'b',
   status_keluar = 'a';

UPDATE pasien SET paparan_wilayah = 'c' WHERE nama = 'John';

UPDATE pasien SET nafas_tersengal = 'a',
       status_keluar = 'b'
WHERE nama = 'George';

UPDATE pasien SET paparan_langsung = paparan_wilayah WHERE kelamin != 'L';

Cobalah untuk menarik garis logika dari perintah-perintah tersebut. SQL sebenarnya cukup mudah dipahami dikarenakan dialeknya mirip dengan bahasa Inggris biasa.

Jika kita perhatikan statement UPDATE yang terakhir agak sedikit baru. Statement tersebut jika dibahasakan sama dengan perintah: Pada tabel pasien, ubahlah kolom paparan_langsung menjadi sama isinya dengan paparan_wilayah, untuk record-record yang memiliki data kolom kelamin tidak sama dengan 'L' (atau laki-laki). Mudah bukan?


Bersambung.

Tutorial SQL Bagian 2

Setelah pada tutorial sebelumnya kita membuat 2 tabel yaitu tabel pasien dan tabel wilayah, kali ini kita akan mencoba menggali informasi dari 2 tabel tersebut dengan query yang lebih canggih, yaitu JOIN:

Pertama kita akan melakukan JOIN antara 2 tabel tersebut:

SELECT pasien.nama,
       pasien.alamat,
       wilayah.nama_wilayah,
       pasien.kelamin,
       pasien.umur
FROM pasien
LEFT JOIN wilayah ON wilayah.kode_wilayah = pasien.kode_wilayah;

hasilnya:

+--------+---------------------+--------------+---------+------+
| nama   | alamat              | nama_wilayah | kelamin | umur |
+--------+---------------------+--------------+---------+------+
| John   | Jl. Enau No. 99     | YOGYAKARTA   | L       |   35 |
| Jane   | Jl. Melati No. 1    | YOGYAKARTA   | P       |   30 |
| George | Jl. Mangga No. 33   | SLEMAN       | L       |   30 |
| Silvy  | Jl. Bambu No. 11    | BANTUL       | P       |   20 |
| Tommy  | Jl. Beringin No. 55 | BANTUL       | L       |   25 |
+--------+---------------------+--------------+---------+------+

Dari hasil query tersebut dapat dilihat bahwa kita telah 'menterjemahkan' kode_wilayah yang kurang dapat dipahami menjadi nama_wilayah yang lebih terbaca oleh manusia. Proses yang terjadi sebenarnya ialah kita menggandengkan 2 query menjadi 1 dengan gandengan berupa field kode_wilayah pada kedua tabel:

  ON wilayah.kode_wilayah = pasien.kode_wilayah

Untuk sementara akan kita tinggalkan dulu query JOIN, selanjutnya akan kita coba query dengan COUNT:

SELECT COUNT(nama) AS jumlah FROM pasien;

hasilnya:

+--------+
| jumlah |
+--------+
|      5 |
+--------+

Kita ingin mengetahui jumlah pasien yang berusia 30 tahun keatas:

SELECT COUNT(nama) AS jumlah
   FROM pasien WHERE umur >= 30;

hasilnya:

+--------+
| jumlah |
+--------+
|      3 |
+--------+

Selanjutnya kita mencoba query dengan GROUP BY. Dengan GROUP BY kita menggabungkan record/baris yang memiliki data yang sama pada field tertentu. Contoh berikut akan mencoba menggali informasi penyebaran pasien berdasarkan jenis kelamin:

SELECT kelamin,COUNT(nama) AS jumlah
   FROM pasien GROUP BY kelamin;

hasilnya:

+---------+--------+
| kelamin | jumlah |
+---------+--------+
| L       |      3 |
| P       |      2 |
+---------+--------+

Kemudian kita akan menggali informasi penyebaran pasien berdasarkan wilayah. Sebelumnya kita akan kembali melihat query JOIN di atas lalu kita modifikasi sedikit menjadi:

SELECT wilayah.nama_wilayah,
       COUNT(pasien.nama) AS jumlah
FROM pasien
LEFT JOIN wilayah ON wilayah.kode_wilayah = pasien.kode_wilayah
GROUP BY wilayah.kode_wilayah;

hasilnya:

+--------------+--------+
| nama_wilayah | jumlah |
+--------------+--------+
| BANTUL       |      2 |
| SLEMAN       |      1 |
| YOGYAKARTA   |      2 |
+--------------+--------+

Demikian tutorial SQL bagian kedua ini telah kita selesaikan. Sedikit demi sedikit kita mulai merasa bahwa mempelajari SQL sangat lah mudah. Dibawah ini kami sertakan sedikit tata bahasa (syntax) SQL, kaitannya dengan perintah-perintah SQL yang telah kita coba. Sampai jumpa lagi di tutorial SQL bagian ketiga.

Bersambung.

---

STATEMENT: CREATE TABLE

CREATE TABLE table_name (
      field_name1 TYPE [(size)] [NOT NULL] [index1]
   [, field_name2 TYPE [(size)] [NOT NULL] [index2]
   [, ...]
)

TYPE	Description
TEXT	Characters, zero to a maximum of 2.14 gigabytes.
CHARACTER	Characters, zero to 255 characters.
TINYINT	An integer value between 0 and 255.
SMALLINT	A short integer between – 32,768 and 32,767.
INTEGER	A long integer between – 2,147,483,648 and 2,147,483,647.
FLOAT	A double-precision floating-point value.
etc.

STATEMENT: SELECT

SELECT [table_name.]field_name1 [AS alias1]
[, table_name.]field_name2 [AS alias2]
[, ...]
FROM table_name
[WHERE where_definition ]

OPERATION: LEFT JOIN

SELECT [ ... ]
FROM table_name1
LEFT JOIN table_name2
ON table_name1.field_name1 COMPARISON_OPERATOR table_name2.field_name2
[AND table_name1.field_name3 COMPARISON_OPERATOR table_name2.field_name4]
[ ... ]

COMPARISON_OPERATOR	Description
=	Equal.
>	Greater than.
<	Less than.
>=	Greater than or equal.
<=	Less than or equal.
LIKE	Compare string pattern.
IN	Returns true if data is any of the values in the "IN"
etc.

CLAUSE: GROUP BY

SELECT fieldlist
FROM table_name
WHERE criteria
[GROUP BY groupfieldlist]

FUNCTION: COUNT

SELECT COUNT(field_name) AS [alias]
FROM table_name
[WHERE ...]
[GROUP BY ...]

Tutorial SQL Bagian 1

SQL adalah singkatan dari Structured Query Language, yang merupakan bahasa standar (lingua franca) untuk mengolah database. Bagi Anda yang baru memulai mempelajari SQL dan kebetulan berkecimpung di bidang informasi kesehatan/ilmu kesehatan, tutorial ini kami sajikan dalam langkah-langkah yang praktis serta berkaitan langsung dengan kasus supaya mudah dipahami.

Bagi Anda yang sudah cukup mahir menggunakan SQL, tutorial ini dapat pula digunakan sebagai penyegaran, selain itu juga dapat sebagai bahan renungan untuk pertimbangan alternatif desain database.

Gambaran Singkat Mengenai Database
Apa dan bagaimanakah database itu? Database adalah sarana penyimpanan data (basis data). Data yang kita peroleh (semisal dari hasil penelitian/survei yang kita lakukan) akan disimpan di database dalam bentuk tabel. Di dalam database boleh terdapat banyak tabel untuk menyimpan informasi yang bermacam-macam. Tabel memiliki kolom dan baris. Kolom sering disebut dalam bahasa lain: column/field/attribute, sedangkan baris: row/record/tuple/relation (kami akan menggunakan bahasa-bahasa tersebut sepanjang tutorial ini).

Untuk mempraktekkan langkah-langkah dalam tutorial ini, Anda membutuhkan aplikasi komputer seperti Microsoft Access, MS SQL Server, MySQL, Oracle, dll, dan diharapkan sudah cukup mengenal penggunaan aplikasi tersebut sehingga kita bisa langsung kepada prakteknya. Contoh-contoh perintah SQL yang dibuat sepanjang tutorial ini juga dapat dicopy dan dipaste ke dalam aplikasi untuk langsung dicoba tanpa perlu mengetik terlalu banyak.

Kita akan mengambil contoh kasus sebuah misi/proyek surveillance of disease sepanjang tutorial ini.

Langkah 1 : Tabel Pasien
Pertama kali, kita akan membuat tabel yang isinya adalah data sosio demografi pasien hasil survei. Tabel ini akan memiliki atribut : nama, alamat, kode_wilayah, kelamin dan umur. Untuk membuat tabel dengan SQL, kita gunakan perintah CREATE TABLE :

CREATE TABLE pasien (
   nama TEXT,
   alamat TEXT,
   kode_wilayah TEXT(10),
   kelamin CHARACTER,
   umur SMALLINT
);

Kemudian kita akan mengisinya dengan data menggunakan perintah INSERT :
[catatan: untuk jenis kelamin L=laki-laki, P=perempuan]

INSERT INTO pasien (nama,alamat,kode_wilayah,kelamin,umur)
   VALUES ('John','Jl. Enau No. 99','3471000000','L','35');

INSERT INTO pasien (nama,alamat,kode_wilayah,kelamin,umur)
   VALUES ('Jane','Jl. Melati No. 1','3471000000','P','30');

INSERT INTO pasien (nama,alamat,kode_wilayah,kelamin,umur)
   VALUES ('George','Jl. Mangga No. 33','3404000000','L','30');

INSERT INTO pasien (nama,alamat,kode_wilayah,kelamin,umur)
   VALUES ('Silvy','Jl. Bambu No. 11','3402000000','P','20');

INSERT INTO pasien (nama,alamat,kode_wilayah,kelamin,umur)
   VALUES ('Tommy','Jl. Beringin No. 55','3402000000','L','25');

Kemudian kita akan sedikit bermain-main dengan data yang telah kita simpan di dalam tabel tadi. Kita akan melakukan query (mengambil data) dengan perintah SELECT:

Tampilkan seluruh data pasien:

SELECT * FROM pasien;

hasilnya:

+--------+---------------------+--------------+---------+------+
| nama   | alamat              | kode_wilayah | kelamin | umur |
+--------+---------------------+--------------+---------+------+
| John   | Jl. Enau No. 99     | 3471000000   | L       |   35 |
| Jane   | Jl. Melati No. 1    | 3471000000   | P       |   30 |
| George | Jl. Mangga No. 33   | 3404000000   | L       |   30 |
| Silvy  | Jl. Bambu No. 11    | 3402000000   | P       |   20 |
| Tommy  | Jl. Beringin No. 55 | 3402000000   | L       |   25 |
+--------+---------------------+--------------+---------+------+

Tampilkan yang berjenis kelamin laki-laki saja:

SELECT * FROM pasien WHERE kelamin = 'L';

hasilnya:

+--------+---------------------+--------------+---------+------+
| nama   | alamat              | kode_wilayah | kelamin | umur |
+--------+---------------------+--------------+---------+------+
| John   | Jl. Enau No. 99     | 3471000000   | L       |   35 |
| George | Jl. Mangga No. 33   | 3404000000   | L       |   30 |
| Tommy  | Jl. Beringin No. 55 | 3402000000   | L       |   25 |
+--------+---------------------+--------------+---------+------+

Tampilkan nama pasien yang berumur 30 tahun:

SELECT nama FROM pasien WHERE umur = '30';

hasilnya:

+--------+
| nama   |
+--------+
| Jane   |
| George |
+--------+

Tampilkan kolom/field nama dan umur saja:

SELECT nama,umur FROM pasien;

hasilnya:

+--------+------+
| nama   | umur |
+--------+------+
| John   |   35 |
| Jane   |   30 |
| George |   30 |
| Silvy  |   20 |
| Tommy  |   25 |
+--------+------+

Langkah 2 : Tabel Wilayah

+--------------+-----------------+
| kode_wilayah | nama_wilayah    |
+--------------+-----------------+
| 3401000000   | KULON PROGO     |
| 3402000000   | BANTUL          |
| 3403000000   | GUNUNG KIDUL    |
| 3404000000   | SLEMAN          |
| 3471000000   | YOGYAKARTA      |
+--------------+-----------------+

Ada pertanyaan:

1. Mengapa kita perlu kode wilayah?
2. Mengapa tidak langsung saja nama wilayah diikutkan sekalian dalam kolom alamat?

Jawab:
Kita bisa saja memasukkan nama wilayah didalam alamat, namun kita akan kesulitan nantinya pada saat mengolah data. Kesulitan itu timbul dikarenakan data menjadi kurang terstruktur (informasi wilayah bercampur dengan alamat). Selain itu, kode yang dimasukkan disini juga bukan asal dibuat, namun sudah dibakukan sebagai kode wilayah standar oleh pemerintah Indonesia melalui Biro Pusat Statistik. Keistimewaan yang kita peroleh nantinya dengan memasukkan wilayah dalam bentuk kode ialah kemudahan untuk bertukar data dengan institusi-institusi mau pun peneliti-peneliti yang lain yang menggunakan kode standar tersebut. Keunggulan lainnya ialah query menjadi lebih optimal dan cepat.

Kita akan membuat tabel wilayah dengan perintah:

CREATE TABLE wilayah (
   kode_wilayah TEXT(10),
   nama_wilayah TEXT(60)
);

Kemudian mengisinya dengan data menggunakan perintah:

INSERT INTO wilayah (kode_wilayah,nama_wilayah)
   VALUES('3401000000','KULON PROGO');
INSERT INTO wilayah (kode_wilayah,nama_wilayah)
   VALUES('3402000000','BANTUL');
INSERT INTO wilayah (kode_wilayah,nama_wilayah)
   VALUES('3403000000','GUNUNG KIDUL');
INSERT INTO wilayah (kode_wilayah,nama_wilayah)
   VALUES('3404000000','SLEMAN');
INSERT INTO wilayah (kode_wilayah,nama_wilayah)
   VALUES('3471000000','YOGYAKARTA');

Kita periksa data yang telah kita masukkan dengan perintah:

SELECT * FROM wilayah;

hasilnya:

+--------------+--------------+
| kode_wilayah | nama_wilayah |
+--------------+--------------+
| 3401000000   | KULON PROGO  |
| 3402000000   | BANTUL       |
| 3403000000   | GUNUNG KIDUL |
| 3404000000   | SLEMAN       |
| 3471000000   | YOGYAKARTA   |
+--------------+--------------+

Setelah ini, kita akan mencoba metode-metode penggalian informasi yang lebih canggih pada tutorial SQL bagian kedua.

Bersambung.