Basis Data

Praktikum 5


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

Homework

connect to MySQL

library(RMySQL)   
library(DBI)

bunbun_RMySQL <- dbConnect(MySQL(), 
                           user='bunbun',
                           password='bunbun123', 
                           dbname='bunbun', 
                           host='localhost')
bunbun_RMySQL
## <MySQLConnection:0,0>

No 1

When do we use Inner Join, Left Join, Right Join, and Full Join?? Please explain your answers!

Left Join

Kita akan menggunakan Left join ketika kita tabel yang menjadi fokus merupakan tabel yang di sebelah kiri. Tabel yang di kiri menjadi primary key, dan tabel yang di sebalah kanan akan menjadi foreign key.

Right Join

Kita akan menggunakan Right join ketika kita tabel yang menjadi fokus merupakan tabel yang di sebelah kanan. Tabel yang di kanan menjadi primary key, dan tabel yang di sebelah kiri akan menjadi foreign key.

Inner Join

Kita akan menggunakan ketika kita membutuhkan isi data yang sama diantara kedua tabel. Inner join bisa dibilang seperti irisan kedua tabel atau data yang muncul yang berada di kedua tabel. Anggapannya seperti memfilter data yang terdapat di kedua tabel.

Full Join

Kita akan menggunakan Full Join ketika kita membutuhkan semua data, walaupun di salah satu tabel ada yang kosong, maka akan diisi NULL.

No 2

Apply left join and right join to returns all records from table Orders and any matching records from table suppliers.

SELECT *
FROM Orders AS O
left JOIN OrderDetails AS OD
ON O.OrderID = OD.OrderID
right join Products P
ON P.ProductID = OD.ProductID
right join Suppliers as S
on S.SupplierID=P.SupplierID
Displaying records 1 - 10
row_names OrderID CustomerID EmployeeID OrderDate ShipperID row_names OrderDetailID OrderID ProductID Quantity row_names ProductID ProductName SupplierID CategoryID Unit Price row_names SupplierID SupplierName ContactName Address City PostalCode Country Phone
1 10248 90 5 1996-07-04 3 1 1 10248 11 12 11 11 Queso Cabrales 5 4 1 kg pkg. 21.00 5 5 Cooperativa de Quesos ‘Las Cabras’ Antonio del Valle Saavedra Calle del Rosal 4 Oviedo 33007 Spain (98) 598 76 54
1 10248 90 5 1996-07-04 3 2 2 10248 42 10 42 42 Singaporean Hokkien Fried Mee 20 5 32 - 1 kg pkgs. 14.00 20 20 Leka Trading Chandra Leka 471 Serangoon Loop, Suite #402 Singapore 512 Singapore 555-8787
1 10248 90 5 1996-07-04 3 3 3 10248 72 5 72 72 Mozzarella di Giovanni 14 4 24 - 200 g pkgs. 34.80 14 14 Formaggi Fortini s.r.l. Elio Rossi Viale Dante, 75 Ravenna 48100 Italy (0544) 60323
2 10249 81 6 1996-07-05 1 4 4 10249 14 9 14 14 Tofu 6 7 40 - 100 g pkgs. 23.25 6 6 Mayumi’s Mayumi Ohno 92 Setsuko Chuo-ku Osaka 545 Japan (06) 431-7877
2 10249 81 6 1996-07-05 1 5 5 10249 51 40 51 51 Manjimup Dried Apples 24 7 50 - 300 g pkgs. 53.00 24 24 G’day, Mate Wendy Mackenzie 170 Prince Edward Parade Hunter’s Hill Sydney 2042 Australia (02) 555-5914
3 10250 34 4 1996-07-08 2 6 6 10250 41 10 41 41 Jack’s New England Clam Chowder 19 8 12 - 12 oz cans 9.65 19 19 New England Seafood Cannery Robb Merchant Order Processing Dept. 2100 Paul Revere Blvd. Boston 2134 USA (617) 555-3267
3 10250 34 4 1996-07-08 2 7 7 10250 51 35 51 51 Manjimup Dried Apples 24 7 50 - 300 g pkgs. 53.00 24 24 G’day, Mate Wendy Mackenzie 170 Prince Edward Parade Hunter’s Hill Sydney 2042 Australia (02) 555-5914
3 10250 34 4 1996-07-08 2 8 8 10250 65 15 65 65 Louisiana Fiery Hot Pepper Sauce 2 2 32 - 8 oz bottles 21.05 2 2 New Orleans Cajun Delights Shelley Burke P.O. Box 78934 New Orleans 70117 USA (100) 555-4822
4 10251 84 3 1996-07-08 1 9 9 10251 22 6 22 22 Gustaf’s Knäckebröd 9 5 24 - 500 g pkgs. 21.00 9 9 PB Knäckebröd AB Lars Peterson Kaloadagatan 13 Göteborg S-345 67 Sweden 031-987 65 43
4 10251 84 3 1996-07-08 1 10 10 10251 57 15 57 57 Ravioli Angelo 26 5 24 - 250 g pkgs. 19.50 26 26 Pasta Buttini s.r.l. Giovanni Giudici Via dei Gelsomini, 153 Salerno 84100 Italy (089) 6547665

No 3

Choose the correct JOIN clause to select all records from the two tables (Orders and Suppliers) where there is a match in both tables.

SELECT *
FROM Orders AS O
inner JOIN OrderDetails AS OD
ON O.OrderID = OD.OrderID
inner join Products P
ON P.ProductID = OD.ProductID
inner join Suppliers as S
on S.SupplierID=P.SupplierID
Displaying records 1 - 10
row_names OrderID CustomerID EmployeeID OrderDate ShipperID row_names OrderDetailID OrderID ProductID Quantity row_names ProductID ProductName SupplierID CategoryID Unit Price row_names SupplierID SupplierName ContactName Address City PostalCode Country Phone
1 10248 90 5 1996-07-04 3 1 1 10248 11 12 11 11 Queso Cabrales 5 4 1 kg pkg. 21.00 5 5 Cooperativa de Quesos ‘Las Cabras’ Antonio del Valle Saavedra Calle del Rosal 4 Oviedo 33007 Spain (98) 598 76 54
1 10248 90 5 1996-07-04 3 2 2 10248 42 10 42 42 Singaporean Hokkien Fried Mee 20 5 32 - 1 kg pkgs. 14.00 20 20 Leka Trading Chandra Leka 471 Serangoon Loop, Suite #402 Singapore 512 Singapore 555-8787
1 10248 90 5 1996-07-04 3 3 3 10248 72 5 72 72 Mozzarella di Giovanni 14 4 24 - 200 g pkgs. 34.80 14 14 Formaggi Fortini s.r.l. Elio Rossi Viale Dante, 75 Ravenna 48100 Italy (0544) 60323
2 10249 81 6 1996-07-05 1 4 4 10249 14 9 14 14 Tofu 6 7 40 - 100 g pkgs. 23.25 6 6 Mayumi’s Mayumi Ohno 92 Setsuko Chuo-ku Osaka 545 Japan (06) 431-7877
2 10249 81 6 1996-07-05 1 5 5 10249 51 40 51 51 Manjimup Dried Apples 24 7 50 - 300 g pkgs. 53.00 24 24 G’day, Mate Wendy Mackenzie 170 Prince Edward Parade Hunter’s Hill Sydney 2042 Australia (02) 555-5914
3 10250 34 4 1996-07-08 2 6 6 10250 41 10 41 41 Jack’s New England Clam Chowder 19 8 12 - 12 oz cans 9.65 19 19 New England Seafood Cannery Robb Merchant Order Processing Dept. 2100 Paul Revere Blvd. Boston 2134 USA (617) 555-3267
3 10250 34 4 1996-07-08 2 7 7 10250 51 35 51 51 Manjimup Dried Apples 24 7 50 - 300 g pkgs. 53.00 24 24 G’day, Mate Wendy Mackenzie 170 Prince Edward Parade Hunter’s Hill Sydney 2042 Australia (02) 555-5914
3 10250 34 4 1996-07-08 2 8 8 10250 65 15 65 65 Louisiana Fiery Hot Pepper Sauce 2 2 32 - 8 oz bottles 21.05 2 2 New Orleans Cajun Delights Shelley Burke P.O. Box 78934 New Orleans 70117 USA (100) 555-4822
4 10251 84 3 1996-07-08 1 9 9 10251 22 6 22 22 Gustaf’s Knäckebröd 9 5 24 - 500 g pkgs. 21.00 9 9 PB Knäckebröd AB Lars Peterson Kaloadagatan 13 Göteborg S-345 67 Sweden 031-987 65 43
4 10251 84 3 1996-07-08 1 10 10 10251 57 15 57 57 Ravioli Angelo 26 5 24 - 250 g pkgs. 19.50 26 26 Pasta Buttini s.r.l. Giovanni Giudici Via dei Gelsomini, 153 Salerno 84100 Italy (089) 6547665

No 4

Choose the correct JOIN clause to select all the records from the suppliers table plus all the matches in the Orders table

SELECT *
FROM Suppliers AS S
right join Products P on S.SupplierID = P.SupplierID
left join OrderDetails OD on P.ProductID = OD.ProductID
left join Orders O on OD.OrderID=O.OrderID
Displaying records 1 - 10
row_names SupplierID SupplierName ContactName Address City PostalCode Country Phone row_names ProductID ProductName SupplierID CategoryID Unit Price row_names OrderDetailID OrderID ProductID Quantity row_names OrderID CustomerID EmployeeID OrderDate ShipperID
5 5 Cooperativa de Quesos ‘Las Cabras’ Antonio del Valle Saavedra Calle del Rosal 4 Oviedo 33007 Spain (98) 598 76 54 11 11 Queso Cabrales 5 4 1 kg pkg. 21.00 1 1 10248 11 12 1 10248 90 5 1996-07-04 3
20 20 Leka Trading Chandra Leka 471 Serangoon Loop, Suite #402 Singapore 512 Singapore 555-8787 42 42 Singaporean Hokkien Fried Mee 20 5 32 - 1 kg pkgs. 14.00 2 2 10248 42 10 1 10248 90 5 1996-07-04 3
14 14 Formaggi Fortini s.r.l. Elio Rossi Viale Dante, 75 Ravenna 48100 Italy (0544) 60323 72 72 Mozzarella di Giovanni 14 4 24 - 200 g pkgs. 34.80 3 3 10248 72 5 1 10248 90 5 1996-07-04 3
6 6 Mayumi’s Mayumi Ohno 92 Setsuko Chuo-ku Osaka 545 Japan (06) 431-7877 14 14 Tofu 6 7 40 - 100 g pkgs. 23.25 4 4 10249 14 9 2 10249 81 6 1996-07-05 1
24 24 G’day, Mate Wendy Mackenzie 170 Prince Edward Parade Hunter’s Hill Sydney 2042 Australia (02) 555-5914 51 51 Manjimup Dried Apples 24 7 50 - 300 g pkgs. 53.00 5 5 10249 51 40 2 10249 81 6 1996-07-05 1
19 19 New England Seafood Cannery Robb Merchant Order Processing Dept. 2100 Paul Revere Blvd. Boston 2134 USA (617) 555-3267 41 41 Jack’s New England Clam Chowder 19 8 12 - 12 oz cans 9.65 6 6 10250 41 10 3 10250 34 4 1996-07-08 2
24 24 G’day, Mate Wendy Mackenzie 170 Prince Edward Parade Hunter’s Hill Sydney 2042 Australia (02) 555-5914 51 51 Manjimup Dried Apples 24 7 50 - 300 g pkgs. 53.00 7 7 10250 51 35 3 10250 34 4 1996-07-08 2
2 2 New Orleans Cajun Delights Shelley Burke P.O. Box 78934 New Orleans 70117 USA (100) 555-4822 65 65 Louisiana Fiery Hot Pepper Sauce 2 2 32 - 8 oz bottles 21.05 8 8 10250 65 15 3 10250 34 4 1996-07-08 2
9 9 PB Knäckebröd AB Lars Peterson Kaloadagatan 13 Göteborg S-345 67 Sweden 031-987 65 43 22 22 Gustaf’s Knäckebröd 9 5 24 - 500 g pkgs. 21.00 9 9 10251 22 6 4 10251 84 3 1996-07-08 1
26 26 Pasta Buttini s.r.l. Giovanni Giudici Via dei Gelsomini, 153 Salerno 84100 Italy (089) 6547665 57 57 Ravioli Angelo 26 5 24 - 250 g pkgs. 19.50 10 10 10251 57 15 4 10251 84 3 1996-07-08 1

Disconnect or close connection

DBI::dbDisconnect(bunbun_RMySQL)