There is a table “customers” in Mysql Database containing the credit limit for each customer.
I use R to apply the Customer Level classification function which classify customer by credit limit into three level : “PLATINUM”,“GOLD” and “SILVER”.
5.Insert the customer level value from duplicated table to table “customers” by inner join
library(DBI)
library(RMySQL)
library(RODBC)
library(dplyr)##
## Attaching package: 'dplyr'## The following objects are masked from 'package:stats':
##
## filter, lag## The following objects are masked from 'package:base':
##
## intersect, setdiff, setequal, union# Create connection to Schemas called classicmodels, there is a table called customers storing the amount of credit inside that Schemas
mydb = dbConnect(MySQL(),
user='root',
password='rfak4755',
dbname='classicmodels',
host='localhost')
# Show all the table in the Schema, there is the target table called "customers"
dbListTables(mydb) ## [1] "cust" "customers" "employees" "offices"
## [5] "orderdetails" "orders" "payments" "productlines"
## [9] "products"customers1 <- dbGetQuery(mydb, "select * from customers")## Warning in .local(conn, statement, ...): Decimal MySQL column 12 imported
## as numericstr(customers1)## 'data.frame': 122 obs. of 13 variables:
## $ customerNumber : int 103 112 114 119 121 124 125 128 129 131 ...
## $ customerName : chr "Atelier graphique" "Signal Gift Stores" "Australian Collectors, Co." "La Rochelle Gifts" ...
## $ contactLastName : chr "Schmitt" "King" "Ferguson" "Labrune" ...
## $ contactFirstName : chr "Carine " "Jean" "Peter" "Janine " ...
## $ phone : chr "40.32.2555" "7025551838" "03 9520 4555" "40.67.8555" ...
## $ addressLine1 : chr "54, rue Royale" "8489 Strong St." "636 St Kilda Road" "67, rue des Cinquante Otages" ...
## $ addressLine2 : chr NA NA "Level 3" NA ...
## $ city : chr "Nantes" "Las Vegas" "Melbourne" "Nantes" ...
## $ state : chr NA "NV" "Victoria" NA ...
## $ postalCode : chr "44000" "83030" "3004" "44000" ...
## $ country : chr "France" "USA" "Australia" "France" ...
## $ salesRepEmployeeNumber: int 1370 1166 1611 1370 1504 1165 NA 1504 1165 1323 ...
## $ creditLimit : num 21000 71800 117300 118200 81700 ...head(customers1)## customerNumber customerName contactLastName
## 1 103 Atelier graphique Schmitt
## 2 112 Signal Gift Stores King
## 3 114 Australian Collectors, Co. Ferguson
## 4 119 La Rochelle Gifts Labrune
## 5 121 Baane Mini Imports Bergulfsen
## 6 124 Mini Gifts Distributors Ltd. Nelson
## contactFirstName phone addressLine1 addressLine2
## 1 Carine 40.32.2555 54, rue Royale <NA>
## 2 Jean 7025551838 8489 Strong St. <NA>
## 3 Peter 03 9520 4555 636 St Kilda Road Level 3
## 4 Janine 40.67.8555 67, rue des Cinquante Otages <NA>
## 5 Jonas 07-98 9555 Erling Skakkes gate 78 <NA>
## 6 Susan 4155551450 5677 Strong St. <NA>
## city state postalCode country salesRepEmployeeNumber
## 1 Nantes <NA> 44000 France 1370
## 2 Las Vegas NV 83030 USA 1166
## 3 Melbourne Victoria 3004 Australia 1611
## 4 Nantes <NA> 44000 France 1370
## 5 Stavern <NA> 4110 Norway 1504
## 6 San Rafael CA 97562 USA 1165
## creditLimit
## 1 21000
## 2 71800
## 3 117300
## 4 118200
## 5 81700
## 6 210500# Create customer Level function
customer_level <- function(credit){
if (credit > 100000 ){
return('PLATINUM')
}else if (credit <= 10000 | credit >=50000){
return('GOLD')
}else if(credit < 50000) {
return("SILVER")
}
}
# Apply customer_level function to customers1
customers1$customerLevel <- sapply(customers1$creditLimit,customer_level)
# Create the table for being written into Mysql Database latter
customerLevel_tbl<- customers1
head(customerLevel_tbl,10)## customerNumber customerName contactLastName
## 1 103 Atelier graphique Schmitt
## 2 112 Signal Gift Stores King
## 3 114 Australian Collectors, Co. Ferguson
## 4 119 La Rochelle Gifts Labrune
## 5 121 Baane Mini Imports Bergulfsen
## 6 124 Mini Gifts Distributors Ltd. Nelson
## 7 125 Havel & Zbyszek Co Piestrzeniewicz
## 8 128 Blauer See Auto, Co. Keitel
## 9 129 Mini Wheels Co. Murphy
## 10 131 Land of Toys Inc. Lee
## contactFirstName phone addressLine1
## 1 Carine 40.32.2555 54, rue Royale
## 2 Jean 7025551838 8489 Strong St.
## 3 Peter 03 9520 4555 636 St Kilda Road
## 4 Janine 40.67.8555 67, rue des Cinquante Otages
## 5 Jonas 07-98 9555 Erling Skakkes gate 78
## 6 Susan 4155551450 5677 Strong St.
## 7 Zbyszek (26) 642-7555 ul. Filtrowa 68
## 8 Roland +49 69 66 90 2555 Lyonerstr. 34
## 9 Julie 6505555787 5557 North Pendale Street
## 10 Kwai 2125557818 897 Long Airport Avenue
## addressLine2 city state postalCode country
## 1 <NA> Nantes <NA> 44000 France
## 2 <NA> Las Vegas NV 83030 USA
## 3 Level 3 Melbourne Victoria 3004 Australia
## 4 <NA> Nantes <NA> 44000 France
## 5 <NA> Stavern <NA> 4110 Norway
## 6 <NA> San Rafael CA 97562 USA
## 7 <NA> Warszawa <NA> 01-012 Poland
## 8 <NA> Frankfurt <NA> 60528 Germany
## 9 <NA> San Francisco CA 94217 USA
## 10 <NA> NYC NY 10022 USA
## salesRepEmployeeNumber creditLimit customerLevel
## 1 1370 21000 SILVER
## 2 1166 71800 GOLD
## 3 1611 117300 PLATINUM
## 4 1370 118200 PLATINUM
## 5 1504 81700 GOLD
## 6 1165 210500 PLATINUM
## 7 NA 0 GOLD
## 8 1504 59700 GOLD
## 9 1165 64600 GOLD
## 10 1323 114900 PLATINUM#Write table "customerLevel" into the same Schemas
dbWriteTable(mydb,"customerLevel_tbl", customerLevel_tbl,overwrite = TRUE,row.names = FALSE)## [1] TRUE# Check whether table "customerLevel" was exported to Mysql or not
dbGetQuery(mydb,"select * from customerLevel_tbl LIMIT 30")## customerNumber customerName contactLastName
## 1 103 Atelier graphique Schmitt
## 2 112 Signal Gift Stores King
## 3 114 Australian Collectors, Co. Ferguson
## 4 119 La Rochelle Gifts Labrune
## 5 121 Baane Mini Imports Bergulfsen
## 6 124 Mini Gifts Distributors Ltd. Nelson
## 7 125 Havel & Zbyszek Co Piestrzeniewicz
## 8 128 Blauer See Auto, Co. Keitel
## 9 129 Mini Wheels Co. Murphy
## 10 131 Land of Toys Inc. Lee
## 11 141 Euro+ Shopping Channel Freyre
## 12 144 Volvo Model Replicas, Co Berglund
## 13 145 Danish Wholesale Imports Petersen
## 14 146 Saveley & Henriot, Co. Saveley
## 15 148 Dragon Souveniers, Ltd. Natividad
## 16 151 Muscle Machine Inc Young
## 17 157 Diecast Classics Inc. Leong
## 18 161 Technics Stores Inc. Hashimoto
## 19 166 Handji Gifts& Co Victorino
## 20 167 Herkku Gifts Oeztan
## 21 168 American Souvenirs Inc Franco
## 22 169 Porto Imports Co. de Castro
## 23 171 Daedalus Designs Imports Ranc\xc3\xa9
## 24 172 La Corne D'abondance, Co. Bertrand
## 25 173 Cambridge Collectables Co. Tseng
## 26 175 Gift Depot Inc. King
## 27 177 Osaka Souveniers Co. Kentary
## 28 181 Vitachrome Inc. Frick
## 29 186 Toys of Finland, Co. Karttunen
## 30 187 AV Stores, Co. Ashworth
## contactFirstName phone addressLine1
## 1 Carine 40.32.2555 54, rue Royale
## 2 Jean 7025551838 8489 Strong St.
## 3 Peter 03 9520 4555 636 St Kilda Road
## 4 Janine 40.67.8555 67, rue des Cinquante Otages
## 5 Jonas 07-98 9555 Erling Skakkes gate 78
## 6 Susan 4155551450 5677 Strong St.
## 7 Zbyszek (26) 642-7555 ul. Filtrowa 68
## 8 Roland +49 69 66 90 2555 Lyonerstr. 34
## 9 Julie 6505555787 5557 North Pendale Street
## 10 Kwai 2125557818 897 Long Airport Avenue
## 11 Diego (91) 555 94 44 C/ Moralzarzal, 86
## 12 Christina 0921-12 3555 Berguvsv\xc3土en 8
## 13 Jytte 31 12 3555 Vinb\xc3奸tet 34
## 14 Mary 78.32.5555 2, rue du Commerce
## 15 Eric +65 221 7555 Bronz Sok.
## 16 Jeff 2125557413 4092 Furth Circle
## 17 Kelvin 2155551555 7586 Pompton St.
## 18 Juri 6505556809 9408 Furth Circle
## 19 Wendy +65 224 1555 106 Linden Road Sandown
## 20 Veysel +47 2267 3215 Brehmen St. 121
## 21 Keith 2035557845 149 Spinnaker Dr.
## 22 Isabel (1) 356-5555 Estrada da sa\xc3榛e n. 58
## 23 Martine 20.16.1555 184, chauss\xc3委 de Tournai
## 24 Marie (1) 42.34.2555 265, boulevard Charonne
## 25 Jerry 6175555555 4658 Baden Av.
## 26 Julie 2035552570 25593 South Bay Ln.
## 27 Mory +81 06 6342 5555 1-6-20 Dojima
## 28 Michael 2125551500 2678 Kingston Rd.
## 29 Matti 90-224 8555 Keskuskatu 45
## 30 Rachel (171) 555-1555 Fauntleroy Circus
## addressLine2 city state postalCode country
## 1 <NA> Nantes <NA> 44000 France
## 2 <NA> Las Vegas NV 83030 USA
## 3 Level 3 Melbourne Victoria 3004 Australia
## 4 <NA> Nantes <NA> 44000 France
## 5 <NA> Stavern <NA> 4110 Norway
## 6 <NA> San Rafael CA 97562 USA
## 7 <NA> Warszawa <NA> 01-012 Poland
## 8 <NA> Frankfurt <NA> 60528 Germany
## 9 <NA> San Francisco CA 94217 USA
## 10 <NA> NYC NY 10022 USA
## 11 <NA> Madrid <NA> 28034 Spain
## 12 <NA> Lule\xc3\xa5 <NA> S-958 22 Sweden
## 13 <NA> Kobenhavn <NA> 1734 Denmark
## 14 <NA> Lyon <NA> 69004 France
## 15 Bronz Apt. 3/6 Tesvikiye Singapore <NA> 079903 Singapore
## 16 Suite 400 NYC NY 10022 USA
## 17 <NA> Allentown PA 70267 USA
## 18 <NA> Burlingame CA 94217 USA
## 19 2nd Floor Singapore <NA> 069045 Singapore
## 20 PR 334 Sentrum Bergen <NA> N 5804 Norway
## 21 Suite 101 New Haven CT 97823 USA
## 22 <NA> Lisboa <NA> 1756 Portugal
## 23 <NA> Lille <NA> 59000 France
## 24 <NA> Paris <NA> 75012 France
## 25 <NA> Cambridge MA 51247 USA
## 26 <NA> Bridgewater CT 97562 USA
## 27 <NA> Kita-ku Osaka 530-0003 Japan
## 28 Suite 101 NYC NY 10022 USA
## 29 <NA> Helsinki <NA> 21240 Finland
## 30 <NA> Manchester <NA> EC2 5NT UK
## salesRepEmployeeNumber creditLimit customerLevel
## 1 1370 21000 SILVER
## 2 1166 71800 GOLD
## 3 1611 117300 PLATINUM
## 4 1370 118200 PLATINUM
## 5 1504 81700 GOLD
## 6 1165 210500 PLATINUM
## 7 NA 0 GOLD
## 8 1504 59700 GOLD
## 9 1165 64600 GOLD
## 10 1323 114900 PLATINUM
## 11 1370 227600 PLATINUM
## 12 1504 53100 GOLD
## 13 1401 83400 GOLD
## 14 1337 123900 PLATINUM
## 15 1621 103800 PLATINUM
## 16 1286 138500 PLATINUM
## 17 1216 100600 PLATINUM
## 18 1165 84600 GOLD
## 19 1612 97900 GOLD
## 20 1504 96800 GOLD
## 21 1286 0 GOLD
## 22 NA 0 GOLD
## 23 1370 82900 GOLD
## 24 1337 84300 GOLD
## 25 1188 43400 SILVER
## 26 1323 84300 GOLD
## 27 1621 81200 GOLD
## 28 1286 76400 GOLD
## 29 1501 96500 GOLD
## 30 1501 136800 PLATINUM#Create Mysql query for adding empty customerLevel column in the customers table
add_column_customers_query <- "ALTER TABLE customers
ADD COLUMN customerLevel VARCHAR(255)"
#Create a empty column in table "customers"
addcolumn <-dbSendQuery(mydb, add_column_customers_query)
dbClearResult(addcolumn)## [1] TRUE# Create Mysql query for adding customer level value to the target column-cusomterLevel in table "customers"
insert_customerLevel_query <- "
UPDATE customers
inner join customerLevel_tbl
on
customers.customerNumber = customerLevel_tbl.customerNumber
SET customers.customerLevel = customerLevel_tbl.customerLevel"
#Insert Customer Level value
insert_customerLevel_value<- dbSendQuery(mydb,insert_customerLevel_query)
dbClearResult(insert_customerLevel_value)## [1] TRUE# Check the value in table "customers" from Mysql
dbGetQuery(mydb,"select customerNumber,creditLimit, customerLevel from customers")## Warning in .local(conn, statement, ...): Decimal MySQL column 1 imported as
## numeric## customerNumber creditLimit customerLevel
## 1 103 21000 SILVER
## 2 112 71800 GOLD
## 3 114 117300 PLATINUM
## 4 119 118200 PLATINUM
## 5 121 81700 GOLD
## 6 124 210500 PLATINUM
## 7 125 0 GOLD
## 8 128 59700 GOLD
## 9 129 64600 GOLD
## 10 131 114900 PLATINUM
## 11 141 227600 PLATINUM
## 12 144 53100 GOLD
## 13 145 83400 GOLD
## 14 146 123900 PLATINUM
## 15 148 103800 PLATINUM
## 16 151 138500 PLATINUM
## 17 157 100600 PLATINUM
## 18 161 84600 GOLD
## 19 166 97900 GOLD
## 20 167 96800 GOLD
## 21 168 0 GOLD
## 22 169 0 GOLD
## 23 171 82900 GOLD
## 24 172 84300 GOLD
## 25 173 43400 SILVER
## 26 175 84300 GOLD
## 27 177 81200 GOLD
## 28 181 76400 GOLD
## 29 186 96500 GOLD
## 30 187 136800 PLATINUM
## 31 189 69400 GOLD
## 32 198 23000 SILVER
## 33 201 92700 GOLD
## 34 202 90300 GOLD
## 35 204 68700 GOLD
## 36 205 90700 GOLD
## 37 206 0 GOLD
## 38 209 53800 GOLD
## 39 211 58600 GOLD
## 40 216 60300 GOLD
## 41 219 11000 SILVER
## 42 223 0 GOLD
## 43 227 120800 PLATINUM
## 44 233 48700 SILVER
## 45 237 0 GOLD
## 46 239 105000 PLATINUM
## 47 240 93900 GOLD
## 48 242 61100 GOLD
## 49 247 0 GOLD
## 50 249 113000 PLATINUM
## 51 250 68100 GOLD
## 52 256 77900 GOLD
## 53 259 120400 PLATINUM
## 54 260 89600 GOLD
## 55 273 0 GOLD
## 56 276 107800 PLATINUM
## 57 278 119600 PLATINUM
## 58 282 93300 GOLD
## 59 286 123700 PLATINUM
## 60 293 0 GOLD
## 61 298 141300 PLATINUM
## 62 299 95100 GOLD
## 63 303 0 GOLD
## 64 307 0 GOLD
## 65 311 90500 GOLD
## 66 314 79900 GOLD
## 67 319 102700 PLATINUM
## 68 320 94500 GOLD
## 69 321 105000 PLATINUM
## 70 323 88000 GOLD
## 71 324 77000 GOLD
## 72 328 43000 SILVER
## 73 333 51600 GOLD
## 74 334 98800 GOLD
## 75 335 0 GOLD
## 76 339 81100 GOLD
## 77 344 59600 GOLD
## 78 347 57700 GOLD
## 79 348 0 GOLD
## 80 350 65000 GOLD
## 81 353 81100 GOLD
## 82 356 0 GOLD
## 83 357 77700 GOLD
## 84 361 0 GOLD
## 85 362 41900 SILVER
## 86 363 114200 PLATINUM
## 87 369 0 GOLD
## 88 376 0 GOLD
## 89 379 70700 GOLD
## 90 381 23500 SILVER
## 91 382 71700 GOLD
## 92 385 81500 GOLD
## 93 386 121400 PLATINUM
## 94 398 94400 GOLD
## 95 406 95000 GOLD
## 96 409 0 GOLD
## 97 412 86800 GOLD
## 98 415 77000 GOLD
## 99 424 67500 GOLD
## 100 443 0 GOLD
## 101 447 49700 SILVER
## 102 448 116400 PLATINUM
## 103 450 77600 GOLD
## 104 452 45300 SILVER
## 105 455 95400 GOLD
## 106 456 39800 SILVER
## 107 458 104600 PLATINUM
## 108 459 0 GOLD
## 109 462 85800 GOLD
## 110 465 0 GOLD
## 111 471 60300 GOLD
## 112 473 34800 SILVER
## 113 475 55400 GOLD
## 114 477 0 GOLD
## 115 480 0 GOLD
## 116 481 0 GOLD
## 117 484 65700 GOLD
## 118 486 72600 GOLD
## 119 487 60300 GOLD
## 120 489 43300 SILVER
## 121 495 85100 GOLD
## 122 496 110000 PLATINUM