RSS

The Query Master

15 Mei

Dibutuhkan seorang programmer Java, PHP, Delphi, Phyton, Ruby, dan berbagai language .Net.


Lowongan pekerjaan seperti itu acapkali membuat keringan dingin bercucuran. Kapan mulai bekerja kalau semua lowongan pekerjaan seperti itu? Tetapi paling tidak, jika aplikasi yang dibuat menyangkut pengolahan database dan Anda telah menguasai penggunaan query-query SQL, Anda telah memiliki bekal yang baik untuk masuk ke dalam lingkungan bahasa pemrograman yang baru.

Sebaliknya, jika Anda masih kesulitan memahami konsep dan penggunaan query sementara aplikasi yang Anda buat membutuhkan berbagai bentuk pengolahan dan pelaporan database, tentunya proses kerja akan mandek, apapun user interface dan bahasa pemrograman yang digunakan.

Untuk itu, rasanya gelar The Query Master harus Anda raih untuk dapat menghasilkan aplikasi database yang baik. Contoh-contoh kasus yang umum sering terjadi, akan dipapartkan dalam artikel ini.

 

 


SQL DDL dan DML

Sebelum melangkah dalam pembahasan yang lebih jauh, perlu ditekankan bahwa sebuah query SQL dapat berupa DDL (Data Definition Language), ataupun DML (Data Manipulation Language). DDL berisi perintah-perintah yang berkaitan dengan struktur data, contohnya seperti pada tabel berikut:

TABEL: PERINTAH DDL
Perintah Fungsi
CREATE DATABASE Menciptakan sebuah database baru
ALTER DATABASE Modifikasi database
CREATE TABLE Menciptakan sebuah table baru
ALTER TABLE Modifikasi table
DROP TABLE Menghapus table
CREATE INDEX Menciptakan index
DROP INDEX Menghapus index

Sedangkan, DML merupakan perintah-perintah untuk pengolahan/manipulasi data, yaitu:

TABEL: PERINTAH DML
Perintah Fungsi
SELECT Menampilkan data
UPDATE Update/edit data
DELETE Menghapus data
INSERT INTO Menambah data

Kasus-kasus yang perlu dihadapi oleh The Query Master ditunjukkan pada skenario-skenario fiktif berikut, siapa tahu dapat menjadi inspirasi bagi Anda yang menghadapi kasus sejenis! Sekadar catatan, seluruh query pada artikel ini telah dicoba dan berjalan baik pada MySQL.

 


Kenaikan Gaji

Setelah 10 tahun tertunda, PT. Suka Duit memutuskan untuk menaikkan gaji seluruh karyawan. Setelah melalui perhitungan rumit, maka dikeluarkan rumus kenaikan gaji yang dicantumkan dalam tabel sebagai berikut:

TABEL: RUMUS KENAIKAN GAJI
Range Gaji Kenaikan
Dibawah 20.000 500
20.000 sampai 30.000 750
Diatas 30.000 2.000

Jadi field gaji pada table karyawan harus di-update dengan gaji terbaru. Untuk itu Anda dapat menggunakan perintah UPDATE. Tetapi hati-hati, karena keseluruhan record di dalam table dapat berubah seketika, Anda tentu tidak ingin nasib The Query Master berakhir di tangan PT. Suka Duit (jika query Anda mengakibatkan perusahaan rugi) atau berakhir di tangan para karyawan (jika query Anda mengakibatkan karyawan rugi).

Karena itu, simulasikan terlebih dahulu query Anda. Dalam kasus ini, salah satu caranya adalah dengan membandingkan gaji saat ini dengan gaji yang telah dihitung ulang, dengan menggunakan perintah SELECT. Perintah selengkapnya adalah sebagai berikut:

SELECT gaji, CASE WHEN gaji < 20000
THEN gaji + 500
WHEN gaji BETWEEN 20000 AND 30000
THEN gaji + 750
WHEN gaji > 30000
THEN gaji + 2000 END AS gaji_setelah_naik FROM karyawan

Akan tampil dua field, yaitu gaji dan gaji_setelah_naik, yang dapat Anda bandingkan apakah query telah dijalankan dengan benar. Contoh hasilnya seperti pada tabel berikut:

TABEL: KARYAWAN
GAJI GAJI SETELAH NAIK
8000 6500
16000 16500
21500 22250
24500 25250
49000 51000

Perhatikan bahwa query tersebut menggunakan perintah CASE WHEN yang berfungsi untuk memberikan hasil sesuai dengan kondisi yang diberikan. Setelah yakin dengan hasilnya, Anda dapat menjalankan perintah UPDATE sebagai berikut:

UPDATE karyawan SET gaji = CASE WHEN gaji < 20000 THEN gaji + 500
WHEN gaji BETWEEN 20000 AND 30000 THEN gaji + 750
WHEN gaji > 30000 THEN gaji + 2000

Selesai menjalankan query tersebut, minta seluruh karyawan PT. Suka Duit untuk mentraktir Anda🙂


Laporan Transaksi

Pimpinan PT Suka Duit tentu ingin melihat performa perusahaan yang dapat tercermin dari kegiatan transaksi yang dilakukan setiap bulannya. Field-field inti table transaksi mungkin seperti pada table berikut:

TABEL: KARYAWAN
FIELD TIPE DATA
id_trx Int (Primary Key)
kode_costumer Varchar(10)
tgl_transaksi Date/Time
total_order Int

Instruksinya sekarang, tampilkan jumlah transaksi dalam tiga bulan terakhir dalam tahun yang sama. Sebuah instruksi memang biasanya jauh lebih singkat dibandingkan proses yang harus dilakukan. Query yang dapat Anda gunakan untuk keperluan tersebut adalah sebagai berikut:

SELECT monthname(DATE_ADD(curdate(), INTERVAL -2 MONTH)) AS bulan,
count(id_trx) AS jml_transaksi FROM transaksi
WHERE month(tgl_transaki) = month(curdate()) - 2
AND year(tgl_transaksi) = year(curdate())
UNION
SELECT monthname(DATE_ADD(curdate(), INTERVAL -1 MONTH)) AS bulan,
count(id_trx) AS jml_transaksi FROM transaksi
WHERE month(tgl_transaksi) = month(curdate()) - 1
AND year(tgl_transaksi) = year(curdate())
UNION
SELECT monthname(curdate()) AS bulan
count(id_trx) AS jml_transaksi FROM transaksi
WHERE month(tgl_transaksi) = month(curdate())
AND year(tgl_transaksi) = year(curdate())

Query yang cukup panjang ini mungkin hanya menghasilkan informasi singkat sebagai berikut:

TABEL: TRANSAKSI
BULAN JML_TRANSAKSI
January 30
February 20
March 10

Beberapa function dari perintah penting pada query di atas adalah:

  1. curdate(). Function yang menghasilkan tanggal saat ini (sesuai dengan tanggal server).
  2. DATE_ADD. Function ini menambahkan jangka waktu tertentu pada sebuah tanggal. Contohnya bila hari ini tanggal 15 Mei 2010 atau ditampilkan 2010-05-15 pada MySQL, maka perintah SELECT DATE_ADD(curdate(), INTERVAL 1 MONTH) akan menampilkan 2010-06-15, alias ditambahkan 1 bulan. Jika Anda ingin mengurangi, gunakan angka minus pada INTERVAL.
  3. UNION. Merupakan operator di dalam SQL yang berfungsi untuk menggabungkan hasil dua atau lebih perintah SELECT, di mana masing-masing perintah SELECT tersebut harus memiliki jumlah kolom, tipe data, dan urutan yang sama.


Saat Rumput Tetangga Lebih Hijau

Setelah melihat laporan transaksi yang ternyata menunjukkan penurunan performa, PT. Suka Duit mulai melihat bahwa “rumput tetangga tampak lebih hijau”, dan melihat perusahaan tetangga alias kompetitor lebih bersinar.

PT. Suka Duit mulai membandingkan produknya dengan kompetitor, tetapi perbandingan manual susah dilakukan karena sedemikian banyak produk yang dikeluarkan oleh PT. Suka Duit maupun kompetitornya, yang menjual kacang goreng sampai kapal pesiar.

Karena itu, dibentuk team khusus untuk menginput produk-produk kompetitor ke dalam sebuah table bernama produk_kompetitor, lalu Anda diminta untuk membandingkan produk apa saja dari kompetitor yang tidak diproduksi oleh PT. Suka Duit. Untuk memenuhi permintaan tersebut, dibuatlah query sebagai berikut:

SELECT nama_produk FROM produk_kompetitor WHERE nama_produk NOT
IN (
SELECT nama_produk from produk
)

Maka akan tampil nama produk yang di ambil dari table produk kompetitor, yang tidak ada di dalam table produk (milik perusahaan PT. Suka Duit).


Customer yang Menjadi Raja

Customer adalah raja (hanya jika customer tersebut banyak membeli), rupanya diyakini oleh pimpinan PT. Suka Duit. Jumlah transaksi yang menurun dalam tiga bulan terakhir membuat pimpinan ingin melihat siapa saja customer yang masih setia membeli dalam jumlah banyak.

Karena itu, instruksi susulan adalah menampilkan peringkat customer yang memiliki total order paling banyak, dengan data diambil dari table Transaksi yang telah dibahas sebelumnya.

Query yang dapat digunakan untuk keperluan ini adalah sebagai berikut:

SELECT kode_costumer, SUM(total_order)
FROM transaksi
GROUP BY kode_customer
ORDER BY SUM(total_order) DESC

Akan tampil daftar kode customer dan penjumlahan total order yang dilakukan customer tersebut, yang diurutkan mulai dari total order yang terbesar. Jika ada kondisi lain, misalnya menampilkan total order yang lebih besar dari 200, maka Anda harus menggunakan HAVING, sehingga query menjadi:

SELECT kode_costumer, SUM(total_order)
FROM transaksi
GROUP BY kode_customer
HAVING sum (total_order) > 200
ORDER BY SUM(total_order) DESC


Rapat Penting

Pimpinan mengambil keputusan untuk mengadakan rapat yang sangat penting dan rahasia, dan hanya menghadirkan mereka yang menduduki kursi manajer. Tetap ada satu permasalahan, yaitu sang pimpinan tidak mengetahui siapa saja yang memiliki posisi manajer, di antara sekian banyak karyawan.

Untungnya, semua data karyawan ada pada tabel karyawan. Hanya saja, ternyata pada table karyawan tersebut hanya menyimpan field-field inti seperti pada table di bawah ini, berikut contoh datanya:

TABEL: KARYAWAN
NAMA ID KARYAWAN ID_MANAGER
Budi 1 2
Ratna 2 3
Tommy 3 0
Gerry 4 2
Chucky 5 2

Pada table tersebut ditunjukkan, setiap karyawan memiliki id_karyawan, setiap karyawan juga memiliki manajer yang ditunjukkan oleh field id_manager. Contohnya ID karyawan Budi adalah 1, sementara ID karyawan Ratna adalah 2. ID manager Budi adalah 2 (Ratna), artinya Ratna adalah manajernya Budi.

Hanya dengan berbekal data dan angka ID di atas, tentunya memeriksa hubungan karyawan dan manajernya merupakan hal yang rumit, apalagi juga terdapat banyak record. Untuk itu, query berikut akan menampilkan nama karyawan dan nama manajernya.

SELECT t1.nama AS nama_karyawan,
t2.nama AS nama_manager
FROM karyawan t1, karyawan t2
WHERE t1.id_manager = t2.id_karyawan

Hasil dari query di atas adalah:

TABEL: KARYAWAN
NAMA_KARYAWAN NAMA_MANAJER
Budi Ratna
Ratna Tommy
Gerry Ratna
Chucky Ratna

Perhatikan query di atas menggunakan apa yang disebut dengan teknik self join. Biasanya Anda mengenal perintah JOIN untuk menghubungkan dua table yang berbeda, tetapi self join justru menghubungkan dua table yang sama.

Dengan sedikit modifikasi query di atas, Anda dapat menghasilkan nama-nama yang memiliki posisi manajer saja, yang berhak mengikuti rapat. Query-nya adalah sebagai berikut:

SELECT DISTINCT t2.nama AS nama_manajer
FROM karyawan t1, karyawan t2
WHERE t1.id_manajer = t2.id_karyawan

Query di atas menghasilkan nama manager sebagai berikut:

TABEL: KARYAWAN
NAMA_MANAJER
Ratna
Tommy


Query Adalah Seni

Masih terdapat banyak sekali contoh kasus dan teknik query yang beragam, sayangnya tidak semuanya dapat ditampilkan di sini. Membuat query merupakan suatu seni, satu kasus mungkin dapat dipecahkan dengan berbagai cara dan query yang berbeda.

Jangan ragu untuk mengeksplorasi dan belajar perintah yang belum Anda pahami, karena sekali Anda menguasai perintah tersebut, bisa jadi banyak masalah yang mampu Anda pecahkan. Selamat menjadi Query Master, yang mampu beraksi menggunakan berbagai database relasional.

 
2 Komentar

Ditulis oleh pada 15 Mei 2010 in Pemrograman, Tips & Trik, Uncategorized

 

Tag: , , , , , , , , , , , , , , , , ,

2 responses to “The Query Master

  1. Registration software

    19 September 2012 at 5:04 pm

    Wow, fantastic blog layout! How long have you been blogging for?

    you make blogging look easy. The overall look of your site is magnificent, as
    well as the content!

     
  2. http://www.pinterest.com/

    11 Desember 2012 at 12:35 pm

    When you have a minor assert, establish whether it is worth every penny to submit along with
    your vehicle insurance organization. While driving, the owner is also responsible for the security of the fellow passengers.
    At this time this has to be understood that the sum of insurance plan differ from time to time simply because insurance providers also have to abide by the state insurance plan legal guidelines.

     

Tinggalkan Balasan

Isikan data di bawah atau klik salah satu ikon untuk log in:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

 
%d blogger menyukai ini: