Basis Data

Ujian Tengah Semester

Kontak	: $\downarrow$
Email	ali.19arifin@gmail.com
Instagram	https://www.instagram.com/arifin.alicia/
RPubs	https://rpubs.com/aliciaarifin/
Nama	Alicia Arifin
NIM	20214920001
Prodi	Statistika, 2021

Connect to SQL

library(RMySQL)   
library(DBI)

bunbun_RMySQL <- dbConnect(MySQL(), 
                           user='bunbun',
                           password='bunbun123', 
                           dbname='bunbun', 
                           host='localhost')
bunbun_RMySQL

## <MySQLConnection:0,0>

dbListTables(bunbun_RMySQL)

## [1] "categories"   "customers"    "employees"    "orderdetails" "orders"      
## [6] "products"     "shippers"     "suppliers"

Relation Table of Factory Data Base

Soal

1

List all orders with customer information

select o.orderid, o.orderdate,  c.customername, c.contactname, c.address, c.city, c.postalcode, c.country from Orders O
inner join Customers C
on C.CustomerID= O.CustomerID

Displaying records 1 - 10
orderid	orderdate	customername	contactname	address	city	postalcode	country
10248	1996-07-04	Wilman Kala	Matti Karttunen	Keskuskatu 45	Helsinki	21240	Finland
10249	1996-07-05	Tradição Hipermercados	Anabela Domingues	Av. Inês de Castro, 414	São Paulo	05634-030	Brazil
10250	1996-07-08	Hanari Carnes	Mario Pontes	Rua do Paço, 67	Rio de Janeiro	05454-876	Brazil
10251	1996-07-08	Victuailles en stock	Mary Saveley	2, rue du Commerce	Lyon	69004	France
10252	1996-07-09	Suprêmes délices	Pascale Cartrain	Boulevard Tirou, 255	Charleroi	B-6000	Belgium
10253	1996-07-10	Hanari Carnes	Mario Pontes	Rua do Paço, 67	Rio de Janeiro	05454-876	Brazil
10254	1996-07-11	Chop-suey Chinese	Yang Wang	Hauptstr. 29	Bern	3012	Switzerland
10255	1996-07-12	Richter Supermarkt	Michael Holz	Grenzacherweg 237	Genève	1203	Switzerland
10256	1996-07-15	Wellington Importadora	Paula Parente	Rua do Mercado, 12	Resende	08737-363	Brazil
10257	1996-07-16	HILARIÓN-Abastos	Carlos Hernández	Carrera 22 con Ave. Carlos Soublette #8-35	San Cristóbal	5022	Venezuela

2

List all orders with product names, quantities, and prices

select od.orderid, p.productname, od.quantity, p.price from OrderDetails OD
left join Products P on P.ProductID = OD.ProductID
order by orderid

Displaying records 1 - 10
orderid	productname	quantity	price
10248	Queso Cabrales	12	21.00
10248	Singaporean Hokkien Fried Mee	10	14.00
10248	Mozzarella di Giovanni	5	34.80
10249	Tofu	9	23.25
10249	Manjimup Dried Apples	40	53.00
10250	Jack’s New England Clam Chowder	10	9.65
10250	Manjimup Dried Apples	35	53.00
10250	Louisiana Fiery Hot Pepper Sauce	15	21.05
10251	Gustaf’s Knäckebröd	6	21.00
10251	Ravioli Angelo	15	19.50

3

This will list all customers, whether they placed any order or not

select customername as all_customername from customers

Displaying records 1 - 10
all_customername
Alfreds Futterkiste
Ana Trujillo Emparedados y helados
Antonio Moreno Taquería
Around the Horn
Berglunds snabbköp
Blauer See Delikatessen
Blondel père et fils
Bólido Comidas preparadas
Bon app’
Bottom-Dollar Marketse

4

List customers that have not placed orders

select customername as customer_ajikorder  from customers
where customerid not in (select customerid from orders )

Displaying records 1 - 10
customer_ajikorder
Alfreds Futterkiste
Blauer See Delikatessen
Cactus Comidas para llevar
FISSA Fabrica Inter. Salchichas S.A.
France restauration
Great Lakes Food Market
La corne d’abondance
Laughing Bacchus Wine Cellars
Lazy K Kountry Store
Let’s Stop N Shop

notes : ajik = belum

5

List all products with order quantities greater than 80

select od.orderid,p.productname, od.quantity  from OrderDetails OD
left join Products P
on P.ProductID = OD.ProductID
where quantity > 80

3 records
orderid	productname	quantity
10286	Steeleye Stout	100
10398	Pâté chinois	120
10440	Sirop d’érable	90

6

which products were sold by the unit (i.e. quantity=1)?

select od.orderid, p.productname, p.unit, od.quantity from OrderDetails OD
left join Products P
on P.ProductID = OD.ProductID
where quantity = 1

3 records
orderid	productname	unit	quantity
10281	Teatime Chocolate Biscuits	10 boxes x 12 pieces	1
10259	Gravad lax	12 - 500 g pkgs.	1
10308	Gudbrandsdalsost	10 kg pkg.	1

7

List customers who placed orders that are larger than the average of each customer order

SQL Code {sql connection= bunbun_RMySQL, output.var = count }

select customers.CustomerName, count(orders.orderid) as total_ordered from customers
inner join orders on customers.CustomerID=orders.CustomerID
group by customername
having count(orders.orderid)
order by count(orders.orderid) desc

mean(count$total_ordered)

rata-rata orang memesan online sebanyak 2.64 per customer

select customers.CustomerName, count(orders.orderid) as total_ordered from customers
inner join orders on customers.CustomerID=orders.CustomerID
group by customername
having count(orders.orderid) >= 2.64
order by count(orders.orderid) desc

Displaying records 1 - 10
CustomerName	total_ordered
Ernst Handel	10
Rattlesnake Canyon Grocery	7
Wartian Herkku	7
QUICK-Stop	7
Split Rail Beer & Ale	6
Hungry Owl All-Night Grocers	6
LILA-Supermercado	5
Mère Paillarde	5
La maison d’Asie	5
Old World Delicatessen	4

8

FInd best selling products based on quantity

select products.ProductName, sum(orderdetails.Quantity) as best_products from orderdetails
inner join products on orderdetails.ProductID =products.ProductID
group by productname 
order by best_products desc
limit 1

1 records
ProductName	best_products
Gorgonzola Telino	458

9

FInd best selling products based on revenue

select products.ProductName, orderdetails.Quantity*products.Price as revenue from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Côte de Blaye	5270

10

FInd best selling products based on revenue for each country

how many country

select country  from customers
group by country

Displaying records 1 - 10
country
Argentina
Austria
Belgium
Brazil
Canada
Denmark
Finland
France
Germany
Ireland

terdapat 21 negara dari sisi customer.

Argentina

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='Argentina'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Tofu	279

Austria

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='Austria'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Côte de Blaye	5270

Belgium

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='Belgium'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Sir Rodney’s Marmalade	3240

Canada

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='Canada'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Côte de Blaye	12911.5

Denmark

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='Denmark'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Côte de Blaye	13175

Finland

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='Finland'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Ipoh Coffee	1150

Brazil

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='Brazil'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Côte de Blaye	10540

France

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='France'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Thüringer Rostbratwurst	4332.65

Germany

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='Germany'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Raclette Courdavault	3850

Ireland

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='Ireland'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Manjimup Dried Apples	2544

Italy

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='italy'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Gumbär Gummibärchen	936.9

Mexico

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='mexico'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Carnarvon Tigers	750

norway

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='norway'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Raclette Courdavault	660

poland

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='poland'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Gorgonzola Telino	375

portugal

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='portugal'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Gnocchi di nonna Alice	1064

spain

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='spain'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Boston Crab Meat	736

sweden

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='sweden'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Sir Rodney’s Marmalade	2268

switzerland

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='switzerland'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Camembert Pierrot	2040

UK

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='UK'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Camembert Pierrot	2720

USA

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='usa'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Côte de Blaye	5270

venezuela

select products.ProductName, orderdetails.Quantity*products.Price as revenue  from orderdetails
inner join products on orderdetails.ProductID=products.ProductID
inner join orders on orderdetails.OrderID=orders.OrderID
inner join customers on orders.CustomerID=customers.CustomerID
where country ='venezuela'
group by productname
order by revenue desc
limit 1

1 records
ProductName	revenue
Perth Pasties	2296

11

Find suppliers with products price less than 50$

select suppliername from suppliers
where exists( select productname from products where products.SupplierID = suppliers.SupplierID and price<50)

Displaying records 1 - 10
suppliername
Exotic Liquid
New Orleans Cajun Delights
Grandma Kelly’s Homestead
Tokyo Traders
Cooperativa de Quesos ‘Las Cabras’
Mayumi’s
Pavlova, Ltd.
Specialty Biscuits, Ltd.
PB Knäckebröd AB
Refrescos Americanas LTDA

12

find top 10 best employees based on their sales quantity

select employees.FirstName, sum(orderdetails.quantity) as top10sales from orders
inner join employees on orders.EmployeeID = employees.EmployeeID
inner join orderdetails on orderdetails.OrderID = orders.OrderID
group by firstname
order by top10sales desc

9 records
FirstName	top10sales
Margaret	3232
Nancy	1924
Janet	1725
Andrew	1315
Laura	1293
Michael	1094
Steven	778
Robert	733
Anne	649

13

find top 10 best supplier countries based on quantity

select suppliers.Country, sum(orderdetails.quantity) top10country_supplier from orders
inner join orderdetails on orders.OrderID = orderdetails.OrderID
inner join products on orderdetails.ProductID = products.ProductID
inner join suppliers on products.SupplierID = suppliers.SupplierID
group by country
order by top10country_supplier  desc
limit 10

Displaying records 1 - 10
Country	top10country_supplier
Australia	1610
USA	1581
France	1436
Germany	1339
Italy	1217
UK	1213
Canada	949
Norway	836
Japan	560
Sweden	465

14

find top 10 best customers countries based on quantity

select customers.Country, sum(orderdetails.quantity) top10country_sales from orders
inner join customers on orders.CustomerID = customers.CustomerID
inner join orderdetails on orderdetails.OrderID = orders.OrderID
group by country
order by top10country_sales  desc
limit 10

Displaying records 1 - 10
Country	top10country_sales
USA	2139
Germany	2015
Austria	1565
Brazil	1117
France	889
Canada	772
UK	698
Ireland	565
Venezuela	492
Sweden	399

15

FInd top 10 best selling products based on quantity in every year

overall

select products.ProductName, sum(orderdetails.Quantity) as top10_products from orderdetails
inner join products on orderdetails.ProductID =products.ProductID
group by productname 
order by top10_products desc
limit 10

Displaying records 1 - 10
ProductName	top10_products
Gorgonzola Telino	458
Camembert Pierrot	430
Steeleye Stout	369
Raclette Courdavault	346
Chang	341
Pavlova	338
Fløtemysost	336
Alice Mutton	331
Tarte au sucre	325
Geitost	316

tahun 1996

select products.ProductName, sum(orderdetails.Quantity) as top10_products1996 from orderdetails
inner join products on orderdetails.ProductID =products.ProductID
inner join orders on orderdetails.OrderID = orders.OrderID
where orderdate like '%1996%'
group by productname 
order by top10_products1996 desc
limit 10

Displaying records 1 - 10
ProductName	top10_products1996
Gorgonzola Telino	444
Camembert Pierrot	370
Steeleye Stout	274
Chartreuse verte	266
Fløtemysost	261
Mozzarella di Giovanni	260
Pavlova	252
Tarte au sucre	250
Alice Mutton	234
Raclette Courdavault	231

tahun 1997

select products.ProductName, sum(orderdetails.Quantity) as top10_products1997 from orderdetails
inner join products on orderdetails.ProductID =products.ProductID
inner join orders on orderdetails.OrderID = orders.OrderID
where orderdate like '%1997%'
group by productname 
order by top10_products1997 desc
limit 10

Displaying records 1 - 10
ProductName	top10_products1997
Gnocchi di nonna Alice	173
Tourtière	126
Geitost	119
Raclette Courdavault	115
Chang	115
Sirop d’érable	106
Vegie-spread	100
Côte de Blaye	99
Alice Mutton	97
Steeleye Stout	95

16

List all contacts, i.e., suppliers and customers

select customername, contactname, address, city, postalcode, country, orders.OrderID, orders.OrderDate, shippers.ShipperName, products.ProductName, orderdetails.Quantity, products.Unit, products.Price from customers 
inner join orders on customers.CustomerID=orders.CustomerID
inner join orderdetails on orders.OrderID=orderdetails.OrderID
inner join shippers on orders.ShipperID=shippers.ShipperID
inner join products on orderdetails.ProductID=products.ProductID

Displaying records 1 - 10
customername	contactname	address	city	postalcode	country	OrderID	OrderDate	ShipperName	ProductName	Quantity	Unit	Price
Blondel père et fils	Frédérique Citeaux	24, place Kléber	Strasbourg	67000	France	10265	1996-07-25	Speedy Express	Alice Mutton	30	20 - 1 kg tins	39.00
Blondel père et fils	Frédérique Citeaux	24, place Kléber	Strasbourg	67000	France	10265	1996-07-25	Speedy Express	Outback Lager	20	24 - 355 ml bottles	15.00
Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden	10278	1996-08-12	United Package	Gula Malacca	16	20 - 2 kg bags	19.45
Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden	10278	1996-08-12	United Package	Raclette Courdavault	15	5 kg pkg.	55.00
Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden	10278	1996-08-12	United Package	Vegie-spread	8	15 - 625 g jars	43.90
Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden	10278	1996-08-12	United Package	Röd Kaviar	25	24 - 150 g jars	15.00
Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden	10280	1996-08-14	Speedy Express	Guaraná Fantástica	12	12 - 355 ml cans	4.50
Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden	10280	1996-08-14	Speedy Express	Pâté chinois	20	24 boxes x 2 pies	24.00
Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden	10280	1996-08-14	Speedy Express	Rhönbräu Klosterbier	30	24 - 0.5 l bottles	7.75
B’s Beverages	Victoria Ashworth	Fauntleroy Circus	London	EC2 5NT	UK	10289	1996-08-26	Federal Shipping	Aniseed Syrup	30	12 - 550 ml bottles	10.00

Disconnect or close connection

DBI::dbDisconnect(bunbun_RMySQL)

Kontak	: \(\downarrow\)
Email	ali.19arifin@gmail.com
Instagram	https://www.instagram.com/arifin.alicia/
RPubs	https://rpubs.com/aliciaarifin/
Nama	Alicia Arifin
NIM	20214920001
Prodi	Statistika, 2021