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:
- INSERT INTO countries: Specifies the insertion operation into the 'countries' table.
- VALUES(501, 'India', 102): Specifies the values to be inserted into the columns of the 'countries' table. In this case:
- 501 is inserted into the 'COUNTRY_ID' column.
- 'India' is inserted into the 'COUNTRY_NAME' column.
- 102 is inserted into the 'REGION_ID' column.
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous:Write a MySQL query to insert rows into the table countries in which the value of country_id column will be unique and auto incremented.
Next:Write a MySQL query to insert rows in the job_history table in which one column job_id is containing those values which are exists in job_id column of jobs table.
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/insert-into-statement/insert-into-statement-exercise-11.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics