Belajar SQL - ArungRuang

Contoh Penggunaan JOIN dalam Perintah SQL SELECT

superadmin January 15, 2017 No Comments

[Belajar SQL](http://pojokprogrammer.net/tags/belajar-sql) | Setelah sebelumnya kita anatomi dan penggunaan perintah SQL SELECT untuk melakukan proses temu kembali data (*data retrieval*), maka langkah selanjutnya kita harus mempertajam ***Skill SQL***, dengan memperbanyak latihan, dan salah satu cara untuk melatih *skill SQL* adalah dengan cara mencoba membantu menyelesaikan problem SQL yang dihadapi orang lain. Beberapa situs yang bisa kita kunjungi untuk melatih *skill SQL* adalah [Oracle Community Forum](https://community.oracle.com/community/database/developer-tools/sql_and_pl_sql), namun berbahasa Inggris. Sedangkan untuk yang berbahasa Indonesia bisa mengunjungi [Programmer Forum di Kaskus](https://www.kaskus.co.id/thread/000000000000000007584619/sql-yang-punya-problem-sql-kumpul-disini-gan-no-urgent-please/). Dalam artikel ini kita akan membahas beberapa kasus berkaitan dengan SQL JOIN.
(more…)

Temu Kembali Data (Data Retrieval) Menggunakan SELECT

superadmin January 5, 2017 No Comments

**Bagian ketiga dari rangkaian artikel tentang Konsep Database Relasional.** Jika pada bagian kedua (***Membuat Model Basis Data***) kita sudah membahas bagaimana menganalisa dan membuat design model basis data (*database model*) mulai dari model konsep (*conceptual model*), model logis (*logical model*), sampai menjadi model fisik (*physical model*). Maka di artikel ini kita akan fokus pada anatomi dan penggunaan perintah SQL SELECT — atau biasa disebut sebagai *query* — untuk melakukan proses temu kembali data (*data retrieval*). Kita akan menggunakan database PostgreSQL dan pgAdmin3 dalam artikel ini, namun tidak ada halangan untuk mempraktekkannya menggunakan database MySQL, karena secara umum sintaks SQL di semua RDBMS sama.

(more…)

Membuat Model Basis Data

superadmin July 23, 2016 No Comments

Sebelum kita membuat desain sebuah database, maka pertama-tama yang perlu kita lakukan ***requirements gathering***, yaitu mengumpulkan semua kebutuhan dan keinginan para pengguna (*users*) kemudian melakukan analisa terhadap kebutuhan dan keinginan tersebut. Salah satu *kebutuhan* yang harus didapatkan dalam proses analisa ini adalah *data apa saja* yang ingin disimpan ke dalam database. Seorang analis sistem (*system analyst*) kemudian harus dapat mengidetifikasikan apa saja entitas (*entity*) yang terlibat dalam sistem yang ingin dikembangkan, kemudian membuat ***Conceptual Model*** berupa relasi antar entitas tersebut. Dan juga pembuatan diagram konseptual (*conceptual model*) menjadi diagram logikal (*logical model*), dan akhirnya menjadi diagram fisik (*physical model*).

**Bagian kedua dari rangkaian artikel tentang [konsep database relational dan dasar-dasar SQL](http://pojokprogrammer.net/content/konsep-database-relasional-dan-bahasa-sql)**

(more…)

Atur Data Anda

superadmin July 10, 2016 No Comments

[**Belajar SQL**](http://pojokprogrammer.net/tags/belajar-sql) | Dalam database relasional, ada dua hal yang perlu kita pahami, yaitu **normalisasi** data, dan **relasi antar tabel**. Tujuan dari normalisasi adalah menghilangkan rudundansi dan duplikasi data, sehingga kita bisa menghidari kemungkinan masalah saat proses manipulasi data. Aturan-aturan yang harus diikuti pada saat kita mendisain sebuah database agar kita bisa terhindar dari duplikasi data inilah yang disebut normalisasi.

**Bagian pertama dari rangkaian artikel tentang [konsep database relational dan dasar-dasar SQL](http://pojokprogrammer.net/content/konsep-database-relasional-dan-bahasa-sql)**

(more…)

Konsep Database Relasional dan Bahasa SQL

superadmin July 9, 2016 No Comments

[**Belajar SQL**](http://pojokprogrammer.net/tags/belajar-sql) | Seiring dengan kebutuhan aplikasi-aplikasi bisnis dan layanan publik masa kini, maka kebutuhan penyimpanan data dalam bentuk database relasional menjadi hal yang tidak terelakkan, sehingga pemahaman yang baik tentang ***structured query language*** (***SQL***) menjadi hal yang **amat sangat penting** karena SQL adalah bahasa yang kita gunakan untuk *berkomunikasi* dengan database. Sayangnya kenyataan berkata lain, peningkatan skill SQL menjadi prioritas paling akhir karena kebanyakan programmer terlalu fokus pada hal lain yang lebih *seksi*, seperti, meningkatkan skill bahasa pemrograman tertentu, membuat user interface yang atraktif dan user-friendly, dan membuat aplikasi yang bisa berjalan di database apapun

(more…)

Belajar SQL: Perhitungan Stok dan Harga Rata-Rata Barang untuk Aplikasi Inventory

superadmin December 29, 2013 No Comments

Perhitungan Stok dan Harga Rata-Rata Barang untuk Aplikasi Inventory Satu Perintah Single SQL Statement

Belajar SQL | Semua programmer atau software developer aplikasi perkantoran pasti pernah menggunakan SQL. minimal untuk proses CRUD (Create, Read, Update, Delete) sederhana. Namun hanya sedikit yang memanfaatkan SQL untuk proses perhitungan rumit seperti penggajian, perhitungan average harga barang, status inventory, dan sebagainya hanya dengan menggunakan Single SQL Statement, alias dengan satu perintah SQL saja. Beberapa waktu lalu saat diskusi online dengan kawan-kawan di Facebook, saya tergelitik untuk membuat contoh SQL beraksi untuk menghitung nilai stok dan harga rata-rata barang setelah terjadi proses keluar masuk barang pada bulan tertentu. Contoh kasusnya adalah seperti ini…..

Di Ketahui ;

QOB := 1.670,28 Kg ( Opening Balance )
vOB := Rp. 8.397.476,7 ( Value Opening Balance 

Akumulasi selama 1 bulan dari pembelian ( Harus Proses seluruh transaksi masuk )
SQB := 1.600,Kg ( Supply quantity Barang )
vSQB := Rp. 8.825.600 ( Value Quantity Barang )

Akumulasi pemakain material selam 1 bulan ( Harus Proses seluruh Transaksi Keluar )
uQB := 973,83 ( Used Quantity Balance )

Di Tanya ;

vuQB : …..?? ( Value used Quatity Balance )
SAB : …… ( Saldo akhir balance )
vSAB : ……..( Value saldo akhir balance )

Jawab ;

Average Bahan Baku := (( vSQB+vOB) / (QOB+SQB) )
vuQB := uQB * average bahan baku 
SAB := (( QOB+SQB)-UQB)
vSAB := SAB * average bahan baku

Ketika kita berinteraksi dengan database, maka ada Golden Rule yang harus selalu dipegang. Golden Rule ini disampaikan oleh Tom Kyte, Evangelist Oracle, dalam salah satu artikel di blog-nya

Sebisa mungkin, selalu gunakan satu perintah SQL.
Jika tidak bisa dalam satu perintah SQL, maka gunakan Stored Procedure
Jika tidak bisa menggunakan Stored Procedure, lakukan di aplikasi kita
Jika masih tidak bisa juga, mungkin kita perlu memikirkan masak-masak, sebenarnya perlu gak sih kita lakukan hal ini? 🙂

Untuk itu saya akan coba menyelesaikan problem di atas hanya menggunakan satu perintah SQL saja. Namun saya akan jelaskan langkah-demi-langkah dalam membangun "satu perintah SQL" tersebut, agar teman-teman bisa memahami algoritma dan logika yang ada di dalamnya. Untuk kasus ini saya akan menggunakan PostgreSQL untuk menyelesaikannya, namun hasil akhir adalah perintah SQL standar yang bisa digunakan di RDBMS lain. Mari kita mulai dengan membuka command line PostgreSQL ………………..

C:\Program Files\PostgreSQL\9.3\bin>psql -U postgres -d inventory
psql (9.3.1)
WARNING: Console code page (850) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

Untuk menyelesaikan kasus di atas kita memerlukan beberapa buah table, yaitu

table items untuk menampung data master barang.
table item_balances untuk menampung data saldo awal bulanan tiap-tiap barang.
table transactions untuk mencatat transaksi harian, keluar dan masuk barang.

Pertama-tama kita buat struktur table items.

inventory=# -- -----------------------
inventory=# -- buat table items
inventory=# -- -----------------------
inventory=# create table items (
inventory(#   id serial primary key,
inventory(#   code varchar(20) not null,
inventory(#   name varchar(200) null
inventory(# ) ;
CREATE TABLE

Kemudian isikan beberapa data menggunakan perintah insert into. dan tampilkan hasilnya untuk memastikan.

inventory=# -- -----------------------
inventory=# -- masukkan beberapa data
inventory=# -- -----------------------
inventory=# insert into items (code, name)
inventory-# select 'ITEM01', 'Barang Pertama' union all
inventory-# select 'ITEM02', 'Barang Kedua' union all
inventory-# select 'ITEM03', 'Barang Ketiga' 
inventory-# ;
INSERT 0 3

inventory=# -- -----------------------
inventory=# -- lihat hasilnya
inventory=# -- -----------------------
inventory=# select * from items 
inventory-# ;

 id |  code  |      name
----+--------+----------------
  1 | ITEM01 | Barang Pertama
  2 | ITEM02 | Barang Kedua
  3 | ITEM03 | Barang Ketiga
(3 rows)

Berikutnya kita siapkan table item_balances yang digunakan untuk menyimpan saldo awal dan harga barang setiap bulannya. untuk itu perlu field periode untuk identifikasi bulan.

inventory=# -- -----------------------
inventory=# -- buat table balance
inventory=# -- -----------------------
inventory=# create table item_balances (
inventory(#   id serial primary key,
inventory(#   item_id integer not null,
inventory(#   period date not null,
inventory(#   quantity numeric(15,2),
inventory(#   unit_price numeric(15,2),
inventory(#   foreign key (item_id) references items(id)
inventory(# ) ;
CREATE TABLE

Kemudian isikan beberapa data menggunakan perintah insert into. dan tampilkan hasilnya untuk memastikan.

inventory=# -- -----------------------
inventory=# -- masukkan beberapa data
inventory=# -- -----------------------
inventory=# insert into item_balances (item_id, period, quantity, unit_price)
inventory-# select 1, to_date('2013-12-01','YYYY-MM-DD'), 1600, 23125 union all
inventory-# select 2, to_date('2013-12-01','YYYY-MM-DD'), 1250, 13100 union all
inventory-# select 3, to_date('2013-12-01','YYYY-MM-DD'), 3250, 3100 
inventory-# ;
INSERT 0 3

inventory=# -- -----------------------
inventory=# -- lihat hasilnya
inventory=# -- -----------------------
inventory=# select * from item_balances 
inventory-# ;

 id | item_id |   period   | quantity | unit_price
----+---------+------------+----------+------------
  1 |       1 | 2013-12-01 |  1600.00 |   23125.00
  2 |       2 | 2013-12-01 |  1250.00 |   13100.00
  3 |       3 | 2013-12-01 |  3250.00 |    3100.00
(3 rows)

Karena desain database kita sudah mengikuti kaidah normalisasi, maka yang tersimpan di table item_balances hanya id barang saja, Untuk mengetahui nama barang bersangkutan, kita gunakan klausa JOIN seperti di bawah ini…

inventory=# -- -----------------------
inventory=# -- tambahkan klausa JOIN
inventory=# -- -----------------------
inventory=# select a.*, b.period, b.quantity, b.unit_price
inventory-# , b.quantity*b.unit_price as item_value
inventory-# from items a join item_balances b on a.id=b.item_id 
inventory-# ;

 id |  code  |      name      |   period   | quantity | unit_price |  item_value
----+--------+----------------+------------+----------+------------+---------------
  1 | ITEM01 | Barang Pertama | 2013-12-01 |  1600.00 |   23125.00 | 37000000.00
  2 | ITEM02 | Barang Kedua   | 2013-12-01 |  1250.00 |   13100.00 | 16375000.00
  3 | ITEM03 | Barang Ketiga  | 2013-12-01 |  3250.00 |    3100.00 | 10075000.00
(3 rows)

Sekarang buatkan table transactions untuk menampung data transaksi harian, meliputi transaksi masuk dan keluar. Untuk membedakan transaksi masuk atau keluar, kita sediakan satu field tx_type untuk membedakannya.

inventory=# -- -----------------------
inventory=# -- buat table transaksi
inventory=# -- -----------------------
inventory=# create table transactions (
inventory(#   id serial primary key,
inventory(#   item_id integer not null,
inventory(#   tx_date date not null,
inventory(#   tx_type varchar(10) not null,
inventory(#   quantity numeric(15,2),
inventory(#   unit_price numeric(15,2),
inventory(#   foreign key (item_id) references items(id)
inventory(# ) ;
CREATE TABLE

Kemudian isikan beberapa data menggunakan perintah insert into. dan tampilkan hasilnya untuk memastikan. Kita tambahkan klausa JOIN untuk mengetahui informasi lebih jelas tentang barang yang terlibat dalam transaksi yang kita masukkan

inventory=# -- -----------------------
inventory=# -- masukkan beberapa data
inventory=# -- -----------------------
inventory=# insert into transactions (item_id, tx_date, tx_type, quantity, unit_price)
inventory-# select 1, to_date('2013-12-10','YYYY-MM-DD'), 'IN', 100, 24125 union all
inventory-# select 1, to_date('2013-12-20','YYYY-MM-DD'), 'IN', 200, 25125 union all
inventory-# select 1, to_date('2013-12-05','YYYY-MM-DD'), 'OUT', 100, 0 union all
inventory-# select 1, to_date('2013-12-08','YYYY-MM-DD'), 'OUT', 200, 0 union all
inventory-# select 1, to_date('2013-12-18','YYYY-MM-DD'), 'OUT', 400, 0 union all
inventory-# select 2, to_date('2013-12-02','YYYY-MM-DD'), 'IN', 1000, 15125 union all
inventory-# select 2, to_date('2013-12-23','YYYY-MM-DD'), 'IN', 2000, 14100 union all
inventory-# select 2, to_date('2013-12-04','YYYY-MM-DD'), 'OUT', 1100, 0 union all
inventory-# select 2, to_date('2013-12-05','YYYY-MM-DD'), 'OUT', 1200, 0 union all
inventory-# select 2, to_date('2013-12-15','YYYY-MM-DD'), 'OUT', 400, 0 union all
inventory-# select 3, to_date('2013-12-12','YYYY-MM-DD'), 'IN', 1000, 3500 union all
inventory-# select 3, to_date('2013-12-24','YYYY-MM-DD'), 'IN', 2000, 3700 union all
inventory-# select 3, to_date('2013-12-04','YYYY-MM-DD'), 'OUT', 1100, 0 union all
inventory-# select 3, to_date('2013-12-15','YYYY-MM-DD'), 'OUT', 1200, 0 union all
inventory-# select 3, to_date('2013-12-25','YYYY-MM-DD'), 'OUT', 2400, 0 
inventory-# ;
INSERT 0 15

inventory=# -- -----------------------
inventory=# -- lihat hasilnya, pakai JOIN
inventory=# -- -----------------------
inventory=# select a.*, b.code, b.name
inventory-# from transactions a
inventory-# join items b on a.item_id=b.id
inventory-# order by tx_date, tx_type 
inventory-# ;

 id | item_id |  tx_date   | tx_type | quantity | unit_price |  code  |      name
----+---------+------------+---------+----------+------------+--------+----------------
  6 |       2 | 2013-12-02 | IN      |  1000.00 |   15125.00 | ITEM02 | Barang Kedua
  8 |       2 | 2013-12-04 | OUT     |  1100.00 |       0.00 | ITEM02 | Barang Kedua
 13 |       3 | 2013-12-04 | OUT     |  1100.00 |       0.00 | ITEM03 | Barang Ketiga
  3 |       1 | 2013-12-05 | OUT     |   100.00 |       0.00 | ITEM01 | Barang Pertama
  9 |       2 | 2013-12-05 | OUT     |  1200.00 |       0.00 | ITEM02 | Barang Kedua
  4 |       1 | 2013-12-08 | OUT     |   200.00 |       0.00 | ITEM01 | Barang Pertama
  1 |       1 | 2013-12-10 | IN      |   100.00 |   24125.00 | ITEM01 | Barang Pertama
 11 |       3 | 2013-12-12 | IN      |  1000.00 |    3500.00 | ITEM03 | Barang Ketiga
 14 |       3 | 2013-12-15 | OUT     |  1200.00 |       0.00 | ITEM03 | Barang Ketiga
 10 |       2 | 2013-12-15 | OUT     |   400.00 |       0.00 | ITEM02 | Barang Kedua
  5 |       1 | 2013-12-18 | OUT     |   400.00 |       0.00 | ITEM01 | Barang Pertama
  2 |       1 | 2013-12-20 | IN      |   200.00 |   25125.00 | ITEM01 | Barang Pertama
  7 |       2 | 2013-12-23 | IN      |  2000.00 |   14100.00 | ITEM02 | Barang Kedua
 12 |       3 | 2013-12-24 | IN      |  2000.00 |    3700.00 | ITEM03 | Barang Ketiga
 15 |       3 | 2013-12-25 | OUT     |  2400.00 |       0.00 | ITEM03 | Barang Ketiga
(15 rows)

Langkah berikutnya adalah membuat query untuk emnghitung summary transaksi. Kita akan menggunakan teknik crosstab untuk memindahkan row transaksi IN dan OUT menjadi kolom.

inventory=# -- -----------------------
inventory=# -- crosstab data transaksi
inventory=# -- -----------------------
inventory=# select item_id
inventory-# , date_trunc('month',tx_date) tx_date
inventory-# , sum(case when tx_type='IN' then quantity else 0 end) qty_in
inventory-# , sum(case when tx_type='IN' then quantity*unit_price else 0 end) value_in
inventory-# , sum(case when tx_type='OUT' then quantity else 0 end) qty_out
inventory-# from transactions
inventory-# group by item_id
inventory-# , date_trunc('month',tx_date) ;

 item_id |        tx_date         | qty_in  |   value_in    | qty_out
---------+------------------------+---------+---------------+---------
       2 | 2013-12-01 00:00:00+07 | 3000.00 | 43325000.0000 | 2700.00
       1 | 2013-12-01 00:00:00+07 |  300.00 |  7437500.0000 |  700.00
       3 | 2013-12-01 00:00:00+07 | 3000.00 | 10900000.0000 | 4700.00
(3 rows)

Terakhir…… lakukan JOIN terhadal table items, item_balance dan hasil crosstab transactions untuk proses perhitungan sesuai dengan rumus yang diberikan di bagian awal artikel ini.

inventory=# -- -----------------------
inventory=# -- perhitungan akhir
inventory=# -- -----------------------
inventory=# select it.id, it.code, it.name
inventory-# , bl.period, bl.quantity QOB, bl.unit_price Avg1
inventory-# , ( bl.quantity *bl.unit_price ) vOB
inventory-# , tx.qty_in SQB, tx.value_in vSQB, tx.qty_out uQB
inventory-# , ( (tx.value_in +bl.quantity *bl.unit_price) / (bl.quantity +tx.qty_in) ) Avg2
inventory-# , tx.qty_out
inventory-# * ( (tx.value_in+bl.quantity*bl.unit_price) / (bl.quantity +tx.qty_in) ) vuQB
inventory-# , ( bl.quantity +tx.qty_in ) -tx.qty_out SAB
inventory-# , ( (bl.quantity +tx.qty_in) -tx.qty_out )
inventory-# * ( (tx.value_in +bl.quantity *bl.unit_price) / (bl.quantity +tx.qty_in) ) vSAB
inventory-# from items it
inventory-# join item_balances bl on it.id = bl.item_id
inventory-# left join (
inventory(#   select item_id
inventory(#   , date_trunc('month',tx_date) tx_date
inventory(#   , sum(case when tx_type='IN' then quantity else 0 end) qty_in
inventory(#   , sum(case when tx_type='IN' then quantity*unit_price else 0 end) value_in
inventory(#   , sum(case when tx_type='OUT' then quantity else 0 end) qty_out
inventory(#   from transactions
inventory(#   group by item_id
inventory(#   , date_trunc('month',tx_date)
inventory(# ) tx
inventory-# on bl.item_id = tx.item_id
inventory-# and bl.period = tx.tx_date
inventory-# ;

 id |  code  |      name      |   period   |   qob   |   avg1   |     vob     |   sqb   |     vsqb    |   uqb   |   avg2   |    vuqb     |   sab   |   vsab
----+--------+----------------+------------+---------+----------+-------------+---------+-------------+---------+----------+-------------+---------+-------------
  1 | ITEM01 | Barang Pertama | 2013-12-01 | 1600.00 | 23125.00 | 37000000.00 |  300.00 |  7437500.00 |  700.00 | 23388.16 | 16371712.00 | 1200.00 | 28065792.00
  2 | ITEM02 | Barang Kedua   | 2013-12-01 | 1250.00 | 13100.00 | 16375000.00 | 3000.00 | 43325000.00 | 2700.00 | 14047.06 | 37927062.00 | 1550.00 | 21772943.00
  3 | ITEM03 | Barang Ketiga  | 2013-12-01 | 3250.00 |  3100.00 | 10075000.00 | 3000.00 | 10900000.00 | 4700.00 |  3356.00 | 15773200.00 | 1550.00 |  5201800.00
(3 rows)

BERHASIL!!!!!!

Kita berhasil membuat satu perintah SQL untuk perhitungan status stok akhir bulan dan menghitung harga rata-ratanya. hasil dari perintah SQL ini sudah matang, sehingga aplikasi kita – PHP atau VB.net atau lainnya – bisa langsung menampilkan hasilnya tanpa perlu melakukan proses perhitungan lagi……..

Semoga berguna….

^_^

Belajar SQL: Query Sederhana Menghitung Status Dinamis

superadmin December 17, 2013 No Comments

Belajar SQL: Query Sederhana untuk Field Status Dinamis

Belajar SQL: | Kadangkala kita memerlukan sebuah field berubah nilainya seiring waktu berjalan. sebagai contoh kasus adalah untuk mengetahui apakah garansi suatu barang masih berlaku atau tidak. Salah satu caranya adalah dengan menyediakan field "status" untuk menyimpan nilai berlaku atau habis untuk mengetahui status garansi bersangkutan. Cara ini punya kerumitan karena kita perlu mengubah isinya. Maka, alih-alih menyimpan data status garansi dalam sebuah field khusus, maka akan lebih mudah jika kita cukup menyimpan akhir masa garansi dan menentukan status garansi menggunakan query. Metode ini dinamakan juga sebagai Computed Field.

Penggunaan Computed Field seperti ini akan jauh lebih mudah dan jauh lebih fleksible karena hasil query akan berubah sesuai perubahan hari. Silahkan pelajari langkah-langkah berikut ini untuk membangun query status garansi tersebut.

mysql> use test ;
Database changed
 
mysql> -- --------------------------------------
mysql> -- create table contoh
mysql> -- --------------------------------------
mysql> create table garansi (
    ->   id int auto_increment primary key,
    ->   awal_garansi date null,
    ->   akhir_garansi date null
    -> ) ;
Query OK, 0 rows affected (0.11 sec)
 
mysql> -- --------------------------------------
mysql> -- insert sample data
mysql> -- --------------------------------------
mysql> insert into
    -> garansi (awal_garansi, akhir_garansi)
    -> values ('2010-07-17', '2013-07-16')
    ->      , ('2011-09-24', '2014-09-23')
    ->      , ('2010-10-14', '2013-10-23')
    ->      , ('2012-01-14', '2015-01-23')
    -> ;
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> -- --------------------------------------
mysql> -- lihat isi sample data
mysql> -- --------------------------------------
mysql> select * from garansi ;
+----+--------------+---------------+
| id | awal_garansi | akhir_garansi |
+----+--------------+---------------+
|  1 | 2010-07-17   | 2013-07-16    |
|  2 | 2011-09-24   | 2014-09-23    |
|  3 | 2010-10-14   | 2013-10-23    |
|  4 | 2012-01-14   | 2015-01-23    |
+----+--------------+---------------+
4 rows in set (0.00 sec)
 
mysql> -- --------------------------------------
mysql> -- lakukan query
mysql> -- - tampilkan tanggal hari ini
mysql> -- - hitung selisih tanggal hari ini dengan akhir garansi
mysql> -- - dengan logika percabangan, 
mysql> --   => jika selisih positif artinya garansi sudah habis, 
mysql> --   => sebaliknya jika minus, maka garansi masih berlaku
mysql> -- --------------------------------------
mysql> select garansi.*
    -> , current_date tanggal
    -> , datediff(current_date,akhir_garansi) selisih
    -> , case
    ->   when datediff(current_date,akhir_garansi)>0
    ->        then 'habis'
    ->   else 'aktif' end status
    -> from garansi ;
+----+--------------+---------------+------------+---------+--------+
| id | awal_garansi | akhir_garansi | tanggal    | selisih | status |
+----+--------------+---------------+------------+---------+--------+
|  1 | 2010-07-17   | 2013-07-16    | 2013-12-17 |     154 | habis  |
|  2 | 2011-09-24   | 2014-09-23    | 2013-12-17 |    -280 | aktif  |
|  3 | 2010-10-14   | 2013-10-23    | 2013-12-17 |      55 | habis  |
|  4 | 2012-01-14   | 2015-01-23    | 2013-12-17 |    -402 | aktif  |
+----+--------------+---------------+------------+---------+--------+
4 rows in set (0.00 sec)
 
mysql> -- --------------------------------------
mysql> -- YAAAYYY !!! BERHASIL !!!
mysql> -- Silakan gunakan query terakhir dalam program php Anda
mysql> -- --------------------------------------

Walaupun contoh di atas menggunakan database MySQL, namun SQL yang ada bisa juga diterapkan di database lain seperti database PostgreSQL.

Semoga berguna………… (y)

Belajar SQL: Penggunaan SUM dan JOIN untuk membuat Laporan Summary

superadmin November 16, 2013 No Comments

Belajar SQL | Membuat laporan summary data dari table di database bisa dibilang gampang-gampang susah. Gampang ketika hanya membuat summary dengan sumber data satu buah table saja. Namun jadi makin rumit ketika summary melibatkan 2 (dua) table atau lebih. Silakan pelajari TUTORIAL berikut ini sebagai salah satu contoh kasus untuk membuat summary data dari 3 buah table terpisah.

Pertama-tama kita buat Table MASTER untuk sample data menggunakan perintah SQL CREATE TABLE … AS SELECT … kemudian tampilkan isinya untuk memastikan.

mysql> create table t_master as
    -> select 1 id, 'A' data union all
    -> select 2 id, 'B' data union all
    -> select 3 id, 'C' data ;
Query OK, 3 rows affected (0.19 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_master ;
+----+------+
| id | data |
+----+------+
|  1 | A    |
|  2 | B    |
|  3 | C    |
+----+------+
3 rows in set (0.00 sec)

Kemudian buat table TRANSAKSI pertama juga menggunakan perintah SQL CREATE TABLE … AS SELECT … kemudian tampilkan isinya untuk memastikan.

mysql> create table t_transaksi_1 as
    -> select 1 id, 'DOC 1' doc, 2000 value union all
    -> select 1 id, 'DOC 1' doc, 1000 value union all
    -> select 2 id, 'DOC 2' doc, 5000 value union all
    -> select 3 id, 'DOC 3' doc, 3000 value union all
    -> select 3 id, 'DOC 4' doc, 3000 value union all
    -> select 3 id, 'DOC 4' doc, 2000 value ;
Query OK, 6 rows affected (0.30 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t_transaksi_1 ;
+----+-------+-------+
| id | doc   | value |
+----+-------+-------+
|  1 | DOC 1 |  2000 |
|  1 | DOC 1 |  1000 |
|  2 | DOC 2 |  5000 |
|  3 | DOC 3 |  3000 |
|  3 | DOC 4 |  3000 |
|  3 | DOC 4 |  2000 |
+----+-------+-------+
6 rows in set (0.00 sec)

Berikutnya buat table TRANSAKSI kedua juga menggunakan perintah SQL CREATE TABLE … AS SELECT … kemudian tampilkan isinya untuk memastikan.

mysql> create table t_transaksi_2 as
    -> select 2 id, 'DOC 2' doc, 2000 value union all
    -> select 2 id, 'DOC 2' doc, 2000 value union all
    -> select 3 id, 'DOC 3' doc, 4000 value union all
    -> select 3 id, 'DOC 4' doc, 5000 value union all
    -> select 3 id, 'DOC 4' doc, 1000 value ;
Query OK, 5 rows affected (0.15 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t_transaksi_2 ;
+----+-------+-------+
| id | doc   | value |
+----+-------+-------+
|  2 | DOC 2 |  2000 |
|  2 | DOC 2 |  2000 |
|  3 | DOC 3 |  4000 |
|  3 | DOC 4 |  5000 |
|  3 | DOC 4 |  1000 |
+----+-------+-------+
5 rows in set (0.00 sec)

Sekarang kita buatkan summary untuk table TRANSAKSI pertama, menggunakan GROUP BY dan fungsi aggregat SUM()

mysql>    select tm.id, data, doc, sum(value) value1
    ->    from t_master tm
    ->    join t_transaksi_1 tr on tm.id=tr.id
    ->    group by id, doc
    -> ;
+----+------+-------+---------+
| id | data | doc   | value11 |
+----+------+-------+---------+
|  1 | A    | DOC 1 |    3000 |
|  2 | B    | DOC 2 |    5000 |
|  3 | C    | DOC 3 |    3000 |
|  3 | C    | DOC 4 |    5000 |
+----+------+-------+---------+
4 rows in set (0.00 sec)

Sekarang kita buatkan summary untuk table TRANSAKSI kedua, menggunakan GROUP BY dan fungsi aggregat SUM().

mysql> select tm.id, data, doc, sum(value) value2
    ->    from t_master tm
    ->    join t_transaksi_2 tr on tm.id=tr.id
    ->    group by id, doc  ;
+----+------+-------+---------+
| id | data | doc   | value12 |
+----+------+-------+---------+
|  2 | B    | DOC 2 |    4000 |
|  3 | C    | DOC 3 |    4000 |
|  3 | C    | DOC 4 |    6000 |
+----+------+-------+---------+
3 rows in set (0.00 sec)

Sekarang gabungkan SUMMARY dari TRANSAKSI pertama dan kedua, menggunakan LEFT JOIN menjadi seperti ini.


mysql> select res1.id, res1.data, res1.doc
    -> , value1
    -> , value2
    -> from (
    ->    select tm.id, data, doc, sum(value) value1
    ->    from t_master tm
    ->    join t_transaksi_1 tr on tm.id=tr.id
    ->    group by id, doc
    -> ) res1
    -> left join (
    ->    select tm.id, data, doc, sum(value) value2
    ->    from t_master tm
    ->    join t_transaksi_2 tr on tm.id=tr.id
    ->    group by id, doc
    -> ) res2 on res1.id=res2.id and res1.doc=res2.doc ;
+----+------+-------+--------+--------+
| id | data | doc   | value1 | value2 |
+----+------+-------+--------+--------+
|  1 | A    | DOC 1 |   3000 |   NULL |
|  2 | B    | DOC 2 |   5000 |   4000 |
|  3 | C    | DOC 3 |   3000 |   4000 |
|  3 | C    | DOC 4 |   5000 |   6000 |
+----+------+-------+--------+--------+
4 rows in set (0.00 sec)

Namun masih ada yang sedikit aneh, karena ada nilai NULL di sana, maka kita gunakan perintah IFNULL() untuk mengunbahknya menjadi angka 0 (nol).


mysql> select res1.id, res1.data, res1.doc
    -> , ifnull(value1,0) value1
    -> , ifnull(value2,0) value2
    -> from (
    ->    select tm.id, data, doc, sum(value) value1
    ->    from t_master tm
    ->    join t_transaksi_1 tr on tm.id=tr.id
    ->    group by id, doc
    -> ) res1
    -> left join (
    ->    select tm.id, data, doc, sum(value) value2
    ->    from t_master tm
    ->    join t_transaksi_2 tr on tm.id=tr.id
    ->    group by id, doc
    -> ) res2 on res1.id=res2.id and res1.doc=res2.doc ;
+----+------+-------+--------+--------+
| id | data | doc   | value1 | value2 |
+----+------+-------+--------+--------+
|  1 | A    | DOC 1 |   3000 |      0 |
|  2 | B    | DOC 2 |   5000 |   4000 |
|  3 | C    | DOC 3 |   3000 |   4000 |
|  3 | C    | DOC 4 |   5000 |   6000 |
+----+------+-------+--------+--------+
4 rows in set (0.00 sec)

Demikian TUTORIAL ini semoga membantu memahami penggunaan SUBQUERY, JOIN, GROUP BY, dan fungsi agregate SUM.

Belajar SQL: CASE Clause – Logika Percabangan

superadmin August 1, 2013 No Comments

Belajar SQL Logika Percabangan Case clause

Salah satu fungsi atau clause yang kurang begitu populer di SQL adalah CASE clause. Salah satu sebabnya adalah umunya SQL lebih dikenal hanya untuk proses SELECT, INSERT, UPDATE dan DELETE saja, dan karena CASE clause punya fungsi yang sama dengan IF di semua bahasa pemrograman yang ada, misalkan PHP, Java, dan sebagainya. Untuk itu saya akan berikan contoh sederhana penggunaan SQL untuk melakukan perhitungan dengan sedikit logika IF sederhana.

Misalkan kita mempunya contoh data seperti berikut ini

mysql> select * from penggunaan;
+----------+-------+--------+-------+-------+-------+-------+--------+
| kegiatan | senin | selasa | rabu  | kamis | jumat | sabtu | minggu |
+----------+-------+--------+-------+-------+-------+-------+--------+
| olahraga | ya    | tidak  | ya    | tidak | ya    | tidak | tidak  |
| diskusi  | tidak | ya     | tidak | tidak | tidak | ya    | tidak  |
| rapat    | ya    | tidak  | tidak | tidak | tidak | tidak | tidak  |
| ulangan  | tidak | ya     | ya    | ya    | tidak | tidak | tidak  |
+----------+-------+--------+-------+-------+-------+-------+--------+
4 rows in set (0.00 sec)

ketika kita ingin tahu berapa banyak dalam seminggu kegiatan-kegiatan tersebut di atas dilakukan atau tidak. Sebagai contoh untuk kegiatan OLAHRAGA dalam satu minggu dilakukan sebanyak 3 kali dan tidak dilakukan sebanyak 4 kali. jika kita bisa dengan mudah menggunakan PHP dan looping untuk menghitungnya menggunakan logika percabangan IF, misalkan.

if ($row['senin']=='ya')
    $dilakukan += 1;
else
    $tidak_dilakukan += 1;

Kalau menggunakan SQL caranya adalah seperti ini

mysql> select penggunaan.*
    -> , case when senin='ya' then 1 else 0 end
    -> + case when selasa='ya' then 1 else 0 end
    -> + case when rabu='ya' then 1 else 0 end
    -> + case when kamis='ya' then 1 else 0 end
    -> + case when jumat='ya' then 1 else 0 end
    -> + case when sabtu='ya' then 1 else 0 end
    -> + case when minggu='ya' then 1 else 0 end as dilakukan
    -> , case when senin='tidak' then 1 else 0 end
    -> + case when selasa='tidak' then 1 else 0 end
    -> + case when rabu='tidak' then 1 else 0 end
    -> + case when kamis='tidak' then 1 else 0 end
    -> + case when jumat='tidak' then 1 else 0 end
    -> + case when sabtu='tidak' then 1 else 0 end
    -> + case when minggu='tidak' then 1 else 0 end as tidak_dilakukan
    -> from penggunaan ;

Saat menghitung jumlah "dilakukan" kita berikan nilai 1 jika field berisikan 'ya', selain itu beli nilai 0. Dan saat menghitung jumlah "tidak_dilakukan" kita berikan nilai 1 jika field berisikan 'tidak', selain itu beli nilai 0. Setelah itu jumlahkan semua CASE clause tersebut sehingga kita dapatkan hasil seperti ini

+----------+-------+--------+-------+-------+-------+-------+--------+-----------+-----------------+
| kegiatan | senin | selasa | rabu  | kamis | jumat | sabtu | minggu | dilakukan | tidak_dilakukan |
+----------+-------+--------+-------+-------+-------+-------+--------+-----------+-----------------+
| olahraga | ya    | tidak  | ya    | tidak | ya    | tidak | tidak  |         3 |               4 |
| diskusi  | tidak | ya     | tidak | tidak | tidak | ya    | tidak  |         2 |               5 |
| rapat    | ya    | tidak  | tidak | tidak | tidak | tidak | tidak  |         1 |               6 |
| ulangan  | tidak | ya     | ya    | ya    | tidak | tidak | tidak  |         3 |               4 |
+----------+-------+--------+-------+-------+-------+-------+--------+-----------+-----------------+
4 rows in set (0.00 sec)

Tadaaaa….. Bagaimana? Luar biasa, bukan?

Dengan satu perintah SQL, kita bisa mendapatkan data yang matang dan ketika membuat program PHP-nya kita cukup menmpilkannya saja dalam bertuk <table></table> dan tidak perlu menghitung-hitung lagi. Mudah dan Cepat (y)

The Magic of Trigger

superadmin June 15, 2013 No Comments

Salah satu business rule yang banyak dijumpai dalam aplikasi perkantoran adalah penyimpana data menggunakan skema table master-detail seperti data Purchase Order, Invoice dan sebagainya. Tantangannya adalah membuat sebuah field yang berisi summary nilai pembelian yang ada di dalam table detail. Misalkan ada data Purchase Order dengan 5 item masing-masing bernilai 1.000.000 rupiah maka akan ada field summary di table master yang berisikan nilai 5.000.000.

Business Rule seperti di atas dapat di-enforce di level aplikasi menggunakan code PHP, Java, atau .net. Namun dapat juga dilakukan di level database dengan menggunakan trigger. Untuk mencapainya silakan ikuti langkah-langkah berikut ini,

Pertama-tama kita buat table master dan table detail.

mysql> -----------------------------
mysql> -- buat table header
mysql> -----------------------------
mysql> create table header (
    ->   id int auto_increment primary key,
    ->   amount int default 0
    -> ) ;
Query OK, 0 rows affected (0.04 sec)
 
mysql> -----------------------------
mysql> -- buat table detail
mysql> -----------------------------
mysql> create table detail (
    ->   id int auto_increment primary key,
    ->   header_id int not null,
    ->   quantity int default 0,
    ->   unit_price int default 0,
    ->   foreign key (header_id) references header (id)
    -> ) ;
Query OK, 0 rows affected (0.03 sec)

Kemudian coba kita buatkan trigger untuk meng-handle aktifitas insert ke table detail. Logikanya adalah, setiap kali ada penginputan row baru ke table detail maka field amount di table master harus diubah.

mysql> -----------------------------
mysql> -- buat insert trigger untu tabel detail
mysql> -- amount di table header dihitung
mysql> -- berdasarkan perkalian quantity*unit_price
mysql> -----------------------------
mysql> delimiter |
mysql> create trigger trg_detail_insert
    -> after insert on detail
    -> for each row
    -> begin
    ->   update header
    ->   set amount = (
    ->     select sum(quantity*unit_price)
    ->     from detail where header_id = new.header_id )
    ->   where id = new.header_id ;
    -> end;
    -> |
Query OK, 0 rows affected (0.01 sec)
 
mysql> delimiter ;

Untuk keperluan ujicoba, maka kita masukkan beberapa row record ke table master dan coba tampilkan untuk mengetahui isinya. Dan dapat terlihat bahwa amount masih bernilai 0 (nol)

mysql> -----------------------------
mysql> -- insert sampel data ke header
mysql> -- kemudian tampilkan
mysql> -----------------------------
mysql> insert into header(amount)
    -> values (0), (0), (0) ;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select * from header ;
+----+--------+
| id | amount |
+----+--------+
|  1 |      0 |
|  2 |      0 |
|  3 |      0 |
+----+--------+
3 rows in set (0.00 sec)

Kemudian coba kita masukkan beberapa row record data ke table detail dan coba tampilkan isinya.

mysql> -----------------------------
mysql> -- insert sampel data ke detail
mysql> -- kemudian tampilkan
mysql> -----------------------------
mysql> insert into detail (header_id, quantity, unit_price)
    -> values (1, 10, 100), (1, 2, 300),
    ->        (2, 1, 500), (2, 5, 300) ;
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0
 
mysql> select * from detail;
+----+-----------+----------+------------+
| id | header_id | quantity | unit_price |
+----+-----------+----------+------------+
|  3 |         1 |       10 |        100 |
|  4 |         1 |        2 |        300 |
|  5 |         2 |        1 |        500 |
|  6 |         2 |        5 |        300 |
+----+-----------+----------+------------+
4 rows in set (0.00 sec)

Setelah kita lihat data yang ada di table detail di atas, maka seharusnya trigger akan mengubah field amount yang ada di table master. jadi sekarang kita coba tampilkan isi table master seperti di bawah ini.

mysql> -----------------------------
mysql> -- coba tampilkan isi table header
mysql> -- seharusnya amount sudah berubah
mysql> -----------------------------
mysql> select * from header;
+----+--------+
| id | amount |
+----+--------+
|  1 |   1600 |
|  2 |   2000 |
|  3 |      0 |
+----+--------+
3 rows in set (0.00 sec)

Tadaaa……. Sukses…. BERHASIL !!!!

mysql> -----------------------------
mysql> -- YESS....... !!!
mysql> -- it works
mysql> -----------------------------
mysql>

Setelah ini masih ada beberapa hal yang dapat dikembangkan lagi, yaitu trigger untuk proses UPDATE detail, karena proses update di table detail seharusnya juga mengubah nilai amount yang ada di table master. Selain itu contoh di atas menggunakan database MySQL, sehingga teman-teman yang ingin menerapkannya di sistem RDBMS lain harus menyesuaikan kembali sintaks-nya.

Semoga tutorial ini bisa bermanfaat.