Lesson 1 of 0
In Progress
Day 4 Tables
Employees Table
- Table Structure:
employeeID
(INT): A unique identifier for each employee.name
(VARCHAR): The name of the employee.departmentID
(INT): The ID of the department the employee belongs to.email
(VARCHAR): The email address of the employee.hireDate
(DATE): The date when the employee was hired.
CREATE TABLE employees (
employeeID INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
departmentID INT,
email VARCHAR(100),
hireDate DATE
)
INSERT INTO employees (name, departmentID, email, hireDate) VALUES
('Alice Johnson', 1, 'alice.johnson@example.com', '2019-06-15'),
('Bob Smith', 2, 'bob.smith@example.com', '2018-07-22'),
('Charlie Davis', 1, 'charlie.davis@example.com', '2020-03-30');
Departments Table
- Table Structure:
departmentID
(INT): A unique identifier for each department.departmentName
(VARCHAR): The name of the department.
CREATE TABLE departments (
departmentID INT AUTO_INCREMENT PRIMARY KEY,
departmentName VARCHAR(100)
)
INSERT INTO departments (departmentName) VALUES
('IT'),
('HR'),
('Marketing');
Projects Table
- Table Structure:
projectID
(INT): A unique identifier for each project.projectName
(VARCHAR): The name of the project.startDate
(DATE): The start date of the project.endDate
(DATE): The end date of the project.
CREATE TABLE projects (
projectID INT AUTO_INCREMENT PRIMARY KEY,
projectName VARCHAR(100),
startDate DATE,
endDate DATE
)
INSERT INTO projects (projectName, startDate, endDate) VALUES
('Website Redesign', '2021-01-01', '2021-06-30'),
('Product Launch', '2021-03-01', '2021-09-30');
Employee_Projects Table (Many-to-Many Relationship)
- Table Structure:
employeeID
(INT): The ID of the employee (foreign key).projectID
(INT): The ID of the project (foreign key).
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),
(2, 2),
(3, 1);