Database System

System Quarry SQL

Kontak	\(\downarrow\)
Email	calvin.riswandi@gmail.com
Instagram	https://www.instagram.com/cvnopp_/
RPubs	https://rpubs.com/calvinriswandy/
Nama	Calvin Riswandi
NIM	20214920003

---

1 Connect MySQL

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)

poodle <- dbConnect(MariaDB(),
                   user = 'root',
                   password = '',
                   dbname = 'calvinbase',
                   host = 'localhost',
                   port = 3306)
knitr::opts_chunk$set(connection = "poodle")

dbWriteTable(poodle, "Customers", Customers, append=T)
dbWriteTable(poodle, "Categories", Categories, append=T)
dbWriteTable(poodle, "Employees", Employees, append=T)
dbWriteTable(poodle, "OrderDetails", OrderDetails, append=T)
dbWriteTable(poodle, "Orders", Orders, append=T)
dbWriteTable(poodle, "Products", Products, append=T)
dbWriteTable(poodle, "Shippers", Shippers, append=T)
dbWriteTable(poodle, "Suppliers", Suppliers, append=T)

2 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 ecclsiastiques	Guylne Nodier	203, Rue des Francs-Bourgeois	France	Paris
Aux joyeux ecclsiastiques	Guylne Nodier	203, Rue des Francs-Bourgeois	France	Paris
Aux joyeux ecclsiastiques	Guylne Nodier	203, Rue des Francs-Bourgeois	France	Paris
Aux joyeux ecclsiastiques	Guylne Nodier	203, Rue des Francs-Bourgeois	France	Paris
Aux joyeux ecclsiastiques	Guylne Nodier	203, Rue des Francs-Bourgeois	France	Paris
Bigfoot Breweries	Cheryl Saylor	3400 - 8th Avenue Suite 210	USA	Bend
Bigfoot Breweries	Cheryl Saylor	3400 - 8th Avenue Suite 210	USA	Bend
Bigfoot Breweries	Cheryl Saylor	3400 - 8th Avenue Suite 210	USA	Bend
Bigfoot Breweries	Cheryl Saylor	3400 - 8th Avenue Suite 210	USA	Bend
Bigfoot Breweries	Cheryl Saylor	3400 - 8th Avenue Suite 210	USA	Bend

3 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
Zaanse Snoepfabriek	Dirk Luchte	Verkoop Rijnweg 22	Netherlands	Zaandam
Zaanse Snoepfabriek	Dirk Luchte	Verkoop Rijnweg 22	Netherlands	Zaandam
Zaanse Snoepfabriek	Dirk Luchte	Verkoop Rijnweg 22	Netherlands	Zaandam
Zaanse Snoepfabriek	Dirk Luchte	Verkoop Rijnweg 22	Netherlands	Zaandam
Tokyo Traders	Yoshi Nagase	9-8 Sekimai Musashino-shi	Japan	Tokyo
Tokyo Traders	Yoshi Nagase	9-8 Sekimai Musashino-shi	Japan	Tokyo
Tokyo Traders	Yoshi Nagase	9-8 Sekimai Musashino-shi	Japan	Tokyo
Tokyo Traders	Yoshi Nagase	9-8 Sekimai Musashino-shi	Japan	Tokyo
Tokyo Traders	Yoshi Nagase	9-8 Sekimai Musashino-shi	Japan	Tokyo

4 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
Pavlova, Ltd.	Ian Devling	74 Rose St. Moonie Ponds	Australia	Melbourne
Pavlova, Ltd.	Ian Devling	74 Rose St. Moonie Ponds	Australia	Melbourne
Pavlova, Ltd.	Ian Devling	74 Rose St. Moonie Ponds	Australia	Melbourne
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
G’day, Mate	Wendy Mackenzie	170 Prince Edward Parade Hunter’s Hill	Australia	Sydney
G’day, Mate	Wendy Mackenzie	170 Prince Edward Parade Hunter’s Hill	Australia	Sydney
G’day, Mate	Wendy Mackenzie	170 Prince Edward Parade Hunter’s Hill	Australia	Sydney
G’day, Mate	Wendy Mackenzie	170 Prince Edward Parade Hunter’s Hill	Australia	Sydney

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

select SupplierName, ContactName, Address, Country, City
from suppliers
order by country DESC, city DESC

Displaying records 1 - 10

SupplierName	ContactName	Address	Country	City
New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	USA	New Orleans
New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	USA	New Orleans
New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	USA	New Orleans
New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	USA	New Orleans
New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	USA	New Orleans
New England Seafood Cannery	Robb Merchant	Order Processing Dept. 2100 Paul Revere Blvd.	USA	Boston
New England Seafood Cannery	Robb Merchant	Order Processing Dept. 2100 Paul Revere Blvd.	USA	Boston
New England Seafood Cannery	Robb Merchant	Order Processing Dept. 2100 Paul Revere Blvd.	USA	Boston
New England Seafood Cannery	Robb Merchant	Order Processing Dept. 2100 Paul Revere Blvd.	USA	Boston
New England Seafood Cannery	Robb Merchant	Order Processing Dept. 2100 Paul Revere Blvd.	USA	Boston

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

SELECT *,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	OrderDetailID	OrderID..7	ProductID	Quantity	Quantity..10
10398	71	2	1996-12-30	3	401	10398	55	120	120
10398	71	2	1996-12-30	3	401	10398	55	120	120
10398	71	2	1996-12-30	3	401	10398	55	120	120
10398	71	2	1996-12-30	3	401	10398	55	120	120
10398	71	2	1996-12-30	3	401	10398	55	120	120
10398	71	2	1996-12-30	3	401	10398	55	120	120
10398	71	2	1996-12-30	3	401	10398	55	120	120
10398	71	2	1996-12-30	3	401	10398	55	120	120
10398	71	2	1996-12-30	3	401	10398	55	120	120
10398	71	2	1996-12-30	3	401	10398	55	120	120

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

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
9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97
9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97
9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97
9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97
9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97
20	Sir Rodney’s Marmalade	8	3	30 gift boxes	81
20	Sir Rodney’s Marmalade	8	3	30 gift boxes	81
20	Sir Rodney’s Marmalade	8	3	30 gift boxes	81
20	Sir Rodney’s Marmalade	8	3	30 gift boxes	81
20	Sir Rodney’s Marmalade	8	3	30 gift boxes	81

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
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
Cte de Blaye	12 - 75 cl bottles	263.5

10 Find the number of Supplier USA!

SELECT *
FROM suppliers
WHERE Country='USA'

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

11 Compute the total Quantity of orderitem!

SELECT SUM(Quantity)
FROM orderdetails

1 records

SUM(Quantity)
63715

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'

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

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

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
1996-07-04	11	Queso Cabrales	21
1996-07-04	11	Queso Cabrales	21
1996-07-04	11	Queso Cabrales	21
1996-07-04	11	Queso Cabrales	21
1996-07-04	42	Singaporean Hokkien Fried Mee	14
1996-07-04	42	Singaporean Hokkien Fried Mee	14
1996-07-04	42	Singaporean Hokkien Fried Mee	14
1996-07-04	42	Singaporean Hokkien Fried Mee	14
1996-07-04	42	Singaporean Hokkien Fried Mee	14

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
74	Longlife Tofu	4	7	5 kg pkg.	10
3	Aniseed Syrup	1	2	12 - 550 ml bottles	10
21	Sir Rodney’s Scones	8	3	24 pkgs. x 4 pieces	10
21	Sir Rodney’s Scones	8	3	24 pkgs. x 4 pieces	10
21	Sir Rodney’s Scones	8	3	24 pkgs. x 4 pieces	10
21	Sir Rodney’s Scones	8	3	24 pkgs. x 4 pieces	10
3	Aniseed Syrup	1	2	12 - 550 ml bottles	10
3	Aniseed Syrup	1	2	12 - 550 ml bottles	10
3	Aniseed Syrup	1	2	12 - 550 ml bottles	10
74	Longlife Tofu	4	7	5 kg pkg.	10

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.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
24	Guaran Fantstica	10	1	12 - 355 ml cans	4.5
24	Guaran Fantstica	10	1	12 - 355 ml cans	4.5
24	Guaran Fantstica	10	1	12 - 355 ml cans	4.5
24	Guaran Fantstica	10	1	12 - 355 ml cans	4.5
24	Guaran Fantstica	10	1	12 - 355 ml cans	4.5

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'

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

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..4
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
Old World Delicatessen	New England Seafood Cannery	USA	USA
Old World Delicatessen	New England Seafood Cannery	USA	USA

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

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

24 List all suppliers that do have a fax number!

Tidak ada kolom nomor Fax, maka dianggap 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

0 records

CustomerName	AverageOrders

26 List total customers in each country.

SELECT Country, COUNT(CustomerName) TotalCustomer
FROM customers
GROUP BY 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