Introduction

Seperti yang kita ketahui database dapat menyimpan dan memelihara serta mengelola data, dalam database SQL yang dapat digunakan untuk menyimpan dan memelihara serta mengelola data dalam database tersebut baik secara langsung maupun menggunakan macam-macam bahasa pemrograman. database yang relasional akan mengambil data dan menganalisis data dengan menggunakan query. kita akan mempelajari beberapa query untuk mengolah data.

sebelumnya kita konekan R dengan database kita. coding :

library(RMySQL)
library(DBI)
yosia <- dbConnect(RMySQL::MySQL(),
                   dbname = 'new_mariadb',
                   username = 'root',
                   password = '',
                   host = 'localhost',
                   port = 3306)
knitr::opts_chunk$set(connection = "yosia")

Jadi kita konekan SQL dengan nama ‘yosia’ agar lebih simpel dalam memasukan coding

SELECT

di sesi ini kita akan mengambil data dari tabel yang sudah di gabungkan, kita memilih tabel mana yang akan kita ambil. contohnya :

SELECT  CustomerName, Address, City, Country
FROM CUSTOMERS;

dari tabel-tabel di atas muncul tabel yang sudah kita select atau pilih.

DISTINCT

DISTINCT digunakan saat kita ingin menampilkan representasi dari suatu tabel, dengan kata lain hanya 1 varian data yang muncul jika ada data yang sama. contohnya :

SELECT DISTINCT Country
From CUSTOMERS;

dari tabel di atas kita melihat bahwa dari 91 data dari CUSTOMERS yang tabelnya COUNTRY ternyata ada 21 varian data yang berbeda, dan sisanya data yang duplicate.

WHERE

WHERE merupakan perintah dasar SQL yang di gunakan untuk mem-filter hasil SELECT dengan mengekstrak record yang memenuhi persyaratan tertentu.

SELECT *
FROM customers
WHERE country='Mexico';

dilihat dari tabel diatas berarti kita mengambil data dari customers dan kita hanya memilih negara mexico yang di dalam data customers.

catatan

BETWEEN

kondisi yang memungkinkan kita untuk melakukan pengujian jika sebuah nilai berada dalam suatu rentang nilai tertentu. jadi kita ingin menampilkan data-data yang kita sudah tentukan. Contohnya:

SELECT *
FROM products
WHERE price
BETWEEN 10 AND 20;

dari data di atas kita melihat bahwa price berada dalam rentang 10-20. kondisi between juga bisa digunakan dalam tanggal, jadi kita menentukan dari tanggal berapa dan sampai tanggal berapa. Contohnya OrderDate BETWEEN ‘01-July-1996’ and ‘31-July-1996’:

SELECT *
FROM orders
WHERE orderdate
BETWEEN '1996-07-01' AND '1996-07-31';

IN

kondisi ini digunakan pada saat datanya string/character

SELECT *
FROM customers
WHERE country
IN ('Germany', 'France', 'UK');

SELECT *
FROM customers
WHERE country
IN (SELECT country FROM suppliers);

LIKE

Kondisi ini dilakukan untuk mencari pola tertentu. ada 2 wildcard yang sering digunakan pada kondisi LIKE;

• % = mewakili nol, satu, atau beberapa karakter
• _ = mewakili satu karakter

kita ingin mencari customername yang namanya awalan ‘a’ :

SELECT *
FROM customers
WHERE customername
LIKE 'a%';

beberapa contoh perbedaan operator LIKE antara ‘%’ dan ’_’ wildcard:

AND, OR and NOT

Operator AND, OR dan NOT merupakan perintah dasar SQL yang biasanya di kombinasikan dengan perintah WHERE. Ketiganya di gunakan untuk mem-filter record berdasarkan suatu kondisi, operator AND akan menampilkan record apabila semua kondisi bernilai TRUE, operator OR akan menampilkan record apabila salah satu kondisi bernilai TRUE, sedangkan operator NOT akan menampilkan record apabila semua kondisi bernilai FALSE.

SELECT * FROM customers
where country='Germany' AND (city='Berlin' OR city='München');

ORDER BY

di gunakan untuk mengurutkan result-set dalam pengurutan ‘ascending’ atau ’descending. mengurutkan dari yang terkecil ke terbesar.

SELECT * FROM orderdetails
WHERE quantity BETWEEN 20 AND 50  
ORDER BY quantity, orderid;

LIMIT

kondisi ini kita gunakan saat kita hanya ingin menampilkan data teratas dari tabel yang kita pilih. contohnya saya akan menampilkan 3 teratas :

SELECT * FROM orderdetails
WHERE quantity BETWEEN 20 AND 50  
ORDER BY quantity, orderid
LIMIT 3;

selanjutnya, kita akan mengambil 5 baris teratas setelah 3 baris teratas.

SELECT * FROM orderdetails
WHERE quantity BETWEEN 20 AND 50  
ORDER BY quantity, orderid
LIMIT 3,5;

MIN and MAX

function MIN() digunakan untuk mencari nilai terkecil. dan function MAX() digunakan untuk mencari nilai terbesar. kedua fungsi ini hanya menampilkan 1 nilai tidak seperti order by. Contohnya

kita ingin mencari harga termurah dari suatu produk

SELECT MIN(price) AS SmallestPrice
FROM products ;

selanjutnya kita ingin mencari nilai tertinggi dari suatu produk

SELECT MAX(price) AS LargestPrice
FROM products ;

COUNT, SUM, and AVG

fungsi COUNT() digunakan untuk mengetahui berapa kolom pada tabel. fungsi AVG() digunakan untuk mencari rata-rata. fungsi SUM() digunakan untuk mengetahui jumlahan atau total dari suatu kolom.

mencari rata-rata

SELECT AVG(price)
From Products

mencari jumlahan

SELECT SUM(price)
From Products

mencari berapa data

SELECT COUNT(price)
From Products

HAVING

digunakan saat fungsi where tidak bisa digunakan. Penggunaan WHERE pada fungsi agregat akan menyebabkan error

Pernyataan SQL berikut mencantumkan jumlah customers di setiap country, diurutkan dari tinggi ke rendah (Hanya sertakan country dengan lebih dari 5 customers):

SELECT COUNT(customerid), country
FROM customers
GROUP BY country
HAVING count(customerid) > 5
ORDER BY count(customerid) DESC;

CASE

digunakan untuk memberikan pesan atau catatan dari suatu tabel. pernyataan sama seperti (IF, THEN, ELSE). jadi jika kondisi terpenuhi dia akan stop membaca dan memasukan hasilnya. tapi jika kondisi tidak terpenuhi dia akan memasukan nilai dari klausa ELSE. jika ELSE tidak terpenuhi juga maka akan diberikan nilai NULL.

Contohnya:

SELECT orderid, quantity,
CASE
  WHEN quantity > 30 THEN 'kuantitas lebih besar dari 30'
  WHEN quantity = 30 THEN 'kuantitas sama dengan 30'
  ELSE 'Kuantitas lebih kecil dari  30'
END AS QuantityText
FROM orderdetails;

SQL berikut akan mengurutkan customers berdasarkan city. Namun, jika city adalah NULL, maka order by Country:

SELECT customername, city, country
FROM customers
ORDER BY
(CASE
  WHEN city IS NULL THEN country
  ELSE city
END);