Email : juliansalomo2@gmail.com
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")
Select Some attributes of suppliers in alphabetical order!
SELECT SupplierName, SupplierID, City, Country
FROM suppliers
ORDER BY SupplierName;
datatable(Num1,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Some attributes of suppliers in alphabetical order')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Some attributes of suppliers in reverse alphabetical order!
SELECT SupplierName, SupplierID, City, Country
FROM suppliers
ORDER BY SupplierName DESC;
datatable(Num2,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Some attributes of suppliers in reverse alphabetical order')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Some attributes of suppliers ordered by country, then by city!
SELECT SupplierName, SupplierID, City, Country
FROM suppliers
ORDER BY Country, City;
datatable(Num3,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Some attributes of suppliers ordered by country, then by city')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
All atributes of suppliers and reverse alphabetical ordered by country, then by city!
SELECT *
FROM suppliers
ORDER BY Country DESC, City DESC;
datatable(Num4,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('All atributes of suppliers and reverse alphabetical ordered by country, then by city')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
All orders, sorted by total amount, the largest first!
SELECT OrderId, OrderDetailID, (Quantity*Price) TotalAmount
FROM orderdetails D
JOIN products P
ON P.ProductID = D.ProductID
ORDER BY TotalAmount DESC;
datatable(Num5,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('All orders, sorted by total amount, the largest first')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Get all but the 10 most expensive products sorted by price!
SELECT *
FROM products
ORDER BY Price DESC
LIMIT 10;
datatable(Num6,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Get all but the 10 most expensive products sorted by price')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Get the 10th to 15th most expensive products sorted by price!
SELECT *
FROM products
ORDER BY Price DESC
LIMIT 9, 6;
datatable(Num7,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Get the 10th to 15th most expensive products sorted by price')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
List all supplier countries in alphabetical order!
SELECT DISTINCT Country
FROM suppliers
ORDER BY Country;
datatable(Num8,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('List all supplier countries in alphabetical order')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Find the cheapest product and Expensive Orders!
SELECT OrderID, MAX(a.TotalOrderPrice) OrderAmount
FROM
(
SELECT OrderID, SUM(Quantity*Price) AS TotalOrderPrice
FROM orderdetails O
JOIN products P
ON O.ProductID=P.ProductID
GROUP BY O.OrderID
) a
datatable(Num9_1,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('The most expensive Order')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
SELECT ProductName, MIN(Price) CheapestPrice
FROM products
datatable(Num9_2,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('The Cheapest Product')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Find the number of Supplier USA
SELECT Country, COUNT(SupplierID) SupplierAmount
FROM suppliers
WHERE Country ='USA';
datatable(Num10,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('The number of Supplier USA')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Compute the total Quantity of orderitem!
SELECT ProductName, SUM(O.Quantity) Quantity
FROM orderdetails O
JOIN products P
ON O.ProductID = P.ProductID
GROUP BY ProductName
ORDER BY ProductName;
datatable(Num11,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('The total Quantity of orderitem')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Compute the average UnitPrice of all product!
SELECT AVG(Price) AveragePrice
FROM products;
datatable(Num12,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('The average UnitPrice of all product')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
List all customers from Spain or France!
SELECT *
FROM customers
WHERE Country
IN ('Spain', 'France')
ORDER BY Country, CustomerID;
datatable(Num14,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('All customers from Spain or France')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
List all customers that are not from the USA!
SELECT *
FROM customers
WHERE Country !='USA'
datatable(Num15,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('All customers that are not from the USA')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
List all orders that not between $50 and $15000!
SELECT O.OrderID, Price,SUM(P.Price*O.Quantity) TotalOrderPrice
FROM orderdetails O
JOIN products P
ON O.ProductID=P.ProductID
GROUP BY OrderID
HAVING TotalOrderPrice
NOT BETWEEN 50 AND 15000
datatable(Num16,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('All orders that not between $50 and $15000')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
List all products between $10 and $20
SELECT *
FROM products
WHERE Price
BETWEEN 10 AND 20;
datatable(Num17,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('All products between $10 and $20')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
List all products not between $10 and $100 sorted by price!
SELECT *
FROM products
WHERE Price
NOT BETWEEN 10 AND 100
ORDER BY Price;
datatable(Num18,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('All products not between $10 and $100 sorted by price')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Get the list of orders and amount sold between 1996 Jan 01 and 1996 Des 31!
SELECT O.OrderID, OrderDate, sum(Quantity) AmountSold
FROM orders O
JOIN orderdetails D
ON O.OrderID = D.OrderID
GROUP BY O.OrderID
HAVING OrderDate
BETWEEN '1996-01-01' AND '1996-12-31';
datatable(Num19,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('the list of orders and amount sold between 1996 Jan 01 and 1996 Des 31')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
List all suppliers from the USA, UK, OR Japan!
SELECT *
FROM suppliers
WHERE Country
IN ('USA', 'UK', 'Japan');
datatable(Num20,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('All suppliers from USA, UK, OR Japan!')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
List all products that are not exactly $10, $20, $30, $40, or $50!
SELECT *
FROM products
WHERE Price
NOT IN (10, 20, 30, 40, 50);
datatable(Num21,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('All products that are not exactly $10, $20, $30, $40, or $50')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
List all customers that are from the same countries as the suppliers!
SELECT CustomerName, SupplierName, S.ContactName, S.Country
FROM customers C
JOIN (((orders O
JOIN orderdetails D
ON O.OrderID = D.OrderID)
JOIN products P
ON P.ProductID = D.ProductID)
JOIN suppliers S
ON S.SupplierID = P.SupplierID)
ON C.CustomerID = O.CustomerID
WHERE
CASE
WHEN C.Country = S.Country THEN '1'
ELSE '0'
END = '1';
datatable(Num22,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('All customers that are from the same countries as the suppliers')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
List all products that start with ‘Cha’ or ‘Chan’ and have one more character!
SELECT ProductName
FROM products
WHERE ProductName LIKE 'Cha_' OR ProductName LIKE 'Chan_';
datatable(Num24,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('All products that start with ‘Cha’ or ‘Chan’ and have one more character')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
List all suppliers that do have a fax number!
Because there is no fax column in the data, we assume that all of the suppliers do not have a fax number.
List all customer with average orders between $1000 and $1200 !
SELECT CustomerName, AVG(OrderTotalAmount) AverageOrder
FROM
(
SELECT CustomerName, SUM(Quantity*Price) OrderTotalAmount
FROM ((customers C
JOIN orders O
ON C.CustomerID= O.CustomerID)
JOIN orderdetails D
ON O.OrderID = D.OrderID)
JOIN products P
ON D.ProductID = P.ProductID
GROUP BY D.OrderID
) E
GROUP BY CustomerName
HAVING AverageOrder
BETWEEN 1000 AND 1200
datatable(Num26,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('All customer with average orders between $1000 and $1200')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
List total customers in each country.
SELECT Country, COUNT(CustomerName) TotalCustomer
FROM customers
GROUP BY Country WITH ROLLUP
datatable(Num27,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Total Customer in each Country')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)