Connect To MySQL
## Loading required package: DBI
library(RMariaDB)
library(DBI)
kefas <- dbConnect(RMySQL::MySQL(),
dbname='factory_db',
username='root',
password='',
host='localhost',
port=3306)
knitr::opts_chunk$set(connection="kefas")
Jawaban
List all orders with product names, quantities, and prices!
SELECT DISTINCT O.OrderID, P.ProductName, OD.Quantity, P.Price
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;
library(DT)
datatable(no2,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('All Orders with Product Names, Quantities, and Prices')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
This will list all customers, whether they placed any order or not!
SELECT C.CustomerID, C.CustomerName, O.OrderID
FROM customers C
LEFT JOIN orders O
ON C.CustomerID = O.CustomerID
ORDER BY C.CustomerID;
library(DT)
datatable(no3,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('List All Customers, whether They Placed Any Order or Not')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
List customers that have not placed orders!
SELECT C.CustomerID, CustomerName, Country, OrderID
FROM customers C
LEFT JOIN orders O
ON C.CustomerID = O.CustomerID
WHERE O.OrderID IS NULL
ORDER BY C.CustomerID;
library(DT)
datatable(no4,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('List Customers That Have Not Placed Orders')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
List products with order quantities greater than 80!
SELECT ProductName
FROM products P
WHERE EXISTS (SELECT *
FROM (SELECT ProductID, SUM(Quantity) AS Kuantitas
FROM orderdetails
GROUP BY ProductID) AS G
WHERE G.ProductID = P.ProductID
AND G.Kuantitas > 80)
library(DT)
datatable(no6,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('List Products with Order Quantities Greater Than 80')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Which products were sold by the unit (i.e. quantity =1)?
SELECT ProductName
FROM products
WHERE ProductID = ANY (SELECT ProductID
FROM orderdetails
WHERE Quantity = 1)
library(DT)
datatable(no7,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Products were Sold by he Unit (i.e. quantity =1)')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
List customers who placed orders that are larger than the average of each customer order!
SELECT 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 P.ProductID = OD.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)
library(DT)
datatable(no8,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('List Customers Who Placed Orders That Are Larger Than the Average of Each Customer Order')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Find best selling products based on quantity!
SELECT P.ProductID, P.ProductName, P.Price, SUM(OD.Quantity) AS Quantity
FROM products P
JOIN orderdetails OD
ON P.ProductID = OD.ProductID
GROUP BY ProductName
ORDER BY Quantity DESC
LIMIT 1;
library(DT)
datatable(no9,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Best Selling Products Based on Quantity')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = 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;
library(DT)
datatable(no10,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Best Selling Products Based on Revenue')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Find best selling products based on revenue for each country!
SELECT ProductName, Country, 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
ORDER BY Country;
library(DT)
datatable(no11,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Best Selling Products Based on Revenue for Each country')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Find suppliers with a product price less than $50!
SELECT SupplierName
FROM suppliers S
WHERE EXISTS
(SELECT ProductName
FROM products P
WHERE P.SupplierID = S.SupplierID
AND Price < 50)
library(DT)
datatable(no12,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Suppliers with A Product Price Less Than $50')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Find top 10 best employees based on their sales quantity!
SELECT FirstName, LastName, SUM(Quantity) AS Sales
FROM employees E
LEFT JOIN orders O
ON E.EmployeeID = O.EmployeeID
LEFT JOIN orderdetails OD
ON O.OrderID = OD.OrderID
GROUP BY FirstName, LastName
ORDER BY SUM(Quantity) DESC
LIMIT 10;
library(DT)
datatable(no13,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Top 10 Best Employees Based on Their Sales Quantity')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Find top 10 best supplier countries based on quantity!
SELECT 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 Country
ORDER BY SUM(Quantity) DESC
LIMIT 10;
library(DT)
datatable(no14,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Top 10 Best Supplier Countries Based on Quantity')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Find top 10 best customer countries based on quantity!
SELECT 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 Country
ORDER BY SUM(Quantity) DESC
LIMIT 10;
library(DT)
datatable(no15,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('Top 10 Best Customer Countries Based on Quantity')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Find top 10 best selling products based on quantity in every year!
(SELECT ProductName, YEAR(O.OrderDate) Year, SUM(OD.Quantity) Quantity
FROM orders O
JOIN orderdetails OD
ON O.OrderID = OD.OrderID
JOIN products P
ON P.ProductID = OD.ProductID
GROUP BY Year, ProductName
HAVING Year = 1996
ORDER BY Year, Quantity DESC
LIMIT 10)
UNION
(SELECT ProductName, YEAR(O.OrderDate) Year, SUM(OD.Quantity) 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)
library(DT)
datatable(no16,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('top 10 Best Selling Products Based on Quantity in Every Year')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
SELECT date_format(OrderDate, '%Y')
FROM orders
Displaying records 1 - 10
| NA |
| NA |
| NA |
| NA |
| NA |
| NA |
| NA |
| NA |
| NA |
| NA |
SELECT P.ProductName, date_format(O.OrderDate, '%Y') 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