Assignemnt 6

-- Create DEPTT table
CREATE TABLE DEPTT (
    DPTNO INT PRIMARY KEY,
    DPTNAME VARCHAR(50),
    LOCATION VARCHAR(50)
);

-- Create SALGRADE table
CREATE TABLE SALGRADE (
    MINSAL INT,
    MAXSAL INT,
    GRADE INT PRIMARY KEY
);

-- Create EMPLOYEE table with foreign key references
CREATE TABLE EMPLOYEE (
    EMPNO INT PRIMARY KEY,
    EMPNAME VARCHAR(50),
    JOB VARCHAR(50),
    DEPTNO INT,
    SALARY INT,
    COMM INT,
    DOB DATE,
    MANAGER_NO INT,
    HIRE_DATE DATE,
    FOREIGN KEY (DEPTNO) REFERENCES DEPTT(DPTNO),
    FOREIGN KEY (MANAGER_NO) REFERENCES EMPLOYEE(EMPNO),
    CONSTRAINT CHK_SALARY CHECK (SALARY BETWEEN (SELECT MIN(MINSAL) FROM SALGRADE) AND (SELECT MAX(MAXSAL) FROM SALGRADE))
);

--Show name, salary, and grade for all employees.
SELECT E.EMPNAME, E.SALARY, S.GRADE
FROM EMPLOYEE E
JOIN SALGRADE S ON E.SALARY BETWEEN S.MINSAL AND S.MAXSAL;

--Show all employee names and their department name, in department name order.
SELECT E.EMPNAME, D.DPTNAME
FROM EMPLOYEE E
JOIN DEPTT D ON E.DEPTNO = D.DPTNO
ORDER BY D.DPTNAME;

--List employee details except clerks, sorted by descending salary
SELECT E.EMPNAME, E.JOB, E.SALARY, S.GRADE, D.DPTNAME
FROM EMPLOYEE E
JOIN SALGRADE S ON E.SALARY BETWEEN S.MINSAL AND S.MAXSAL
JOIN DEPTT D ON E.DEPTNO = D.DPTNO
WHERE E.JOB != 'CLERK'
ORDER BY E.SALARY DESC;

--List employees earning Rs. 84000/year or clerks
SELECT E.EMPNAME, E.JOB, E.SALARY, E.DEPTNO, D.DPTNAME, S.GRADE
FROM EMPLOYEE E
JOIN SALGRADE S ON E.SALARY BETWEEN S.MINSAL AND S.MAXSAL
JOIN DEPTT D ON E.DEPTNO = D.DPTNO
WHERE E.SALARY = 84000 OR E.JOB = 'CLERK';

--Show distinct jobs in dept_no 20 or 25.
SELECT DISTINCT JOB
FROM EMPLOYEE
WHERE DEPTNO IN (20, 25);

--Show common jobs of dept_no 20 and 25
SELECT JOB
FROM EMPLOYEE
WHERE DEPTNO = 20
INTERSECT
SELECT JOB
FROM EMPLOYEE
WHERE DEPTNO = 25;

--Show jobs of dept_no 10 except those in dept_no 25
SELECT JOB
FROM EMPLOYEE
WHERE DEPTNO = 10
MINUS
SELECT JOB
FROM EMPLOYEE
WHERE DEPTNO = 25;

--Show departments without employees
SELECT D.DPTNAME
FROM DEPTT D
LEFT JOIN EMPLOYEE E ON D.DPTNO = E.DEPTNO
WHERE E.EMPNO IS NULL;

--List employees with managers' names and numbers
SELECT E1.EMPNAME AS EmployeeName, E1.EMPNO AS EmployeeNumber, E2.EMPNAME AS ManagerName, E2.EMPNO AS ManagerNumber
FROM EMPLOYEE E1
LEFT JOIN EMPLOYEE E2 ON E1.MANAGER_NO = E2.EMPNO;

--Show employees with no manager (assuming managers have NULL in MANAGER_NO field)
SELECT EMPNAME
FROM EMPLOYEE
WHERE MANAGER_NO IS NULL;

--Find the job filled in the first half of 1985 and 1986
SELECT JOB
FROM EMPLOYEE
WHERE EXTRACT(YEAR FROM HIRE_DATE) = 1985 AND EXTRACT(MONTH FROM HIRE_DATE) <= 6
UNION
SELECT JOB
FROM EMPLOYEE
WHERE EXTRACT(YEAR FROM HIRE_DATE) = 1986 AND EXTRACT(MONTH FROM HIRE_DATE) <= 6;

--Find employees who joined before their managers
SELECT E1.EMPNAME AS EmployeeName, E2.EMPNAME AS ManagerName
FROM EMPLOYEE E1
JOIN EMPLOYEE E2 ON E1.MANAGER_NO = E2.EMPNO
WHERE E1.HIRE_DATE < E2.HIRE_DATE;

--Employees with the lowest salary in each department
SELECT E1.EMPNAME, E1.SALARY, E1.DEPTNO
FROM EMPLOYEE E1
WHERE E1.SALARY = (SELECT MIN(E2.SALARY) FROM EMPLOYEE E2 WHERE E1.DEPTNO = E2.DEPTNO);

--Employees with the lowest salary in their department
SELECT E1.EMPNAME, E1.SALARY, E1.DEPTNO
FROM EMPLOYEE E1
WHERE E1.SALARY = (SELECT MIN(SALARY) FROM EMPLOYEE E2 WHERE E1.DEPTNO = E2.DEPTNO);

--Employees earning more than the lowest salary in department 20
SELECT EMPNAME, SALARY
FROM EMPLOYEE
WHERE SALARY > (SELECT MIN(SALARY) FROM EMPLOYEE WHERE DEPTNO = 20);

--Employees earning more than every employee in department 20.
SELECT EMPNAME, SALARY
FROM EMPLOYEE
WHERE SALARY > ALL (SELECT SALARY FROM EMPLOYEE WHERE DEPTNO = 20);

--Departments with an average salary bill greater than deptt-30.
SELECT D.DPTNAME
FROM DEPTT D
JOIN EMPLOYEE E ON D.DPTNO = E.DEPTNO
GROUP BY D.DPTNAME
HAVING AVG(E.SALARY) > (SELECT AVG(SALARY) FROM EMPLOYEE WHERE DEPTNO = 30);

--Job with the highest average salary.
SELECT JOB
FROM EMPLOYEE
GROUP BY JOB
ORDER BY AVG(SALARY) DESC
FETCH FIRST 1 ROWS ONLY;

--Employees with salary higher than the highest in any sales department
SELECT EMPNAME, JOB, HIRE_DATE
FROM EMPLOYEE
WHERE SALARY > (SELECT MAX(SALARY) FROM EMPLOYEE WHERE DEPTNO IN (SELECT DPTNO FROM DEPTT WHERE DPTNAME = 'SALES'));

--Employees who don't manage anyone
SELECT EMPNAME
FROM EMPLOYEE
WHERE EMPNO NOT IN (SELECT MANAGER_NO FROM EMPLOYEE WHERE MANAGER_NO IS NOT NULL);

--Most recently hired employees in each department
SELECT EMPNAME, HIRE_DATE
FROM (
    SELECT EMPNAME, HIRE_DATE, DEPTNO,
           ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY HIRE_DATE DESC) AS rnum
    FROM EMPLOYEE
) WHERE rnum = 1;

--Departments with no employees (using subquery)
SELECT DPTNAME
FROM DEPTT
WHERE DPTNO NOT IN (SELECT DISTINCT DEPTNO FROM EMPLOYEE);

--Top 5 earners in the company.
SELECT EMPNAME, SALARY
FROM EMPLOYEE
ORDER BY SALARY DESC
FETCH FIRST 5 ROWS ONLY;

--Departments with employees from all salary grades
SELECT D.DPTNAME
FROM DEPTT D
WHERE NOT EXISTS (
    SELECT DISTINCT GRADE
    FROM SALGRADE S
    WHERE NOT EXISTS (
        SELECT 1
        FROM EMPLOYEE E
        WHERE E.DEPTNO = D.DPTNO AND E.SALARY BETWEEN S.MINSAL AND S.MAXSAL
    )
);