w3resource

MySQL Alter Table Statement Exercises: Change the data type of the column country_id to integer in the table locations

MySQL Alter Table Statement: Exercise-5 with Solution

Write a MySQL statement change the data type of the column country_id to integer in 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 modifying the data type of an existing column.
-- The 'country_id' column's data type is changed to INT (integer).

ALTER TABLE locations

-- Modify the data type of the 'country_id' column to INT (integer).
MODIFY country_id INT;

Let execute the above code in MySQL command prompt

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_PROVINCE | varchar(25)  | YES  |     | NULL    |       |
| country_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 we want to change the structure of the 'locations' table.
  • MODIFY country_id INT;: This specifies the action to be taken. It modifies the data type of the 'country_id' column to INT (integer).

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 after state_province to the table locations.
Next: Write a SQL statement to drop the column city from 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-5.php