MySQL Alter Table Statement Exercises: Drop the column city from the table locations
MySQL Alter Table Statement: Exercise-6 with Solution
Write a MySQL statement to drop the column city from 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 removing a column.
-- The 'city' column is being dropped from the table.
ALTER TABLE locations
-- Drop the 'city' column from the 'locations' table.
DROP city;
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 | | | 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.
- DROP city;: This specifies the action to be taken. It removes (drops) the 'city' column from the 'locations' table.
The above result shows that there is no 'city' column; it has been dropped from the table locations.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a SQL statement change the data type of the column country_id to integer in the table locations.
Next: Write a SQL statement to change the name of the column state_province to state, keeping the data type and size same.
What is the difficulty level of this exercise?
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-6.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics