SQL Join Table

Sistem Basis Data

Email             : putriangelina865@gmail.com
Instagram     : https://www.instagram.com/putriangelinaw
RPubs            : https://rpubs.com/putriangelinaw/

---

1 Needed Packages

library(DBI)
library(RMySQL)
library(DT)
library(readxl)

2 Connect To SQL

conn <- dbConnect(MySQL(),
                     user="root",
                     password="",
                     dbname="factory_db",
                     host="localhost")

3 Questions

1. Apply Left join and Right join to returns all records from table Orders and any matching records from table Suppliers.

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

3. Choose the correct JOIN clause to select all the records from the Suppliers table plus all the matches in the Orders table.

4 Table Orders

5 Table Suppliers

6 Question 1

SELECT CustomerID, EmployeeID, OrderDate, ShipperID, SupplierName, ContactName, Address, City, Postalcode, Country, Phone
FROM Orders o
LEFT JOIN OrderDetails od
ON o.OrderID = od.OrderID
RIGHT JOIN products p
ON od.ProductID = p.ProductID
RIGHT JOIN suppliers s
ON p.SupplierID = s.SupplierID

7 Question 2

SELECT CustomerID, EmployeeID, OrderDate, ShipperID, SupplierName, ContactName, Address, City, Postalcode, Country, Phone
FROM Orders o
INNER JOIN OrderDetails od
ON o.OrderID = od.OrderID
INNER JOIN products p
ON od.ProductID = p.ProductID
INNER JOIN suppliers s
ON p.SupplierID = s.SupplierID

8 Question 3

SELECT CustomerID, EmployeeID, OrderDate, ShipperID, SupplierName, ContactName, Address, City, Postalcode, Country, Phone
FROM Orders o
RIGHT JOIN OrderDetails od
ON o.OrderID = od.OrderID
RIGHT JOIN products p
ON od.ProductID = p.ProductID
RIGHT JOIN suppliers s
ON p.SupplierID = s.SupplierID