Database System Week 6

Data Manipulation ~ Simple Query

Email             : sherlytaurinsiri@gmail.com
Instagram     : https://www.instagram.com/sherlytaurin
RPubs            : https://rpubs.com/sherlytaurin/
Github           : https://github.com/sherlytaurin/
Telegram       : @Sherlytaurin

---

Persiapan Data

Load Packages

library(RMySQL)
library(DBI)
library(DT)

Connect ke MySQL

sherly <- dbConnect(MySQL(),
                    user = 'root',
                    password = '',
                    dbname = 'factory_db',
                    host = 'localhost',
                    port = 3306)
knitr::opts_chunk$set(connection = "sherly")  # mengatur connection agar bisa menggunakan SQL Chunk

1 Select Some attributes of suppliers in alphabetical order!

SELECT SupplierID, SupplierName, Address, City, Country
  FROM Suppliers S
     ORDER BY S.SupplierName ASC;

2 Some attributes of suppliers in reverse alphabetical order!

SELECT SupplierID, SupplierName, Address, City, Country
  FROM Suppliers S
     ORDER BY S.SupplierName DESC

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

SELECT SupplierID, SupplierName, Address, City, Country
  FROM Suppliers S
     ORDER BY S.Country ASC, S.City ASC;

4 All atributes of suppliers and reverse alphabetical ordered by country, then by city!

SELECT S.*
  FROM Suppliers S
     ORDER BY S.Country DESC, S.City DESC;

5 All orders, sorted by total amount, the largest first!

SELECT O.*, OD.Quantity
  FROM Orders O
    RIGHT JOIN orderdetails OD
      ON O.OrderID = OD.OrderID
        ORDER BY OD.Quantity DESC;

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

SELECT P.*
  FROM products P
    ORDER BY P.Price DESC
      LIMIT 10;

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

SELECT P.*
  FROM products P
    ORDER BY P.Price DESC
      LIMIT 6 OFFSET 9;

8 List all supplier countries in alphabetical order!

SELECT DISTINCT Country
  FROM suppliers S
    ORDER BY S.Country ASC;

9 Find the cheapest product and Expensive Orders!

9.1 The Cheapest Product

SELECT P.ProductName, P.Unit, P.Price
  FROM products P
      ORDER BY P.Price ASC
        LIMIT 1;

9.2 The Expensive Product

SELECT P.ProductName, P.Unit, P.Price
  FROM products P
      ORDER BY P.Price DESC
        LIMIT 1;

10 Find the number of Supplier USA!

SELECT S.Country, COUNT(S.Country) AS 'Total_Supplier'
  FROM suppliers S
    WHERE S.Country='USA' ;

11 Compute the total Quantity of orderitem!

SELECT SUM(OD.Quantity) AS 'TotalQuantity'
  FROM orderdetails OD
    

12 Compute the average UnitPrice of all product!

SELECT AVG(P.Price) AS 'Average_UnitPrice'
  FROM products P

13 Get all information about customer named Thomas Hardy!

SELECT C.*
  FROM customers C
    WHERE ContactName='Thomas Hardy';

14 List all customers from Spain or France!

SELECT C.*
  FROM customers C
    WHERE Country="Spain" OR Country="France";

15 List all customers that are not from the USA!

SELECT C.*
  FROM customers C
    WHERE NOT Country="USA"

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

SELECT O.*,P.ProductID, P.ProductName, P.Price
  FROM orders O
    LEFT JOIN orderdetails OD
      ON O.OrderID = OD.OrderID
    LEFT JOIN products P
      ON OD.ProductID = P.ProductID
  WHERE P.Price 
    NOT BETWEEN 50 AND 15000;

17 List all products between $10 and $20

SELECT DISTINCT P.*
  FROM products P
    WHERE P.Price 
     BETWEEN 10 AND 20;

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

SELECT DISTINCT P.*
  FROM products P
    WHERE P.Price 
     NOT BETWEEN 10 AND 100
ORDER BY P.Price ASC;

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

SELECT O.OrderID, O.OrderDate, sum(OD.Quantity) AS 'AmountSold'
  FROM orders O
    JOIN orderdetails OD
      ON O.OrderID = OD.OrderID
    GROUP BY O.OrderID
    HAVING O.OrderDate
    BETWEEN '1996-01-01' AND '1996-12-31'
  ORDER BY O.OrderDate;

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

SELECT S.*
  FROM suppliers S
    WHERE S.Country 
      IN ('USA', 'UK', 'JAPAN');

21 List all products that are not exactly $10, $20, $30, $40, or $50!

SELECT P.*
  FROM products P
    WHERE P.Price
      NOT IN (10, 20, 30, 40, 50);

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

SELECT C.CustomerName, S.SupplierName, C.Country AS 'CustomerCountry', S.Country AS 'SupplierCountry'
  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 
      CASE
         WHEN C.Country=S.Country THEN '1'
         ELSE '0'
        END = '1';
    

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

SELECT P.ProductName
  FROM products P
    WHERE ProductName LIKE 'Cha_' OR ProductName LIKE 'Chan_';

24 List all suppliers that do have a fax number!

Because there is no fax number column in the database, we assume that all of the suppliers don’t have any fax number.

25 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
        ) S
  GROUP BY S.CustomerName
    HAVING AverageOrder
      BETWEEN 1000 AND 1200
  ORDER BY AverageOrder ASC;

26 List total customers in each country.

SELECT C.Country, COUNT(C.Country) AS 'TotalCustomer'
  FROM customers C
    GROUP BY C.Country WITH ROLLUP