library(RMySQL)
library(DBI)
library(DT)
vanessa <- dbConnect(RMySQL::MySQL(),
dbname='factory_db',
username='root',
password='',
host='localhost',
port=3306)
List all orders with product names, quantities, and prices!
SELECT OrderID, P.ProductID, ProductName, Quantity, Price
FROM (orderdetails OD
LEFT JOIN products P
ON OD.ProductID = P.ProductID)
ORDER BY OrderID ASC;
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, CustomerName, City, Country, OrderID
FROM customers C
LEFT JOIN orders O
ON C.CustomerID=O.CustomerID
ORDER BY C.CustomerID;
datatable(No3,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('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, City, Country, OrderID
FROM customers C
LEFT JOIN orders O
ON C.CustomerID=O.CustomerID
WHERE O.OrderID IS NULL
ORDER BY C.CustomerID;
datatable(No4,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('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 quantitys
FROM orderdetails
GROUP BY ProductID) AS B
WHERE B.ProductID = P.ProductID
AND B.quantitys>80);
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 P
WHERE ProductID = ANY ( SELECT ProductID
FROM orderdetails
WHERE Quantity = 1
);
datatable(No7,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('products were sold by the 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
FROM ((customers C
LEFT JOIN orders O
ON C.CustomerID=O.CustomerID)
LEFT JOIN orderdetails OD
ON O.OrderID=OD.OrderID)
LEFT JOIN products P
ON OD.ProductID = P.ProductID
WHERE (Quantity*Price) > ALL (SELECT AVG(Quantity*Price)
FROM orderdetails OD
LEFT JOIN products P
ON OD.ProductID = P.ProductID)
GROUP BY CustomerName
datatable(No8,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('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 ProductName
FROM products P
JOIN orderdetails OD
ON P.ProductID=OD.ProductID
GROUP BY ProductName
HAVING SUM(Quantity) >= ALL (SELECT SUM(Quantity)
FROM products P
JOIN orderdetails OD
ON P.ProductID=OD.ProductID
GROUP BY ProductName)
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 DISTINCT ProductName
FROM orderdetails OD
LEFT JOIN products P
ON OD.ProductID = P.ProductID
WHERE P.ProductID = ( SELECT B.ProductID
FROM ( SELECT P.ProductID, SUM(Quantity*Price) Revenue
FROM orderdetails OD
LEFT JOIN products P
ON OD.ProductID = P.ProductID) AS B
GROUP BY B.ProductID
HAVING MAX(B.Revenue))
datatable(No11,
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, MAX(Revenue) Revenue, Country
from
(
SELECT ProductName, sum(Quantity*Price) Revenue, Country
FROM products p
JOIN orderdetails d
ON p.ProductID = d.ProductID
JOIN orders o
ON o.OrderID = d.OrderID
JOIN customers c
ON c.CustomerID = o.CustomerID
GROUP BY ProductName, Country
ORDER BY Revenue DESC
) as t
GROUP BY Country
ORDER BY ProductName
datatable(No12,
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)
datatable(No13,
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 Selling
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
datatable(No14,
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 Selling
FROM (suppliers S
LEFT JOIN products P
ON S.SupplierID = P.SupplierID)
LEFT JOIN orderdetails OD
ON P.ProductID = OD.ProductID
GROUP BY Country
ORDER BY Selling DESC
LIMIT 10;
datatable(No15,
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 AS CustomerCountry, SUM(Quantity) AS Selling
FROM (customers C
LEFT JOIN orders O
ON C.CustomerID=O.CustomerID)
LEFT JOIN orderdetails OD
ON O.OrderID=OD.OrderID
GROUP BY CustomerCountry
ORDER BY Selling DESC
LIMIT 10;
datatable(No16,
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!
1996
(SELECT ProductName, date_format(o.OrderDate, '%Y') SalesYear, sum(Quantity) Quantity
FROM orders o
JOIN orderdetails d
ON o.OrderID = d.OrderID
JOIN products p
ON p.ProductID = d.ProductID
WHERE date_format(o.OrderDate, '%Y') = 1996
GROUP BY ProductName
ORDER BY SalesYear, Quantity DESC
LIMIT 10)
UNION
(SELECT ProductName, date_format(o.OrderDate, '%Y') SalesYear, sum(Quantity) Quantity
FROM orders o
JOIN orderdetails d
ON o.OrderID = d.OrderID
JOIN products p
ON p.ProductID = d.ProductID
WHERE date_format(o.OrderDate, '%Y') = 1997
GROUP BY ProductName
ORDER BY SalesYear, Quantity DESC
LIMIT 10);
datatable(No17,
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)
)