MySQL Alter Table Statement Exercises: Add a column region_id after state_province to the table locations

MySQL Alter Table Statement: Exercise-4 with Solution

Write a MySQL statement to add a column region_id after state_province to the table locations.

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 adding a new column.
-- The new column is named 'region_id' with a data type of INT (integer),
-- and it is positioned after the existing column 'state_province'.

ALTER TABLE locations

-- Add a new column named 'region_id' to the 'locations' table.
ADD region_id INT 

-- Position the new column 'region_id' after the existing column 'state_province'.
AFTER state_province;

Let execute the above code in MySQL command prompt

Here is 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_PROVINCE | varchar(25)  | YES  |     | NULL    |       |
| region_id      | int(11)      | 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 statement indicates that you want to make changes to the structure of the 'locations' table.
• ADD region_id INT: This specifies the action to be taken. It adds a new column named 'region_id' to the 'locations' table, and the data type for this column is set to INT (integer).
• AFTER state_province;: This further refines the modification. It specifies that the new column 'region_id' should be positioned after the existing column 'state_province' in the table.

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

Previous: Write a SQL statement to add a columns ID as the first column of the table locations.
Next: Write a SQL statement change the data type of the column country_id to integer in the table locations.