MySQL Create Table Exercises: Create a table employees including some specific columns with a primary key and a foreign key with combination of two or more columns
MySQL Create Tables: Exercise-15 with Solution
15. 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.
Assume the structure of departments table below.
+-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | DEPARTMENT_ID | decimal(4,0) | NO | PRI | 0 | | | DEPARTMENT_NAME | varchar(30) | NO | | NULL | | | MANAGER_ID | decimal(6,0) | NO | PRI | 0 | | | LOCATION_ID | decimal(4,0) | YES | | NULL | | +-----------------+--------------+------+-----+---------+-------+
Sample Solution:
CREATE TABLE IF NOT EXISTS employees (
EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY,
FIRST_NAME varchar(20) DEFAULT NULL,
LAST_NAME varchar(25) NOT NULL,
EMAIL varchar(25) NOT NULL,
PHONE_NUMBER varchar(20) DEFAULT NULL,
HIRE_DATE date NOT NULL,
JOB_ID varchar(10) NOT NULL,
SALARY decimal(8,2) DEFAULT NULL,
COMMISSION_PCT decimal(2,2) DEFAULT NULL,
MANAGER_ID decimal(6,0) DEFAULT NULL,
DEPARTMENT_ID decimal(4,0) DEFAULT NULL,
FOREIGN KEY(DEPARTMENT_ID,MANAGER_ID)
REFERENCES departments(DEPARTMENT_ID,MANAGER_ID)
)ENGINE=InnoDB;
Let execute the above code in MySQL 5.6 command prompt
Here is the structure of the table:
mysql> DESC employees; +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | EMPLOYEE_ID | decimal(6,0) | NO | PRI | NULL | | | FIRST_NAME | varchar(20) | YES | | NULL | | | LAST_NAME | varchar(25) | NO | | NULL | | | EMAIL | varchar(25) | NO | | NULL | | | PHONE_NUMBER | varchar(20) | YES | | NULL | | | HIRE_DATE | date | NO | | NULL | | | JOB_ID | varchar(10) | NO | | NULL | | | SALARY | decimal(8,2) | YES | | NULL | | | COMMISSION_PCT | decimal(2,2) | YES | | NULL | | | MANAGER_ID | decimal(6,0) | YES | | NULL | | | DEPARTMENT_ID | decimal(4,0) | YES | MUL | NULL | | +----------------+--------------+------+-----+---------+-------+ 11 rows in set (0.03 sec)
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a SQL statement 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.
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 column department_id, reference by the column department_id of departments table, can contain only those values which are exists in the departments table and another foreign key column job_id, referenced by the column job_id of jobs table, can contain only those values which are exists in the jobs table. The InnoDB Engine have been used to create the tables.
What is the difficulty level of this exercise?
- New Content published on w3resource:
- HTML-CSS Practical: Exercises, Practice, Solution
- Java Regular Expression: Exercises, Practice, Solution
- Scala Programming Exercises, Practice, Solution
- Python Itertools exercises
- Python Numpy exercises
- Python GeoPy Package exercises
- Python Pandas exercises
- Python nltk exercises
- Python BeautifulSoup exercises
- Form Template
- Composer - PHP Package Manager
- PHPUnit - PHP Testing
- Laravel - PHP Framework
- Angular - JavaScript Framework
- Vue - JavaScript Framework
- Jest - JavaScript Testing Framework