Assignemnt 4

-- Creating DEPTT table
CREATE TABLE DEPTT (
    DPTNO NUMBER PRIMARY KEY,
    DPTNAME VARCHAR2(50),
    LOCATION VARCHAR2(50)
);

-- Creating EMPLOYEE table with foreign key reference to DEPTT table
CREATE TABLE EMPLOYEE (
    EMPNO NUMBER PRIMARY KEY,
    EMPNAME VARCHAR2(50),
    JOB VARCHAR2(50),
    DEPTNO NUMBER,
    SALARY NUMBER(10, 2),
    COMM NUMBER(10, 2),
    DOB DATE,
    MANAGER_NO NUMBER,
    HIRE_DATE DATE,
    CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPTT(DPTNO)
);

--Display all employees whose names have 'AM' or 'SH' in them.
SELECT EMPNAME, JOB, SALARY
FROM EMPLOYEE
WHERE EMPNAME LIKE '%AM%' OR EMPNAME LIKE '%SH%';

--List EMPNAME, JOB, and SALARY for all who have a manager.
SELECT EMPNAME, JOB, SALARY
FROM EMPLOYEE
WHERE MANAGER_NO IS NOT NULL;

--Show EMPNAME and TOTAL remuneration of all employees.
SELECT EMPNAME, SALARY + NVL(COMM, 0) AS TOTAL_REMUNERATION
FROM EMPLOYEE;

--Show all employees who were hired during 1998.
SELECT EMPNAME
FROM EMPLOYEE
WHERE EXTRACT(YEAR FROM HIRE_DATE) = 1998;

--Show name, annual salary, and commission of all sales people whose monthly salary is greater than their commission. The output should be ordered by name within salary, highest first.
SELECT EMPNAME, SALARY * 12 AS ANNUAL_SALARY, COMM
FROM EMPLOYEE
WHERE JOB = 'Salesman' AND SALARY > NVL(COMM, 0)
ORDER BY EMPNAME, SALARY DESC;

--Show the employee details under the heading “who, what and when”.
SELECT EMPNAME || ' HAS HELD THE POSITION OF ' || JOB || ' IN DEPT ' || DEPTNO || ' SINCE ' || TO_CHAR(HIRE_DATE, 'DD-MON-YYYY') AS "who,what and when"
FROM EMPLOYEE;

--Calculate and show the minimum salary for each different job.
SELECT JOB, MIN(SALARY) AS MIN_SALARY
FROM EMPLOYEE
GROUP BY JOB;

--Show the average salary for job excluding manager.
SELECT JOB, AVG(SALARY) AS AVG_SALARY
FROM EMPLOYEE
WHERE JOB <> 'Manager'
GROUP BY JOB;

--Show the average monthly salary bill for each job type within a department.
SELECT DEPTNO, JOB, AVG(SALARY) * 12 AS AVG_MONTHLY_SALARY_BILL
FROM EMPLOYEE
GROUP BY DEPTNO, JOB;

--Show the average salary for all departments employing more than three people.
SELECT DEPTNO, AVG(SALARY) AS AVG_SALARY
FROM EMPLOYEE
GROUP BY DEPTNO
HAVING COUNT(*) > 3;

--Show only those jobs where the maximum salary is greater than or equal to 1000.
SELECT DISTINCT JOB
FROM EMPLOYEE
WHERE JOB IN (SELECT JOB FROM EMPLOYEE GROUP BY JOB HAVING MAX(SALARY) >= 1000);

--Show job and average salary for all excluding those of managers.
SELECT JOB, AVG(SALARY) AS AVERAGE_SALARY
FROM EMPLOYEE
WHERE JOB <> 'Manager'
GROUP BY JOB;

--Find all departments, which have more than 3 employees.
SELECT DEPTNO
FROM EMPLOYEE
GROUP BY DEPTNO
HAVING COUNT(*) > 3;

--Check whether all employee numbers are indeed unique.
SELECT CASE
         WHEN COUNT(EMPNO) = COUNT(DISTINCT EMPNO) THEN 'All Employee Numbers are Unique'
         ELSE 'Duplicate Employee Numbers Exist'
       END AS RESULT
FROM EMPLOYEE;

--List managers’ minimum salary by excluding any groups where the minimum salary is less than 1000 and sort the output by salary.
SELECT MANAGER_NO, MIN(SALARY) AS MINIMUM_SALARY
FROM EMPLOYEE
WHERE MANAGER_NO IS NOT NULL
GROUP BY MANAGER_NO
HAVING MIN(SALARY) >= 1000
ORDER BY MINIMUM_SALARY;

--people working in HR department
SELECT E.EMPNO, E.EMPNAME
FROM EMPLOYEE029 E
JOIN DEPTT D ON E.DEPTNO = D.DPTNO
WHERE D.DPTNAME = 'HR Department';