Introduction to SQL

# Introduction to SQL
## Inspect and Modify Data
### Prof. Dr. Jan Kirenz
### HdM Stuttgart

---

layout: true
  
<div class="my-footer"><span> Introduction to SQL | Prof. Dr. Jan Kirenz </span></div>

---

# Setup

```
pw = "your_password"
```

```r
library(DBI)
library(RPostgres)

con <- dbConnect(RPostgres::Postgres(),
                 dbname = "postgres",
                  host = "localhost", 
                  port = 5432,
                  user = "postgres", 
                  password = pw)
```

* Note: the examples used in this presentation are based on the excellent book  "A Beginner's Guide to Storytelling with Data" from Anthony DeBarros (2018).

---
## Inspecting and modifying data

* Create Table:

```sql
CREATE TABLE meat_poultry_egg_inspect (
    est_number varchar(50) CONSTRAINT est_number_key PRIMARY KEY,
    company varchar(100),
    street varchar(100),
    city varchar(30),
    st varchar(2),
    zip varchar(5),
    phone varchar(14),
    grant_date date,
    activities text,
    dbas text
);
```

---
## Inspecting and modifying data
### Import data

* Data: [MPI_Directory_by_Estaplishment_Name.csv]https://github.com/kirenz/datasets/blob/master/MPI_Directory_by_Establishment_Name.csv)

```sql
COPY meat_poultry_egg_inspect
FROM '/tmp/MPI_Directory_by_Establishment_Name.csv'
WITH (FORMAT CSV, HEADER, DELIMITER ',');
```

---
## Inspecting and modifying data
### Create index

```sql
CREATE INDEX company_idx ON meat_poultry_egg_inspect (company);
```

```sql
SELECT *
FROM meat_poultry_egg_inspect
LIMIT 20
```

---

```{=html}
<div id="htmlwidget-35d72eadf9ef85cc7bd5" style="width:100%;height:auto;" class="datatables html-widget"></div>
<script type="application/json" data-for="htmlwidget-35d72eadf9ef85cc7bd5">{"x":{"filter":"none","fillContainer":false,"data":[["1","2","3","4","5"],["M46712+P46712","M13561+P13561","M46724+P46724","M7067+P7067","M48225"],["121 In-Flight Catering LLC","165368 C. Corporation ","1732 Meats LLC","1st Original Texas Chili Company, Inc.","290 West Bar &amp; Grill"],["45 Rason Road","5617 Hoover Street, Suite A","6250 Baltimore Pike","3313 N. Jones Street","12013 HWY 290 West"],["Inwood","Houston","Yeadon","Fort Worth","Austin"],["NY","TX","PA","TX","TX"],["11096","77092","19050","76106","78737"],["(718) 663-4612","(713) 263-1944","(267) 879-7214","(817) 626-0983","(512) 963-8963"],["2016-06-27","2014-06-30","2015-09-08","2003-11-28","2016-11-01"],["Meat Processing, Poultry Processing","Meat Processing","Meat Processing, Poultry Processing","Meat Processing","Meat Processing"],[null,"Long Phung Food Products",null,"T.C. Foods; Texas Chili","Afia Foods"]],"container":"<table class=\"display\">\n  <thead>\n    <tr>\n      <th> <\/th>\n      <th>est_number<\/th>\n      <th>company<\/th>\n      <th>street<\/th>\n      <th>city<\/th>\n      <th>st<\/th>\n      <th>zip<\/th>\n      <th>phone<\/th>\n      <th>grant_date<\/th>\n      <th>activities<\/th>\n      <th>dbas<\/th>\n    <\/tr>\n  <\/thead>\n<\/table>","options":{"pageLength":4,"order":[],"autoWidth":false,"orderClasses":false,"columnDefs":[{"orderable":false,"targets":0}],"lengthMenu":[4,10,25,50,100]}},"evals":[],"jsHooks":[]}</script>
```

---
## Inspecting and modifying data
### Inspect data

* Count rows:

```sql
-- Count the rows imported:
SELECT count(*) 
FROM meat_poultry_egg_inspect;
```

<table>
 <thead>
  <tr>
   <th style="text-align:right;"> count </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 6287 </td>
  </tr>
</tbody>
</table>

---
## Inspecting and modifying data
### Inspect data

* Finding multiple companies at the same address

```sql
SELECT company,
       street,
       city,
       st,
       count(*) AS address_count
FROM meat_poultry_egg_inspect
GROUP BY company, street, city, st
*HAVING count(*) > 1     --
ORDER BY company, street, city, st;
```

---
class: middle, center

```{=html}
<div id="htmlwidget-894e075b30ff2033da78" style="width:100%;height:auto;" class="datatables html-widget"></div>
<script type="application/json" data-for="htmlwidget-894e075b30ff2033da78">{"x":{"filter":"none","fillContainer":false,"data":[["1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23"],["Acre Station Meat Farm","Beltex Corporation","Cloverleaf Cold Storage","Crete Core Ingredients, LLC","Crider, Inc.","Dimension Marketing &amp; Sales, Inc.","Foster Poultry Farms, A California Corporation","Freezer &amp; Dry Storage, LLC","JBS Souderton Inc.","KB Poultry Processing LLC","Lakeside Refrigerated Services","Liberty Cold Storage","M.G. Waldbaum Company","Midway International Logistics LLC","Nordic Logistics and Warehousing, LLC","OK Foods, Inc.","Pacific Produce Corporation","Payless Distribution Center (PDC)","Piatkowski Riteway Meats Inc.","Preferred Freezer Services","THE AMERICAN PIG","The Classic Jerky Company","United States Cold Storage Inc."],["17076 Hwy 32 N","3801 North Grove Street","111 Imperial Drive","2220 County Road I","1 Plant Avenue","386 West 9400 South","6648 Highway 15 North","21740 Trolley Industrial Drive","249 Allentown Road","15024 Sandstone Dr.","2600 Oldmans Creek Road","1310 Remington Blvd.","120 Tower Street","948 Bradley Street","220 Nordic Way","3921 Reed Lane","220 East Harmon Industrial Park Road","370 Mendioka Street","3555 Witmer Road","2700 Trade Street","25 MEADOW ROAD","21655 Trolley Industrial Drive","11801 NW 102 Road"],["Pinetown","Fort Worth","Sanford","Crete","Stillmore","Sandy","Farmerville","Taylor","Souderton","Utica","Swedesboro","Bolingbrook","Gaylord","Watertown","Pooler","Fort Smith","Tamuning","Dededo","Niagara Falls","Chesapeake","ASHEVILLE","Taylor","Medley"],["NC","TX","NC","NE","GA","UT","LA","MI","PA","MN","NJ","IL","MN","NY","GA","AR","GU","GU","NY","VA","NC","MI","FL"],[2,2,2,2,3,2,2,2,2,2,2,2,3,2,2,2,2,2,2,2,2,2,2]],"container":"<table class=\"display\">\n  <thead>\n    <tr>\n      <th> <\/th>\n      <th>company<\/th>\n      <th>street<\/th>\n      <th>city<\/th>\n      <th>st<\/th>\n      <th>address_count<\/th>\n    <\/tr>\n  <\/thead>\n<\/table>","options":{"pageLength":4,"columnDefs":[{"className":"dt-right","targets":5},{"orderable":false,"targets":0}],"order":[],"autoWidth":false,"orderClasses":false,"lengthMenu":[4,10,25,50,100]}},"evals":[],"jsHooks":[]}</script>
```

???

Manche Unternehmen sind mehrfach bei der gleichen Adresse gelistet. Dies ist nicht zwingend ein Fehler. Möglicherweise sind sie unter verschiedenen Unternehmenstypen gelistet. Dies müsste in den Daten nun geprüft werden.

---
## Inspecting and modifying data
### Missing values

* Check wether any rows are missing

* How many of the companies are in each state?

```sql
-- Grouping and counting states
SELECT st, 
       count(*) AS st_count
FROM meat_poultry_egg_inspect
GROUP BY st
*ORDER BY st NULLS FIRST;    --
```

* NULL values will either appear first or last in a sorted column (depending on the database).

* You can specify `NULLS FIRST` or `NULLS LAST` to an `ORDER BY`

---
## Inspecting and modifying data

```{=html}
<div id="htmlwidget-b45fb6b6409c5d95554e" style="width:100%;height:auto;" class="datatables html-widget"></div>
<script type="application/json" data-for="htmlwidget-b45fb6b6409c5d95554e">{"x":{"filter":"none","fillContainer":false,"data":[["1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31","32","33","34","35","36","37","38","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57"],[null,"AK","AL","AR","AS","AZ","CA","CO","CT","DC","DE","FL","GA","GU","HI","IA","ID","IL","IN","KS","KY","LA","MA","MD","ME","MI","MN","MO","MP","MS","MT","NC","ND","NE","NH","NJ","NM","NV","NY","OH","OK","OR","PA","PR","RI","SC","SD","TN","TX","UT","VA","VI","VT","WA","WI","WV","WY"],[3,17,93,87,1,37,666,121,55,2,22,322,206,14,47,149,38,348,82,69,110,49,101,75,24,177,160,158,4,69,23,212,22,110,18,244,28,35,346,186,59,86,364,84,27,55,24,113,387,71,111,2,27,139,184,23,1]],"container":"<table class=\"display\">\n  <thead>\n    <tr>\n      <th> <\/th>\n      <th>st<\/th>\n      <th>st_count<\/th>\n    <\/tr>\n  <\/thead>\n<\/table>","options":{"pageLength":5,"columnDefs":[{"className":"dt-right","targets":2},{"orderable":false,"targets":0}],"order":[],"autoWidth":false,"orderClasses":false,"lengthMenu":[5,10,25,50,100]}},"evals":[],"jsHooks":[]}</script>
```

???

Es sind mehr als 50 US Staaten, da Puerto Rico, Guam und American Samoa enthalten sind. Alaska (AK) ist mit 17 Unternehmen aufgeführt.

Auf Seite 12 ist zu sehen, dass es 3 fehlende Werte bei ST gibt

---

## Inspecting and modifying data
### Find missing values

* Using `IS NULL` to find missing values in the st column.

```sql
SELECT est_number,
       company,
       city,
       st,
       zip
FROM meat_poultry_egg_inspect
*WHERE st IS NULL;       --
```

---

```{=html}
<div id="htmlwidget-acba652851a18739c0f6" style="width:100%;height:auto;" class="datatables html-widget"></div>
<script type="application/json" data-for="htmlwidget-acba652851a18739c0f6">{"x":{"filter":"none","fillContainer":false,"data":[["1","2","3"],["V18677A","M45319+P45319","M263A+P263A+V263A"],["Atlas Inspection, Inc.","Hall-Namie Packing Company, Inc","Jones Dairy Farm"],["Blaine",null,null],[null,null,null],["55449","36671","53538"]],"container":"<table class=\"display\">\n  <thead>\n    <tr>\n      <th> <\/th>\n      <th>est_number<\/th>\n      <th>company<\/th>\n      <th>city<\/th>\n      <th>st<\/th>\n      <th>zip<\/th>\n    <\/tr>\n  <\/thead>\n<\/table>","options":{"pageLength":5,"order":[],"autoWidth":false,"orderClasses":false,"columnDefs":[{"orderable":false,"targets":0}],"lengthMenu":[5,10,25,50,100]}},"evals":[],"jsHooks":[]}</script>
```

---
## Inspecting and modifying data

* We’ve discovered that we’ll need to add 3 missing values to the st column to clean up this table.

* Let’s look at what other issues exist in our data set and make a list of cleanup tasks.

---

## Inspecting and modifying data
### Checking inconsistent data values

* Using GROUP BY and count() to find inconsistent names

```sql
SELECT company,
       count(*) AS company_count
FROM meat_poultry_egg_inspect
GROUP BY company
ORDER BY company ASC;
```

---

```{=html}
<div id="htmlwidget-697554f0f966a553c3ea" style="width:100%;height:auto;" class="datatables html-widget"></div>
<script type="application/json" data-for="htmlwidget-697554f0f966a553c3ea">{"x":{"filter":"none","fillContainer":false,"data":[["1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31","32","33","34","35","36","37","38","39","40","41","42","43","44","45","46","47","48","49","50","51","52","53","54","55","56","57","58","59","60","61","62","63","64","65","66","67","68","69","70","71","72","73","74","75","76","77","78","79","80","81","82","83","84","85","86","87","88","89","90","91","92","93","94","95","96","97","98","99","100","101","102","103","104","105","106","107","108","109","110","111","112","113","114","115","116","117","118","119","120","121","122","123","124","125","126","127","128","129","130","131","132","133","134","135","136","137","138","139","140","141","142","143","144","145","146","147","148","149","150","151","152","153","154","155","156","157","158","159","160","161","162","163","164","165","166","167","168","169","170","171","172","173","174","175","176","177","178","179","180","181","182","183","184","185","186","187","188","189","190","191","192","193","194","195","196","197","198","199","200","201","202","203","204","205","206","207","208","209","210","211","212","213","214","215","216","217","218","219","220","221","222","223","224","225","226","227","228","229","230","231","232","233","234","235","236","237","238","239","240","241","242","243","244","245","246","247","248","249","250","251","252","253","254","255","256","257","258","259","260","261","262","263","264","265","266","267","268","269","270","271","272","273","274","275","276","277","278","279","280","281","282","283","284","285","286","287","288","289","290","291","292","293","294","295","296","297","298","299","300","301","302","303","304","305","306","307","308","309","310","311","312","313","314","315","316","317","318","319","320","321","322","323","324","325","326","327","328","329","330","331","332","333","334","335","336","337","338","339","340","341","342","343","344","345","346","347","348","349","350","351","352","353","354","355","356","357","358","359","360","361","362","363","364","365","366","367","368","369","370","371","372","373","374","375","376","377","378","379","380","381","382","383","384","385","386","387","388","389","390","391","392","393","394","395","396","397","398","399","400","401","402","403","404","405","406","407","408","409","410","411","412","413","414","415","416","417","418","419","420","421","422","423","424","425","426","427","428","429","430","431","432","433","434","435","436","437","438","439","440","441","442","443","444","445","446","447","448","449","450","451","452","453","454","455","456","457","458","459","460","461","462","463","464","465","466","467","468","469","470","471","472","473","474","475","476","477","478","479","480","481","482","483","484","485","486","487","488","489","490","491","492","493","494","495","496","497","498","499","500","501","502","503","504","505","506","507","508","509","510","511","512","513","514","515","516","517","518","519","520","521","522","523","524","525","526","527","528","529","530","531","532","533","534","535","536","537","538","539","540","541","542","543","544","545","546","547","548","549","550","551","552","553","554","555","556","557","558","559","560","561","562","563","564","565","566","567","568","569","570","571","572","573","574","575","576","577","578","579","580","581","582","583","584","585","586","587","588","589","590","591","592","593","594","595","596","597","598","599","600","601","602","603","604","605","606","607","608","609","610","611","612","613","614","615","616","617","618","619","620","621","622","623","624","625","626","627","628","629","630","631","632","633","634","635","636","637","638","639","640","641","642","643","644","645","646","647","648","649","650","651","652","653","654","655","656","657","658","659","660","661","662","663","664","665","666","667","668","669","670","671","672","673","674","675","676","677","678","679","680","681","682","683","684","685","686","687","688","689","690","691","692","693","694","695","696","697","698","699","700","701","702","703","704","705","706","707","708","709","710","711","712","713","714","715","716","717","718","719","720","721","722","723","724","725","726","727","728","729","730","731","732","733","734","735","736","737","738","739","740","741","742","743","744","745","746","747","748","749","750","751","752","753","754","755","756","757","758","759","760","761","762","763","764","765","766","767","768","769","770","771","772","773","774","775","776","777","778","779","780","781","782","783","784","785","786","787","788","789","790","791","792","793","794","795","796","797","798","799","800","801","802","803","804","805","806","807","808","809","810","811","812","813","814","815","816","817","818","819","820","821","822","823","824","825","826","827","828","829","830","831","832","833","834","835","836","837","838","839","840","841","842","843","844","845","846","847","848","849","850","851","852","853","854","855","856","857","858","859","860","861","862","863","864","865","866","867","868","869","870","871","872","873","874","875","876","877","878","879","880","881","882","883","884","885","886","887","888","889","890","891","892","893","894","895","896","897","898","899","900","901","902","903","904","905","906","907","908","909","910","911","912","913","914","915","916","917","918","919","920","921","922","923","924","925","926","927","928","929","930","931","932","933","934","935","936","937","938","939","940","941","942","943","944","945","946","947","948","949","950","951","952","953","954","955","956","957","958","959","960","961","962","963","964","965","966","967","968","969","970","971","972","973","974","975","976","977","978","979","980","981","982","983","984","985","986","987","988","989","990","991","992","993","994","995","996","997","998","999","1000"],["121 In-Flight Catering LLC","165368 C. Corporation ","1732 Meats LLC","1st Original Texas Chili Company, Inc.","290 West Bar &amp; Grill","3 Little Pigs LLC","3-A Enterprises","3282 Beaver Meadow Road LLC","3D Meats, LLC","4 Frendz Meat Market","4-L Processing","41 Market","412 Meat Processing Inc.","458 1/2 South Broadway Meat Inc","4G Meat Processing LLC","50th State Poultry","58 Place Seafood Inc.","5th Generation Adams Farm","701 Foods, Inc.","814 Americas, Inc.","86-17 Northern Blvd. Corp.","888 Food Company","A &amp; A Finest","A &amp; A Halal Distributors","A &amp; B Famous Gefilte Fish Inc.","A &amp; G Food Service LLC","A &amp; M Cold Storage","A &amp; R Packing Co., Inc.","A &amp; S Distributors","A &amp; S Produce Inc.","A Casa Enterprises LLC","A Full Measure Catering","A G Specialty Foods","A La Carte Foods, Inc.","A To Z Kosher Meat Prod's In","A to Z Portion Control Meats, Inc.","A&amp;M Packing LLC","A&amp;S &amp; Son","A&amp;W Country Meats, Inc.","A-1 Meat Inc.","A. Altieri &amp; Sons","A. Concepcion Hnos","A. Decoite Packing House, Inc.","A. F. Wendling, Inc.","A. Gemmen &amp; Sons, Inc.","A. Gimenez Trading LLC","A. I. Foods","A. N. Deringer, Inc.","A. S. K. Foods Inc.","A. Tarantino &amp; Sons","A.A. Rubashkin &amp; Sons","A.B. Vannoy Hams","A.F.I. Food Service L.L.C.","A.J. Cetak's Meat Market","A.J. Sons, Inc.","A.J.'s Lena Maid Meats, Inc.","A.L. Beck &amp; Sons, Inc.","A.N. Deringer Inc.","A.N. Deringer, Inc.","A.S.K. Foods Inc.","A.T.A. Meat Company, Inc.","AA Meat Products Inc.","AA Poultry Processing, LLC","ABF Packing, Inc.","ACC Central Kitchen LLC","ACME Jerky, LLC","AFS Classico, LLC","AG Food Products Corp.","AGA Investments Inc.","AGRO Merchants Oakland LLC","AGRO Merchants Oakland, LLC","ALFREDO AIELLO ITALIAN FOODS, INC.","AMERICAN FOOD SERVICE","AMICK FARMS, LLC","AMPC, Inc.","ARC Industries","ASU Food Safety and Product Development Lab","ATG Transportation LLC","ATK Foods, Inc.","ATM International USA, Inc.","ATPAC Inc.","AU, LAU and Associates, Inc.","AVA Pork Products, Inc.","AVF Holding LLC","AZ Gourmet Foods Inc.","AZ Grass Fed Beef","Aala Meat Market, Inc.","Abanto Forwarding, Inc.","Abattoir Associates Inc.","Abbott's Meat, Inc.","Abbyland Foods, Inc.","Abbyland Pork Pack, Inc.","Abe's Finest Meats","Abe's Kosher Meats LLC","Abeles &amp; Heymann, LLC","Able Freight Services Inc.","Abner Snack Foods, Inc.","Abuelito Meat Inc.","Abundant Foods","Academy Packing Co Inc","Acadian Fine Foods, LLC","Accu-Ray Inspection Services","Achatz Handmade Pie Company LLC","Achatz Soup From Skratzch","Acme Delivery Service","Acoreana Manufacturing, Co.","Acre Station Meat Farm","Activ International, Inc.","Ada Valley Gourmet Foods","Adam Farms, LLC","Adams Farm Slaughterhouse LLC","Addielee's Inc.","Adena Poultry","Adesa International LLC","Adirondack Meat Co.","Adolf's Meat Products","Advance Pierre Foods, Inc","Advance Pierre Foods, Inc.","AdvancePierre Foods","AdvancePierre Foods, Inc","AdvancePierre Foods, Inc.","Advertising Resources, Inc","Afandina Halal","Afya Brands International, Inc.","Against The Grain Gourmet","Agri Star Meat and Poultry, LLC","Agro Merchant Group Inc.","Agro Merchants Group Charleston LLC","Airfrigo USA Inc/Seafrigo Coldstorage Fairmont","Ajinomoto Toyo Frozen Noodle, Inc.","Ajinomoto Windsor Inc.","Ajinomoto Windsor, Inc.","Ajuua","Al &amp; John Inc.","Al - Baghdadi Food Inc.","Al Lunardi &amp; Son's Meat Co Inc","Al Shabrawy Inc.","Al's Hickory House BBQ","Al's Wholesale Meats, Inc.","Al-Marwa L. L. C.","Alabama CatfishLLC","Alaska Commercial Co.","Alaska Natural Meats, Ltd.","Alaska Sausage Company, Inc.","Alatrade Foods","Alatrade Foods LLC","Alba Cold Storage","Albert Lea Select Foods Inc.","Alberto's Meat Shop","Albertville Quality Foods","Albie's Foods, Inc.","Albion Locker","Alcor Foods, Inc.","Alderfer, LLC","Aldon Food Corporation","AleCon Enterprises Inc.","Alef Sausage","Alena Foods","Alewel's Country Meats","Alex &amp; George Wholesale Meats, Inc.","Alex Froehlich Packing Company","Alex's Deli","Alex's Meat &amp; Provision","Alexander &amp; Hornung","Alexander's Ham Co.","Alexandra Foods","Alexis Wholesale Inc.","Alfresco Pasta, LLC","Ali International Inc.","All American Cold Storage","All American Meats, Inc.","All Foods LLC","All People's Food","Alle-Pia","Alleghany Highlands Agricultural Center, LLC","Allen Brothers, Inc","Allen Harim Foods, LLC.","Allentown Refrigerated Terminals Inc.","Allied Caribbean Distribution","Allied Frozen Storage inc.","Allied Poultry Sales Co., Inc.","Allonco Inc.","Alma Cold Storage, Inc.","Alma Foods, LLC","Almi Inc.","Alpha Foods Co.","Alpha Omega Distributors, LTD","Alpine Meats Inc.","Alpine Sausage Company","Alta Vista Locker","Alto Valle Foods, Inc.","AmBar Foods","Amana Meat Shop &amp; Smokehouse","Amaro Foods Enterprise Inc","Amba Ham Company Inc.","Ambassador Meat Dist. Inc.","Amboy Group, LLC","Amend Packing Co.","AmeriCold Logistics","AmeriCold Logistics Crete Dist","AmeriCold Logistics, Inc.","AmeriCold Logistics, LLC","AmeriQual Group, LLC","AmeriQual Packaging","America New York Ri Wang Food Group","America's Best Steak","America's Catch Inc","America's Custom Brokers, Inc.","American Alliance Partners LLC","American Beef Packers, Inc.","American Butchers, Inc.","American Butchers, LLC","American Cold Storage","American Cold Storage-IN. Div.","American Consolidation &amp; Logistics","American Custom Meats LLC","American Egg Products, LLC","American Food Export Service","American Food Services, LLC","American Food Supplies LLC","American Food Systems Inc.","American Halal Meat","American Halal Meat Inc.","American Kitchen Delights Inc.","American Laboratories, Inc","American Laboratories, Inc.","American Meat Company","American Meat Processing Co., Inc.","American Outdoor Products, Inc","American Pasteurization Co LLC","American Pasteurization Company","American Samoa Government","American Skin Food Group LLC","American Soy Products, Inc.","American Specialty Food, Inc.","Americold","Americold - Ontario, Oregon","Americold Corporation","Americold Logistics","Americold Logistics Inc","Americold Logistics Inc.","Americold Logistics LLC","Americold Logistics, Inc.","Americold Logistics, LLC","Americold Logistics, LLC.","Americold, LLC","Ameripack Foods LLC","Ameristar Meats, Inc","Ameristar Meats, Inc.","Amick Farms, LLC","Amigo's Mexican Foods","Amigos Frozen Solutions","Amilas Foods","Aminchi Foods International, Inc.","Amity Packing Co. Inc.","Amor Nino Foods, Inc.","Amy Food Inc.","Anchor Distribution Service, Inc.","Anco Poultry Processing","Anderson Boneless Beef, Inc.","Anderson Produce Company, Inc.","Andrade Slaughterhouse","Andy's Deli &amp; Mikolajczk","Andy's Meats Inc.","Angelina Foods","Angus Meats, Inc.","Angy's Landolfi Food Group LLC","Anichini Bros Inc","Ankeny Cold Storage, LLC","Anna's Kitchen, Inc.","Annex Food Company","Ansaldos Sausage Corp.","Antonio Pena Distributors","Apache Foods LLC","Apex Cold Storage","Apex Cold Storage Co.","Apollo Ship Chandlers Inc.","Apostolic Christian World Relief","Appalachian Meats LLC","Appalachian Meats, LLC","Appalachian Smoked Meats","Appetito Provisions Co Inc","Apple Valley Farms","Applied Process Systems","Aquasouth","Ara Food Corp.","Arapahoe Foods Inc.","Arch Foods Inc","Archie's Foods, Inc.","Arck Foods, Inc.","Arctic Cold Storage","Arctic Foods Inc","Arctic Frozen Foods LLC","Arctic Logistics LLC","Argo Merchants Oakland","Argus Food Processing Corporation","Ariake USA, Inc.","Aries Prepared Beef Co.","Aristocrat Products, Inc","Arkansas Department of Corrections","Arkansas Refrigerated Services","Arko Veal Company, Inc.","Arlindo Catering Inc.","Arm National Foods","Armour - Eckrich Meats, LLC","Armour-Eckrich Meats LLC","Armour-Eckrich Meats, Inc.","Armour-Eckrich Meats, LLC","Arnold's &amp; Eddies Foods Inc.","Arsho Meat Products","Artisan Bread Co., LLC","Arveybell Farm Co.","Aryzta LLC","Asahi Foods Inc.","Asheville Packing Co.","Ashland Cold Storage Co.","Ashland Cold Storage Company","Ashland Sausage Company","Ashley Foods Inc.","Ashton Farms Custom Meats","Asiago Foods-USA, Inc.","Asianic Inc.","Aspen Foods Div Koch Meat Co.","Aspen Hollow Sheep Station Mobile Harvest Unit","Assemblers Inc.","Associated Brands Inc.","Associated Grocers of FL","Astra Foods Inc.","At Last! Gourmet Foods","Athena Foods","Athens Foods, Inc.","Athens Sausage Co.","Atkins Sheep Ranch Inc.","Atkins Sheep Ranch, Inc.","Atlanta Meat Company, Inc.","Atlanta Sausage Company","Atlantic Coast Freezers LLC","Atlantic Coast Freezers, LLC","Atlantic Veal &amp; Lamb Inc","Atlantic Veal &amp; Lamb LLC","Atlas Inspection, Inc.","Atlas Meat Company","Attilio Esposito Inc.","Au Bon Canard Foie Gras, Inc.","Aufschnitt Meats (W &amp; L Kosher Meats)","Augustine's Italian Village Inc.","Aunt Kitty's Foods Inc","Aurnish Enterprises Corp.","Aurora Packing Company, Inc.","Authentic Brands of Chicago","Avalanche Cheese Company","Avalon Meat Candy, LLC","Avanti Foods","Avenal State Prison","Ayrshire Farm Management, LC","Ayrshire Farm Management, LLC","Az Grass Raised Bone Broth","Azar and Company","Aztec Imperio","B &amp; G Foods, Inc. Distribution Center","B &amp; G Foods, North America, Inc.","B &amp; H Foods, Inc.","B &amp; K Meat","B &amp; M Provisions","B &amp; R Bierocks, Inc.","B &amp; R Meat Processing","B &amp; S Food Products","B&amp;B Food Products","B&amp;B Poultry Co., Inc.","B&amp;G Foods, Inc.","B&amp;G Meats Inc.","B&amp;M Processing","B&amp;O Island Style Chamorro Sausage","B&amp;P Quality Meats","B&amp;R Quality Meats","B. Joszt Inc.","B.W.J.W. Inc.","BAFS, Inc.","BD &amp; K Foods, Inc.","BDC Properties/Construction, Inc.","BEF Foods, Inc.","BIG RED FARMS","BJ's Wholessale Club","BKBG Enterprises","BLUE RIBBON MEAT CO","BMC Cali, Inc.","BONOLLO PROVISIONS CO., INC.","BPT Products Inc.","Babybeef USA Corp.","Bach Cuc Beef Jerky, Inc","Bachetti Bros. Market","Badalamente Sausage Co.","Badger Boiled Ham Co., Inc.","Baffo's Enterprises","Baffoni's Poultry Farm Inc.","Bagel Bites","Baily International","Baily International Inc.","Baily Meat","Baja Foods, LLC","Baja Frige Inc.","Baja Frige, Inc.","Bak Foods","Bakalars Sausage Co., Inc.","Baker River Deer Farm","Baker, Inc.","Bakkavor Foods USA, Inc","Bakkavor Foods USA, Inc.","Bakkavor Foods, USA Inc.","Balaton Food Inc.","Balkan Meat Market Inc.","Ballas Egg Products Corporation","Ballester Hermanos Inc.","Ballester Hermanos, Inc.","Bally United Produce, LTD","Balter Meat Co.","Bama Sea Products","Bangkok Meatball Co.","Bangkok Meatball Corp. #2","Banner Creek, LLC","Bar-S Foods Co.","Bar-S Foods Company","Barakah Kabab Inc.","Barbarossa LLC","Baretta Provision Inc","Baringer Brothers","Barkaat Foods, LLC","Barron's Creek Beef Jerky","Barry's Barbeque","Bartels Farms","Bartels Packing","Bas Foods Inc.","Bastan Corporation","Bateman","Batesville Cold, Inc.","Baton Rouge Shrimp Company","Battaglia Distributing Co Inc","Battistoni Italian Specialty Meats, LLC","Bauman's Butcher Block","Bavaria Sausage Kitchen, Inc.","Bavarian Meat Products","Bay Valley Foods","Bay View Packing Co.","Bayou Barataria Foods, LLC","Bear Creek Smokehouse","Beatrice Meat Company LLC","Beaver Meadow Road LLC","Beaver Street Fisheries Inc","Bee Bee Farms LLC","Beech-Nut Nutrition Corporation","Beef Burger Corp.","Beef International Inc.","Beef Jerky Unlimited","Beef Products, Inc.","Beef, Inc.","Behrmann Meat &amp; Processing, Inc.","BelCampo Butchery","BelGioioso Cheese Inc.","Bell Brands LLC","Bell Foods, LLC","Bell Tasty Foods Inc","Bella Bella Gourmet Foods, LLC","Bellingham Cold Storage","Bellisio Foods, Inc.","Belly's Take-N-Bake Foods, LLC","Belmont Sausage Company","Beltex Corporation","Bemar Snacks Inc.","Bemka Corporation","Ben E. Keith Company","Ben-Lee Processing Inc.","Bende &amp; Son Salami Co. Inc.","Benedetti Farms","Benner's Butcher Shoppe, LLC","Benny's Pork Skins","Bergen Wholesale Meats","Berger Wholesale Meat Co.","Berghorst Farms","Berix Coffee Deli LLC","Berk Lombardo Pkg. Co. Inc.","Berks Packing Co., Inc.","Berkshire Refrigerated Warehouse","Berkshire Refrigerated Warehousing, LLC","Bern Meat Plant","Bernatello's Pizza, Inc.","Bernthal Packing Inc.","Berry &amp; Sons Rababeh Isl Slau","Berry Veal Corp.","Bert Hazekamp &amp; Son Inc.","Bert Posess Inc","Best Buy Export LLC","Best Chicago Meat Co, LLC","Best Choice Meats","Best Express Foods, Inc.","Best Provision Co., Inc.","Best Value Food Products","Bestway Sandwiches Inc.","Bethlehem Sausage Works Inc.","Better Baked Foods of Erie, LLC","Better Baked Foods, Inc","Better Business - - - Better Foods","Better Meat Inc.","Beverly Processing, Inc.","BiRite Foodservice Distributors","Bianco &amp; Son Inc.","Bickel's Snack Foods Inc.","Bierig Brothers Inc.","Bierly's Meat Market","Big A Meatball Company","Big Boy Food Group LLC","Big Chef","Big Dog Meats LLC","Big Easy Foods Louisiana Cuisine","Big Meat, LLC","Bighams Ham Company","Bill Newsome Hams","Bill Newsomes Hams","Bill's Meat Market","Biltong USA","Bimmy's Food Made With Love","Bingman's Packing","Binkert's Meat Products, LLC","Birchwood Foods","Birdsboro Kosher Farms Corp.","Birmingham Dreamland, Inc.","Black Jack Beef Jerky","Black Pearl Platinum Brand LLC","Blackberry Farm","Blackhawk Specialty Foods","Blake's Turkey Farm","Blakent Brands, LLC","Blalock Meat Processing","Blalock Meats","Bland Correctional Center","Blankenship Farms Meat Processing","Blessed Meat, Inc.","Bleu Blanc Rouge","Bloemer's Food Sales Co.","Bloom-N-Egg Farm","Bloomfield Food, Inc.","Blossom Foods, LLC","Blount Fine Foods","Blount Fine Foods Corp.","Blount Seaford Corporation","Blue &amp; Gold Sausage Co.","Blue Chip Group, Inc","Blue Frog Foods LLC","Blue Grass Provision Company, Inc.","Blue Line Distributing","Blue Line Food Service Distribution","Blue Mountain Meats, Inc.","Blue Ridge Meats of Front Royal","Blue Star Meat Corp.","Blue Valley Meats, LLC","Bo Kho Saigon Beef Jerky Company Inc.","Bo-Bo Poultry Market Inc","Boar's Head Provision Co, Inc.","Boar's Head Provisions Co., Inc.","Bob Evans Farms Inc.","Bob's Better Beef, Inc.","Bobby Salazar's Food Products, Inc.","Boe Farms","Boesl Packing Co., Inc.","Boja's Foods, Inc.","Bolder Enterprises","Bolke-Miller Company","Bon Pate LLC","Bon Ton Food Service","Bonavista foods Inc.","Bonne Terre Meat Company","Bonneville Meats","Boone's Abattoir, Inc.","Border View Foods Inc.","Borenstein Caterers Inc.","Boston Brisket Company Inc.","Boston Salads and Provisions Company Inc.","Boston Sausage Co","Bostrom Farms Livestock and Meats","Bot N Bot","Boulder Organic Foods LLC","Bourgeois Smokehouse, LLC","Boutte's Boudin","Bowman &amp; Landes Turkeys, Inc.","Bowman's Butcher Shop, LLC","Boyd Specialties LLC","Boyd's Wholesale Meats","Boyle's Famous Corned Beef Co.","Braaitime LLC","Bradley's Country Store","Brakebush Brothers, Inc.","Brakebush Irving, Inc.","Brand Aromatics Inc","Brandy Meats, Inc.","Brasstown Beef, LLC","Braunger Foods, LLC","Bravo Foods and Bakery, LLC","Bread and Chocolate Inc","Brenneman's Meat Market Inc","Brett Anthony Foods","Brewer Meats","Briden Foods / Shannons Deli Meats","Bridgford Foods of Illinois","Bridgford Foods of North Carolina","Bright Oak Meats, Inc.","Brimhall Foods Company Inc.","Brinkman Turkey Farms, Inc.","Bristol Beef","Britt's Slaughterhouse","Broadleaf Inc.","Broadway Corn Beef Co., Inc.","Brock's Esto Meat Processing","Broken Arrow Ranch, Inc.","Bromley Meats","Bronson Locker","Brooke Industries, Inc.","Brooklyn Meat Sales","Brooks Meats, Inc.","Brooksville Meat Fabrication Center, Inc.","Brookwood Farms","Broomes Poultry","Brothee LLC","Brother &amp; Sister Food Services Inc.","Brother and Sister Food Services Inc.","Brother's Custom Processing Inc.","Brothers Meats Processors, LLC","Brown Packing Co., Inc.","Brown's Meat Locker","Bruce Packing Company","BrucePac - Durant","Brush Meat Processors LLC","Brushy Prairie Packing, Inc.","Bryan's Meat Inc.","Bryant's Meat Inc","Bubba Foods, LLC","Bucher Meats","Buckhead Beef","Buckhead Beef Charlotte","Buckhead Beef Northeast","Buckhead Beef Rhode Island","Buckhead Beef of Florida","Buddy's Kitchen, Inc.","Buedel Fine Meats and Provisions","Bueno Foods","Buffalo SAV, Inc.","Bum Foods LLC","Bumble Bee Seafoods LLC","Buona Vita Inc.","Bupik Inc.","Burger Maker Inc.","Burger's Ozark Country Cured","Burgundy Pasture Beef, LP","Burk Ridge Farms","Burk Ridge Farms, LLC","Burke Marketing Corporation","Burnett &amp; Son Meat Co., Inc.","Burnette Foods Inc.","Burris Logistics","Burris Retail Logistics","Burrito Kitchens Enterprises","Burt's Meat &amp; Poultry","Busetto Foods","Bush Brothers Provision Co., Inc.","Busseto Foods Inc","Busseto Foods, Inc.","Butch's Original Pizza, Inc.","Butcher Block &amp; Smokehouse, Inc.","Butterball LLC","Butterball, LLC","Butterfield Foods Company","Butterfield Foods, LLC","Buzz Products, Inc.","By George, Inc.","Byerly Foods International, Inc.","Bylada Foods LLC","Byron Center Wholesale Meats, Inc.","C &amp; C Processing, Inc.","C &amp; L Foods, Inc.","C &amp; S Poultry","C &amp; W Meat Packers","C&amp;C Meat Sales, Inc.","C&amp;F Meats","C&amp;F Packing Company","C&amp;H Meat Co.","C&amp;H Meat Company","C&amp;H Veal Co.","C&amp;J Tendermeat Co., Inc.","C&amp;K Unlimited, Inc.","C&amp;S Wholesale Meat Co.","C. Roy, Inc.","C. W Brown Foods Inc","C.R. Meats","CAFE SPICE GCT, INC.","CANNON COLD STORAGE","CARGILL FOOD DISTRIBUTION","CARGILL MEAT SOLUTIONS","CB Foods Inc.","CC Kitchens LLC","CCP Poultry &amp; Meat Corp.","CJ Foods Manufacturing Corporation","CLASSIC SEAFOOD GROUP, INC.","CLS Gourmet CL Saigon Food Company","CLW Foods, LLC","CM &amp; R, Inc.","CP Fresh","CPK Quality Foods","CPS Distribution Service","CPoW Livestock Processors Cooperative Association","CREATIVE FOOD PROCESSING","CRISF, Inc.","CS Best Food, Inc.","CS Meat International","CS2 Ranch Brand Jerky LLC","CSU - Fresno","CTH US INC.","CTH US Inc.","CTH US, LLC","CTI","CTI Foods Saginaw, LLC","CTI-SSI Food Services, LLC","CUISINE SOLUTIONS INC","CW Egg Products, LLC","Cabal Sausage Company, LLC","Cabin Hollow Butcher Shop, Inc","Cabool Kountry Meats, LLC","Cabrera's Spanish Sausages","Cabrito Market, Inc.","Cacique, Inc.","Caesar's Pasta, LLC","Caggiano Co.","CaiE Foods","Caine Warehousing LTD","Caito Foods, Inc.","Cajun Original Foods, Inc.","Cajun Specialty Meats","Cal Poly Meats","Calabro Cheese Corporation","Caldwell Farms","Caledonia Packing LLC","Calif. Qi Li's Braised Chicken LLC","California Farms Meat Company Inc.","California Jerky Factory, Inc.","California Ranch Food Company Inc.","California Sausage","California State University, Chico","Calihan Pork Processors","Callaway Packing, Inc.","Callicrate Cattle Co.","Calumet Diversified Meats Inc.","Camacho's Food Processing","Cambridge Packing Co","Camden Port Services Inc.","Camden Port Services, Inc.","Cameco Inc.","Camellia General Provision Co.","Cameron's British Foods Inc","Camino Real Foods, Inc.","Campbell Soup Company","Campbell Soup Supply Co., LLC","Campbell Soup Supply Company","Campbell Soup Supply Company L.L.C.","Campo Lindo Farms","Campofrio Food Group - America, Inc.","Campos Foods LLC","Cangialosi Specialty Sausage Company, Inc.","Canino's Sausage Company Co., Inc.","Canteen","Canteen/Covenco","Canyon Foods Dist Inc","Cape Code Cafe Foods","Capitol Cold Storage &amp; Distribution","Capitol Sausage &amp; Provisions Inc","Capitol Wholesale Meats, Inc.","Capri Restaurant","Captain Ken's Foods, Inc.","Captain's Fine Foods, LLC","Carando Gourmet Frozen Foods Corporation","Cargill","Cargill Food Dist.","Cargill Food Distribution","Cargill Fresno Grind","Cargill Kitchen Solutions, Inc.","Cargill Meat Solution Corporation","Cargill Meat Solutions","Cargill Meat Solutions Corp.","Cargill Meat Solutions Corpora","Cargill Meat Solutions Corporation","Cargill Meats Solutions Corporation","Cargill Value Added Meats","Cargill, Inc","Cargo International Consolidators Inc.","Caribbean Breeze Frozen Foods Corp.","Caribbean Food Delights","Caribbean Snacks &amp; More","Carl Buddig &amp; Company","Carl Buddig and Co","Carl Buddig and Company","Carl Streit &amp; Son Co.","Carl Venezia Meats","Carl's Boned Chicken, Inc.","Carla's Pasta Inc","Carle's Bratwurst, Inc.","Carlie C's Operation Center, Inc.","Carlotta's Kitchen LLC","Carlson Meat Processing, Inc.","Carlton Foods, Llc","Carlton Packing Company","Carnegie Deli Inc.","Carnival Brands","Carnivore Market","Carnivore Meat Company LLC","Carolina Catering dba Sky Caterers, Inc.","Carolina Fresh Foods","Carolina Packers, Inc.","Carolina Pride Foods, Inc.","Carrington Foods Inc","Carso's Pasta Company","Carters Creative Catering","Casa Blanca Santa Fe, LLC","Casa Di Carfagna","Casanova Market Inc.","Cascade Food Corp.","Cascade Wholesale Meats","Cascioppo Brothers Meats, Inc.","Case Farms Processing, Inc","Case Farms Processing, Inc.","Case Farms of North Carolina, Inc.","Case Farms of Ohio, Inc","Case Pork Roll Company Inc.","Casi di Pasta, Inc.","Casing Associates LLC","Casselton Cold Storage Inc.","Cassinelli Food Products, Inc.","Castelo Cold Storage LLC","Castillo Brothers Enterprises Inc.","Castle Rock Meats, Inc.","Castro Foods Wholesale, Inc","Catalina Finer Meat Corp.","Catelli Brothers Inc.","Cathay Foods Co.","Cattaneo BBQ Service","Cattaneo Bros, Inc.","Cauldron Soups LLC","Caver Meats","Caviness Beef Packers, Ltd.","Cedar Creek Beef Jerky L.L.C.","Cedar Valley Services","Cedar Valley Services, Inc.","Cedarlane Natural Foods, Inc.","Cedrick Inc.","Celebrity Foods Division of Atalanta Corporation","Center Cut Meats LLC","Center Fresh Egg Farm L.L.P.","Center Road Enterprises","Center for Advancement of Meat Production and Processing","Center of Innovation","Central Cold Storage","Central Falls Provisions Co., Inc.","Central Illinois Poultry Processing, LLC","Central KY Custom Meats, Inc.","Central Maine Meats","Central Maine Meats, LLC","Central Meat &amp; Provisions","Central Meat Packing","Central Meat Processors, Inc.","Central Meat and Provision Co","Central Missouri Meat &amp; Sausage","Central Oregon Butcher Boys","Central Snacks, Inc.","Central Storage &amp; Warehouse Co","Central Storage &amp; Warehouse Co.","Central Storage and Warehouse Co., Inc.","Central Valley Meat Co., Inc.","Centrum Valley Farms L.L.P.","Century Frozen Foods, LLC","Century Harvest Farms","Century Oak Packing Company","Century Packing Corp.","Cepas, Inc","Certified Meat Products","Cervantes Distributor Inc.","Cesina Sausage Co.","Chair City Meats Inc.","Chairman's Foods LLC","Chalet Market Inc.","Chambersburg Cold Storage Inc.","Chamblee Meats &amp; Supplies","Champ Meatball Company Inc.","Champion Foods LLC","Champion Gourmet Products","Champion Pizza","Champlain Beef Company Inc","Chandler Foods, Inc.","Chang Tuh","Chapa's Fried Pork Skins","Chaparro's Tamales","Chappell Hill Sausage Company","Charles Austin Holdings, LLC","Charles Ritter Inc","Charley &amp; Son's","Charlie's Meat Market","Charlie's Produce","Chaudhry Meat Company, Inc.","Che Pibe Gourmet Products","Chee Foo International Inc","Cheese Pleasers Inc.","Chef Charles Catering Company","Chef Creations LLC","Chef John Folse and Company","Chef Minute Meals Inc","Chef One Corporation","Chef's Choice Cash &amp; Carry Food Distribution Inc","Chef's Delight Packing Co, Inc","Chef's Requested Foods, Inc.","Chefs Commissary","Chelsea Food Services","Cheney Bros. Inc.","Cheney OFS, Inc.","Chenoa Locker, Inc.","Cher-Make Sausage Company","Cheraw Packing Plant, Inc.","Cherry Meat Company","Cherry Meat Packers, Inc.","Chez Dalida","Chia Hsiung Corp.","Chicago Cold Storage","Chicago Meat Authority","Chicago Seafood &amp; Restaurant Supply Inc.","Chicharon Poblano LLC","Chicharrones J&amp;J","Chicharrones Pacheco","Chick-A-Ray Poultry &amp; Egg Co., Inc.","Chickasha Meat Company, LLC","Chicopee Provision Company Inc.","Chieffo's Frozen Foods","Chihade International, Inc.","Childers Meat Company","Chillicothe Meats LLC","Chinamerica Food Manufacture, Inc.","Chinese Spaghetti Factory","Chino Valley Ranchers","Chip Steak &amp; Provision","Chirpy's Barbecue","Chisesi Brothers Meat Packing Company","Choice Canning Company Inc.","Choice Food Products Inc.","Choice Products USA","Chorizo Janitzio, Inc.","Chorizo Selecto","Chorizo de San Manuel Inc.","Chow Bros. Whsle Meats Co Inc","Christian Aid Ministries","Chungs Products LP","Chunwei, Inc.","Church of Jesus Christ of Latter Day Saints","Ciales Poultry, Inc.","Cibao Meat Products Inc.","CieBend Inc.","Cifelli &amp; Sons, Inc","Cimpl's, Inc.","Circle M Meats LLC","Circle Pines Sausage Haus, Inc.","Circle R Beef Inc.","Circle S Farms","Circle S Groom Sausage LLC","Cisneros Packing Company, Inc.","City Beef Company Inc","City Foods, Inc.","City Line Distributors Inc.","City Meat Company of Tampa Inc.","City Meat Steak Co., Inc.","Clair D Thompson &amp; Son's Inc","Clark Meat Science Laboratory","Clark's Poultry and Seafood","Clarmil Manufacturing Corp.","Claro's Italian Markets, Inc.","Classic Delight, Inc.","Classic Foods, LP","Classic Recipe Chili, Inc.","Claus Meats, Inc.","Clausen Meat Company Inc.","Claymont Food Co.","Claysburg Pizza Fundraisers","Clem's Custom Cut Meats","Clemens Food Group, LLC","Clint &amp; Sons Beef Jerky","Clipper Foods","Clougherty Packing Inc.","Clougherty Packing LLC","Cloverdale Foods Co.","Cloverleaf Cold Storage","Cloverleaf Cold Storage Cherokee","Cloverleaf Cold Storage Co","Cloverleaf Cold Storage Co.","Cloverleaf Cold Storage Company","Cloverleaf Cold Storage Le Mars","Club House Market, Inc","Clydes Sausage, Inc.","Coast Packing Company","Coastal Pacific Food Distributors","Coastal Processing, LLC"],[1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,1,1,1,2,1,1,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,1,2,1,1,1,1,1,1,1,1,1,2,1,1,1,1,1,1,1,1,1,2,1,1,1,7,1,1,1,1,1,1,1,1,1,2,7,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,7,1,1,2,1,1,1,1,1,1,1,1,1,1,4,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,1,1,1,1,1,11,1,2,21,2,1,8,3,9,1,1,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,3,1,2,1,1,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,3,1,1,1,1,1,1,1,1,2,2,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,3,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,3,1,1,1,1,1,1,1,1,1,1,5,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,3,1,5,2,8,3,1,6,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,4,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,2,1,1,1,2,1,11,1,1,4,1,1,1,1,1,1,1]],"container":"<table class=\"display\">\n  <thead>\n    <tr>\n      <th> <\/th>\n      <th>company<\/th>\n      <th>company_count<\/th>\n    <\/tr>\n  <\/thead>\n<\/table>","options":{"pageLength":8,"columnDefs":[{"className":"dt-right","targets":2},{"orderable":false,"targets":0}],"order":[],"autoWidth":false,"orderClasses":false,"lengthMenu":[8,10,25,50,100]}},"evals":[],"jsHooks":[]}</script>
```

???

Armoour 4 different spellings

---
## Inspecting and modifying data
### Checking for malformed values

* length() is a string function that counts the number of characters in a string

---
## Inspecting and modifying data
### Checking for malformed values

* Using length() and count() to test the zip column

```sql
SELECT length(zip),
       count(*) AS length_count
FROM meat_poultry_egg_inspect
GROUP BY length(zip)
ORDER BY length(zip) ASC;
```

<table>
 <thead>
  <tr>
   <th style="text-align:right;"> length </th>
   <th style="text-align:right;"> length_count </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 3 </td>
   <td style="text-align:right;"> 86 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 4 </td>
   <td style="text-align:right;"> 496 </td>
  </tr>
  <tr>
   <td style="text-align:right;"> 5 </td>
   <td style="text-align:right;"> 5705 </td>
  </tr>
</tbody>
</table>

* What happend here?

---
## Inspecting and modifying data
### Checking for malformed values

* Question: What happens if you store the value "0174" as 
  * text?
  * integer?

???

Die PLZ wurde als integer gespeichert...
---
## Inspecting and modifying data
### Checking for malformed values

* Filtering with length() to find short zip values

```sql
SELECT st,
       count(*) AS st_count
FROM meat_poultry_egg_inspect
WHERE length(zip) < 5
GROUP BY st
ORDER BY st ASC;
```

---
class: center, middle

<table>
 <thead>
  <tr>
   <th style="text-align:left;"> st </th>
   <th style="text-align:right;"> st_count </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:left;"> CT </td>
   <td style="text-align:right;"> 55 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> MA </td>
   <td style="text-align:right;"> 101 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> ME </td>
   <td style="text-align:right;"> 24 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> NH </td>
   <td style="text-align:right;"> 18 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> NJ </td>
   <td style="text-align:right;"> 244 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> PR </td>
   <td style="text-align:right;"> 84 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> RI </td>
   <td style="text-align:right;"> 27 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> VI </td>
   <td style="text-align:right;"> 2 </td>
  </tr>
  <tr>
   <td style="text-align:left;"> VT </td>
   <td style="text-align:right;"> 27 </td>
  </tr>
</tbody>
</table>

---
## Inspecting and modifying data
### Items to correct

* Missing values for three rows in the st column

* Inconsistent spelling of at least one company’s name

* Inaccurate ZIP Codes due to file conversion

---
## Inspecting and modifying data
### Modifying tables, columns and data

* ALTER TABLE

* Review additional [`ALTER TABLE` Options](https://www.postgresql.org/docs/current/sql-altertable.html) in PostgreSQL

* UPDATE

* ADD COLUMN

* ALTER COLUMN

* DROP COLUMN

---
## Inspecting and modifying data
### Modifying tables with ALTER TABLE

* Adding a column

* ALTER TABLE table ADD COLUMN column data_type;

* Delete a column

* ALTER TABLE table DROP COLUMN column;

* To change the data type of a column, we would use this code:

* ALTER TABLE table ALTER COLUMN column SET DATA TYPE data_type;

---
## Inspecting and modifying data
### Modifying tables with ALTER TABLE

* Adding a NOT NULL constraint to a column will look like the following:

* ALTER TABLE table ALTER COLUMN column SET NOT NULL;

Note that in PostgreSQL and some other systems, adding a constraint to the table causes all rows to be checked to see whether they comply with the constraint. If the table has millions of rows, this could take a while.

* Removing the NOT NULL constraint looks like this:
 * ALTER TABLE table ALTER COLUMN column DROP NOT NULL;

---
## Inspecting and modifying data
### Modifying values with UPDATE

* The UPDATE statement modifies the data in a column in all rows or in a subset of rows that meet a condition.

```
UPDATE table
SET column = value
```

* The new value to place in the column can be a string, number, the name of another column, or even a query or expression that generates a value.

* We can update values in multiple columns at a time by adding additional columns and source values, and separating each column and value statement with a comma:

```
UPDATE table
SET column_a = value,
SET column_b = value;

```
---
## Inspecting and modifying data
### Modifying values with UPDATE

* Restrict update to certain rows with WHERE

```
UPDATE table
SET column = value
WHERE criteria; 
```

* Update one table with values from another table.

* Standard ANSI SQL requires that we use a __subquery__ (we cover this in a seperate presentation), a query inside a query, to specify which values and rows to update:

```
UPDATE table
SET column = (SELECT column
              FROM table_b
              WHERE table.column = table_b.column)
WHERE EXISTS (SELECT column
              FROM table_b
              WHERE table.column = table_b.column);
```
???
 * The value portion of the SET clause is a subquery, which is a SELECT statement inside parentheses that generates the values for the update.

* Similarly, the WHERE EXISTS clause uses a SELECT statement to generate values that serve as the filter for the update.

---
## Inspecting and modifying data
### Modifying values with UPDATE

* Some database managers offer additional syntax for updating across tables.

* PostgreSQL supports the ANSI standard but also a simpler syntax using a FROM clause for updating values across tables:

```
UPDATE table
SET column = table_b.column
FROM table_b
WHERE table.column = table_b.column; 
```

* When you execute an UPDATE statement, PostgreSQL returns a message stating UPDATE along with the number of rows affected.

---
## Inspecting and modifying data
### Creating backup tables

* Backing up a table (create an identical table):

```sql
CREATE TABLE meat_poultry_egg_inspect_backup 
AS (SELECT * 
    FROM meat_poultry_egg_inspect);
```

* Check number of records:

```sql
SELECT 
    (SELECT count(*) FROM meat_poultry_egg_inspect) AS original,
    (SELECT count(*) FROM meat_poultry_egg_inspect_backup) AS backup;
```

--
<table>
 <thead>
  <tr>
   <th style="text-align:right;"> original </th>
   <th style="text-align:right;"> backup </th>
  </tr>
 </thead>
<tbody>
  <tr>
   <td style="text-align:right;"> 6287 </td>
   <td style="text-align:right;"> 6287 </td>
  </tr>
</tbody>
</table>

---
## Inspecting and modifying data
### Creating backup tables

* Indexes are not copied when creating a table backup using the CREATE TABLE statement.

* If you decide to run queries on the backup, be sure to create a separate index on that table.

---
## Inspecting and modifying data
### Creating a column copy

* Creating and filling the st_copy column with ALTER TABLE and UPDATE

```sql
-- add a new column st_copy
ALTER TABLE meat_poultry_egg_inspect ADD COLUMN st_copy varchar(2);
```

```sql
-- fill the new column with st
UPDATE meat_poultry_egg_inspect
SET st_copy = st;
```
* Checking values in the st and st_copy columns

```sql
SELECT st,
       st_copy
FROM meat_poultry_egg_inspect
ORDER BY st;
```

---
class: center, middle

---
## Inspecting and modifying data
### Updating rows where values are missing

* Atlas Inspection is located in Minnesota; Hall-Namie Packing is in Alabama; and Jones Dairy is in Wisconsin:

```sql
UPDATE meat_poultry_egg_inspect
SET st = 'MN'
WHERE est_number = 'V18677A';

```

---

## Inspecting and modifying data
### Updating rows where values are missing

```sql

UPDATE meat_poultry_egg_inspect
SET st = 'AL'
WHERE est_number = 'M45319+P45319';

```

```sql

UPDATE meat_poultry_egg_inspect
SET st = 'WI'
WHERE est_number = 'M263A+P263A+V263A';
```

---
## Inspecting and modifying data
### Updating rows where values are missing

* If something goes wrong, we could restore the original st column values:

A) Restoring from the column backup
 
``` 
UPDATE meat_poultry_egg_inspect
SET st = st_copy;
```

B) Restoring from the table backup

```
UPDATE meat_poultry_egg_inspect original
SET st = backup.st
FROM meat_poultry_egg_inspect_backup backup
WHERE original.est_number = backup.est_number; 
```
---
## Inspecting and modifying data
### Updating values for consistency

* In our data, we have the following spelling variations:

```
Armour - Eckrich Meats, LLC
Armour-Eckrich Meats LLC
Armour-Eckrich Meats, Inc.
Armour-Eckrich Meats, LLC
```

* We use UPDATE to standardize the spelling

* However, we do not alter the original column but first create a new one, which we name `company_standard`

---
## Inspecting and modifying data
### Updating values for consistency

* Creating and filling the company_standard column:

```sql
ALTER TABLE meat_poultry_egg_inspect ADD COLUMN company_standard varchar(100);
```

```sql
UPDATE meat_poultry_egg_inspect
SET company_standard = company;
```

---
## Inspecting and modifying data
### Updating values for consistency

* Let's standardize any name with "Armour" to "Armour-Eckrich Meats"

* Use UPDATE to modify field values that match a string

```sql
UPDATE meat_poultry_egg_inspect
SET company_standard = 'Armour-Eckrich Meats'
WHERE company LIKE 'Armour%';
```

---
## Inspecting and modifying data
### Concatenation

* Now we come back to the issue with the column ZIP (missing zeros at the beginning)

* Creating and filling the zip_copy column:

```sql
ALTER TABLE meat_poultry_egg_inspect ADD COLUMN zip_copy varchar(5);
```

```sql
UPDATE meat_poultry_egg_inspect
SET zip_copy = zip;
```

---
## Inspecting and modifying data
### Concatenation

* Modify codes in the zip column missing two leading zeros for Puerto Rico (PR) and the Virgin Islands (VI):

```sql
UPDATE meat_poultry_egg_inspect
SET zip = '00' || zip
WHERE st IN('PR','VI') AND length(zip) = 3;
```

* The double-pipe string operator (||) performs concatenation.

???
* We use SET to set the zip column to a value that is the result of the concatenation of the string 00 and the existing content of the zip column.

* We limit the UPDATE to only those rows where the st column has the state codes PR and VI using the IN comparison operator and add a test for rows where the length of zip is 3.

This entire statement will then only update the zip values for Puerto Rico and the Virgin Islands. Run the query; PostgreSQL should return the message UPDATE 86, which is the number of rows we expect to change based on our earlier count in Listing 9-6.

---
## Inspecting and modifying data
### Concatenation

* Modify codes in the zip column missing one leading zero

```sql
UPDATE meat_poultry_egg_inspect
SET zip = '0' || zip
WHERE st IN('CT','MA','ME','NH','NJ','RI','VT') AND length(zip) = 4;
```

---
## Inspecting and modifying data
### Concatenation

* Using length() and count() to test the zip column

```sql
SELECT length(zip),
       count(*) AS length_count
FROM meat_poultry_egg_inspect
GROUP BY length(zip)
ORDER BY length(zip) ASC;
```

---
## Inspecting and modifying data
### Concatenation

* Before concatenation

* After concatenation

---
## Inspecting and modifying data
### Updating values across tables

* Let’s say we’re setting an inspection date for each of the companies in our table.

* We want to do this by U.S. regions, such as Northeast, Pacific, and so on, but those regional designations don’t exist in our table.

* However, they do exist in a data set we can add to our database that also contains matching st state codes.

* This means we can use that other data as part of our UPDATE statement to provide the necessary information.

---
## Inspecting and modifying data
### Updating values across tables

*Let’s begin with the New England region to see how this works.

Creating and filling a state_regions table:

```sql
CREATE TABLE state_regions (
    st varchar(2) CONSTRAINT st_key PRIMARY KEY,
    region varchar(20) NOT NULL
);
```

???
We’ll create two columns in a state_regions table: one containing the two-character state code st and the other containing the region name. We set the primary key constraint to the st column, which holds a unique st_key value to identify each state. In the data you’re importing, each state is present and assigned to a U.S. Census region, and territories outside the United States are labeled as outlying areas. We’ll update the table one region at a time.

---
## Inspecting and modifying data
### Updating values across tables

* Add a column for inspection dates, and then fill in that column with the New England states.

```sql
COPY state_regions
FROM '/tmp/state_regions.csv'
WITH (FORMAT CSV, HEADER, DELIMITER ',');
```

---
## Inspecting and modifying data
### Updating values across tables

* Adding and updating an inspection_date column

```sql
ALTER TABLE meat_poultry_egg_inspect ADD COLUMN inspection_date date;
```

```sql
UPDATE meat_poultry_egg_inspect AS inspect
SET inspection_date = '2019-12-01'
WHERE EXISTS (SELECT state_regions.region
              FROM   state_regions
              WHERE  inspect.st = state_regions.st 
              AND    state_regions.region = 'New England');
```

???
The ALTER TABLE statement creates the inspection_date column in the meat_poultry_egg_inspect table. In the UPDATE statement, we start by naming the table using an alias of inspect to make the code easier to read

➊. Next, the SET clause assigns a date value of 2019-12-01 to the new inspection_date column

➋. Finally, the WHERE EXISTS clause includes a subquery that connects the meat_poultry_egg_inspect table to the state_regions table we created in Listing 9-18 and specifies which rows to update

➌. The subquery (in parentheses, beginning with SELECT) looks for rows in the state_regions table where the region column matches the string New England. At the same time, it joins the meat_poultry_egg_inspect table with the state_regions table using the st column from both tables. In effect, the query is telling the database to find all the st codes that correspond to the New England region and use those codes to filter the update.

---
## Inspecting and modifying data
### Updating values across tables

* Viewing updated inspection_date values

```sql
SELECT st, inspection_date
FROM meat_poultry_egg_inspect
GROUP BY st, inspection_date
ORDER BY st;
```

---
class: center, middle

```{=html}
<div id="htmlwidget-453d18b0b7eaae9b4ab8" style="width:100%;height:auto;" class="datatables html-widget"></div>
<script type="application/json" data-for="htmlwidget-453d18b0b7eaae9b4ab8">{"x":{"filter":"none","fillContainer":false,"data":[["1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20"],["AK","AL","AR","AS","AZ","CA","CO","CT","DC","DE","FL","GA","GU","HI","IA","ID","IL","IN","KS","KY"],[null,null,null,null,null,null,null,"2019-12-01",null,null,null,null,null,null,null,null,null,null,null,null]],"container":"<table class=\"display\">\n  <thead>\n    <tr>\n      <th> <\/th>\n      <th>st<\/th>\n      <th>inspection_date<\/th>\n    <\/tr>\n  <\/thead>\n<\/table>","options":{"pageLength":8,"order":[],"autoWidth":false,"orderClasses":false,"columnDefs":[{"orderable":false,"targets":0}],"lengthMenu":[8,10,25,50,100]}},"evals":[],"jsHooks":[]}</script>
```

---
## Inspecting and modifying data
### Deleting data

* DELETE FROM: Deleting all rows from a table

```
DELETE FROM table_name;
```

* Alternatively, you can drop the entire table from the databse

```
DROP TABLE table_name;
```

* Delete matching cases:

```
DELETE FROM table_name
WHERE expression;
```
---
## Inspecting and modifying data
### Deleting data

* Delete rows matching an expression

```
DELETE FROM meat_poultry_egg_inspect
WHERE st IN('PR','VI');
```
---
## Inspecting and modifying data
### Deleting data

* DROP COLUMN: Delete columns

* Remove a column from a table using DROP

```
ALTER TABLE meat_poultry_egg_inspect DROP COLUMN zip_copy;
```

* Remove a table from a database using DROP

```
DROP TABLE meat_poultry_egg_inspect_backup;
```
---
## Inspecting and modifying data
### Transaction blocks

* The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation.

* The intermediate states between the steps are not visible to other concurrent transactions.

* If some failure occurs that prevents the transaction from completing, then none of the steps affect the database at all.

Source: [PostgreSQL](https://www.postgresql.org/docs/current/tutorial-transactions.html)

---
## Inspecting and modifying data
### Transaction blocks

* START TRANSACTION signals the start of the transaction block.

* In PostgreSQL, you can also use the non-ANSI SQL BEGIN keyword.

* COMMIT signals the end of the block and saves all changes.

* ROLLBACK signals the end of the block and reverts all changes.

> When you start a transaction, any changes you make to the data aren’t visible to other database users until you execute COMMIT

---
## Inspecting and modifying data
### Transaction blocks

* We can apply this transaction block technique to review changes a query makes and then decide whether to keep or discard them.

* let’s say we’re cleaning dirty data related to the company AGRO Merchants Oakland LLC.

```
AGRO Merchants Oakland LLC
AGRO Merchants Oakland LLC
AGRO Merchants Oakland, LLC 
```

* We want the name to be consistent, so we’ll remove the comma from the third row using an UPDATE query, as we did earlier.

* But this time we’ll check the result of our update before we make it final (and we’ll purposely make a mistake we want to discard).

---

### Transaction block demo

* Demonstrating a transaction block

* START TRANSACTION

```sql
START TRANSACTION;
```

* UPDATE TABLE (with error in spelling)

```sql
UPDATE meat_poultry_egg_inspect
SET company = 'AGRO Merchantss Oakland LLC'
WHERE company = 'AGRO Merchants Oakland, LLC';
```

---

### Transaction block demo

* Show result

```sql
-- view changes
SELECT company
FROM meat_poultry_egg_inspect
WHERE company LIKE 'AGRO%'
ORDER BY company;
```

```{=html}
<div id="htmlwidget-329f23fc59543a635cff" style="width:100%;height:auto;" class="datatables html-widget"></div>
<script type="application/json" data-for="htmlwidget-329f23fc59543a635cff">{"x":{"filter":"none","fillContainer":false,"data":[["1","2","3"],["AGRO Merchants Oakland LLC","AGRO Merchants Oakland LLC","AGRO Merchantss Oakland LLC"]],"container":"<table class=\"display\">\n  <thead>\n    <tr>\n      <th> <\/th>\n      <th>company<\/th>\n    <\/tr>\n  <\/thead>\n<\/table>","options":{"pageLength":8,"order":[],"autoWidth":false,"orderClasses":false,"columnDefs":[{"orderable":false,"targets":0}],"lengthMenu":[8,10,25,50,100]}},"evals":[],"jsHooks":[]}</script>
```

---

### Transaction block demo

* Revert changes with ROLLBACK

```sql
ROLLBACK;
```

* Show result

```sql
-- view changes
SELECT company
FROM meat_poultry_egg_inspect
WHERE company LIKE 'AGRO%'
ORDER BY company;
```

```{=html}
<div id="htmlwidget-2abfb9c6a566f87d0294" style="width:100%;height:auto;" class="datatables html-widget"></div>
<script type="application/json" data-for="htmlwidget-2abfb9c6a566f87d0294">{"x":{"filter":"none","fillContainer":false,"data":[["1","2","3"],["AGRO Merchants Oakland LLC","AGRO Merchants Oakland LLC","AGRO Merchants Oakland, LLC"]],"container":"<table class=\"display\">\n  <thead>\n    <tr>\n      <th> <\/th>\n      <th>company<\/th>\n    <\/tr>\n  <\/thead>\n<\/table>","options":{"pageLength":8,"order":[],"autoWidth":false,"orderClasses":false,"columnDefs":[{"orderable":false,"targets":0}],"lengthMenu":[8,10,25,50,100]}},"evals":[],"jsHooks":[]}</script>
```

???
We’ll run each statement separately, beginning with START TRANSACTION; ➊. The database responds with the message START TRANSACTION, letting you know that any succeeding changes you make to data will not be made permanent unless you issue a COMMIT command. Next, we run the UPDATE statement, which changes the company name in the row where it has an extra comma. I intentionally added an extra s in the name used in the SET clause ➋ to introduce a mistake. When we view the names of companies starting with the letters AGRO using the SELECT statement ➌, we see that, oops, one company name is misspelled now:
company

```
AGRO Merchants Oakland LLC
AGRO Merchants Oakland LLC
AGRO Merchantss Oakland LLC 
```

Instead of rerunning the UPDATE statement to fix the typo, we can simply discard the change by running the ROLLBACK; ➍ command. When we rerun the SELECT statement to view the company names, we’re back to where we started:

AGRO Merchants Oakland LLC
AGRO Merchants Oakland LLC
AGRO Merchants Oakland, LLC

From here, you could correct your UPDATE statement by removing the extra s and rerun it, beginning with the START TRANSACTION statement again. If you’re happy with the changes, run COMMIT; to make them permanent.
---

## Thank you!

[Prof. Dr. Jan Kirenz](https://www.kirenz.com)

HdM Stuttgart  
Nobelstraße 10  
70569 Stuttgart

![](https://upload.wikimedia.org/wikipedia/commons/5/5e/HdM_Logo.svg)