w3resource

Insert a unique row into the MySQL 'jobs' table for the 'job_id' column

MySQL insert into Statement: Exercise-8 with Solution

8. Write a MySQL query to insert one row in jobs table to ensure that no duplicate value will be entered in the job_id column.

Create the table jobs.

CREATE TABLE IF NOT EXISTS jobs ( 
JOB_ID integer NOT NULL UNIQUE PRIMARY KEY, 
JOB_TITLE varchar(35) NOT NULL, 
MIN_SALARY decimal(6,0)
);

INSERT INTO jobs VALUES(1001,'OFFICER',8000);

mysql> SELECT * FROM jobs;
+--------+-----------+------------+
| JOB_ID | JOB_TITLE | MIN_SALARY |
+--------+-----------+------------+
|   1001 | OFFICER   |       8000 |
+--------+-----------+------------+

Sample Solution:

-- Inserting a new record into the 'jobs' table
INSERT INTO jobs VALUES(1001, 'OFFICER', 8000);

Let execute the above code in MySQL command prompt.

mysql> INSERT INTO jobs VALUES(1001,'OFFICER',8000);
ERROR 1062 (23000): Duplicate entry '1001' for key 'PRIMARY'

Explanation:

Here's a breakdown of the above MySQL query:

  • INSERT INTO jobs: Specifies the insertion operation into the 'jobs' table.
  • VALUES(1001, 'OFFICER', 8000): Specifies the values to be inserted into the columns of the 'jobs' table. In this case:
    • 1001 is inserted into the 'JOB_ID' column.
    • 'OFFICER' is inserted into the 'JOB_TITLE' column.
    • 8000 is inserted into the 'MIN_SALARY' column.

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

Previous:Write a MySQL query to insert one row in jobs table to ensure that no duplicate value will be entered in the job_id column.
Next:Write a MySQL query to insert a record into the table countries to ensure that, a country_id and region_id combination will be entered once in the table.

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-8.php