/* 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 08: For each employee, return their name and all of the projects that they work on.   (Can you do this with and without JOIN?) */

/* Without JOIN */
SELECT EmployeeName, ProjectName
FROM Works_T, Employee_T, Project_T
WHERE Works_T.EmployeeNum = Employee_T.EmployeeNum
AND Works_T.ProjectNum = Project_T.ProjectNum;

/* Using JOIN */
SELECT EmployeeName, ProjectName
FROM Works_T, Employee_T JOIN Project_T ON
Works_T.EmployeeNum = Employee_T.EmployeeNum
AND Works_T.ProjectNum = Project_T.ProjectNum;


/* Exercise 09:	Return each employee's name and the department that they work in.   (Can you do this with and without JOIN?) */
/* Without JOIN */
SELECT EmployeeName, DepartmentName
FROM Employee_T, Department_T
WHERE Employee_T.DepartmentNum = Department_T.DepartmentNum;

/* Using JOIN */


/* Exercise 10: Return each employee's name and the department that they work in (make sure to display all employees). */
SELECT EmployeeName, DepartmentName
FROM Employee_T LEFT OUTER JOIN Department_T ON
Employee_T.DepartmentNum = Department_T.DepartmentNum;


/* Exercise 11: For each department, return the name of the department, the number of employees in that department, and the range of their salaries. Use GROUP BY.*/
SELECT DepartmentName, COUNT(*), MAX(EmployeeSalary) - MIN(EmployeeSalary) AS Range
FROM Department_T D, Employee_T E
WHERE D.DepartmentNum = E.DepartmentNum
GROUP BY DepartmentName;

SELECT DepartmentName, COUNT(*), MAX(EmployeeSalary), MIN(EmployeeSalary)
FROM Department_T D INNER JOIN Employee_T E ON
D.DepartmentNum = E.DepartmentNum
GROUP BY DepartmentName;


/* Exercise 12: For each department with at least 2 employees, return the name of the department, the number of employees in that department, and the range of their salaries.  (Use HAVING) */
SELECT DepartmentName, COUNT(*), MAX(EmployeeSalary), MIN(EmployeeSalary)
FROM Department_T D INNER JOIN Employee_T E ON
D.DepartmentNum = E.DepartmentNum
GROUP BY DepartmentName
HAVING COUNT(*) >= 2;


/* Exercise 13: Return the employee name and their salary for each employee with a salary that is above the average salary (in the company). Can you also display the average salary? */
SELECT EmployeeName, EmployeeSalary, AverageSalary
FROM Employee_T, (SELECT AVG(EmployeeSalary) as AverageSalary FROM Employee_T)
WHERE EmployeeSalary > AverageSalary;

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


/* Exercise 14: Return the employee name and their salary for each employee with a salary that is above the average salary in their department.  (If you're having troubles, first, write a query to display the average salary for each department.  Also, as a hint, think correlated inner query :)*/
/* Average per department */
SELECT DepartmentName, AVG(EmployeeSalary) AS AverageDepartmentSalary
FROM Department_T D, Employee_T E
WHERE D.DepartmentNum = E.DepartmentNum
GROUP BY DepartmentName;

/* Returns Sarah, but she should only be returned for >= (and not just >) */
SELECT EmployeeName, DepartmentName, EmployeeSalary
FROM Employee_T, Department_T
WHERE EmployeeSalary > (SELECT AVG(EmployeeSalary) AS AverageDepartmentSalary
FROM Department_T D, Employee_T E
WHERE D.DepartmentNum = E.DepartmentNum
GROUP BY DepartmentName)
AND Employee_T.DepartmentNum = Department_T.DepartmentNum;

SELECT EmployeeName, EmployeeSalary
FROM Employee_T E
WHERE EmployeeSalary > (SELECT AVG(EmployeeSalary) AS AverageDepartmentSalary
FROM Employee_T I
WHERE E.DepartmentNum = I.DepartmentNum);

/* Exercise 15: Return the employee name for the employee that works in on project #1 and #2. */
SELECT EmployeeName
FROM Employee_T E, Works_T W
WHERE E.EmployeeNum = W.EmployeeNum
AND W.ProjectNum = 1
AND E.EmployeeNum IN (SELECT EmployeeNum FROM Works_T WHERE ProjectNum = 2);


SELECT EmployeeName
FROM Employee_T E NATURAL JOIN Works_T W
WHERE W.ProjectNum = 1
INTERSECT
SELECT EmployeeName
FROM Employee_T E NATURAL JOIN Works_T W
WHERE W.ProjectNum =2;


/* Exercise 16: Return the name of each employee, their salary and the difference between their salary and the average salary for the company. */
SELECT EmployeeName, EmployeeSalary, AverageSalary, EmployeeSalary - AverageSalary AS SALARY_DIFFERENCE
FROM Employee_T, (SELECT AVG(EmployeeSalary) as AverageSalary FROM Employee_T)