-- Creating Table Employee Details
CREATE TABLE EDETAILS(
EMP_ID INT,
ENAME VARCHAR(50) NOT NULL,
DEPT VARCHAR(100) NOT NULL,
SAL DECIMAL(7,2) NOT NULL,
CONSTRAINT PK1 PRIMARY KEY (EMP_ID)
);

-- Inserting values in Employee details table
INSERT INTO EDETAILS(EMP_ID,ENAME,DEPT,SAL) 
VALUES
(7120,'HARISH','DATA ANALYSIS',5200.00),
(7133,'UJWAL','DATA ANALYSIS',7340.30),
(7246,'SARTHAK','SOFTWARE DEVELOPMENT',3200.70),
(7234,'SAHIL','SOFTWARE DEVELOPMENT',7659.79),
(7311,'VINAYAK','SOFTWARE TESTING',4250.00),
(7321,'SAKIB','SOFTWARE TESTING',6990.50);

-- Creating table Projects
CREATE TABLE PROJECT(
PROJECT_ID INT,
PNAME VARCHAR(50) NOT NULL,
DEPT INT NOT NULL,
CONSTRAINT PK2 PRIMARY KEY (PROJECT_ID)
);

-- Inserting values in Project table
INSERT INTO PROJECT(PROJECT_ID, PNAME, DEPT)
VALUES
(1110, 'DATA ANALYSIS', 'DATA ANALYSIS'),
(1120, 'DATA MIGRATION', 'DATA ANALYSIS'),
(1130, 'DATA VISUALIZATION','DATA ANALYSIS'),
(1140, 'DATA CLEANSING', 'DATA ANALYSIS'),
(1150, 'DATA MANIPULATION', 'DATA ANALYSIS'),
(2210, 'WEBSITE DESIGN', 'SOFTWARE DEVELOPMENT'),
(2220, 'UI/UX DESIGN', 'SOFTWARE DEVELOPMENT'),
(2230,'WEBSITE REDESIGN', 'SOFTWARE DEVELOPMENT'),
(2240, 'APPLICATION DEVELOPER', 'SOFTWARE DEVELOPMENT'),
(3310, 'APP TESTING', 'SOFTWARE TESTING'),
(3320,'WEBSITE TESTING','SOFTWARE TESTING'),
(3330, 'QUALITY ASSURANCE', 'SOFTWARE TESTING'),
(3340, 'TEST ANALYST', 'SOFTWARE TESTING');

-- Creating table Assignments
CREATE TABLE ASSIGNMENT(
ASSIGNMENT_ID INT,
EMP_ID INT NOT NULL,
PROJECT_ID INT NOT NULL,
HOURS_WORKED INT,
CONSTRAINT PK3 PRIMARY KEY (ASSIGNMENT_ID),
CONSTRAINT FK3 FOREIGN KEY (EMP_ID) REFERENCES EDETAILS (EMP_ID),
CONSTRAINT FK4 FOREIGN KEY (PROJECT_ID) REFERENCES PROJECT (PROJECT_ID)
);

-- Inserting values in Assignments table
INSERT INTO ASSIGNMENT(ASSIGNMENT_ID, EMP_ID, PROJECT_ID, HOURS_WORKED)
VALUES
(101, 7120, 1120, 72),
(102, 7246, 2210, 36),
(103, 7133, 1110, 32),
(104, 7311, 3310, 41),
(105, 7234, 2230, 53),
(106, 7321, 3310, 65),
(107, 7133, 1120, 25),
(108, 7133, 1130, 25),
(109, 7120, 1120, 30),
(110, 7246, 2220, 39),
(111, 7234, 3320, 21),
(112, 7246, 3330, 17),
(113, 7321, 2230, 23),
(114, 7311, 2210, 29),
(115, 7120, 2240, 10),
(116, 7246, 2230, 16),
(117, 7133, 1140, 33),
(118, 7120, 2220, 11);


-- Testing each value from every table
SELECT * FROM EMP;
SELECT * FROM PROJECTS;
SELECT * FROM ASSIGNMENTS;

-- PROBLEM 1 : Query to find the top-earning employee in each department
SELECT E.DEPT, E.ENAME, E.SAL
FROM EDETAILS E
JOIN (SELECT DEPT, MAX(SAL) AS MAX_SAL
	FROM EDETAILS
	GROUP BY DEPT) AS MAX_SALARIES
ON E.DEPT=MAX_SALARIES.DEPT
AND E.SAL=MAX_SALARIES.MAX_SAL;

-- In this problem first main query executes and reads department, name and salary columns
-- Then the subquery will be executed by going in the EDETAILS table, grouping the rows by dept column, and then extracting the requred columns
-- The inner join will be performed between main query and result of subquery
-- The matched records will be selected and final output table will be displayed

-- Problem 2 : Query to  identify employees who have worked on more than 3 projects
SELECT E.EMP_ID, E.ENAME, COUNT(A.PROJECT_ID) AS PCOUNT
FROM EDETAILS E
JOIN ASSIGNMENT A ON E.EMP_ID = A.EMP_ID
GROUP BY E.EMP_ID, E.ENAME
HAVING COUNT(A.PROJECT_ID)>3;

-- The execution process starts from searching for required data
-- Then the inner join operation is performed between EDETAILS and ASSIGNMENT table
-- Group by clause will group the result by Employee ID and Employee name
-- Then the condition executes where it'll make sure that count of projects is more than 3

-- Problem 3: Query to calculate the total hours worked on all projects within each department
SELECT E.DEPT, SUM(A.HOURS_WORKED)
FROM EDETAILS E
JOIN ASSIGNMENT A ON E.EMP_ID = A.EMP_ID
GROUP BY E.DEPT;

-- First the requred data is collected from EDETAILS table
-- The inner join is performed between EDETAILS and ASSIGNMENT table
-- Group the result by department

-- Problem 4: Query to find employees who are earning below the average salary for their department but have worked over 100 hours on projects.
SELECT E.EMP_ID, E.ENAME, E.DEPT, E.SAL, SUM(A.HOURS_WORKED) AS total_hours_worked
FROM EDETAILS E
JOIN ASSIGNMENT A ON E.EMP_ID = A.EMP_ID
JOIN (SELECT DEPT, AVG(SAL) AS AVG_SAL
    FROM EDETAILS
    GROUP BY DEP) AS DEPT_AVG_SAL 
ON E.DEPT = DEPT_AVG_SAL.DEPT
WHERE E.SAL < DEPT_AVG_SAL.AVG_SAL
GROUP BY E.EMP_ID, E.ENAME, E.DEPT, E.SAL
HAVING SUM(A.HOURS_WORKED) > 100;

-- The subquery is executed first by selecting the required result and grouping them according to department
-- The employee details table is joined with the assignment table and with result of subquery by using inner join
-- We apply condition using where clause which filters out the specific condition
-- The filtered condition is then grouped by employee id, name, department and salary
-- Having clause will make sure that sum of worked hours are greater then 100
-- The select clause will display the required output

-- Problem 5: Query to identify projects that have employees assigned from multiple departments
SELECT P.PROJECT_ID, P.PNAME
FROM PROJECT P
JOIN ASSIGNMENT A ON P.PROJECT_ID = A.PROJECT_ID
JOIN EDETAILS E ON A.EMP_ID = E.EMP_ID
GROUP BY P.PROJECT_ID, P.PNAME
HAVING COUNT(DISTINCT(E.DEPT)) > 1;

-- First from clause will get into project table
-- The assignment table is joined with project table based on project id then joins the result with employee table based on employee id
-- Group by clause will group the columns project id and project name
-- Having clause will check if count of distinct department is greater than 1
-- The select clause will display the output

-- Additional challenge: Query to find the average hours worked per project for each employee who has completed assignments on at least 2 projects
SELECT E.EMP_ID, E.ENAME, AVG(A.HOURS_WORKED)
FROM EDETAILS E
JOIN ASSIGNMENT A ON E.EMP_ID = A.EMP_ID
GROUP BY E.EMP_ID, E.ENAME
HAVING COUNT(DISTINCT (A.PROJECT_ID)) >= 2;

-- From clause will get us into employee details table
-- Then inner join is performed between assignment table and employee details table based on employee id
-- The data will be grouped according to employee id and employee name
-- Having clause will check whether count of distinct of project id from assignment table is greater than or equal to 2
-- Select clause will display the output