Database System

MIDTERM Database System

Email             : natalieekaren@gmail.com
RPubs            : https://rpubs.com/karennatalie/
Jurusan          : Statistika
Address         : ARA Center, Matana University Tower
                         Jl. CBD Barat Kav, RT.1, Curug Sangereng, Kelapa Dua, Tangerang, Banten 15810.

---

1 Connect MYSQL to R

Untuk menjalankan Database dalam MySQL, kita aktifkan XAMPP terlebih dahulu, setelah itu, kita koneksikan MYSQL ke dalam R dengan cara di bawah ini.

library(RMySQL)

## Loading required package: DBI

library(DBI)

karen <- dbConnect(RMySQL::MySQL(),
                   dbname='factory_db',
                   user='root',
                   password='',
                   host='localhost',
                   port=3306)
knitr::opts_chunk$set(connection = "karen")

2 Select Some attributes of suppliers in alphabetical order!

SELECT SupplierID, SupplierName, Address, City, Country
  FROM Suppliers S
    ORDER BY S.SupplierName ASC;

Displaying records 1 - 10

SupplierID	SupplierName	Address	City	Country
18	Aux joyeux eccl?siastiques	203, Rue des Francs-Bourgeois	Paris	France
16	Bigfoot Breweries	3400 - 8th Avenue Suite 210	Bend	USA
5	Cooperativa de Quesos ‘Las Cabras’	Calle del Rosal 4	Oviedo	Spain
27	Escargots Nouveaux	22, rue H. Voiron	Montceau	France
1	Exotic Liquid	49 Gilbert St.	Londona	UK
29	For?ts d’?rables	148 rue Chasseur	Ste-Hyacinthe	Canada
14	Formaggi Fortini s.r.l.	Viale Dante, 75	Ravenna	Italy
24	G’day, Mate	170 Prince Edward Parade Hunter’s Hill	Sydney	Australia
28	Gai p?turage	Bat. B 3, rue des Alpes	Annecy	France
3	Grandma Kelly’s Homestead	707 Oxford Rd.	Ann Arbor	USA

Keterangan: Disini kita ingin memilih beberapa supplier ke dalam urutan abjad dengan fungsi SELECT dan masukkan Data apa yang ingin kita panggil. Disini saya ingin memanggil data yang berhubungan dengan Supplier yaitu SupplierID, SupplierName, Address, City dan Country. Dengan adanya data Address, City dan Country kita bisa mengetahui tempat supplier tinggal secara lengkap. Fungsi FROM untuk memanggil data utama. Fungsi ORDER BY untuk mengurutkan data pada data SupplierName dan ASC untuk mengurutkan dari kecil ke terbesar, karena disini ingin mengurutkan secara alphabet, maka dari A sampai Z.

3 Some attributes of suppliers in reverse alphabetical order!

SELECT SupplierID, SupplierName, Address, City, Country
  FROM Suppliers 
    ORDER BY Suppliername DESC;

Displaying records 1 - 10

SupplierID	SupplierName	Address	City	Country
22	Zaanse Snoepfabriek	Verkoop Rijnweg 22	Zaandam	Netherlands
4	Tokyo Traders	9-8 Sekimai Musashino-shi	Tokyo	Japan
17	Svensk Sj?f?da AB	Brovallav?gen 231	Stockholm	Sweden
8	Specialty Biscuits, Ltd.	29 King’s Way	Manchester	UK
10	Refrescos Americanas LTDA	Av. das Americanas 12.890	S?o Paulo	Brazil
12	Plutzer Lebensmittelgro?m?rkte AG	Bogenallee 51	Frankfurt	Germany
9	PB Kn?ckebr?d AB	Kaloadagatan 13	G?teborg	Sweden
7	Pavlova, Ltd.	74 Rose St. Moonie Ponds	Melbourne	Australia
26	Pasta Buttini s.r.l.	Via dei Gelsomini, 153	Salerno	Italy
15	Norske Meierier	Hatlevegen 5	Sandvika	Norway

Keterangan: Soal ke-2 mirip dengan soal ke-1, yang membedakan hanya di fungsi DESC karena fungsi tersebut untuk mengurutkan suatu data dari yang terbesar ke yang terkecil, seperti pada hasil chunk di atas, dari Z sampai A. Sifatnya dari besar ke yang terkecil.

4 Some attributes of suppliers ordered by country, then by city!

SELECT SupplierID, SupplierName,Country, City 
  FROM Suppliers 
    ORDER BY Country , City;

Displaying records 1 - 10

SupplierID	SupplierName	Country	City
7	Pavlova, Ltd.	Australia	Melbourne
24	G’day, Mate	Australia	Sydney
10	Refrescos Americanas LTDA	Brazil	S?o Paulo
25	Ma Maison	Canada	Montr?al
29	For?ts d’?rables	Canada	Ste-Hyacinthe
21	Lyngbysild	Denmark	Lyngby
23	Karkki Oy	Finland	Lappeenranta
28	Gai p?turage	France	Annecy
27	Escargots Nouveaux	France	Montceau
18	Aux joyeux eccl?siastiques	France	Paris

Keterangan: disini kita ingin melihat suppliers memesan berdassarkan negara lalu berdasarkan kota sesuai dengan hasil chunk di atas.

5 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

Keterangan: kita ingin memanggil semua data yang ada dalam Supplier dengan mengSELECT all on Suppliers dan mengurutkan abjad secara terbalik dengan menggunakan fungsi DESC.

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

SELECT M.*, MK.Quantity
  FROM Orders M
    RIGHT JOIN orderdetail MK
      ON M.OrderID = MK.OrderID
        ORDER BY MK.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

Keteranagn: kita ingin semua pesanan diurutkan berdasarkan jumlah total yang terbesar dengan memilih Quantity pada data Orders, setelah itu kita ingin mengembalikan semua record dari data ORDERDETAIL dan record yang cocok dari dataframe ORDER menggunakan fungsi RIGHT JOIN, setelah itu kita memilih OrderID dan mengurutkan data Quantity menggunakan kunci DESC agar data nya diurutkan dalam urutan menurun.

7 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

Keterangan: kita ingin mendata 10 barang termahal dalam data product dan diurutkan berdasarkan harga tertingginya. Fungsi LIMIT digunakan untuk mengambil data sesuai yang diinginkan, seperti permasalahan di atas, kita ingin mendata 10 barang termahal, maka LIMIT yang dimasukkan sebanyak 10 dan diurutkan menggunakan kunci DESC.

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

SELECT Productname, unit, price
  FROM products
    ORDER BY Price DESC
      LIMIT 10,6;

6 records

Productname	unit	price
Schoggi Schokolade	100 - 100 g pieces	43.9
Vegie-spread	15 - 625 g jars	43.9
Northwoods Cranberry Sauce	12 - 12 oz jars	40.0
Alice Mutton	20 - 1 kg tins	39.0
Queso Manchego La Pastora	10 - 500 g pkgs.	38.0
Gnocchi di nonna Alice	24 - 250 g pkgs.	38.0

Keterangan: pada permasalahan ini, kita ingin mendata produk termahal ke-10 hingga ke-15 yang diurutkan berdasarkan harga. Kita mengSELECT Nama Produk, Unit dan Harga nya dalam data Product setelah itu kita urutkan berdasarkan harga dengan TOP 6 baris (10 s.d 15) setelah TOP 10 baris produk termahal

9 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

Keterangan: kita ingin mendata semua negara supplier dalam urutan abjad dengan SELECT DISTINCT. DISTINCT digunakan bersamaan dengan SELECT untuk menghilangkan semua data yang double atau duplikat, setelah itu urutkan menggunakan kunci ASC.

10 Find the cheapest product and Expensive Orders!

10.1 The Cheapest Orders

SELECT ProductName, Unit, Price
  FROM products 
      ORDER BY Price ASC
        LIMIT 1;

1 records

ProductName	Unit	Price
Geitost	500 g	2.5

Keterangan: disini kita ingin mencari tahu pesanan termurah pada data Produk, dan hasilnya adalah Produk Geitost dengan unit 500g memiliki harga termurah yaitu 2,5.

10.2 The Expensive Order

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

Keterangan: disini kita ingin mencari tahu pesanan termahal pada data Produk, dan hasilnya adalah Produk C?te de Blaye dengan unit 12-75 cl bottles memiliki harga termahal yaitu 263,5.

11 Find the number of Supplier USA!

SELECT Country, COUNT(Country) AS 'supplier USA'
  FROM suppliers 
    WHERE Country='USA' ;

1 records

Country	supplier USA
USA	4

Keterangan: Untuk menemukan nomor supplier dari negara USA, kita perlu data Country dari Suppliers dan menggunakan fungsi WHERE untuk memfilter RECORD USA pada data suppliers.

12 Compute the total Quantity of orderitem!

SELECT SUM(Quantity) AS 'TotalQuantity'
  FROM orderdetail 

1 records

TotalQuantity
12743

Keterangan: kita perlu menjumlahkan seluruh Quantity yang ada pada data Orderdetail dan menghasilkan total quantity sebesar 12743.Jika ingin melihat secara rinci maka kita dapat mengSELECT nama produk serta SUM dari Quantity tiap produknya dari OrderDetail, setelah itu kita gabungkan dengan data dari Product dan menghasilkan output sebagai berikut.

SELECT ProductName, SUM(O.Quantity) Quantity
  FROM orderdetail O
    JOIN Products P
      ON O.ProductID = P.ProductID
      GROUP BY ProductName
        ORDER BY ProductName;

Displaying records 1 - 10

ProductName	Quantity
Alice Mutton	331
Aniseed Syrup	80
Boston Crab Meat	256
C?te de Blaye	239
Camembert Pierrot	430
Carnarvon Tigers	106
Chais	159
Chang	341
Chartreuse verte	266
Chef Anton’s Cajun Seasoning	107

13 Compute the average UnitPrice of all product!

SELECT AVG(Price) AS 'Average_UnitPrice'
  FROM products

1 records

Average_UnitPrice
28.86636

Keterangan: untuk semua rata-rata harga untuk unit produk sebesar 28.86636. Untuk melihat lebih rinci, kita bisa menggunakan cara seperti di bawah ini.

SELECT ProductName, Unit, AVG(Price) Average_UnitPrice
  FROM products
    GROUP BY ProductName
      ORDER BY ProductName;

Displaying records 1 - 10

ProductName	Unit	Average_UnitPrice
Alice Mutton	20 - 1 kg tins	39.0
Aniseed Syrup	12 - 550 ml bottles	10.0
Boston Crab Meat	24 - 4 oz tins	18.4
C?te de Blaye	12 - 75 cl bottles	263.5
Camembert Pierrot	15 - 300 g rounds	34.0
Carnarvon Tigers	16 kg pkg.	62.5
Chais	10 boxes x 20 bags	18.0
Chang	24 - 12 oz bottles	19.0
Chartreuse verte	750 cc per bottle	18.0
Chef Anton’s Cajun Seasoning	48 - 6 oz jars	22.0

14 Get all information about customer named Thomas Hardy!

SELECT DISTINCT*
  FROM ((customers M
    JOIN orders K
      ON M.CustomerID=K.CustomerID)
        JOIN orderdetail MK
          ON K.OrderID=MK.OrderID)
           JOIN products H
             ON MK.ProductID=H.ProductID
    WHERE ContactName='Thomas Hardy'
      ORDER BY OrderDate;

5 records

row_names	CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country	row_names	OrderID	CustomerID	EmployeeID	OrderDate	ShipperID	row_names	OrderDetailID	OrderID	ProductID	Quantity	row_names	ProductID	ProductName	SupplierID	CategoryID	Unit	Price
4	4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK	108	10355	4	6	1996-11-15	1	285	285	10355	24	25	24	24	Guaran? Fant?stica	10	1	12 - 355 ml cans	4.50
4	4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK	108	10355	4	6	1996-11-15	1	286	286	10355	57	25	57	57	Ravioli Angelo	26	5	24 - 250 g pkgs.	19.50
4	4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK	136	10383	4	8	1996-12-16	3	358	358	10383	13	20	13	13	Konbu	6	8	2 kg box	6.00
4	4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK	136	10383	4	8	1996-12-16	3	359	359	10383	50	15	50	50	Valkoinen suklaa	23	3	12 - 100 g bars	16.25
4	4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK	136	10383	4	8	1996-12-16	3	360	360	10383	56	20	56	56	Gnocchi di nonna Alice	26	5	24 - 250 g pkgs.	38.00

Keterangan: Untuk menemukan informasi tentang pelanggan bernama Thomas Hardy, kita hanya mengSELECT ALL INFORMATION dengan simbol * dari data Customers. Huruf M, K, MK, H, untuk membedakan data tersebut berasal, seperti M.CustomerID berasal dari data Customers M.

15 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

Keterangan: memilih customer dari negara SPain dan France dari data Customers dan menggunakan fungsi OR untuk menampilkan RECORD Spain dan France.

16 List all customers that are not from the USA!

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

Keterangan: menggunakan rumus NOT untuk menampilkan hasil RECORD yang mengecualikan tidak berasal dari USA

17 List all orders that not between $50 and $15000!

SELECT O.*,P.ProductID, P.ProductName, P.Price
  FROM orders O
    LEFT JOIN orderdetail OD
      ON O.OrderID = OD.OrderID
    LEFT JOIN products P
      ON OD.ProductID = P.ProductID
  WHERE P.Price 
    NOT BETWEEN 50 AND 15000;

Displaying records 1 - 10

row_names	OrderID	CustomerID	EmployeeID	OrderDate	ShipperID	ProductID	ProductName	Price
1	10248	90	5	1996-07-04	3	11	Queso Cabrales	21.00
1	10248	90	5	1996-07-04	3	42	Singaporean Hokkien Fried Mee	14.00
1	10248	90	5	1996-07-04	3	72	Mozzarella di Giovanni	34.80
2	10249	81	6	1996-07-05	1	14	Tofu	23.25
3	10250	34	4	1996-07-08	2	41	Jack’s New England Clam Chowder	9.65
3	10250	34	4	1996-07-08	2	65	Louisiana Fiery Hot Pepper Sauce	21.05
4	10251	84	3	1996-07-08	1	22	Gustaf’s Kn?ckebr?d	21.00
4	10251	84	3	1996-07-08	1	57	Ravioli Angelo	19.50
4	10251	84	3	1996-07-08	1	65	Louisiana Fiery Hot Pepper Sauce	21.05
5	10252	76	4	1996-07-09	2	33	Geitost	2.50

Keterangan: mendata semua orderan yang harganya tidak di antara 50 dan 15000 dollar dengan menggunakan fungsi NOT BETWEEN.

18 List all products between $10 and $20

SELECT DISTINCT *
  FROM products 
    WHERE Price 
     BETWEEN 10 AND 20;

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
3	3	Aniseed Syrup	1	2	12 - 550 ml bottles	10.00
15	15	Genen Shouyu	6	2	24 - 250 ml bottles	15.50
16	16	Pavlova	7	3	32 - 500 g boxes	17.45
21	21	Sir Rodney’s Scones	8	3	24 pkgs. x 4 pieces	10.00
25	25	NuNuCa Nu?-Nougat-Creme	11	3	20 - 450 g glasses	14.00
31	31	Gorgonzola Telino	14	4	12 - 100 g pkgs	12.50
34	34	Sasquatch Ale	16	1	24 - 12 oz bottles	14.00
35	35	Steeleye Stout	16	1	24 - 12 oz bottles	18.00

Keterangan: mendata semua produk yang harga nya di antara 10 dan 20 dollar dan tidak melebihi di atas 20 dollar maupun di bawah 10 dollar menggunakan fungsi BETWEEN.

19 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

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

Keterangan: mendata semua produk yang harganya tidak di antara 10 dan 100 dollar dan di urutkan dari yang terendah hingga harga tertinggi.

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

SELECT M.OrderID, M.OrderDate, sum(MK.Quantity) AS 'AmountSold'
  FROM orders M
    JOIN orderdetail MK
      ON M.OrderID = MK.OrderID
    GROUP BY M.OrderID
    HAVING M.OrderDate
    BETWEEN '1996-01-01' AND '1996-12-31'
  ORDER BY M.OrderDate;

Displaying records 1 - 10

OrderID	OrderDate	AmountSold
10248	1996-07-04	27
10249	1996-07-05	49
10250	1996-07-08	60
10251	1996-07-08	41
10252	1996-07-09	105
10253	1996-07-10	102
10254	1996-07-11	57
10255	1996-07-12	110
10256	1996-07-15	27
10257	1996-07-16	46

Keterangan: mendata daftar pesanan dan jumlah yang terjual habis antara tanggal 96/1/1 dan 96/12/31 dari Orderdate pada data Order dan orderdetail.

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

SELECT *
  FROM suppliers 
    WHERE Country 
      IN ('USA', 'UK', '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

Keterangan: mendapatkan informasi suppliers yang berasal dari negara USA, UK dan JAPAN dari Country dan di filter menggunakan fungsi IN.

22 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

Keterangan: mendata semua product yang harga nya tidak pas dengan harga 10, 20, 30, 40, dan 50 menggunakan fungsi NOT IN

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

SELECT CustomerName, SupplierName, C.Country, S.Country
  FROM Customers C
    JOIN Orders O
      ON C.CustomerID=O.CustomerID
        JOIN OrderDetail 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

Keterangan: mendata semua customer yang berasal dari country yang sama dengan suppliers.

24 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

row_names	ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	1	Chais	1	1	10 boxes x 20 bags	18
2	2	Chang	1	1	24 - 12 oz bottles	19
39	39	Chartreuse verte	18	1	750 cc per bottle	18

Keterangan: Untuk menemukan nama produk yang berawalan CHA atau CHAN kita menggunakan fungsi LIKE untuk mencari nya pada kolom ProductName dan tambahkan wildcard % untuk mewakili beberapa karakter.

25 List all suppliers that do have a fax number!

Dalam database factory_db, tidak ada kolom nomor fax pada data suppliers hanya ada SupplierID yang disertakan dalam bentuk integer, jadi tidak ada pemasok yang memiliki nomor fax.

26 List all customer with average orders between $1000 and $1200 !

SELECT CustomerName, AVG(TotalOrderAmount) AS AverageOrder
  FROM 
      ( 
       SELECT CustomerName, SUM(OD.Quantity*P.Price) AS TotalOrderAmount
        FROM customers C
          JOIN orders O
            ON C.CustomerID = O.CustomerID
          JOIN orderdetail OD
            ON O.OrderID = OD.OrderID
          JOIN products P
            ON OD.ProductID = P.ProductID
        GROUP BY OD.OrderID
        ) S
  GROUP BY S.CustomerName
    HAVING AverageOrder
      BETWEEN 1000 AND 1200
  ORDER BY AverageOrder ASC;

6 records

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

Keterangan: mendata semua customers dengan rata-rata orders antara 1000 dan 1200 dollar dari data Customer, Orderdetail dan produk. menggunakan fungsi HAVING karena permasalahan melibatkan jumlah rata-rata order antara 1000 dan 1200 dollar kemudian diurutkan menggunakan fungsi ASC.

27 List total customers in each country.

SELECT Country, COUNT(Country) AS 'TotalCustomer'
  FROM customers 
    GROUP BY Country 

Displaying records 1 - 10

Country	TotalCustomer
NA	0
Argentina	3
Austria	2
Belgium	2
Brazil	9
Canada	3
Denmark	2
Finland	2
France	10
Germany	11

Keterangan: mendata total customers di setiap negara.

28 List all orders with customer information!

SELECT *
  FROM customers M
    JOIN orders K
      ON M.CustomerID=K.CustomerID
       ORDER BY M.CustomerID;

Displaying records 1 - 10

row_names	CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country	row_names	OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
2	2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constituci?n 2222	M?xico D.F.	5021	Mexico	61	10308	2	7	1996-09-18	3
3	3	Antonio Moreno Taquer?a	Antonio Moreno	Mataderos 2312	M?xico D.F.	5023	Mexico	118	10365	3	3	1996-11-27	2
4	4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK	108	10355	4	6	1996-11-15	1
4	4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK	136	10383	4	8	1996-12-16	3
5	5	Berglunds snabbk?p	Christina Berglund	Berguvsv?gen 8	Lule? S-958 22	Sweden	NA	31	10278	5	8	1996-08-12	2
5	5	Berglunds snabbk?p	Christina Berglund	Berguvsv?gen 8	Lule? S-958 22	Sweden	NA	33	10280	5	2	1996-08-14	1
5	5	Berglunds snabbk?p	Christina Berglund	Berguvsv?gen 8	Lule? S-958 22	Sweden	NA	137	10384	5	3	1996-12-16	3
7	7	Blondel p?re et fils	Fr?d?rique Citeaux	24, place Kl?ber	Strasbourg	67000	France	18	10265	7	2	1996-07-25	1
7	7	Blondel p?re et fils	Fr?d?rique Citeaux	24, place Kl?ber	Strasbourg	67000	France	50	10297	7	5	1996-09-04	2
7	7	Blondel p?re et fils	Fr?d?rique Citeaux	24, place Kl?ber	Strasbourg	67000	France	113	10360	7	4	1996-11-22	3

Keterangan: mendata semua pesanan dengan informasi customers.

29 List all orders with product names, quantities, and prices!

SELECT DISTINCT O.OrderID, P.ProductName, OD.Quantity, P.Price
  FROM Orders O
    JOIN orderdetail OD
      ON OD.OrderID = OD.OrderID
       JOIN products P
          ON OD.ProductID = P.ProductID;

Displaying records 1 - 10

OrderID	ProductName	Quantity	Price
10248	Queso Cabrales	12	21
10249	Queso Cabrales	12	21
10250	Queso Cabrales	12	21
10251	Queso Cabrales	12	21
10252	Queso Cabrales	12	21
10253	Queso Cabrales	12	21
10254	Queso Cabrales	12	21
10255	Queso Cabrales	12	21
10256	Queso Cabrales	12	21
10257	Queso Cabrales	12	21

Keterangan: disini kita ingin mendata semua orderan dengan nama produk, quantity dan harga nya.

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

SELECT C.CustomerID, C.CustomerName, C.ContactName, O.OrderID
  FROM customers C
    JOIN orders O
      ON C.CustomerID = O.CustomerID
    ORDER BY C.CustomerID ASC;

Displaying records 1 - 10

CustomerID	CustomerName	ContactName	OrderID
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
7	Blondel p?re et fils	Fr?d?rique Citeaux	10265
7	Blondel p?re et fils	Fr?d?rique Citeaux	10297
7	Blondel p?re et fils	Fr?d?rique Citeaux	10360

31 List customers that have not placed orders!

SELECT *
  FROM customers 
    WHERE CustomerID NOT IN ( SELECT CustomerID
                                FROM orders  )
      ORDER BY 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

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

32 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

33 List products with order quantities greater than 80!

SELECT P.ProductID, P.ProductName, SUM(OD.Quantity) AS Quantity
  FROM Products P
    JOIN Orderdetail 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

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

SELECT P.ProductID, P.ProductName, OD.Quantity
  FROM Products P
    INNER JOIN OrderDetail 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

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

36 Find best selling products based on quantity!

SELECT P.ProductID, P.ProductName, P.Price, SUM(OD.Quantity) AS 'Quantity'
  FROM Products P
    JOIN Orderdetail 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

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

38 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 orderdetail 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
    ORDER 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

39 Find suppliers with a product price 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

40 Find 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 orderdetail 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

41 Find 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 orderdetail 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

42 Find 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 orderdetail 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

43 Find top 10 best selling products based on quantity in every year!

SELECT ProductName, date_format(OrderDate, '%Y') SalesYear, SUM(Quantity) Quantity
  FROM Orders M
    JOIN OrderDetail K
      ON M.OrderID=K.OrderID
       JOIN Products H
         ON H.ProductID=K.ProductID
    WHERE date_format(OrderDate, '%Y') 
  GROUP BY ProductName
      ORDER BY SalesYear, Quantity DESC
         LIMIT 10

Displaying records 1 - 10

ProductName	SalesYear	Quantity
Gorgonzola Telino	1996	458
Camembert Pierrot	1996	430
Steeleye Stout	1996	369
Raclette Courdavault	1996	346
Chang	1996	341
Pavlova	1996	338
Fl?temysost	1996	336
Alice Mutton	1996	331
Tarte au sucre	1996	325
Geitost	1996	316