MySQL Alter Table Statement Exercises: Add a columns ID as the first column of the table locations

MySQL Alter Table Statement: Exercise-3 with Solution

Write a MySQL statement to add a columns ID as the first column of 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 'ID', its data type is set to INT (integer), and it is positioned as the first column.

ALTER TABLE locations

-- Add a new column named 'ID' to the 'locations' table.
ADD ID INT FIRST;

Let execute the above code in MySQL command prompt

See the structure of the table after alteration.

mysql> SHOW COLUMNS FROM locations;
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| ID             | int(11)      | YES  |     | NULL    |       |
| 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    |       |
+----------------+--------------+------+-----+---------+-------+

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 ID INT FIRST;: This specifies the action to be taken. It adds a new column named 'ID' to the 'locations' table, and the data type for this column is set to INT (integer). The "FIRST" keyword indicates that the new column should be positioned as the first column 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 column region_id to the table locations.
Next: Write a SQL statement to add a column region_id after state_province to the table locations.