Email             :
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")

#List all orders with customer information!

SELECT DISTINCT O.OrderID,C.*
  FROM Orders O
    JOIN Customers C
      ON O.CustomerID=C.CustomerID
        ORDER BY OrderID
datatable(No1,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 1')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

#List all orders with product names, quantities, and prices!

SELECT DISTINCT P.ProductName, SUM(OD.Quantity) AS Quantity, P.Price
  FROM Orders O
    JOIN OrderDetails OD
      ON O.OrderID=OD.OrderID
        JOIN Products P
          ON OD.ProductID=P.ProductID
            GROUP BY P.ProductName
              ORDER BY P.ProductName
datatable(No2,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 2')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

#This will list all customers, whether they placed any order or not!

SELECT DISTINCT C.CustomerID, C.CustomerName, O.OrderID
  FROM Customers C
    LEFT JOIN Orders O
      ON C.CustomerID=O.CustomerID
          ORDER BY C.CustomerID ASC
datatable(No3,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 3')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

#List customers that have not placed orders!

SELECT DISTINCT C.*
  FROM customers C
    WHERE C.CustomerID NOT IN ( SELECT O.CustomerID 
                                  FROM orders O
    )
     ORDER BY C.CustomerID ASC;
datatable(No4,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 4')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

#List all contacts, i.e., suppliers and customers!

SELECT DISTINCT S.ContactName, 'Supplier' Status
  FROM suppliers S
    UNION ALL
      SELECT DISTINCT C.ContactName, 'Customer' Status
        FROM customers C
          ORDER BY ContactName ASC;
datatable(No5,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 5')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

#List products with order quantities greater than 80!

SELECT P.ProductID, P.ProductName, SUM(OD.Quantity) AS Quantity
  FROM Products P
    JOIN Orderdetails OD
      ON P.ProductID=OD.ProductID
        GROUP BY P.ProductID
          HAVING SUM(OD.Quantity)>80
            ORDER BY P.ProductID
datatable(No6,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 6')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

#Which products were sold by the unit (i.e. quantity =1)?

SELECT P.ProductName,OD.Quantity
  FROM Products P
    JOIN Orderdetails OD
      ON P.ProductID=OD.ProductID
        WHERE Quantity=1
datatable(No7,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 7')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

#List customers who placed orders that are larger than the average of each customer order!

SELECT CustomerID, CustomerName, ContactName, NumberofOrders
  FROM (SELECT C.*, COUNT(OD.Quantity) AS 'NumberofOrders'
          FROM customers C
            JOIN orders O
              ON C.CustomerID = O.CustomerID
                JOIN orderdetails OD
                  ON O.OrderID = OD.OrderID
                    GROUP BY OD.OrderID
       ) AS S
    GROUP BY S.CustomerID
      HAVING NumberofOrders > AVG(NumberofOrders)
datatable(No8,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 8')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

#Find best selling products based on quantity!

SELECT P.ProductName, OD.Quantity
  FROM Products P
    JOIN Orderdetails OD
      ON P.ProductID=OD.ProductID
        ORDER BY Quantity DESC
          LIMIT 1
datatable(No9,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 9')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

#Find best selling products based on revenue!

SELECT P.ProductName, P.Price, OD.Quantity, SUM(P.Price*OD.Quantity) AS Revenue
  FROM Products P
    JOIN Orderdetails OD
      ON P.ProductID=OD.ProductID
        GROUP BY ProductName
          ORDER BY Revenue DESC
            LIMIT 1
datatable(No10,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 10')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

#Find best selling products based on revenue for each country!

SELECT Country, ProductName, MAX(Revenue) AS Revenue
  FROM(SELECT P.ProductName, C.Country, SUM(OD.Quantity*P.Price) AS Revenue
         FROM Products P
           JOIN Orderdetails OD
             ON P.ProductID=OD.ProductID
               JOIN Orders O
                 ON OD.OrderID=O.OrderID
                   JOIN Customers C
                     ON O.CustomerID=C.CustomerID
                       GROUP BY C.Country, P.ProductName
                         ORDER BY Country ASC, Revenue DESC) S
    GROUP BY Country
datatable(No11,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 11')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

#Find suppliers with a product price less than $50!

SELECT S.SupplierName, P.Price
  FROM Suppliers S
    JOIN Products P
      ON S.SupplierID=P.SupplierID
        WHERE P.Price<50
          GROUP BY S.SupplierName
            ORDER BY s.SupplierName
datatable(No12,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 12')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

#Find top 10 best employees based on their sales quantity!

SELECT CONCAT(FirstName, ' ', LastName) AS FullName
  FROM Employees E
    LEFT JOIN Orders O
      ON E.EmployeeID=O.EmployeeID
        LEFT JOIN Orderdetails OD
          ON O.OrderID=OD.OrderID
            GROUP BY E.EmployeeID
              ORDER BY SUM(OD.Quantity) DESC
                LIMIT 10
datatable(No13,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 13')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

#Find top 10 best supplier countries based on quantity!

SELECT S.Country
  FROM Suppliers S
    JOIN Products P
      ON S.SupplierID=P.SupplierID
        JOIN Orderdetails OD
          ON P.ProductID=OD.ProductID
            GROUP BY S.Country
              ORDER BY SUM(Quantity) DESC
                LIMIT 10
datatable(No14,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 14')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

#Find top 10 best customer countries based on quantity!

SELECT C.Country
  FROM Customers C
    JOIN Orders O
      ON C.CustomerID=O.CustomerID
        JOIN Orderdetails OD
          ON O.OrderID=OD.OrderID
            GROUP BY C.Country
              ORDER BY SUM(Quantity) DESC
                LIMIT 10
datatable(No15,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 15')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )

#Find top 10 best selling products based on quantity in every year!

(SELECT P.ProductID, P.ProductName, RIGHT(O.OrderDate,4) AS Year, SUM(OD.Quantity) AS 'Quantity'
   FROM Products P
     JOIN Orderdetails OD
       ON P.ProductID=OD.ProductID
         JOIN Orders O
           ON OD.OrderID=O.OrderID
               GROUP BY Year, ProductName
               HAVING Year=1996
                 ORDER BY OrderDate, Quantity DESC
                   LIMIT 10)
   UNION
     (SELECT P.ProductID, P.ProductName, RIGHT(O.OrderDate,4) AS Year, SUM(OD.Quantity) AS 'Quantity'
        FROM Products P
          JOIN Orderdetails OD
            ON P.ProductID=OD.ProductID
              JOIN Orders O
                ON OD.OrderID=O.OrderID
                  GROUP BY Year, ProductName
                    HAVING Year=1997
                      ORDER BY OrderDate, Quantity DESC
                        LIMIT 10)
datatable(No16,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Table 16')),
            extensions = 'FixedColumns',
            options = list(scrollX = TRUE, fixedColums = TRUE)          
          )