*Kontak **\(\downarrow\)*
Email
Instagram https://www.instagram.com/its_bangjeki/
RPubs https://rpubs.com/valensiusjimy/
Nama Valensius Jimy
NIM 20214920005

  Pada kesempatan kali ini, adapun tujuan dibuatnya project ini untuk memenuhi kewajiban atau tugas dalam agenda Ujian Tengah Semester dengan mata kuliah Database Systems. Penulis berharap dapat memaksimalkan semua potensi yang ada dengan baik, serta dapat memberikan yang terbaik guna mendapat nilai yang baik juga. Adapun ujian kali ini tentang bagaimana langkah atau cara memanipulasi data SQL dengan bantuan aplikasi R.

1 Connect SQL to R

  Langkah paling awal yang harus dilakukan adalah menyambungkan koneksi database SQL yang kita miliki dengan aplikasi R dengan langkah seperti berikut ini.

pacman::p_load(RMariaDB,
               RMySQL,
               RSQLite,
               DBI)
jekiw <- dbConnect(MariaDB(),
                   user = 'root',
                   password = '',
                   dbname = 'bang jeki',
                   host = 'localhost')
knitr::opts_chunk$set(connection = "jekiw")

2 Select Some attributes of suppliers in alphabetical order!

  Pada kasus pertama ini, diminta untuk memilih beberapa kolom dan isinya dari tabel Suppliers dan diurutkan secara A-Z atau dalam kata lain menggunakan fungsi ASC. Sebelum itu, saya ingin melihat terlebih dahulu pada tabel tersebut terdapat kolom apa saja.

dbListTables(jekiw)
## [1] "categories"   "customers"    "employees"    "orderdetails" "orders"      
## [6] "products"     "shippers"     "suppliers"
select * 
  from suppliers
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

Saya akan memilih beberapa kolom, seperti SupplierName, Address, City, Phone agar tampilan lebih mudah dilihat dan informasi yang didapat akan lebih cepat dibanding menampilkan semuanya.

select suppliername, address, city, phone
from suppliers
Displaying records 1 - 10
suppliername address city phone
Exotic Liquid 49 Gilbert St. Londona (171) 555-2222
New Orleans Cajun Delights P.O. Box 78934 New Orleans (100) 555-4822
Grandma Kelly’s Homestead 707 Oxford Rd. Ann Arbor (313) 555-5735
Tokyo Traders 9-8 Sekimai Musashino-shi Tokyo (03) 3555-5011
Cooperativa de Quesos ‘Las Cabras’ Calle del Rosal 4 Oviedo (98) 598 76 54
Mayumi’s 92 Setsuko Chuo-ku Osaka (06) 431-7877
Pavlova, Ltd. 74 Rose St. Moonie Ponds Melbourne (03) 444-2343
Specialty Biscuits, Ltd. 29 King’s Way Manchester (161) 555-4448
PB Knäckebröd AB Kaloadagatan 13 Göteborg 031-987 65 43
Refrescos Americanas LTDA Av. das Americanas 12.890 São Paulo (11) 555 4640

Dari hasil di atas sudah terlihat kolom yang ingin ditampilkan seperti apa saja, tapi di sini informasi tersebut belum diurutkan sesuai dengan yang diminta. Maka dari itu, saya akan mencoba untuk mengurutkan informasi pada tabel tersebut berdasarkan nama suppliernya.

select suppliername, address, city, phone
from suppliers
order by suppliername ASC
Displaying records 1 - 10
suppliername address city phone
Aux joyeux ecclésiastiques 203, Rue des Francs-Bourgeois Paris (1) 03.83.00.68
Bigfoot Breweries 3400 - 8th Avenue Suite 210 Bend (503) 555-9931
Cooperativa de Quesos ‘Las Cabras’ Calle del Rosal 4 Oviedo (98) 598 76 54
Escargots Nouveaux 22, rue H. Voiron Montceau 85.57.00.07
Exotic Liquid 49 Gilbert St. Londona (171) 555-2222
Forêts d’érables 148 rue Chasseur Ste-Hyacinthe (514) 555-2955
Formaggi Fortini s.r.l. Viale Dante, 75 Ravenna (0544) 60323
G’day, Mate 170 Prince Edward Parade Hunter’s Hill Sydney (02) 555-5914
Gai pâturage Bat. B 3, rue des Alpes Annecy 38.76.98.06
Grandma Kelly’s Homestead 707 Oxford Rd. Ann Arbor (313) 555-5735

Terlihat bahwa tabel tersebut sudah menampilkan informasi yang diurutkan berdasarkan nama supplier dengan kondisi A-Z.

2.1 Some attributes of suppliers in reverse alphabetical order!

  Lantas, bagaimana jika ingin mengurutkannya dari bawah ke atas atau secara abjad adalah Z-A. Di sini saya akan menggunakan fungsi DESC dengan langkah seperti berikut ini.

select suppliername, address, city, phone
from suppliers
order by suppliername DESC
Displaying records 1 - 10
suppliername address city phone
Zaanse Snoepfabriek Verkoop Rijnweg 22 Zaandam (12345) 1212
Tokyo Traders 9-8 Sekimai Musashino-shi Tokyo (03) 3555-5011
Svensk Sjöföda AB Brovallavägen 231 Stockholm 08-123 45 67
Specialty Biscuits, Ltd. 29 King’s Way Manchester (161) 555-4448
Refrescos Americanas LTDA Av. das Americanas 12.890 São Paulo (11) 555 4640
Plutzer Lebensmittelgroßmärkte AG Bogenallee 51 Frankfurt (069) 992755
PB Knäckebröd AB Kaloadagatan 13 Göteborg 031-987 65 43
Pavlova, Ltd. 74 Rose St. Moonie Ponds Melbourne (03) 444-2343
Pasta Buttini s.r.l. Via dei Gelsomini, 153 Salerno (089) 6547665
Norske Meierier Hatlevegen 5 Sandvika (0)2-953010

Maka, akan terlihat perbedaannya dengan tabel sebelumnnya yang menampilkan nama supplier dengan abjad paling awal terlebih dahulu.

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

select suppliername, city, country, phone
from suppliers
order by country, city
Displaying records 1 - 10
suppliername city country phone
Pavlova, Ltd. Melbourne Australia (03) 444-2343
G’day, Mate Sydney Australia (02) 555-5914
Refrescos Americanas LTDA São Paulo Brazil (11) 555 4640
Ma Maison Montréal Canada (514) 555-9022
Forêts d’érables Ste-Hyacinthe Canada (514) 555-2955
Lyngbysild Lyngby Denmark 43844108
Karkki Oy Lappeenranta Finland (953) 10956
Gai pâturage Annecy France 38.76.98.06
Escargots Nouveaux Montceau France 85.57.00.07
Aux joyeux ecclésiastiques Paris France (1) 03.83.00.68

3.1 Reverse Alphabetical

select suppliername, city, country, phone
from suppliers
order by country desc, city desc
Displaying records 1 - 10
suppliername city country phone
New Orleans Cajun Delights New Orleans USA (100) 555-4822
New England Seafood Cannery Boston USA (617) 555-3267
Bigfoot Breweries Bend USA (503) 555-9931
Grandma Kelly’s Homestead Ann Arbor USA (313) 555-5735
Specialty Biscuits, Ltd. Manchester UK (161) 555-4448
Exotic Liquid Londona UK (171) 555-2222
Svensk Sjöföda AB Stockholm Sweden 08-123 45 67
PB Knäckebröd AB Göteborg Sweden 031-987 65 43
Cooperativa de Quesos ‘Las Cabras’ Oviedo Spain (98) 598 76 54
Leka Trading Singapore Singapore 555-8787

4 All attributes of suppliers and reverse alphabetically order 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 M.ProductID, ProductName, Unit, (M.price*MK.quantity) as total_amount
from products M
join orderdetails MK
on M.ProductID = MK.ProductID
order by total_amount DESC
Displaying records 1 - 10
ProductID ProductName Unit total_amount
38 Côte de Blaye 12 - 75 cl bottles 13175.00
38 Côte de Blaye 12 - 75 cl bottles 13175.00
38 Côte de Blaye 12 - 75 cl bottles 12911.50
38 Côte de Blaye 12 - 75 cl bottles 10540.00
38 Côte de Blaye 12 - 75 cl bottles 5270.00
38 Côte de Blaye 12 - 75 cl bottles 5270.00
29 Thüringer Rostbratwurst 50 bags x 30 sausgs. 4332.65
59 Raclette Courdavault 5 kg pkg. 3850.00
59 Raclette Courdavault 5 kg pkg. 3850.00
63 Vegie-spread 15 - 625 g jars 3512.00 
select c.customername, sum(od.quantity) as quantity
from customers c
join orders o
on c.CustomerID = o.CustomerID
join orderdetails od
on o.OrderID = od.OrderID
group by customername 
order by quantity desc
limit 10
Displaying records 1 - 10
customername quantity
Ernst Handel 1418
QUICK-Stop 839
Save-a-lot Markets 775
Rattlesnake Canyon Grocery 573
Hungry Owl All-Night Grocers 565
Frankenversand 553
Mère Paillarde 422
Blondel père et fils 367
Bottom-Dollar Marketse 350
Seven Seas Imports 321

6 Get all about 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 productname, price
from products
order by price desc
limit 9, 6
6 records
productname price
Rössle Sauerkraut 45.6
Vegie-spread 43.9
Schoggi Schokolade 43.9
Northwoods Cranberry Sauce 40.0
Alice Mutton 39.0
Queso Manchego La Pastora 38.0

8 List all supplier countries in alphabetical order!

select distinct country
from suppliers
order by country asc
Displaying records 1 - 10
country
Australia
Brazil
Canada
Denmark
Finland
France
Germany
Italy
Japan
Netherlands

8.1 Reverse Alphabetical

select distinct country
from suppliers
order by country desc
Displaying records 1 - 10
country
USA
UK
Sweden
Spain
Singapore
Norway
Netherlands
Japan
Italy
Germany

9 Find the Cheapest and Expensive Products!

9.1 Cheapest Products

select productname, price
from products
order by price asc 
limit 1
1 records
productname price
Geitost 2.5

9.2 Expensive Products

select productname, price
from products
order by price desc
limit 1
1 records
productname price
Côte de Blaye 263.5

10 Find the number of Suppliers USA!

select country, count(country) as Total_Suppliers
from suppliers
where country = 'USA'
1 records
country Total_Suppliers
USA 4

11 Compute the total Quantity of the ordered item!

select sum(quantity) as Total_Quantity
from orderdetails
1 records
Total_Quantity
12743

12 Compute the average UnitPrice of all products!

select avg(price) as average_price
from products
1 records
average_price
28.86636

13 Get all information about a customer named Thomas Hardy!

select *
from customers J
join orders E
on J.customerID = E.customerID
join orderdetails K
on E.OrderID = K.OrderID
join products I
on K.ProductID = I.ProductID
where contactname = 'thomas hardy'
order by orderdate
5 records
CustomerID CustomerName ContactName Address City PostalCode Country OrderID CustomerID..9 EmployeeID OrderDate ShipperID OrderDetailID OrderID..14 ProductID Quantity ProductID..17 ProductName SupplierID CategoryID Unit Price
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK 10355 4 6 1996-11-15 1 285 10355 24 25 24 Guaraná Fantástica 10 1 12 - 355 ml cans 4.50
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK 10355 4 6 1996-11-15 1 286 10355 57 25 57 Ravioli Angelo 26 5 24 - 250 g pkgs. 19.50
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK 10383 4 8 1996-12-16 3 360 10383 56 20 56 Gnocchi di nonna Alice 26 5 24 - 250 g pkgs. 38.00
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK 10383 4 8 1996-12-16 3 358 10383 13 20 13 Konbu 6 8 2 kg box 6.00
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK 10383 4 8 1996-12-16 3 359 10383 50 15 50 Valkoinen suklaa 23 3 12 - 100 g bars 16.25

14 List all customers from Spain or France!

select customername, address, city, country
from customers
where country ='Spain' or country =  'France'
order by country asc
Displaying records 1 - 10
customername address city country
Blondel père et fils 24, place Kléber Strasbourg France
Victuailles en stock 2, rue du Commerce Lyon France
Spécialités du monde 25, rue Lauriston Paris France
Paris spécialités 265, boulevard Charonne Paris France
La maison d’Asie 1 rue Alsace-Lorraine Toulouse France
La corne d’abondance 67, avenue de l’Europe Versailles France
Vins et alcools Chevalier 59 rue de l’Abbaye Reims France
France restauration 54, rue Royale Nantes France
Folies gourmandes 184, chaussée de Tournai Lille France
Du monde entier 67, rue des Cinquante Otages Nantes France 
select customername, address, city, country
from customers
where country = 'sweden' or country = 'mexico'
order by country asc
7 records
customername address city country
Ana Trujillo Emparedados y helados Avda. de la Constitución 2222 México D.F. Mexico
Antonio Moreno Taquería Mataderos 2312 México D.F. Mexico
Centro comercial Moctezuma Sierras de Granada 9993 México D.F. Mexico
Pericles Comidas clásicas Calle Dr. Jorge Cash 321 México D.F. Mexico
Tortuga Restaurante Avda. Azteca 123 México D.F. Mexico
Berglunds snabbköp Berguvsvägen 8 Luleå Sweden
Folk och fä HB Åkergatan 24 Bräcke Sweden

15 List all customers that are not from the USA!

select * 
from customers
where not 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 are not between $50 and $15000!

select o.*, 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 p.Price
not between 50 and 15000
Displaying records 1 - 10
OrderID CustomerID EmployeeID OrderDate ShipperID productid productname price
10248 90 5 1996-07-04 3 11 Queso Cabrales 21.00
10248 90 5 1996-07-04 3 42 Singaporean Hokkien Fried Mee 14.00
10248 90 5 1996-07-04 3 72 Mozzarella di Giovanni 34.80
10249 81 6 1996-07-05 1 14 Tofu 23.25
10250 34 4 1996-07-08 2 41 Jack’s New England Clam Chowder 9.65
10250 34 4 1996-07-08 2 65 Louisiana Fiery Hot Pepper Sauce 21.05
10251 84 3 1996-07-08 1 22 Gustaf’s Knäckebröd 21.00
10251 84 3 1996-07-08 1 57 Ravioli Angelo 19.50
10251 84 3 1996-07-08 1 65 Louisiana Fiery Hot Pepper Sauce 21.05
10252 76 4 1996-07-09 2 33 Geitost 2.50

17 List all products between $10 and $20!

select distinct *
from products
where Price 
between 10 and 20 
order by price asc
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
31 Gorgonzola Telino 14 4 12 - 100 g pkgs 12.50
68 Scottish Longbreads 8 3 10 boxes x 8 pieces 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
25 NuNuCa Nuß-Nougat-Creme 11 3 20 - 450 g glasses 14.00

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

select distinct * 
from products
where price
not between 10 and 100
order by price asc
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 amounts sold between 1996 Jan 01 and 1996 Des 31

select o.orderdate, sum(od.quantity) as Jumlah_Terjual, (p.price*od.quantity) as Total_Penjualan
from orders o
join orderdetails od
on o.orderid = od.orderid
join products p 
on od.ProductID = p.ProductID
group by o.orderid
having o.OrderDate
between '1996-01-01' and '1996-12-31'
order by o.orderdate
Displaying records 1 - 10
orderdate Jumlah_Terjual Total_Penjualan
1996-07-04 27 252.00
1996-07-05 49 209.25
1996-07-08 41 126.00
1996-07-08 60 96.50
1996-07-09 105 3240.00
1996-07-10 102 250.00
1996-07-11 57 67.50
1996-07-12 110 380.00
1996-07-15 27 492.00
1996-07-16 46 1097.50

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

select *
from suppliers
where country
in ('USA', 'UK', '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 productname, unit, price
from products
where price
not in (10, 20, 30, 40, 50)
Displaying records 1 - 10
productname unit price
Chais 10 boxes x 20 bags 18.00
Chang 24 - 12 oz bottles 19.00
Chef Anton’s Cajun Seasoning 48 - 6 oz jars 22.00
Chef Anton’s Gumbo Mix 36 boxes 21.35
Grandma’s Boysenberry Spread 12 - 8 oz jars 25.00
Mishi Kobe Niku 18 - 500 g pkgs. 97.00
Ikura 12 - 200 ml jars 31.00
Queso Cabrales 1 kg pkg. 21.00
Queso Manchego La Pastora 10 - 500 g pkgs. 38.00
Konbu 2 kg box 6.00 
select productname, unit, price
from products
where price = 20
1 records
productname unit price
Maxilaku 24 - 50 g pkgs. 20

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

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

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

select *
from products
where ProductName
like 'cha_%' or 'chan_%'
3 records
ProductID ProductName SupplierID CategoryID Unit Price
1 Chais 1 1 10 boxes x 20 bags 18
2 Chang 1 1 24 - 12 oz bottles 19
39 Chartreuse verte 18 1 750 cc per bottle 18 
select *
from customers
where customername 
like 'al_%'
1 records
CustomerID CustomerName ContactName Address City PostalCode Country
1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

24 List all customers with average orders between $1000 and $1200

SELECT CustomerName, AVG(TotalAmount) Average_Orders
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
) avg
GROUP BY CustomerName
HAVING Average_Orders
BETWEEN 1000 AND 1200
ORDER BY Average_Orders desc
6 records
CustomerName Average_Orders
Chop-suey Chinese 1124.500
Magazzini Alimentari Riuniti 1080.300
Folk och fä HB 1078.475
Tortuga Restaurante 1067.350
Königlich Essen 1039.375
Que Delícia 1012.275

25 List the total customers in each country

select country, count(customername) as total_customer
from customers
group by country
Displaying records 1 - 10
country total_customer
Argentina 3
Austria 2
Belgium 2
Brazil 9
Canada 3
Denmark 2
Finland 2
France 11
Germany 11
Ireland 1

26 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

27 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 o.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 Mozzarella di Giovanni 5 34.80
10248 Singaporean Hokkien Fried Mee 10 14.00
10249 Tofu 9 23.25
10249 Manjimup Dried Apples 40 53.00
10250 Jack’s New England Clam Chowder 10 9.65
10250 Louisiana Fiery Hot Pepper Sauce 15 21.05
10250 Manjimup Dried Apples 35 53.00
10251 Gustaf’s Knäckebröd 6 21.00
10251 Louisiana Fiery Hot Pepper Sauce 20 21.05

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

select 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
customername contactname orderid
Alfreds Futterkiste Maria Anders NA
Ana Trujillo Emparedados y helados Ana Trujillo 10308
Antonio Moreno Taquería Antonio Moreno 10365
Around the Horn Thomas Hardy 10355
Around the Horn Thomas Hardy 10383
Berglunds snabbköp Christina Berglund 10280
Berglunds snabbköp Christina Berglund 10278
Berglunds snabbköp Christina Berglund 10384
Blauer See Delikatessen Hanna Moos NA
Blondel père et fils Frédérique Citeaux 10265

29 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 Maria Anders Obere Str. 57 Berlin 12209 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
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
32 Great Lakes Food Market Howard Snyder 2732 Baker Blvd. Eugene 97403 USA
40 La corne d’abondance Daniel Tonini 67, avenue de l’Europe Versailles 78000 France
42 Laughing Bacchus Wine Cellars Yoshi Tannamuri 1900 Oak St. Vancouver V3F 2K1 Canada
43 Lazy K Kountry Store John Steel 12 Orchestra Terrace Walla Walla 99362 USA
45 Let’s Stop N Shop Jaime Yorres 87 Polk St. Suite 5 San Francisco 94117 USA

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

select s.contactname, 'supplier' type
from suppliers s
union all
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

31 List products with order quantities greater than 80

select p.productname, sum(od.quantity) as 'quantity'
from products p
join orderdetails od
on p.ProductID = od.ProductID
group by p.ProductID
order by quantity
Displaying records 1 - 10
productname quantity
Laughing Lumberjack Lager 5
Røgede sild 15
Gustaf’s Knäckebröd 18
Mishi Kobe Niku 20
Genen Shouyu 25
Uncle Bob’s Organic Dried Pears 25
Queso Manchego La Pastora 27
Grandma’s Boysenberry Spread 36
Gravad lax 39
Röd Kaviar 45 
select p.productname, sum(od.quantity) as 'quantity'
from products p
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
productname quantity
Grandma’s Boysenberry Spread 36
Uncle Bob’s Organic Dried Pears 25
Mishi Kobe Niku 20
Queso Manchego La Pastora 27
Genen Shouyu 25
Gustaf’s Knäckebröd 18
NuNuCa Nuß-Nougat-Creme 71
Mascarpone Fabioli 52
Gravad lax 39
Singaporean Hokkien Fried Mee 77

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

select p.productname, od.quantity
from products p
join orderdetails od
on p.productid = od.ProductID
where od.Quantity = 1
order by p.ProductID
3 records
productname quantity
Teatime Chocolate Biscuits 1
Gravad lax 1
Gudbrandsdalsost 1 
select p.productname, od.quantity
from products p
join orderdetails od
on p.productid = od.productid 
where od.quantity <= 10
order by p.productid
Displaying records 1 - 10
productname quantity
Chais 10
Chang 7
Chang 10
Chef Anton’s Cajun Seasoning 10
Grandma’s Boysenberry Spread 6
Uncle Bob’s Organic Dried Pears 10
Queso Cabrales 6
Queso Cabrales 6
Konbu 2
Konbu 10

33 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 'Total_Amount'
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 Total_Amount
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

34 Find best-selling products based on quantity!

select 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
productname price quantity
Gorgonzola Telino 12.5 458

35 Find best-selling products based on revenue!

select productname, p.price, od.quantity, sum(od.quantity*p.price) 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

36 Find best-selling products based on revenue for each country!

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
Displaying records 1 - 10
productname country revenue
Tofu Argentina 279.00
Sir Rodney’s Scones Argentina 120.00
Côte de Blaye Austria 18445.00
Raclette Courdavault Austria 3850.00
Vegie-spread Austria 2853.50
Chef Anton’s Gumbo Mix Austria 2070.95
Gudbrandsdalsost Austria 1800.00
Alice Mutton Austria 1755.00
Thüringer Rostbratwurst Austria 1733.06
Nord-Ost Matjeshering Austria 1553.40

37 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

38 Find the top 10 best employees based on their sales quantity!

select concat(firstname, ' ', lastname) as 'name', sum(quantity) as 'quantity'
from employees e 
join orders o 
on e.employeeid = o.employeeid 
join orderdetails od
on o.orderid = od.orderid
group by e.EmployeeID
order by quantity desc
limit 10
9 records
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

39 Find the top 10 best supplier countries based on quantity!

select s.country, sum(quantity) as 'quantity'
from suppliers s 
join products p
on s.supplierid = p.supplierid 
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

40 Find the top 10 best customer countries based on quantity!

select c.country, sum(quantity) as 'quantity'
from customers c
join orders o
on c.customerid = o.customerid 
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 889
Canada 772
UK 698
Ireland 565
Venezuela 492
Sweden 399

41 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

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

SELECT Country, ProductName, AVG(Revenue) AS 'Revenue' 
FROM(SELECT P.ProductName, C.Country, 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 C. Country, P.ProductName
   ) S
    GROUP BY Country
    Order by revenue desc
Displaying records 1 - 10
Country ProductName Revenue
Denmark Côte de Blaye 1525.9561
Belgium Alice Mutton 1341.8833
Canada Alice Mutton 1222.5977
Austria Alice Mutton 1198.8261
USA Alice Mutton 1024.3556
Brazil Boston Crab Meat 874.7403
Ireland Camembert Pierrot 756.5668
Switzerland Camembert Pierrot 719.9773
France Alice Mutton 713.8204
Germany Alice Mutton 681.7604

43 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 'average'
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 year, productname
having year = 1996
order by average desc
limit 10)

union

(select p.productname, c.country, year(o.orderdate) as 'year', avg(od.quantity*p.price) as 'average'
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 year, productname
having year = 1997
order by average desc
limit 10)
Displaying records 1 - 10
productname country year average
Côte de Blaye USA 1996 7378.000
Northwoods Cranberry Sauce USA 1996 2800.000
Thüringer Rostbratwurst Venezuela 1996 2175.167
Sir Rodney’s Marmalade Belgium 1996 1717.200
Manjimup Dried Apples Brazil 1996 1439.833
Carnarvon Tigers Mexico 1996 1325.000
Raclette Courdavault Switzerland 1996 1270.500
Gumbär Gummibärchen Venezuela 1996 1249.200
Vegie-spread Sweden 1996 1196.275
Camembert Pierrot Belgium 1996 1143.636