SIM Modul 3
Angwin Meilisa Aulia
2026-03-11
Jumlah Item Tiap Product Scale
# Memuat paket yang diperlukan untuk koneksi database dan visualisasi
library(DBI)
library(ggplot2)
library(scales)Pendahuluan
Laporan ini menyajikan bahanyaknya item untuk tiap product scale dari database “classicmodels”. Proses ini mengintegrasikan SQL untuk pengambilan dara dan visualisasi.
Koneksi Databased dan Pengambilan Data
con <- DBI::dbConnect(odbc::odbc(),
Driver = "MySQL ODBC 8.0 ANSI Driver",
Server = "127.0.0.1",
Database = "classicmodels",
UID = "root",
PWD = "deksol1234",
Port = 3306)Setelah koneksi terjalin, kita menggunakan blok kode SQL untuk mendapatkan banyaknya item untuk tiap product scale. Simpan data hasil kueri data dalam suatu data frame R
# Mendapatkan data yang diperlukan
data1 <- dbGetQuery(con, "SELECT productScale, COUNT(*) AS totalItems FROM products GROUP BY productScale")
data1## 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 10Visualisasi banyaknya item tiap product scale
Kita menggunakan library ggplot2 untuk membuat barchart. Fungsi geom_bar() digunakan untuk melihat banyaknya item tiap product scale.
# Membuat barchart untuk banyaknya item tiap product scale
ggplot(data1,
aes(x = productScale, y = totalItems)) + geom_bar(stat = "identity", fill = "blue", alpha = 1) + theme_minimal() + labs(title = "Total Item per Skala Produk",
x = "Skala Produk",
y = "Total Item") + theme(axis.text.x = element_text(angle = 45, hjust = 1))
Latihan Soal No. 3
query3 = "SELECT c.country, o.orderNumber, od.quantityOrdered, od.priceEach, (od.quantityOrdered * od.priceEach) AS value FROM customers c JOIN orders o ON c.customerNumber = o.customerNumber JOIN orderdetails od ON o.orderNumber = od.orderNumber WHERE c.country IN ('Denmark','Finland','Norway','Sweden')"
dt = dbGetQuery(con, query3)
dt## country orderNumber quantityOrdered priceEach value
## 1 Norway 10103 26 214.30 5571.80
## 2 Norway 10103 42 119.67 5026.14
## 3 Norway 10103 27 121.64 3284.28
## 4 Norway 10103 35 94.50 3307.50
## 5 Norway 10103 22 58.34 1283.48
## 6 Norway 10103 27 92.19 2489.13
## 7 Norway 10103 35 61.84 2164.40
## 8 Norway 10103 25 86.92 2173.00
## 9 Norway 10103 46 86.31 3970.26
## 10 Norway 10103 36 98.07 3530.52
## 11 Norway 10103 41 40.75 1670.75
## 12 Norway 10103 36 107.34 3864.24
## 13 Norway 10103 25 88.62 2215.50
## 14 Norway 10103 31 92.46 2866.26
## 15 Norway 10103 45 63.35 2850.75
## 16 Norway 10103 42 94.07 3950.94
## 17 Norway 10158 22 67.79 1491.38
## 18 Norway 10309 41 94.74 3884.34
## 19 Norway 10309 26 144.60 3759.60
## 20 Norway 10309 21 96.92 2035.32
## 21 Norway 10309 24 59.56 1429.44
## 22 Norway 10309 50 93.89 4694.50
## 23 Norway 10309 28 74.04 2073.12
## 24 Norway 10325 47 111.52 5241.44
## 25 Norway 10325 42 193.25 8116.50
## 26 Norway 10325 24 166.10 3986.40
## 27 Norway 10325 24 114.74 2753.76
## 28 Norway 10325 44 114.73 5048.12
## 29 Norway 10325 38 44.37 1686.06
## 30 Norway 10325 28 55.30 1548.40
## 31 Norway 10325 38 99.55 3782.90
## 32 Norway 10325 44 56.24 2474.56
## 33 Sweden 10112 29 197.16 5717.64
## 34 Sweden 10112 23 85.10 1957.30
## 35 Sweden 10320 31 184.84 5730.04
## 36 Sweden 10320 35 102.17 3575.95
## 37 Sweden 10320 38 63.84 2425.92
## 38 Sweden 10320 25 139.64 3491.00
## 39 Sweden 10320 26 60.62 1576.12
## 40 Sweden 10326 32 94.79 3033.28
## 41 Sweden 10326 50 73.73 3686.50
## 42 Sweden 10326 41 120.43 4937.63
## 43 Sweden 10326 41 86.74 3556.34
## 44 Sweden 10326 20 81.34 1626.80
## 45 Sweden 10326 39 60.67 2366.13
## 46 Sweden 10334 26 130.01 3380.26
## 47 Sweden 10334 46 108.00 4968.00
## 48 Sweden 10334 34 52.87 1797.58
## 49 Sweden 10334 20 147.33 2946.60
## 50 Sweden 10334 49 101.71 4983.79
## 51 Sweden 10334 42 117.57 4937.94
## 52 Denmark 10105 50 127.84 6392.00
## 53 Denmark 10105 41 205.72 8434.52
## 54 Denmark 10105 29 141.88 4114.52
## 55 Denmark 10105 22 136.59 3004.98
## 56 Denmark 10105 38 87.73 3333.74
## 57 Denmark 10105 41 75.48 3094.68
## 58 Denmark 10105 43 117.97 5072.71
## 59 Denmark 10105 44 73.46 3232.24
## 60 Denmark 10105 50 75.47 3773.50
## 61 Denmark 10105 41 54.00 2214.00
## 62 Denmark 10105 29 86.61 2511.69
## 63 Denmark 10105 31 60.72 1882.32
## 64 Denmark 10105 39 92.16 3594.24
## 65 Denmark 10105 22 99.31 2184.82
## 66 Denmark 10105 25 44.77 1119.25
## 67 Denmark 10238 28 161.49 4521.72
## 68 Denmark 10238 29 104.52 3031.08
## 69 Denmark 10238 20 73.42 1468.40
## 70 Denmark 10238 41 68.35 2802.35
## 71 Denmark 10238 49 144.05 7058.45
## 72 Denmark 10238 44 120.53 5303.32
## 73 Denmark 10238 47 53.88 2532.36
## 74 Denmark 10238 22 67.91 1494.02
## 75 Denmark 10256 34 93.49 3178.66
## 76 Denmark 10256 29 52.83 1532.07
## 77 Denmark 10327 25 154.54 3863.50
## 78 Denmark 10327 45 74.34 3345.30
## 79 Denmark 10327 25 74.84 1871.00
## 80 Denmark 10327 20 79.68 1593.60
## 81 Denmark 10327 21 65.05 1366.05
## 82 Denmark 10327 43 85.14 3661.02
## 83 Denmark 10327 37 83.42 3086.54
## 84 Denmark 10327 37 48.05 1777.85
## 85 Denmark 10406 61 124.56 7598.16
## 86 Denmark 10406 48 133.72 6418.56
## 87 Denmark 10406 65 117.26 7621.90
## 88 Norway 10181 27 155.66 4202.82
## 89 Norway 10181 28 113.92 3189.76
## 90 Norway 10181 20 67.03 1340.60
## 91 Norway 10181 36 107.10 3855.60
## 92 Norway 10181 44 124.56 5480.64
## 93 Norway 10181 42 124.44 5226.48
## 94 Norway 10181 22 74.69 1643.18
## 95 Norway 10181 21 129.45 2718.45
## 96 Norway 10181 27 130.68 3528.36
## 97 Norway 10181 45 147.33 6629.85
## 98 Norway 10181 30 73.17 2195.10
## 99 Norway 10181 22 120.53 2651.66
## 100 Norway 10181 39 137.04 5344.56
## 101 Norway 10181 34 45.28 1539.52
## 102 Norway 10181 37 32.85 1215.45
## 103 Norway 10181 23 54.49 1253.27
## 104 Norway 10181 25 122.17 3054.25
## 105 Norway 10188 48 95.70 4593.60
## 106 Norway 10188 38 111.80 4248.40
## 107 Norway 10188 45 182.04 8191.80
## 108 Norway 10188 32 52.09 1666.88
## 109 Norway 10188 25 95.80 2395.00
## 110 Norway 10188 40 61.70 2468.00
## 111 Norway 10188 44 81.91 3604.04
## 112 Norway 10188 29 96.11 2787.19
## 113 Norway 10289 38 92.47 3513.86
## 114 Norway 10289 24 44.75 1074.00
## 115 Norway 10289 43 141.75 6095.25
## 116 Norway 10289 45 41.22 1854.90
## 117 Finland 10155 32 129.20 4134.40
## 118 Finland 10155 38 138.77 5273.26
## 119 Finland 10155 44 83.44 3671.36
## 120 Finland 10155 29 105.87 3070.23
## 121 Finland 10155 23 62.34 1433.82
## 122 Finland 10155 34 56.55 1922.70
## 123 Finland 10155 37 76.31 2823.47
## 124 Finland 10155 44 58.69 2582.36
## 125 Finland 10155 32 89.61 2867.52
## 126 Finland 10155 20 87.75 1755.00
## 127 Finland 10155 43 76.80 3302.40
## 128 Finland 10155 44 70.33 3094.52
## 129 Finland 10155 34 49.16 1671.44
## 130 Finland 10299 23 76.56 1760.88
## 131 Finland 10299 29 164.61 4773.69
## 132 Finland 10299 24 123.51 2964.24
## 133 Finland 10299 39 62.17 2424.63
## 134 Finland 10299 49 119.04 5832.96
## 135 Finland 10299 47 107.07 5032.29
## 136 Finland 10299 33 58.87 1942.71
## 137 Finland 10299 32 66.29 2121.28
## 138 Finland 10299 24 36.21 869.04
## 139 Finland 10299 38 84.70 3218.60
## 140 Finland 10299 44 77.29 3400.76
## 141 Finland 10377 24 65.44 1570.56
## 142 Finland 10377 50 112.86 5643.00
## 143 Finland 10377 35 124.56 4359.60
## 144 Finland 10377 31 61.60 1909.60
## 145 Finland 10377 36 125.18 4506.48
## 146 Finland 10377 39 143.94 5613.66
## 147 Denmark 10161 28 121.72 3408.16
## 148 Denmark 10161 43 102.04 4387.72
## 149 Denmark 10161 48 139.41 6691.68
## 150 Denmark 10161 23 125.40 2884.20
## 151 Denmark 10161 36 132.80 4780.80
## 152 Denmark 10161 25 62.72 1568.00
## 153 Denmark 10161 37 73.49 2719.13
## 154 Denmark 10161 23 47.29 1087.67
## 155 Denmark 10161 20 82.69 1653.80
## 156 Denmark 10161 25 108.04 2701.00
## 157 Denmark 10161 20 72.77 1455.40
## 158 Denmark 10161 30 94.23 2826.90
## 159 Denmark 10314 38 176.63 6711.94
## 160 Denmark 10314 46 125.40 5768.40
## 161 Denmark 10314 36 169.56 6104.16
## 162 Denmark 10314 45 95.99 4319.55
## 163 Denmark 10314 42 135.90 5707.80
## 164 Denmark 10314 20 129.76 2595.20
## 165 Denmark 10314 23 84.71 1948.33
## 166 Denmark 10314 29 129.26 3748.54
## 167 Denmark 10314 44 51.44 2263.36
## 168 Denmark 10314 39 31.82 1240.98
## 169 Denmark 10314 38 111.18 4224.84
## 170 Denmark 10314 35 58.41 2044.35
## 171 Denmark 10314 28 115.75 3241.00
## 172 Denmark 10314 38 50.38 1914.44
## 173 Denmark 10314 23 83.15 1912.45
## 174 Norway 10284 45 137.19 6173.55
## 175 Norway 10284 31 68.43 2121.33
## 176 Norway 10284 22 101.76 2238.72
## 177 Norway 10284 30 65.08 1952.40
## 178 Norway 10284 39 59.83 2333.37
## 179 Norway 10284 21 65.51 1375.71
## 180 Norway 10284 21 66.65 1399.65
## 181 Norway 10284 50 60.54 3027.00
## 182 Norway 10284 33 35.78 1180.74
## 183 Norway 10284 24 87.69 2104.56
## 184 Norway 10284 45 95.73 4307.85
## 185 Norway 10284 25 68.00 1700.00
## 186 Norway 10284 32 73.29 2345.28
## 187 Norway 10301 37 114.65 4242.05
## 188 Norway 10301 32 118.22 3783.04
## 189 Norway 10301 47 119.49 5616.03
## 190 Norway 10301 22 113.52 2497.44
## 191 Norway 10301 23 135.47 3115.81
## 192 Norway 10301 39 137.04 5344.56
## 193 Norway 10301 27 64.67 1746.09
## 194 Norway 10301 22 40.75 896.50
## 195 Norway 10301 48 32.10 1540.80
## 196 Norway 10301 22 86.73 1908.06
## 197 Norway 10301 50 122.17 6108.50
## 198 Finland 10151 24 114.95 2758.80
## 199 Finland 10151 43 152.27 6547.61
## 200 Finland 10151 49 106.78 5232.22
## 201 Finland 10151 39 58.34 2275.26
## 202 Finland 10151 21 167.65 3520.65
## 203 Finland 10151 42 109.90 4615.80
## 204 Finland 10151 30 29.35 880.50
## 205 Finland 10151 27 84.75 2288.25
## 206 Finland 10151 41 43.29 1774.89
## 207 Finland 10151 26 108.81 2829.06
## 208 Finland 10239 21 100.19 2103.99
## 209 Finland 10239 46 70.07 3223.22
## 210 Finland 10239 47 135.47 6367.09
## 211 Finland 10239 20 32.47 649.40
## 212 Finland 10239 29 133.41 3868.89
## 213 Finland 10373 39 118.32 4614.48
## 214 Finland 10373 28 143.50 4018.00
## 215 Finland 10373 22 75.70 1665.40
## 216 Finland 10373 50 99.52 4976.00
## 217 Finland 10373 38 58.92 2238.96
## 218 Finland 10373 33 82.31 2716.23
## 219 Finland 10373 46 53.92 2480.32
## 220 Finland 10373 23 83.86 1928.78
## 221 Finland 10373 39 62.10 2421.90
## 222 Finland 10373 44 58.00 2552.00
## 223 Finland 10373 32 76.94 2462.08
## 224 Finland 10373 41 69.39 2844.99
## 225 Finland 10373 34 94.16 3201.44
## 226 Finland 10373 37 83.42 3086.54
## 227 Finland 10373 45 68.11 3064.95
## 228 Finland 10373 25 44.20 1105.00
## 229 Finland 10373 29 48.05 1393.45
## 230 Finland 10141 21 114.95 2413.95
## 231 Finland 10141 39 160.46 6257.94
## 232 Finland 10141 47 103.09 4845.23
## 233 Finland 10141 34 143.94 4893.96
## 234 Finland 10141 20 50.86 1017.20
## 235 Finland 10141 21 32.18 675.78
## 236 Finland 10141 40 104.09 4163.60
## 237 Finland 10141 24 53.03 1272.72
## 238 Finland 10141 44 94.92 4176.48
## 239 Finland 10247 44 195.33 8594.52
## 240 Finland 10247 25 140.50 3512.50
## 241 Finland 10247 27 167.83 4531.41
## 242 Finland 10247 48 143.62 6893.76
## 243 Finland 10247 40 58.41 2336.40
## 244 Finland 10247 49 51.55 2525.95
## 245 Finland 10363 33 180.95 5971.35
## 246 Finland 10363 34 106.87 3633.58
## 247 Finland 10363 34 68.63 2333.42
## 248 Finland 10363 46 103.64 4767.44
## 249 Finland 10363 22 61.60 1355.20
## 250 Finland 10363 46 69.15 3180.90
## 251 Finland 10363 24 124.94 2998.56
## 252 Finland 10363 32 52.22 1671.04
## 253 Finland 10363 28 123.50 3458.00
## 254 Finland 10363 21 70.08 1471.68
## 255 Finland 10363 43 56.10 2412.30
## 256 Finland 10363 21 52.05 1093.05
## 257 Finland 10363 31 113.75 3526.25
## 258 Finland 10363 43 75.99 3267.57
## 259 Finland 10363 50 92.90 4645.00
## 260 Sweden 10167 44 123.76 5445.44
## 261 Sweden 10167 43 141.92 6102.56
## 262 Sweden 10167 46 69.68 3205.28
## 263 Sweden 10167 34 84.70 2879.80
## 264 Sweden 10167 33 110.60 3649.80
## 265 Sweden 10167 21 54.81 1151.01
## 266 Sweden 10167 20 77.00 1540.00
## 267 Sweden 10167 32 64.44 2062.08
## 268 Sweden 10167 29 73.80 2140.20
## 269 Sweden 10167 43 66.00 2838.00
## 270 Sweden 10167 29 87.80 2546.20
## 271 Sweden 10167 46 62.16 2859.36
## 272 Sweden 10167 24 85.14 2043.36
## 273 Sweden 10167 28 83.42 2335.76
## 274 Sweden 10167 40 42.71 1708.40
## 275 Sweden 10167 38 43.68 1659.84
## 276 Sweden 10291 37 210.01 7770.37
## 277 Sweden 10291 30 141.83 4254.90
## 278 Sweden 10291 41 123.00 5043.00
## 279 Sweden 10291 41 96.84 3970.44
## 280 Sweden 10291 26 52.26 1358.76
## 281 Sweden 10291 47 99.28 4666.16
## 282 Sweden 10291 37 56.21 2079.77
## 283 Sweden 10291 23 93.20 2143.60
## 284 Sweden 10291 48 96.86 4649.28
## 285 Sweden 10291 29 45.28 1313.12
## 286 Sweden 10291 48 109.90 5275.20
## 287 Sweden 10291 26 82.83 2153.58
## 288 Sweden 10291 32 53.00 1696.00
## 289 Sweden 10291 28 86.99 2435.72
## 290 Sweden 10389 26 182.90 4755.40
## 291 Sweden 10389 25 95.13 2378.25
## 292 Sweden 10389 36 76.61 2757.96
## 293 Sweden 10389 47 102.49 4817.03
## 294 Sweden 10389 49 63.91 3131.59
## 295 Sweden 10389 39 52.09 2031.51
## 296 Sweden 10389 45 112.70 5071.50
## 297 Sweden 10389 49 61.70 3023.30ggplot(dt, aes(x = value)) +
geom_histogram(binwidth = 500, fill = "coral", color = "black") + theme_minimal() +
labs(title = "Value Order Dari Negara Nordic",
x = "Order Value",
y = "Frekuensi")