1 Connect MySQL

library(RMySQL)
## Loading required package: DBI
library(DBI)
kefas <- dbConnect(RMySQL::MySQL(),
                   dbname='factory_db',
                   username='root',
                   password='',
                   host='localhost',
                   port=3306)
knitr::opts_chunk$set(connection="kefas")

2 Jawaban

2.1 Select Some attributes of suppliers in alphabetical order!

SELECT SupplierName, ContactName, Address
  FROM suppliers
    ORDER BY SupplierName
library(DT)
datatable(no1,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Suppliers In Alphabetical Order')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

2.2 Some attributes of suppliers in reverse alphabetical order!

SELECT SupplierName, ContactName, Address
  FROM suppliers
    ORDER BY SupplierName DESC
library(DT)
datatable(no2,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Suppliers In Reverse Alphabetical Order')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

2.3 Some attributes of suppliers ordered by country, then by city!

SELECT SupplierName, ContactName, Address, Country, City
  FROM suppliers
    ORDER BY Country, City
library(DT)
datatable(no3,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Suppliers Ordered By Country, Then By City')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

2.4 All attributes of suppliers and reverse alphabetical ordered by country, then by city!

SELECT SupplierName, Address, Country, City
  FROM suppliers
    ORDER BY Country DESC, City DESC
library(DT)
datatable(no4,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('Suppliers and Reverse Alphabetical Ordered By Country, Then By City')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

2.5 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;
library(DT)
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)
          )

2.6 Get all but the 10 most expensive products sorted by price!

SELECT ProductID, ProductName, Price, Unit
  FROM products
    ORDER BY Price DESC
      LIMIT 10; 
library(DT)
datatable(no6,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('10 most expensive products sorted by price')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

2.7 Get the 10th to 15th most expensive products sorted by price!

SELECT ProductID, ProductName, Price, Unit
  FROM products
    ORDER BY Price DESC
      LIMIT 9, 6;
library(DT)
datatable(no7,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('10th to 15th most expensive products sorted by price')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

2.8 List all supplier countries in alphabetical order!

SELECT SupplierName, Address, Country
  FROM suppliers
    ORDER BY Country
library(DT)
datatable(no8,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('All Supplier Countries in Alphabetical Order')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

2.9 Find the cheapest product and Expensive Orders!

2.9.1 Cheapest Product

SELECT MIN(Price) 
  FROM products;
library(DT)
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)
          )

2.9.2 Expensive Orders

SELECT O.OrderID, SUM(P.Price*O.Quantity) TotalOrder_Price
  FROM orderdetails O
    JOIN products P
    ON O.ProductID = P.ProductID
      GROUP BY O.OrderID
        ORDER BY TotalOrder_Price DESC
          LIMIT 0,1;
      
library(DT)
datatable(no10,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('The Expensive Order')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

2.10 Find the number of Supplier USA!

SELECT SupplierID, SupplierName, City, Country
  FROM suppliers
    WHERE Country IN ('USA')
      ORDER BY Country
library(DT)
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)
          )

2.11 Compute the total Quantity of orderitem!

SELECT COUNT(Quantity)
  FROM orderdetails
library(DT)
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)
          )

2.12 Compute the average UnitPrice of all product!

SELECT AVG(Price)
  FROM products
library(DT)
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)
          )

2.13 Get all information about customer named Thomas Hardy!

SELECT DISTINCT *
  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 CustomerName='Thomas Hardy'
    ORDER BY OrderDate ASC;
library(DT)
datatable(no14,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('All Information About Customer Named Thomas Hardy')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

2.14 List all customers from Spain or France!

SELECT CustomerName, Address, City, Country
  FROM customers
    WHERE Country
      IN ('Spain', 'France')
library(DT)
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)
          )

2.15 List all customers that are not from the USA!

SELECT *
  FROM customers
    WHERE NOT Country='USA'
library(DT)
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)
          )

2.16 List all orders that not between $50 and $15000!

SELECT *
  FROM products
    WHERE Price
      BETWEEN 50 AND 15000
library(DT)
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)
          )

2.17 List all products between $10 and $20

SELECT *
  FROM products
    WHERE Price
      BETWEEN 10 AND 20
library(DT)
datatable(no18,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('all products between $10 and $20')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

2.18 List all products not between $10 and $100 sorted by price!

SELECT *
  FROM products
    WHERE Price NOT BETWEEN 10 AND 100
      ORDER BY Price
library(DT)
datatable(no19,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('all products not between $10 and $100 sorted by price')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

2.19 Get the list of orders and amount sold between 1996 Jan 01 and 1996 Des 31!

SELECT *
  FROM orders
    WHERE OrderDate
      BETWEEN '1/1/1996' AND '12/32/1996'
library(DT)
datatable(no20,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('list of orders and amount sold between 1996 Jan 01 and 1996 Des 31')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

2.20 List all suppliers from the USA, UK, OR Japan!

SELECT *
  FROM customers
    WHERE Country
      IN ('USA', 'UK', 'Japan')
library(DT)
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)
          )

2.21 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)
library(DT)
datatable(no22,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('ll products that are not exactly $10, $20, $30, $40, or $50')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

2.22 List all customers that are from the same countries as the suppliers!

SELECT *
  FROM customers
    WHERE Country 
      IN (SELECT Country
            FROM suppliers)
library(DT)
datatable(no23,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('all customers that are from the same countries as the suppliers')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )

2.23 List all products that start with ‘Cha’ or ‘Chan’ and have one more character!

SELECT *
  FROM products
    WHERE ProductName LIKE 'Cha_'
      OR ProductName LIKE 'Chan_'
library(DT)
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)
          )

2.24 List all suppliers that do have a fax number!

Pada data yang kami miliki, tidak ada kolom fax number, sehingga dapat kami simpulkan bahwa all suppliers tidak memiliki fax number.

2.25 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
library(DT)
datatable(no26,
          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)
          )

2.26 List total customers in each country.

SELECT COUNT(CustomerID) 
AS TotalCustomer, Country
AS COUNTRY 
  FROM customers
    GROUP BY Country
library(DT)
datatable(no27,
          caption = htmltools::tags$caption(
            style = 'caption-side: bottom; text-align: center;',
            htmltools::em('List Total Customers in Each Country')),
            extensions = 'FixedColumns',
            option = list(scrollX = TRUE, fixedColumns = TRUE)
          )