Panduan Singkat Database Relasional

Panduan ini memberikan gambaran singkat pada pembaca terkait operasi atau query umum yang digunakan untuk mengekstrak data pada database relasional. Hal yang akan dipelajari antara lain:

1. Mengakses SQL dari Microsoft Access
2. Memilih kolom dari sebuah tabel
3. Melakukan operasi untuk membentuk kolom baru
4. Melakukan filter pada data
5. Menghitung nilai aggregat suatu variabel dan mengurutkan nilai tersebut
6. Menggabungkan dua buah tabel data
7. Query lainnya

1. Database Relasional

Database yang digunakan pada artikel ini adalah database penjualan dengan bentuk relasi yang digambarkan pada gambar berikut:

skema relasi database penjualan.

2. Mengakses SQL dari Microsoft Access

Untuk melakukan query menggunakan SQL jalankan langkah berikut:

1. Pada bagian Ribbon klik Tab Create
2. Klik Query Design
3. Tutup jendela Show Table yang muncul setelahnya
4. Pada Ribbon Design klik SQL View
5. Pada jendela query yang muncul, pembaca dapat mengetikkan query yang diinginkan.
6. Setelah query diinputkan, klik Run
7. Proses tersebut akan menghasilkan tabel data sesuai dengan query yang diinputkan. Untuk mengedit kembali query yang diinputkan, klik View dan pilih SQL View
8. Untuk menyimpan query tekan ctrl+s (untuk windows) atau command+s (untuk macOSX).

tahapan melakukan query menggunakan SQL.

3. Memilih Kolom dari Sebuah Tabel

Untuk memilih kolom pada sebuah tabel, kita dapat menggunakan perintah SELECT untuk menentukan nama kolom yang akan diambil dan FROM untuk menentukan tabel yang akan diambil kolomnya. Secara sederhana proses tersebut ditampilkan pada gambar berikut:

format memilih kolom dalam sebuah tabel.

Contoh 1 (memilih kolom pada tabel Barang)

Lakukan proses pengambilan data id_barang, nama, dan varian pada tabel Barang !

query:

SELECT id_barang, nama, varian
FROM Barang;

output:

id_barang	nama	varian
Ma0	Makaroni rasa-rasa	original
Ma1	Makaroni rasa-rasa	pedas
dst…

4. Melakukan Operasi Untuk Membentuk Kolom Baru

Untuk membentuk kolom baru pada data, operasi matematis (penjumlahan, pengurangan, transformsi, dll) dapat dilakukan pada SQL melalui baris fungsi SELECT. Kolom baru yang terbentuk selanjutnya dapat diberikan nama baru sesuai dengan kemauan pembaca menggunakan fungsi AS. Format operasi tersebut ditampilkan pada gambar berikut:

format operasi untuk membentuk kolom baru.

Operator matematika yang digunakan dalam SQL antara lain:

• “+” : opertor penjumlahan
• “-” : operator pengurangan
• "*" : operator perkalian
• “/”" : operator pembagian
• “MOD()” : mencari sisa hasil bagi bilangan pertama terhadap bilangan kedua
• “^” : operator pangkat

Contoh 2 (menghitung keuntungan masing-masing produk)

Lakukan pengambilan data id_barang, nama, varian dan lakukan pehitungan keuntungan dengan melakukan operasi pengurangan antara harga_jual-harga_beli dan beri nama keuntungan pada kolom baru tersebut!

query:

SELECT id_barang, nama, varian, harga_jual-harga_beli AS keuntungan
FROM Barang;

output:

id_barang	nama	varian	keuntungan
Ma0	Makaroni rasa-rasa	original	2000
Ma1	Makaroni rasa-rasa	pedasa	2000
dst…

5. Melakukan Filter pada Tabel

Terkadang tidak semua nilai kita inginkan untuk ada dalam data yang kita miliki. Filter terhadap data perlu dilakukan. Filter data dilakukan dengan menggunakan fungsi WHERE dengan menambahkan kondisi yang diinginkan pada data. Format proses filter data ditampilkan pada gambar berikut:

format operasi melakukan filter data.

Operator perbandingan yang digunakan adalah sebagai berikut:

• “>” : lebih besar dari
• “<” : lebih kecil dari
• “=” : sama dengan
• “>=” : lebih besar sama dengan
• “<=” : lebih kecil sama dengan
• “<>” : tidak sama dengan

Operator logika yang digunakan antara lain:

• AND : menghasilkan nilai TRUE jika ekspresi 1 dan ekspresi kedua TRUE
• OR : menghasilkan nilai TRUE jika ekspresi 1 atau ekspresi kedua TRUE
• EQV : menghasilkan nilai TRUE jika ekspresi 1 dan ekspresi kedua TRUE atau saat ekpresi 1 dan ekspresi 2 adalah FALSE
• NOT : Menghasilkan negasi dari sebuah ekspresi
• XOR : menghasilkan nilai TRUE jika ekspresi 1 adalah TRUE atau ekspresi kedua adalah TRUE, tetapi tidak keduanya

Contoh 3 (menghitung keuntungan masing-masing produk)

Lakukan perhitungan keuntungan masing-masing produk dengan melakukan operasi pengurangan terhadap harga_jual dan harga_beli! (gunakan tanda "*" untuk memilih seluruh kolom pada suatu tabel)

query:

SELECT *
FROM Pesanan
WHERE qty >= 3 OR qty <2;

output:

id_pesanan	id_pembeli	id_barang	qty	tgl_pesan
1	SBY37507	Ma2	1	1/1/2019
dst…

6. Menghitung Nilai Aggregat Suatu Variabel dan Mengurutkan Nilainya

Pehitungan nilai aggregat berguna jika kita ingin mengetahui nilai statistik dari sejumlah kelompok data, seperti: menghitung jumlah transaksi yang dilakukan masing-masing pelanggan. Agar dapat melakukannya data perlu dikelompokkan terlebih dahulu berdasarkan variabel pengelompok. Fungsi yang digunakan untuk melakukannya adalah fungsi GROUP BY. Hasil yang diperoleh selanjutnya dapat diurutkan nilainnya menggunakan fungsi ORDER BY. Format perhitungan nilai aggregat data ditampilkan pada gambar berikut:

format operasi untuk membentuk aggregat data.

Contoh 4 (menghitung jumlah pembelian suatu konsumen terhadap sebuah produk)

Lakukan perhitungan untuk memperoleh nilai total pembelian konsumen terhadap produk makaroni rasa-rasa original (id_barang = “Ma0”) dan tentutan konsumen mana yang melakukan total pembelian tertinggi!

query:

SELECT id_pembeli, id_barang, SUM(qty) AS jumlah_pembelian
FROM Pesanan
WHERE id_barang = "Ma0"
GROUP BY id_pembeli, id_barang
ORDER BY SUM(qty) DESC;

output:

id_pembeli	id_barang	jumlah_pembelian
MDN36326	Ma0	738
SBY99754	Ma0	715
dst..

7. Menggabungkan Dua Buah Tabel Data

Menggabungkan dua buah tabel data berdasarkan kolom primary key pada tabel pertama dan kolom foreign key pada tabel kedua merupakan operasi yang sering dilakukan pada database. SUatu tabel sering-kali membutuhkan informasi lain untuk memudahkan kita membacanya (contoh: mengabungkan tabel 1 dan tabel 2 untuk memperoleh informasi nama produk yang ada pada tabel 2 menggunakan kolom kunci yang ada pada kedua tabel). Format umum proses penggabungan tabel ditampilkan pada gambar berikut:

format menggabungkan tabel melalui inner join.

Terdapat beberapa jenis join yang ada pada SQL, antara lain:

• INNER JOIN : melakukan join hanya pada observasi dengan elemen kunci yang sama-sama ada pada kedua tabel.
• LEFT JOIN : menggabungkan seluruh baris pada tabel kiri dan sebagian baris pada tabel kanan yang elemen kuncinya cocok dengan tabel kiri
• RIGHT JOIN : kebalikan dari LEFT JOIN
• FULL JOIN : menggabungkan seluruh observasi pada kedua tabel melalui kolom elemen kunci.

Visualisasi proses join dapat dilihat pada gambar berikut:

visualisasi variasi join pada SQL.

Contoh 4 (menggabungkan tabel barang dan pesanan)

Gabungkan tabel barang dan pesanan menggunakan elemen kunci id_barang dan pada hasil join hanya tampilkan kolom id_barang, nama, varian, dan tgl_pesan!

query:

SELECT Barang.id_barang, Barang.nama, 
      Barang.varian, Pesanan.tgl_pesan
FROM Barang
INNER JOIN Pesanan 
      ON Pesanan.id_barang = Barang.id_barang;

output:

id_barang	nama	varian
Ma0	Makaroni rasa-rasa	original
Ma0	Makaroni rasa-rasa	original
dst..

8. Query Lainnya

Menghitung Usia Konsumen

Buatlah sebuah query untuk menghitung usia konsumen! (gunakan fungsi DATE() untuk memperoleh tanggal hari ini dan fungsi DATEDIFF() untuk menghitung selisih tanggal)

query:

SELECT Pembeli.id_pembeli, Pembeli.nama, 
      Min(Pesanan.tgl_pesan) AS tgl_beli_pertama,
      Max(Pesanan.tgl_pesan) AS tgl_beli_terakhir
FROM Pembeli 
INNER JOIN Pesanan 
      ON Pembeli.id_pembeli = Pesanan.id_pembeli
GROUP BY Pembeli.id_pembeli, Pembeli.nama;

id_pembeli	nama	jns_kelamin	kota	usia
BDG15240	jesika	P	Bandung	27
BDG16736	desi	P	Bandung	38
dst..

Mencari tanggal pembelian pertama dan terakhir konsumen

Buatlah sebuah query yang dapat digunakan untuk menentukan tanggal transaksi pertama dan terakhir konsumen!

query:

SELECT Pembeli.id_pembeli, Pembeli.nama, 
      Min(Pesanan.tgl_pesan) AS tgl_beli_pertama,
      Max(Pesanan.tgl_pesan) AS tgl_beli_terakhir
FROM Pembeli 
INNER JOIN Pesanan 
      ON Pembeli.id_pembeli = Pesanan.id_pembeli
GROUP BY Pembeli.id_pembeli, Pembeli.nama;

output:

id_pembei	nama	tgl_beli_pertama	tgl_beli_terakhir
BDG15240	jesica	1/1/2019	12/31/2019
BDG16736	desi	1/1/2019	12/31/2019
dst..

Menghitung penjualan bulanan masing-masing produk

Buatlah sebuah query untuk menghitung total penjualan masing-masing produk tiap bulan! (gunakan fungsi DATEPART() untuk memisahkan hari, bulan, dan tahun)

query:

SELECT DATEPART(m, Pesanan.tgl_pesan) AS bulan, 
      DATEPART(yyyy, Pesanan.tgl_pesan) AS tahun,
      Pesanan.id_barang, Barang.nama, Barang.varian,
      Sum(Pesanan.qty) AS penjualan
FROM Barang 
INNER JOIN Pesanan 
      ON Barang.id_barang = Pesanan.id_barang
GROUP BY Pesanan.id_barang, Barang.nama, 
      Barang.varian, DATEPART(m, Pesanan.tgl_pesan),
      DATEPART(yyyy, Pesanan.tgl_pesan)
ORDER BY DATEPART(yyyy, Pesanan.tgl_pesan) DESC , 
      DATEPART(m, Pesanan.tgl_pesan);

output:

bulan	tahun	id_barang	nama	varian	penjualan
1	2019	Ma0	Makaroni rasa-rasa	original	2728
1	2019	Ma1	Makaroni rasa-rasa	pedas	2763
dst..

Menghitung jumlah transaksi setiap konsumen

Buatlah sebuah query untuk menghitung jumlah transaksi masing-masing konsumen! (gunakan fungsi DISTICT untuk memperoleh elemen unik pada tiap tgl_pesan)

query:

SELECT Pesanan.id_pembeli, Pembeli.nama, 
      COUNT(Pesanan.tgl_pesan) AS jumlah_transaksi
FROM (SELECT DISTINCT tgl_pesan,id_pelanggan 
      FROM Pesanan)
INNER JOIN Pembeli 
      ON Pembeli.id_pembeli = Pesanan.id_pelanggan
GROUP BY Pesanan.id_pelanggan, Pembeli.nama
ORDER BY COUNT(Pesanan.tgl_pesan) DESC;

output:

id_pelanggan | nama | jumlah_transaksi | DPS55607 | handoko | 320 | JKT92062 | rosidi | 319 | dst..|

Baca Juga

1. Tutorial MS Access
2. Dokumentasi MS Access
3. SQL Cheat Sheet