/* Remove any tables that previous existed
DROP TABLE Department_T;
DROP TABLE Project_T;
DROP TABLE Employee_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
*/



/* Department_T
DepartmentNum	DepartmentName
10	HR
20	Sales
30 	Management
*/


/* Project_T
ProjectNum	ProjectName
1	Community Connector
2	Neighborhood Network
3	Unified Society
*/


/* 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
*/


/*
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 */

/* Using JOIN */


/* Exercise 09:	Return each employee's name and the department that they work in.   (Can you do this with and without JOIN?) */

/* Without JOIN */

/* Using JOIN */


/* Exercise 10: Return each employee's name and the department that they work in (make sure to display all employees). */


/* 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.*/


/* 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) */


/* 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? */


/* 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 :)*/


/* Exercise 15: Return the employee name for the employee that works in on project #1 and #2. */


/* Exercise 16: Return the name of each employee, their salary and the difference between their salary and the average salary for the company. */