Day 5 Tables

Kedeisha November 23, 2023

Employees Table

Table Structure:
- employeeID (INT): Unique identifier for each employee.
- name (VARCHAR): Employee’s name.
- position (VARCHAR): Job position of the employee.
- hireDate (DATE): Date when the employee was hired.
- departmentID (INT): Identifier for the department the employee works in.

CREATE TABLE employees (
  employeeID INT PRIMARY KEY,
  name VARCHAR(100),
  position VARCHAR(100),
  hireDate DATE,
  departmentID INT
)
INSERT INTO employees (employeeID, name, position, hireDate, departmentID) VALUES
(1, 'Alice Johnson', 'Software Engineer', '2019-06-15', 1),
(2, 'Bob Smith', 'Project Manager', '2018-07-22', 2),
(3, 'Charlie Davis', 'Analyst', '2020-03-30', 1),
(4, 'Diana Ross', 'HR Manager', '2017-11-01', 3);

Departments Table

Table Structure:
- departmentID (INT): Unique identifier for each department.
- departmentName (VARCHAR): Name of the department.

CREATE TABLE departments (
  departmentID INT PRIMARY KEY,
  departmentName VARCHAR(100)
)
INSERT INTO departments (departmentID, departmentName) VALUES
(1, 'IT'),
(2, 'Project Management'),
(3, 'Human Resources');

Projects Table

Table Structure:
- projectID (INT): Unique identifier for each project.
- projectName (VARCHAR): Name of the project.
- startDate (DATE): Start date of the project.
- endDate (DATE): End date of the project.

CREATE TABLE projects (
  projectID INT PRIMARY KEY,
  projectName VARCHAR(100),
  startDate DATE,
  endDate DATE
)
INSERT INTO projects (projectID, projectName, startDate, endDate) VALUES
(1, 'Website Redesign', '2021-01-01', '2021-06-30'),
(2, 'Product Launch', '2021-03-01', '2021-09-30'),
(3, 'HR System Overhaul', '2020-12-15', '2021-07-15');

Employee_Projects Table

Table Structure:
- employeeID (INT): Identifier for the employee.
- projectID (INT): Identifier for the project.

CREATE TABLE employee_projects (
  employeeID INT,
  projectID INT,
  FOREIGN KEY (employeeID) REFERENCES employees(employeeID),
  FOREIGN KEY (projectID) REFERENCES projects(projectID)
)
INSERT INTO employee_projects (employeeID, projectID) VALUES
(1, 1),
(1, 2),
(2, 2),
(3, 1),
(4, 3);