# Mengatur koneksi dan penyimpanan kedalam ruang kerja
library(RMySQL)
library(DBI)
Clara <- dbConnect(RMySQL::MySQL(),
                  dbname='new_mariadb',
                  username='root',
                  password='', 
                  host='localhost',
                  port=3306)
knitr::opts_chunk$set(connection = "Clara") # Untuk mengatur koneksi pada Rmarkdown

SELECT SupplierID, SupplierName, Address, City, Country
  FROM Suppliers S
     ORDER BY S.SupplierName ASC;

SELECT SupplierID, SupplierName, Address, City, Country
  FROM Suppliers S
     ORDER BY S.SupplierName DESC;

SELECT SupplierID, SupplierName, Address, City, Country
  FROM Suppliers S
     ORDER BY S.Country ASC, S.City ASC;

SELECT S.*
  FROM Suppliers S
     ORDER BY S.Country DESC, S.City DESC;

SELECT O.*, OD.Quantity
  FROM Orders O
    RIGHT JOIN orderdetails OD
      ON O.OrderID = OD.OrderID
        ORDER BY OD.Quantity DESC;

SELECT P.*
  FROM products P
    ORDER BY P.Price DESC
      LIMIT 10;

SELECT P.*
  FROM products P
    ORDER BY P.Price DESC
      LIMIT 6 OFFSET 9;

SELECT DISTINCT Country
  FROM suppliers S
    ORDER BY S.Country ASC;

SELECT P.ProductName, P.Unit, P.Price
  FROM products P
      ORDER BY P.Price ASC
        LIMIT 1;

SELECT P.ProductName, P.Unit, P.Price
  FROM products P
      ORDER BY P.Price DESC
        LIMIT 1;

SELECT S.Country, COUNT(S.Country) AS 'Total_Supplier'
  FROM suppliers S
    WHERE S.Country='USA' ;

SELECT SUM(OD.Quantity) AS 'TotalQuantity'
  FROM orderdetails OD

SELECT AVG(P.Price) AS 'Average_UnitPrice'
  FROM products P

SELECT C.*
  FROM customers C
    WHERE ContactName='Thomas Hardy';

SELECT C.*
  FROM customers C
    WHERE Country="Spain" OR Country="France";

SELECT C.*
  FROM customers C
    WHERE NOT Country="USA"

SELECT O.*,P.ProductID, P.ProductName, P.Price
  FROM orders O
    LEFT JOIN orderdetails OD
      ON O.OrderID = OD.OrderID
    LEFT JOIN products P
      ON OD.ProductID = P.ProductID
  WHERE P.Price 
    NOT BETWEEN 50 AND 15000;

SELECT DISTINCT P.*
  FROM products P
    WHERE P.Price 
     BETWEEN 10 AND 20;

SELECT DISTINCT P.*
  FROM products P
    WHERE P.Price 
     NOT BETWEEN 10 AND 100
ORDER BY P.Price ASC;

SELECT O.OrderID, O.OrderDate, sum(OD.Quantity) AS 'AmountSold'
  FROM orders O
    JOIN orderdetails OD
      ON O.OrderID = OD.OrderID
    GROUP BY O.OrderID
    HAVING O.OrderDate
    BETWEEN '1996-01-01' AND '1996-12-31'
  ORDER BY O.OrderDate;

SELECT S.*
  FROM suppliers S
    WHERE S.Country 
      IN ('USA', 'UK', 'JAPAN');

SELECT P.*
  FROM products P
    WHERE P.Price
      NOT IN (10, 20, 30, 40, 50);

SELECT C.CustomerName, S.SupplierName, C.Country AS 'CustomerCountry', S.Country AS 'SupplierCountry'
  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
    JOIN Suppliers S
      ON P.SupplierID=S.SupplierID
    WHERE 
      CASE
         WHEN C.Country=S.Country THEN '1'
         ELSE '0'
        END = '1';

SELECT P.ProductName
  FROM products P
    WHERE ProductName LIKE 'Cha_' OR ProductName LIKE 'Chan_';

SELECT CustomerName, AVG(TotalOrderAmount) AS AverageOrder
  FROM 
      ( 
       SELECT CustomerName, SUM(OD.Quantity*P.Price) AS TotalOrderAmount
        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 OD.OrderID
        ) S
  GROUP BY S.CustomerName
    HAVING AverageOrder
      BETWEEN 1000 AND 1200
  ORDER BY AverageOrder ASC;

SELECT C.Country, COUNT(C.Country) AS 'TotalCustomer'
  FROM customers C
    GROUP BY C.Country WITH ROLLUP

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

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;

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;

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

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

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

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

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
                                    )

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;

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;

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

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

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;

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;

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;

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