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

1 Pengenalan

Kekuatan sebenarnya dari database relasional terletak pada kemampuannya untuk mengambil dan menganalisis data Anda dengan cepat dengan menjalankan kueri. Di bagian ini, kita akan mempelajari cara membuat kueri satu tabel sederhana.
Pertama, kita perlu mengkoneksikan ke database yang kita punya seperti di bawah ini:

library(RMySQL)
## Loading required package: DBI
library(DBI)

Jika belum ada database ‘factory_db’ kita bisa menambahkan nya seperti dibawah ini.

dbExecute(MySQL, "CREATE DATABASE factory_db")
factory_db <- dbConnect(MySQL(),
                        user='root',
                        password='',
                        dbname='factory_db',
                        host='localhost')

dbExecute(factory_db, "CREATE TABLE Persons(
          PersonID int,
          LastName varchar(255),
          FirstName varchar(255),
          Address varchar(255),
          City varchar(255))")

Lalu kita mengkoneksikan ke database ‘karen’

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

2 SELECT

Kekuatan sebenarnya dari SQL SELECT adalah mengambil data dari database terletak pada kemampuannya untuk mengambil dan menganalisis data Anda dengan cepat dengan menjalankan kueri. Di bagian ini, kita akan mempelajari cara membuat kueri satu tabel sederhana.
Jika belum ada data Customers dalam factory_db, kita bisa menambahkan dengan fungsi dbWriteTable seperti di bawah ini:

Customers <- read.csv("C:/Users/HP/OneDrive/Documents/kuliah/R/database system/tugas2/Customers.csv")
dbWriteTable(factory_db, "Customers", Customers, append=T)
Shippers <- read.csv("C:/Users/HP/OneDrive/Documents/kuliah/R/database system/tugas2/Shippers.csv")
dbWriteTable(factory_db, "Shippers", Shippers, appned=T)
Orders <- read.csv("C:/Users/HP/OneDrive/Documents/kuliah/R/database system/tugas2/Orders.csv")
dbWriteTable(factory_db, "Orders", Orders, append=T)

Setelah itu, kita akan SELECT CustomerName, Address, City dsn Country pada data CUSTOMERS.

SELECT CustomerName, Address, City, Country
  FROM CUSTOMERS;
Displaying records 1 - 10
CustomerName Address City Country
Alfreds Futterkiste Obere Str. 57 Berlin 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? S-958 22 NA
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

3 Dictinct

SQL DISTINCT digunakan bersamaan dengan SELECT untuk menghilangkan semua data atau catatan yang double/duplikat dan hanya mengambil catatan yang unik. Sintaks dasar kata kunci DISTINCT untuk menghilangkan catatan duplikat sebagai berikut:

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

4 WHERE

WHERE selain bisa gunakan dalam SELECT bisa digunakan dalam UPDATE, DELETE dan lain-lain dan kegunaan nya adalah untuk memfilter RECORD atau mengekstrak hanya record yang memenuhi kondisi tertentu. Berikut sintaks yang bisa kita lakukan seperti di bawah ini:

SELECT *
  FROM Customers
    WHERE Country='Mexico';
5 records
row_names CustomerID CustomerName ContactName Address City PostalCode Country
2 2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constituci?n 2222 M?xico D.F. 5021 Mexico
3 3 Antonio Moreno Taquer?a Antonio Moreno Mataderos 2312 M?xico D.F. 5023 Mexico
13 13 Centro comercial Moctezuma Francisco Chang Sierras de Granada 9993 M?xico D.F. 5022 Mexico
58 58 Pericles Comidas cl?sicas Guillermo Fern?ndez Calle Dr. Jorge Cash 321 M?xico D.F. 5033 Mexico
80 80 Tortuga Restaurante Miguel Angel Paolino Avda. Azteca 123 M?xico D.F. 5033 Mexico

Ada banyak operator yang bisa kita gunakan dalam WHERE. Berikut operatos nya di bawah ini:

df <- data.frame(Operator=rep(c('=', '>', '<', '>=', '<=', '<>', 'IS NULL or IS NOT NUL', 'BETWEEN', 'LIKE', 'IN')),
                 Deskripsi=rep(c('setara', 'lebih besar dari', 'kurang dari', 'lebih besar sama dengan', 'kurang dari sama dengan', 'tidak sama', 'bidang tanpa nilai', 'antara rentang tertentu', 'mencari pola', 'menentukan beberapa kemungkinan nilai untuk kolom')))

head(df)
##   Operator               Deskripsi
## 1        =                  setara
## 2        >        lebih besar dari
## 3        <             kurang dari
## 4       >= lebih besar sama dengan
## 5       <= kurang dari sama dengan
## 6       <>              tidak sama

5 BETWEEN

Operator BETWEEN memilih nilai dalam rentang tertentu. Nilai dalam BETWEEN berarti dapat berupa angka, teks, atau tanggal. Berikut contoh dalam memilih semua produk dengan harga BETWEEN 10 dan 20.
Jika belum ada Product, kita bisa menambahkan ke dalam factory_db sebagai berikut:

Products <- read.csv("C:/Users/HP/OneDrive/Documents/kuliah/R/database system/tugas2/Products.csv")
dbWriteTable(factory_db, "Products", Products, append=T)

Setelah itu kita jalankan contohnya.

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

Contoh berikutnya yaitu, kita memilih semua pesanan dengan OrderDate BETWEEN ‘01 Juli 1996’ dan ‘31 Juli 1996’ sebagai berikut:

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

6 IN

Operator IN untuk menentukan beberapa nilai dalam klausa WHERE. Berikut contoh untuk memilih semua pelanggan yang berolaksi di “Jerman”, “Perancis”, atau “Inggris Raya” sebagai berikut:

SELECT *
  FROM Customers
    WHERE Country
      IN ('Germany', 'France', 'UK');
Displaying records 1 - 10
row_names CustomerID CustomerName ContactName Address City PostalCode Country
1 1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
4 4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
6 6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
7 7 Blondel p?re et fils Fr?d?rique Citeaux 24, place Kl?ber Strasbourg 67000 France
9 9 Bon app’ Laurence Lebihans 12, rue des Bouchers Marseille 13008 France
11 11 B’s Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK
16 16 Consolidated Holdings Elizabeth Brown Berkeley Gardens 12 Brewery London WX1 6LT UK
17 17 Drachenblut Delikatessend Sven Ottlieb Walserweg 21 Aachen 52066 Germany
18 18 Du monde entier Janine Labrune 67, rue des Cinquante Otages Nantes 44000 France
19 19 Eastern Connection Ann Devon 35 King George London WX3 6FW UK

Contoh berikutnya yaitu memilih semua pelanggan yang berasal dari negara yang sama dengan pemasok sebagai berikut:

Suppliers <- read.csv("C:/Users/HP/OneDrive/Documents/kuliah/R/database system/tugas2/Suppliers.csv")
dbWriteTable(factory_db, "Suppliers", Suppliers, append=T)
SELECT *
  FROM Customers
    WHERE Country
      IN(SELECT Country FROM Suppliers);
Displaying records 1 - 10
row_names CustomerID CustomerName ContactName Address City PostalCode Country
4 4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
11 11 B’s Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK
16 16 Consolidated Holdings Elizabeth Brown Berkeley Gardens 12 Brewery London WX1 6LT UK
19 19 Eastern Connection Ann Devon 35 King George London WX3 6FW UK
38 38 Island Trading Helen Bennett Garden House Crowther Way Cowes PO31 7PJ UK
53 53 North/South Simon Crowther South House 300 Queensbridge London SW7 1RZ UK
72 72 Seven Seas Imports Hari Kumar 90 Wadhurst Rd. London OX15 4NB UK
32 32 Great Lakes Food Market Howard Snyder 2732 Baker Blvd. Eugene 97403 USA
36 36 Hungry Coyote Import Store Yoshi Latimer City Center Plaza 516 Main St. Elgin 97827 USA
43 43 Lazy K Kountry Store John Steel 12 Orchestra Terrace Walla Walla 99362 USA

7 LIKE

Operator LIKE digunakan untuk mencari pola tertentu dalam kolom. Ada dua wildcard yang digunakan:
- % : mewakili nol, satu atau beberapa karakter
- _ : mewakili satu karakter
Berikut contoh untuk memilih semua pelanggan dengan CUSTOMERNAME yang dimulai dengan “a”:

SELECT *
  FROM customers 
    WHERE CustomerName
      LIKE 'a%';
4 records
row_names CustomerID CustomerName ContactName Address City PostalCode Country
1 1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constituci?n 2222 M?xico D.F. 5021 Mexico
3 3 Antonio Moreno Taquer?a Antonio Moreno Mataderos 2312 M?xico D.F. 5023 Mexico
4 4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

8 AND, OR and NOT

  • Operator AND : menampilkan record jika semua kondisi yang dipisahkan oleh AND adalah TRUE.
  • Operator OR : menampilkan record jika salah satu kondisi yang dipisahkan oleh OR adalah TRUE.
  • Operator NOT : menampilkan record jika kondisi NOT TRUE.
    Berikut contoh untuk memilih semua bidang dari “Customers” di mana negara adalah “Jerman” AND kota harus “Berlin” OR “München” sebagai berikut:
SELECT * FROM customers
WHERE Country='Germany' AND (City='Berlin' OR City='Munchen');
1 records
row_names CustomerID CustomerName ContactName Address City PostalCode Country
1 1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

Mari lihat satu contoh lagi, pernyataan SQL berikut memilih semua bidang dari “Customers” di mana negara BUKAN “Jerman” dan BUKAN “AS”:

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='Munchen');
1 records
row_names CustomerID CustomerName ContactName Address City PostalCode Country
1 1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

9 ORDER BY

SQL ORDER digunakan untuk mengurutkan data dalam urutan menaik atau menurun, berdasarkan satu atau beberapa kolom.Tetapi ORDER BY secara default mengurutkan data dalam urutan menaik. Kita dapat menggunakan kata kunci DESC untuk mengurutkan data dalam urutan menurun dan kata kunci ASC untuk mengurutkan dalam urutan menaik.
Berikut contoh untuk mengurutkan hasilnya dalam urutan menaik berdasarkan CITY dan COUNTRY:

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='Munchen')
  ORDER BY Country, City;
1 records
row_names CustomerID CustomerName ContactName Address City PostalCode Country
1 1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

10 LIMIT

Klausa LIMIT digunakan untuk menetapkan batas atas jumlah tupel yang dikembalikan oleh SQL. Kalusa ini tidak didukung oleh semua versi SQL.Klausa LIMIT juga dapat ditentukan menggunakan klausa SQL 2008 OFFSET/FETCH FIRST.Berikut kita ilustrasikan LIMIT untuk mengumpulkan 3 baris TOP:

SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='Munchen')
  ORDER BY Country, City
      LIMIT 3;
1 records
row_names CustomerID CustomerName ContactName Address City PostalCode Country
1 1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

Selanjutnya, berikut ini mengilustrasikan klausa LIMIT untuk mengumpulkan TOP 5 baris setelah TOP 3 baris:

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
Old World Delicatessen 2743 Bering St. Anchorage USA
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

11 MIN and MAX

Fungsi MIN() mengembalikan nilai terkecil dari kolom yang dipilih. Fungsi MAX() mengembalikan nilai terbesar dari kolom yang dipilih. Berikut contoh menemukan harga produk termurah:

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

Berikut contoh selanjutnya yaitu menemukan harga produk paling mahal:

SELECT MAX(Price) AS LargestPrice
  FROM Products;
1 records
LargestPrice
263.5

12 COUNT, SUM, and AVG

  • Fungsi COUNT() : mengembalikan jumlah baris yang cocok dengan kriteria yang ditentukan.
  • Fungsi AVG() mengembalikan nilai rata-rata kolom numerik.
  • Fungsi SUM() mengembalikan jumlah total kolom numerik.
    Berikut vontoh untuk menemukan harga rata-rata semua produk:
SELECT AVG(Price)
  FROM Products;
1 records
AVG(Price)
28.86636

13 HAVING

Klausa HAVING ditambahkan ke SQL karena kata kunci WHERE tidak dapat digunakan dengan fungsi agregat. Berikut kita bisa implementasikan klausa HAVING ke dalam sintaks dengan permasalahan 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;
5 records
COUNT(CustomerID) Country
13 USA
11 Germany
10 France
9 Brazil
7 UK

14 CASE

CASE melewati kondisi dan mengembalikan nilai ketika kondisi pertama terpenuhi,jadi, setelah suatu kondisi benar, itu akan berhenti membaca dan mengembalikan hasilnya. Berikut contohnya yaitu SQL berikut melewati kondisi dan mengembalikan nilai saat kondisi pertama terpenuhi:

OrderDetail <- read.csv("C:/Users/HP/OneDrive/Documents/kuliah/R/database system/tugas2/OrderDetails.csv")
dbWriteTable(factory_db, "OrderDetail", OrderDetail, append=T)
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 OrderDetail;
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

Contoh berikutnya yaitu mengurutkan pelanggan berdasarkan Kota. Namun, jika Kota 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
Folies gourmandes 59000 NA
Vaffeljernet ?rhus Denmark
Drachenblut Delikatessend Aachen Germany
Rattlesnake Canyon Grocery Albuquerque USA
Old World Delicatessen Anchorage USA
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

15 EXERCISE

15.1 Select Some attributes of suppliers in alphabetical order!

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
16 Bigfoot Breweries 3400 - 8th Avenue Suite 210 Bend USA
5 Cooperativa de Quesos ‘Las Cabras’ Calle del Rosal 4 Oviedo Spain
27 Escargots Nouveaux 22, rue H. Voiron Montceau France
1 Exotic Liquid 49 Gilbert St. Londona UK
29 For?ts d’?rables 148 rue Chasseur Ste-Hyacinthe Canada
14 Formaggi Fortini s.r.l. Viale Dante, 75 Ravenna Italy
24 G’day, Mate 170 Prince Edward Parade Hunter’s Hill Sydney Australia
28 Gai p?turage Bat. B 3, rue des Alpes Annecy France
3 Grandma Kelly’s Homestead 707 Oxford Rd. Ann Arbor USA

15.2 Some attributes of suppliers in reverse alphabetical order!

SELECT SupplierID, SupplierName, Address, City, Country
  FROM Suppliers 
    ORDER BY Suppliername DESC;
Displaying records 1 - 10
SupplierID SupplierName Address City Country
22 Zaanse Snoepfabriek Verkoop Rijnweg 22 Zaandam Netherlands
4 Tokyo Traders 9-8 Sekimai Musashino-shi Tokyo Japan
17 Svensk Sj?f?da AB Brovallav?gen 231 Stockholm Sweden
8 Specialty Biscuits, Ltd. 29 King’s Way Manchester UK
10 Refrescos Americanas LTDA Av. das Americanas 12.890 S?o Paulo Brazil
12 Plutzer Lebensmittelgro?m?rkte AG Bogenallee 51 Frankfurt Germany
9 PB Kn?ckebr?d AB Kaloadagatan 13 G?teborg Sweden
7 Pavlova, Ltd. 74 Rose St. Moonie Ponds Melbourne Australia
26 Pasta Buttini s.r.l. Via dei Gelsomini, 153 Salerno Italy
15 Norske Meierier Hatlevegen 5 Sandvika Norway

15.3 Some attributes of suppliers ordered by country, then by city!

SELECT SupplierID, SupplierName, Address, City, Country
  FROM Suppliers 
    ORDER BY Country ASC, City ASC;
Displaying records 1 - 10
SupplierID SupplierName Address City Country
7 Pavlova, Ltd. 74 Rose St. Moonie Ponds Melbourne 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
25 Ma Maison 2960 Rue St. Laurent Montr?al Canada
29 For?ts d’?rables 148 rue Chasseur Ste-Hyacinthe Canada
21 Lyngbysild Lyngbysild Fiskebakken 10 Lyngby Denmark
23 Karkki Oy Valtakatu 12 Lappeenranta Finland
28 Gai p?turage Bat. B 3, rue des Alpes Annecy France
27 Escargots Nouveaux 22, rue H. Voiron Montceau France
18 Aux joyeux eccl?siastiques 203, Rue des Francs-Bourgeois Paris France

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

SELECT  *
  FROM Suppliers 
    ORDER BY country DESC, City DESC;
Displaying records 1 - 10
row_names SupplierID SupplierName ContactName Address City PostalCode Country Phone
2 2 New Orleans Cajun Delights Shelley Burke P.O. Box 78934 New Orleans 70117 USA (100) 555-4822
19 19 New England Seafood Cannery Robb Merchant Order Processing Dept. 2100 Paul Revere Blvd. Boston 2134 USA (617) 555-3267
16 16 Bigfoot Breweries Cheryl Saylor 3400 - 8th Avenue Suite 210 Bend 97101 USA (503) 555-9931
3 3 Grandma Kelly’s Homestead Regina Murphy 707 Oxford Rd. Ann Arbor 48104 USA (313) 555-5735
8 8 Specialty Biscuits, Ltd. Peter Wilson 29 King’s Way Manchester M14 GSD UK (161) 555-4448
1 1 Exotic Liquid Charlotte Cooper 49 Gilbert St. Londona EC1 4SD UK (171) 555-2222
17 17 Svensk Sj?f?da AB Michael Bj?rn Brovallav?gen 231 Stockholm S-123 45 Sweden 08-123 45 67
9 9 PB Kn?ckebr?d AB Lars Peterson Kaloadagatan 13 G?teborg S-345 67 Sweden 031-987 65 43
5 5 Cooperativa de Quesos ‘Las Cabras’ Antonio del Valle Saavedra Calle del Rosal 4 Oviedo 33007 Spain (98) 598 76 54
20 20 Leka Trading Chandra Leka 471 Serangoon Loop, Suite #402 Singapore 512 Singapore 555-8787

15.5 All orders, sorted by total amount, the largest first!

SELECT O.*, OD.Quantity
  FROM Orders O
    RIGHT JOIN orderdetail OD
      ON O.OrderID = OD.OrderID
        ORDER BY OD.Quantity DESC;
Displaying records 1 - 10
row_names OrderID CustomerID EmployeeID OrderDate ShipperID Quantity
151 10398 71 2 1996-12-30 3 120
39 10286 63 8 1996-08-21 3 100
193 10440 71 4 1997-02-10 2 90
77 10324 71 9 1996-10-08 1 80
98 10345 63 2 1996-11-04 2 80
112 10359 72 5 1996-11-21 3 80
126 10373 37 4 1996-12-05 3 80
195 10442 20 3 1997-02-11 2 80
104 10351 20 1 1996-11-11 1 77
20 10267 25 4 1996-07-29 1 70

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

SELECT *
  FROM products 
    ORDER BY Price DESC
      LIMIT 10;
Displaying records 1 - 10
row_names ProductID ProductName SupplierID CategoryID Unit Price
38 38 C?te de Blaye 18 1 12 - 75 cl bottles 263.50
29 29 Th?ringer Rostbratwurst 12 6 50 bags x 30 sausgs. 123.79
9 9 Mishi Kobe Niku 4 6 18 - 500 g pkgs. 97.00
20 20 Sir Rodney’s Marmalade 8 3 30 gift boxes 81.00
18 18 Carnarvon Tigers 7 8 16 kg pkg. 62.50
59 59 Raclette Courdavault 28 4 5 kg pkg. 55.00
51 51 Manjimup Dried Apples 24 7 50 - 300 g pkgs. 53.00
62 62 Tarte au sucre 29 3 48 pies 49.30
43 43 Ipoh Coffee 20 1 16 - 500 g tins 46.00
28 28 R?ssle Sauerkraut 12 7 25 - 825 g cans 45.60

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

SELECT *
  FROM products 
    ORDER BY Price DESC
      LIMIT 6 OFFSET 10;
6 records
row_names ProductID ProductName SupplierID CategoryID Unit Price
27 27 Schoggi Schokolade 11 3 100 - 100 g pieces 43.9
63 63 Vegie-spread 7 2 15 - 625 g jars 43.9
8 8 Northwoods Cranberry Sauce 3 2 12 - 12 oz jars 40.0
17 17 Alice Mutton 7 6 20 - 1 kg tins 39.0
12 12 Queso Manchego La Pastora 5 4 10 - 500 g pkgs. 38.0
56 56 Gnocchi di nonna Alice 26 5 24 - 250 g pkgs. 38.0

15.8 List all supplier countries in alphabetical order!

SELECT DISTINCT Country
  FROM suppliers 
    ORDER BY Country ASC;
Displaying records 1 - 10
Country
Australia
Brazil
Canada
Denmark
Finland
France
Germany
Italy
Japan
Netherlands

15.9 Find the cheapest product and Expensive Orders!

15.9.1 The Cheapest Orders

SELECT ProductName, Unit, Price
  FROM products 
      ORDER BY Price ASC
        LIMIT 1;
1 records
ProductName Unit Price
Geitost 500 g 2.5

15.9.2 The Expensive Order

SELECT ProductName, Unit, Price
  FROM products 
      ORDER BY Price DESC
        LIMIT 1;
1 records
ProductName Unit Price
C?te de Blaye 12 - 75 cl bottles 263.5

15.10 Find the number of Supplier USA!

SELECT Country, COUNT(Country) AS 'Total_Supplier'
  FROM suppliers 
    WHERE Country='USA' ;
1 records
Country Total_Supplier
USA 4

15.11 Compute the total Quantity of orderitem!

SELECT SUM(OD.Quantity) AS 'TotalQuantity'
  FROM orderdetail OD
1 records
TotalQuantity
12743

15.12 Compute the average UnitPrice of all product!

SELECT AVG(Price) AS 'Average_UnitPrice'
  FROM products
1 records
Average_UnitPrice
28.86636

15.13 Get all information about customer named Thomas Hardy!

SELECT *
  FROM customers 
    WHERE ContactName='Thomas Hardy';
1 records
row_names CustomerID CustomerName ContactName Address City PostalCode Country
4 4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

15.14 List all customers from Spain or France!

SELECT *
  FROM customers 
    WHERE Country="Spain" OR Country="France";
Displaying records 1 - 10
row_names CustomerID CustomerName ContactName Address City PostalCode Country
7 7 Blondel p?re et fils Fr?d?rique Citeaux 24, place Kl?ber Strasbourg 67000 France
8 8 B?lido Comidas preparadas Mart?n Sommer C/ Araquil, 67 Madrid 28023 Spain
9 9 Bon app’ Laurence Lebihans 12, rue des Bouchers Marseille 13008 France
18 18 Du monde entier Janine Labrune 67, rue des Cinquante Otages Nantes 44000 France
22 22 FISSA Fabrica Inter. Salchichas S.A. Diego Roel C/ Moralzarzal, 86 Madrid 28034 Spain
26 26 France restauration Carine Schmitt 54, rue Royale Nantes 44000 France
29 29 Galer?a del gastr?nomo Eduardo Saavedra Rambla de Catalu?a, 23 Barcelona 8022 Spain
30 30 Godos Cocina T?pica Jos? Pedro Freyre C/ Romero, 33 Sevilla 41101 Spain
40 40 La corne d’abondance Daniel Tonini 67, avenue de l’Europe Versailles 78000 France
41 41 La maison d’Asie Annette Roulet 1 rue Alsace-Lorraine Toulouse 31000 France

15.15 List all customers that are not from the USA!

SELECT *
  FROM customers 
    WHERE NOT Country="USA"
Displaying records 1 - 10
row_names CustomerID CustomerName ContactName Address City PostalCode Country
1 1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constituci?n 2222 M?xico D.F. 5021 Mexico
3 3 Antonio Moreno Taquer?a Antonio Moreno Mataderos 2312 M?xico D.F. 5023 Mexico
4 4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
6 6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
7 7 Blondel p?re et fils Fr?d?rique Citeaux 24, place Kl?ber Strasbourg 67000 France
8 8 B?lido Comidas preparadas Mart?n Sommer C/ Araquil, 67 Madrid 28023 Spain
9 9 Bon app’ Laurence Lebihans 12, rue des Bouchers Marseille 13008 France
10 10 Bottom-Dollar Marketse Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen T2F 8M4 Canada
11 11 B’s Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK

15.16 List all orders that not between $50 and $15000!

SELECT O.*,P.ProductID, P.ProductName, P.Price
  FROM orders O
    LEFT JOIN orderdetail 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
row_names OrderID CustomerID EmployeeID OrderDate ShipperID ProductID ProductName Price
1 10248 90 5 1996-07-04 3 11 Queso Cabrales 21.00
1 10248 90 5 1996-07-04 3 42 Singaporean Hokkien Fried Mee 14.00
1 10248 90 5 1996-07-04 3 72 Mozzarella di Giovanni 34.80
2 10249 81 6 1996-07-05 1 14 Tofu 23.25
3 10250 34 4 1996-07-08 2 41 Jack’s New England Clam Chowder 9.65
3 10250 34 4 1996-07-08 2 65 Louisiana Fiery Hot Pepper Sauce 21.05
4 10251 84 3 1996-07-08 1 22 Gustaf’s Kn?ckebr?d 21.00
4 10251 84 3 1996-07-08 1 57 Ravioli Angelo 19.50
4 10251 84 3 1996-07-08 1 65 Louisiana Fiery Hot Pepper Sauce 21.05
5 10252 76 4 1996-07-09 2 33 Geitost 2.50

15.17 List all products between $10 and $20

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

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

SELECT DISTINCT *
  FROM products 
    WHERE Price 
     NOT BETWEEN 10 AND 100
ORDER BY Price ASC;
Displaying records 1 - 10
row_names ProductID ProductName SupplierID CategoryID Unit Price
33 33 Geitost 15 4 500 g 2.50
24 24 Guaran? Fant?stica 10 1 12 - 355 ml cans 4.50
13 13 Konbu 6 8 2 kg box 6.00
52 52 Filo Mix 24 5 16 - 2 kg boxes 7.00
54 54 Tourti?re 25 6 16 pies 7.45
75 75 Rh?nbr?u Klosterbier 12 1 24 - 0.5 l bottles 7.75
23 23 Tunnbr?d 9 5 12 - 250 g pkgs. 9.00
19 19 Teatime Chocolate Biscuits 8 3 10 boxes x 12 pieces 9.20
45 45 R?gede sild 21 8 1k pkg. 9.50
47 47 Zaanse koeken 22 3 10 - 4 oz boxes 9.50

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

SELECT O.OrderID, O.OrderDate, sum(OD.Quantity) AS 'AmountSold'
  FROM orders O
    JOIN orderdetail 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 27
10249 1996-07-05 49
10250 1996-07-08 60
10251 1996-07-08 41
10252 1996-07-09 105
10253 1996-07-10 102
10254 1996-07-11 57
10255 1996-07-12 110
10256 1996-07-15 27
10257 1996-07-16 46

15.20 List all suppliers from the USA, UK, OR Japan!

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

15.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);
Displaying records 1 - 10
row_names ProductID ProductName SupplierID CategoryID Unit Price
1 1 Chais 1 1 10 boxes x 20 bags 18.00
2 2 Chang 1 1 24 - 12 oz bottles 19.00
4 4 Chef Anton’s Cajun Seasoning 2 2 48 - 6 oz jars 22.00
5 5 Chef Anton’s Gumbo Mix 2 2 36 boxes 21.35
6 6 Grandma’s Boysenberry Spread 3 2 12 - 8 oz jars 25.00
9 9 Mishi Kobe Niku 4 6 18 - 500 g pkgs. 97.00
10 10 Ikura 4 8 12 - 200 ml jars 31.00
11 11 Queso Cabrales 5 4 1 kg pkg. 21.00
12 12 Queso Manchego La Pastora 5 4 10 - 500 g pkgs. 38.00
13 13 Konbu 6 8 2 kg box 6.00

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

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 OrderDetail 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
Old World Delicatessen New England Seafood Cannery USA USA
Rattlesnake Canyon Grocery New Orleans Cajun Delights USA USA
Rattlesnake Canyon Grocery Grandma Kelly’s Homestead USA USA
Morgenstern Gesundkost Plutzer Lebensmittelgro?m?rkte AG Germany Germany
Lehmanns Marktstand Heli S??waren GmbH & Co. KG Germany Germany
B’s Beverages Exotic Liquid UK UK
Blondel p?re et fils Aux joyeux eccl?siastiques France France
Die Wandernde Kuh Plutzer Lebensmittelgro?m?rkte AG Germany Germany
Die Wandernde Kuh Plutzer Lebensmittelgro?m?rkte AG Germany Germany
Rattlesnake Canyon Grocery New England Seafood Cannery USA USA

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

SELECT ProductName
  FROM products 
    WHERE ProductName LIKE 'Cha_' OR ProductName LIKE 'Chan_';
1 records
ProductName
Chang

15.24 List all suppliers that do have a fax number!

Dalam database factory_db, tidak ada kolom nomor fax pada data suppliers hanya ada SupplierID yang disertakan dalam bentuk integer, jadi tidak ada pemasok yang memiliki nomor fax.

15.25 List all customer with average orders between $1000 and $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 orderdetail 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;
6 records
CustomerName AverageOrder
Que Del?cia 1012.275
K?niglich Essen 1039.375
Tortuga Restaurante 1067.350
Folk och f? HB 1078.475
Magazzini Alimentari Riuniti 1080.300
Chop-suey Chinese 1124.500

15.26 List total customers in each country.

SELECT Country, COUNT(Country) AS 'TotalCustomer'
  FROM customers 
    GROUP BY Country WITH ROLLUP
Displaying records 1 - 10
Country TotalCustomer
NA 0
Argentina 3
Austria 2
Belgium 2
Brazil 9
Canada 3
Denmark 2
Finland 2
France 10
Germany 11

15.27 Display results with easy to understand column headers.

done.