Team Based Project 1
kelompok 8
2022-06-07
Nama Anggota Kelompok
- Adigama Tri N (M0720002)
- Aulia Bunga S (M0720014)
- Fadlyansyah (M0720028)
- Regita Diah Pramesti (M0720057)
- Ulayya Salma Habibah (M0720067)
Mengkoneksikan R dengan MySQL
library(DBI)## Warning: package 'DBI' was built under R version 4.1.3con2 <- DBI::dbConnect(odbc::odbc(),
Driver = "MySQL ODBC 8.0 ANSI Driver",
Server = "127.0.0.1",
Database = "classicmodels",
UID = "root",
PWD = "LaptopkuLenovo123",
Port = 3306)Nomor 1
Ambil data dari database classicmodels dengan ketentuan sebagai berikut: Tampilkan data yang berisi jumlah harga yang diperoleh dari tiap productCode (tampilkan semua atribut yang memungkinkan).
- Sintaks R untuk menampilkan data yang berisi jumlah harga yang diperoleh dari setiap productCode dengan menampilkan semua atribut yang memungkinkan.
harga <- dbGetQuery(con2,"SELECT productCode,productName,productline, priceEach,SUM(quantityOrdered),(SUM(quantityOrdered)*priceEach) AS JumlahHarga FROM products JOIN orderdetails USING (productCode) GROUP BY productCode")
harga## productCode productName productline
## 1 S10_1678 1969 Harley Davidson Ultimate Chopper Motorcycles
## 2 S10_1949 1952 Alpine Renault 1300 Classic Cars
## 3 S10_2016 1996 Moto Guzzi 1100i Motorcycles
## 4 S10_4698 2003 Harley-Davidson Eagle Drag Bike Motorcycles
## 5 S10_4757 1972 Alfa Romeo GTA Classic Cars
## 6 S10_4962 1962 LanciaA Delta 16V Classic Cars
## 7 S12_1099 1968 Ford Mustang Classic Cars
## 8 S12_1108 2001 Ferrari Enzo Classic Cars
## 9 S12_1666 1958 Setra Bus Trucks and Buses
## 10 S12_2823 2002 Suzuki XREO Motorcycles
## 11 S12_3148 1969 Corvair Monza Classic Cars
## 12 S12_3380 1968 Dodge Charger Classic Cars
## 13 S12_3891 1969 Ford Falcon Classic Cars
## 14 S12_3990 1970 Plymouth Hemi Cuda Classic Cars
## 15 S12_4473 1957 Chevy Pickup Trucks and Buses
## 16 S12_4675 1969 Dodge Charger Classic Cars
## 17 S18_1097 1940 Ford Pickup Truck Trucks and Buses
## 18 S18_1129 1993 Mazda RX-7 Classic Cars
## 19 S18_1342 1937 Lincoln Berline Vintage Cars
## 20 S18_1367 1936 Mercedes-Benz 500K Special Roadster Vintage Cars
## 21 S18_1589 1965 Aston Martin DB5 Classic Cars
## 22 S18_1662 1980s Black Hawk Helicopter Planes
## 23 S18_1749 1917 Grand Touring Sedan Vintage Cars
## 24 S18_1889 1948 Porsche 356-A Roadster Classic Cars
## 25 S18_1984 1995 Honda Civic Classic Cars
## 26 S18_2238 1998 Chrysler Plymouth Prowler Classic Cars
## 27 S18_2248 1911 Ford Town Car Vintage Cars
## 28 S18_2319 1964 Mercedes Tour Bus Trucks and Buses
## 29 S18_2325 1932 Model A Ford J-Coupe Vintage Cars
## 30 S18_2432 1926 Ford Fire Engine Trucks and Buses
## 31 S18_2581 P-51-D Mustang Planes
## 32 S18_2625 1936 Harley Davidson El Knucklehead Motorcycles
## 33 S18_2795 1928 Mercedes-Benz SSK Vintage Cars
## 34 S18_2870 1999 Indy 500 Monte Carlo SS Classic Cars
## 35 S18_2949 1913 Ford Model T Speedster Vintage Cars
## 36 S18_2957 1934 Ford V8 Coupe Vintage Cars
## 37 S18_3029 1999 Yamaha Speed Boat Ships
## 38 S18_3136 18th Century Vintage Horse Carriage Vintage Cars
## 39 S18_3140 1903 Ford Model A Vintage Cars
## 40 S18_3232 1992 Ferrari 360 Spider red Classic Cars
## 41 S18_3259 Collectable Wooden Train Trains
## 42 S18_3278 1969 Dodge Super Bee Classic Cars
## 43 S18_3320 1917 Maxwell Touring Car Vintage Cars
## 44 S18_3482 1976 Ford Gran Torino Classic Cars
## 45 S18_3685 1948 Porsche Type 356 Roadster Classic Cars
## 46 S18_3782 1957 Vespa GS150 Motorcycles
## 47 S18_3856 1941 Chevrolet Special Deluxe Cabriolet Vintage Cars
## 48 S18_4027 1970 Triumph Spitfire Classic Cars
## 49 S18_4409 1932 Alfa Romeo 8C2300 Spider Sport Vintage Cars
## 50 S18_4522 1904 Buick Runabout Vintage Cars
## 51 S18_4600 1940s Ford truck Trucks and Buses
## 52 S18_4668 1939 Cadillac Limousine Vintage Cars
## 53 S18_4721 1957 Corvette Convertible Classic Cars
## 54 S18_4933 1957 Ford Thunderbird Classic Cars
## 55 S24_1046 1970 Chevy Chevelle SS 454 Classic Cars
## 56 S24_1444 1970 Dodge Coronet Classic Cars
## 57 S24_1578 1997 BMW R 1100 S Motorcycles
## 58 S24_1628 1966 Shelby Cobra 427 S/C Classic Cars
## 59 S24_1785 1928 British Royal Navy Airplane Planes
## 60 S24_1937 1939 Chevrolet Deluxe Coupe Vintage Cars
## 61 S24_2000 1960 BSA Gold Star DBD34 Motorcycles
## 62 S24_2011 18th century schooner Ships
## 63 S24_2022 1938 Cadillac V-16 Presidential Limousine Vintage Cars
## 64 S24_2300 1962 Volkswagen Microbus Trucks and Buses
## 65 S24_2360 1982 Ducati 900 Monster Motorcycles
## 66 S24_2766 1949 Jaguar XK 120 Classic Cars
## 67 S24_2840 1958 Chevy Corvette Limited Edition Classic Cars
## 68 S24_2841 1900s Vintage Bi-Plane Planes
## 69 S24_2887 1952 Citroen-15CV Classic Cars
## 70 S24_2972 1982 Lamborghini Diablo Classic Cars
## 71 S24_3151 1912 Ford Model T Delivery Wagon Vintage Cars
## 72 S24_3191 1969 Chevrolet Camaro Z28 Classic Cars
## 73 S24_3371 1971 Alpine Renault 1600s Classic Cars
## 74 S24_3420 1937 Horch 930V Limousine Vintage Cars
## 75 S24_3432 2002 Chevy Corvette Classic Cars
## 76 S24_3816 1940 Ford Delivery Sedan Vintage Cars
## 77 S24_3856 1956 Porsche 356A Coupe Classic Cars
## 78 S24_3949 Corsair F4U ( Bird Cage) Planes
## 79 S24_3969 1936 Mercedes Benz 500k Roadster Vintage Cars
## 80 S24_4048 1992 Porsche Cayenne Turbo Silver Classic Cars
## 81 S24_4258 1936 Chrysler Airflow Vintage Cars
## 82 S24_4278 1900s Vintage Tri-Plane Planes
## 83 S24_4620 1961 Chevrolet Impala Classic Cars
## 84 S32_1268 1980<U+0092>s GM Manhattan Express Trucks and Buses
## 85 S32_1374 1997 BMW F650 ST Motorcycles
## 86 S32_2206 1982 Ducati 996 R Motorcycles
## 87 S32_2509 1954 Greyhound Scenicruiser Trucks and Buses
## 88 S32_3207 1950's Chicago Surface Lines Streetcar Trains
## 89 S32_3522 1996 Peterbilt 379 Stake Bed with Outrigger Trucks and Buses
## 90 S32_4289 1928 Ford Phaeton Deluxe Vintage Cars
## 91 S32_4485 1974 Ducati 350 Mk3 Desmo Motorcycles
## 92 S50_1341 1930 Buick Marquette Phaeton Vintage Cars
## 93 S50_1392 Diamond T620 Semi-Skirted Tanker Trucks and Buses
## 94 S50_1514 1962 City of Detroit Streetcar Trains
## 95 S50_4713 2002 Yamaha YZR M1 Motorcycles
## 96 S700_1138 The Schooner Bluenose Ships
## 97 S700_1691 American Airlines: B767-300 Planes
## 98 S700_1938 The Mayflower Ships
## 99 S700_2047 HMS Bounty Ships
## 100 S700_2466 America West Airlines B757-200 Planes
## 101 S700_2610 The USS Constitution Ship Ships
## 102 S700_2824 1982 Camaro Z28 Classic Cars
## 103 S700_2834 ATA: B757-300 Planes
## 104 S700_3167 F/A 18 Hornet 1/72 Planes
## 105 S700_3505 The Titanic Ships
## 106 S700_3962 The Queen Mary Ships
## 107 S700_4002 American Airlines: MD-11S Planes
## 108 S72_1253 Boeing X-32A JSF Planes
## 109 S72_3212 Pont Yacht Ships
## priceEach SUM(quantityOrdered) JumlahHarga
## 1 81.35 1057 85986.95
## 2 214.30 961 205942.30
## 3 105.86 999 105754.14
## 4 172.36 985 169774.60
## 5 127.84 1030 131675.20
## 6 119.67 932 111532.44
## 7 165.38 933 154299.54
## 8 205.72 1019 209628.68
## 9 121.64 972 118234.08
## 10 122.00 1028 125416.00
## 11 131.44 963 126576.72
## 12 96.30 925 89077.50
## 13 141.88 965 136914.20
## 14 75.81 900 68229.00
## 15 111.39 1056 117627.84
## 16 107.10 992 106243.20
## 17 94.50 999 94405.50
## 18 117.48 947 111253.56
## 19 95.55 1111 106156.05
## 20 43.13 960 41404.80
## 21 118.22 914 108053.08
## 22 134.04 1040 139401.60
## 23 136.00 918 124848.00
## 24 67.76 972 65862.72
## 25 137.98 917 126527.66
## 26 135.90 986 133997.40
## 27 55.09 832 45834.88
## 28 122.73 1053 129234.69
## 29 108.06 957 103413.42
## 30 58.34 998 58223.32
## 31 81.10 917 74368.70
## 32 52.70 945 49801.50
## 33 167.06 880 147012.80
## 34 126.72 855 108345.60
## 35 92.19 1038 95693.22
## 36 61.84 985 60912.40
## 37 80.86 966 78110.76
## 38 86.92 907 78836.44
## 39 136.59 883 120608.97
## 40 165.95 1808 300037.60
## 41 87.73 918 80536.14
## 42 73.17 974 71267.58
## 43 86.31 992 85619.52
## 44 132.29 915 121045.35
## 45 125.74 948 119201.52
## 46 52.84 959 50673.56
## 47 94.22 1076 101380.72
## 48 119.20 945 112644.00
## 49 75.46 866 65348.36
## 50 75.48 990 74725.20
## 51 98.07 1061 104052.27
## 52 40.75 995 40546.25
## 53 139.87 1013 141688.31
## 54 62.00 767 47554.00
## 55 72.02 803 57832.06
## 56 52.02 976 50771.52
## 57 96.92 1033 100118.36
## 58 43.27 915 39592.05
## 59 107.23 972 104227.56
## 60 32.53 937 30480.61
## 61 73.12 1015 74216.80
## 62 117.97 1011 119267.67
## 63 44.35 955 42354.25
## 64 107.34 1029 110452.86
## 65 64.41 947 60996.27
## 66 82.69 949 78472.81
## 67 30.41 983 29893.03
## 68 65.77 940 61823.80
## 69 112.74 873 98422.02
## 70 32.10 912 29275.20
## 71 73.46 991 72798.86
## 72 80.47 870 70008.90
## 73 60.01 969 58149.69
## 74 55.89 884 49406.76
## 75 96.37 894 86154.78
## 76 75.47 923 69658.81
## 77 132.00 1052 138864.00
## 78 55.96 1051 58813.96
## 79 35.29 824 29078.96
## 80 106.45 867 92292.15
## 81 88.62 983 87113.46
## 82 71.00 1009 71639.00
## 83 67.10 941 63141.10
## 84 92.46 911 84231.06
## 85 88.90 1014 90144.60
## 86 36.21 906 32806.26
## 87 51.95 955 49612.25
## 88 56.55 934 52817.70
## 89 63.35 988 62589.80
## 90 65.35 972 63520.20
## 91 87.76 898 78808.48
## 92 35.78 1074 38427.72
## 93 114.59 979 112183.61
## 94 53.31 966 51497.46
## 95 74.85 992 74251.20
## 96 54.00 934 50436.00
## 97 91.34 894 81657.96
## 98 86.61 898 77775.78
## 99 85.09 897 76325.73
## 100 99.72 984 98124.48
## 101 60.72 1020 61934.40
## 102 94.07 997 93787.79
## 103 113.90 973 110824.70
## 104 76.00 1047 79572.00
## 105 92.16 952 87736.32
## 106 99.31 896 88981.76
## 107 70.33 1085 76308.05
## 108 43.70 960 41952.00
## 109 44.77 958 42889.66- Membuat grafik yang sesuai dengan data yang diperoleh menggunakan R-Studio. Buat grafiknya semenarik mungkin.
library(ggplot2)## Warning: package 'ggplot2' was built under R version 4.1.2barchartno1 <- ggplot(harga,
aes(x = reorder (productline,JumlahHarga),
y = JumlahHarga)) +
geom_bar(stat = "identity", color='#c71585', fill='#c71585') +
theme(axis.text.x = element_text(angle = 45, hjust = 1)) +
labs(title = "Jumlah Harga Tiap Produk", subtitle="Database classicmodels",
y = "Harga Produk", x = "Jenis Produk") +
coord_flip()
barchartno1
plot(barchartno1)
- Menganalisis data dengan RStudio
harga2=dbGetQuery(con2,"SELECT productCode, productName, productline, priceEach,SUM(quantityOrdered),(SUM(quantityOrdered)*priceEach) AS JumlahHarga FROM products JOIN orderdetails USING (productCode) WHERE productline='Classic Cars' or productline='Vintage Cars' GROUP BY productCode")
harga2## productCode productName productline priceEach
## 1 S10_1949 1952 Alpine Renault 1300 Classic Cars 214.30
## 2 S10_4757 1972 Alfa Romeo GTA Classic Cars 127.84
## 3 S10_4962 1962 LanciaA Delta 16V Classic Cars 119.67
## 4 S12_1099 1968 Ford Mustang Classic Cars 165.38
## 5 S12_1108 2001 Ferrari Enzo Classic Cars 205.72
## 6 S12_3148 1969 Corvair Monza Classic Cars 131.44
## 7 S12_3380 1968 Dodge Charger Classic Cars 96.30
## 8 S12_3891 1969 Ford Falcon Classic Cars 141.88
## 9 S12_3990 1970 Plymouth Hemi Cuda Classic Cars 75.81
## 10 S12_4675 1969 Dodge Charger Classic Cars 107.10
## 11 S18_1129 1993 Mazda RX-7 Classic Cars 117.48
## 12 S18_1342 1937 Lincoln Berline Vintage Cars 95.55
## 13 S18_1367 1936 Mercedes-Benz 500K Special Roadster Vintage Cars 43.13
## 14 S18_1589 1965 Aston Martin DB5 Classic Cars 118.22
## 15 S18_1749 1917 Grand Touring Sedan Vintage Cars 136.00
## 16 S18_1889 1948 Porsche 356-A Roadster Classic Cars 67.76
## 17 S18_1984 1995 Honda Civic Classic Cars 137.98
## 18 S18_2238 1998 Chrysler Plymouth Prowler Classic Cars 135.90
## 19 S18_2248 1911 Ford Town Car Vintage Cars 55.09
## 20 S18_2325 1932 Model A Ford J-Coupe Vintage Cars 108.06
## 21 S18_2795 1928 Mercedes-Benz SSK Vintage Cars 167.06
## 22 S18_2870 1999 Indy 500 Monte Carlo SS Classic Cars 126.72
## 23 S18_2949 1913 Ford Model T Speedster Vintage Cars 92.19
## 24 S18_2957 1934 Ford V8 Coupe Vintage Cars 61.84
## 25 S18_3136 18th Century Vintage Horse Carriage Vintage Cars 86.92
## 26 S18_3140 1903 Ford Model A Vintage Cars 136.59
## 27 S18_3232 1992 Ferrari 360 Spider red Classic Cars 165.95
## 28 S18_3278 1969 Dodge Super Bee Classic Cars 73.17
## 29 S18_3320 1917 Maxwell Touring Car Vintage Cars 86.31
## 30 S18_3482 1976 Ford Gran Torino Classic Cars 132.29
## 31 S18_3685 1948 Porsche Type 356 Roadster Classic Cars 125.74
## 32 S18_3856 1941 Chevrolet Special Deluxe Cabriolet Vintage Cars 94.22
## 33 S18_4027 1970 Triumph Spitfire Classic Cars 119.20
## 34 S18_4409 1932 Alfa Romeo 8C2300 Spider Sport Vintage Cars 75.46
## 35 S18_4522 1904 Buick Runabout Vintage Cars 75.48
## 36 S18_4668 1939 Cadillac Limousine Vintage Cars 40.75
## 37 S18_4721 1957 Corvette Convertible Classic Cars 139.87
## 38 S18_4933 1957 Ford Thunderbird Classic Cars 62.00
## 39 S24_1046 1970 Chevy Chevelle SS 454 Classic Cars 72.02
## 40 S24_1444 1970 Dodge Coronet Classic Cars 52.02
## 41 S24_1628 1966 Shelby Cobra 427 S/C Classic Cars 43.27
## 42 S24_1937 1939 Chevrolet Deluxe Coupe Vintage Cars 32.53
## 43 S24_2022 1938 Cadillac V-16 Presidential Limousine Vintage Cars 44.35
## 44 S24_2766 1949 Jaguar XK 120 Classic Cars 82.69
## 45 S24_2840 1958 Chevy Corvette Limited Edition Classic Cars 30.41
## 46 S24_2887 1952 Citroen-15CV Classic Cars 112.74
## 47 S24_2972 1982 Lamborghini Diablo Classic Cars 32.10
## 48 S24_3151 1912 Ford Model T Delivery Wagon Vintage Cars 73.46
## 49 S24_3191 1969 Chevrolet Camaro Z28 Classic Cars 80.47
## 50 S24_3371 1971 Alpine Renault 1600s Classic Cars 60.01
## 51 S24_3420 1937 Horch 930V Limousine Vintage Cars 55.89
## 52 S24_3432 2002 Chevy Corvette Classic Cars 96.37
## 53 S24_3816 1940 Ford Delivery Sedan Vintage Cars 75.47
## 54 S24_3856 1956 Porsche 356A Coupe Classic Cars 132.00
## 55 S24_3969 1936 Mercedes Benz 500k Roadster Vintage Cars 35.29
## 56 S24_4048 1992 Porsche Cayenne Turbo Silver Classic Cars 106.45
## 57 S24_4258 1936 Chrysler Airflow Vintage Cars 88.62
## 58 S24_4620 1961 Chevrolet Impala Classic Cars 67.10
## 59 S32_4289 1928 Ford Phaeton Deluxe Vintage Cars 65.35
## 60 S50_1341 1930 Buick Marquette Phaeton Vintage Cars 35.78
## 61 S700_2824 1982 Camaro Z28 Classic Cars 94.07
## SUM(quantityOrdered) JumlahHarga
## 1 961 205942.30
## 2 1030 131675.20
## 3 932 111532.44
## 4 933 154299.54
## 5 1019 209628.68
## 6 963 126576.72
## 7 925 89077.50
## 8 965 136914.20
## 9 900 68229.00
## 10 992 106243.20
## 11 947 111253.56
## 12 1111 106156.05
## 13 960 41404.80
## 14 914 108053.08
## 15 918 124848.00
## 16 972 65862.72
## 17 917 126527.66
## 18 986 133997.40
## 19 832 45834.88
## 20 957 103413.42
## 21 880 147012.80
## 22 855 108345.60
## 23 1038 95693.22
## 24 985 60912.40
## 25 907 78836.44
## 26 883 120608.97
## 27 1808 300037.60
## 28 974 71267.58
## 29 992 85619.52
## 30 915 121045.35
## 31 948 119201.52
## 32 1076 101380.72
## 33 945 112644.00
## 34 866 65348.36
## 35 990 74725.20
## 36 995 40546.25
## 37 1013 141688.31
## 38 767 47554.00
## 39 803 57832.06
## 40 976 50771.52
## 41 915 39592.05
## 42 937 30480.61
## 43 955 42354.25
## 44 949 78472.81
## 45 983 29893.03
## 46 873 98422.02
## 47 912 29275.20
## 48 991 72798.86
## 49 870 70008.90
## 50 969 58149.69
## 51 884 49406.76
## 52 894 86154.78
## 53 923 69658.81
## 54 1052 138864.00
## 55 824 29078.96
## 56 867 92292.15
## 57 983 87113.46
## 58 941 63141.10
## 59 972 63520.20
## 60 1074 38427.72
## 61 997 93787.79Pada analisis data, di sini kita mencari perbedaan jumlah harga dari jenis produk Classic Cars dan Vintage Cars dengan sintax
uji1 = t.test (JumlahHarga ~ productline, var.equal=TRUE, data = harga2)
uji1##
## Two Sample t-test
##
## data: JumlahHarga by productline
## t = 2.5743, df = 59, p-value = 0.01258
## alternative hypothesis: true difference in means between group Classic Cars and group Vintage Cars is not equal to 0
## 95 percent confidence interval:
## 6966.742 55601.766
## sample estimates:
## mean in group Classic Cars mean in group Vintage Cars
## 105250.12 73965.86Interpretasi: Dari hasil uji 2 sampel independen diatas didapatkan hasil perbedaan JumlahHarga dari produk produk Classic Cars yaitu 105250.12 dan Vintage Cars yaitu 73965.86. Lalu dari hasil visualisasi data yang telah didapatkan dari no 1b dengan menggunakan barchart atau diagram batang didapatkan kesimpulan bahwa JumlahHarga terbanyak adalah produk Classic Cars dan JumlahHarga paling sedikit adalah Trains.
Nomor 2
Ambil suatu informasi/insight apapun yang dapat Anda peroleh dari database classicmodels, ceritakan tentang hasil data yang diperoleh, kemudian kerjakan seperti pada soal nomor 1 (satu)
- Sintaks R untuk menampilkan data yang berisi banyak pegawai yang bekerja di suatu kantor dengan menampilkan semua atribut yang memungkinkan.
dbGetQuery(con2,
"SELECT productName, productLine, quantityOrdered
FROM products JOIN orderdetails USING (productCode)
JOIN orders USING (orderNumber)
GROUP BY productCode
")## productName productLine
## 1 1965 Aston Martin DB5 Classic Cars
## 2 1999 Indy 500 Monte Carlo SS Classic Cars
## 3 1948 Porsche Type 356 Roadster Classic Cars
## 4 1966 Shelby Cobra 427 S/C Classic Cars
## 5 1996 Moto Guzzi 1100i Motorcycles
## 6 1936 Harley Davidson El Knucklehead Motorcycles
## 7 1938 Cadillac V-16 Presidential Limousine Vintage Cars
## 8 1917 Grand Touring Sedan Vintage Cars
## 9 1911 Ford Town Car Vintage Cars
## 10 1932 Model A Ford J-Coupe Vintage Cars
## 11 1932 Alfa Romeo 8C2300 Spider Sport Vintage Cars
## 12 1957 Ford Thunderbird Classic Cars
## 13 1970 Chevy Chevelle SS 454 Classic Cars
## 14 1939 Chevrolet Deluxe Coupe Vintage Cars
## 15 1949 Jaguar XK 120 Classic Cars
## 16 1952 Citroen-15CV Classic Cars
## 17 1969 Chevrolet Camaro Z28 Classic Cars
## 18 2002 Chevy Corvette Classic Cars
## 19 1936 Mercedes Benz 500k Roadster Vintage Cars
## 20 1993 Mazda RX-7 Classic Cars
## 21 1948 Porsche 356-A Roadster Classic Cars
## 22 1995 Honda Civic Classic Cars
## 23 1992 Ferrari 360 Spider red Classic Cars
## 24 1982 Lamborghini Diablo Classic Cars
## 25 1956 Porsche 356A Coupe Classic Cars
## 26 1937 Lincoln Berline Vintage Cars
## 27 2003 Harley-Davidson Eagle Drag Bike Motorcycles
## 28 P-51-D Mustang Planes
## 29 1997 BMW R 1100 S Motorcycles
## 30 1928 British Royal Navy Airplane Planes
## 31 1960 BSA Gold Star DBD34 Motorcycles
## 32 1900s Vintage Tri-Plane Planes
## 33 1997 BMW F650 ST Motorcycles
## 34 1928 Ford Phaeton Deluxe Vintage Cars
## 35 1930 Buick Marquette Phaeton Vintage Cars
## 36 American Airlines: B767-300 Planes
## 37 America West Airlines B757-200 Planes
## 38 ATA: B757-300 Planes
## 39 F/A 18 Hornet 1/72 Planes
## 40 1928 Mercedes-Benz SSK Vintage Cars
## 41 1969 Harley Davidson Ultimate Chopper Motorcycles
## 42 1969 Dodge Super Bee Classic Cars
## 43 1976 Ford Gran Torino Classic Cars
## 44 1957 Vespa GS150 Motorcycles
## 45 1957 Corvette Convertible Classic Cars
## 46 1971 Alpine Renault 1600s Classic Cars
## 47 1952 Alpine Renault 1300 Classic Cars
## 48 1962 LanciaA Delta 16V Classic Cars
## 49 1958 Setra Bus Trucks and Buses
## 50 1940 Ford Pickup Truck Trucks and Buses
## 51 1936 Mercedes-Benz 500K Special Roadster Vintage Cars
## 52 1926 Ford Fire Engine Trucks and Buses
## 53 1913 Ford Model T Speedster Vintage Cars
## 54 1934 Ford V8 Coupe Vintage Cars
## 55 18th Century Vintage Horse Carriage Vintage Cars
## 56 1917 Maxwell Touring Car Vintage Cars
## 57 1940s Ford truck Trucks and Buses
## 58 Corsair F4U ( Bird Cage) Planes
## 59 American Airlines: MD-11S Planes
## 60 1904 Buick Runabout Vintage Cars
## 61 18th century schooner Ships
## 62 1912 Ford Model T Delivery Wagon Vintage Cars
## 63 The Schooner Bluenose Ships
## 64 The Titanic Ships
## 65 The Queen Mary Ships
## 66 Pont Yacht Ships
## 67 1968 Ford Mustang Classic Cars
## 68 2002 Suzuki XREO Motorcycles
## 69 1982 Ducati 900 Monster Motorcycles
## 70 1974 Ducati 350 Mk3 Desmo Motorcycles
## 71 2002 Yamaha YZR M1 Motorcycles
## 72 1957 Chevy Pickup Trucks and Buses
## 73 1998 Chrysler Plymouth Prowler Classic Cars
## 74 1964 Mercedes Tour Bus Trucks and Buses
## 75 1970 Dodge Coronet Classic Cars
## 76 1962 Volkswagen Microbus Trucks and Buses
## 77 1958 Chevy Corvette Limited Edition Classic Cars
## 78 1980<U+0092>s GM Manhattan Express Trucks and Buses
## 79 1954 Greyhound Scenicruiser Trucks and Buses
## 80 Diamond T620 Semi-Skirted Tanker Trucks and Buses
## 81 1939 Cadillac Limousine Vintage Cars
## 82 1936 Chrysler Airflow Vintage Cars
## 83 1996 Peterbilt 379 Stake Bed with Outrigger Trucks and Buses
## 84 1982 Camaro Z28 Classic Cars
## 85 1972 Alfa Romeo GTA Classic Cars
## 86 2001 Ferrari Enzo Classic Cars
## 87 1969 Ford Falcon Classic Cars
## 88 1903 Ford Model A Vintage Cars
## 89 1992 Porsche Cayenne Turbo Silver Classic Cars
## 90 1950's Chicago Surface Lines Streetcar Trains
## 91 1962 City of Detroit Streetcar Trains
## 92 1968 Dodge Charger Classic Cars
## 93 1970 Plymouth Hemi Cuda Classic Cars
## 94 1969 Dodge Charger Classic Cars
## 95 1961 Chevrolet Impala Classic Cars
## 96 1982 Ducati 996 R Motorcycles
## 97 1969 Corvair Monza Classic Cars
## 98 Collectable Wooden Train Trains
## 99 1970 Triumph Spitfire Classic Cars
## 100 The Mayflower Ships
## 101 1940 Ford Delivery Sedan Vintage Cars
## 102 1999 Yamaha Speed Boat Ships
## 103 The USS Constitution Ship Ships
## 104 Boeing X-32A JSF Planes
## 105 1980s Black Hawk Helicopter Planes
## 106 1941 Chevrolet Special Deluxe Cabriolet Vintage Cars
## 107 1900s Vintage Bi-Plane Planes
## 108 1937 Horch 930V Limousine Vintage Cars
## 109 HMS Bounty Ships
## quantityOrdered
## 1 26
## 2 46
## 3 34
## 4 50
## 5 39
## 6 32
## 7 43
## 8 21
## 9 42
## 10 42
## 11 36
## 12 23
## 13 22
## 14 45
## 15 32
## 16 25
## 17 49
## 18 43
## 19 46
## 20 34
## 21 29
## 22 29
## 23 42
## 24 31
## 25 25
## 26 42
## 27 46
## 28 29
## 29 35
## 30 39
## 31 34
## 32 29
## 33 22
## 34 29
## 35 49
## 36 47
## 37 24
## 38 24
## 39 43
## 40 34
## 41 37
## 42 25
## 43 55
## 44 26
## 45 38
## 46 48
## 47 30
## 48 27
## 49 29
## 50 42
## 51 21
## 52 50
## 53 48
## 54 34
## 55 45
## 56 26
## 57 45
## 58 41
## 59 31
## 60 36
## 61 35
## 62 24
## 63 41
## 64 31
## 65 37
## 66 40
## 67 45
## 68 49
## 69 20
## 70 41
## 71 49
## 72 33
## 73 28
## 74 38
## 75 55
## 76 49
## 77 31
## 78 41
## 79 11
## 80 18
## 81 41
## 82 25
## 83 45
## 84 42
## 85 47
## 86 42
## 87 24
## 88 24
## 89 44
## 90 28
## 91 44
## 92 48
## 93 24
## 94 29
## 95 23
## 96 33
## 97 33
## 98 22
## 99 24
## 100 43
## 101 37
## 102 21
## 103 32
## 104 44
## 105 49
## 106 34
## 107 24
## 108 46
## 109 44- Membuat grafik yang sesuai dengan data yang diperoleh menggunakan R-Studio. Buat grafiknya semenarik mungkin.
databaru2 = dbGetQuery(con2, "SELECT productline, sum(quantityOrdered) AS jumlahan
FROM products JOIN orderdetails USING (productcode)
GROUP BY productcode")
databaru2## productline jumlahan
## 1 Motorcycles 1057
## 2 Classic Cars 961
## 3 Motorcycles 999
## 4 Motorcycles 985
## 5 Classic Cars 1030
## 6 Classic Cars 932
## 7 Classic Cars 933
## 8 Classic Cars 1019
## 9 Trucks and Buses 972
## 10 Motorcycles 1028
## 11 Classic Cars 963
## 12 Classic Cars 925
## 13 Classic Cars 965
## 14 Classic Cars 900
## 15 Trucks and Buses 1056
## 16 Classic Cars 992
## 17 Trucks and Buses 999
## 18 Classic Cars 947
## 19 Vintage Cars 1111
## 20 Vintage Cars 960
## 21 Classic Cars 914
## 22 Planes 1040
## 23 Vintage Cars 918
## 24 Classic Cars 972
## 25 Classic Cars 917
## 26 Classic Cars 986
## 27 Vintage Cars 832
## 28 Trucks and Buses 1053
## 29 Vintage Cars 957
## 30 Trucks and Buses 998
## 31 Planes 917
## 32 Motorcycles 945
## 33 Vintage Cars 880
## 34 Classic Cars 855
## 35 Vintage Cars 1038
## 36 Vintage Cars 985
## 37 Ships 966
## 38 Vintage Cars 907
## 39 Vintage Cars 883
## 40 Classic Cars 1808
## 41 Trains 918
## 42 Classic Cars 974
## 43 Vintage Cars 992
## 44 Classic Cars 915
## 45 Classic Cars 948
## 46 Motorcycles 959
## 47 Vintage Cars 1076
## 48 Classic Cars 945
## 49 Vintage Cars 866
## 50 Vintage Cars 990
## 51 Trucks and Buses 1061
## 52 Vintage Cars 995
## 53 Classic Cars 1013
## 54 Classic Cars 767
## 55 Classic Cars 803
## 56 Classic Cars 976
## 57 Motorcycles 1033
## 58 Classic Cars 915
## 59 Planes 972
## 60 Vintage Cars 937
## 61 Motorcycles 1015
## 62 Ships 1011
## 63 Vintage Cars 955
## 64 Trucks and Buses 1029
## 65 Motorcycles 947
## 66 Classic Cars 949
## 67 Classic Cars 983
## 68 Planes 940
## 69 Classic Cars 873
## 70 Classic Cars 912
## 71 Vintage Cars 991
## 72 Classic Cars 870
## 73 Classic Cars 969
## 74 Vintage Cars 884
## 75 Classic Cars 894
## 76 Vintage Cars 923
## 77 Classic Cars 1052
## 78 Planes 1051
## 79 Vintage Cars 824
## 80 Classic Cars 867
## 81 Vintage Cars 983
## 82 Planes 1009
## 83 Classic Cars 941
## 84 Trucks and Buses 911
## 85 Motorcycles 1014
## 86 Motorcycles 906
## 87 Trucks and Buses 955
## 88 Trains 934
## 89 Trucks and Buses 988
## 90 Vintage Cars 972
## 91 Motorcycles 898
## 92 Vintage Cars 1074
## 93 Trucks and Buses 979
## 94 Trains 966
## 95 Motorcycles 992
## 96 Ships 934
## 97 Planes 894
## 98 Ships 898
## 99 Ships 897
## 100 Planes 984
## 101 Ships 1020
## 102 Classic Cars 997
## 103 Planes 973
## 104 Planes 1047
## 105 Ships 952
## 106 Ships 896
## 107 Planes 1085
## 108 Planes 960
## 109 Ships 958Selanjutnya, akan dilakukan visualisasi. Di sini perlu diperhatikan akan memvisualisasikan data dalam bentuk apa, kelompok kami menggunakan visualisasi dengan bentuk barchart atau diagram batang. Alasannya yaitu diagram batang sangat cocok digunakan dalam menampilkan perbedaan jumlah antar jenis. Dalam visualisasi ini, pada sumbu X akan ditampilkan nama produk, sedangkan sumbu Y adalah banyaknya jumlah order. Selain itu, kami juga menentukan warna batang dengan ‘pink’ dan warna border dari batang yaitu ‘purple’. Untuk memudahkan orang awam membaca diagram batang ini, kami menambahkan label pada setiap bar nya, dengan meletakkan di ujung bar Kami juga menentukan label X sebagai nama produk dan label Y sebagai jumlah order. Diagram batang ini diberi nama “Jumlah Orderan”. Visualisasi ini dilakukan dengan sintaks sebagai berikut.
library(ggplot2)
Dapat dilihat dari bar chart tersebut, bahwa jumlah produk Classic Car yang tersedia paling banyak ketimbang produk lainnya. Lalu, jumlah produk Trains yang tersedia paling sedikit daripada produk lainnya
- Menganalisis data dengan RStudio
datasaya = dbGetQuery(con2,"SELECT productName,productLine, quantityOrdered
FROM products JOIN orderdetails USING(productCode)
JOIN orders USING (orderNumber)
WHERE productLine = 'ships' OR productLine = 'planes'
GROUP BY productCode
")
datasaya## productName productLine quantityOrdered
## 1 1980s Black Hawk Helicopter Planes 36
## 2 P-51-D Mustang Planes 34
## 3 1999 Yamaha Speed Boat Ships 41
## 4 1928 British Royal Navy Airplane Planes 28
## 5 18th century schooner Ships 43
## 6 1900s Vintage Bi-Plane Planes 49
## 7 Corsair F4U ( Bird Cage) Planes 50
## 8 1900s Vintage Tri-Plane Planes 26
## 9 The Schooner Bluenose Ships 41
## 10 American Airlines: B767-300 Planes 31
## 11 The Mayflower Ships 29
## 12 HMS Bounty Ships 30
## 13 America West Airlines B757-200 Planes 34
## 14 The USS Constitution Ship Ships 31
## 15 ATA: B757-300 Planes 32
## 16 F/A 18 Hornet 1/72 Planes 44
## 17 The Titanic Ships 39
## 18 The Queen Mary Ships 22
## 19 American Airlines: MD-11S Planes 48
## 20 Boeing X-32A JSF Planes 48
## 21 Pont Yacht Ships 25uji2 = t.test (quantityOrdered ~ productLine, var.equal=TRUE, data = datasaya)
uji2##
## Two Sample t-test
##
## data: quantityOrdered by productLine
## t = 1.3198, df = 19, p-value = 0.2026
## alternative hypothesis: true difference in means between group Planes and group Ships is not equal to 0
## 95 percent confidence interval:
## -2.86450 12.64228
## sample estimates:
## mean in group Planes mean in group Ships
## 38.33333 33.44444Dari output uji dua sampel independen tersebut, kami simpulkan bahwa rata-rata Quantity Ordered jenis produk dari Planes dan Ships tidak berbeda signifikan. Kemudian, dari hasil visualisasi data yang telah didapatkan dari (2a) dengan barchart atau diagram batang didapatkan kesimpulan bahwa quantity Ordered (jumlah produk) terbanyak adalah produk Classic Cars dan quantity Ordered (jumlah produk) paling sedikit adalah produk Trains