Email             :
RPubs            : https://rpubs.com/ardifo/
Jurusan          : Statistika
Address         : ARA Center, Matana University Tower
                         Jl. CBD Barat Kav, RT.1, Curug Sangereng, Kelapa Dua, Tangerang, Banten 15810.

1 Introduction

Kekuatan sebenarnya dari database relasional terletak pada kemampuannya untuk mengambil dan menganalisis data kita dengan cepat dengan menjalankan query. Query memungkinkan kita menarik informasi dari satu atau beberapa tabel berdasarkan serangkaian kondisi pencarian yang Anda tetapkan. Di bagian ini, kita akan mempelajari cara membuat Query satu tabel sederhana.

Pertama, kita perlu connect ke database kita. Silahkan ketik kode berikut di console R kita.

# set up connection and save it into workspace
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
library(RMySQL)
## Loading required package: DBI
library(DBI)
lopo <- dbConnect(RMySQL::MySQL(),
                    dbname ='new_mariadb',
                    username='root',
                    password ='',
                    host ='localhost',
                    port =3306)

knitr::opts_chunk$set(connection = "lopo") # to set up the connection in your Rmarkdown chunk

2 Select

SQL SELECT digunakan untuk mengambil data dari tabel database yang mengembalikan data ini dalam bentuk tabel hasil. Tabel hasil ini disebut juga result-sets. Sintax dasar dari statment adalah sebagai berikut:

SELECT column1, column2, columnN
  FROM table_name;

Asumsikan column1, column2… adalah bidang label yang nilainya ingin kita ambil. Nah, jika kita ingin mengambil beberapa bidang yang tersedia di bidang tersebut, maka kita dapat menggunakan sintax berikut.

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

selanjutnya, merupakan cara untuk menyeleksi semua field pada tabel data customers kita.

SELECT *
  FROM customers
Displaying records 1 - 10
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Alfed Schmidt Obere Str. 57 Frankfurt 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

3 Distinct

Distint kegunaannya adalah sampir sama dengan Select, hanya saja distinct menamilkan record yang di seleksi tanpa duplikasi, sehingga setiap records hanya muncul sekali

Query dibawah adalah penenrapan fungsi distinct dengan menDISTINCT column country dari tabel customer.

SELECT DISTINCT Country 
  FROM customers;
Displaying records 1 - 10
Country
Germany
Mexico
UK
Sweden
France
Spain
Canada
Argentina
Switzerland
Brazil

4 Where

Fungsi Where ini digunakan untuk memfilter records sesuai apa yang kita ingin, Query dibawah ini adalah penerapan fungsi Where dengan menyeleksi semua kolom yang ada pada tabel customers yang memiliki filter country yang berasal dari Mexico

SELECT *
FROM customers 
WHERE Country='mexico';
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
13 Centro comercial Moctezuma Francisco Chang Sierras de Granada 9993 México D.F. 5022 Mexico
58 Pericles Comidas clásicas Guillermo Fernández Calle Dr. Jorge Cash 321 México D.F. 5033 Mexico
80 Tortuga Restaurante Miguel Angel Paolino Avda. Azteca 123 México D.F. 5033 Mexico
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
13 Centro comercial Moctezuma Francisco Chang Sierras de Granada 9993 México D.F. 5022 Mexico
58 Pericles Comidas clásicas Guillermo Fernández Calle Dr. Jorge Cash 321 México D.F. 5033 Mexico
80 Tortuga Restaurante Miguel Angel Paolino Avda. Azteca 123 México D.F. 5033 Mexico

5 Between

Fungsi Between menyeleksi sesuai range yang diberikan. Range tersebut dapat berupa nomor, waktu, atau tanggal. Dibawah penerapan fungsi between untuk menampilkan field pada tabel products dengan harga diantara 10 dan 20

SELECT *
FROM products
WHERE 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

Nah, bibawah ini juga penerepan fungsi Between untuk menampilkan field tada table orders, dimana Order Date ada diantara 1 Juli 1996 hingga 31 Juli 1996

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

6 In

Fungsi In merupakan fungsi yang digunakan untuk menyeleksi dengan kriteria banyak value. Fungsi ini merupakan pengembangan dari fungsi Where

Dibawah merupakan penerapan fungsi In untuk menyeleksi field kolom COuntry yang berisi Germany, France, dan UK pada tabel basis data Customers

SELECT * 
FROM customers 
WHERE Country 
IN ('Germany','France','UK');
Displaying records 1 - 10
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Alfed Schmidt Obere Str. 57 Frankfurt 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

Selanjutnya dibawah ini fungsi In yang digunakan untk menyeleksi semua customers yang berasal dari negara yang sama dengan suppliers.

SELECT *
FROM customers
WHERE Country
IN (SELECT Country FROM suppliers);
Displaying records 1 - 10
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Alfed Schmidt Obere Str. 57 Frankfurt 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

Operator LIKE merupakan pengembangan fungsi WHERE untuk mencari pola tertentu dalam suatu kolom. Pada fungsi Like terdapat dua operator :

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

_ : Tanda underscore mewakili single karakter

Query dibawah merupakan penerapan fungsi Like yang digunakan untuk menyeleksi semua kolom pada basis data customer dimana CustomerName diawali dengan huruf A

SELECT *
FROM customers
WHERE CustomerName
LIKE 'a%';
Displaying records 1 - 10
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Alfed Schmidt Obere Str. 57 Frankfurt 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
1 Alfreds Futterkiste Alfed Schmidt Obere Str. 57 Frankfurt 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
1 Alfreds Futterkiste Alfed Schmidt Obere Str. 57 Frankfurt 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 5021 Mexico 
Berikut Merupakan beberapa contoh like operator

Like ‘a%’ mencari nilai yang diawali dengan huruf A

Like ‘%a’ mencari nilai yang diakhiri dengan huruf A

Like ‘%or%’ mencari nilai yang terdapat or disemua posisi

Like ’_r%’ Mencari nilai yang terdapat huruf r pada posisi kedua

Like ’a_% mencari nilai yang diawali dengan dengan huruf a dan memiliki paling sedikit 2 karakter

Like’a__%’ Mencari nilai yang diawali dengan huruf A dan memiliki paling sedikit 3 karakter

8 AND OR NOT

Fungsi Where dapat dikombinasikan dengan AND, OR, dan NOT operators. The AND and OR digunakan untuk memfilter record berdasarkan lebih dari 1 kondisi :

Operator AND akan menampilkan record jika semua kondisi yang dipisahkan dengan fungsi AND adalah TRUE

Operator OR akan menampilkan record jika salah satu kondisi yang dipisahkan dengan OR adalah TRUE

Operator NOT akan menampilkan Record apabila tidak ada kondisi yang terpenuhi

Query dibawah merupakan menyeleksi semua records yang ada pada basis data pada tabel customers dengan kondisi yang dengan Country bernilai Germany dan dengan CITY yang bernilai Berlin atau Munchen.

SELECT * FROM customers
WHERE Country='Germany' AND (City='Berlin' OR City='Munchen');
9 records
CustomerID CustomerName ContactName Address City PostalCode Country
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany

9 ORDER BY

Fungsi Order By digunakan untuk menyeleksi data secara ascending or descending berdasarkan satu atau lebih kolom. Beberapa basis data biasanya sudah mengurutkan basis data dengan ascending

Berikut merupakan penggunaan fungsi order By dengan menyeleksi semua records pada tabel basis data customers dengan kondisi Country bernilai Germany dan Kota yang berasal dari Berlin atau Munchen dan kemudian mengurutkan country dan city secara ascending.

SELECT * FROM customers
WHERE COuntry='Germany' AND (City='Berlin' OR City='Munchen')
ORDER BY Country, City;
9 records
CustomerID CustomerName ContactName Address City PostalCode Country
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany

10 LIMIT

Fungsi Limit digunakan untuk membatasi jumlah record yang akan ditampilkan

Query dibawah merupakan cara untuk menampilkan seleksi semua records pada table customers dengan kondisi country bernilai Germany dan City bernilai Berlin atau Munchen kemudian kolom Country dan City diurutkan ascending berjumlah 3 records yang paling atas

SELECT * FROM customers
WHERE Country='Germany' AND (City='Berlin' OR City='Munchen')
ORDER BY Country, City
LIMIT 3;
3 records
CustomerID CustomerName ContactName Address City PostalCode Country
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany

Query dibawah merupakan cara untuk menampilkan seleksi semua records pada table customers dengan kondisi country bernilai Germany dan City bernilai Berlin atau Munchen kemudian kolom Country dan City diurutkan ascending berjumlah 5 records yang paling atas setelah 3 records.

SELECT CustomerName, Address, City, Country
FROM customers
ORDER BY City, Country DESC
LIMIT 3,5;
5 records
CustomerName Address City Country
Drachenblut Delikatessend Walserweg 21 Aachen Germany
Drachenblut Delikatessend Walserweg 21 Aachen Germany
Drachenblut Delikatessend Walserweg 21 Aachen Germany
Drachenblut Delikatessend Walserweg 21 Aachen Germany
Drachenblut Delikatessend Walserweg 21 Aachen Germany

11 MIN AND MAX

Menampilkan nilai minimal dari kolom yang diseleksi

Query dibawah merupakan cara mencari nilai minimal pada kolom Price yang diambil dari tabel basis data products

SELECT MIN(Price) AS SmallestPrice
FROM products;
1 records
SmallestPrice
2.5

Query dibawah merupakan cara mencari nilai maksimal pada kolom Price yang diambil dari tabel basis data products

SELECT MAX(Price) AS LargestPrice
FROM products
1 records
LargestPrice
263.5

12 COUNT, SUM, and AVERAGE

Fungsi COUNT merupakan fungsi yang digunakan untuk menghitung jumlah baris yang sesuai dengan kondisi

Query dibawah merupakan cara untuk menghitung rataan pada kolom Price pada tabel basis data Products

SELECT AVG(Price)
FROM products;
1 records
AVG(Price)
28.86636

13 HAVING

Fungsi HAVING merupakan pengembangan dari fungsi WHERE untuk menambahkan suatu kondisi agregat.

Berikut merupakan penerapan fungsi having untuk menghitung jumlah baris pada customer ID, dan menyeleksi kolom Country pada tabel Customer dan kemudian pengelompokkan berdasarkan Country. Record yang ditampilkan hanya negara yang memiliki lebih dari 5 customers dan kemudian nama negara diurutkan secara DESCENDING.

SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
Having COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;
Displaying records 1 - 10
COUNT(CustomerID) Country
117 USA
99 Germany
99 France
81 Brazil
63 UK
45 Spain
45 Mexico
36 Venezuela
27 Canada
27 Argentina

14 CASE

FUngsi Case merupakan fungsi SQL yang sangat mirip dengan fungsi IF THEN ELSE. Ketika suatu kondisi benar maka komputer akan berhenti membaca dan memberikan output.

Berikut merupakan Query yang digunakan dengan menyeleksi orderID dan Quantity. Untuk Quantity yang lebih dari 30 menambahan text the quantity greater than 30, untuk quantity = 30 menambahkan text the quantity is 30 selain itu menampilkan text the Quantity is under 30. Text ditampilkan pada kolom dengan nama QuantityText. ORDER ID dan Quantity diambil dari table basis data orderdetails

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

Query dibawah menyeleksi CustomerName, City, Country yang berasal dari tabel basis data Customers dimana data diurutkan secara Ascending. Tetapi, jika City bernilai NULL kemudian diurutkan berdasarkan Country.

SELECT CustomerName, City, Country
FROM customers
ORDER BY
(CASE
WHEN City IS NULL THEN Country
ELSE City
END);
Displaying records 1 - 10
CustomerName City Country
Drachenblut Delikatessend Aachen Germany
Drachenblut Delikatessend Aachen Germany
Drachenblut Delikatessend Aachen Germany
Drachenblut Delikatessend Aachen Germany
Drachenblut Delikatessend Aachen Germany
Drachenblut Delikatessend Aachen Germany
Drachenblut Delikatessend Aachen Germany
Drachenblut Delikatessend Aachen Germany
Drachenblut Delikatessend Aachen Germany
Rattlesnake Canyon Grocery Albuquerque USA