-- create a table
CREATE TABLE EMPLOYEE
(
    ENO NUMBER NOT NULL PRIMARY KEY,
    ENAME VARCHAR2(30) NOT NULL,
    DEPT VARCHAR2(20) NOT NULL,
    BASIC NUMBER CHECK (BASIC > 5000 AND BASIC < 45000)
);
-- insert some values
INSERT INTO EMPLOYEE VALUES (101, 'Amit',   'Accounts', 15000);
INSERT INTO EMPLOYEE VALUES (102, 'Rahul',  'Finance',  22000);
INSERT INTO EMPLOYEE VALUES (103, 'Sneha',  'Education',18000);
INSERT INTO EMPLOYEE VALUES (104, 'Priya',  'Accounts', 30000);
INSERT INTO EMPLOYEE VALUES (105, 'Kiran',  'Finance',  12000);
COMMIT;
-- fetch some values
ALTER TABLE EMPLOYEE ADD
(
    DA NUMBER(10,2),
    HRA NUMBER(10,2),
    GrossPay NUMBER(10,2),
    PF NUMBER(10,2),
    PT NUMBER(10,2),
    Netpay NUMBER(10,2)
);
UPDATE EMPLOYEE
SET DA =
    CASE
        WHEN BASIC <= 20000 THEN BASIC * 0.80
        ELSE BASIC * 0.70
    END;
UPDATE EMPLOYEE
SET HRA = BASIC * 0.10;
UPDATE EMPLOYEE
SET GrossPay = BASIC + DA + HRA;
UPDATE EMPLOYEE
SET PF = GrossPay * 0.12;
UPDATE EMPLOYEE
SET PT = GrossPay * 0.05;
UPDATE EMPLOYEE
SET Netpay = GrossPay - (PF + PT);

COMMIT;
SELECT * FROM EMPLOYEE;
SELECT ENAME
FROM EMPLOYEE
WHERE DEPT = 'Finance';
SELECT ENAME
FROM EMPLOYEE
ORDER BY ENAME ASC;
SELECT ENAME, Netpay
FROM EMPLOYEE
ORDER BY Netpay DESC;
SELECT *
FROM EMPLOYEE
WHERE Netpay BETWEEN 15000 AND 35000;
SELECT ENAME, BASIC
FROM EMPLOYEE
WHERE ENAME LIKE '%h';
SELECT DEPT, COUNT(*) AS NUMBER_OF_EMPLOYEES
FROM EMPLOYEE
GROUP BY DEPT
HAVING COUNT(*) > 3;
DELETE FROM EMPLOYEE
WHERE DEPT = 'Accounts';

COMMIT;
SELECT * FROM EMPLOYEE;