w3resource

Create a MySQL table named 'job_history' ensuring that the 'employee_id' column has no duplicate values during insertion and 'job_id' contains only existing values from the 'jobs' table

MySQL Create Tables: Exercise-14 with Solution

14. Write a MySQL query to create a table job_history including columns employee_id, start_date, end_date, job_id and department_id and make sure that, the employee_id column does not contain any duplicate value at the time of insertion and the foreign key column job_id contain only those values which are exists in the jobs table.

Here is the structure of the table jobs;

+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| JOB_ID     | varchar(10)  | NO   | PRI |         |       |
| JOB_TITLE  | varchar(35)  | NO   |     | NULL    |       |
| MIN_SALARY | decimal(6,0) | YES  |     | NULL    |       |
| MAX_SALARY | decimal(6,0) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+

Sample Solution:

-- Creating a table named 'job_history' to store historical job information for employees

CREATE TABLE job_history(
    -- Column to store employee IDs with decimal precision of 6, 0, marked as NOT NULL and serving as the PRIMARY KEY
    EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY,

    -- Column to store the start date of the job, marked as NOT NULL
    START_DATE date NOT NULL,

    -- Column to store the end date of the job, marked as NOT NULL
    END_DATE date NOT NULL,

    -- Column to store job IDs, marked as NOT NULL
    JOB_ID varchar(10) NOT NULL,

    -- Column to store department IDs with decimal precision of 4, 0, marked as DEFAULT NULL
    DEPARTMENT_ID decimal(4,0) DEFAULT NULL,

    -- Creating a FOREIGN KEY constraint on the JOB_ID column, referencing the jobs table
    FOREIGN KEY (job_id) REFERENCES jobs(job_id)
);

Let execute the above code in MySQL command prompt

Here is the structure of the table:

mysql> DESC job_history;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| EMPLOYEE_ID   | decimal(6,0) | NO   | PRI | NULL    |       |
| START_DATE    | date         | NO   |     | NULL    |       |
| END_DATE      | date         | NO   |     | NULL    |       |
| JOB_ID        | varchar(10)  | NO   | MUL | NULL    |       |
| DEPARTMENT_ID | decimal(4,0) | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

Explanation:

The above MySQL code does the following:

  • Create a table named "job_history" to store historical job information for employees.
  • Define columns for:
    • EMPLOYEE_ID: Employee IDs with decimal precision of 6, 0, marked as NOT NULL and serving as the PRIMARY KEY.
    • START_DATE: Start date of the job, marked as NOT NULL.
    • END_DATE: End date of the job, marked as NOT NULL.
    • JOB_ID: Job IDs, marked as NOT NULL.
    • DEPARTMENT_ID: Department IDs with decimal precision of 4, 0, marked as DEFAULT NULL.
  • Create a FOREIGN KEY constraint on the JOB_ID column, referencing the jobs table.

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

Previous: Write a SQL statement to create a table countries including columns country_id, country_name and region_id and make sure that the combination of columns country_id and region_id will be unique.
Next: Write a SQL statement to create a table employees including columns employee_id, first_name, last_name, email, phone_number hire_date, job_id, salary, commission, manager_id and department_id and make sure that, the employee_id column does not contain any duplicate value at the time of insertion and the foreign key columns combined by department_id and manager_id columns contain only those unique combination values, which combinations are exists in the departments table.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.