1 Petunjuk

Sistem relasi basis data yang sistematis dan tertata rapi akan membuat kemudahan perusahaan untuk mengambil data dan menganalisis data untuk menjawab suatu permasalahan/pertanyaan. Pertanyaan tersebut akan membutuhkan informasi dari database tersebut sesuai dengan kondisi yang dihadapi

2 Mengkoneksikan R ke MySQL

Berikut merupakan program untuk menghubungkan rstudio dengan sistem basis data MySQL

library(RMySQL)

## Loading required package: DBI

library(DBI)
albert <- dbConnect(RMySQL::MySQL(),
                    dbname='new_mariadb',
                    username='root',
                    password='',
                    host='localhost',
                    port=3306)
knitr::opts_chunk$set(connection="albert")

3 Select

SQL select digunakan untuk menyeleksi kolom yang dibutuhkan untuk diamati dan dianalisis dalam suatu basis data.

Berikut merupakan proses Select kolom CustomerName, Address, City dan Country dari tabel basis data customers

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

Berikut merupakan cara untuk menyeleksi semua field pada table basis data customer.

SELECT *
FROM customers

Displaying records 1 - 10
CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Alfred 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

4 DISTINCT

Distinct merupakan fungsi yang kegunaanya hampir sama dengan fungsi Select. Hanya saja fungsi distinct hanya menampilkan records yang diseleksi tanpa duplikasinya. Sehingga setiap records hanya muncul satu kali saja

Query dibawah merupakan penerapan fungsi Distinct dengan menDISTINCT kolom country dari tabel basis data customers.

SELECT DISTINCT Country
FROM customers;

Displaying records 1 - 10
Country
Germany
Mexico
UK
Sweden
France
Spain
Canada
Argentina
Switzerland
Brazil

5 WHERE

Fungsi Where digunakan untuk memfilter records sesuai dengan apa yang kita mau. Query dibawah ini merupakan penerapan fungsi WHERE dengan menyeleksi semua kolom yang ada pada basis data 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

6 Between

Fungsi Between digunakan untuk menyeleksi sesuai range yang diberikan. Range tersebut dapat berupa nomor, waktu, atau tanggal. Dibawah merupakan penerapan fungsi between untuk menampilkan field pada tabel basis data 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

Berikut dibawah merupakan penerapan fungsi Between untuk menampilkan field pada tabel data 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
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

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

Dibawah merupakan fungsi In yang digunakan untuk 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	Alfred 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

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

9 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');

8 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

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

8 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

11 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

12 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

13 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

14 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
104	USA
88	Germany
88	France
72	Brazil
56	UK
40	Mexico
40	Spain
32	Venezuela
24	Argentina
24	Italy

15 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
Rattlesnake Canyon Grocery	Albuquerque	USA
Rattlesnake Canyon Grocery	Albuquerque	USA

Tugas 3

Sistem Basis Data

Albert Agung Prayogo (20204920019)

March 02, 2022