Database System Week 7

Data Manipulation ~ Multi-table Query

Email             : sherlytaurinsiri@gmail.com
Instagram     : https://www.instagram.com/sherlytaurin
RPubs            : https://rpubs.com/sherlytaurin/
Github           : https://github.com/sherlytaurin/
Telegram       : @Sherlytaurin

---

Persiapan Awal

Load Packages

library(RMySQL)
library(DBI)
library(DT)

Connect ke MySQL

sherly <- dbConnect(MySQL(),
                    user = 'root',
                    password = '',
                    dbname = 'factory_db',
                    host = 'localhost',
                    port = 3306)
knitr::opts_chunk$set(connection = "sherly")  # mengatur connection agar bisa menggunakan SQL Chunk

1 List all orders with customer information!

SELECT DISTINCT O.OrderID, C.*
  FROM Orders O
    INNER JOIN customers C
      ON O.CustomerID = C.CustomerID
     ORDER BY O.OrderID ASC;

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

SELECT DISTINCT O.OrderID, P.ProductName, OD.Quantity, P.Price
  FROM Orders O
    INNER JOIN orderdetails OD
            ON OD.OrderID = OD.OrderID
    INNER JOIN products P
            ON OD.ProductID = P.ProductID
     ORDER BY O.OrderID ASC;

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

SELECT C.CustomerID, C.CustomerName, C.ContactName, O.OrderID
  FROM customers C
    LEFT JOIN orders O
      ON C.CustomerID = O.CustomerID
     ORDER BY C.CustomerID ASC;

4 List customers that have not placed orders!

SELECT C.*
  FROM customers C
    WHERE C.CustomerID NOT IN ( SELECT O.CustomerID 
                                  FROM orders O )
     ORDER BY C.CustomerID ASC;

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

SELECT S.ContactName, 'Supplier' Type
  FROM suppliers S
    UNION ALL
      SELECT C.ContactName, 'Customer' Type
        FROM customers C
  ORDER BY ContactName ASC;

6 List products with order quantities greater than 80!

SELECT P.ProductID, P.ProductName, SUM(OD.Quantity) AS 'Quantity'
    FROM Products P
      INNER JOIN OrderDetails OD
            ON P.ProductID = OD.ProductID
      GROUP BY P.ProductID
      HAVING SUM(OD.Quantity) > 80
ORDER BY P.ProductID

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

SELECT P.ProductID, P.ProductName, OD.Quantity
    FROM Products P
      INNER JOIN OrderDetails OD
            ON P.ProductID = OD.ProductID
      WHERE OD.Quantity = 1
ORDER BY P.ProductID

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

SELECT C.CustomerID, C.CustomerName, OD.Quantity*P.Price as 'TotalAmount'
  FROM customers C
    JOIN orders O
      ON C.CustomerID = O.CustomerID
    JOIN orderdetails OD
      ON O.OrderID = OD.OrderID
    JOIN products P
      ON OD.ProductID = P.ProductID
  WHERE OD.Quantity*P.Price > ALL (
                                    SELECT AVG(OD.Quantity*P.Price)
                                      FROM customers C
                                        JOIN orders O
                                          ON C.CustomerID = O.CustomerID
                                        JOIN orderdetails OD
                                          ON O.OrderID = OD.OrderID
                                        JOIN products P
                                          ON OD.ProductID = P.ProductID
                                      GROUP BY c.CustomerID
                                      )
    

9 Find best selling products based on quantity!

SELECT P.ProductID, P.ProductName, P.Price, SUM(OD.Quantity) AS 'Quantity'
  FROM Products P
    JOIN Orderdetails OD
      ON P.ProductID=OD.ProductID
        GROUP BY ProductName
          ORDER BY Quantity DESC
            LIMIT 1;

10 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;

11 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
   ORDER BY Country;

12 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

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

SELECT CONCAT(FirstName, ' ', LastName) AS 'Name', sum(Quantity) AS 'Quantity'
  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 Quantity DESC
      LIMIT 10;

14 Find top 10 best supplier countries based on quantity!

SELECT S.Country, sum(Quantity) AS 'Quantity'
  FROM suppliers S
    LEFT JOIN products P
          ON S.SupplierID = P.SupplierID
    LEFT JOIN orderdetails OD
          ON OD.ProductID = P.ProductID
  GROUP BY S.Country
    ORDER BY Quantity DESC
      LIMIT 10;

15 Find top 10 best customer countries based on quantity!

SELECT C.Country, sum(Quantity) AS 'Quantity'
  FROM customers C
    LEFT JOIN orders O
          ON C.CustomerID = O.CustomerID
    LEFT JOIN orderdetails OD
          ON O.OrderID = OD.OrderID
  GROUP BY C.Country
    ORDER BY Quantity DESC
      LIMIT 10;

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

(SELECT P.ProductName, YEAR(O.OrderDate) 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 Year, Quantity DESC
    LIMIT 10)

UNION

  (SELECT P.ProductName, YEAR(O.OrderDate) 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 Year, Quantity DESC
    LIMIT 10)