Database System

Tugas 3 Exercise DBS-5


Kontak \(\downarrow\)
Email
Instagram https://www.instagram.com/nbrigittag/
RPubs https://rpubs.com/naftalibrigitta/
Nama Naftali Brigitta Gunawan
NIM 20214920002 
library(DBI)
## Warning: package 'DBI' was built under R version 4.1.3
library(RMySQL)
## Warning: package 'RMySQL' was built under R version 4.1.3
naftali <- dbConnect(RMySQL::MySQL(),
                     user = 'root',
                     password = '',
                     dbname = 'factory_db',
                     host = 'localhost')

knitr::opts_chunk$set(connection = "naftali") # to set up the connection in your Rmarkdown chunk

1. Select Some attributes of suppliers in alphabetical order!

SELECT SupplierName, ContactName, Address, Country, City
FROM suppliers
ORDER BY SupplierName
Displaying records 1 - 10
SupplierName ContactName Address Country City
Aux joyeux ecclésiastiques Guylène Nodier 203, Rue des Francs-Bourgeois France Paris
Bigfoot Breweries Cheryl Saylor 3400 - 8th Avenue Suite 210 USA Bend
Cooperativa de Quesos ‘Las Cabras’ Antonio del Valle Saavedra Calle del Rosal 4 Spain Oviedo
Escargots Nouveaux Marie Delamare 22, rue H. Voiron France Montceau
Exotic Liquid Charlotte Cooper 49 Gilbert St. UK Londona
Forêts d’érables Chantal Goulet 148 rue Chasseur Canada Ste-Hyacinthe
Formaggi Fortini s.r.l. Elio Rossi Viale Dante, 75 Italy Ravenna
G’day, Mate Wendy Mackenzie 170 Prince Edward Parade Hunter’s Hill Australia Sydney
Gai pâturage Eliane Noz Bat. B 3, rue des Alpes France Annecy
Grandma Kelly’s Homestead Regina Murphy 707 Oxford Rd. USA Ann Arbor

2. Some attributes of suppliers in reverse alphabetical order!

SELECT SupplierName, ContactName, Address, Country, City
FROM suppliers
ORDER BY SupplierName DESC
Displaying records 1 - 10
SupplierName ContactName Address Country City
Zaanse Snoepfabriek Dirk Luchte Verkoop Rijnweg 22 Netherlands Zaandam
Tokyo Traders Yoshi Nagase 9-8 Sekimai Musashino-shi Japan Tokyo
Svensk Sjöföda AB Michael Björn Brovallavägen 231 Sweden Stockholm
Specialty Biscuits, Ltd. Peter Wilson 29 King’s Way UK Manchester
Refrescos Americanas LTDA Carlos Diaz Av. das Americanas 12.890 Brazil São Paulo
Plutzer Lebensmittelgroßmärkte AG Martin Bein Bogenallee 51 Germany Frankfurt
PB Knäckebröd AB Lars Peterson Kaloadagatan 13 Sweden Göteborg
Pavlova, Ltd. Ian Devling 74 Rose St. Moonie Ponds Australia Melbourne
Pasta Buttini s.r.l. Giovanni Giudici Via dei Gelsomini, 153 Italy Salerno
Norske Meierier Beate Vileid Hatlevegen 5 Norway Sandvika

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

SELECT SupplierName, ContactName, Address, Country, City
FROM suppliers
ORDER BY Country, City
Displaying records 1 - 10
SupplierName ContactName Address Country City
Pavlova, Ltd. Ian Devling 74 Rose St. Moonie Ponds Australia Melbourne
G’day, Mate Wendy Mackenzie 170 Prince Edward Parade Hunter’s Hill Australia Sydney
Refrescos Americanas LTDA Carlos Diaz Av. das Americanas 12.890 Brazil São Paulo
Ma Maison Jean-Guy Lauzon 2960 Rue St. Laurent Canada Montréal
Forêts d’érables Chantal Goulet 148 rue Chasseur Canada Ste-Hyacinthe
Lyngbysild Niels Petersen Lyngbysild Fiskebakken 10 Denmark Lyngby
Karkki Oy Anne Heikkonen Valtakatu 12 Finland Lappeenranta
Gai pâturage Eliane Noz Bat. B 3, rue des Alpes France Annecy
Escargots Nouveaux Marie Delamare 22, rue H. Voiron France Montceau
Aux joyeux ecclésiastiques Guylène Nodier 203, Rue des Francs-Bourgeois France Paris

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

SELECT *
FROM suppliers
ORDER BY Country DESC, City DESC
Displaying records 1 - 10
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
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
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
1 Exotic Liquid Charlotte Cooper 49 Gilbert St. Londona EC1 4SD UK (171) 555-2222
17 Svensk Sjöföda AB Michael Björn Brovallavägen 231 Stockholm S-123 45 Sweden 08-123 45 67
9 PB Knäckebröd AB Lars Peterson Kaloadagatan 13 Göteborg S-345 67 Sweden 031-987 65 43
5 Cooperativa de Quesos ‘Las Cabras’ Antonio del Valle Saavedra Calle del Rosal 4 Oviedo 33007 Spain (98) 598 76 54
20 Leka Trading Chandra Leka 471 Serangoon Loop, Suite #402 Singapore 512 Singapore 555-8787

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

SELECT o.*, od.Quantity
  FROM orders o
    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
10286 63 8 1996-08-21 3 100
10440 71 4 1997-02-10 2 90
10345 63 2 1996-11-04 2 80
10359 72 5 1996-11-21 3 80
10373 37 4 1996-12-05 3 80
10442 20 3 1997-02-11 2 80
10324 71 9 1996-10-08 1 80
10351 20 1 1996-11-11 1 77
10401 65 1 1997-01-01 1 70

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

SELECT *
FROM products
ORDER BY Price DESC
LIMIT 10
Displaying records 1 - 10
ProductID ProductName SupplierID CategoryID Unit Price
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
9 Mishi Kobe Niku 4 6 18 - 500 g pkgs. 97.00
20 Sir Rodney’s Marmalade 8 3 30 gift boxes 81.00
18 Carnarvon Tigers 7 8 16 kg pkg. 62.50
59 Raclette Courdavault 28 4 5 kg pkg. 55.00
51 Manjimup Dried Apples 24 7 50 - 300 g pkgs. 53.00
62 Tarte au sucre 29 3 48 pies 49.30
43 Ipoh Coffee 20 1 16 - 500 g tins 46.00
28 Rössle Sauerkraut 12 7 25 - 825 g cans 45.60

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

SELECT *
FROM products
ORDER BY Price DESC
LIMIT 10,15;
Displaying records 1 - 10
ProductID ProductName SupplierID CategoryID Unit Price
63 Vegie-spread 7 2 15 - 625 g jars 43.90
27 Schoggi Schokolade 11 3 100 - 100 g pieces 43.90
8 Northwoods Cranberry Sauce 3 2 12 - 12 oz jars 40.00
17 Alice Mutton 7 6 20 - 1 kg tins 39.00
56 Gnocchi di nonna Alice 26 5 24 - 250 g pkgs. 38.00
12 Queso Manchego La Pastora 5 4 10 - 500 g pkgs. 38.00
69 Gudbrandsdalsost 15 4 10 kg pkg. 36.00
72 Mozzarella di Giovanni 14 4 24 - 200 g pkgs. 34.80
60 Camembert Pierrot 28 4 15 - 300 g rounds 34.00
64 Wimmers gute Semmelknödel 12 5 20 bags x 4 pieces 33.25

8. List all supplier countries in alphabetical order!

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

9. Find the cheapest product and Expensive Orders!

9.1 Cheapest Product

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

9.2 Expensive Product

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

10. Find the number of Supplier USA!

SELECT *
FROM suppliers
WHERE Country='USA'
4 records
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
3 Grandma Kelly’s Homestead Regina Murphy 707 Oxford Rd. Ann Arbor 48104 USA (313) 555-5735
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

11. Compute the total Quantity of orderitem!

SELECT SUM(Quantity)
FROM orderdetails
1 records
SUM(Quantity)
12743

12. Compute the average UnitPrice of all product!

SELECT AVG(Price)
FROM products
1 records
AVG(Price)
28.86636

13. Get all information about customer named Thomas Hardy!

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

14. List all customers from Spain or France!

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

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

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

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

SELECT o.OrderDate, p.ProductID, p.ProductName, p.Price
FROM orders o
JOIN orderdetails od
ON o.OrderID = od.OrderID
JOIN products p
ON od.ProductID = p.ProductID
WHERE Price NOT BETWEEN 50 AND 15000
Displaying records 1 - 10
OrderDate ProductID ProductName Price
1996-07-04 11 Queso Cabrales 21.00
1996-07-04 42 Singaporean Hokkien Fried Mee 14.00
1996-07-04 72 Mozzarella di Giovanni 34.80
1996-07-05 14 Tofu 23.25
1996-07-08 41 Jack’s New England Clam Chowder 9.65
1996-07-08 65 Louisiana Fiery Hot Pepper Sauce 21.05
1996-07-08 22 Gustaf’s Knäckebröd 21.00
1996-07-08 57 Ravioli Angelo 19.50
1996-07-08 65 Louisiana Fiery Hot Pepper Sauce 21.05
1996-07-09 33 Geitost 2.50

17. List all products between $10 and $20

SELECT *
FROM products
WHERE Price BETWEEN 10 AND 20
ORDER BY Price
Displaying records 1 - 10
ProductID ProductName SupplierID CategoryID Unit Price
3 Aniseed Syrup 1 2 12 - 550 ml bottles 10.00
21 Sir Rodney’s Scones 8 3 24 pkgs. x 4 pieces 10.00
74 Longlife Tofu 4 7 5 kg pkg. 10.00
46 Spegesild 21 8 4 - 450 g glasses 12.00
68 Scottish Longbreads 8 3 10 boxes x 8 pieces 12.50
31 Gorgonzola Telino 14 4 12 - 100 g pkgs 12.50
48 Chocolade 22 3 10 pkgs. 12.75
77 Original Frankfurter grüne Soße 12 2 12 boxes 13.00
58 Escargots de Bourgogne 27 8 24 pieces 13.25
34 Sasquatch Ale 16 1 24 - 12 oz bottles 14.00

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

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

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

SELECT *
FROM orders
WHERE 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

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

SELECT *
FROM suppliers
WHERE Country='USA' OR Country='UK' OR Country='Japan'
8 records
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

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

SELECT *
FROM products
WHERE Price NOT IN(10,20,30,40,50)
Displaying records 1 - 10
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

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
Rattlesnake Canyon Grocery New Orleans Cajun Delights USA USA
Rattlesnake Canyon Grocery Grandma Kelly’s Homestead USA USA
Morgenstern Gesundkost Plutzer Lebensmittelgroßmärkte AG Germany Germany
Berglunds snabbköp Svensk Sjöföda AB Sweden Sweden
Lehmanns Marktstand Heli Süßwaren GmbH & Co. KG Germany Germany
B’s Beverages Exotic Liquid UK UK
Blondel père et fils Aux joyeux ecclésiastiques France France
Die Wandernde Kuh Plutzer Lebensmittelgroßmärkte AG Germany Germany
Die Wandernde Kuh Plutzer Lebensmittelgroßmärkte AG Germany Germany

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

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

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

Tidak ada kolom nomor Fax, maka dianggap para pembeli tidak mempunyai nomor Fax.

25. List all customer 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
6 records
CustomerName AverageOrders
Que Delícia 1012.275
Königlich Essen 1039.375
Tortuga Restaurante 1067.350
Folk och fä HB 1078.475
Magazzini Alimentari Riuniti 1080.300
Chop-suey Chinese 1124.500

26. List total customers in each country.

SELECT Country, COUNT(CustomerName) TotalCustomer
FROM customers
GROUP BY Country
Displaying records 1 - 10
Country TotalCustomer
Argentina 3
Austria 2
Belgium 2
Brazil 9
Canada 3
Denmark 2
Finland 2
France 11
Germany 11
Ireland 1

27. Display results with easy to understand column headers.