library(RMySQL)
library(DBI)
library(DT)
vanessa <- dbConnect(RMySQL::MySQL(),
dbname='factory_db',
username='root',
password='',
host='localhost',
port=3306)
Select some atttributes 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('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, Address, City, Country
FROM suppliers
ORDER BY SupplierName DESC;
datatable(No2,
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, Address, City, Country
FROM suppliers
ORDER BY Country, City;
datatable(No3,
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(No4,
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 OrderDetailID, OrderID, OD.ProductID, (Quantity*Price) AS TotalAmount
FROM orderdetails OD
LEFT JOIN products P
ON OD.ProductID = P.ProductID
ORDER BY TotalAmount DESC;
datatable(No5,
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(No6,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('the 10 most expensive products')),
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 10, 5;
datatable(No7,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('10th to 15th most expensive products')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
List all supplier countries in alphabetical order!
SELECT DISTINCT Country
FROM suppliers
ORDER BY Country ASC;
datatable(No8,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('10th to 15th most expensive products')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Find the cheapest product and Expensive Orders!
The Cheapest Products
SELECT MIN(Price) AS CheapestPrice , ProductName
FROM products;
datatable(No9,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('the cheapest product')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
The Expensive Orders
SELECT O.OrderID, SUM(P.Price*O.Quantity) TotalOrderPrice
FROM orderdetails O
JOIN products P
ON O.ProductID=P.ProductID
GROUP BY O.OrderID
ORDER BY TotalOrderPrice DESC
LIMIT 0,1;
datatable(No10,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('the Expensive Orders')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)
Find the number of Supplier USA!
SELECT COUNT(SupplierID) AS NumberOfSupplier, Country
FROM suppliers
WHERE Country='USA';
datatable(No11,
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 SUM(Quantity) AS TotalOrder
FROM orderdetails;
datatable(No12,
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) AS UnitPriceAverage
FROM products;
datatable(No13,
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')
datatable(No15,
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(No16,
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*Quantity) AS TotalOrderPrice
FROM orderdetails O
JOIN products P
ON O.ProductID = P.ProductID
GROUP BY OrderID
HAVING TotalOrderPrice
NOT BETWEEN 50 AND 15000;
datatable(No17,
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(No18,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('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 ProductName, Unit, Price
FROM products
WHERE Price
BETWEEN 10 AND 100
ORDER BY Price ASC;
datatable(No19,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('all products not between $10 and $100')),
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*Price) TotalAmount
FROM ((orders O
LEFT JOIN orderdetails OD
ON O.OrderID = OD.OrderID)
LEFT JOIN Products P
ON OD.ProductID = P.ProductID)
GROUP BY O.OrderID
HAVING OrderDate
BETWEEN '01/01/1996' AND '31/12/1996';
datatable(No20,
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='USA' OR Country='UK' OR Country='Japan'
datatable(No21,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('all suppliers from the 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(No22,
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 CustomerID, CustomerName, Country
FROM customers
WHERE Country
IN (SELECT Country FROM suppliers)
datatable(No23,
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 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(No24,
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!
Dari data supplier yang kita miliki kolom nomor Fax. jadi dapat ditarik kesimpulan bahwa supplier tidak memiliki nomor fax.
List all customer with average orders between $1000 and $1200 !
SELECT AVG(Quantity*Price), 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
GROUP BY CustomerName
HAVING AVG(Quantity*Price)
BETWEEN 1000 AND 1200
datatable(No25,
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 COUNT(CustomerID) AS TotalCustomer, Country AS Nation
FROM customers
GROUP BY Country
datatable(No26,
caption = htmltools::tags$caption(
style = 'caption-side: bottom; text-align: center;',
htmltools::em('total customers in each country')),
extensions = 'FixedColumns',
option = list(scrollX = TRUE, fixedColumns = TRUE)
)