Create a MySQL table named countries if it doesn't already exist

MySQL Create Tables: Exercise-2 with Solution

2. Write a MySQL query to create a simple table countries including columns country_id, country_name and region_id which is already exists.

Sample Solution:

-- Creating a table named 'countries' if it doesn't already exist, to store information about countries

CREATE TABLE IF NOT EXISTS countries(
    -- Column to store the two-letter country code
    COUNTRY_ID varchar(2),

    -- Column to store the name of the country (up to 40 characters)
    COUNTRY_NAME varchar(40),

    -- Column to store the region ID with decimal precision of 10, 0
    REGION_ID decimal(10,0)
);

Let execute the above code in MySQL command prompt

Here is the structure of the table:

mysql> DESC countries;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| COUNTRY_ID   | varchar(2)    | YES  |     | NULL    |       |
| COUNTRY_NAME | varchar(40)   | YES  |     | NULL    |       |
| REGION_ID    | decimal(10,0) | YES  |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
3 rows in set (0.13 sec)

Explanation:

The above MySQL code creates a table named "countries" with three columns:

• COUNTRY_ID: A column to store two-letter country codes (varchar type with a length of 2).
• COUNTRY_NAME: A column to store the names of countries (varchar type with a maximum length of 40 characters).
• REGION_ID: A column to store region IDs with a decimal precision of 10 and no decimal places (decimal type with precision 10, scale 0). The IF NOT EXISTS clause ensures that the table is only created if it doesn't already exist in the database.

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

Previous: Write a SQL statement to create a simple table countries including columns country_id,country_name and region_id.
Next: Write a SQL statement to create the structure of a table dup_countries similar to countries.