MySQL Alter Table Statement Exercises: Change the name of the column state_province to state, keeping the data type and size same

MySQL Alter Table Statement: Exercise-7 with Solution

Write a MySQL statement to change the name of the column state_province to state, keeping the data type and size same.

Here is the structure of the table locations.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID    | decimal(4,0) | YES  |     | NULL    |       |
| STREET_ADDRESS | varchar(40)  | YES  |     | NULL    |       |
| POSTAL_CODE    | varchar(12)  | YES  |     | NULL    |       |
| CITY           | varchar(30)  | YES  |     | NULL    |       |
| STATE_PROVINCE | varchar(25)  | YES  |     | NULL    |       |
| COUNTRY_ID     | varchar(2)   | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

Code:

-- This SQL statement is used to alter the 'locations' table by removing one column and adding another.
-- The 'state_province' column is being dropped, and a new 'state' column with a varchar(25) data type is being added after the 'city' column.

ALTER TABLE locations

-- Drop the 'state_province' column from the 'locations' table.
DROP state_province,

-- Add a new column named 'state' with a varchar(25) data type after the 'city' column.
ADD state varchar(25)
AFTER city;

Let execute the above code in MySQL command prompt

In that case, if there are no data in the table, the old column will be removed and new column will be create, no problem at all, but if data in the table you can use the following statement :

ALTER TABLE locations
CHANGE state_province state varchar(25);

Now see the structure of the table locations after alteration.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| LOCATION_ID    | decimal(4,0) | YES  |     | NULL    |       |
| STREET_ADDRESS | varchar(40)  | YES  |     | NULL    |       |
| POSTAL_CODE    | varchar(12)  | YES  |     | NULL    |       |
| CITY           | varchar(30)  | YES  |     | NULL    |       |
| state          | varchar(25)  | YES  |     | NULL    |       |
| COUNTRY_ID     | varchar(2)   | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+

Explanation:

Here's a brief explanation of the above MySQL code:

• ALTER TABLE locations: This part of the query indicates that we want to make changes to the structure of the 'locations' table.
• DROP state_province,: This specifies the first action to be taken. It removes (drops) the 'state_province' column from the 'locations' table.
• ADD state varchar(25) AFTER city;: It adds a new column named 'state' with a varchar(25) data type to the 'locations' table. The "AFTER city" indicates that the new column should be positioned after the existing 'city' column.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a SQL statement to drop the column city from the table locations.
Next: Write a SQL statement to add a primary key for the columns location_id in the locations table.