Introduction

# set up the connection and save it into the workspace
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
library(RMySQL)
library(DBI)
diyas <- dbConnect(RMySQL::MySQL(),
                   dbname='factory_db',
                   username='root',
                   password='',
                   host='localhost',
                   port=3306)
knitr::opts_chunk$set(connection = "diyas")  #set up the connection

SELECT

SELECT dalam SQL digunakan sebagai argumen untuk memilih beberapa kolom yang diingikan.

SELECT CustomerName, Address, City, Country
  FROM CUSTOMERS C;

Jika kita ingin memilih semua kolom maka kita gunakan tanda bintang(*).

SELECT *
  FROM CUSTOMERS C;

DISTINCT

DISTINCT hampir sama dengan SELECT yaitu argumen yang digunakan untuk memilih values dari suatu kolom, akan tetapi hasil yang ditampilkan akan menghasilkan data yang unique. Misal kolom country ada 7 values yang isinya “Germany” maka pada hasilnya akan ditampilkan sekali saja tidak berulang.

SELECT DISTINCT Country
  FROM customers C;

WHERE

WHERE adalah argumen untuk memfilter values dari kolom. WHERE akan memfilter value sesuai dengan kondisi yang kita inginkan.

SELECT *
  FROM Customers C
    WHERE C.Country='Mexico';

Operators Where

BETWEEN

BETWEEN digunakan untuk memilih values dengan kondisi range tertentu. Kondisi range dapat berupa angka, teks, dan date. Untuk rangenya misal dari 20-50 maka range awal(20) dan range akhir(50) akan ikut dalam pemilihan value tersebut.

Contoh untuk BETWEEN dengan range angka

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

Contoh untuk BETWEEN dengan range date

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

IN

IN digunakan jika ingin menggunakan argumen WHERE dengan lebih dari 2 kondisi.

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

SELECT *
  FROM Customers C
    WHERE C.Country
      IN (SELECT S.Country FROM Suppliers S);

LIKE

LIKE adalah argumen yang hampir sama dengan WHERE digunakan untuk memfilter dengan pola yang spesifik. Misal kita ingin menemukan nama customer yang depannya berawalan dari huruf D atau bisa kita cari nama customer yang tengahnya berawalan dari huruf A dsb.

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

operasi yang terkenal di argumen Like adalah
- % operasi ini mewakili multi character
- _ operasi ini mewakili single character

Operators Like

AND, OR and NOT

Argumen ini dapat dikombinasikan dengan argumen WHERE. AND, OR, NOT sama halnya dengan logika matematika.

- operasi AND dimana akan terlihat hasilnya jika kedua data tersebut sama atau bernilai TRUE dan akan eror jika ada salah satu yang tidak sama atau bernilai FALSE.
- operasi OR dimana akan terlihat hasilnya jika salah satu data tersebut bernilai TRUE atau sesuai dengan kondisi yang diingikan.
- operasi NOT adalah operasi yang menampilkan hasil yang bernilai FALSE.

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

Jika kita ingin menggunakan NOT pada WHERE dapat diwakilkan dengan <>

SELECT * 
  FROM Customers C
    WHERE C.Country <> 'Germany' AND C.Country <> 'USA';

ORDER BY

ORDER BY digunakan untuk mengurutkan data dari besar ke kecil (z-a) atau bisa juka kecil ke besar (a-z) dengan kondisi

- DESC atau descending yaitu mengurutkan data dari besar ke kecil.
- ASC atau ascending yaitu mengurutkan data dari kecil ke besar.
- Akan tetapi jika kita tidak menggunakan keyword DESC atau ASC maka data akan diurutkan otomatis dengan ASC.

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

LIMIT

LIMIT digunakan untuk memilih beberapa data sesuai dengan range atau rentang yang kita inginkan. Argumen ini digunakan jika data yang dihasilkan terlalu banyak. Maka kita dapat menggunakan arguman ini untuk memilih data dengan batas atas dikurang batas bawah. Misal kita mengambil 15 data akan tetapi kita tidak mau 10 data yang diatasnya maka kita gunakan argumen ini dengan batas atas 15 dan batas bawah 10. Secara otomatis dari 15 data akan ada 10 data yang diabaikan.

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

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

MIN and MAX

MIN digunakan jika kita ingin melihat values yang memiliki nilai yang kecil. Sedangkan MAX digunakan untuk melihat values yang memiliki nilai yang besar.

SELECT MIN(P.Price) AS SmallestPrice
  FROM Products P;

SELECT MAX(P.Price) AS LargestPrice
  FROM Products P;

COUNT, SUM and AVG

COUNT digunakan untuk menjumlahkan beberapa baris yang sama sesuai dengan kriteria tertentu. SUM digunakan untuk menjumlahkan data yang memiliki type data numeric. Begitupun AVG digunakan untuk mencari rata-rata data numeric.

SELECT AVG(P.Price)
  FROM Products P;

HAVING

HAVING adalah argumen yang digunakan apabila argumen WHERE tidak dapat digunakan. Biasanya kondisi WHERE tidak dapat digunakan pada aggregate function maka dari itu kita gunakan argumen HAVING.

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

CASE

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

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

Exercise

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

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

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

SELECT *
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 9, 15;

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

SELECT min(P.Price) Cheapestprice
FROM products P

SELECT max(P.Price) Expensiveprice
FROM products P

SELECT COUNT(S.Country) TotalSupplierUSA
FROM suppliers S
WHERE S.Country = "USA"

SELECT SUM(OD.Quantity) TotalQuantity
FROM orderdetails OD

SELECT AVG(P.Price) AveragePrice
FROM products P

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

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

SELECT *
FROM customers C
WHERE C.Country <> 'USA'

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

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

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

SELECT C.CustomerID, C.CustomerName, O.OrderID, O.CustomerID, O.OrderDate, OD.OrderID, SUM(OD.Quantity) AmountSold
FROM customers C
LEFT JOIN orders O
ON C.CustomerID = O.CustomerID
LEFT JOIN orderdetails OD
ON O.OrderID = OD.OrderID
GROUP BY O.OrderID
HAVING O.OrderDate
BETWEEN '1996-01-01' AND '1996-12-31'

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

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

SELECT C.CustomerName, C.Country, S.SupplierName, S.Country
FROM customers C
JOIN suppliers S
ON C.Country = S.Country

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

SELECT *
FROM suppliers S
WHERE S.Phone IS NOT NULL

SELECT CustomerName, AVG(OrderAmount) AvgOrder
FROM 
(
SELECT C.CustomerName, SUM(OD.Quantity * P.Price) OrderAmount
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
) COPAVG
GROUP BY COPAVG.CustomerName
HAVING AvgOrder
BETWEEN 1000 AND 1200
ORDER BY AvgOrder

SELECT C.Country, COUNT(C.Country) TotalCustomer
FROM customers C
GROUP BY C.Country