Sql Commands
Sql command js
13Views
1Likes
0Shares
CREATE TABLE Department (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(100) NOT NULL,
Location VARCHAR(100) NOT NULL
);
INSERT INTO Department (DepartmentID, DepartmentName, Location) VALUES
(1, 'Human Resources', 'New York'),
(2, 'Engineering', 'San Francisco'),
(3, 'Marketing', 'Chicago'),
(4, 'Finance', 'Boston'),
(5, 'IT Support', 'Seattle');
INSERT INTO Department (DepartmentID, DepartmentName, Location) VALUES
(1, ' IT Support ', 'New York');
ALTER TABLE Department ADD CONSTRAINT uq_name UNIQUE ( DepartmentName );
ALTER TABLE Employee
ADD Email VARCHAR(100) UNIQUE;
ALTER TABLE Employee
ADD CONSTRAINT chk_salary_positive CHECK (Salary > 0);
ALTER TABLE Employee
ADD CONSTRAINT chk_jdate_past CHECK (JDate <= CURRENT_DATE);
Let’s now assume relationships:
Each employee belongs to a department.
Each project is assigned to a department and may have employees working on it.
ALTER TABLE Employee
ADD DepartmentID INT,
ADD CONSTRAINT fk_employee_department
FOREIGN KEY (DepartmentID)
REFERENCES Department(DepartmentID);
CREATE TABLE Project (
ProjectID INT PRIMARY KEY,
ProjectName VARCHAR(100) NOT NULL,
Budget DECIMAL(12, 2) CHECK (Budget > 0),
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID)
);
CREATE TABLE EmployeeProject (
EmployeeID INT,
ProjectID INT,
AssignedDate DATE,
PRIMARY KEY (EmployeeID, ProjectID),
FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID),
FOREIGN KEY (ProjectID) REFERENCES Project(ProjectID)
);
INSERT INTO Employee (EmployeeID, FirstName, LastName, DateOfBirth, Position, Salary, JDate, DepartmentID, Email) VALUES
(101, 'Alice', 'Johnson', '1990-04-12', 'HR Manager', 75000.00, '2015-06-01', 1, 'alice.johnson@company.com'),
(102, 'Bob', 'Smith', '1985-09-20', 'Software Engineer', 95000.00, '2018-03-15', 2, 'bob.smith@company.com'),
(103, 'Carol', 'Davis', '1992-11-05', 'Marketing Analyst', 68000.00, '2020-01-10', 3, 'carol.davis@company.com'),
(104, 'David', 'Lee', '1988-07-22', 'Accountant', 72000.00, '2016-08-25', 4, 'david.lee@company.com'),
(105, 'Emma', 'Clark', '1995-02-28', 'IT Technician', 60000.00, '2021-09-01', 5, 'emma.clark@company.com'),
(106, 'Frank', 'Martin', '1991-03-15', 'DevOps Engineer', 98000.00, '2019-05-18', 2, 'frank.martin@company.com'),
(107, 'Grace', 'Wong', '1993-06-30', 'HR Assistant', 55000.00, '2022-02-14', 1, 'grace.wong@company.com');
INSERT INTO Project (ProjectID, ProjectName, Budget, DepartmentID) VALUES
(201, 'Employee Onboarding System', 30000.00, 1),
(202, 'AI Development Platform', 150000.00, 2),
(203, 'Social Media Campaign', 40000.00, 3),
(204, 'Budget Forecast Tool', 50000.00, 4),
(205, 'Network Upgrade', 35000.00, 5),
(206, 'Cloud Migration', 120000.00, 2),
(207, 'Training Portal', 25000.00, 1);
INSERT INTO EmployeeProject (EmployeeID, ProjectID, AssignedDate) VALUES
(101, 201, '2022-05-01'),
(101, 207, '2023-02-15'),
(102, 202, '2021-07-10'),
(102, 206, '2022-11-05'),
(103, 203, '2020-03-12'),
(104, 204, '2018-10-22'),
(105, 205, '2021-09-15'),
(106, 202, '2021-07-12'),
(106, 206, '2023-01-25'),
(107, 201, '2022-06-10'),
(107, 207, '2023-03-01');
