Email           :
Instagram   : https://www.instagram.com/marvis.zerex/
RPubs          : https://rpubs.com/invokerarts/
Linkedin     : https://www.linkedin.com/in/jeffry-wijaya-087a191b5/
Majors         : Business Statistics
Address      : ARA Center, Matana University Tower
                         Jl. CBD Barat Kav, RT.1, Curug Sangereng, Kelapa Dua, Tangerang, Banten 15810.


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 CustomerID, EmployeeID, OrderDate, ShipperID, OrderDetailID, SupplierName, ContactName, Address, City, PostalCode, Country, Phone
  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(No1,
          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 CustomerID, EmployeeID, OrderDate, ShipperID, OrderDetailID, 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
                ORDER BY O.CustomerID
datatable(No2,
          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.3 Choose the correct JOIN clause to select all the records from the Suppliers table plus all the matches in the Orders table.

SELECT CustomerID, EmployeeID, OrderDate, ShipperID, OrderDetailID, 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
                ORDER BY O.CustomerID
datatable(No3,
          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)          
          )