Email             :
RPubs            : https://rpubs.com/juliansalomo/
Department  : Business Statistics
Address         : ARA Center, Matana University Tower
                         Jl. CBD Barat Kav, RT.1, Curug Sangereng, Kelapa Dua, Tangerang, Banten 15810.


Setup our MySQL connection to R

Data <- dbConnect(RMySQL::MySQL(),
                 user ='root',
                 password='',
                 dbname='factory_db',
                 host='localhost',
                 port=3306)
knitr::opts_chunk$set(connection = "Data")

1 List all orders with customer information!

SELECT *
  FROM customers c
    JOIN orders o
    ON c.CustomerID=o.CustomerID
      ORDER BY c.CustomerID;
datatable(x,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('All orders with customer information')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

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

SELECT o.OrderID, o.OrderDate, p.ProductName, d.Quantity, p.Price
  FROM orders o
    JOIN orderdetails d
    ON o.OrderID = d.OrderID
      JOIN products p
      ON p.ProductID = d.ProductID
datatable(x,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('All orders with product names, quantities, and prices')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

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

SELECT c.CustomerID, c.CustomerName, o.OrderID
  FROM customers c
    LEFT JOIN orders o
    ON c.CustomerID=o.CustomerID
      ORDER BY c.CustomerID;
datatable(x,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('List all customers, whether they placed any order or not')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

4 List customers that have not placed orders!

SELECT c.CustomerID, c.CustomerName, o.OrderID
  FROM customers c
    LEFT JOIN orders o
    ON c.CustomerID=o.CustomerID
  WHERE o.OrderID IS NULL;
datatable(x,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('List customers that have not placed orders')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

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

SELECT *
  FROM(
      SELECT 'Customer' Type, c.ContactName
        FROM customers c
          UNION
      SELECT 'Supplier' Type, s.ContactName
        FROM suppliers s
      ) CS
    ORDER BY CS.ContactName
datatable(x,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('List all contacts, i.e., suppliers and customers!')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

6 List products with order quantities greater than 80!

SELECT p.ProductID, p.ProductName
  FROM products p
    WHERE EXISTS
    (
      SELECT *
        FROM 
        (
          SELECT ProductID, sum(quantity) Quantity
            FROM orderdetails
              group by ProductID
        ) d
          WHERE d.ProductID = p.ProductID
            AND Quantity > 80
    );
datatable(x,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Products with order quantities greater than 80')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

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

SELECT p.ProductID, p.ProductName
  FROM products p
    WHERE ProductID = ANY 
    (
      SELECT ProductID
        FROM orderdetails
          WHERE Quantity=1
    );
datatable(x,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Products were sold by the unit (i.e. quantity =1)')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

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

SELECT CustomerName, Quantity*Price TotalAmount
  FROM (((customers c
    JOIN orders o
      ON c.CustomerID = o.CustomerID)
    JOIN orderdetails d
      ON o.OrderID = d.OrderID)
    JOIN products p
      ON p.ProductID = d.ProductID)
        WHERE d.Quantity*p.Price > ALL (
                                        SELECT AVG(Quantity*Price)
                                          FROM (((customers c
                                            JOIN orders o
                                              ON c.CustomerID = o.CustomerID)
                                            JOIN orderdetails d
                                              ON o.OrderID = d.OrderID)
                                            JOIN products p
                                              ON p.ProductID = d.ProductID)
                                                GROUP BY c.CustomerID
                                )
datatable(x,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Customers who placed orders that are larger than the average of each customer order')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

9 Find best selling products based on quantity!

SELECT ProductName, sum(Quantity) Quantity
  FROM products p
    JOIN orderdetails d
      ON p.ProductID = d.ProductID
        GROUP BY ProductName
          HAVING sum(Quantity) >=
            ALL 
            (
              SELECT sum(Quantity)
                FROM products p
                  JOIN orderdetails d
                    ON p.ProductID = d.ProductID
                      GROUP BY ProductName
            )
datatable(x,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Best selling products based on quantity')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

10 Find best selling products based on revenue!

SELECT ProductName, sum(Quantity*Price) Revenue
  FROM products p
    JOIN orderdetails d
      ON p.ProductID = d.ProductID
        GROUP BY ProductName
          HAVING sum(Quantity*Price) >=
            ALL 
            (
              SELECT sum(Quantity*Price)
                FROM products p
                  JOIN orderdetails d
                    ON p.ProductID = d.ProductID
                      GROUP BY ProductName
            )
datatable(x,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Best selling products based on Revenue')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

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

SELECT ProductName, MAX(Revenue) Revenue, Country
from
(
SELECT ProductName, sum(Quantity*Price) Revenue, Country
  FROM products p
    JOIN orderdetails d
      ON p.ProductID = d.ProductID
      JOIN orders o
        ON o.OrderID = d.OrderID
        JOIN customers c
          ON c.CustomerID = o.CustomerID
      GROUP BY ProductName, Country
        ORDER BY Revenue DESC
) as t
GROUP BY Country
ORDER BY ProductName
datatable(x,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Best selling products based on revenue for each country')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

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

SELECT DISTINCT SupplierName
  FROM suppliers s
    JOIN products p
      ON p.SupplierID = s.SupplierID
    WHERE Price < 50
      ORDER BY SupplierName
datatable(x,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Suppliers with a product price less than $50')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

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

SELECT CONCAT(FirstName,'  ', LastName) as Name, sum(Quantity) Quantity
  FROM employees e
    LEFT JOIN orders o
      ON o.EmployeeID = e.EmployeeID
      LEFT JOIN orderdetails d
        ON d.OrderID = o.OrderID
    GROUP BY e.EmployeeID
      ORDER BY Quantity DESC
        LIMIT 0,10
datatable(x,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Top 10 best employees based on their sales quantity')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

14 Find top 10 best supplier countries based on quantity!

SELECT Country, sum(Quantity) Quantity
  FROM suppliers s
    LEFT JOIN products p
      ON p.SupplierID = s.SupplierID 
        LEFT JOIN orderdetails d
        ON d.ProductID = p.ProductID
    GROUP BY s.Country
      ORDER BY Quantity DESC
        LIMIT 0,10
datatable(x,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Top 10 best supplier countries based on quantity!')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

15 Find top 10 best customer countries based on quantity!

SELECT Country, sum(Quantity) Quantity
  FROM customers c
    LEFT JOIN orders o
      ON c.CustomerID = o.CustomerID
      LEFT JOIN orderdetails d
        ON d.OrderID = o.OrderID
    GROUP BY c.Country
      ORDER BY Quantity DESC
        LIMIT 0,10
datatable(x,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Top 10 best customer countries based on quantity')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

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

(SELECT ProductName, YEAR(o.OrderDate) SalesYear, sum(Quantity) Quantity
  FROM orders o
    JOIN orderdetails d
      ON o.OrderID = d.OrderID
      JOIN products p
        ON p.ProductID = d.ProductID
  GROUP BY SalesYear, ProductName
    HAVING SalesYear = 1996
      ORDER BY SalesYear, Quantity DESC
        LIMIT 10)
        
UNION

(SELECT ProductName, YEAR(o.OrderDate) SalesYear, sum(Quantity) Quantity
  FROM orders o
    JOIN orderdetails d
      ON o.OrderID = d.OrderID
      JOIN products p
        ON p.ProductID = d.ProductID
  GROUP BY SalesYear, ProductName
    HAVING SalesYear = 1997
      ORDER BY SalesYear, Quantity DESC
        LIMIT 10)
datatable(x,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Top 10 best selling products based on quantity in every year')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )  
SELECT date_format(OrderDate, '%Y')
from orders
Displaying records 1 - 10
date_format(OrderDate, ‘%Y’)
1996
1996
1996
1996
1996
1996
1996
1996
1996
1996 
SELECT ProductName, date_format(o.OrderDate, '%Y') SalesYear, sum(Quantity) Quantity
  FROM orders o
    JOIN orderdetails d
      ON o.OrderID = d.OrderID
      JOIN products p
        ON p.ProductID = d.ProductID
    WHERE date_format(o.OrderDate, '%Y') = 1996
  GROUP BY ProductName
      ORDER BY SalesYear, Quantity DESC
        LIMIT 10
Displaying records 1 - 10
ProductName SalesYear Quantity
Gorgonzola Telino 1996 444
Camembert Pierrot 1996 370
Steeleye Stout 1996 274
Chartreuse verte 1996 266
Fløtemysost 1996 261
Mozzarella di Giovanni 1996 260
Pavlova 1996 252
Tarte au sucre 1996 250
Alice Mutton 1996 234
Raclette Courdavault 1996 231