*Kontak **\(\downarrow\)*
Email
Instagram https://www.instagram.com/its_bangjeki/
RPubs https://rpubs.com/valensiusjimy/
Nama Valensius Jimy
NIM 20214920005

1 Pendahuluan

  Jika pada sebelumnya sudah berbicara tentang bagaiamana menggabungkan tabel di SQL dan bagaimana memproses dengan sederhana. Saat ini, kita akan berbicara lebih jauh lagi untuk melakukan proses manipulasi data di SQL dan karena kita akan mengeksekusinya dengan bantuan aplikasi R, maka kita perlu untuk menyambungkan antara R dengan SQL yang kita miliki dan jangan lupa untuk memanggil library yang akan digunakan.

pacman::p_load(RMariaDB,
               RMySQL,
               RSQLite,
               DBI)

setelah itu, kita dapat menyambungkan dengan sintaks berikut.

jekiw <- dbConnect(MariaDB(),
                   user = 'root',
                   password = '',
                   dbname = 'bang jeki',
                   host = 'localhost')
knitr::opts_chunk$set(connection = "jekiw")

2 Select

Adapun untuk fungsi pertama adalah select yang bertujuan untuk memilih beberapa kolom dari suatu tabel. Penggunaannya adalah sebagai berikut

select customername, address, city
from customers
Displaying records 1 - 10
customername address city
Alfreds Futterkiste Obere Str. 57 Berlin
Ana Trujillo Emparedados y helados Avda. de la Constitución 2222 México D.F.
Antonio Moreno Taquería Mataderos 2312 México D.F.
Around the Horn 120 Hanover Sq. London
Berglunds snabbköp Berguvsvägen 8 Luleå
Blauer See Delikatessen Forsterstr. 57 Mannheim
Blondel père et fils 24, place Kléber Strasbourg
Bólido Comidas preparadas C/ Araquil, 67 Madrid
Bon app’ 12, rue des Bouchers Marseille
Bottom-Dollar Marketse 23 Tsawassen Blvd. Tsawassen

dapat dilihat dari hasil di atas, bahwa terdapat data kustomer yang memuat nama kustomer, alamat dan kota tempat tinggalnya. Dengan adanya fungsi ini memudahkan untuk melihat informasi dari sebuah data dengan ringkas. Kita juga bisa untuk memanggil semua kolom pada sebuah tabel dengan menggunakan sintaks *.

select *
from customers
Displaying records 1 - 10
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 5021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 5023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
7 Blondel père et fils Frédérique Citeaux 24, place Kléber Strasbourg 67000 France
8 Bólido Comidas preparadas Martín Sommer C/ Araquil, 67 Madrid 28023 Spain
9 Bon app’ Laurence Lebihans 12, rue des Bouchers Marseille 13008 France
10 Bottom-Dollar Marketse Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen T2F 8M4 Canada

terlihat perbedaannya yang sangat mencolok, dimana ketika menggunakan sintaks * akan tampil semua kolom yang terdapat pada tabel customers.

3 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;
Displaying records 1 - 10
Country
Germany
Mexico
UK
Sweden
France
Spain
Canada
Argentina
Switzerland
Brazil

4 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='sweden';
2 records
CustomerID CustomerName ContactName Address City PostalCode Country
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
24 Folk och fä HB Maria Larsson Åkergatan 24 Bräcke S-844 67 Sweden

5 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;
Displaying records 1 - 10
ProductID ProductName SupplierID CategoryID Unit Price
1 Chais 1 1 10 boxes x 20 bags 18.00
2 Chang 1 1 24 - 12 oz bottles 19.00
3 Aniseed Syrup 1 2 12 - 550 ml bottles 10.00
15 Genen Shouyu 6 2 24 - 250 ml bottles 15.50
16 Pavlova 7 3 32 - 500 g boxes 17.45
21 Sir Rodney’s Scones 8 3 24 pkgs. x 4 pieces 10.00
25 NuNuCa Nuß-Nougat-Creme 11 3 20 - 450 g glasses 14.00
31 Gorgonzola Telino 14 4 12 - 100 g pkgs 12.50
34 Sasquatch Ale 16 1 24 - 12 oz bottles 14.00
35 Steeleye Stout 16 1 24 - 12 oz bottles 18.00

Contoh untuk BETWEEN dengan range date

SELECT *
  FROM Orders O
    WHERE O.OrderDate
      BETWEEN '1996-07-01' AND '1996-07-31';
Displaying records 1 - 10
OrderID CustomerID EmployeeID OrderDate ShipperID
10248 90 5 1996-07-04 3
10249 81 6 1996-07-05 1
10250 34 4 1996-07-08 2
10251 84 3 1996-07-08 1
10252 76 4 1996-07-09 2
10253 34 3 1996-07-10 2
10254 14 5 1996-07-11 2
10255 68 9 1996-07-12 3
10256 88 3 1996-07-15 2
10257 35 4 1996-07-16 3

6 IN

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

SELECT *
  FROM Customers C
    WHERE C.Country
      IN ('Germany', 'France', 'UK');
Displaying records 1 - 10
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
7 Blondel père et fils Frédérique Citeaux 24, place Kléber Strasbourg 67000 France
9 Bon app’ Laurence Lebihans 12, rue des Bouchers Marseille 13008 France
11 B’s Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK
16 Consolidated Holdings Elizabeth Brown Berkeley Gardens 12 Brewery London WX1 6LT UK
17 Drachenblut Delikatessend Sven Ottlieb Walserweg 21 Aachen 52066 Germany
18 Du monde entier Janine Labrune 67, rue des Cinquante Otages Nantes 44000 France
19 Eastern Connection Ann Devon 35 King George London WX3 6FW UK 
SELECT *
  FROM Customers C
    WHERE C.Country
      IN (SELECT S.Country FROM Suppliers S);
Displaying records 1 - 10
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
7 Blondel père et fils Frédérique Citeaux 24, place Kléber Strasbourg 67000 France
8 Bólido Comidas preparadas Martín Sommer C/ Araquil, 67 Madrid 28023 Spain
9 Bon app’ Laurence Lebihans 12, rue des Bouchers Marseille 13008 France
10 Bottom-Dollar Marketse Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen T2F 8M4 Canada
11 B’s Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK
15 Comércio Mineiro Pedro Afonso Av. dos Lusíadas, 23 São Paulo 05432-043 Brazil

7 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%';
4 records
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 5021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 5023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

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

8 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');
2 records
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany

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

SELECT * 
  FROM Customers C
    WHERE C.Country <> 'Germany' AND C.Country <> 'USA';
Displaying records 1 - 10
CustomerID CustomerName ContactName Address City PostalCode Country
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 5021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 5023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
7 Blondel père et fils Frédérique Citeaux 24, place Kléber Strasbourg 67000 France
8 Bólido Comidas preparadas Martín Sommer C/ Araquil, 67 Madrid 28023 Spain
9 Bon app’ Laurence Lebihans 12, rue des Bouchers Marseille 13008 France
10 Bottom-Dollar Marketse Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen T2F 8M4 Canada
11 B’s Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK
12 Cactus Comidas para llevar Patricio Simpson Cerrito 333 Buenos Aires 1010 Argentina

9 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;
2 records
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany

10 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;
2 records
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany 
SELECT CustomerName, Address, City, Country
  FROM customers C
  ORDER BY C.City, C.Country DESC
    LIMIT 3, 5;
5 records
CustomerName Address City Country
Vaffeljernet Smagsløget 45 Århus Denmark
Galería del gastrónomo Rambla de Cataluña, 23 Barcelona Spain
LILA-Supermercado Carrera 52 con Ave. Bolívar #65-98 Llano Largo Barquisimeto Venezuela
Magazzini Alimentari Riuniti Via Ludovico il Moro 22 Bergamo Italy
Alfreds Futterkiste Obere Str. 57 Berlin Germany

11 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;
1 records
SmallestPrice
2.5 
SELECT MAX(P.Price) AS LargestPrice
  FROM Products P;
1 records
LargestPrice
263.5

12 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;
1 records
AVG(P.Price)
28.86636

13 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;
5 records
COUNT(C.CustomerID) Country
13 USA
11 Germany
11 France
9 Brazil
7 UK

14 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;
Displaying records 1 - 10
OrderID Quantity QuantityText
10248 12 The quantity is under 30
10248 10 The quantity is under 30
10248 5 The quantity is under 30
10249 9 The quantity is under 30
10249 40 The quantity is greater than 30
10250 10 The quantity is under 30
10250 35 The quantity is greater than 30
10250 15 The quantity is under 30
10251 6 The quantity is under 30
10251 15 The quantity is under 30 
SELECT CustomerName, City, Country
FROM Customers C
ORDER BY
(CASE
    WHEN C.City IS NULL THEN C.Country
    ELSE C.City
END);
Displaying records 1 - 10
CustomerName City Country
Drachenblut Delikatessend Aachen Germany
Rattlesnake Canyon Grocery Albuquerque USA
Old World Delicatessen Anchorage USA
Vaffeljernet Århus Denmark
Galería del gastrónomo Barcelona Spain
LILA-Supermercado Barquisimeto Venezuela
Magazzini Alimentari Riuniti Bergamo Italy
Alfreds Futterkiste Berlin Germany
Chop-suey Chinese Bern Switzerland
Save-a-lot Markets Boise USA