w3resource

Insert records into the MySQL 'countries' table with auto-incremented unique values for the 'country_id' column, and default 'N/A' for the 'country_name' column

MySQL insert into Statement: Exercise-11 with Solution

11. Write a MySQL query to insert records into the table countries to ensure that the country_id column will not contain any duplicate data and this will be automatically incremented and the column country_name will be filled up by 'N/A' if no value assigned for that column.

Create the table countries.


CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID integer NOT NULL AUTO_INCREMENT PRIMARY KEY,
COUNTRY_NAME varchar(40) NOT NULL DEFAULT 'N/A',
REGION_ID integer NOT NULL
);

Sample Solution:

-- Inserting a new record into the 'countries' table
INSERT INTO countries VALUES(501, 'India', 102);

Let execute the above code in MySQL command prompt.

Here is the structure of the table:

mysql> SELECT * FROM countries;
+------------+--------------+-----------+
| COUNTRY_ID | COUNTRY_NAME | REGION_ID |
+------------+--------------+-----------+
|        501 | India        |       102 |
+------------+--------------+-----------+
1 row in set (0.00 sec)
INSERT INTO countries(region_id) VALUES(109);

Let execute the above code in MySQL command prompt.

Here is the structure of the table:

mysql> SELECT * FROM countries;
+------------+--------------+-----------+
| COUNTRY_ID | COUNTRY_NAME | REGION_ID |
+------------+--------------+-----------+
|        501 | India        |       102 |
|        502 | N/A          |       109 |
+------------+--------------+-----------+
2 rows in set (0.00 sec)
INSERT INTO countries(country_name,region_id) VALUES('Australia',121);

Let execute the above code in MySQL 5.6 command prompt.

Here is the structure of the table:

mysql> SELECT * FROM countries;
+------------+--------------+-----------+
| COUNTRY_ID | COUNTRY_NAME | REGION_ID |
+------------+--------------+-----------+
|        501 | India        |       102 |
|        502 | N/A          |       109 |
|        503 | Australia    |       121 |
+------------+--------------+-----------+
3 rows in set (0.00 sec)

Explanation:

Here's a breakdown of the above MySQL query:

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.