Email : jirene113@gmail.com
Instagram : https://www.instagram.com/irenegani
RPubs : https://rpubs.com/irenegani/
Department : Business Statistics
Address : ARA Center, Matana University Tower
Jl. CBD Barat Kav, RT.1, Curug Sangereng, Kelapa Dua, Tangerang, Banten 15810.
Connect to MySQL
library(DBI)
library(RMySQL)
library(DT)
Jocelyn <- dbConnect(MySQL(),
user='root',
password='',
dbname='dataraw',
host='localhost',
port=3306)
knitr::opts_chunk$set(conection="Jocelyn")
Select Some attributes of suppliers in alphabetical order!
SELECT SupplierName, Address, City, Country
FROM Suppliers
ORDER BY SupplierName ASC
datatable(No1,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 1')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
Some attributes of suppliers in reverse alphabetical order!
SELECT SupplierID, SupplierName, Address, City, Country
FROM Suppliers
ORDER BY SupplierName DESC
datatable(No2,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 2')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
Some attributes of suppliers ordered by country, then by city!
SELECT SupplierID, SupplierName, Address, City, Country
FROM Suppliers
ORDER BY Country, City
datatable(No3,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 3')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
All atributes of suppliers and reverse alphabetical ordered by country, then by city!
SELECT *
FROM Suppliers
ORDER BY Country, City
datatable(No4,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 4')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
All orders, sorted by total amount, the largest first!
SELECT O.*, Quantity
FROM Orders O
JOIN OrderDetails OD
ON O.OrderID = OD.OrderID
ORDER BY Quantity DESC
datatable(No5,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 5')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
Get all but the 10 most expensive products sorted by price!
SELECT *
FROM Products
ORDER BY Price DESC
LIMIT 10
datatable(No6,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 6')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
Get the 10th to 15th most expensive products sorted by price!
SELECT *
FROM Products
ORDER BY Price DESC
LIMIT 9, 6
datatable(No7,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 7')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
List all supplier countries in alphabetical order!
SELECT *
FROM Suppliers
ORDER BY Country
datatable(No8,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 8')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
Find the cheapest product and Expensive Orders!
SELECT DISTINCT ProductName, P.*, Quantity
FROM Products P
JOIN OrderDetails OD
ON P.ProductID = OD.ProductID
ORDER BY Price ASC, Quantity DESC
datatable(No9,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 9')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
Find the number of Supplier USA!
SELECT COUNT(S.Country) AS 'SupplierFromUSA'
FROM Suppliers S
WHERE S.Country='USA'
datatable(No10,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 10')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
Compute the total Quantity of orderitem!
SELECT SUM(Quantity) AS TotalQuantity
FROM Orderdetails
datatable(No11,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 11')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
Compute the average UnitPrice of all product!
SELECT AVG(Price) AS AverageAllUnitPrice
FROM Products
datatable(No12,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 12')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
List all customers from Spain or France!
SELECT *
FROM Customers
WHERE Country
IN ('Spain', 'France')
ORDER BY Country
datatable(No14,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 14')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
List all customers that are not from the USA!
SELECT *
FROM Customers
WHERE Country
NOT IN ('USA')
ORDER BY Country
datatable(No15,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 15')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
List all orders that not between $50 and $15000!
SELECT O.*, 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 DESC
datatable(No16,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 16')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
List all products between $10 and $20
SELECT ProductName, Price
FROM Products
WHERE Price
BETWEEN 10 AND 20
ORDER BY ProductName
datatable(No17,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 17')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
List all products not between $10 and $100 sorted by price!
SELECT ProductName, Price
FROM Products
WHERE Price
NOT BETWEEN 10 AND 100
ORDER BY PRICE
datatable(No18,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 18')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
Get the list of orders and amount sold between 1996 Jan 01 and 1996 Des 31!
SELECT O.*, Quantity
FROM Orders O
JOIN OrderDetails OD
ON O.OrderID = OD.OrderID
WHERE OrderDate
BETWEEN '01/01/1996' AND '31/12/1996'
ORDER BY OrderDate DESC
datatable(No19,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 19')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
List all suppliers from the USA, UK, OR Japan!
SELECT *
FROM Suppliers
WHERE Country='USA' OR Country='UK' OR Country='JAPAN'
datatable(No20,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 20')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = 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(No21,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 21')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
List all customers that are from the same countries as the suppliers!
SELECT CustomerName, 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
datatable(No22,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 22')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
List all products that start with ‘Cha’ or ‘Chan’ and have one more character!
SELECT *
FROM Products
WHERE ProductName
LIKE 'Cha_%' OR 'Chan_%'
datatable(No23,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 23')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
List all suppliers that do have a fax number!
Dalam data suppliers, tidak ada kolom fax.
List all customer with average orders between $1000 and $1200 !
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
) Z
GROUP BY CustomerName
HAVING AverageOrder
BETWEEN 1000 AND 1200
ORDER BY AverageOrder ASC;
datatable(No25,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 25')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
List total customers in each country.
SELECT Country, COUNT(*) AS `Customer(s)`
FROM Customers
GROUP BY Country
datatable(No26,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 26')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)