SPOOL fix1.log


Prompt Updating USA State Province (country_id = 52790) 

Update customers
set cust_state_province = 'ALABAMA'
where cust_state_province = 'AL';

Update customers
set cust_state_province = 'ALASKA'
where cust_state_province = 'AK';
                                                      
Update customers
set cust_state_province =  'AMERICAN SAMOA'              
where cust_state_province = 'AS';                                                      
               
Update customers
set cust_state_province = 'ARIZONA'                                                      
where cust_state_province = 'AZ'  ;        
                                                      
Update customers
set cust_state_province = 'ARKANSAS'
where cust_state_province = 'AR';
                   
Update customers
set cust_state_province = 'CALIFORNIA'                                                      
where cust_state_province = 'CA';
               
Update customers
set cust_state_province = 'COLORADO'                                                       
where cust_state_province = 'CO';

               
Update customers
set cust_state_province = 'CONNECTICUT'                                                      
where cust_state_province = 'CT';
               
Update customers
set cust_state_province =   'DELAWARE'                                                    
where cust_state_province = 'DE';
               
Update customers
set cust_state_province = 'DISTRICT OF COLUMBIA'                                                      
where cust_state_province = 'DC';
               
                                                      
Update customers
set cust_state_province = 'FEDERATED STATES OF MICRONESIA'
where cust_state_province = 'FM';


Update customers
set cust_state_province = 'FLORIDA'
where cust_state_province = 'FL';

Update customers
set cust_state_province = 'GEORGIA'
where cust_state_province = 'GA';
                                                      
Update customers
set cust_state_province =  'GUAM'              
where cust_state_province = 'GU';                                                      
               
Update customers
set cust_state_province = 'HAWAII'                                                      
where cust_state_province = 'HI'  ;        
                                                      
Update customers
set cust_state_province = 'IDAHO'
where cust_state_province = 'ID';
                   
Update customers
set cust_state_province = 'ILLINOIS'                                                      
where cust_state_province = 'IL';
               
Update customers
set cust_state_province = 'INDIANA'                                                       
where cust_state_province = 'IN';

               
Update customers
set cust_state_province = 'IOWA'                                                      
where cust_state_province = 'IA';
               
Update customers
set cust_state_province =   'KANSAS'                                                    
where cust_state_province = 'KS';
               
Update customers
set cust_state_province = 'KENTUCKY'                                                      
where cust_state_province = 'KY';
               
                                                      
Update customers
set cust_state_province = 'LOUISIANA'
where cust_state_province = 'LA';


Update customers
set cust_state_province = 'MAINE'
where cust_state_province = 'ME';

Update customers
set cust_state_province = 'MARYLAND'
where cust_state_province = 'MD';
                                                      
Update customers
set cust_state_province =  'MASSACHUSETTS'              
where cust_state_province = 'MA';                                                      
               
Update customers
set cust_state_province = 'MICHIGAN'                                                      
where cust_state_province = 'MI'  ;        
                                                      
Update customers
set cust_state_province = 'MINNESOTA'
where cust_state_province = 'MN';
                   
Update customers
set cust_state_province = 'MISSISSIPPI'                                                      
where cust_state_province = 'MS';
               
Update customers
set cust_state_province = 'MISSOURI'                                                       
where cust_state_province = 'MO';

               
Update customers
set cust_state_province = 'MONTANA'                                                      
where cust_state_province = 'MT';
               
Update customers
set cust_state_province =   'NEBRASKA'                                                    
where cust_state_province = 'NE';
               
Update customers
set cust_state_province = 'NEVADA'                                                      
where cust_state_province = 'NV';
               
                                                      
Update customers
set cust_state_province = 'NEW HAMPSHIRE'
where cust_state_province = 'NH';


Update customers
set cust_state_province = 'NEW JERSEY'
where cust_state_province = 'NJ';

Update customers
set cust_state_province = 'NEW MEXICO'
where cust_state_province = 'NM';
                                                      
Update customers
set cust_state_province =  'NEW YORK'              
where cust_state_province = 'NY';                                                      
               
Update customers
set cust_state_province = 'NORTH CAROLINA'                                                      
where cust_state_province = 'NC'  ;        
                                                      
Update customers
set cust_state_province = 'NORTH DAKOTA'
where cust_state_province = 'ND';
                   
Update customers
set cust_state_province = 'OHIO'                                                      
where cust_state_province = 'OH';
               
Update customers
set cust_state_province = 'OKLAHOMA'                                                       
where cust_state_province = 'OK';

               
Update customers
set cust_state_province = 'PENNSYLVANIA'                                                      
where cust_state_province = 'PA';
               
Update customers
set cust_state_province =   'OREGON'                                                    
where cust_state_province = 'OR';
               
Update customers
set cust_state_province = 'RHODE ISLAND'                                                      
where cust_state_province = 'RI';
               
                                                      
Update customers
set cust_state_province = 'SOUTH CAROLINA'
where cust_state_province = 'SC';


update customers
set cust_state_province = 'SOUTH DAKOTA'
where cust_state_province = 'SD';

Update customers
set cust_state_province = 'TENNESSEE'
where cust_state_province = 'TN';
                                                      
Update customers
set cust_state_province =  'TEXAS'              
where cust_state_province = 'TX';                                                      
               
Update customers
set cust_state_province = 'UTAH'                                                      
where cust_state_province = 'UT'  ;        
                                                      
Update customers
set cust_state_province = 'VERMONT'
where cust_state_province = 'VT';
                   
Update customers
set cust_state_province = 'VIRGINIA'                                                      
where cust_state_province = 'VA';
               
Update customers
set cust_state_province = 'WASHINGTON'                                                       
where cust_state_province = 'WA';

               
Update customers
set cust_state_province = 'WISCONSIN'                                                      
where cust_state_province = 'WI';
               
Update customers
set cust_state_province =   'WYOMING'                                                    
where cust_state_province = 'WY';
               
Update customers
set cust_state_province = 'WEST VIRGINIA'                                                      
where cust_state_province = 'WV';
               
                                                      
Update customers
set cust_state_province = 'VIRGIN ISLANDS'
where cust_state_province = 'VI';


Update customers
set cust_state_province = 'PUERTO RICO'
where cust_state_province = 'PR';

Update customers
set cust_state_province = 'PALAU'
where cust_state_province = 'PW';
                                                      
Update customers
set cust_state_province =  'NORTHERN MARIANA ISLANDS'              
where cust_state_province = 'MP';                                                      
               
Update customers
set cust_state_province = 'MARSHALL ISLANDS'                                                      
where cust_state_province = 'MH'  ;        

Update customers
set cust_state_province = 'MARSHALL ISLANDS'                                                      
where cust_state_province = 'MH'  ;        

                
UPDATE CUSTOMERS 
SET CUST_STATE_PROVINCE = INITCAP(LTRIM(RTRIM(CUST_STATE_PROVINCE))) 
WHERE COUNTRY_ID = 52790;

update customers
set cust_state_province = 'Georgia'                                                      
where cust_state_province = 'Ge'  ;        


Prompt Updating Malayasian city with Singapore city (country = 52769)

update customers 
set cust_city = 'Jurong' 
where cust_city = 'Batu Ferringhi';

update customers 
set cust_city = 'Seletar' 
where cust_city = 'Georgetown';

update customers 
set cust_city = 'Bedok' 
where cust_city = 'Klang';

update customers 
set cust_city = 'Woodlands' 
where cust_city = 'Petaling Jaya';

update customers 
set cust_city = 'Singapore' 
where cust_city = 'Shah Alam';

update customers 
set cust_city = 'Changi' 
where cust_city = 'Kuala Lumpur';


update customers 
set cust_state_province = 'Singapore' 
where country_id = 52769;

Prompt Updating Indian City with India (country = 52781) 

update CUSTOMERS CUS
SET CUS.COUNTRY_ID = 52781
 WHERE ((cus.country_id = 52771)); 

UPDATE CUSTOMERS CUS
SET CUST_CITY = 'Kolkata' 
WHERE CUST_CITY = 'Alleppey'; 

UPDATE CUSTOMERS CUS
SET CUST_CITY = 'Lucknow' 
WHERE CUST_CITY = 'Secunderabad'; 

UPDATE CUSTOMERS CUS
SET CUST_CITY = 'Bangalore' 
WHERE CUST_CITY = 'Pune'; 

UPDATE CUSTOMERS CUS
SET CUST_STATE_PROVINCE = 'West Bengal' 
WHERE CUST_CITY = 'Kolkata'; 

UPDATE CUSTOMERS CUS
SET CUST_STATE_PROVINCE = 'Uttar Pradesh' 
WHERE CUST_CITY = 'Lucknow'; 

UPDATE CUSTOMERS CUS
SET CUST_STATE_PROVINCE = 'Karnataka' 
WHERE CUST_CITY = 'Bangalore'; 


prompt switching city of Soest from Italy to Germany
update customers
set country_id = 52776
where cust_city='Soest';

prompt switching cities from Italy to The Netherlands
update customers
set country_id = 52784
where country_id=52770;

update customers
set cust_first_name = 'Carla', cust_last_name = 'Daniels'
where cust_id = 169;

update customers
set cust_first_name = 'Kasturi', cust_last_name = 'Shekhar'
where cust_id = 336;


prompt Introducing Major Cities in US

Update customers
set cust_city = 'Boston' 
where cust_city = 'Housatonic';

Update customers
set cust_city = 'Atlanta' 
where cust_city = 'Waycross';

Update customers
set cust_city = 'Chicago' 
where cust_city = 'Gays';


Update customers
set cust_city = 'New York' 
where cust_city = 'Attica';


Update customers
set cust_city = 'Dallas' 
where cust_city = 'Marshal';

Update customers
set cust_city = 'Redwood Shores' 
where cust_city = 'Quartzhill';

Update customers
set cust_city = 'Seattle' 
where cust_city = 'Buckley';

Update customers
set cust_city = 'Cincinnati' 
where cust_city = 'Otway';

Update customers
set cust_city = 'Mumbai' 
where cust_city = 'Mumbai Bombay';

Update customers
set cust_city = 'Cochin' 
where cust_city = 'Cochin Kochi';


Commit;


SPOOL OFF

exit;