Database System

Assignment 5: SQL Join Data

Email             : juliansalomo2@gmail.com
RPubs            : https://rpubs.com/juliansalomo/
Department  : Business Statistics
Address         : ARA Center, Matana University Tower
                         Jl. CBD Barat Kav, RT.1, Curug Sangereng, Kelapa Dua, Tangerang, Banten 15810.

---

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

Data <- dbConnect(RMySQL::MySQL(),
                 user ='root',
                 password='',
                 dbname='factory_db',
                 host='localhost',
                 port=3306)
knitr::opts_chunk$set(connection = "Data")
Num1 <- dbGetQuery(Data, "SELECT O.*, S.*
                            FROM ((Orders O
                              LEFT JOIN OrderDetails OD
                                ON O.OrderID = OD.OrderID)
                              LEFT JOIN Products P
                                ON OD.ProductID = P.ProductID)
                              LEFT JOIN Suppliers S
                                ON P.SupplierID = S.SupplierID
                                  ORDER BY O.OrderID, S.SupplierID")
datatable(Num1,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Left and Right Join')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )    

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.

Data <- dbConnect(RMySQL::MySQL(),
                 user ='root',
                 password='',
                 dbname='factory_db',
                 host='localhost',
                 port=3306)
Num2 <- dbGetQuery(Data, "SELECT O.*, S.*
                            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
                                  ORDER BY O.OrderID, S.SupplierID")
datatable(Num2,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Left and Right Join')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )    

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

Data <- dbConnect(RMySQL::MySQL(),
                 user ='root',
                 password='',
                 dbname='factory_db',
                 host='localhost',
                 port=3306)
Num2 <- dbGetQuery(Data, "SELECT S.*, O.*
                            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
                                  ORDER BY S.SupplierID, O.OrderID")
datatable(Num2,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Left and Right Join')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )