Create a MySQL table named job_history and enforce a constraint to ensure that values in the end_date column are entered in a specific format at runtime

MySQL Create Tables: Exercise-8 with Solution

8. Write a MySQL query to create a table named job_histry including columns employee_id, start_date, end_date, job_id and department_id and make sure that the value against column end_date will be entered at the time of insertion to the format like '--/--/----'.

Sample Solution:

-- Creating a table named 'job_history' if it doesn't already exist to store historical job information for employees

CREATE TABLE IF NOT EXISTS job_history(
    -- Column to store employee IDs with decimal precision of 6, 0, marked as NOT NULL (required)
    EMPLOYEE_ID decimal(6,0) NOT NULL,

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

    -- Column to store the end date of the job, marked as NOT NULL (required)
    END_DATE date NOT NULL
        -- CHECK constraint to ensure the end date format is '--/--/----'
        CHECK (END_DATE LIKE '--/--/----'),

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

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

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   |     | NULL    |       |
| START_DATE    | date         | NO   |     | NULL    |       |
| END_DATE      | date         | NO   |     | NULL    |       |
| JOB_ID        | varchar(10)  | NO   |     | NULL    |       |
| DEPARTMENT_ID | decimal(4,0) | NO   |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.04 sec)

Explanation:

The above MySQL code creates a table named "job_history" if it doesn't already exist. The table has five columns:

• EMPLOYEE_ID: Stores employee IDs with decimal precision of 6, 0 and is marked as NOT NULL (required).
• START_DATE: Stores the start date of a job and is marked as NOT NULL (required).
• END_DATE: Stores the end date of a job and is marked as NOT NULL (required). Additionally, there is a "CHECK" constraint to ensure the end date follows the format '--/--/----'.
• JOB_ID: Stores job IDs and is marked as NOT NULL (required).
• DEPARTMENT_ID: Stores department IDs with decimal precision of 4, 0 and is marked as NOT NULL (required).

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

Previous: Write a SQL statement to create a table named countries including columns country_id, country_name and region_id and make sure that no countries except Italy, India and China will be entered in the table.
Next: Write a SQL statement to create a table named countries including columns country_id,country_name and region_id and make sure that no duplicate data against column country_id will be allowed at the time of insertion.