Email : je070601@gmail.com
Instagram : https://www.instagram.com/marvis.zerex/
RPubs : https://rpubs.com/invokerarts/
Linkedin : https://www.linkedin.com/in/jeffry-wijaya-087a191b5/
Majors : Business Statistics
Address : ARA Center, Matana University Tower
Jl. CBD Barat Kav, RT.1, Curug Sangereng, Kelapa Dua, Tangerang, Banten 15810.
List all orders with product names, quantities, and prices!
SELECT DISTINCT P.ProductName, SUM(OD.Quantity) AS Quantity, P.Price
FROM Orders O
JOIN OrderDetails OD
ON O.OrderID=OD.OrderID
JOIN Products P
ON OD.ProductID=P.ProductID
GROUP BY P.ProductName
ORDER BY P.ProductName
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)
)
This will list all customers, whether they placed any order or not!
SELECT DISTINCT C.CustomerID, C.CustomerName, O.OrderID
FROM Customers C
LEFT JOIN Orders O
ON C.CustomerID=O.CustomerID
ORDER BY C.CustomerID ASC
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)
)
List customers that have not placed orders!
SELECT DISTINCT C.*
FROM customers C
WHERE C.CustomerID NOT IN ( SELECT O.CustomerID
FROM orders O
)
ORDER BY C.CustomerID ASC;
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)
)
List products with order quantities greater than 80!
SELECT P.ProductID, P.ProductName, SUM(OD.Quantity) AS Quantity
FROM Products P
JOIN Orderdetails OD
ON P.ProductID=OD.ProductID
GROUP BY P.ProductID
HAVING SUM(OD.Quantity)>80
ORDER BY P.ProductID
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)
)
Which products were sold by the unit (i.e. quantity =1)?
SELECT P.ProductName,OD.Quantity
FROM Products P
JOIN Orderdetails OD
ON P.ProductID=OD.ProductID
WHERE Quantity=1
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 customers who placed orders that are larger than the average of each customer order!
SELECT CustomerID, CustomerName, ContactName, NumberofOrders
FROM (SELECT C.*, COUNT(OD.Quantity) AS 'NumberofOrders'
FROM customers C
JOIN orders O
ON C.CustomerID = O.CustomerID
JOIN orderdetails OD
ON O.OrderID = OD.OrderID
GROUP BY OD.OrderID
) AS S
GROUP BY S.CustomerID
HAVING NumberofOrders > AVG(NumberofOrders)
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 best selling products based on quantity!
SELECT P.ProductName, OD.Quantity
FROM Products P
JOIN Orderdetails OD
ON P.ProductID=OD.ProductID
ORDER BY Quantity DESC
LIMIT 1
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 best selling products based on revenue!
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
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)
)
Find best selling products based on revenue for each country!
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
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)
)
Find suppliers with a product price less than $50!
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
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)
)
Find top 10 best employees based on their sales quantity!
SELECT CONCAT(FirstName, ' ', LastName) AS FullName
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 SUM(OD.Quantity) DESC
LIMIT 10
datatable(No13,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Table 13')),
extensions = 'FixedColumns',
options = list(scrollX = TRUE, fixedColums = TRUE)
)
Find top 10 best supplier countries based on quantity!
SELECT S.Country
FROM Suppliers S
JOIN Products P
ON S.SupplierID=P.SupplierID
JOIN Orderdetails OD
ON P.ProductID=OD.ProductID
GROUP BY S.Country
ORDER BY SUM(Quantity) DESC
LIMIT 10
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)
)
Find top 10 best customer countries based on quantity!
SELECT C.Country
FROM Customers C
JOIN Orders O
ON C.CustomerID=O.CustomerID
JOIN Orderdetails OD
ON O.OrderID=OD.OrderID
GROUP BY C.Country
ORDER BY SUM(Quantity) DESC
LIMIT 10
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)
)
Find top 10 best selling products based on quantity in every year!
(SELECT P.ProductID, P.ProductName, RIGHT(O.OrderDate,4) 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 OrderDate, Quantity DESC
LIMIT 10)
UNION
(SELECT P.ProductID, P.ProductName, RIGHT(O.OrderDate,4) 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 OrderDate, Quantity DESC
LIMIT 10)
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)
)