Assignemnt 5

--TABLE 1:

CREATE TABLE EMPLOYEE (
    EMPNO NUMBER(4) PRIMARY KEY,
    EMPNAME VARCHAR2(50),
    JOB VARCHAR2(30),
    DEPTNO NUMBER(2),
    SALARY NUMBER(8, 2),
    COMM NUMBER(8, 2),
    DOB DATE,
    MNO NUMBER(4),
    HIRE_DATE DATE
);

--TABLE 2:

CREATE TABLE DEPTT (
    DPTNO NUMBER(2) PRIMARY KEY,
    DPTNAME VARCHAR2(50),
    LOCATION VARCHAR2(50)
);



--INSERT DATA:

INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (1, 'Oliver', ''Analyst', 1, 60000, 1000, TO_DATE('1989-01-10', 'yyyy-mm-dd'), 3, TO_DATE('2009-01-05', 'yyyy-mm-dd'));

INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (2, 'Samuel', 'Salesperson', 2, 25000, 150, TO_DATE('1995-09-21', 'yyyy-mm-dd'), 1, TO_DATE('2013-02-15', 'yyyy-mm-dd'));

INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (3, 'Alexander', 'Manager', 3, 50000, NULL, TO_DATE('1980-02-15', 'yyyy-mm-dd'), NULL, TO_DATE('2008-10-09', 'yyyy-mm-dd'));

INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (4, 'James', 'Analyst', 1, 40000, 5000, TO_DATE('1990-02-19', 'yyyy-mm-dd'), 1, TO_DATE('2016-05-01', 'yyyy-mm-dd'));

INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (5, Andrew', 'Salesperson', 2, 28000, 400, TO_DATE('1987-11-20', 'yyyy-mm-dd'), 1, TO_DATE('2016-05-10', 'yyyy-mm-dd'));

INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (6, 'Jennifer', 'Engineer', 3, 50000, 800, TO_DATE('1990-09-30', 'yyyy-mm-dd'), 2, TO_DATE('2015-12-05', 'yyyy-mm-dd'));

INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (7, 'Charles', 'Engineer', 4, 70000, 2500, TO_DATE('1987-02-09', 'yyyy-mm-dd'), 2, TO_DATE('2006-09-12', 'yyyy-mm-dd'));

INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (8, 'Anthony', 'Analyst', 1, 52000, 1500, TO_DATE('1995-09-06', 'yyyy-mm-dd'), 3, TO_DATE('2019-03-14', 'yyyy-mm-dd'));

INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (9, Joseph', 'Manager', 3, 68000, NULL, TO_DATE('1990-11-25', 'yyyy-mm-dd'), NULL, TO_DATE('2020-01-09', 'yyyy-mm-dd'));

INSERT INTO EMPLOYEE (EMPNO, EMPNAME, JOB, DEPTNO, SALARY, COMM, DOB, MNO, HIRE_DATE)
VALUES (10, 'Isaac', 'Salesperson', 2, 26000, 400, TO_DATE('1996-02-25', 'yyyy-mm-dd'), 1, TO_DATE('2019-09-30', 'yyyy-mm-dd'));


--sql query


--Add a column "Marital Status" to the EMPLOYEE table

ALTER TABLE EMPLOYEE
ADD Marital_Status VARCHAR2(20) CHECK (Marital_Status IN ('Unmarried', 'Married', 'Divorce'));

--Change the column name from MNO to MGRNO

ALTER TABLE EMPLOYEE
RENAME COLUMN MNO TO MGRNO;

--Create a view named EMPVIEW containing EMPNO, EMPNAME, and SALARY of employees who work in department 3

CREATE VIEW EMPVIEW AS
SELECT EMPNO, EMPNAME, SALARY
FROM EMPLOYEE
WHERE DEPTNO = 3;

--Create a view named deptt_summary that consists of deptt_name, no. of employees, total_salary, maximum salary, and minimum salary for each department

CREATE VIEW deptt_summary AS
SELECT
    D.DEPTNO AS Department_Number,
    D.DEPTNAME AS Department_Name,
    COUNT(E.EMPNO) AS No_of_Employees,
    SUM(E.SALARY) AS Total_Salary,
    MAX(E.SALARY) AS Maximum_Salary,
    MIN(E.SALARY) AS Minimum_Salary
FROM
    DEPARTMENT D
LEFT JOIN
    EMPLOYEE E
ON
    D.DEPTNO = E.DEPTNO
GROUP BY
    D.DEPTNO, D.DEPTNAME;

--Grant INSERT and UPDATE privileges on DEPT to both RAM and SHAYM

GRANT INSERT, UPDATE ON DEPARTMENT TO RAM, SHAYM;

--Delete all information about department no. 3 and job='Engineer'

DELETE FROM EMPLOYEE
WHERE DEPTNO = 3 AND JOB = 'Engineer';

--Modify the data type of DPTNO from number to varchar2

ALTER TABLE EMPLOYEE
MODIFY (DEPTNO varchar2);

--Increase the salary of each employee by 10%

UPDATE EMPLOYEE
SET SALARY = SALARY * 1.10;

--Show the total salary of each employee (including SAL and COMM)

Show the total salary of each employee (including SAL and COMM)

--Show the salary of each employee who has not received any commission

SELECT EMPNO, EMPNAME, SALARY
FROM EMPLOYEE
WHERE COMM IS NULL;

--Increase the salary of each employee as the sum of salary and comm

UPDATE EMPLOYEE
SET SALARY = SALARY + COALESCE(COMM, 0);