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.
Select Some attributes of suppliers in alphabetical order!
SELECT SupplierID, 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 order item!
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!
Tidak ada kolom fax number pada kolom supplier
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)
)