DATABASE SYSTEMS

MIDTERM

Email             : brigita.melantika@student.matanauniversity.ac.id
RPubs            : https://rpubs.com/brigitatiaraem/
Jurusan          : Statistika
Address         : ARA Center, Matana University Tower
                         Jl. CBD Barat Kav, RT.1, Curug Sangereng, Kelapa Dua, Tangerang, Banten 15810.

---

1 Pilih Beberapa atribut pemasok dalam urutan abjad!

# Mengatur koneksi dan simpan ke ruang kerja

library(RMySQL)

## Loading required package: DBI

library(DBI)
library(DT)
brigita <- dbConnect(RMySQL::MySQL(),
                  dbname='mysql',
                  username='root',
                  password='', 
                  host='localhost',
                  port=3306)
knitr::opts_chunk$set(connection = "brigita") # mengatur koneksi di Rmarkdown chunk

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
18	Aux joyeux ecclésiastiques	203, Rue des Francs-Bourgeois	Paris	France
18	Aux joyeux ecclésiastiques	203, Rue des Francs-Bourgeois	Paris	France
16	Bigfoot Breweries	3400 - 8th Avenue Suite 210	Bend	USA
16	Bigfoot Breweries	3400 - 8th Avenue Suite 210	Bend	USA
16	Bigfoot Breweries	3400 - 8th Avenue Suite 210	Bend	USA
5	Cooperativa de Quesos ‘Las Cabras’	Calle del Rosal 4	Oviedo	Spain
5	Cooperativa de Quesos ‘Las Cabras’	Calle del Rosal 4	Oviedo	Spain
5	Cooperativa de Quesos ‘Las Cabras’	Calle del Rosal 4	Oviedo	Spain
27	Escargots Nouveaux	22, rue H. Voiron	Montceau	France

2 Beberapa atribut pemasok dalam urutan abjad terbalik!

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

Displaying records 1 - 10

SupplierID	SupplierName	Address	City	Country
22	Zaanse Snoepfabriek	Verkoop Rijnweg 22	Zaandam	Netherlands
22	Zaanse Snoepfabriek	Verkoop Rijnweg 22	Zaandam	Netherlands
22	Zaanse Snoepfabriek	Verkoop Rijnweg 22	Zaandam	Netherlands
4	Tokyo Traders	9-8 Sekimai Musashino-shi	Tokyo	Japan
4	Tokyo Traders	9-8 Sekimai Musashino-shi	Tokyo	Japan
4	Tokyo Traders	9-8 Sekimai Musashino-shi	Tokyo	Japan
17	Svensk Sjöföda AB	Brovallavägen 231	Stockholm	Sweden
17	Svensk Sjöföda AB	Brovallavägen 231	Stockholm	Sweden
17	Svensk Sjöföda AB	Brovallavägen 231	Stockholm	Sweden
8	Specialty Biscuits, Ltd.	29 King’s Way	Manchester	UK

3 Beberapa atribut pemasok dipesan berdasarkan negara, lalu berdasarkan kota!

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

Displaying records 1 - 10

SupplierID	SupplierName	Address	City	Country
7	Pavlova, Ltd.	74 Rose St. Moonie Ponds	Melbourne	Australia
7	Pavlova, Ltd.	74 Rose St. Moonie Ponds	Melbourne	Australia
7	Pavlova, Ltd.	74 Rose St. Moonie Ponds	Melbourne	Australia
24	G’day, Mate	170 Prince Edward Parade Hunter’s Hill	Sydney	Australia
24	G’day, Mate	170 Prince Edward Parade Hunter’s Hill	Sydney	Australia
24	G’day, Mate	170 Prince Edward Parade Hunter’s Hill	Sydney	Australia
10	Refrescos Americanas LTDA	Av. das Americanas 12.890	São Paulo	Brazil
10	Refrescos Americanas LTDA	Av. das Americanas 12.890	São Paulo	Brazil
10	Refrescos Americanas LTDA	Av. das Americanas 12.890	São Paulo	Brazil
25	Ma Maison	2960 Rue St. Laurent	Montréal	Canada

4 Semua atribut pemasok dan urutan abjad terbalik menurut negara, lalu menurut kota!

SELECT S.*
  FROM Suppliers S
     ORDER BY S.Country DESC, S.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
2	New Orleans Cajun Delights	Shelley Burke	P.O. Box 78934	New Orleans	70117	USA	(100) 555-4822
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
19	New England Seafood Cannery	Robb Merchant	Order Processing Dept. 2100 Paul Revere Blvd.	Boston	2134	USA	(617) 555-3267
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
16	Bigfoot Breweries	Cheryl Saylor	3400 - 8th Avenue Suite 210	Bend	97101	USA	(503) 555-9931
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

5 Semua pesanan, diurutkan berdasarkan jumlah total, yang terbesar dulu!

SELECT O.*, OD.Quantity
  FROM Orders O
    RIGHT JOIN orderdetails OD
      ON O.OrderID = OD.OrderID
        ORDER BY OD.Quantity DESC;

Displaying records 1 - 10

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID	Quantity
10398	71	2	1996-12-30	3	120
10398	71	2	1996-12-30	3	120
10398	71	2	1996-12-30	3	120
10286	63	8	1996-08-21	3	100
10286	63	8	1996-08-21	3	100
10286	63	8	1996-08-21	3	100
10440	71	4	1997-02-10	2	90
10440	71	4	1997-02-10	2	90
10440	71	4	1997-02-10	2	90
10345	63	2	1996-11-04	2	80

6 Dapatkan semua kecuali 10 produk paling mahal yang diurutkan berdasarkan harga!

SELECT P.*
  FROM products P
    ORDER BY P.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
38	Côte de Blaye	18	1	12 - 75 cl bottles	263.50
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
29	Thüringer Rostbratwurst	12	6	50 bags x 30 sausgs.	123.79
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
9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97.00
9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97.00
20	Sir Rodney’s Marmalade	8	3	30 gift boxes	81.00

7 Dapatkan produk termahal ke-10 hingga ke-15 yang diurutkan berdasarkan harga!

SELECT P.*
  FROM products P
    ORDER BY P.Price DESC
      LIMIT 6 OFFSET 9;

6 records

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
20	Sir Rodney’s Marmalade	8	3	30 gift boxes	81.0
20	Sir Rodney’s Marmalade	8	3	30 gift boxes	81.0
20	Sir Rodney’s Marmalade	8	3	30 gift boxes	81.0
18	Carnarvon Tigers	7	8	16 kg pkg.	62.5
18	Carnarvon Tigers	7	8	16 kg pkg.	62.5
18	Carnarvon Tigers	7	8	16 kg pkg.	62.5

8 Daftar semua negara pemasok dalam urutan abjad!

SELECT DISTINCT Country
  FROM suppliers S
    ORDER BY S.Country ASC;

Displaying records 1 - 10

Country
Australia
Brazil
Canada
Denmark
Finland
France
Germany
Italy
Japan
Netherlands

9 Temukan produk termurah dan Pesanan Mahal!

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

1 records

ProductName	Unit	Price
Geitost	500 g	2.5

SELECT P.ProductName, P.Unit, P.Price
  FROM products P
      ORDER BY P.Price DESC
        LIMIT 1;

1 records

ProductName	Unit	Price
Côte de Blaye	12 - 75 cl bottles	263.5

10 Temukan nomor Supplier USA!

SELECT S.Country, COUNT(S.Country) AS 'Total_Supplier'
  FROM suppliers S
    WHERE S.Country='USA' ;

1 records

Country	Total_Supplier
USA	12

11 Hitung jumlah total orderitem!

SELECT SUM(OD.Quantity) AS 'TotalQuantity'
  FROM orderdetails OD

1 records

TotalQuantity
38229

12 Hitung Harga Satuan rata-rata dari semua produk!

SELECT AVG(P.Price) AS 'Average_UnitPrice'
  FROM products P

1 records

Average_UnitPrice
28.86636

13 Dapatkan semua informasi tentang pelanggan bernama Thomas Hardy!

SELECT C.*
  FROM customers C
    WHERE ContactName='Thomas Hardy';

3 records

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

14 Daftar semua pelanggan dari Spanyol atau Prancis!

SELECT C.*
  FROM customers C
    WHERE Country="Spain" OR Country="France";

Displaying records 1 - 10

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
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
18	Du monde entier	Janine Labrune	67, rue des Cinquante Otages	Nantes	44000	France
22	FISSA Fabrica Inter. Salchichas S.A.	Diego Roel	C/ Moralzarzal, 86	Madrid	28034	Spain
23	Folies gourmandes	Martine Rancé	184, chaussée de Tournai	Lille	59000	France
26	France restauration	Carine Schmitt	54, rue Royale	Nantes	44000	France
29	Galería del gastrónomo	Eduardo Saavedra	Rambla de Cataluña, 23	Barcelona	8022	Spain
30	Godos Cocina Típica	José Pedro Freyre	C/ Romero, 33	Sevilla	41101	Spain
40	La corne d’abondance	Daniel Tonini	67, avenue de l’Europe	Versailles	78000	France

15 Daftar semua pelanggan yang bukan dari AS!

SELECT C.*
  FROM customers C
    WHERE NOT Country="USA"

Displaying records 1 - 10

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Alfred Schmidt	Obere Str. 57	Frankfurt	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 Daftar semua pesanan yang tidak antara $50 dan $15000!

SELECT O.*,P.ProductID, P.ProductName, P.Price
  FROM orders O
    LEFT JOIN orderdetails 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

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID	ProductID	ProductName	Price
10248	90	5	1996-07-04	3	11	Queso Cabrales	21.00
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
10251	84	3	1996-07-08	1	22	Gustaf’s Knäckebröd	21.00
10252	76	4	1996-07-09	2	33	Geitost	2.50
10253	34	3	1996-07-10	2	31	Gorgonzola Telino	12.50
10253	34	3	1996-07-10	2	39	Chartreuse verte	18.00
10254	14	5	1996-07-11	2	24	Guaraná Fantástica	4.50
10255	68	9	1996-07-12	3	2	Chang	19.00
10255	68	9	1996-07-12	3	16	Pavlova	17.45

17 Daftar semua produk antara $10 dan $20

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

Displaying records 1 - 10

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	Chais	1	1	10 boxes x 20 bags	18.00
2	Chang	1	1	24 - 12 oz bottles	19.00
3	Aniseed Syrup	1	2	12 - 550 ml bottles	10.00
15	Genen Shouyu	6	2	24 - 250 ml bottles	15.50
16	Pavlova	7	3	32 - 500 g boxes	17.45
21	Sir Rodney’s Scones	8	3	24 pkgs. x 4 pieces	10.00
25	NuNuCa Nuß-Nougat-Creme	11	3	20 - 450 g glasses	14.00
31	Gorgonzola Telino	14	4	12 - 100 g pkgs	12.50
34	Sasquatch Ale	16	1	24 - 12 oz bottles	14.00
35	Steeleye Stout	16	1	24 - 12 oz bottles	18.00

18 Daftar semua produk tidak antara $10 dan $100 diurutkan berdasarkan harga!

SELECT DISTINCT P.*
  FROM products P
    WHERE P.Price 
     NOT BETWEEN 10 AND 100
ORDER BY P.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 Dapatkan daftar pesanan dan jumlah yang terjual antara 01 Jan 1996 dan 31 Des 1996!

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

Displaying records 1 - 10

OrderID	OrderDate	AmountSold
10248	1996-07-04	81
10249	1996-07-05	147
10250	1996-07-08	180
10251	1996-07-08	123
10252	1996-07-09	315
10253	1996-07-10	306
10254	1996-07-11	171
10255	1996-07-12	330
10256	1996-07-15	81
10257	1996-07-16	138

20 Daftar semua pemasok dari AS, Inggris, ATAU Jepang!

SELECT S.*
  FROM suppliers S
    WHERE S.Country 
      IN ('USA', 'UK', 'JAPAN');

Displaying records 1 - 10

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

21 Daftar semua produk yang tidak persis $10, $20, $30, $40, atau $50!

SELECT P.*
  FROM products P
    WHERE P.Price
      NOT IN (10, 20, 30, 40, 50);

Displaying records 1 - 10

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

22 Daftar semua pelanggan yang berasal dari negara yang sama dengan pemasok!

SELECT C.CustomerName, S.SupplierName, C.Country AS 'CustomerCountry', S.Country AS 'SupplierCountry'
  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 
      CASE
         WHEN C.Country=S.Country THEN '1'
         ELSE '0'
        END = '1';

Displaying records 1 - 10

CustomerName	SupplierName	CustomerCountry	SupplierCountry
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	New Orleans Cajun Delights	USA	USA
Rattlesnake Canyon Grocery	Grandma Kelly’s Homestead	USA	USA

23 Daftar semua produk yang dimulai dengan ‘Cha’ atau ‘Chan’ dan memiliki satu karakter lagi!

SELECT P.ProductName
  FROM products P
    WHERE ProductName LIKE 'Cha_' OR ProductName LIKE 'Chan_';

3 records

ProductName
Chang
Chang
Chang

24 Daftar semua pemasok yang memiliki nomor faks!

Karena tidak ada kolom nomor faks dalam database, kami berasumsi bahwa semua pemasok tidak memiliki nomor faks.

25 Daftar semua pelanggan dengan pesanan rata-rata antara $1000 dan $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 orderdetails 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;

0 records

CustomerName	AverageOrder

26 Daftar total pelanggan di setiap negara.

SELECT C.Country, COUNT(C.Country) AS 'TotalCustomer'
  FROM customers C
    GROUP BY C.Country WITH ROLLUP

Displaying records 1 - 10

Country	TotalCustomer
Argentina	9
Austria	6
Belgium	6
Brazil	27
Canada	9
Denmark	6
Finland	6
France	33
Germany	33
Ireland	3

27 Menampilkan hasil dengan header kolom yang mudah dipahami.

28 Daftar semua pesanan dengan informasi pelanggan!

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

29 Daftar semua pesanan dengan nama produk, jumlah, dan harga!

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	Vegie-spread	65	43.90
10248	Singaporean Hokkien Fried Mee	10	14.00
10248	Rhönbräu Klosterbier	24	7.75
10248	Schoggi Schokolade	50	43.90
10248	Lakkalikööri	18	18.00
10248	Pavlova	49	17.45
10248	Singaporean Hokkien Fried Mee	9	14.00
10248	Pavlova	16	17.45
10248	Wimmers gute Semmelknödel	50	33.25
10248	Tourtière	18	7.45

30 Ini akan mencantumkan semua pelanggan, apakah mereka memesan atau tidak!

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	Alfred Schmidt	NA
1	Alfreds Futterkiste	Alfred Schmidt	NA
1	Alfreds Futterkiste	Alfred Schmidt	NA
2	Ana Trujillo Emparedados y helados	Ana Trujillo	10308
2	Ana Trujillo Emparedados y helados	Ana Trujillo	10308
2	Ana Trujillo Emparedados y helados	Ana Trujillo	10308
3	Antonio Moreno Taquería	Antonio Moreno	10365
3	Antonio Moreno Taquería	Antonio Moreno	10365
3	Antonio Moreno Taquería	Antonio Moreno	10365
4	Around the Horn	Thomas Hardy	10383

31 Daftar pelanggan yang belum melakukan pemesanan!

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	Alfred Schmidt	Obere Str. 57	Frankfurt	12209	Germany
1	Alfreds Futterkiste	Alfred Schmidt	Obere Str. 57	Frankfurt	12209	Germany
1	Alfreds Futterkiste	Alfred Schmidt	Obere Str. 57	Frankfurt	12209	Germany
6	Blauer See Delikatessen	Hanna Moos	Forsterstr. 57	Mannheim	68306	Germany
6	Blauer See Delikatessen	Hanna Moos	Forsterstr. 57	Mannheim	68306	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
12	Cactus Comidas para llevar	Patricio Simpson	Cerrito 333	Buenos Aires	1010	Argentina
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

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
Alejandra Camino	Customer
Alejandra Camino	Customer
Alexander Feuer	Customer
Alexander Feuer	Customer
Alexander Feuer	Customer
Alfred Schmidt	Customer
Alfred Schmidt	Customer
Alfred Schmidt	Customer
Ana Trujillo	Customer

32 Daftar semua kontak, yaitu pemasok dan pelanggan!

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
Alejandra Camino	Customer
Alejandra Camino	Customer
Alexander Feuer	Customer
Alexander Feuer	Customer
Alexander Feuer	Customer
Alfred Schmidt	Customer
Alfred Schmidt	Customer
Alfred Schmidt	Customer
Ana Trujillo	Customer

33 Daftar produk dengan jumlah pesanan lebih dari 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	1431
2	Chang	3069
3	Aniseed Syrup	720
4	Chef Anton’s Cajun Seasoning	963
5	Chef Anton’s Gumbo Mix	1161
6	Grandma’s Boysenberry Spread	324
7	Uncle Bob’s Organic Dried Pears	225
8	Northwoods Cranberry Sauce	1260
9	Mishi Kobe Niku	180
10	Ikura	765

34 Produk mana yang dijual per unit (yaitu kuantitas = 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

Displaying records 1 - 10

ProductID	ProductName	Quantity
19	Teatime Chocolate Biscuits	1
19	Teatime Chocolate Biscuits	1
19	Teatime Chocolate Biscuits	1
19	Teatime Chocolate Biscuits	1
19	Teatime Chocolate Biscuits	1
19	Teatime Chocolate Biscuits	1
19	Teatime Chocolate Biscuits	1
19	Teatime Chocolate Biscuits	1
19	Teatime Chocolate Biscuits	1
37	Gravad lax	1

35 Daftar pelanggan yang memesan lebih besar dari rata-rata setiap pesanan pelanggan!

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
                                    )

Displaying records 1 - 10

CustomerID	CustomerName	TotalAmount
20	Ernst Handel	5270.00
59	Piccolo und mehr	13175.00
7	Blondel père et fils	4332.65
62	Queen Cozinha	10540.00
51	Mère Paillarde	12911.50
20	Ernst Handel	5270.00
59	Piccolo und mehr	13175.00
7	Blondel père et fils	4332.65
62	Queen Cozinha	10540.00
51	Mère Paillarde	12911.50

36 Temukan produk terlaris berdasarkan kuantitas!

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	4122

37 Temukan produk terlaris berdasarkan pendapatan!

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	566788.5

38 Temukan produk terlaris berdasarkan pendapatan untuk setiap negara!

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
    ORDER BY Country;

Displaying records 1 - 10

Country	ProductName	Revenue
Argentina	Tofu	7533.0
Austria	Côte de Blaye	498015.0
Belgium	Sir Rodney’s Marmalade	87480.0
Brazil	Côte de Blaye	284580.0
Canada	Côte de Blaye	348610.5
Denmark	Côte de Blaye	355725.0
Finland	Fløtemysost	40635.0
France	Thüringer Rostbratwurst	116981.6
Germany	Raclette Courdavault	103950.0
Ireland	Manjimup Dried Apples	68688.0

39 Temukan pemasok dengan harga produk kurang dari $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 Temukan 10 karyawan terbaik berdasarkan kuantitas penjualan mereka!

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	9696
Nancy Davolio	5772
Janet Leverling	5175
Andrew Fuller	3945
Laura Callahan	3879
Michael Suyama	3282
Steven Buchanan	2334
Robert King	2199
Anne Dodsworth	1947
Adam West	NA

41 Temukan 10 negara pemasok terbaik berdasarkan kuantitas!

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	43470
USA	42687
France	38772
Germany	36153
Italy	32859
UK	32751
Canada	25623
Norway	22572
Japan	15120
Sweden	12555

42 Temukan 10 negara pelanggan terbaik berdasarkan kuantitas!

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	19251
Germany	18135
Austria	14085
Brazil	10053
France	8001
Canada	6948
UK	6282
Ireland	5085
Venezuela	4428
Sweden	3591

43 Temukan 10 produk terlaris berdasarkan kuantitas di setiap tahun!

(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	3996
Camembert Pierrot	1996	3330
Steeleye Stout	1996	2466
Chartreuse verte	1996	2394
Fløtemysost	1996	2349
Mozzarella di Giovanni	1996	2340
Pavlova	1996	2268
Tarte au sucre	1996	2250
Alice Mutton	1996	2106
Raclette Courdavault	1996	2079