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