library(RMySQL)
library(DBI)
library(DT)
stroberi <- dbConnect(RMySQL::MySQL(), 
                  user='root',
                  password='', 
                  dbname='stroberi', 
                  host='localhost',
                  port=3306)
knitr::opts_chunk$set(connection = "stroberi") # to set up the connection in your Rmarkdown chunk

SELECT SupplierName, ContactName, Address, Country, City
FROM suppliers
ORDER BY SupplierName

SELECT SupplierName, ContactName, Address, Country, City
FROM suppliers
ORDER BY SupplierName DESC

SELECT SupplierName, ContactName, Address, Country, City
FROM suppliers
ORDER BY Country, City

SELECT *
FROM suppliers
ORDER BY Country DESC, City DESC

SELECT CustomerName, SUM(TotalAmount) TotalOrders
FROM 
(
SELECT CustomerName, SUM(od.Quantity * p.Price) 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
GROUP BY od.OrderID
) A
GROUP BY CustomerName
ORDER BY TotalOrders DESC

SELECT *
FROM products
ORDER BY Price DESC
LIMIT 10

SELECT *
FROM products
ORDER BY Price DESC
LIMIT 9,6;

SELECT SupplierID, SupplierName, Country
FROM suppliers
ORDER BY Country

SELECT *
FROM suppliers
WHERE Country='USA'

SELECT COUNT(SupplierID) NumberOfSuppliers , Country
FROM suppliers
WHERE Country = 'USA'
ORDER BY COUNT(SupplierID)

SELECT SUM(Quantity) ItemsSold
FROM orderdetails

SELECT AVG(Price) AveragePriceofProducts
FROM products

SELECT *
FROM customers
WHERE ContactName='Thomas Hardy'

SELECT *
FROM customers
WHERE Country='Spain' OR Country='France'
ORDER BY Country, CustomerID

SELECT *
FROM customers
WHERE NOT Country = 'USA'

SELECT o.OrderDate, p.ProductID, p.ProductName, p.Price
FROM orders o
JOIN orderdetails od
ON o.OrderID = od.OrderID
JOIN products p
ON od.ProductID = p.ProductID
WHERE Price NOT BETWEEN 50 AND 15000
ORDER BY Price

SELECT *
FROM products
WHERE Price BETWEEN 10 AND 20
ORDER BY Price

SELECT *
FROM products
WHERE Price NOT BETWEEN 10 AND 100
ORDER BY Price

SELECT *
FROM orders
WHERE OrderDate BETWEEN '1996-01-01' AND '1996-12-31';

SELECT *
FROM suppliers
WHERE Country='USA' OR Country='UK' OR Country='Japan'
ORDER BY country

SELECT *
FROM products
WHERE Price NOT IN(10,20,30,40,50)
ORDER BY Price

SELECT c.CustomerName, s.SupplierName, c.Country, s.Country
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 c.Country = s.Country

SELECT *
FROM products
WHERE ProductName LIKE 'Cha_' OR ProductName LIKE 'Chan_'

SELECT CustomerName, AVG(TotalAmount) AverageOrders
FROM 
(
SELECT CustomerName, SUM(od.Quantity * p.Price) 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
GROUP BY od.OrderID
) A
GROUP BY CustomerName
HAVING AverageOrders
BETWEEN 1000 AND 1200
ORDER BY AverageOrders

SELECT Country, COUNT(CustomerName) TotalCustomer
FROM customers
GROUP BY Country

SELECT C.CustomerID, C.CustomerName, O.OrderID, OD.ProductID, P.ProductName, P.Price ,OD.Quantity,(OD.Quantity*P.Price) AS SaleTotal
  FROM Customers C
    INNER JOIN orders O
            ON C.CustomerID = O.CustomerID
    INNER JOIN orderdetails OD
            ON O.OrderID= OD.OrderID
          INNER JOIN products P
          ON OD.ProductID = P.productID
          
ORDER BY OrderID
LIMIT 10

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 ,(OD.Quantity*P.Price) AS SaleTotal
  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 O.OrderID 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', (P.Price*SUM(OD.Quantity)) AS TotalRevenue
  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, OD.ProductID ,(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)

SELECT Country, ProductName, AVG(Revenue) AS 'Revenue' 
FROM(SELECT P.ProductName, C.Country, AVG(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  P.ProductName
   ) S
    GROUP BY Country
    Order BY Revenue DESC

(SELECT P.ProductName, C.Country, YEAR(O.OrderDate) AS 'Year', AVG(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 Country, Year
        HAVING Year = 1996
         ORDER BY Year, Quantity DESC
          LIMIT 16)
UNION

(SELECT P.ProductName, C.Country, YEAR(O.OrderDate) AS 'Year', AVG(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 Country, Year
        HAVING Year = 1997
         ORDER BY Year, Quantity DESC
          LIMIT 16)
          ORDER BY Revenue, Year