1. INTRODUCTION

Database Relasional tentunya memiliki sebuah kekuatan yang terletak pada kemampuan dalam mengambil serta menganalisis data dengan cepat dimana dengan menjalankan QUERY. Query ini merupakan sebuah permintaan akan informasi dimana meliputi informasi yang diterima ataupun diambil dari database. Jadi Query ini akan mengambil sebuah informasi dari satu ataupun beberapa tabel dengan berdasar pada serangkaian kondisi pencarian yang ditetapkan.

Langkah pertama yang harus dilakukan adalah menghubungkan kedatabase kita.

# Mengatur koneksi dan penyimpanan kedalam ruang kerja
library(RMySQL)
library(DBI)
Clara <- dbConnect(RMySQL::MySQL(),
                  dbname='new_mariadb',
                  username='root',
                  password='', 
                  host='localhost',
                  port=3306)
knitr::opts_chunk$set(connection = "Clara") # Untuk mengatur koneksi pada Rmarkdown

2. SELECT

SQL SELECT digunakan dalam mengambil suatu data dari tabel-tabel database dimana akan mengembalikan data tersebut kedalam bentuk tabel hasil yang disebut result-sets. Sintaks dasarnya adalah

SQL Select

Dapat mengasumsikan coloumn1,coloumn2,coloumnN,... sebuah kolom atau bidang tabel yang akan diambil nilainya dan jika ingin mengambil beberapa bidang atau kolom yang tersedia dapat menggunakan sintaks :

SELECT CustomerName, Address, City, Country 
  FROM CUSTOMERS;

Jika ingin mengambil seluruh kolom tabel “CUSTOMERS” kita dapat menggunakan queri :

SELECT *
  FROM CUSTOMERS;

3. DISTINCT

SQL DISTINCT diguanakan secara bersamaan dengan SELECT dalam menghilangkan seluruh catatan duplikat dimana hanya mengambil sebuah catatan yang unik ataupun berbeda pada suatu data. Jadi didalam sebuah tabel, kolom sering berisi banyak nilai duplikat dan perintah tersebut hanya menampilkan nilai yang berbeda dari suatu data.

Sintaks dasar kata kunci DISTINCT untuk menghilangkan rekaman duplikat:

SELECT DISTINCT column_name
  FROM table_name

Contohnya adalah mengambil kolom Country dari Data Customers dimana terdapat 21 data yang berbeda.

SELECT DISTINCT Country   
  FROM customers;

4. WHERE

WHERE digunakan dalam memfilter atau menyaring record dimana untuk mengekstrak record yang memenuhi sebuah persyaratan tertentu.

SELECT column1, column2, ...
  FROM table_name
    WHERE [condition];

WHERE digunakan dalam pernyataan UPDATE,DELETE,Dll.

Contohnya dalam memilih seluruh pelanggan dari negara “MEKSIKO” pada data “CUSTOMERS”.

SELECT * 
  FROM Customers
    WHERE Country='Mexico';

Beberapa Operator yang dapat digunakan dalam klausa WHERE adalah :

5. BETWEEN

Operator ini dapat menentukan atau memilih nilai dalam rentang tertentu, dimana nilainya tersebut dapat berupa angka, teks ataupun tanggal. Operator BETWEEN ini bersifat inklusif dimana nantinya nilai awal dan akhir disertakan atau dimasukkan.

SELECT column_name(s)
  FROM table_name
    WHERE column_name 
      BETWEEN value1 AND value2;

Contohnya adalah memilih semua produk dengan harga antara 10 dan 20:

SELECT * 
  FROM Products
    WHERE Price 
      BETWEEN 10 AND 20;

Contoh Selanjutnya adalah memilih orderDate antara 01-July-1996 dan 31-July-1996.

SELECT * 
  FROM Orders
    WHERE OrderDate 
      BETWEEN '1996-07-01' AND '1996-07-31'

6. IN

Operator ini dapat menentukan beberapa nilai yang terkandung dalam WHERE.

SELECT column_name(s)
  FROM table_name
    WHERE column_name 
      IN (SELECT STATEMENT);

Contoh memilih seluruh CUSTOMERS yang berlokasi di “Germany”,“France” atau “UK”

SELECT * 
  FROM Customers
    WHERE Country 
      IN ('Germany', 'France', 'UK');

Contoh selanjutnya adalah memilih seluruh CUSTOMERS dari negara yang sama dengan pemasok

SELECT * 
  FROM Customers
    WHERE Country 
      IN (SELECT Country FROM Suppliers);

7. LIKE

LIKE digunakan dalam mencari pola tertentu pada kolom dimana terdapat 2 wildcard yang sering digunakan secara bersamaan dengan operator LIKE, yaitu :

• % : Tanda persen mewakili nol, satu, atau beberapa karakter

• _ Garis bawah mewakili satu karakter

Syntax Dasarnya adalah

SELECT column1, column2, ...
  FROM table_name
    WHERE column LIKE pattern;

Contohnya dalam emmilih seluruh CUSTOMERS dengan Nama Pelanggan dimulai dengan huruf “a”.

SELECT * 
  FROM Customers
    WHERE CustomerName 
      LIKE 'a%';

Beberapa contoh yang menunjukkan operator LIKE yang berbeda dengan wildcard ‘%’ dan ’_’.

8. AND,OR,and NOT

WHERE dapat digabungkan dengan operator AND, OR, dan NOT.

• AND = menampilkan record jika semua kondisi yang dipisahkan oleh AND adalah TRUE.
• OR = menampilkan record jika salah satu kondisi yang dipisahkan oleh OR adalah TRUE.
• NOT = menampilkan record jika kondisi NOT TRUE.

SELECT column1, column2, ...
  FROM table_name
    WHERE condition1 AND condition2 OR condition3 NOT condition4

Contoh memilih seluruh kolom dari data CUSTOMERS dimana negaranya “Germany” dan Kota “Berlin” ATAU “München”.

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');

9. ORDER BY

SQL ORDER BY digunakan dalam mengurutkan suatu data dimana dalam urutan menaik maupun menurun dengan berdasarkan pada satu atau beberapa kolom. Sintaks dasar ORDER BY adalah sebagai berikut:

SELECT column-list 
  FROM table_name 
    [WHERE condition] 
      [ORDER BY column1, column2, .. columnN] [ASC | DESC];

Secara umum ataupun default ORDER BY mengurutkan sebuah data dalam urutan menaik.Untuk mengurutkan data dalam urutan menurun kita dapat menggunakan kata Kunci DESC lalu untuk mengurutkan dalam urutan Menaik dapat menggunakan kata Kunci ASC. Kita dapat menggunakan lebih dari satu kolom dalam ORDER BY. Namun untuk mengurutkan kolom yang ada pada daftar blok diharuskan memastikan kolom yang digunakan. Blok kode berikut memiliki contoh, yang akan mengurutkan hasilnya dalam urutan menaik berdasarkan Kota dan Negara:

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München')
  ORDER BY Country, City;

10. LIMIT

Jika terdapat beberapa tupel yang memenuhi kondisi QUERY akan lebih mudah melihat hanya dalam satu waktu.

• Digunakan untuk Menetapkan Batas atas Jumlah Tupel yang dikembalikan oleh SQL
• Dapat ditentukan menggunakan klausa SQL 2008 OFFSET/FETCH FIRST.
• Ekspresi limit/offset harus berupa bilangan bulat non-negatif.

SELECT column-list 
  FROM table_name 
    [WHERE condition] 
      [ORDER BY column1, column2, .. columnN] [ASC | DESC]
        LIMIT rows_to_skip, next_rows_to_skip;

Contoh mengumpulkan 3 baris TOP

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München')
  ORDER BY Country, City
      LIMIT 3;

Contoh Selanjutnya mengumpulkan 5 baris TOP setelah 3 baris TOP

SELECT CustomerName, Address, City, Country 
  FROM customers
    ORDER BY City, Country DESC
      LIMIT 3, 5;

11. MIN and MAX

• Fungsi MIN() = Mengembalikan nilai terkecil dari kolom yang dipilih.
• Fungsi MAX() = Mengembalikan nilai terbesar dari kolom yang dipilih.

SELECT MIN/MAX(column_name)
  FROM table_name
    WHERE condition;

Contoh menemukan harga produk yang termurah :

SELECT MIN(Price) AS SmallestPrice
  FROM Products;

Contoh Selanjutnya menemukan harga produk yang termahal :

SELECT MAX(Price) AS LargestPrice
  FROM Products;

12. COUNT,SUM,and AVG

• Fungsi COUNT() digunakan untuk mengembalikan jumlah baris yang sesuai dengan kriteria yang sudah ditentukan.
• Fungsi AVG() digunakan dalam mengembalikan nilai rata-rata pada kolom numerik.
• Fungsi SUM() digunakan dalam mengembalikan jumlah total pada kolom numerik.

SELECT COUNT/SUM/AVG(column_name)
FROM table_name
WHERE condition;

Contoh Mencari Jumlah Baris Harga pada semua products

SELECT COUNT(Price)
  FROM Products;

Contoh Selanjutnya Mencari Rata-Rata Harga pada semua products

SELECT AVG(Price)
  FROM Products;

Contoh Selanjutnya Mencari Jumlah Harga pada semua products

SELECT SUM(Price)
  FROM Products;

13. HAVING

Pada SQL, HAVING ditambahkan dikarenakan kata kunci WHERE tidak daoat digunakan dengan fungsi agregat.

SELECT column_name(s)
  FROM table_name
    WHERE condition
      GROUP BY column_name(s)
        HAVING condition
          ORDER BY column_name(s);

Contoh mencantumkan jumlah pelanggan pada setiap negara yang diurutkan dari tinggi ke rendah dengan lebih dari 5 pelanggan.

SELECT COUNT(CustomerID), Country
  FROM Customers
    GROUP BY Country
      HAVING COUNT(CustomerID) > 5
        ORDER BY COUNT(CustomerID) DESC;

14. CASE

CASE mengembalikan nilai saat pada kondisi seperti pernyataan IF-THEN-ELSE terpenuhi. Jadi jika suatu kondisi benar-benar terpenuhi itu nanti akan berhenti membaca serta mengembalikan hasilnya. namun jika tidak terdapat kondisi yang benar atau terpenuhi, maka akan mengembalikan nilai dalam ELSE. Jika tidak terdapat ELSE dan tidak terdapat kondisi yang benar atau terpenuhi, akan mengembalikan NULL.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

Contoh SQL mengembalikan nilai saat kondisi pertama terpenuhi.

SELECT OrderID, Quantity,
CASE
    WHEN Quantity > 30 THEN 'The quantity is greater than 30'
    WHEN Quantity = 30 THEN 'The quantity is 30'
    ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;

Contoh selanjutnya mengurutkan CUSTOMERS berdasarkan Kota namun Jika Kota NULL maka berdasarkan Negara.

SELECT CustomerName, City, Country
FROM Customers
ORDER BY
(CASE
    WHEN City IS NULL THEN Country
    ELSE City
END);

15. EXERCISE

Menghubungkan ke dalam database kita.

# Mengatur koneksi dan penyimpanan kedalam ruang kerja
library(RMySQL)
library(DBI)
Clara <- dbConnect(RMySQL::MySQL(),
                  dbname='new_mariadb',
                  username='root',
                  password='', 
                  host='localhost',
                  port=3306)
knitr::opts_chunk$set(connection = "Clara") # Untuk mengatur koneksi pada Rmarkdown

SELECT SupplierID, SupplierName, Address, City, Country
  FROM Suppliers S
     ORDER BY S.SupplierName ASC;

SELECT SupplierID, SupplierName, Address, City, Country
  FROM Suppliers S
     ORDER BY S.SupplierName DESC;

SELECT SupplierID, SupplierName, Address, City, Country
  FROM Suppliers S
     ORDER BY S.Country ASC, S.City ASC;

SELECT S.*
  FROM Suppliers S
     ORDER BY S.Country DESC, S.City DESC;

SELECT O.*, OD.Quantity
  FROM Orders O
    RIGHT JOIN orderdetails OD
      ON O.OrderID = OD.OrderID
        ORDER BY OD.Quantity DESC;

SELECT P.*
  FROM products P
    ORDER BY P.Price DESC
      LIMIT 10;

SELECT P.*
  FROM products P
    ORDER BY P.Price DESC
      LIMIT 6 OFFSET 9;

SELECT DISTINCT Country
  FROM suppliers S
    ORDER BY S.Country ASC;

SELECT P.ProductName, P.Unit, P.Price
  FROM products P
      ORDER BY P.Price ASC
        LIMIT 1;

SELECT P.ProductName, P.Unit, P.Price
  FROM products P
      ORDER BY P.Price DESC
        LIMIT 1;

SELECT S.Country, COUNT(S.Country) AS 'Total_Supplier'
  FROM suppliers S
    WHERE S.Country='USA' ;

SELECT SUM(OD.Quantity) AS 'TotalQuantity'
  FROM orderdetails OD

SELECT AVG(P.Price) AS 'Average_UnitPrice'
  FROM products P

SELECT C.*
  FROM customers C
    WHERE ContactName='Thomas Hardy';

SELECT C.*
  FROM customers C
    WHERE Country="Spain" OR Country="France";

SELECT C.*
  FROM customers C
    WHERE NOT Country="USA"

SELECT O.*,P.ProductID, P.ProductName, P.Price
  FROM orders O
    LEFT JOIN orderdetails OD
      ON O.OrderID = OD.OrderID
    LEFT JOIN products P
      ON OD.ProductID = P.ProductID
  WHERE P.Price 
    NOT BETWEEN 50 AND 15000;

SELECT DISTINCT P.*
  FROM products P
    WHERE P.Price 
     BETWEEN 10 AND 20;

SELECT DISTINCT P.*
  FROM products P
    WHERE P.Price 
     NOT BETWEEN 10 AND 100
ORDER BY P.Price ASC;

SELECT O.OrderID, O.OrderDate, sum(OD.Quantity) AS 'AmountSold'
  FROM orders O
    JOIN orderdetails OD
      ON O.OrderID = OD.OrderID
    GROUP BY O.OrderID
    HAVING O.OrderDate
    BETWEEN '1996-01-01' AND '1996-12-31'
  ORDER BY O.OrderDate;

SELECT S.*
  FROM suppliers S
    WHERE S.Country 
      IN ('USA', 'UK', 'JAPAN');

SELECT P.*
  FROM products P
    WHERE P.Price
      NOT IN (10, 20, 30, 40, 50);

SELECT C.CustomerName, S.SupplierName, C.Country AS 'CustomerCountry', S.Country AS 'SupplierCountry'
  FROM customers C
    JOIN Orders O
      ON C.CustomerID=O.CustomerID
    JOIN OrderDetails OD
      ON O.OrderID=OD.OrderID
    JOIN Products P
      ON OD.ProductID=P.ProductID
    JOIN Suppliers S
      ON P.SupplierID=S.SupplierID
    WHERE 
      CASE
         WHEN C.Country=S.Country THEN '1'
         ELSE '0'
        END = '1';

SELECT P.ProductName
  FROM products P
    WHERE ProductName LIKE 'Cha_' OR ProductName LIKE 'Chan_';

SELECT CustomerName, AVG(TotalOrderAmount) AS AverageOrder
  FROM 
      ( 
       SELECT CustomerName, SUM(OD.Quantity*P.Price) AS TotalOrderAmount
        FROM customers C
          JOIN orders O
            ON C.CustomerID = O.CustomerID
          JOIN orderdetails OD
            ON O.OrderID = OD.OrderID
          JOIN products P
            ON OD.ProductID = P.ProductID
        GROUP BY OD.OrderID
        ) S
  GROUP BY S.CustomerName
    HAVING AverageOrder
      BETWEEN 1000 AND 1200
  ORDER BY AverageOrder ASC;

SELECT C.Country, COUNT(C.Country) AS 'TotalCustomer'
  FROM customers C
    GROUP BY C.Country WITH ROLLUP

16. Reference

https://rpubs.com/dsciencelabs/dbs5