Database System

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

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(RMySQL :: MySQL(),
                   user = 'root',
                   password = '',
                   dbname = 'calvinbase',
                   host = 'localhost',
                   port = 3306)

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

knitr::opts_chunk$set(connection = "poodle")

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

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

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

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
row_names	SupplierID	SupplierName	ContactName	Address	City	PostalCode	Country	Phone
2	2	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA	(100) 555-4822
19	19	New England Seafood Cannery	Robb Merchant	Order Processing Dept. 2100 Paul Revere Blvd.	Boston	2134	USA	(617) 555-3267
16	16	Bigfoot Breweries	Cheryl Saylor	3400 - 8th Avenue Suite 210	Bend	97101	USA	(503) 555-9931
3	3	Grandma Kelly’s Homestead	Regina Murphy	707 Oxford Rd.	Ann Arbor	48104	USA	(313) 555-5735
8	8	Specialty Biscuits, Ltd.	Peter Wilson	29 King’s Way	Manchester	M14 GSD	UK	(161) 555-4448
1	1	Exotic Liquid	Charlotte Cooper	49 Gilbert St.	Londona	EC1 4SD	UK	(171) 555-2222
17	17	Svensk Sj?f?da AB	Michael Bj?rn	Brovallav?gen 231	Stockholm	S-123 45	Sweden	08-123 45 67
9	9	PB Kn?ckebr?d AB	Lars Peterson	Kaloadagatan 13	G?teborg	S-345 67	Sweden	031-987 65 43
5	5	Cooperativa de Quesos ‘Las Cabras’	Antonio del Valle Saavedra	Calle del Rosal 4	Oviedo	33007	Spain	(98) 598 76 54
20	20	Leka Trading	Chandra Leka	471 Serangoon Loop, Suite #402	Singapore	512	Singapore	555-8787

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
row_names	OrderID	CustomerID	EmployeeID	OrderDate	ShipperID	Quantity
151	10398	71	2	1996-12-30	3	120
39	10286	63	8	1996-08-21	3	100
193	10440	71	4	1997-02-10	2	90
77	10324	71	9	1996-10-08	1	80
98	10345	63	2	1996-11-04	2	80
112	10359	72	5	1996-11-21	3	80
126	10373	37	4	1996-12-05	3	80
195	10442	20	3	1997-02-11	2	80
104	10351	20	1	1996-11-11	1	77
20	10267	25	4	1996-07-29	1	70

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

SELECT *
FROM products
ORDER BY Price DESC
LIMIT 10

Displaying records 1 - 10
row_names	ProductID	ProductName	SupplierID	CategoryID	Unit	Price
38	38	C?te de Blaye	18	1	12 - 75 cl bottles	263.50
29	29	Th?ringer Rostbratwurst	12	6	50 bags x 30 sausgs.	123.79
9	9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97.00
20	20	Sir Rodney’s Marmalade	8	3	30 gift boxes	81.00
18	18	Carnarvon Tigers	7	8	16 kg pkg.	62.50
59	59	Raclette Courdavault	28	4	5 kg pkg.	55.00
51	51	Manjimup Dried Apples	24	7	50 - 300 g pkgs.	53.00
62	62	Tarte au sucre	29	3	48 pies	49.30
43	43	Ipoh Coffee	20	1	16 - 500 g tins	46.00
28	28	R?ssle Sauerkraut	12	7	25 - 825 g cans	45.60

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

SELECT *
FROM products
ORDER BY Price DESC
LIMIT 6,9;

9 records
row_names	ProductID	ProductName	SupplierID	CategoryID	Unit	Price
51	51	Manjimup Dried Apples	24	7	50 - 300 g pkgs.	53.0
62	62	Tarte au sucre	29	3	48 pies	49.3
43	43	Ipoh Coffee	20	1	16 - 500 g tins	46.0
28	28	R?ssle Sauerkraut	12	7	25 - 825 g cans	45.6
27	27	Schoggi Schokolade	11	3	100 - 100 g pieces	43.9
63	63	Vegie-spread	7	2	15 - 625 g jars	43.9
8	8	Northwoods Cranberry Sauce	3	2	12 - 12 oz jars	40.0
17	17	Alice Mutton	7	6	20 - 1 kg tins	39.0
12	12	Queso Manchego La Pastora	5	4	10 - 500 g pkgs.	38.0

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

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

Find the number of Supplier USA!

SELECT *
FROM suppliers
WHERE Country='USA'

4 records
row_names	SupplierID	SupplierName	ContactName	Address	City	PostalCode	Country	Phone
2	2	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA	(100) 555-4822
3	3	Grandma Kelly’s Homestead	Regina Murphy	707 Oxford Rd.	Ann Arbor	48104	USA	(313) 555-5735
16	16	Bigfoot Breweries	Cheryl Saylor	3400 - 8th Avenue Suite 210	Bend	97101	USA	(503) 555-9931
19	19	New England Seafood Cannery	Robb Merchant	Order Processing Dept. 2100 Paul Revere Blvd.	Boston	2134	USA	(617) 555-3267

Compute the total Quantity of the ordered item!

SELECT SUM(Quantity)
FROM orderdetails

1 records
SUM(Quantity)
12743

Compute the average UnitPrice of all products!

SELECT AVG(Price)
FROM products

1 records
AVG(Price)
28.86636

Get all information about customer named Thomas Hardy!

SELECT *
FROM customers
WHERE ContactName='Thomas Hardy'

1 records
row_names	CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
4	4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK

List all customers from Spain or France!

SELECT *
FROM customers
WHERE Country='Spain' OR Country='France'

Displaying records 1 - 10
row_names	CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
7	7	Blondel p?re et fils	Fr?d?rique Citeaux	24, place Kl?ber	Strasbourg	67000	France
8	8	B?lido Comidas preparadas	Mart?n Sommer	C/ Araquil, 67	Madrid	28023	Spain
9	9	Bon app’	Laurence Lebihans	12, rue des Bouchers	Marseille	13008	France
18	18	Du monde entier	Janine Labrune	67, rue des Cinquante Otages	Nantes	44000	France
22	22	FISSA Fabrica Inter. Salchichas S.A.	Diego Roel	C/ Moralzarzal, 86	Madrid	28034	Spain
26	26	France restauration	Carine Schmitt	54, rue Royale	Nantes	44000	France
29	29	Galer?a del gastr?nomo	Eduardo Saavedra	Rambla de Catalu?a, 23	Barcelona	8022	Spain
30	30	Godos Cocina T?pica	Jos? Pedro Freyre	C/ Romero, 33	Sevilla	41101	Spain
40	40	La corne d’abondance	Daniel Tonini	67, avenue de l’Europe	Versailles	78000	France
41	41	La maison d’Asie	Annette Roulet	1 rue Alsace-Lorraine	Toulouse	31000	France

List all customers that are not from the USA!

SELECT *
FROM customers
WHERE Country!='USA'

Displaying records 1 - 10
row_names	CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constituci?n 2222	M?xico D.F.	5021	Mexico
3	3	Antonio Moreno Taquer?a	Antonio Moreno	Mataderos 2312	M?xico D.F.	5023	Mexico
4	4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
6	6	Blauer See Delikatessen	Hanna Moos	Forsterstr. 57	Mannheim	68306	Germany
7	7	Blondel p?re et fils	Fr?d?rique Citeaux	24, place Kl?ber	Strasbourg	67000	France
8	8	B?lido Comidas preparadas	Mart?n Sommer	C/ Araquil, 67	Madrid	28023	Spain
9	9	Bon app’	Laurence Lebihans	12, rue des Bouchers	Marseille	13008	France
10	10	Bottom-Dollar Marketse	Elizabeth Lincoln	23 Tsawassen Blvd.	Tsawassen	T2F 8M4	Canada
11	11	B’s Beverages	Victoria Ashworth	Fauntleroy Circus	London	EC2 5NT	UK

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

List all products between $10 and $20

SELECT *
FROM products
WHERE Price BETWEEN 10 AND 20
ORDER BY Price

Displaying records 1 - 10
row_names	ProductID	ProductName	SupplierID	CategoryID	Unit	Price
3	3	Aniseed Syrup	1	2	12 - 550 ml bottles	10.00
21	21	Sir Rodney’s Scones	8	3	24 pkgs. x 4 pieces	10.00
74	74	Longlife Tofu	4	7	5 kg pkg.	10.00
46	46	Spegesild	21	8	4 - 450 g glasses	12.00
31	31	Gorgonzola Telino	14	4	12 - 100 g pkgs	12.50
68	68	Scottish Longbreads	8	3	10 boxes x 8 pieces	12.50
48	48	Chocolade	22	3	10 pkgs.	12.75
77	77	Original Frankfurter gr?ne So?e	12	2	12 boxes	13.00
58	58	Escargots de Bourgogne	27	8	24 pieces	13.25
25	25	NuNuCa Nu?-Nougat-Creme	11	3	20 - 450 g glasses	14.00

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
row_names	ProductID	ProductName	SupplierID	CategoryID	Unit	Price
33	33	Geitost	15	4	500 g	2.50
24	24	Guaran? Fant?stica	10	1	12 - 355 ml cans	4.50
13	13	Konbu	6	8	2 kg box	6.00
52	52	Filo Mix	24	5	16 - 2 kg boxes	7.00
54	54	Tourti?re	25	6	16 pies	7.45
75	75	Rh?nbr?u Klosterbier	12	1	24 - 0.5 l bottles	7.75
23	23	Tunnbr?d	9	5	12 - 250 g pkgs.	9.00
19	19	Teatime Chocolate Biscuits	8	3	10 boxes x 12 pieces	9.20
45	45	R?gede sild	21	8	1k pkg.	9.50
47	47	Zaanse koeken	22	3	10 - 4 oz boxes	9.50

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
row_names	OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
1	10248	90	5	1996-07-04	3
2	10249	81	6	1996-07-05	1
3	10250	34	4	1996-07-08	2
4	10251	84	3	1996-07-08	1
5	10252	76	4	1996-07-09	2
6	10253	34	3	1996-07-10	2
7	10254	14	5	1996-07-11	2
8	10255	68	9	1996-07-12	3
9	10256	88	3	1996-07-15	2
10	10257	35	4	1996-07-16	3

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

SELECT *
FROM suppliers
WHERE Country='USA' OR Country='UK' OR Country='Japan'

8 records
row_names	SupplierID	SupplierName	ContactName	Address	City	PostalCode	Country	Phone
1	1	Exotic Liquid	Charlotte Cooper	49 Gilbert St.	Londona	EC1 4SD	UK	(171) 555-2222
2	2	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA	(100) 555-4822
3	3	Grandma Kelly’s Homestead	Regina Murphy	707 Oxford Rd.	Ann Arbor	48104	USA	(313) 555-5735
4	4	Tokyo Traders	Yoshi Nagase	9-8 Sekimai Musashino-shi	Tokyo	100	Japan	(03) 3555-5011
6	6	Mayumi’s	Mayumi Ohno	92 Setsuko Chuo-ku	Osaka	545	Japan	(06) 431-7877
8	8	Specialty Biscuits, Ltd.	Peter Wilson	29 King’s Way	Manchester	M14 GSD	UK	(161) 555-4448
16	16	Bigfoot Breweries	Cheryl Saylor	3400 - 8th Avenue Suite 210	Bend	97101	USA	(503) 555-9931
19	19	New England Seafood Cannery	Robb Merchant	Order Processing Dept. 2100 Paul Revere Blvd.	Boston	2134	USA	(617) 555-3267

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
row_names	ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	1	Chais	1	1	10 boxes x 20 bags	18.00
2	2	Chang	1	1	24 - 12 oz bottles	19.00
4	4	Chef Anton’s Cajun Seasoning	2	2	48 - 6 oz jars	22.00
5	5	Chef Anton’s Gumbo Mix	2	2	36 boxes	21.35
6	6	Grandma’s Boysenberry Spread	3	2	12 - 8 oz jars	25.00
9	9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97.00
10	10	Ikura	4	8	12 - 200 ml jars	31.00
11	11	Queso Cabrales	5	4	1 kg pkg.	21.00
12	12	Queso Manchego La Pastora	5	4	10 - 500 g pkgs.	38.00
13	13	Konbu	6	8	2 kg box	6.00

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
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
Rattlesnake Canyon Grocery	New England Seafood Cannery	USA	USA

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
row_names	ProductID	ProductName	SupplierID	CategoryID	Unit	Price
2	2	Chang	1	1	24 - 12 oz bottles	19

List all suppliers that do have a fax number!

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

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

List total customers in each country.

SELECT Country, COUNT(CustomerName) TotalCustomer
FROM customers
GROUP BY Country

Displaying records 1 - 10
Country	TotalCustomer
NA	2
Argentina	3
Austria	2
Belgium	2
Brazil	9
Canada	3
Denmark	2
Finland	2
France	10
Germany	11

Display results with easy to understand column headers.
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	row_names	CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
10248	90	90	Wilman Kala	Matti Karttunen	Keskuskatu 45	Helsinki	21240	Finland
10249	81	81	Tradi??o Hipermercados	Anabela Domingues	Av. In?s de Castro, 414	S?o Paulo	05634-030	Brazil
10250	34	34	Hanari Carnes	Mario Pontes	Rua do Pa?o, 67	Rio de Janeiro	05454-876	Brazil
10251	84	84	Victuailles en stock	Mary Saveley	2, rue du Commerce	Lyon	69004	France
10252	76	76	Supr?mes d?lices	Pascale Cartrain	Boulevard Tirou, 255	Charleroi	B-6000	Belgium
10253	34	34	Hanari Carnes	Mario Pontes	Rua do Pa?o, 67	Rio de Janeiro	05454-876	Brazil
10254	14	14	Chop-suey Chinese	Yang Wang	Hauptstr. 29	Bern	3012	Switzerland
10255	68	68	Richter Supermarkt	Michael Holz	Grenzacherweg 237	Gen?ve	1203	Switzerland
10256	88	88	Wellington Importadora	Paula Parente	Rua do Mercado, 12	Resende	08737-363	Brazil
10257	35	35	HILARI?N-Abastos	Carlos Hern?ndez	Carrera 22 con Ave. Carlos Soublette #8-35	San Crist?bal	5022	Venezuela

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	Queso Cabrales	12	21.00
10248	Singaporean Hokkien Fried Mee	10	14.00
10248	Mozzarella di Giovanni	5	34.80
10248	Tofu	9	23.25
10248	Manjimup Dried Apples	40	53.00
10248	Jack’s New England Clam Chowder	10	9.65
10248	Manjimup Dried Apples	35	53.00
10248	Louisiana Fiery Hot Pepper Sauce	15	21.05
10248	Gustaf’s Kn?ckebr?d	6	21.00
10248	Ravioli Angelo	15	19.50

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	Maria Anders	NA
2	Ana Trujillo Emparedados y helados	Ana Trujillo	10308
3	Antonio Moreno Taquer?a	Antonio Moreno	10365
4	Around the Horn	Thomas Hardy	10355
4	Around the Horn	Thomas Hardy	10383
5	Berglunds snabbk?p	Christina Berglund	10278
5	Berglunds snabbk?p	Christina Berglund	10280
5	Berglunds snabbk?p	Christina Berglund	10384
6	Blauer See Delikatessen	Hanna Moos	NA
7	Blondel p?re et fils	Fr?d?rique Citeaux	10265

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
row_names	CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
6	6	Blauer See Delikatessen	Hanna Moos	Forsterstr. 57	Mannheim	68306	Germany
12	12	Cactus Comidas para llevar	Patricio Simpson	Cerrito 333	Buenos Aires	1010	Argentina
22	22	FISSA Fabrica Inter. Salchichas S.A.	Diego Roel	C/ Moralzarzal, 86	Madrid	28034	Spain
26	26	France restauration	Carine Schmitt	54, rue Royale	Nantes	44000	France
32	32	Great Lakes Food Market	Howard Snyder	2732 Baker Blvd.	Eugene	97403	USA
40	40	La corne d’abondance	Daniel Tonini	67, avenue de l’Europe	Versailles	78000	France
42	42	Laughing Bacchus Wine Cellars	Yoshi Tannamuri	1900 Oak St.	Vancouver	V3F 2K1	Canada
43	43	Lazy K Kountry Store	John Steel	12 Orchestra Terrace	Walla Walla	99362	USA
45	45	Let’s Stop N Shop	Jaime Yorres	87 Polk St. Suite 5	San Francisco	94117	USA

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

SELECT s.ContactName, 'Supplier' Type
FROM suppliers s
UNION
SELECT c.ContactName, 'Customer' Type
FROM customers c
ORDER BY ContactName ASC;

Displaying records 1 - 10
ContactName	Type
Alejandra Camino	Customer
Alexander Feuer	Customer
Ana Trujillo	Customer
Anabela Domingues	Customer
Andr? Fonseca	Customer
Ann Devon	Customer
Anne Heikkonen	Supplier
Annette Roulet	Customer
Antonio del Valle Saavedra	Supplier
Antonio Moreno	Customer

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	159
2	Chang	341
4	Chef Anton’s Cajun Seasoning	107
5	Chef Anton’s Gumbo Mix	129
8	Northwoods Cranberry Sauce	140
10	Ikura	85
11	Queso Cabrales	182
13	Konbu	92
14	Tofu	152
16	Pavlova	338

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

3 records
ProductID	ProductName	Quantity
19	Teatime Chocolate Biscuits	1
37	Gravad lax	1
69	Gudbrandsdalsost	1

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
                                    )

7 records
CustomerID	CustomerName	TotalAmount
75	Split Rail Beer & Ale	5270.00
20	Ernst Handel	5270.00
59	Piccolo und mehr	13175.00
7	Blondel p?re et fils	4332.65
62	Queen Cozinha	10540.00
73	Simons bistro	13175.00
51	M?re Paillarde	12911.50

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	458

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	62976.5

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
NA	Sir Rodney’s Marmalade	2268.00
Argentina	Tofu	279.00
Austria	C?te de Blaye	18445.00
Belgium	Sir Rodney’s Marmalade	3240.00
Brazil	C?te de Blaye	10540.00
Canada	C?te de Blaye	12911.50
Denmark	C?te de Blaye	13175.00
Finland	Fl?temysost	1505.00
France	Th?ringer Rostbratwurst	4332.65
Germany	Raclette Courdavault	3850.00

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

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	3232
Nancy Davolio	1924
Janet Leverling	1725
Andrew Fuller	1315
Laura Callahan	1293
Michael Suyama	1094
Steven Buchanan	778
Robert King	733
Anne Dodsworth	649
Adam West	NA

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	1610
USA	1581
France	1436
Germany	1339
Italy	1217
UK	1213
Canada	949
Norway	836
Japan	560
Sweden	465

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	2139
Germany	2015
Austria	1565
Brazil	1117
France	838
Canada	772
UK	698
Ireland	565
Venezuela	492
Denmark	355

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	444
Camembert Pierrot	1996	370
Steeleye Stout	1996	274
Chartreuse verte	1996	266
Fl?temysost	1996	261
Mozzarella di Giovanni	1996	260
Pavlova	1996	252
Tarte au sucre	1996	250
Alice Mutton	1996	234
Raclette Courdavault	1996	231

Measure the average order of product names from each country and order it from max to min. Ukur rata-rata urutan nama produk dari setiap negara dan pesan dari maks hingga 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 ASC

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
NA	Camembert Pierrot	18.33333
France	Alice Mutton	21.03921
Brazil	Boston Crab Meat	21.72072

Compare the average order of product names from each country in the year 1996 vs 1997 ordering from max to min. Bandingkan rata-rata urutan nama produk dari setiap negara pada tahun 1996 vs 1997 pemesanan dari maks ke min. ue, Year

(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

Database System

System Quarry SQL

Calvin Riswandi

November 02, 2022

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