w3resource

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

MySQL Alter Table Statement: Exercise-2 with Solution

Write a MySQL statement to add a column region_id 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' and its data type is set to INT (integer).

ALTER TABLE locations

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

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 |
+----------------+--------------+------+-----+---------+-------+
| 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    |       |
| region_id      | int(11)      | 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).

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

Previous: Write a SQL statement to rename the table countries to country_new.
Next: Write a SQL statement to add a columns ID as the first column of the table locations.

What is the difficulty level of this exercise?



Become a Patron!

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://w3resource.com/mysql-exercises/alter-table-statement/alter-table-exercise-2.php