POM

    Scenarios:
---1. Display the client names who do not have a fax number

SELECT CLIENT_NAME
FROM CLIENT_2
WHERE FAX IS NULL;

--2. Display the clients that have more than one project

SELECT CLIENT_ID,COUNT(PROJECT_ID)
FROM ASSIGNMENT_2
GROUP BY CLIENT_ID
HAVING COUNT(PROJECT_ID) >1;

--3. Display the project titles and their expected durations in years for all the projects that are still not closed

SELECT  TITLE
FROM PROJECT_2
WHERE TO_CHAR(EXPECTED_END_DATE,'YYYY') = TO_CHAR(SYSDATE,'YYYY');

--4. Display the no of women resources from India

SELECT COUNT(RES_ID)
FROM RESOURCE_2
WHERE GENDER = 'FEMALE'
AND COUNTRY ='INDIA';


--5. Display the resource name and no of projects under which they are working as on today

SELECT  R.RES_NAME,COUNT(A.PROJECT_ID)
FROM ASSIGNMENT_2 A,RESOURCE_2 R,PROJECT_2 P
WHERE R.RES_ID = A.RES_ID
AND P.PROJECT_ID = A.PROJECT_ID
AND A.ASSIGNED_DATE  BETWEEN P.START_DATE AND SYSDATE
GROUP BY   R.RES_NAME
HAVING COUNT(A.PROJECT_ID)>1;


6. Display resource names who are catering to more than one client currently

SELECT RES_NAME
FROM RESOURCE_2
WHERE RES_ID IN (
SELECT  RES_ID
FROM ASSIGNMENT_2
WHERE TO_CHAR(ASSIGNED_DATE,'MOM-YYYY') = TO_CHAR(SYSDATE,'MON-YYYY')
GROUP BY RES_ID
HAVING COUNT(CLIENT_ID) >1);

7. Display the resources whose age is more than the average age of all the managers

SELECT RES_NAME
FROM RESOURCE_2
WHERE (MONTHS_BETWEEN(SYSDATE,DOB)/12) >
(SELECT AVG(MONTHS_BETWEEN(SYSDATE,DOB)/12)
FROM RESOURCE_2
WHERE DEGINATION ='MANAGER');


--8. Display resources who have worked for foreign clients


SELECT R.RES_NAME
FROM CLIENT_2 C,ASSIGNMENT_2 A,RESOURCE_2 R
WHERE C.CLIENT_ID = A.CLIENT_ID
AND R.RES_ID = A.RES_ID
AND C.COUNTRY NOT IN ('INDIA');

9. Display the projects which has got delayed in deliver and also display its delay in no of days

SELECT TITLE,(ACTUAL_END_DATE -EXPECTED_END_DATE ) AS delay_in_no_of_days
FROM PROJECT_2;

10. Display the employee names who were assigned to a minimum of 5 assignments in the current year

SELECT R.RES_NAME,COUNT(A.CLIENT_ID)
FROM ASSIGNMENT_2 A,RESOURCE_2 R
WHERE A.RES_ID = R.RES_ID
AND TO_CHAR(ASSIGNED_DATE,'YYYY') = TO_CHAR(SYSDATE,'YYYY')
GROUP BY R.RES_NAME
HAVING COUNT(A.CLIENT_ID)>5;

--11. Display the projects that were not closed as expected for the client ‘Cathay Pacific’ in the current year.

 SELECT  P.TITLE
  FROM PROJECT_2 P,ASSIGNMENT_2 A,CLIENT_2 C
  WHERE P.PROJECT_ID = A.PROJECT_ID
  AND C.CLIENT_ID = A.CLIENT_ID
  AND P.EXPECTED_END_DATE ='10-DEC-15'
  AND C.CLIENT_NAME = 'Cathay Pacific';
 
--12. Display the no of resources working on a single project as on today

SELECT DISTINCT PROJECT_ID,COUNT(RES_ID)
FROM ASSIGNMENT_2
WHERE RELEASE_DATE IS NULL
GROUP BY PROJECT_ID;


13. Display the resource names who are not assigned to any project as on today


SELECT RES_NAME
FROM RESOURCE_2
WHERE RES_ID  NOT IN (SELECT RES_ID FROM ASSIGNMENT_2 WHERE  RELEASE_DATE   IS     NULL);


--15. Display the client country wise no of client, no of projects for the current quarter.

SELECT COUNTRY,TO_CHAR(P.EXPECTED_END_DATE,'Q'),COUNT(C.CLIENT_ID)
FROM CLIENT_2 C,ASSIGNMENT_2 A,PROJECT_2 P
WHERE C.CLIENT_ID = A.CLIENT_ID
AND A.PROJECT_ID =P.PROJECT_ID
AND TO_CHAR(P.EXPECTED_END_DATE,'Q') = TO_CHAR(SYSDATE,'Q')
GROUP BY COUNTRY,TO_CHAR(P.EXPECTED_END_DATE,'Q');

--16. Display no of projects opened in the previous financial year for every quarter. Quarter depends on financial year.

--Q1 is from April to June, Q2 is between July and Sep and likewise other quarters.

SELECT distinct CASE
        WHEN TO_CHAR(P.START_DATE,'MON') IN ('APR','MAY','JUN') THEN 'Q1'
        WHEN TO_CHAR(P.START_DATE,'MON') IN ('JUL','AUG','SEP') THEN 'Q1'
        WHEN TO_CHAR(P.START_DATE,'MON') IN ('OCT','NOV','DEC') THEN 'Q1'
        WHEN TO_CHAR(P.START_DATE,'MON') IN ('JAN','FEB','MAR') THEN 'Q1'
        END AS Quarter,A.PROJECT_ID
  FROM PROJECT_2 P,ASSIGNMENT_2 A
  WHERE P.PROJECT_ID = A.PROJECT_ID;