Email             :
Instagram     : https://www.instagram.com/nikitaindriyni
RPubs            : https://rpubs.com/nikitaindriyani/

Connect to MySQL

library(DBI)
library(RMySQL)
## Warning: package 'RMySQL' was built under R version 4.0.4
library(DT)
Nikita <- dbConnect(MySQL(),
                   user='root',
                   password='',
                   dbname='nikita',
                   host='localhost',
                   port=3306)
knitr::opts_chunk$set(conection="Nikita")

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)          
          )