Email         : naufal3433@gmail.com
RPubs       : https://rpubs.com/muhammad_naufal/
Jurusan : Statistika
Address : Jalan Gunung Galunggung 5 Blok E9, No. 21
   .

1 PENGENALAN DASAR

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

Pertama, kita perlu terhubung ke database kita. Silakan ketik kode berikut di konsol R Anda:

# Mengatur koneksi dan simpan ke ruang kerja

library(RMySQL)

## Loading required package: DBI

library(DBI)
library(dbplyr)

naufal <- dbConnect(RMySQL::MySQL(),
                  dbname='mysql',
                  username='root',
                  password='', 
                  host='localhost',
                  port=3306)
knitr::opts_chunk$set(connection = "naufal") # mengatur koneksi di Rmarkdown chunk

2 SELECT

Pernyataan SQL SELECT digunakan untuk mengambil data dari tabel database yang mengembalikan data ini dalam bentuk tabel hasil. Tabel hasil ini disebut hasil-set. Sintaks dasar dari pernyataan SELECT adalah sebagai berikut:

PILIH kolom1, kolom2, kolomN DARI nama_tabel

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

Jika ingin mengambil semua bidang tabel PELANGGAN, maka bentuk query sebagai berikut.

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

3 DISTINCT

Perintah SELECT DISTINCT merupakan perintah dasar SQL yang di gunakan untuk mengembalikan hanya nilai yang berbeda dari dalam sebuah tabel, dengan kata lain semua record duplikat (record dengan nilai yang sama) yang terdapat pada tabel akan di anggap sebagai satu record/nilai.

Berikut merupakan syntax dasar yang digunakan dalam DISTINCT untuk menghilangkan rekaman duplikat.

SELECT DISTINCT Country   
  FROM customers;

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

4 WHERE

Perintah WHERE merupakan perintah dasar SQL yang di gunakan untuk mem-filter hasil SELECT dengan mengekstrak record yang memenuhi persyaratan tertentu.

Pernyataan SQL berikut memilih semua pelanggan dari negara “Meksiko”, di tabel “Pelanggan”

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

Berikut merupakan operator yang dapat digunakan dalam WHERE.

= : Equal.
: Lebih besar dari.
< : Lebih kecil dari.
= : Lebih besar dari atau sama dengan.
<= :Lebih kecil dari atau sama dengan.
<> : Tidak sama (atau bisa menggunkan !=).
IS NULL or IS NOT NUL : Bidang dengan nilai NULL adalah bidang tanpa nilai.
BETWEEN : Antara rentang tertentu.
LIKE : Cari pola.
IN : Menentukan beberapa kemungkinan nilai untuk kolom.

5 BETWEEN

Fungsi BETWEEN dalam SQL Server merupakan pernyataan kejadian diantara dua kriteria. Berikut contoh SQL memilih semua produk dengan harga BETWEEN 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 contoh SQL yang memilih semua pesanan dengan OrderDate BETWEEN ‘01-July-1996’ dan ‘31-July-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

6 IN

IN memungkinkan Anda untuk menentukan beberapa nilai dalam klausa WHERE. Operator IN adalah singkatan untuk beberapa kondisi OR.

Berikut ini contoh dari pernyataan SQL yang memilih semua pelanggan yang berlokasi di “Jerman”, “Prancis” atau “Inggris Raya”.

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

Berikut ini adalah contoh dari pernyataan SQL yang memilih semua pelanggan yang berasal dari negara yang sama dengan pemasok.

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

7 LIKE

Query yang dapat anda gunakan adalah operator LIKE. Operator LIKE ini memang digunakan untuk melakukan pencarian data pada database, berdasarkan teks / huruf yang dimasukan.

% : Tanda persen mewakili nol, satu, atau beberapa karakter.
_ : Garis bawah mewakili satu karakter.

Berikut contoh SQL yang memilih semua pelanggan dengan NamaPelanggan dimulai dengan “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 adalah beberapa contoh yang menunjukkan operator LIKE yang berbeda dengan wildcard ‘%’ dan ’_’.

WHERE CustomerName LIKE ‘a%’ : Menemukan nilai apa pun yang dimulai dengan “a”.
WHERE CustomerName LIKE ‘%a’ : Menemukan nilai apa pun yang diakhiri dengan “a”.
WHERE CustomerName LIKE ‘%or%’ : Menemukan nilai apa pun yang memiliki “or” di posisi apa pun.
WHERE CustomerName LIKE ’_r%’ : Menemukan nilai apa pun yang memiliki “r” di posisi kedua.
WHERE CustomerName LIKE ‘a_%’ : Menemukan nilai apa pun yang dimulai dengan “a” dan panjangnya minimal 2 karakter.
WHERE CustomerName LIKE ’a__%’ : Menemukan nilai apa pun yang dimulai dengan “a” dan panjangnya minimal 3 karakter.

8 AND, OR dan NOT

Operator AND, OR dan NOT merupakan perintah dasar SQL yang biasanya di kombinasikan dengan perintah WHERE. Ketiganya di gunakan untuk mem-filter record berdasarkan suatu kondisi, operator AND akan menampilkan record apabila semua kondisi bernilai TRUE, operator OR akan menampilkan record apabila salah satu kondisi bernilai TRUE, sedangkan operator NOT akan menampilkan record apabila semua kondisi bernilai FALSE.

Berikut contoh SQL yang memilih semua bidang dari “Pelanggan” di mana negara adalah “Jerman” DAN kota harus “Berlin” ATAU “München”.

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

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

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

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

9 ORDER BY

Perintah ORDER BY merupakan perintah dasar SQL yang di gunakan untuk mengurutkan result-set dalam pengurutan ‘ascending’ atau ‘descending’. Secara default perintah ORDER BY menampilkan record dalam pengurutan ‘ascending’ (‘ASC’). Untuk mengurutkan ‘descending’, gunakan kata kunci ‘DESC’.

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

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

10 LIMIT

Jika ada sejumlah besar tupel yang memenuhi kondisi kueri, mungkin lebih bijaksana untuk melihat hanya segelintir tupel dalam satu waktu.

Klausa LIMIT digunakan untuk menetapkan batas atas jumlah tupel yang dikembalikan oleh SQL
Penting untuk dicatat bahwa klausa ini tidak didukung oleh semua versi SQL.
Klausa LIMIT juga dapat ditentukan menggunakan klausa SQL 2008 OFFSET/FETCH FIRST.
Ekspresi limit/offset harus berupa bilangan bulat non-negatif.

Berikut ini contoh dari syntax LIMIT untuk mengumpulkan 3 baris TOP.

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München')
  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

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

5 records
CustomerName	Address	City	Country
Rattlesnake Canyon Grocery	2817 Milton Dr.	Albuquerque	USA
Rattlesnake Canyon Grocery	2817 Milton Dr.	Albuquerque	USA
Rattlesnake Canyon Grocery	2817 Milton Dr.	Albuquerque	USA
Old World Delicatessen	2743 Bering St.	Anchorage	USA
Old World Delicatessen	2743 Bering St.	Anchorage	USA

11 MIN dan MAX

Fungsi MIN() merupakan perintah dasar SQL yang di gunakan untuk mendapatkan nilai terkecil dari suatu kolom, Anda dapat menerapkannya pada kolom ‘harga’, ‘nilai’, ‘qty’ atau kolom yang semisal dengan itu, berbeda dengan perintah ORDEY BY, fungsi MIN() hanya menampilkan satu record saja yang memenuhi kriteria yang Anda tentukan.

Fungsi MAX() merupakan perintah dasar SQL yang di gunakan untuk mendapatkan nilai terbesar dari suatu kolom, seperti halnya fungsi MIN() Anda dapat menerapkannya pada kolom ‘harga’, ‘nilai’, ‘qty’ atau kolom yang semisal dengan itu.

Berikut contoh SQL yang menemukan harga produk termurah.

SELECT MIN(Price) AS SmallestPrice
  FROM Products;

1 records
SmallestPrice
2.5

Berikut adalah contoh dari pernyataan SQL yang menemukan harga produk paling mahal.

SELECT MAX(Price) AS LargestPrice
  FROM Products;

1 records
LargestPrice
263.5

12 COUNT, SUM, dan AVG

Fungsi COUNT() merupakan perintah dasar SQL yang di gunakan untuk mendapatkan jumlah hitungan record yang memenuhi suatu kriteria.

Fungsi SUM() merupakan perintah dasar SQL yang di gunakan untuk mendapatkan jumlah record yang memenuhi suatu kriteria, tentunya nilai pada kolom harus numerik.

Fungsi AVG() merupakan perintah dasar SQL yang di gunakan untuk mendapatkan rata-rata record yang memenuhi suatu kriteria, tentunya nilai pada kolom harus numerik.

Berikut contoh SQL yang menemukan harga rata-rata semua produk.

SELECT AVG(Price)
  FROM Products;

1 records
AVG(Price)
28.86636

13 HAVING

Pada HAVING ditambahkan ke SQL karena kata kunci WHERE tidak dapat digunakan dengan fungsi agregat.

Berikut contoh SQL yang mencantumkan jumlah pelanggan di setiap negara, diurutkan dari tinggi ke rendah (Hanya sertakan negara dengan lebih dari 5 pelanggan).

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
39	USA
33	Germany
33	France
27	Brazil
21	UK
15	Mexico
15	Spain
12	Venezuela
9	Italy
9	Canada

14 CASE

Pernyataan CASE melewati kondisi dan mengembalikan nilai ketika kondisi pertama terpenuhi (seperti pernyataan IF-THEN-ELSE). Jadi, setelah suatu kondisi benar, itu akan berhenti membaca dan mengembalikan hasilnya. Jika tidak ada kondisi yang benar, ia mengembalikan nilai dalam klausa ELSE.Jika tidak ada bagian ELSE dan tidak ada kondisi yang benar, ia mengembalikan NULL.

SQL berikut melewati kondisi dan mengembalikan nilai saat kondisi pertama terpenuhi.

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

SQL berikut akan mengurutkan pelanggan berdasarkan Kota. Namun, jika Kota adalah NULL, maka pesan berdasarkan Negara.

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
Rattlesnake Canyon Grocery	Albuquerque	USA
Rattlesnake Canyon Grocery	Albuquerque	USA
Rattlesnake Canyon Grocery	Albuquerque	USA
Old World Delicatessen	Anchorage	USA
Old World Delicatessen	Anchorage	USA
Old World Delicatessen	Anchorage	USA
Vaffeljernet	Ã…rhus	Denmark

15 EXERCISE

15.1 1

Pilih Beberapa atribut pemasok dalam urutan abjad!

# Mengatur koneksi dan simpan ke ruang kerja

library(RMySQL)
library(DBI)
library(DT)
naufal <- dbConnect(RMySQL::MySQL(),
                  dbname='mysql',
                  username='root',
                  password='', 
                  host='localhost',
                  port=3306)
knitr::opts_chunk$set(connection = "naufal") # mengatur koneksi di Rmarkdown chunk

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

Displaying records 1 - 10
SupplierID	SupplierName	Address	City	Country
18	Aux joyeux ecclÃ©siastiques	203, Rue des Francs-Bourgeois	Paris	France
18	Aux joyeux ecclÃ©siastiques	203, Rue des Francs-Bourgeois	Paris	France
18	Aux joyeux ecclÃ©siastiques	203, Rue des Francs-Bourgeois	Paris	France
16	Bigfoot Breweries	3400 - 8th Avenue Suite 210	Bend	USA
16	Bigfoot Breweries	3400 - 8th Avenue Suite 210	Bend	USA
16	Bigfoot Breweries	3400 - 8th Avenue Suite 210	Bend	USA
5	Cooperativa de Quesos ‘Las Cabras’	Calle del Rosal 4	Oviedo	Spain
5	Cooperativa de Quesos ‘Las Cabras’	Calle del Rosal 4	Oviedo	Spain
5	Cooperativa de Quesos ‘Las Cabras’	Calle del Rosal 4	Oviedo	Spain
27	Escargots Nouveaux	22, rue H. Voiron	Montceau	France

15.2 2

Beberapa atribut pemasok dalam urutan abjad terbalik!

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

Displaying records 1 - 10
SupplierID	SupplierName	Address	City	Country
22	Zaanse Snoepfabriek	Verkoop Rijnweg 22	Zaandam	Netherlands
22	Zaanse Snoepfabriek	Verkoop Rijnweg 22	Zaandam	Netherlands
22	Zaanse Snoepfabriek	Verkoop Rijnweg 22	Zaandam	Netherlands
4	Tokyo Traders	9-8 Sekimai Musashino-shi	Tokyo	Japan
4	Tokyo Traders	9-8 Sekimai Musashino-shi	Tokyo	Japan
4	Tokyo Traders	9-8 Sekimai Musashino-shi	Tokyo	Japan
17	Svensk SjÃ¶fÃ¶da AB	BrovallavÃ¤gen 231	Stockholm	Sweden
17	Svensk SjÃ¶fÃ¶da AB	BrovallavÃ¤gen 231	Stockholm	Sweden
17	Svensk SjÃ¶fÃ¶da AB	BrovallavÃ¤gen 231	Stockholm	Sweden
8	Specialty Biscuits, Ltd.	29 King’s Way	Manchester	UK

15.3 3

Beberapa atribut pemasok dipesan berdasarkan negara, lalu berdasarkan kota!

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

Displaying records 1 - 10
SupplierID	SupplierName	Address	City	Country
7	Pavlova, Ltd.	74 Rose St. Moonie Ponds	Melbourne	Australia
7	Pavlova, Ltd.	74 Rose St. Moonie Ponds	Melbourne	Australia
7	Pavlova, Ltd.	74 Rose St. Moonie Ponds	Melbourne	Australia
24	G’day, Mate	170 Prince Edward Parade Hunter’s Hill	Sydney	Australia
24	G’day, Mate	170 Prince Edward Parade Hunter’s Hill	Sydney	Australia
24	G’day, Mate	170 Prince Edward Parade Hunter’s Hill	Sydney	Australia
10	Refrescos Americanas LTDA	Av. das Americanas 12.890	SÃ£o Paulo	Brazil
10	Refrescos Americanas LTDA	Av. das Americanas 12.890	SÃ£o Paulo	Brazil
10	Refrescos Americanas LTDA	Av. das Americanas 12.890	SÃ£o Paulo	Brazil
25	Ma Maison	2960 Rue St. Laurent	MontrÃ©al	Canada

15.4 4

Semua atribut pemasok dan urutan abjad terbalik menurut negara, lalu menurut kota!

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

Displaying records 1 - 10
SupplierID	SupplierName	ContactName	Address	City	PostalCode	Country	Phone
2	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA	(100) 555-4822
2	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA	(100) 555-4822
2	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA	(100) 555-4822
19	New England Seafood Cannery	Robb Merchant	Order Processing Dept. 2100 Paul Revere Blvd.	Boston	2134	USA	(617) 555-3267
19	New England Seafood Cannery	Robb Merchant	Order Processing Dept. 2100 Paul Revere Blvd.	Boston	2134	USA	(617) 555-3267
19	New England Seafood Cannery	Robb Merchant	Order Processing Dept. 2100 Paul Revere Blvd.	Boston	2134	USA	(617) 555-3267
16	Bigfoot Breweries	Cheryl Saylor	3400 - 8th Avenue Suite 210	Bend	97101	USA	(503) 555-9931
16	Bigfoot Breweries	Cheryl Saylor	3400 - 8th Avenue Suite 210	Bend	97101	USA	(503) 555-9931
16	Bigfoot Breweries	Cheryl Saylor	3400 - 8th Avenue Suite 210	Bend	97101	USA	(503) 555-9931
3	Grandma Kelly’s Homestead	Regina Murphy	707 Oxford Rd.	Ann Arbor	48104	USA	(313) 555-5735

15.5 5

Semua pesanan, diurutkan berdasarkan jumlah total, yang terbesar dulu!

SELECT O.*, OD.Quantity
  FROM Orders O
    RIGHT JOIN orderdetails OD
      ON O.OrderID = OD.OrderID
        ORDER BY OD.Quantity DESC;

Displaying records 1 - 10
OrderID	CustomerID	EmployeeID	OrderDate	ShipperID	Quantity
10398	71	2	1996-12-30	3	120
10398	71	2	1996-12-30	3	120
10398	71	2	1996-12-30	3	120
10286	63	8	1996-08-21	3	100
10286	63	8	1996-08-21	3	100
10286	63	8	1996-08-21	3	100
10440	71	4	1997-02-10	2	90
10440	71	4	1997-02-10	2	90
10440	71	4	1997-02-10	2	90
10442	20	3	1997-02-11	2	80

15.6 6

Dapatkan semua kecuali 10 produk paling mahal yang diurutkan berdasarkan harga!

SELECT P.*
  FROM products P
    ORDER BY P.Price DESC
      LIMIT 10;

Displaying records 1 - 10
ProductID	ProductName	SupplierID	CategoryID	Unit	Price
38	CÃ´te de Blaye	18	1	12 - 75 cl bottles	263.50
38	CÃ´te de Blaye	18	1	12 - 75 cl bottles	263.50
38	CÃ´te de Blaye	18	1	12 - 75 cl bottles	263.50
29	ThÃ¼ringer Rostbratwurst	12	6	50 bags x 30 sausgs.	123.79
29	ThÃ¼ringer Rostbratwurst	12	6	50 bags x 30 sausgs.	123.79
29	ThÃ¼ringer Rostbratwurst	12	6	50 bags x 30 sausgs.	123.79
9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97.00
9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97.00
9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97.00
20	Sir Rodney’s Marmalade	8	3	30 gift boxes	81.00

15.7 7

Dapatkan produk termahal ke-10 hingga ke-15 yang diurutkan berdasarkan harga!

SELECT P.*
  FROM products P
    ORDER BY P.Price DESC
      LIMIT 6 OFFSET 9;

6 records
ProductID	ProductName	SupplierID	CategoryID	Unit	Price
20	Sir Rodney’s Marmalade	8	3	30 gift boxes	81.0
20	Sir Rodney’s Marmalade	8	3	30 gift boxes	81.0
20	Sir Rodney’s Marmalade	8	3	30 gift boxes	81.0
18	Carnarvon Tigers	7	8	16 kg pkg.	62.5
18	Carnarvon Tigers	7	8	16 kg pkg.	62.5
18	Carnarvon Tigers	7	8	16 kg pkg.	62.5

15.8 8

Daftar semua negara pemasok dalam urutan abjad!

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

Displaying records 1 - 10
Country
Australia
Brazil
Canada
Denmark
Finland
France
Germany
Italy
Japan
Netherlands

15.9 9

Temukan produk termurah dan Pesanan Mahal!

SELECT P.ProductName, P.Unit, P.Price
  FROM products P
      ORDER BY P.Price ASC
        LIMIT 1;

1 records
ProductName	Unit	Price
Geitost	500 g	2.5

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

1 records
ProductName	Unit	Price
CÃ´te de Blaye	12 - 75 cl bottles	263.5

15.10 10

Temukan nomor Supplier USA!

SELECT S.Country, COUNT(S.Country) AS 'Total_Supplier'
  FROM suppliers S
    WHERE S.Country='USA' ;

1 records
Country	Total_Supplier
USA	12

15.11 11

Hitung jumlah total orderitem!

SELECT SUM(OD.Quantity) AS 'TotalQuantity'
  FROM orderdetails OD

1 records
TotalQuantity
38229

15.12 12

Hitung Harga Satuan rata-rata dari semua produk!

SELECT AVG(P.Price) AS 'Average_UnitPrice'
  FROM products P

1 records
Average_UnitPrice
28.86636

15.13 13

Dapatkan semua informasi tentang pelanggan bernama Thomas Hardy!

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

3 records
CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK

15.14 14

Daftar semua pelanggan dari Spanyol atau Prancis!

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

Displaying records 1 - 10
CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
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
18	Du monde entier	Janine Labrune	67, rue des Cinquante Otages	Nantes	44000	France
22	FISSA Fabrica Inter. Salchichas S.A.	Diego Roel	C/ Moralzarzal, 86	Madrid	28034	Spain
23	Folies gourmandes	Martine RancÃ©	184, chaussÃ©e de Tournai	Lille	59000	France
26	France restauration	Carine Schmitt	54, rue Royale	Nantes	44000	France
29	GalerÃa del gastrÃ³nomo	Eduardo Saavedra	Rambla de CataluÃ±a, 23	Barcelona	8022	Spain
30	Godos Cocina TÃpica	JosÃ© Pedro Freyre	C/ Romero, 33	Sevilla	41101	Spain
40	La corne d’abondance	Daniel Tonini	67, avenue de l’Europe	Versailles	78000	France

15.15 15

Daftar semua pelanggan yang bukan dari AS!

SELECT C.*
  FROM customers C
    WHERE NOT Country="USA"

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

15.16 16

Daftar semua pesanan yang tidak antara $50 dan $15000!

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

Displaying records 1 - 10
OrderID	CustomerID	EmployeeID	OrderDate	ShipperID	ProductID	ProductName	Price
10248	90	5	1996-07-04	3	11	Queso Cabrales	21.00
10249	81	6	1996-07-05	1	14	Tofu	23.25
10250	34	4	1996-07-08	2	41	Jack’s New England Clam Chowder	9.65
10251	84	3	1996-07-08	1	22	Gustaf’s KnÃ¤ckebrÃ¶d	21.00
10252	76	4	1996-07-09	2	33	Geitost	2.50
10253	34	3	1996-07-10	2	31	Gorgonzola Telino	12.50
10253	34	3	1996-07-10	2	39	Chartreuse verte	18.00
10254	14	5	1996-07-11	2	24	GuaranÃ¡ FantÃ¡stica	4.50
10255	68	9	1996-07-12	3	2	Chang	19.00
10255	68	9	1996-07-12	3	16	Pavlova	17.45

15.17 17

Daftar semua produk antara $10 dan $20

SELECT DISTINCT P.*
  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

15.18 18

Daftar semua produk tidak antara $10 dan $100 diurutkan berdasarkan harga!

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

Displaying records 1 - 10
ProductID	ProductName	SupplierID	CategoryID	Unit	Price
33	Geitost	15	4	500 g	2.50
24	GuaranÃ¡ FantÃ¡stica	10	1	12 - 355 ml cans	4.50
13	Konbu	6	8	2 kg box	6.00
52	Filo Mix	24	5	16 - 2 kg boxes	7.00
54	TourtiÃ¨re	25	6	16 pies	7.45
75	RhÃ¶nbrÃ¤u Klosterbier	12	1	24 - 0.5 l bottles	7.75
23	TunnbrÃ¶d	9	5	12 - 250 g pkgs.	9.00
19	Teatime Chocolate Biscuits	8	3	10 boxes x 12 pieces	9.20
45	RÃ¸gede sild	21	8	1k pkg.	9.50
47	Zaanse koeken	22	3	10 - 4 oz boxes	9.50

15.19 19

Dapatkan daftar pesanan dan jumlah yang terjual antara 01 Jan 1996 dan 31 Des 1996!

SELECT O.OrderID, O.OrderDate, sum(OD.Quantity) AS 'AmountSold'
  FROM orders O
    JOIN orderdetails OD
      ON O.OrderID = OD.OrderID
    GROUP BY O.OrderID
    HAVING O.OrderDate
    BETWEEN '1996-01-01' AND '1996-12-31'
  ORDER BY O.OrderDate;

Displaying records 1 - 10
OrderID	OrderDate	AmountSold
10248	1996-07-04	81
10249	1996-07-05	147
10250	1996-07-08	180
10251	1996-07-08	123
10252	1996-07-09	315
10253	1996-07-10	306
10254	1996-07-11	171
10255	1996-07-12	330
10256	1996-07-15	81
10257	1996-07-16	138

15.20 20

Daftar semua pemasok dari AS, Inggris, ATAU Jepang!

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

Displaying records 1 - 10
SupplierID	SupplierName	ContactName	Address	City	PostalCode	Country	Phone
1	Exotic Liquid	Charlotte Cooper	49 Gilbert St.	Londona	EC1 4SD	UK	(171) 555-2222
2	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA	(100) 555-4822
3	Grandma Kelly’s Homestead	Regina Murphy	707 Oxford Rd.	Ann Arbor	48104	USA	(313) 555-5735
4	Tokyo Traders	Yoshi Nagase	9-8 Sekimai Musashino-shi	Tokyo	100	Japan	(03) 3555-5011
6	Mayumi’s	Mayumi Ohno	92 Setsuko Chuo-ku	Osaka	545	Japan	(06) 431-7877
8	Specialty Biscuits, Ltd.	Peter Wilson	29 King’s Way	Manchester	M14 GSD	UK	(161) 555-4448
16	Bigfoot Breweries	Cheryl Saylor	3400 - 8th Avenue Suite 210	Bend	97101	USA	(503) 555-9931
19	New England Seafood Cannery	Robb Merchant	Order Processing Dept. 2100 Paul Revere Blvd.	Boston	2134	USA	(617) 555-3267
1	Exotic Liquid	Charlotte Cooper	49 Gilbert St.	Londona	EC1 4SD	UK	(171) 555-2222
2	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA	(100) 555-4822

15.21 21

Daftar semua produk yang tidak persis $10, $20, $30, $40, atau $50!

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

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
4	Chef Anton’s Cajun Seasoning	2	2	48 - 6 oz jars	22.00
5	Chef Anton’s Gumbo Mix	2	2	36 boxes	21.35
6	Grandma’s Boysenberry Spread	3	2	12 - 8 oz jars	25.00
9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97.00
10	Ikura	4	8	12 - 200 ml jars	31.00
11	Queso Cabrales	5	4	1 kg pkg.	21.00
12	Queso Manchego La Pastora	5	4	10 - 500 g pkgs.	38.00
13	Konbu	6	8	2 kg box	6.00

15.22 22

Daftar semua pelanggan yang berasal dari negara yang sama dengan pemasok!

SELECT C.CustomerName, S.SupplierName, C.Country AS 'CustomerCountry', S.Country AS 'SupplierCountry'
  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 
      CASE
         WHEN C.Country=S.Country THEN '1'
         ELSE '0'
        END = '1';

Displaying records 1 - 10
CustomerName	SupplierName	CustomerCountry	SupplierCountry
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	Grandma Kelly’s Homestead	USA	USA

15.23 23

Daftar semua produk yang dimulai dengan ‘Cha’ atau ‘Chan’ dan memiliki satu karakter lagi!

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

3 records
ProductName
Chang
Chang
Chang

15.24 24

Daftar semua pemasok yang memiliki nomor faks!

Karena tidak ada kolom nomor faks dalam database, kami berasumsi bahwa semua pemasok tidak memiliki nomor faks.

15.25 25

Daftar semua pelanggan dengan pesanan rata-rata antara $1000 dan $1200!

SELECT CustomerName, AVG(TotalOrderAmount) AS AverageOrder
  FROM 
      ( 
       SELECT CustomerName, SUM(OD.Quantity*P.Price) AS TotalOrderAmount
        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
        ) S
  GROUP BY S.CustomerName
    HAVING AverageOrder
      BETWEEN 1000 AND 1200
  ORDER BY AverageOrder ASC;

0 records
CustomerName	AverageOrder

15.26 26

Daftar total pelanggan di setiap negara.

SELECT C.Country, COUNT(C.Country) AS 'TotalCustomer'
  FROM customers C
    GROUP BY C.Country WITH ROLLUP

Displaying records 1 - 10
Country	TotalCustomer
Argentina	9
Austria	6
Belgium	6
Brazil	27
Canada	9
Denmark	6
Finland	6
France	33
Germany	33
Ireland	3

DATA BASE

WEEK 6

Muhammad Naufal Ardiansyah (20204920017)

March 10, 2022