Introduction

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

SELECT

SELECT digunakan untuk mengambil data dari tabel database yang mengembalikan data ini dalam bentuk tabel hasil.

SELECT CustomerName, Address, City, Country
  FROM CUSTOMERS;

Jika Anda ingin mengambil semua bidang tabel PELANGGAN, maka Anda harus menggunakan kueri berikut.

SELECT *
  FROM CUSTOMERS;

DISTINCT

DISTINCT berfungsi untuk menampilkan nilai yang berbeda. Biasanya dalam tabel terdapat kolom yang memiliki nilai duplikat, kita bisa menggunakan Distinct untuk menghilangkan nilai duplikat tersebut. Cara menggunakan Distinct yaitu bersamaan dengan Select seperti syntax di bawah ini :

SELECT DISTINCT Country
  FROM customers;

WHERE

WHERE berfungsi untuk menyaring atau memfilter record. Dengan kata lain, Where juga dipakai dalam menentukan kondisi ketika bergabung dengan beberapa tabel atau mengambil data dari satu tabel. Selain bersamaan dengan SELECT, Where juga bisa digunakan bersamaan dengan UPDATE, DELETE, dan perintah dasar SQL lainnya. Berikut ini syntax untuk memilih semua Customer dari negara Mexico :

SELECT *
  FROM Customers
    WHERE Country='Mexico';

Untuk menggunakan WHERE, kita harus memahami operator-operator yang digunakan dalam WHERE. Operator tersebut yaitu :
1. = (sama dengan)
2. > (lebih besar dari)
3. < (lebih kecil dari)
4. >= (lebih besar sama dengan)
5. <= (lebih kecil sama dengan)
6. <> atau != (tidak sama)
7. IS NULL atau IS NOT NUL (field tanpa nilai)
8. BETWEEN (antara range tertentu)
9. LIKE (mencari pola)
10. IN (menentukan beberapa kemungkinan nilai untuk kolom)

BETWEEN

BETWEEN berfungsi untuk memilih nilai dalam range tertentu. nilai tersebut bisa berupa angka, teks, ataupun tanggal. Between bisa digunakan bersamaan dengan SELECT, INSERT, UPDATE, atau DELETE.

Di bawah ini merupakan syntax Between menggunakan range angka :

SELECT *
  FROM Products
    WHERE Price
      BETWEEN 10 AND 20;

Di bawah ini merupakan syntax Between menggunakan range tanggal :

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

IN

IN berfungsi untuk menentukan beberapa kemungkinan nilai untuk kolom. Kita bisa gunakan IN bersamaan dengan WHERE jika ingin menggunakan lebih dari 2 kondisi. Berikut ini syntax dengan memilih semua Customer yang berlokasi di “Germany”, “France”, atau “UK” :

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

Berikut ini syntax dengan memilih semua Customer yang berasal dari negara yang sama dengan Suppliernya :

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

LIKE

LIKE berfungsi untuk mencari pola yang spesifik pada kolom. Like digunakan bersamaan dengan WHERE. Biasanya pada like, kita juga menggunakan % (mewakili multi character) atau _ (mewakili single character). Di bawah ini syntax dengan memilih semua Customer yang CustomerName-nya dimulai dengan huruf “a” :

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

Penerapan % dan _ sebagai berikut :
1. WHERE CustomerName LIKE ‘a%’ (mencari nilai berawalan “a”)
2. WHERE CustomerName LIKE ‘%a’ (mencari nilai berakhiran “a”)
3. WHERE CustomerName LIKE ‘%or%’ (mencari nilai yang memiliki “or” di suatu posisi)
4. WHERE CustomerName LIKE ‘r%’ (mencari nilai yang memiliki “r” di posisi kedua)
5. WHERE CustomerName LIKE ’a%’ (mencari nilai berawalan “a” dan setidaknya 2 karakter panjangnya)
6. WHERE CustomerName LIKE ’a__%’ (mencari nilai berawalan “a” dan setidaknya 3 karakter panjangnya)
7. WHERE ContactName LIKE ‘a%o’

AND, OR and NOT

Argumen ini bisa dikombinasikan dengan argumen WHERE.
- 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.

Syntax di bawah ini memilih semua field dari “Customers” yang negaranya adalah “Germany” dan kotanya “Berlin atau”München” :

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 berfungsi untuk mengurutkan data dari besar ke kecil (z-a) atau bisa juga kecil ke besar (a-z) berdasarkan satu atau lebih kolom.
- 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.

Berikut ini syntaxnya :

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

LIMIT

LIMIT berfungsi untuk menentukan jumlah record yang akan ditampilkan. Selain bersamaan dengan WHERE dan ORDER BY, LIMIT juga bisa dipadukan dengan OFFSET.

top 3 rows:

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

top 5 rows setelah top 3 rows :

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

MIN and MAX

MIN dan MAX berfungsi untuk menentukan nilai terkecil dan terbesar dalam bidang berdasakan pengelompokan yang ditentukan.

syntax min untuk menentukan harga produk termurah:

SELECT MIN(Price) AS SmallestPrice
  FROM Products;

syntax max untuk menentukan harga produk terbesar:

SELECT MAX(Price) AS LargestPrice
  FROM Products;

COUNT, SUM and AVG

COUNT berfungsi untuk menentukan jumlah baris yang cocok dengan kriteria tertentu. SUM berfungsi untuk menjumlah total kolom numerik. AVG berfungsi untuk menghitung nilai rata-rata kolom numerik.

syntax count :

SELECT COUNT(P.Price)
  FROM Products P;

syntax sum :

SELECT SUM(P.Price)
  FROM Products P;

syntax avg :

SELECT AVG(P.Price)
  FROM Products P;

HAVING

HAVING digunakan sebagai pengganti WHERE dengan fungsi agregat (COUNT, MAX, MIN, SUM, AVG).

syntax having dengan menggunakan fungsi COUNT:

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 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;

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

Exercise

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

1. Select Some attributes of suppliers in alphabetical order!

SELECT SupplierName, ContactName, Address, Country, City
FROM suppliers
ORDER BY SupplierName

2. Some attributes of suppliers in reverse alphabetical order!

SELECT SupplierName, ContactName, Address, Country, City
FROM suppliers
ORDER BY SupplierName DESC

3. Some attributes of suppliers ordered by country, then by city!

SELECT SupplierName, ContactName, Address, Country, City
FROM suppliers
ORDER BY Country, City

4. All atributes of suppliers and reverse alphabetical ordered by country, then by city!

SELECT *
FROM suppliers
ORDER BY Country DESC, City DESC

5. All orders, sorted by total amount, the largest first!

SELECT o.*, od.Quantity
  FROM orders o
    JOIN orderdetails od
      ON o.OrderID = od.OrderID
        ORDER BY od.Quantity DESC;

6. Get all but the 10 most expensive products sorted by price!

SELECT *
FROM products
ORDER BY Price DESC
LIMIT 10

7. Get the 10th to 15th most expensive products sorted by price!

SELECT *
FROM products
ORDER BY Price DESC
LIMIT 10,15;

8. List all supplier countries in alphabetical order!

SELECT DISTINCT Country
FROM suppliers
ORDER BY Country

9. Find the cheapest product and Expensive Orders!

SELECT ProductName, Unit, Price
FROM products
ORDER BY Price
LIMIT 1

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

10. Find the number of Supplier USA!

SELECT *
FROM suppliers
WHERE Country='USA'

11. Compute the total Quantity of orderitem!

SELECT SUM(Quantity)
FROM orderdetails

12. Compute the average UnitPrice of all product!

SELECT AVG(Price)
FROM products

13. Get all information about customer named Thomas Hardy!

SELECT *
FROM customers
WHERE ContactName='Thomas Hardy'

14. List all customers from Spain or France!

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

15. List all customers that are not from the USA!

SELECT *
FROM customers
WHERE Country!='USA'

16. List all orders that not between $50 and $15000!

SELECT o.OrderDate, p.ProductID, p.ProductName, p.Price
FROM orders o
JOIN orderdetails od
ON o.OrderID = od.OrderID
JOIN products p
ON od.ProductID = p.ProductID
WHERE Price NOT BETWEEN 50 AND 15000

17. List all products between $10 and $20

SELECT *
FROM products
WHERE Price BETWEEN 10 AND 20
ORDER BY Price

18. List all products not between $10 and $100 sorted by price!

SELECT *
FROM products
WHERE Price NOT BETWEEN 10 AND 20
ORDER BY Price

19. Get the list of orders and amount sold between 1996 Jan 01 and 1996 Des 31!

SELECT *
FROM orders
WHERE OrderDate BETWEEN '1996-01-01' AND '1996-12-31';

20. List all suppliers from the USA, UK, OR Japan!

SELECT *
FROM suppliers
WHERE Country='USA' OR Country='UK' OR Country='Japan'

21. List all products that are not exactly $10, $20, $30, $40, or $50!

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

22. List all customers that are from the same countries as the suppliers!

SELECT c.CustomerName, s.SupplierName, c.Country, s.Country
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 c.Country = s.Country

23. List all products that start with ‘Cha’ or ‘Chan’ and have one more character!

SELECT *
FROM products
WHERE ProductName LIKE 'Cha_' OR ProductName LIKE 'Chan_'

24. List all suppliers that do have a fax number!

Tidak ada kolom nomor Fax, maka dianggap para pembeli tidak mempunyai nomor Fax.

25. List all customer with average orders between $1000 and $1200 !

SELECT CustomerName, AVG(TotalAmount) AverageOrders
FROM 
(
SELECT CustomerName, SUM(od.Quantity * p.Price) TotalAmount
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
) A
GROUP BY CustomerName
HAVING AverageOrders
BETWEEN 1000 AND 1200
ORDER BY AverageOrders

26. List total customers in each country.

SELECT Country, COUNT(CustomerName) TotalCustomer
FROM customers
GROUP BY Country