Adding, Updating, or Deleting Records

Let’s pretend DC is a state!  Add DC as a state to the state table and add data for all the fields.  You can look at the Structure tab in phpMyAdmin to see it.  Here is some data:

• What should our state name be?  Postal code stays same…let’s just do “The Washington” for now.
• Capitol and Biggest City will stay “Washington”
• 2010 Population:  604,453
• 2000 Population: 572,046
• Region – do you think we are Northeast or Southeast?  Maryland is Northeast…Virginia is Southeast.  Let’s go with Northeast – 1
• LandArea – 68.34
• You should know how many electors we have…..what color we are..and how many counties.
• If we became a state – we would have 1 house seat.
• Let’s dream that we get statehood next year – April 15, 2018

Now…what is the SQL to do that?  Hint – there is a space after every comma…

Let’s add Puerto Rico too!  But, for Puerto Rico – let’s just add the fields that we know since we aren’t sure when they will become a state or how many electors or house members they will have.  And they don’t really have counties.

• State name stays same… Postal code stays same..
• Capitol and Biggest City will stay is….”San Juan”
• 2010 Population:  3.722 million
• 2000 Population: 3.811 million
• Region – Southeast – 2
• LandArea – 3,515
• Color – Blue!!!!

Now…what is the SQL to do that?  Hint – there is a space after every comma…

How many states have 3 electors?

How many states are in region 2?

What SQL would you write (and do so!) to insert a state with the following values:

• Name: Test
• PostalCode:  TE
• Region: 3
• Electors: 1
• HouseSeats: 1

Based on their 2010 Population, how many electors and house seats should Puerto Rico have?  Do a SQL statement to pull out states with similar population.

Based on the last question, update Puerto Rico’s electors and house seats.  What is the SQL?

How many states have 7 electors?

What SQL did you use to get that count?

Update the state with postal code TE to have a Capitol called “Capitol” and LandArea of 55,000.  What is the SQL you used?

What is the SQL to delete the Test State?