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

1 Introduction

Seperti yang tertera pada judul, kali ini kita akan menganalisis data menggunakan query yang mana nantinya kita akan membuat satu tabel simple query. Kegunaan query yaitu untuk menarik informasi dari tabel. Sebelum memulai, kita hubungkan database terlebih dahulu dengan R console berikut ini:

# set up the connection and save it into the workspace
#~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
library(RMySQL)
## Warning: package 'RMySQL' was built under R version 4.1.3
## Loading required package: DBI
## Warning: package 'DBI' was built under R version 4.1.3
library(DBI)
sausan <- dbConnect(RMySQL::MySQL(),
                   dbname='factory_db',
                   username='root',
                   password='',
                   host='localhost',
                   port=3306)
knitr::opts_chunk$set(connection = "sausan")  #set up the connection

2 SELECT

SELECT merupakan perintah dasar dari SQL yang berfungsi untuk memilih data dari database dan kemudian menampilkannya dalam hasil.

Di bawah ini ialah syntax saat kita menggunakan Select yang mana hanya memilih beberapa tabel saja :

SELECT CustomerName, Address, City, Country
  FROM CUSTOMERS C;
Displaying records 1 - 10
CustomerName Address City Country
Alfreds Futterkiste Obere Str. 57 Turki 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

Di bawah ini ialah syntax saat menggunakan Select yang mana kita mengambil semua tabel customers (menggunakan *) :

SELECT *
  FROM CUSTOMERS C;
Displaying records 1 - 10
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Ramadhani Obere Str. 57 Turki 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

DISTINCT berfungsi untuk menampilkan nilai yang berbeda. Biasanya dalam tabel terdapat kolom yang memiliki nilai duplikat, kita bisa menggunakan Distinct untuk menghilangkan nilai duplikat tersebut. Cara menggunakan Distinct yaitu bersamaan dengan Select seperti syntax di bawah ini :

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

4 WHERE

WHERE berfungsi untuk menyaring atau memfilter record. Dengan kata lain, Where juga dipakai dalam menentukan kondisi ketika bergabung dengan beberapa tabel atau mengambil data dari satu tabel. Selain bersamaan dengan SELECT, Where juga bisa digunakan bersamaan dengan UPDATE, DELETE, dan perintah dasar SQL lainnya. Berikut ini syntax untuk memilih semua Customer dari negara Mexico :

SELECT *
  FROM Customers C
    WHERE C.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

Untuk menggunakan WHERE, kita harus memahami operator-operator yang digunakan dalam WHERE. Operator tersebut yaitu :
1. = (sama dengan)
2. > (lebih besar dari)
3. < (lebih kecil dari)
4. >= (lebih besar sama dengan)
5. <= (lebih kecil sama dengan)
6. <> atau != (tidak sama)
7. IS NULL atau IS NOT NUL (field tanpa nilai)
8. BETWEEN (antara range tertentu)
9. LIKE (mencari pola)
10. IN (menentukan beberapa kemungkinan nilai untuk kolom)

5 BETWEEN

BETWEEN berfungsi untuk memilih nilai dalam range tertentu. nilai tersebut bisa berupa angka, teks, ataupun tanggal. Between bisa digunakan bersamaan dengan SELECT, INSERT, UPDATE, atau DELETE.

Di bawah ini merupakan syntax Between menggunakan range angka :

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

Di bawah ini merupakan syntax Between menggunakan range tanggal :

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

6 IN

IN berfungsi untuk menentukan beberapa kemungkinan nilai untuk kolom. Kita bisa gunakan IN bersamaan dengan WHERE jika ingin menggunakan lebih dari 2 kondisi. Berikut ini syntax dengan memilih semua Customer yang berlokasi di “Germany”, “France”, atau “UK” :

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

Berikut ini syntax dengan memilih semua Customer yang berasal dari negara yang sama dengan Suppliernya :

SELECT *
  FROM Customers C
    WHERE C.Country
      IN (SELECT S.Country FROM Suppliers S);
Displaying records 1 - 10
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Ramadhani Obere Str. 57 Turki 12209 Germany
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
7 Blondel père et fils Frédérique Citeaux 24, place Kléber Strasbourg 67000 France
8 Bólido Comidas preparadas Martín Sommer C/ Araquil, 67 Madrid 28023 Spain
9 Bon app’ Laurence Lebihans 12, rue des Bouchers Marseille 13008 France
10 Bottom-Dollar Marketse Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen T2F 8M4 Canada
11 B’s Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK
15 Comércio Mineiro Pedro Afonso Av. dos Lusíadas, 23 São Paulo 05432-043 Brazil

7 LIKE

LIKE berfungsi untuk mencari pola yang spesifik pada kolom. Like digunakan bersamaan dengan WHERE. Biasanya pada like, kita juga menggunakan % (mewakili multi character) atau _ (mewakili single character). Di bawah ini syntax dengan memilih semua Customer yang CustomerName-nya dimulai dengan huruf “a” :

SELECT *
  FROM Customers C
    WHERE C.CustomerName
      LIKE 'a%';
8 records
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Ramadhani Obere Str. 57 Turki 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 Ramadhani Obere Str. 57 Turki 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

Penerapan % dan _ sebagai berikut :
1. WHERE CustomerName LIKE ‘a%’ (mencari nilai berawalan “a”)
2. WHERE CustomerName LIKE ‘%a’ (mencari nilai berakhiran “a”)
3. WHERE CustomerName LIKE ‘%or%’ (mencari nilai yang memiliki “or” di suatu posisi)
4. WHERE CustomerName LIKE ‘r%’ (mencari nilai yang memiliki “r” di posisi kedua)
5. WHERE CustomerName LIKE ’a%’ (mencari nilai berawalan “a” dan setidaknya 2 karakter panjangnya)
6. WHERE CustomerName LIKE ’a__%’ (mencari nilai berawalan “a” dan setidaknya 3 karakter panjangnya)
7. WHERE ContactName LIKE ‘a%o’

8 AND, OR and NOT

AND, OR, dan NOT bisa dikombinasikan dengan argumen WHERE.
- opeator AND menampilkan data jika semua kondisi yang dipisahkan AND bernilai TRUE. Jika FALSE maka akan terjadi eror
- operator OR menampilkan data jika salah satu kondisi yang dipisahkan oleh OR bernilai TRUE.
- operator NOT menampilkan hasil yang bernilai FALSE.

Syntax di bawah ini memilih semua field dari “Customers” yang negaranya adalah “Germany” dan kotanya “Berlin atau”München” :

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

syntax NOT bisa diwakilkan dengan <> seperti dibawah ini:

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

9 ORDER BY

ORDER BY berfungsi untuk mengurutkan hasil set data dalam urutan besar ke kecil (z-a) atau sebaliknya mengurutkan data dari kecil ke besar (a-z) berdasarkan satu atau lebih kolom.

ORDER BY berisikan DESC atau descending (mengurutkan data dari besar ke kecil) dan ASC atau ascending (mengurutkan data dari kecil ke besar). Jika tidak memakai keduanya, maka secara otomatis akan menggunakan ASC.

Berikut ini syntaxnya :

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

10 LIMIT

LIMIT berfungsi untuk menentukan jumlah record yang akan ditampilkan. Selain bersamaan dengan WHERE dan ORDER BY, LIMIT juga bisa dipadukan dengan OFFSET.

top 3 rows:

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

top 5 rows setelah top 3 rows :

SELECT CustomerName, Address, City, Country
  FROM customers C
  ORDER BY C.City, C.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
Old World Delicatessen 2743 Bering St. Anchorage USA
Vaffeljernet Smagsløget 45 Århus Denmark
Vaffeljernet Smagsløget 45 Århus Denmark

11 MIN dan MAX

MIN dan MAX berfungsi untuk menentukan nilai terkecil dan terbesar dalam bidang berdasakan pengelompokan yang ditentukan.

syntax min untuk menentukan harga produk termurah:

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

syntax max untuk menentukan harga produk terbesar:

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

12 COUNT, SUM dan AVG

COUNT berfungsi untuk menentukan jumlah baris yang cocok dengan kriteria tertentu. SUM berfungsi untuk menjumlah total kolom numerik. AVG berfungsi untuk menghitung nilai rata-rata kolom numerik.

syntax count :

SELECT COUNT(P.Price)
  FROM Products P;
1 records
COUNT(P.Price)
154

syntax sum :

SELECT SUM(P.Price)
  FROM Products P;
1 records
SUM(P.Price)
4445.42

syntax avg :

SELECT AVG(P.Price)
  FROM Products P;
1 records
AVG(P.Price)
28.86636

13 HAVING

HAVING digunakan sebagai pengganti WHERE dengan fungsi agregat (COUNT, MAX, MIN, SUM, AVG).

syntax having dengan menggunakan fungsi COUNT:

SELECT COUNT(C.CustomerID), C.Country
  FROM Customers C
    GROUP BY C.Country
      HAVING COUNT(C.CustomerID) > 5
        ORDER BY COUNT(C.CustomerID) DESC;
Displaying records 1 - 10
COUNT(C.CustomerID) Country
26 USA
22 Germany
22 France
18 Brazil
14 UK
10 Mexico
10 Spain
8 Venezuela
6 Canada
6 Argentina

14 CASE

CASE berfungsi untuk melewati kondisi dan mengembalikan nilai saat kondisi pertama terpenuhi.

syntax case bersamaan ELSE:

SELECT OrderID, Quantity,
CASE
  WHEN OD.Quantity > 30 THEN 'The quantity is greater than 30'
  WHEN OD.Quantity = 30 THEN 'The quantity is 30'
  ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails OD;
Displaying records 1 - 10
OrderID Quantity QuantityText
10248 12 The quantity is under 30
10248 10 The quantity is under 30
10248 5 The quantity is under 30
10249 9 The quantity is under 30
10249 40 The quantity is greater than 30
10250 10 The quantity is under 30
10250 35 The quantity is greater than 30
10250 15 The quantity is under 30
10251 6 The quantity is under 30
10251 15 The quantity is under 30 
SELECT CustomerName, City, Country
FROM Customers C
ORDER BY
(CASE
    WHEN C.City IS NULL THEN C.Country
    ELSE C.City
END);
Displaying records 1 - 10
CustomerName City Country
Drachenblut Delikatessend Aachen Germany
Drachenblut Delikatessend Aachen Germany
Rattlesnake Canyon Grocery Albuquerque USA
Rattlesnake Canyon Grocery Albuquerque USA
Old World Delicatessen Anchorage USA
Old World Delicatessen Anchorage USA
Vaffeljernet Ã…rhus Denmark
Vaffeljernet Ã…rhus Denmark
Galería del gastrónomo Barcelona Spain
Galería del gastrónomo Barcelona Spain

15 Exercise

15.1 1. some Suppliers in alphabetical order

SELECT SupplierID, SupplierName, City, Country 
  FROM Suppliers S 
    ORDER BY S.SupplierName ASC;
Displaying records 1 - 10
SupplierID SupplierName City Country
18 Aux joyeux ecclésiastiques Paris France
18 Aux joyeux ecclésiastiques Paris France
16 Bigfoot Breweries Bend USA
16 Bigfoot Breweries Bend USA
5 Cooperativa de Quesos ‘Las Cabras’ Oviedo Spain
5 Cooperativa de Quesos ‘Las Cabras’ Oviedo Spain
27 Escargots Nouveaux Montceau France
27 Escargots Nouveaux Montceau France
1 Exotic Liquid Londona UK
1 Exotic Liquid Londona UK

15.2 2. some Suppliers in reverse alphabetical order

SELECT SupplierID, SupplierName, City, Country
FROM Suppliers S
ORDER BY S.SupplierName DESC;
Displaying records 1 - 10
SupplierID SupplierName City Country
22 Zaanse Snoepfabriek Zaandam Netherlands
22 Zaanse Snoepfabriek Zaandam Netherlands
4 Tokyo Traders Tokyo Japan
4 Tokyo Traders Tokyo Japan
17 Svensk Sjöföda AB Stockholm Sweden
17 Svensk Sjöföda AB Stockholm Sweden
8 Specialty Biscuits, Ltd. Manchester UK
8 Specialty Biscuits, Ltd. Manchester UK
10 Refrescos Americanas LTDA São Paulo Brazil
10 Refrescos Americanas LTDA São Paulo Brazil

15.3 3. some Supplier ordered by country then by city

SELECT SupplierID, SupplierName, City, Country
FROM Suppliers S
ORDER BY S.Country, S.City ASC;
Displaying records 1 - 10
SupplierID SupplierName City Country
7 Pavlova, Ltd. Melbourne Australia
7 Pavlova, Ltd. Melbourne Australia
24 G’day, Mate Sydney Australia
24 G’day, Mate Sydney Australia
10 Refrescos Americanas LTDA São Paulo Brazil
10 Refrescos Americanas LTDA São Paulo Brazil
25 Ma Maison Montréal Canada
25 Ma Maison Montréal Canada
29 Forêts d’érables Ste-Hyacinthe Canada
29 Forêts d’érables Ste-Hyacinthe Canada

15.4 4. all Supplier reverse ordered by country the by city

SELECT *
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
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
3 Grandma Kelly’s Homestead Regina Murphy 707 Oxford Rd. Ann Arbor 48104 USA (313) 555-5735
3 Grandma Kelly’s Homestead Regina Murphy 707 Oxford Rd. Ann Arbor 48104 USA (313) 555-5735
8 Specialty Biscuits, Ltd. Peter Wilson 29 King’s Way Manchester M14 GSD UK (161) 555-4448
8 Specialty Biscuits, Ltd. Peter Wilson 29 King’s Way Manchester M14 GSD UK (161) 555-4448

15.5 5. all Order sorted by largest total amount

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
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
10373 37 4 1996-12-05 3 80
10442 20 3 1997-02-11 2 80
10359 72 5 1996-11-21 3 80
10345 63 2 1996-11-04 2 80

15.6 6. Top 10 expensive product

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
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
20 Sir Rodney’s Marmalade 8 3 30 gift boxes 81.00
20 Sir Rodney’s Marmalade 8 3 30 gift boxes 81.00
18 Carnarvon Tigers 7 8 16 kg pkg. 62.50
18 Carnarvon Tigers 7 8 16 kg pkg. 62.50

15.7 7. the 10th to 15th expensive product

SELECT P.*
FROM products P
ORDER BY P.Price DESC
LIMIT 9, 15;
Displaying records 1 - 10
ProductID ProductName SupplierID CategoryID Unit Price
18 Carnarvon Tigers 7 8 16 kg pkg. 62.5
59 Raclette Courdavault 28 4 5 kg pkg. 55.0
59 Raclette Courdavault 28 4 5 kg pkg. 55.0
51 Manjimup Dried Apples 24 7 50 - 300 g pkgs. 53.0
51 Manjimup Dried Apples 24 7 50 - 300 g pkgs. 53.0
62 Tarte au sucre 29 3 48 pies 49.3
62 Tarte au sucre 29 3 48 pies 49.3
43 Ipoh Coffee 20 1 16 - 500 g tins 46.0
43 Ipoh Coffee 20 1 16 - 500 g tins 46.0
28 Rössle Sauerkraut 12 7 25 - 825 g cans 45.6

15.8 8. list country in supplier table (alphabetical order)

SELECT DISTINCT S.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. Cheapest and Expensive product

SELECT min(P.Price) Cheapestprice
FROM products P
1 records
Cheapestprice
2.5
2 records
ProductID ProductName SupplierID CategoryID Unit Price
33 Geitost 15 4 500 g 2.5
33 Geitost 15 4 500 g 2.5 
SELECT max(P.Price) Expensiveprice
FROM products P
1 records
Expensiveprice
263.5
2 records
ProductID ProductName SupplierID CategoryID Unit Price
38 Côte de Blaye 18 1 12 - 75 cl bottles 263.5
38 Côte de Blaye 18 1 12 - 75 cl bottles 263.5

15.10 10. total Supplier from USA

SELECT COUNT(S.Country) TotalSupplierUSA
FROM suppliers S
WHERE S.Country = "USA"
1 records
TotalSupplierUSA
8

15.11 11. total quantity of orderiterm

SELECT SUM(OD.Quantity) TotalQuantity
FROM orderdetails OD
1 records
TotalQuantity
25486

15.12 12. average unitprice in product

SELECT AVG(P.Price) AveragePrice
FROM products P
1 records
AveragePrice
28.86636

15.13 13. customer named Thomas Hardy

SELECT *
FROM customers C
WHERE C.ContactName = 'Thomas Hardy';
2 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

15.14 14. list customer from spain or france

SELECT *
FROM customers C
WHERE C.Country = 'Spain' OR C.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. list customer not from USA

SELECT *
FROM customers C
WHERE C.Country <> 'USA'
Displaying records 1 - 10
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Ramadhani Obere Str. 57 Turki 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. list order not between $50 and $15000

SELECT P.ProductID, P.ProductName, P.Price, O.OrderID, OD.OrderID, OD.Quantity
FROM ((products P
LEFT JOIN orderdetails OD
ON P.ProductID = OD.ProductID)
LEFT JOIN orders O
ON OD.OrderID = O.OrderID)
WHERE P.Price 
NOT BETWEEN 50 AND 15000
Displaying records 1 - 10
ProductID ProductName Price OrderID OrderID Quantity
11 Queso Cabrales 21.0 10248 10248 12
11 Queso Cabrales 21.0 10248 10248 12
42 Singaporean Hokkien Fried Mee 14.0 10248 10248 10
42 Singaporean Hokkien Fried Mee 14.0 10248 10248 10
72 Mozzarella di Giovanni 34.8 10248 10248 5
72 Mozzarella di Giovanni 34.8 10248 10248 5
11 Queso Cabrales 21.0 10248 10248 12
11 Queso Cabrales 21.0 10248 10248 12
42 Singaporean Hokkien Fried Mee 14.0 10248 10248 10
42 Singaporean Hokkien Fried Mee 14.0 10248 10248 10

15.17 17. list product between $10 and $20

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

15.18 18. list product not between $10 and $100 (sorted)

SELECT *
FROM products P
WHERE P.Price
NOT BETWEEN 10 AND 100
ORDER BY P.Price DESC;
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
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
41 Jack’s New England Clam Chowder 19 8 12 - 12 oz cans 9.65
41 Jack’s New England Clam Chowder 19 8 12 - 12 oz cans 9.65
45 Røgede sild 21 8 1k pkg. 9.50
47 Zaanse koeken 22 3 10 - 4 oz boxes 9.50
45 Røgede sild 21 8 1k pkg. 9.50
47 Zaanse koeken 22 3 10 - 4 oz boxes 9.50

15.19 19. list order and amount sold between 1996 Jan 01 and 1996 Des 31

SELECT C.CustomerID, C.CustomerName, O.OrderID, O.CustomerID, O.OrderDate, OD.OrderID, SUM(OD.Quantity) AmountSold
FROM customers C
LEFT JOIN orders O
ON C.CustomerID = O.CustomerID
LEFT JOIN orderdetails OD
ON O.OrderID = OD.OrderID
GROUP BY O.OrderID
HAVING O.OrderDate
BETWEEN '1996-01-01' AND '1996-12-31'
Displaying records 1 - 10
CustomerID CustomerName OrderID CustomerID OrderDate OrderID AmountSold
90 Wilman Kala 10248 90 1996-07-04 10248 108
81 Tradição Hipermercados 10249 81 1996-07-05 10249 196
34 Hanari Carnes 10250 34 1996-07-08 10250 240
84 Victuailles en stock 10251 84 1996-07-08 10251 164
76 Suprêmes délices 10252 76 1996-07-09 10252 420
34 Hanari Carnes 10253 34 1996-07-10 10253 408
14 Chop-suey Chinese 10254 14 1996-07-11 10254 228
68 Richter Supermarkt 10255 68 1996-07-12 10255 440
88 Wellington Importadora 10256 88 1996-07-15 10256 108
35 HILARIÓN-Abastos 10257 35 1996-07-16 10257 184

15.20 20. list supplier from USA, UK or Japan

SELECT *
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. list product not exactly $10, $20, $30, $40, $50

SELECT * 
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. list customer which is from the same country as supplier

SELECT C.CustomerName, C.Country, S.SupplierName, S.Country
FROM customers C
JOIN suppliers S
ON C.Country = S.Country
Displaying records 1 - 10
CustomerName Country SupplierName Country
Alfreds Futterkiste Germany Heli Süßwaren GmbH & Co. KG Germany
Alfreds Futterkiste Germany Plutzer Lebensmittelgroßmärkte AG Germany
Alfreds Futterkiste Germany Nord-Ost-Fisch Handelsgesellschaft mbH Germany
Alfreds Futterkiste Germany Heli Süßwaren GmbH & Co. KG Germany
Alfreds Futterkiste Germany Plutzer Lebensmittelgroßmärkte AG Germany
Alfreds Futterkiste Germany Nord-Ost-Fisch Handelsgesellschaft mbH Germany
Around the Horn UK Exotic Liquid UK
Around the Horn UK Specialty Biscuits, Ltd. UK
Around the Horn UK Exotic Liquid UK
Around the Horn UK Specialty Biscuits, Ltd. UK

15.23 23. list product start with ‘Cha’ or ‘Chan’ and have one more character

SELECT * 
FROM products P
WHERE P.ProductName LIKE 'Cha_' OR P.ProductName LIKE 'Chan_';
2 records
ProductID ProductName SupplierID CategoryID Unit Price
2 Chang 1 1 24 - 12 oz bottles 19
2 Chang 1 1 24 - 12 oz bottles 19

15.24 24. list supplier have fax number

SELECT *
FROM suppliers S
WHERE S.Phone IS NOT NULL
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
5 Cooperativa de Quesos ‘Las Cabras’ Antonio del Valle Saavedra Calle del Rosal 4 Oviedo 33007 Spain (98) 598 76 54
6 Mayumi’s Mayumi Ohno 92 Setsuko Chuo-ku Osaka 545 Japan (06) 431-7877
7 Pavlova, Ltd. Ian Devling 74 Rose St. Moonie Ponds Melbourne 3058 Australia (03) 444-2343
8 Specialty Biscuits, Ltd. Peter Wilson 29 King’s Way Manchester M14 GSD UK (161) 555-4448
9 PB Knäckebröd AB Lars Peterson Kaloadagatan 13 Göteborg S-345 67 Sweden 031-987 65 43
10 Refrescos Americanas LTDA Carlos Diaz Av. das Americanas 12.890 São Paulo 5442 Brazil (11) 555 4640

15.25 25. list customer with average order between $1000 and $1200

SELECT CustomerName, AVG(OrderAmount) AvgOrder
FROM 
(
SELECT C.CustomerName, SUM(OD.Quantity * P.Price) OrderAmount
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
) COPAVG
GROUP BY COPAVG.CustomerName
HAVING AvgOrder
BETWEEN 1000 AND 1200
ORDER BY AvgOrder
1 records
CustomerName AvgOrder
Centro comercial Moctezuma 1008

15.26 26. list total customer in each country

SELECT C.Country, COUNT(C.Country) TotalCustomer
FROM customers C
GROUP BY C.Country
Displaying records 1 - 10
Country TotalCustomer
Argentina 6
Austria 4
Belgium 4
Brazil 18
Canada 6
Denmark 4
Finland 4
France 22
Germany 22
Ireland 2