:—- |:—- Kontak| \(\downarrow\) Email| Instagram | https://www.instagram.com/cvnopp_/ RPubs | https://rpubs.com/calvinriswandy/ Nama | Calvin Riswandi NIM | 20214920003

1 Introduction

library(RMySQL)
## Loading required package: DBI
library(DBI)
poodle <- dbConnect(RMySQL::MySQL(),
                   dbname='calvinbase',
                   username='root',
                   password='',
                   host='localhost',
                   port=3306)
knitr::opts_chunk$set(connection = "poodle")  #set up the connection

2 SELECT

SELECT dalam SQL digunakan sebagai argumen untuk memilih beberapa kolom yang diingikan.

SELECT CustomerName, Address, City, Country
  FROM CUSTOMERS C;
Displaying records 1 - 10
CustomerName Address City Country
Alfreds Futterkiste Obere Str. 57 Berlin Germany
Ana Trujillo Emparedados y helados Avda. de la Constitucin 2222 Mxico D.F. Mexico
Antonio Moreno Taquera Mataderos 2312 Mxico D.F. Mexico
Around the Horn 120 Hanover Sq. London UK
Berglunds snabbkp Berguvsvgen 8 Lule Sweden
Blauer See Delikatessen Forsterstr. 57 Mannheim Germany
Blondel pre et fils 24, place Klber Strasbourg France
Blido Comidas preparadas C/ Araquil, 67 Madrid Spain
Bon app’ 12, rue des Bouchers Marseille France
Bottom-Dollar Marketse 23 Tsawassen Blvd. Tsawassen Canada

Jika kita ingin memilih semua kolom maka kita gunakan tanda bintang(*).

SELECT *
  FROM CUSTOMERS C;
Displaying records 1 - 10
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitucin 2222 Mxico D.F. 5021 Mexico
3 Antonio Moreno Taquera Antonio Moreno Mataderos 2312 Mxico D.F. 5023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbkp Christina Berglund Berguvsvgen 8 Lule S-958 22 Sweden
6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
7 Blondel pre et fils Frdrique Citeaux 24, place Klber Strasbourg 67000 France
8 Blido Comidas preparadas Martn 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 hampir sama dengan SELECT yaitu argumen yang digunakan untuk memilih values dari suatu kolom, akan tetapi hasil yang ditampilkan akan menghasilkan data yang unique. Misal kolom country ada 7 values yang isinya “Germany” maka pada hasilnya akan ditampilkan sekali saja tidak berulang.

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

4 WHERE

WHERE adalah argumen untuk memfilter values dari kolom. WHERE akan memfilter value sesuai dengan kondisi yang kita inginkan.

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 Constitucin 2222 Mxico D.F. 5021 Mexico
3 Antonio Moreno Taquera Antonio Moreno Mataderos 2312 Mxico D.F. 5023 Mexico
13 Centro comercial Moctezuma Francisco Chang Sierras de Granada 9993 Mxico D.F. 5022 Mexico
58 Pericles Comidas clsicas Guillermo Fernndez Calle Dr. Jorge Cash 321 Mxico D.F. 5033 Mexico
80 Tortuga Restaurante Miguel Angel Paolino Avda. Azteca 123 Mxico D.F. 5033 Mexico
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitucin 2222 Mxico D.F. 5021 Mexico
3 Antonio Moreno Taquera Antonio Moreno Mataderos 2312 Mxico D.F. 5023 Mexico
13 Centro comercial Moctezuma Francisco Chang Sierras de Granada 9993 Mxico D.F. 5022 Mexico
58 Pericles Comidas clsicas Guillermo Fernndez Calle Dr. Jorge Cash 321 Mxico D.F. 5033 Mexico
80 Tortuga Restaurante Miguel Angel Paolino Avda. Azteca 123 Mxico D.F. 5033 Mexico

5 BETWEEN

BETWEEN digunakan untuk memilih values dengan kondisi range tertentu. Kondisi range dapat berupa angka, teks, dan date. Untuk rangenya misal dari 20-50 maka range awal(20) dan range akhir(50) akan ikut dalam pemilihan value tersebut.


Contoh untuk BETWEEN dengan 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

Contoh untuk BETWEEN dengan range date

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 digunakan jika ingin menggunakan argumen WHERE dengan lebih dari 2 kondisi.

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 Maria Anders Obere Str. 57 Berlin 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 pre et fils Frdrique Citeaux 24, place Klber 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 
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 Maria Anders Obere Str. 57 Berlin 12209 Germany
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbkp Christina Berglund Berguvsvgen 8 Lule S-958 22 Sweden
6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
7 Blondel pre et fils Frdrique Citeaux 24, place Klber Strasbourg 67000 France
8 Blido Comidas preparadas Martn 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 Comrcio Mineiro Pedro Afonso Av. dos Lusadas, 23 So Paulo 05432-043 Brazil

7 LIKE

LIKE adalah argumen yang hampir sama dengan WHERE digunakan untuk memfilter dengan pola yang spesifik. Misal kita ingin menemukan nama customer yang depannya berawalan dari huruf D atau bisa kita cari nama customer yang tengahnya berawalan dari huruf A dsb.

SELECT *
  FROM Customers C
    WHERE C.CustomerName
      LIKE 'a%';
Displaying records 1 - 10
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitucin 2222 Mxico D.F. 5021 Mexico
3 Antonio Moreno Taquera Antonio Moreno Mataderos 2312 Mxico D.F. 5023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitucin 2222 Mxico D.F. 5021 Mexico
3 Antonio Moreno Taquera Antonio Moreno Mataderos 2312 Mxico D.F. 5023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitucin 2222 Mxico D.F. 5021 Mexico

operasi yang terkenal di argumen Like adalah

  • operasi ini mewakili multi character

  • operasi ini mewakili single character

8 AND, OR and NOT

Argumen ini dapat dikombinasikan dengan argumen WHERE. AND, OR, NOT sama halnya dengan logika matematika.


- operasi AND dimana akan terlihat hasilnya jika kedua data tersebut sama atau bernilai TRUE dan akan eror jika ada salah satu yang tidak sama atau bernilai FALSE.
- operasi OR dimana akan terlihat hasilnya jika salah satu data tersebut bernilai TRUE atau sesuai dengan kondisi yang diingikan.
- operasi NOT adalah operasi yang menampilkan hasil yang bernilai FALSE. 

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

Jika kita ingin menggunakan NOT pada WHERE dapat diwakilkan dengan <>

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 Constitucin 2222 Mxico D.F. 5021 Mexico
3 Antonio Moreno Taquera Antonio Moreno Mataderos 2312 Mxico D.F. 5023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbkp Christina Berglund Berguvsvgen 8 Lule S-958 22 Sweden
7 Blondel pre et fils Frdrique Citeaux 24, place Klber Strasbourg 67000 France
8 Blido Comidas preparadas Martn 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 digunakan untuk mengurutkan data dari besar ke kecil (z-a) atau bisa juka kecil ke besar (a-z) dengan kondisi


- DESC atau descending yaitu mengurutkan data dari besar ke kecil.
- ASC atau ascending yaitu mengurutkan data dari kecil ke besar.
- Akan tetapi jika kita tidak menggunakan keyword DESC atau ASC maka data akan diurutkan otomatis dengan ASC. 

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

10 LIMIT

LIMIT digunakan untuk memilih beberapa data sesuai dengan range atau rentang yang kita inginkan. Argumen ini digunakan jika data yang dihasilkan terlalu banyak. Maka kita dapat menggunakan arguman ini untuk memilih data dengan batas atas dikurang batas bawah. Misal kita mengambil 15 data akan tetapi kita tidak mau 10 data yang diatasnya maka kita gunakan argumen ini dengan batas atas 15 dan batas bawah 10. Secara otomatis dari 15 data akan ada 10 data yang diabaikan.

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;
3 records
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany 
SELECT CustomerName, Address, City, Country
  FROM customers C
  ORDER BY C.City, C.Country DESC
    LIMIT 3, 5;
5 records
CustomerName Address City Country
Vaffeljernet Smagslget 45 rhus Denmark
Vaffeljernet Smagslget 45 rhus Denmark
Drachenblut Delikatessend Walserweg 21 Aachen Germany
Drachenblut Delikatessend Walserweg 21 Aachen Germany
Drachenblut Delikatessend Walserweg 21 Aachen Germany

11 MIN and MAX

MIN digunakan jika kita ingin melihat values yang memiliki nilai yang kecil. Sedangkan MAX digunakan untuk melihat values yang memiliki nilai yang besar.

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

12 COUNT, SUM and AVG

COUNT digunakan untuk menjumlahkan beberapa baris yang sama sesuai dengan kriteria tertentu. SUM digunakan untuk menjumlahkan data yang memiliki type data numeric. Begitupun AVG digunakan untuk mencari rata-rata data numeric.

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

13 HAVING

HAVING adalah argumen yang digunakan apabila argumen WHERE tidak dapat digunakan. Biasanya kondisi WHERE tidak dapat digunakan pada aggregate function maka dari itu kita gunakan argumen HAVING.

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
65 USA
55 France
55 Germany
45 Brazil
35 UK
25 Mexico
25 Spain
20 Venezuela
15 Italy
15 Canada

14 CASE

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
Vaffeljernet rhus Denmark
Vaffeljernet rhus Denmark
Vaffeljernet rhus Denmark
Vaffeljernet rhus Denmark
Vaffeljernet rhus Denmark
Drachenblut Delikatessend Aachen Germany
Drachenblut Delikatessend Aachen Germany
Drachenblut Delikatessend Aachen Germany
Drachenblut Delikatessend Aachen Germany
Drachenblut Delikatessend Aachen Germany

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 ecclsiastiques Paris France
18 Aux joyeux ecclsiastiques Paris France
18 Aux joyeux ecclsiastiques Paris France
18 Aux joyeux ecclsiastiques Paris France
18 Aux joyeux ecclsiastiques Paris France
16 Bigfoot Breweries Bend USA
16 Bigfoot Breweries Bend USA
16 Bigfoot Breweries Bend USA
16 Bigfoot Breweries Bend USA
16 Bigfoot Breweries Bend USA

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
22 Zaanse Snoepfabriek Zaandam Netherlands
22 Zaanse Snoepfabriek Zaandam Netherlands
22 Zaanse Snoepfabriek Zaandam Netherlands
4 Tokyo Traders Tokyo Japan
4 Tokyo Traders Tokyo Japan
4 Tokyo Traders Tokyo Japan
4 Tokyo Traders Tokyo Japan
4 Tokyo Traders Tokyo Japan

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
7 Pavlova, Ltd. Melbourne Australia
7 Pavlova, Ltd. Melbourne Australia
7 Pavlova, Ltd. Melbourne Australia
24 G’day, Mate Sydney Australia
24 G’day, Mate Sydney Australia
24 G’day, Mate Sydney Australia
24 G’day, Mate Sydney Australia
24 G’day, Mate Sydney Australia

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

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
10398 71 2 1996-12-30 3 120
10398 71 2 1996-12-30 3 120
10398 71 2 1996-12-30 3 120
10398 71 2 1996-12-30 3 120
10398 71 2 1996-12-30 3 120
10398 71 2 1996-12-30 3 120
10398 71 2 1996-12-30 3 120
10398 71 2 1996-12-30 3 120

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 Cte de Blaye 18 1 12 - 75 cl bottles 263.50
38 Cte de Blaye 18 1 12 - 75 cl bottles 263.50
38 Cte de Blaye 18 1 12 - 75 cl bottles 263.50
38 Cte de Blaye 18 1 12 - 75 cl bottles 263.50
38 Cte de Blaye 18 1 12 - 75 cl bottles 263.50
29 Thringer Rostbratwurst 12 6 50 bags x 30 sausgs. 123.79
29 Thringer Rostbratwurst 12 6 50 bags x 30 sausgs. 123.79
29 Thringer Rostbratwurst 12 6 50 bags x 30 sausgs. 123.79
29 Thringer Rostbratwurst 12 6 50 bags x 30 sausgs. 123.79
29 Thringer Rostbratwurst 12 6 50 bags x 30 sausgs. 123.79

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
29 Thringer 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
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
20 Sir Rodney’s Marmalade 8 3 30 gift boxes 81.00
20 Sir Rodney’s Marmalade 8 3 30 gift boxes 81.00

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
5 records
ProductID ProductName SupplierID CategoryID Unit Price
33 Geitost 15 4 500 g 2.5
33 Geitost 15 4 500 g 2.5
33 Geitost 15 4 500 g 2.5
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
5 records
ProductID ProductName SupplierID CategoryID Unit Price
38 Cte de Blaye 18 1 12 - 75 cl bottles 263.5
38 Cte de Blaye 18 1 12 - 75 cl bottles 263.5
38 Cte de Blaye 18 1 12 - 75 cl bottles 263.5
38 Cte de Blaye 18 1 12 - 75 cl bottles 263.5
38 Cte 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
20

15.11 11. total quantity of orderiterm

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

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';
5 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
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 pre et fils Frdrique Citeaux 24, place Klber Strasbourg 67000 France
8 Blido Comidas preparadas Martn 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, chausse de Tournai Lille 59000 France
26 France restauration Carine Schmitt 54, rue Royale Nantes 44000 France
29 Galera del gastrnomo Eduardo Saavedra Rambla de Catalua, 23 Barcelona 8022 Spain
30 Godos Cocina Tpica 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 Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitucin 2222 Mxico D.F. 5021 Mexico
3 Antonio Moreno Taquera Antonio Moreno Mataderos 2312 Mxico D.F. 5023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbkp Christina Berglund Berguvsvgen 8 Lule S-958 22 Sweden
6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
7 Blondel pre et fils Frdrique Citeaux 24, place Klber Strasbourg 67000 France
8 Blido Comidas preparadas Martn 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 10248 10248 12
11 Queso Cabrales 21 10248 10248 12
11 Queso Cabrales 21 10248 10248 12
11 Queso Cabrales 21 10248 10248 12
11 Queso Cabrales 21 10248 10248 12
42 Singaporean Hokkien Fried Mee 14 10248 10248 10
42 Singaporean Hokkien Fried Mee 14 10248 10248 10
42 Singaporean Hokkien Fried Mee 14 10248 10248 10
42 Singaporean Hokkien Fried Mee 14 10248 10248 10
42 Singaporean Hokkien Fried Mee 14 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 Cte de Blaye 18 1 12 - 75 cl bottles 263.50
38 Cte de Blaye 18 1 12 - 75 cl bottles 263.50
38 Cte de Blaye 18 1 12 - 75 cl bottles 263.50
38 Cte de Blaye 18 1 12 - 75 cl bottles 263.50
38 Cte de Blaye 18 1 12 - 75 cl bottles 263.50
29 Thringer Rostbratwurst 12 6 50 bags x 30 sausgs. 123.79
29 Thringer Rostbratwurst 12 6 50 bags x 30 sausgs. 123.79
29 Thringer Rostbratwurst 12 6 50 bags x 30 sausgs. 123.79
29 Thringer Rostbratwurst 12 6 50 bags x 30 sausgs. 123.79
29 Thringer Rostbratwurst 12 6 50 bags x 30 sausgs. 123.79

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 3375
81 Tradio Hipermercados 10249 81 1996-07-05 10249 6125
34 Hanari Carnes 10250 34 1996-07-08 10250 7500
84 Victuailles en stock 10251 84 1996-07-08 10251 5125
76 Suprmes dlices 10252 76 1996-07-09 10252 13125
34 Hanari Carnes 10253 34 1996-07-10 10253 12750
14 Chop-suey Chinese 10254 14 1996-07-11 10254 7125
68 Richter Supermarkt 10255 68 1996-07-12 10255 13750
88 Wellington Importadora 10256 88 1996-07-15 10256 3375
35 HILARIN-Abastos 10257 35 1996-07-16 10257 5750

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 Swaren GmbH & Co. KG Germany
Alfreds Futterkiste Germany Plutzer Lebensmittelgromrkte AG Germany
Alfreds Futterkiste Germany Nord-Ost-Fisch Handelsgesellschaft mbH Germany
Alfreds Futterkiste Germany Heli Swaren GmbH & Co. KG Germany
Alfreds Futterkiste Germany Plutzer Lebensmittelgromrkte AG Germany
Alfreds Futterkiste Germany Nord-Ost-Fisch Handelsgesellschaft mbH Germany
Alfreds Futterkiste Germany Heli Swaren GmbH & Co. KG Germany
Alfreds Futterkiste Germany Plutzer Lebensmittelgromrkte AG Germany
Alfreds Futterkiste Germany Nord-Ost-Fisch Handelsgesellschaft mbH Germany
Alfreds Futterkiste Germany Heli Swaren GmbH & Co. KG Germany

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_';
5 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
2 Chang 1 1 24 - 12 oz bottles 19
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 Knckebrd AB Lars Peterson Kaloadagatan 13 Gteborg S-345 67 Sweden 031-987 65 43
10 Refrescos Americanas LTDA Carlos Diaz Av. das Americanas 12.890 So 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
0 records
CustomerName AvgOrder

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 15
Austria 10
Belgium 10
Brazil 45
Canada 15
Denmark 10
Finland 10
France 55
Germany 55
Ireland 5