Create a MySQL table named countries with columns country_id, country_name, and region_id.

MySQL Create Tables: Exercise-1 with Solution

1. Write a MySQL query to create a simple table countries including columns country_id, country_name and region_id.

Sample Solution:

-- Creating a table named 'countries' to store information about countries

CREATE TABLE 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.01 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).

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

Previous: MySQL Create Table
Next: Write a SQL statement to create a simple table countries including columns country_id,country_name and region_id which is already exists.