MySQL Create Table Exercises with Solution

1. Write a SQL statement to create a simple table countries including columns country_id,country_name and region_id.

Solution:

mysql> CREATE TABLE countries( 
COUNTRY_ID varchar(2),
COUNTRY_NAME varchar(40),
REGION_ID decimal(10,0)
);

Let execute the above code in MySQL command prompt

Here is the structure of the table:

mysql> DESC countries;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| COUNTRY_ID   | varchar(2)    | YES  |     | NULL    |       |
| COUNTRY_NAME | varchar(40)   | YES  |     | NULL    |       |
| REGION_ID    | decimal(10,0) | YES  |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

main.sql

CREATE TABLE countries( 
COUNTRY_ID varchar(2),
COUNTRY_NAME varchar(40),
REGION_ID decimal(10,0)
);

DESC countries;

Output:

Field	Type	Null	Key	Default	Extra
COUNTRY_ID	varchar(2)	YES		NULL	
COUNTRY_NAME	varchar(40)	YES		NULL	
REGION_ID	decimal(10,0)	YES		NULL	

---

2. Write a SQL statement to create a simple table countries including columns country_id,country_name and region_id which is already exists.

Solution:

mysql> CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2),
COUNTRY_NAME varchar(40),
REGION_ID decimal(10,0)
);

Let execute the above code in MySQL command prompt

Here is the structure of the table:

mysql> DESC countries;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| COUNTRY_ID   | varchar(2)    | YES  |     | NULL    |       |
| COUNTRY_NAME | varchar(40)   | YES  |     | NULL    |       |
| REGION_ID    | decimal(10,0) | YES  |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
3 rows in set (0.13 sec)

main.sql

CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2),
COUNTRY_NAME varchar(40),
REGION_ID decimal(10,0)
);

DESC countries;

Output:

COUNTRY_ID	varchar(2)	YES		NULL	
COUNTRY_NAME	varchar(40)	YES		NULL	
REGION_ID	decimal(10,0)	YES		NULL	

---

3. Write a SQL statement to create the structure of a table dup_countries similar to countries.

Solution:

CREATE TABLE IF NOT EXISTS dup_countries
LIKE countries;

Let execute the above code in MySQL command prompt

Here is the structure of the table:

mysql> DESC dup_countries;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| COUNTRY_ID   | varchar(2)    | YES  |     | NULL    |       |
| COUNTRY_NAME | varchar(40)   | YES  |     | NULL    |       |
| REGION_ID    | decimal(10,0) | YES  |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
3 rows in set (0.03 sec)

main.sql

CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2),
COUNTRY_NAME varchar(40),
REGION_ID decimal(10,0)
);

CREATE TABLE IF NOT EXISTS dup_countries
LIKE countries;

DESC dup_countries;

Output:

COUNTRY_ID	varchar(2)	YES		NULL	
COUNTRY_NAME	varchar(40)	YES		NULL	
REGION_ID	decimal(10,0)	YES		NULL	

---

4. Write a SQL statement to create a duplicate copy of countries table including structure and data by name dup_countries.

Solution:

CREATE TABLE IF NOT EXISTS dup_countries
AS SELECT * FROM  countries;

Let execute the above code in MySQL command prompt

Here is the structure of the table:

mysql> mysql> DESC dup_countries;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| COUNTRY_ID   | varchar(2)    | YES  |     | NULL    |       |
| COUNTRY_NAME | varchar(40)   | YES  |     | NULL    |       |
| REGION_ID    | decimal(10,0) | YES  |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
3 rows in set (0.11 sec)

main.sql

CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2),
COUNTRY_NAME varchar(40),
REGION_ID decimal(10,0)
);

CREATE TABLE IF NOT EXISTS dup_countries
AS SELECT * FROM  countries;

DESC dup_countries;

Output:

COUNTRY_ID	varchar(2)	YES		NULL	
COUNTRY_NAME	varchar(40)	YES		NULL	
REGION_ID	decimal(10,0)	YES		NULL

---

5. Write a SQL statement to create a table countries set a constraint NULL.

Solution:

CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2) NOT NULL,
COUNTRY_NAME varchar(40) NOT NULL,
REGION_ID decimal(10,0) NOT NULL
);

Let execute the above code in MySQL command prompt

Here is the structure of the table:

mysql> desc countries;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| COUNTRY_ID   | varchar(2)    | NO   |     | NULL    |       |
| COUNTRY_NAME | varchar(40)   | NO   |     | NULL    |       |
| REGION_ID    | decimal(10,0) | NO   |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> 

main.sql

CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2) NOT NULL,
COUNTRY_NAME varchar(40) NOT NULL,
REGION_ID decimal(10,0) NOT NULL
);

desc countries;

Output:

COUNTRY_ID	varchar(2)	NO		NULL	
COUNTRY_NAME	varchar(40)	NO		NULL	
REGION_ID	decimal(10,0)	NO		NULL

---

6. Write a SQL statement to create a table named jobs including columns job_id, job_title, min_salary, max_salary and check whether the max_salary amount exceeding the upper limit 25000.

Solution:

CREATE TABLE IF NOT EXISTS jobs ( 
JOB_ID varchar(10) NOT NULL , 
JOB_TITLE varchar(35) NOT NULL, 
MIN_SALARY decimal(6,0), 
MAX_SALARY decimal(6,0) 
CHECK(MAX_SALARY<=25000)
);

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   |     | NULL    |       |
| JOB_TITLE  | varchar(35)  | NO   |     | NULL    |       |
| MIN_SALARY | decimal(6,0) | YES  |     | NULL    |       |
| MAX_SALARY | decimal(6,0) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.16 sec)

main.sql

CREATE TABLE IF NOT EXISTS jobs ( 
JOB_ID varchar(10) NOT NULL , 
JOB_TITLE varchar(35) NOT NULL, 
MIN_SALARY decimal(6,0), 
MAX_SALARY decimal(6,0) 
CHECK(MAX_SALARY<=25000)
);

DESC jobs;

Output:

JOB_ID	varchar(10)	NO		NULL	
JOB_TITLE	varchar(35)	NO		NULL	
MIN_SALARY	decimal(6,0)	YES		NULL	
MAX_SALARY	decimal(6,0)	YES		NULL	

---

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

Solution:

CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2),
COUNTRY_NAME varchar(40)
CHECK(COUNTRY_NAME IN('Italy','India','China')) ,
REGION_ID decimal(10,0)
);

Let execute the above code in MySQL command prompt

Here is the structure of the table:

mysql> DESC countries;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| COUNTRY_ID   | varchar(2)    | YES  |     | NULL    |       |
| COUNTRY_NAME | varchar(40)   | YES  |     | NULL    |       |
| REGION_ID    | decimal(10,0) | YES  |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

main.sql

CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2),
COUNTRY_NAME varchar(40)
CHECK(COUNTRY_NAME IN('Italy','India','China')) ,
REGION_ID decimal(10,0)
);

DESC countries;

Output:

COUNTRY_ID	varchar(2)	YES		NULL	
COUNTRY_NAME	varchar(40)	YES		NULL	
REGION_ID	decimal(10,0)	YES		NULL		

---

8. Write a SQL statement 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 '--/--/----'.

Solution:

CREATE TABLE IF NOT EXISTS job_history ( 
EMPLOYEE_ID decimal(6,0) NOT NULL, 
START_DATE date NOT NULL, 
END_DATE date NOT NULL
CHECK (END_DATE LIKE '--/--/----'), 
JOB_ID varchar(10) NOT NULL, 
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)

main.sql

CREATE TABLE IF NOT EXISTS job_history ( 
EMPLOYEE_ID decimal(6,0) NOT NULL, 
START_DATE date NOT NULL, 
END_DATE date NOT NULL
CHECK (END_DATE LIKE '--/--/----'), 
JOB_ID varchar(10) NOT NULL, 
DEPARTMENT_ID decimal(4,0) NOT NULL 
);

DESC job_history;

Output:

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	

---

9. 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.

Solution:

CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2) NOT NULL,
COUNTRY_NAME varchar(40) NOT NULL,
REGION_ID decimal(10,0) NOT NULL,
UNIQUE(COUNTRY_ID)
);

Let execute the above code in MySQL command prompt

Here is the structure of the table:

mysql> DESC countries;
+--------------+---------------+------+-----+---------+-------+
| Field        | Type          | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+-------+
| COUNTRY_ID   | varchar(2)    | YES  |     | NULL    |       |
| COUNTRY_NAME | varchar(40)   | YES  |     | NULL    |       |
| REGION_ID    | decimal(10,0) | YES  |     | NULL    |       |
+--------------+---------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

main.sql

CREATE TABLE IF NOT EXISTS countries ( 
COUNTRY_ID varchar(2) NOT NULL,
COUNTRY_NAME varchar(40) NOT NULL,
REGION_ID decimal(10,0) NOT NULL,
UNIQUE(COUNTRY_ID)
);

DESC countries;

Output:

COUNTRY_ID	varchar(2)	NO	PRI	NULL	
COUNTRY_NAME	varchar(40)	NO		NULL	
REGION_ID	decimal(10,0)	NO		NULL

---

10. Write a SQL statement 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.

Solution:

CREATE TABLE IF NOT EXISTS jobs ( 
JOB_ID varchar(10) NOT NULL UNIQUE, 
JOB_TITLE varchar(35) NOT NULL DEFAULT ' ', 
MIN_SALARY decimal(6,0) DEFAULT 8000, 
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)

main.sql

CREATE TABLE IF NOT EXISTS jobs ( 
JOB_ID varchar(10) NOT NULL UNIQUE, 
JOB_TITLE varchar(35) NOT NULL DEFAULT ' ', 
MIN_SALARY decimal(6,0) DEFAULT 8000, 
MAX_SALARY decimal(6,0) DEFAULT NULL
);

DESC jobs;

Output:

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