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?



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/insert-into-statement/insert-into-statement-exercise-11.php