menggunakan library

library(DBI)
## Warning: package 'DBI' was built under R version 4.5.2
library(ggplot2)
library(scales)

mengkoneksikan R ke mySQL

con <- DBI::dbConnect(odbc::odbc(),
                       Driver = "MySQL ODBC 8.0 ANSI Driver",
                       Server = "127.0.0.1",
                       Database = "classicmodels",
                       UID = "root",
                       PWD = "Arifnuri12.",
                       Port = 3306)
  1. Visualisasikan dalam warna biru jumlah items untuk setiap product scale
data_1 <- dbGetQuery(con, "SELECT productScale, 
                     COUNT(*) AS totalItems
                     FROM products
                     GROUP BY productScale")

data_1
##   productScale totalItems
## 1         1:10          6
## 2         1:12          9
## 3         1:18         42
## 4         1:72          4
## 5         1:24         27
## 6         1:32          8
## 7         1:50          4
## 8        1:700         10
ggplot(data_1, aes(x = productScale, y= totalItems)) + 
  geom_bar(stat = "identity", fill = "blue", alpha= 1) + 
  theme_minimal() +
  labs(title = "Total item per skala prouk",
       x = "Skala",
       y = "total item")
## Don't know how to automatically pick scale for object of type <integer64>.
## Defaulting to continuous.

2.Siapkan sebuah line plot dengan label yang sesuai untuk total payments setiap bulannya pada tahun 2004!

data_2 <- dbGetQuery(con,"SELECT MONTH(paymentDate) AS month, 
                    SUM(amount) AS total_payment
                    FROM payments
                    WHERE YEAR(paymentDate) = 2004
                    GROUP BY MONTH(paymentDate)
                    ORDER BY month")

data_2$bulan <- as.Date(paste0("2004-", data_2$month,"-01"))

ggplot(data_2, aes(x = bulan, y = total_payment, group = 1)) +
  geom_line(color= "red") +
  labs(
    title = "Total Payments per Month in 2004",
    x = "Month",
    y = "Total Payments"
  )

  1. Buatlah sebuah histogram dengan label yang sesuai untuk nilai (value) dari orders yang diterima dari negara-negara Nordic (Denmark, Finland, Norway, Sweden)!
data_3 <- dbGetQuery(con, "SELECT country, SUM(quantityOrdered * priceEach) AS value
                    FROM orderdetails
                     JOIN orders USING(orderNumber)
                     JOIN customers USING(customerNumber) 
                     WHERE COUNTRY IN ('Denmark', 'Finland', 'Norway', 'Sweden')
                     GROUP BY country")
data_3
##   country    value
## 1  Norway 270846.3
## 2  Sweden 187638.4
## 3 Denmark 218994.9
## 4 Finland 295149.3
ggplot(data_3, aes(x = country, y= value)) + 
  geom_bar(stat = "identity", fill = "blue", alpha= 1) + 
  theme_minimal() +
  labs(title = "value order dari negara nordic",
       x = "country",
       y = "value")

  1. Buatlah sebuah heatmap untuk product lines dan kota-kota di Norway (Norwegian cities)!
data_4 <- dbGetQuery(con,"
SELECT 
    c.city,
    p.productLine,
    SUM(od.quantityOrdered * od.priceEach) AS total_sales
FROM customers c
JOIN orders o USING(customerNumber)
JOIN orderdetails od USING(orderNumber)
JOIN products p USING(productCode)
WHERE c.country = 'Norway'
GROUP BY c.city, p.productLine
")

data_4
##       city      productLine total_sales
## 1  Stavern     Classic Cars    36941.34
## 2  Stavern Trucks and Buses    26455.99
## 3  Stavern     Vintage Cars    17436.80
## 4  Stavern      Motorcycles    19367.70
## 5  Stavern           Trains     4022.96
## 6   Bergen     Classic Cars    55069.55
## 7   Bergen      Motorcycles    27167.72
## 8   Bergen           Planes     2787.19
## 9   Bergen     Vintage Cars    12538.01
## 10    Oslo           Planes    25719.05
## 11    Oslo     Vintage Cars     6541.11
## 12    Oslo     Classic Cars    36798.88
library(ggplot2)

ggplot(data_4, aes(x = city, y = productLine, fill = total_sales)) +
  geom_tile() +
  scale_fill_gradient(low = "red", high = "green") +
  theme_minimal() +
  labs(
    title = "Heatmap of Product Lines vs Norwegian Cities",
    x = "City (Norway)",
    y = "Product Line",
    fill = "Total Sales"
  )