/* Remove any tables that previous existed */
DROP TABLE Department_T;
DROP TABLE Employee_T;
DROP Table Project_T;
DROP TABLE Works_T;

/* 
 * Create the following tables and insert the values 
 */
/* Employee_T 
EmployeeNum	EmployeeName	EmployeeSalary	DepartmentNum 
1	Smith	100000	10 
2	John	150000	 
3	Peter	60000	10 
4	Sarah	175000	20 
*/
CREATE TABLE Employee_T
(EmployeeNum	INT	NOT NULL,
EmployeeName VARCHAR(20) NOT NULL,
EmployeeSalary INT NOT NULL,
DepartmentNum INT,
CONSTRAINT Employee_PK PRIMARY KEY (EmployeeNum),
CONSTRAINT Employee_FK FOREIGN KEY (DepartmentNum) References Department_T(DepartmentNum));

INSERT INTO Employee_T (EmployeeNum, EmployeeName, EmployeeSalary, DepartmentNum)
VALUES (1, 'Smith', 100000, 10);
INSERT INTO Employee_T (EmployeeNum, EmployeeName, EmployeeSalary, DepartmentNum)
VALUES (2, 'John', 150000, NULL);
INSERT INTO Employee_T (EmployeeNum, EmployeeName, EmployeeSalary, DepartmentNum)
VALUES (3, 'Peter', 60000, 10);
INSERT INTO Employee_T (EmployeeNum, EmployeeName, EmployeeSalary, DepartmentNum)
VALUES (4, 'Sarah', 175000, 20);

SELECT * FROM Employee_T;


/* Department_T 
DepartmentNum	DepartmentName 
10	HR 
20	Sales 
30 	Management 
*/
CREATE TABLE Department_T
(DepartmentNum INT NOT NULL,
DepartmentName VARCHAR (20) NOT NULL,
CONSTRAINT Department_PK PRIMARY KEY (DepartmentNum)
);

INSERT INTO Department_T (DepartmentNum, DepartmentName)
VALUES (10, 'HR');
INSERT INTO Department_T (DepartmentNum, DepartmentName)
VALUES (20, 'Sales');
INSERT INTO Department_T (DepartmentNum, DepartmentName)
VALUES (30, 'Management');

SELECT * FROM Department_T;


/* Project_T 
ProjectNum	ProjectName 
1	Community Connector 
2	Neighborhood Network 
3	Unified Society 
*/
CREATE TABLE Project_T
(ProjectNum	INT	NOT NULL,
ProjectName	VARCHAR(35)	NOT NULL,
CONSTRAINT Project_PK PRIMARY KEY (ProjectNum));

INSERT INTO Project_T (ProjectNum, ProjectName)
VALUES (1, 'Community Connector');
INSERT INTO Project_T (ProjectNum, ProjectName)
VALUES (2, 'Neighborhood Network');
INSERT INTO Project_T (ProjectNum, ProjectName)
VALUES (3, 'Unified Society');

SELECT * FROM Project_T;


/* Works_T 
EmployeeNum	ProjectNum	FromDate	ToDate 
1	1	01/15/2023	01/15/2024 
1	2	01/16/2024	01/15/2025 
2	3	04/13/2024	12/31/2024 
4	2	01/16/2024	01/15/2025 
*/
CREATE TABLE Works_T
(EmployeeNum int NOT NULL,
ProjectNum int NOT NULL,
FromDate date ,
ToDate date ,
CONSTRAINT Works_PK PRIMARY KEY (EmployeeNum, ProjectNum),
CONSTRAINT Works_FK1 FOREIGN KEY (EmployeeNum) References Employee_T(EmployeeNum),
CONSTRAINT Works_FK2 FOREIGN KEY (ProjectNum) References Project_T(ProjectNum));

INSERT INTO Works_T (EmployeeNum, ProjectNum, FromDate, ToDate)
VALUES (1, 1, '01/15/2023', '01/15/2024');
INSERT INTO Works_T (EmployeeNum, ProjectNum, FromDate, ToDate)
VALUES (1, 2, '01/16/2024', '01/15/2025');
INSERT INTO Works_T (EmployeeNum, ProjectNum, FromDate, ToDate)
VALUES (2, 3, '04/13/2024', '12/31/2024');
INSERT INTO Works_T (EmployeeNum, ProjectNum, FromDate, ToDate)
VALUES (4, 2, '01/16/2024', '01/15/2025');

SELECT * from Works_T;




/* 
Complete the following exercises using the tables created above 
*/

/* Exercise 01: Return All Employee Data */
SELECT *
FROM Employee_T;

/* Exercise 02: Return Employee data for employees with DepartmentNum = Null values */
SELECT *
FROM Employee_T
WHERE DepartmentNum is Null;

/* Exercise 03: Calculate what a 10% bonus would be for each employee */
SELECT EmployeeName, EmployeeSalary * 0.1 AS Bonus
FROM Employee_T;

/* Exercise 04: Return employees whose names start with ‘S’ */
SELECT EmployeeName
FROM Employee_T
WHERE EmployeeName LIKE 'S%';

/* Exercise 05:	Return the number of employees in the company and the range of their salaries */
SELECT COUNT(*) AS Number_Employees, MIN(EmployeeSalary), MAX(EmployeeSalary)
From Employee_T;

SELECT COUNT(*) AS Number_Employees,  MAX(EmployeeSalary) - MIN(EmployeeSalary) AS Range
From Employee_T;


/* Exercise 06: Return the names of employees whose salaries are greater than the average salaries for the company */
SELECT EmployeeName, EmployeeSalary, SalaryAvg
FROM Employee_T, (SELECT AVG(EmployeeSalary) AS SalaryAvg FROM Employee_T)
WHERE EmployeeSalary > SalaryAvg;

SELECT EmployeeName, EmployeeSalary, SalaryAvg
FROM Employee_T
WHERE EmployeeSalary > (SELECT AVG(EmployeeSalary) AS SalaryAvg FROM Employee_T);

/* Exercise 07: Return 1) name of employee, 2) his/her salary and 3) the difference between his/her salary and the average salary */
SELECT EmployeeName, EmployeeSalary - SalaryAvg
FROM Employee_T, (SELECT AVG(EmployeeSalary) AS SalaryAvg FROM Employee_T);