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

Customers <- read.csv("Customers.csv")
Categories <- read.csv("Categories.csv")
Employees <- read.csv("Employees.csv")
OrderDetails <- read.csv("OrderDetails.csv")
Orders <- read.csv("Orders.csv")
Products <- read.csv("Products.csv")
Shippers <- read.csv("Shippers.csv")
Suppliers <- read.csv("Suppliers.csv")
pacman::p_load(RMariaDB,
               RMySQL,
               DBI)
sausan <- dbConnect(RMySQL::MySQL(),
                     user = 'root',
                     password = '',
                     dbname = 'factory_db',
                     host = 'localhost')


knitr::opts_chunk$set(connection = "sausan") # to set up the connection in your Rmarkdown chunk
#dbWriteTable(sausan, "Customers", Customers, append=T)
#dbWriteTable(sausan, "Categories", Categories, append=T)
#dbWriteTable(sausan, "Employees", Employees, append=T)
#dbWriteTable(sausan, "OrderDetails", OrderDetails, append=T)
#dbWriteTable(sausan, "Orders", Orders, append=T)
#dbWriteTable(sausan, "Products", Products, append=T)
#dbWriteTable(sausan, "Shippers", Shippers, append=T)
#dbWriteTable(sausan, "Suppliers", Suppliers, append=T

2 Question

2.1 1. Select Some attributes of suppliers in alphabetical order! 

SELECT S.SupplierID, S.SupplierName, S.ContactName, S.Country
FROM suppliers S
ORDER BY S.SupplierName ASC;
Displaying records 1 - 10
SupplierID SupplierName ContactName Country
18 Aux joyeux ecclésiastiques Guylène Nodier France
18 Aux joyeux ecclésiastiques Guylène Nodier France
16 Bigfoot Breweries Cheryl Saylor USA
16 Bigfoot Breweries Cheryl Saylor USA
5 Cooperativa de Quesos ‘Las Cabras’ Antonio del Valle Saavedra Spain
5 Cooperativa de Quesos ‘Las Cabras’ Antonio del Valle Saavedra Spain
27 Escargots Nouveaux Marie Delamare France
27 Escargots Nouveaux Marie Delamare France
1 Exotic Liquid Charlotte Cooper UK
1 Exotic Liquid Charlotte Cooper UK

2.2 2. Some attributes of suppliers in reverse alphabetical order! 

SELECT S.SupplierID, S.SupplierName, S.ContactName, S.Country
FROM suppliers S
ORDER BY S.SupplierName DESC;
Displaying records 1 - 10
SupplierID SupplierName ContactName Country
22 Zaanse Snoepfabriek Dirk Luchte Netherlands
22 Zaanse Snoepfabriek Dirk Luchte Netherlands
4 Tokyo Traders Yoshi Nagase Japan
4 Tokyo Traders Yoshi Nagase Japan
17 Svensk Sjöföda AB Michael Björn Sweden
17 Svensk Sjöföda AB Michael Björn Sweden
8 Specialty Biscuits, Ltd. Peter Wilson UK
8 Specialty Biscuits, Ltd. Peter Wilson UK
10 Refrescos Americanas LTDA Carlos Diaz Brazil
10 Refrescos Americanas LTDA Carlos Diaz Brazil

2.3 3. Some attributes of suppliers ordered by country, then by the city! 

SELECT S.SupplierID, S.SupplierName, S.ContactName, S.City, S.Country
FROM suppliers S
ORDER BY S.Country, S.City;
Displaying records 1 - 10
SupplierID SupplierName ContactName City Country
7 Pavlova, Ltd. Ian Devling Melbourne Australia
7 Pavlova, Ltd. Ian Devling Melbourne Australia
24 G’day, Mate Wendy Mackenzie Sydney Australia
24 G’day, Mate Wendy Mackenzie Sydney Australia
10 Refrescos Americanas LTDA Carlos Diaz São Paulo Brazil
10 Refrescos Americanas LTDA Carlos Diaz São Paulo Brazil
25 Ma Maison Jean-Guy Lauzon Montréal Canada
25 Ma Maison Jean-Guy Lauzon Montréal Canada
29 Forêts d’érables Chantal Goulet Ste-Hyacinthe Canada
29 Forêts d’érables Chantal Goulet Ste-Hyacinthe Canada

2.4 4. All attributes of suppliers and reverse alphabetically order by country, then by city! 

SELECT S.*
FROM suppliers S
ORDER BY S.Country DESC, S.City DESC;
Displaying records 1 - 10
SupplierID SupplierName ContactName Address City PostalCode Country Phone
2 New Orleans Cajun Delights Shelley Burke P.O. Box 78934 New Orleans 70117 USA (100) 555-4822
2 New Orleans Cajun Delights Shelley Burke P.O. Box 78934 New Orleans 70117 USA (100) 555-4822
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

2.5 5. All orders, sorted by total amount, the largest first! 

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
10359 72 5 1996-11-21 3 80
10324 71 9 1996-10-08 1 80
10345 63 2 1996-11-04 2 80
10359 72 5 1996-11-21 3 80

2.6 6. Get all but the 10 most expensive products sorted by price! 

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

2.7 7. Get the 10th to 15th most expensive products sorted by price! 

SELECT P.*
FROM products P
ORDER BY P.Price DESC
LIMIT 6 OFFSET 9;
6 records
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

2.8 8. List all supplier countries in alphabetical order! 

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

2.9 9. Find the cheapest product and Expensive Orders! 

SELECT P.*
FROM products P
WHERE P.Price = (SELECT MIN(P.Price)
                 FROM products P);
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 P.*
FROM products P
WHERE P.Price = (SELECT MAX(P.Price)
                 FROM products P);
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

2.10 10. Find the number of Suppliers USA! 

SELECT S.Country, COUNT(S.Country) NumberOfSuppliersUSA
FROM suppliers S
WHERE S.Country="USA"
ORDER BY S.Country;
1 records
Country NumberOfSuppliersUSA
USA 8

2.11 11. Compute the total Quantity of the ordered item! 

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

2.12 12. Compute the average UnitPrice of all products! 

SELECT AVG(P.price) AverageUnitPrice
FROM products P;
1 records
AverageUnitPrice
28.86636

2.13 13. Get all information about a customer named Thomas Hardy! 

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

2.14 14. List all customers from Spain or France! 

SELECT C.*
FROM customers C
WHERE C.Country="Spain" OR "France";
Displaying records 1 - 10
CustomerID CustomerName ContactName Address City PostalCode Country
8 Bólido Comidas preparadas Martín Sommer C/ Araquil, 67 Madrid 28023 Spain
22 FISSA Fabrica Inter. Salchichas S.A. Diego Roel C/ Moralzarzal, 86 Madrid 28034 Spain
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
69 Romero y tomillo Alejandra Camino Gran Vía, 1 Madrid 28001 Spain
8 Bólido Comidas preparadas Martín Sommer C/ Araquil, 67 Madrid 28023 Spain
22 FISSA Fabrica Inter. Salchichas S.A. Diego Roel C/ Moralzarzal, 86 Madrid 28034 Spain
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
69 Romero y tomillo Alejandra Camino Gran Vía, 1 Madrid 28001 Spain

2.15 15. List all customers that are not from the USA! 

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

2.16 16. List all orders that are not between $50 and $15000! 

SELECT P.*
FROM products P
WHERE P.Price NOT BETWEEN "50" AND "15000";
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
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
7 Uncle Bob’s Organic Dried Pears 3 7 12 - 1 lb pkgs. 30.00
8 Northwoods Cranberry Sauce 3 2 12 - 12 oz jars 40.00
10 Ikura 4 8 12 - 200 ml jars 31.00
11 Queso Cabrales 5 4 1 kg pkg. 21.00

2.17 17. List all products between $10 and $20 

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

2.18 18. List all products, not between $10 and $100 sorted by price! 

SELECT P.*
FROM products P
WHERE P.Price NOT BETWEEN "10" AND "100"
ORDER BY P.Price ASC;
Displaying records 1 - 10
ProductID ProductName SupplierID CategoryID Unit Price
33 Geitost 15 4 500 g 2.50
33 Geitost 15 4 500 g 2.50
24 Guaraná Fantástica 10 1 12 - 355 ml cans 4.50
24 Guaraná Fantástica 10 1 12 - 355 ml cans 4.50
13 Konbu 6 8 2 kg box 6.00
13 Konbu 6 8 2 kg box 6.00
52 Filo Mix 24 5 16 - 2 kg boxes 7.00
52 Filo Mix 24 5 16 - 2 kg boxes 7.00
54 Tourtière 25 6 16 pies 7.45
54 Tourtière 25 6 16 pies 7.45

2.19 19. Get the list of orders and amounts sold between 1996 Jan 01 and 1996 Des 31! 

SELECT O.*
FROM orders O
WHERE O.OrderDate BETWEEN "1996-01-01" AND "1996-12-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

2.20 20. List all suppliers from the USA, UK, OR Japan! 

SELECT S.*
FROM suppliers S
WHERE S.Country="USA" OR S.Country="UK" OR S.Country="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

2.21 21.List all products that are not exactly $10, $20, $30, $40, or $50! 

SELECT P.*
FROM products P
WHERE P.Price NOT IN(10,20,30,40,50);
Displaying records 1 - 10
ProductID ProductName SupplierID CategoryID Unit Price
1 Chais 1 1 10 boxes x 20 bags 18.00
2 Chang 1 1 24 - 12 oz bottles 19.00
4 Chef Anton’s Cajun Seasoning 2 2 48 - 6 oz jars 22.00
5 Chef Anton’s Gumbo Mix 2 2 36 boxes 21.35
6 Grandma’s Boysenberry Spread 3 2 12 - 8 oz jars 25.00
9 Mishi Kobe Niku 4 6 18 - 500 g pkgs. 97.00
10 Ikura 4 8 12 - 200 ml jars 31.00
11 Queso Cabrales 5 4 1 kg pkg. 21.00
12 Queso Manchego La Pastora 5 4 10 - 500 g pkgs. 38.00
13 Konbu 6 8 2 kg box 6.00

2.22 22. List all customers that are from the same countries as the suppliers! 

SELECT C.CustomerName, S.SupplierName, C.Country, S.Country
FROM customers C
JOIN orders O
ON C.CustomerID=O.CustomerID
JOIN orderdetails OD
ON O.OrderID=OD.OrderID
JOIN products P
ON OD.ProductID=P.ProductID
JOIN suppliers S
ON P.SupplierID=S.SupplierID
WHERE C.Country=S.Country;
Displaying records 1 - 10
CustomerName SupplierName Country Country
Old World Delicatessen New England Seafood Cannery USA USA
Old World Delicatessen New England Seafood Cannery USA USA
Old World Delicatessen New England Seafood Cannery USA USA
Old World Delicatessen New England Seafood Cannery USA USA
Old World Delicatessen New England Seafood Cannery USA USA
Old World Delicatessen New England Seafood Cannery USA USA
Old World Delicatessen New England Seafood Cannery USA USA
Old World Delicatessen New England Seafood Cannery USA USA
Rattlesnake Canyon Grocery New Orleans Cajun Delights USA USA
Rattlesnake Canyon Grocery New Orleans Cajun Delights USA USA

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

SELECT P.*
FROM products P
WHERE ProductName LIKE "Cha_" OR 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

2.24 24. List all suppliers that do have a fax number! 

SELECT S.*
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

2.25 25. List all customers with average orders between $1000 and $1200! 

SELECT CustomerName, AVG(TotalAmount) AverageOrders
FROM 
(
SELECT CustomerName, SUM(od.Quantity * p.Price) TotalAmount
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
) A
GROUP BY CustomerName
HAVING AverageOrders
BETWEEN 1000 AND 1200
ORDER BY AverageOrders
1 records
CustomerName AverageOrders
Centro comercial Moctezuma 1008

2.26 26. List the total customers in each country. 

SELECT C.Country, COUNT(CustomerName) 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

2.27 27. Display results with easy-to-understand column headers.

2.28 28. List all orders with customer information! 

SELECT DISTINCT O.OrderID, C.*
FROM Orders O
INNER JOIN customers C
ON O.CustomerID = C.CustomerID
ORDER BY O.OrderID ASC;
Displaying records 1 - 10
OrderID CustomerID CustomerName ContactName Address City PostalCode Country
10248 90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland
10249 81 Tradição Hipermercados Anabela Domingues Av. Inês de Castro, 414 São Paulo 05634-030 Brazil
10250 34 Hanari Carnes Mario Pontes Rua do Paço, 67 Rio de Janeiro 05454-876 Brazil
10251 84 Victuailles en stock Mary Saveley 2, rue du Commerce Lyon 69004 France
10252 76 Suprêmes délices Pascale Cartrain Boulevard Tirou, 255 Charleroi B-6000 Belgium
10253 34 Hanari Carnes Mario Pontes Rua do Paço, 67 Rio de Janeiro 05454-876 Brazil
10254 14 Chop-suey Chinese Yang Wang Hauptstr. 29 Bern 3012 Switzerland
10255 68 Richter Supermarkt Michael Holz Grenzacherweg 237 Genève 1203 Switzerland
10256 88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil
10257 35 HILARIÓN-Abastos Carlos Hernández Carrera 22 con Ave. Carlos Soublette #8-35 San Cristóbal 5022 Venezuela

2.29 29. List all orders with product names, quantities, and prices! 

SELECT DISTINCT O.OrderID, P.ProductName, OD.Quantity, P.Price
FROM Orders O
INNER JOIN orderdetails OD
ON OD.OrderID = OD.OrderID
INNER JOIN products P
ON OD.ProductID = P.ProductID
ORDER BY O.OrderID ASC;
Displaying records 1 - 10
OrderID ProductName Quantity Price
10248 Inlagd Sill 12 19.0
10248 Pâté chinois 120 24.0
10248 Inlagd Sill 6 19.0
10248 Gudbrandsdalsost 15 36.0
10248 Guaraná Fantástica 25 4.5
10248 Geitost 49 2.5
10248 Camembert Pierrot 60 34.0
10248 Scottish Longbreads 8 12.5
10248 Gorgonzola Telino 56 12.5
10248 Gnocchi di nonna Alice 20 38.0

2.30 30. This will list all customers, whether they placed any order or not! 

SELECT C.CustomerID, C.CustomerName, C.ContactName, O.OrderID
FROM customers C
LEFT JOIN orders O
ON C.CustomerID = O.CustomerID
ORDER BY C.CustomerID ASC;
Displaying records 1 - 10
CustomerID CustomerName ContactName OrderID
1 Alfreds Futterkiste Ramadhani NA
1 Alfreds Futterkiste Ramadhani NA
2 Ana Trujillo Emparedados y helados Ana Trujillo 10308
2 Ana Trujillo Emparedados y helados Ana Trujillo 10308
3 Antonio Moreno Taquería Antonio Moreno 10365
3 Antonio Moreno Taquería Antonio Moreno 10365
4 Around the Horn Thomas Hardy 10383
4 Around the Horn Thomas Hardy 10383
4 Around the Horn Thomas Hardy 10355
4 Around the Horn Thomas Hardy 10355

2.31 31. List customers that have not placed orders! 

SELECT C.*
FROM customers C
WHERE C.CustomerID NOT IN ( SELECT O.CustomerID
                            FROM orders O )
ORDER BY C.CustomerID ASC;
Displaying records 1 - 10
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Ramadhani Obere Str. 57 Turki 12209 Germany
1 Alfreds Futterkiste Ramadhani Obere Str. 57 Turki 12209 Germany
6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
12 Cactus Comidas para llevar Patricio Simpson Cerrito 333 Buenos Aires 1010 Argentina
12 Cactus Comidas para llevar Patricio Simpson Cerrito 333 Buenos Aires 1010 Argentina
22 FISSA Fabrica Inter. Salchichas S.A. Diego Roel C/ Moralzarzal, 86 Madrid 28034 Spain
22 FISSA Fabrica Inter. Salchichas S.A. Diego Roel C/ Moralzarzal, 86 Madrid 28034 Spain
26 France restauration Carine Schmitt 54, rue Royale Nantes 44000 France
26 France restauration Carine Schmitt 54, rue Royale Nantes 44000 France

2.32 32. List all contacts, i.e., suppliers and customers! 

SELECT S.ContactName, 'Suppliers' Type
  FROM suppliers S
    UNION ALL
      SELECT C.ContactName, 'Customers' Type
        FROM customers C
  ORDER BY ContactName ASC;
Displaying records 1 - 10
ContactName Type
Alejandra Camino Customers
Alejandra Camino Customers
Alexander Feuer Customers
Alexander Feuer Customers
Ana Trujillo Customers
Ana Trujillo Customers
Anabela Domingues Customers
Anabela Domingues Customers
André Fonseca Customers
André Fonseca Customers

2.33 33. List products with order quantities greater than 80! 

SELECT P.ProductID, P.ProductName, SUM(OD.Quantity) AS 'Quantity'
FROM Products P
INNER JOIN OrderDetails OD
ON P.ProductID = OD.ProductID
GROUP BY P.ProductID
HAVING SUM(OD.Quantity) > 80
ORDER BY P.ProductID
Displaying records 1 - 10
ProductID ProductName Quantity
1 Chais 636
2 Chang 1364
3 Aniseed Syrup 320
4 Chef Anton’s Cajun Seasoning 428
5 Chef Anton’s Gumbo Mix 516
6 Grandma’s Boysenberry Spread 144
7 Uncle Bob’s Organic Dried Pears 100
8 Northwoods Cranberry Sauce 560
10 Ikura 340
11 Queso Cabrales 728

2.34 34. Which products were sold by the unit (i.e. quantity =1)? 

SELECT P.ProductID, P.ProductName, OD.Quantity
FROM Products P
INNER JOIN OrderDetails OD
ON P.ProductID = OD.ProductID
WHERE OD.Quantity = 1
ORDER BY P.ProductID
Displaying records 1 - 10
ProductID ProductName Quantity
19 Teatime Chocolate Biscuits 1
19 Teatime Chocolate Biscuits 1
19 Teatime Chocolate Biscuits 1
19 Teatime Chocolate Biscuits 1
37 Gravad lax 1
37 Gravad lax 1
37 Gravad lax 1
37 Gravad lax 1
69 Gudbrandsdalsost 1
69 Gudbrandsdalsost 1

2.35 35. List customers who placed orders that are larger than the average of each customer order! 

SELECT C.CustomerID, C.CustomerName, OD.Quantity*P.Price as 'TotalAmount'
  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
  WHERE OD.Quantity*P.Price > ALL (
                                    SELECT AVG(OD.Quantity*P.Price)
                                      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 c.CustomerID
                                    )
Displaying records 1 - 10
CustomerID CustomerName TotalAmount
20 Ernst Handel 5270.00
20 Ernst Handel 5270.00
7 Blondel père et fils 4332.65
7 Blondel père et fils 4332.65
51 Mère Paillarde 12911.50
51 Mère Paillarde 12911.50
20 Ernst Handel 5270.00
20 Ernst Handel 5270.00
7 Blondel père et fils 4332.65
7 Blondel père et fils 4332.65

2.36 36. Find best-selling products based on quantity! 

SELECT P.ProductID, P.ProductName, P.Price, SUM(OD.Quantity) AS 'Quantity'
  FROM Products P
    JOIN Orderdetails OD
ON P.ProductID=OD.ProductID
GROUP BY ProductName
ORDER BY Quantity DESC
LIMIT 1;
1 records
ProductID ProductName Price Quantity
31 Gorgonzola Telino 12.5 1832

2.37 37. Find best-selling products based on revenue! 

SELECT P.ProductName, P.Price, OD.Quantity, SUM(P.Price*OD.Quantity) AS Revenue
  FROM Products P
    JOIN Orderdetails OD
      ON P.ProductID=OD. ProductID
        GROUP BY ProductName
          ORDER BY Revenue DESC
            LIMIT 1;
1 records
ProductName Price Quantity Revenue
Côte de Blaye 263.5 20 251906

2.38 38. Find best-selling products based on revenue for each country! 

SELECT Country, ProductName, MAX(Revenue) AS 'Revenue' 
FROM(SELECT P.ProductName, C.Country, SUM(OD.Quantity*P.Price) AS 'Revenue' 
  FROM products P
    JOIN orderdetails OD
      ON P.ProductID = OD.ProductID
    JOIN orders O
      ON OD.OrderID = O.OrderID
    JOIN customers C
      ON O. CustomerID = C.CustomerID
    GROUP BY C. Country, P.ProductName
    ORDER BY Country ASC, Revenue DESC) S
    GROUP BY Country
Displaying records 1 - 10
Country ProductName Revenue
Argentina Tofu 2232.0
Austria Côte de Blaye 147560.0
Belgium Sir Rodney’s Marmalade 25920.0
Brazil Côte de Blaye 84320.0
Canada Côte de Blaye 103292.0
Denmark Côte de Blaye 105400.0
Finland Fløtemysost 12040.0
France Thüringer Rostbratwurst 34661.2
Germany Raclette Courdavault 30800.0
Ireland Manjimup Dried Apples 20352.0

2.39 39. Find suppliers with a product price of less than $50! 

SELECT S.SupplierName, P.Price
  FROM suppliers S
    JOIN products P
      ON S.SupplierID = P.SupplierID
  WHERE P.Price < 50
  GROUP BY S.SupplierName
  ORDER BY S.SupplierName
Displaying records 1 - 10
SupplierName Price
Aux joyeux ecclésiastiques 18.00
Bigfoot Breweries 14.00
Cooperativa de Quesos ‘Las Cabras’ 21.00
Escargots Nouveaux 13.25
Exotic Liquid 18.00
Forêts d’érables 28.50
Formaggi Fortini s.r.l. 12.50
G’day, Mate 7.00
Gai pâturage 34.00
Grandma Kelly’s Homestead 25.00

2.40 40. Find the top 10 best employees based on their sales quantity! 

SELECT CONCAT (FirstName,' ',LastName)AS 'Name', sum(Quantity) AS 'Quantity'
  FROM employees E
    LEFT JOIN orders O
          ON E.EmployeeID = O.EmployeeID
    LEFT JOIN orderdetails OD
          ON O.OrderID = OD.OrderID
  GROUP BY E.EmployeeID
    ORDER BY Quantity DESC
      LIMIT 10;
Displaying records 1 - 10
Name Quantity
Margaret Peacock 12928
Nancy Davolio 7696
Janet Leverling 6900
Andrew Fuller 5260
Laura Callahan 5172
Michael Suyama 4376
Steven Buchanan 3112
Robert King 2932
Anne Dodsworth 2596
Adam West NA

2.41 41. Find the top 10 best supplier countries based on quantity! 

SELECT S.Country, sum(Quantity) AS 'Quantity'
  FROM suppliers S
    LEFT JOIN products P
          ON S.SupplierID = P.SupplierID
    LEFT JOIN orderdetails OD
          ON OD.ProductID = P.ProductID
  GROUP BY S.Country
    ORDER BY Quantity DESC
      LIMIT 10;
Displaying records 1 - 10
Country Quantity
Australia 12880
USA 12648
France 11488
Germany 10712
Italy 9736
UK 9704
Canada 7592
Norway 6688
Japan 4480
Sweden 3720

2.42 42. Find the top 10 best customer countries based on quantity! 

SELECT C.Country, sum(Quantity) AS 'Quantity'
  FROM customers C
    LEFT JOIN orders O
          ON C.CustomerID = O.CustomerID
    LEFT JOIN orderdetails OD
          ON O.OrderID = OD.OrderID
  GROUP BY C.Country
    ORDER BY Quantity DESC
      LIMIT 10;
Displaying records 1 - 10
Country Quantity
USA 8556
Germany 8060
Austria 6260
Brazil 4468
France 3556
Canada 3088
UK 2792
Ireland 2260
Venezuela 1968
Sweden 1596

2.43 43. Find the top 10 best-selling products based on quantity every year! 

(SELECT P.ProductName, YEAR(O.OrderDate) AS 'Year', sum(OD.Quantity) AS 'Quantity'
  FROM products P
    JOIN orderdetails OD
      ON P.ProductID = OD.ProductID
    JOIN orders O
      ON OD.OrderID = O.OrderID
       GROUP BY Year, ProductName
        HAVING Year = 1996
         ORDER BY Year, Quantity DESC
          LIMIT 10)
UNION
  (SELECT P.ProductName, YEAR(O.OrderDate) AS 'Year', sum(OD.Quantity) AS 'Quantity'
    FROM products P
     JOIN orderdetails OD
      ON P.ProductID = OD.ProductID
    JOIN orders O
      ON OD.OrderID = O.OrderID
  GROUP BY Year, ProductName
  HAVING Year = 1997
  ORDER BY Year, Quantity DESC
  LIMIT 10)
Displaying records 1 - 10
ProductName Year Quantity
Gorgonzola Telino 1996 1776
Camembert Pierrot 1996 1480
Steeleye Stout 1996 1096
Chartreuse verte 1996 1064
Fløtemysost 1996 1044
Mozzarella di Giovanni 1996 1040
Pavlova 1996 1008
Tarte au sucre 1996 1000
Alice Mutton 1996 936
Raclette Courdavault 1996 924

2.44 44. Measure the average order of product names from each country and order it from max to min. 

SELECT Country, ProductName, AVG(quantity) AS 'quantity' 
FROM
(SELECT P.ProductName, C.Country, AVG(OD.Quantity) AS 'quantity' 
  FROM products P
    JOIN orderdetails OD
      ON P.ProductID = OD.ProductID
    JOIN orders O
      ON OD.OrderID = O.OrderID
    JOIN customers C
      ON O. CustomerID = C.CustomerID
    GROUP BY C. Country, P.ProductName
   ) S
    GROUP BY Country
    Order BY quantity
Displaying records 1 - 10
Country ProductName quantity
Mexico Alice Mutton 11.45000
Argentina Sir Rodney’s Scones 12.00000
Norway Fløtemysost 12.00000
Spain Boston Crab Meat 13.76667
Finland Fløtemysost 17.20833
Italy Guaraná Fantástica 17.59091
Portugal Chef Anton’s Cajun Seasoning 18.11111
France Alice Mutton 20.53571
Sweden Camembert Pierrot 21.31250
Brazil Boston Crab Meat 21.72072

2.45 45. Compare the average order of product names from each country in the year 1996 vs 1997 ordering from max to min. 

(SELECT P.ProductName, C.Country, YEAR(O.OrderDate) AS 'Year', AVG(OD.Quantity*P.Price) AS 'Revenue'
  FROM products P
    JOIN orderdetails OD
      ON P.ProductID = OD.ProductID
    JOIN orders O
      ON OD.OrderID = O.OrderID
      JOIN customers C
      ON O.CustomerID = C.CustomerID
       GROUP BY Country, Year
        HAVING Year = 1996
         ORDER BY Year, Quantity DESC)
UNION

(SELECT P.ProductName, C.Country, YEAR(O.OrderDate) AS 'Year', AVG(OD.Quantity*P.Price) AS 'Revenue'
  FROM products P
    JOIN orderdetails OD
      ON P.ProductID = OD.ProductID
    JOIN orders O
      ON OD.OrderID = O.OrderID
      JOIN customers C
      ON O.CustomerID = C.CustomerID
       GROUP BY Country, Year
        HAVING Year = 1997
         ORDER BY Year, Quantity DESC)
          ORDER BY Revenue, Year
Displaying records 1 - 10
ProductName Country Year Revenue
Tofu Argentina 1997 199.5000
Guaraná Fantástica Italy 1996 209.3333
Gnocchi di nonna Alice Spain 1997 211.3750
Queso Cabrales Sweden 1997 225.0000
Teatime Chocolate Biscuits Spain 1996 242.4650
Gorgonzola Telino Poland 1996 286.8750
Sir Rodney’s Scones Mexico 1996 293.0780
Guaraná Fantástica Norway 1996 330.9000
Tofu Finland 1997 371.8000
Geitost Denmark 1996 376.6850