Assignment5-Database

Connect to MySQL

library(RMySQL)

## Loading required package: DBI

library(DBI)
vanessa <- dbConnect(RMySQL::MySQL(),
                     dbname='factory_db',
                     username='root',
                     password='',
                     host='localhost',
                     port=3306)
knitr::opts_chunk$set(conection="vanessa")

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

datajoin <-dbGetQuery(vanessa, "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.OrderID" )
library(DT)
datatable(datajoin,
           caption = htmltools::tags$caption(
             style = 'caption-side: bottom; text-align: center;',
             htmltools::em('judul tabel')),
             extensions = 'FixedColumns',
             options = list(scrollX = TRUE, fixedColums = TRUE)
           )

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

datajoin2 <-dbGetQuery(vanessa, "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.OrderID" )
library(DT)
datatable(datajoin2,
           caption = htmltools::tags$caption(
             style = 'caption-side: bottom; text-align: center;',
             htmltools::em('judul tabel')),
             extensions = 'FixedColumns',
             options = list(scrollX = TRUE, fixedColums = TRUE)
           )

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

datajoin3 <-dbGetQuery(vanessa, "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.OrderID" )
library(DT)
datatable(datajoin3,
           caption = htmltools::tags$caption(
             style = 'caption-side: bottom; text-align: center;',
             htmltools::em('judul tabel')),
             extensions = 'FixedColumns',
             options = list(scrollX = TRUE, fixedColums = TRUE)
           )