Database System

SDB QSL Join Table

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

---

Connect to MySQL

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

Jocelyn <- dbConnect(MySQL(),
                   user='root',
                   password='',
                   dbname='dataraw',
                   host='localhost',
                   port=3306)
knitr::opts_chunk$set(conection="Jocelyn")

1 Excercise

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

SELECT * 
  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.CustomerID

datatable(Data1,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 1: SQL Left Join.')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

1.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.

SELECT * 
  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.CustomerID

datatable(Data2,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 2: SQL Inner Join.')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

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

SELECT * 
  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 O.CustomerID

datatable(Data3,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 3: SQL Right Join.')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )