w3resource

Create a MySQL table named jobs with default values for job_title as blank, min_salary as 8000, and max_salary as NULL if no values are provided at runtime

MySQL Create Tables: Exercise-10 with Solution

10. Write a MySQL query to create a table named jobs including columns job_id, job_title, min_salary and max_salary, and make sure that, the default value for job_title is blank and min_salary is 8000 and max_salary is NULL will be entered automatically at the time of insertion if no value assigned for the specified columns.

Sample Solution:

-- Creating a table named 'jobs' if it doesn't already exist to store information about job positions

CREATE TABLE IF NOT EXISTS jobs(
    -- Column to store job IDs, marked as NOT NULL (required) and with a UNIQUE constraint
    JOB_ID varchar(10) NOT NULL UNIQUE,

    -- Column to store job titles, marked as NOT NULL (required) with a default value of an empty string
    JOB_TITLE varchar(35) NOT NULL DEFAULT ' ',

    -- Column to store minimum salary with decimal precision of 6, 0 and a default value of 8000
    MIN_SALARY decimal(6,0) DEFAULT 8000,

    -- Column to store maximum salary with decimal precision of 6, 0 and a default value of NULL
    MAX_SALARY decimal(6,0) DEFAULT NULL
);

Let execute the above code in MySQL command prompt

Here is the structure of the table:

mysql> DESC jobs;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| JOB_ID     | varchar(10)  | NO   | PRI | NULL    |       |
| JOB_TITLE  | varchar(35)  | NO   |     |         |       |
| MIN_SALARY | decimal(6,0) | YES  |     | 8000    |       |
| MAX_SALARY | decimal(6,0) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

Explanation:

The said MySQL code creates a table named "jobs" if it doesn't already exist. The table has four columns:

  • JOB_ID: Stores job IDs, marked as NOT NULL (required) and with a UNIQUE constraint to ensure each job has a unique identifier.
  • JOB_TITLE: Stores job titles, marked as NOT NULL (required) with a default value of an empty string.
  • MIN_SALARY: Stores minimum salary values with decimal precision of 6, 0, and a default value of 8000.
  • MAX_SALARY: Stores maximum salary values with decimal precision of 6, 0, and a default value of NULL.

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 duplicate data against column country_id will be allowed at the time of insertion.
Next: Write a SQL statement to create a table named countries including columns country_id, country_name and region_id and make sure that the country_id column will be a key field which will not contain any duplicate data at the time of insertion.

What is the difficulty level of this exercise?



Follow us on Facebook and Twitter for latest update.