CREATE TABLE employees (
employee_id NUMBER PRIMARY KEY,
full_name VARCHAR2(100) NOT NULL,
department VARCHAR2(50),
salary NUMBER CHECK (salary >= 0),
commission NUMBER DEFAULT 0,
hire_date DATE,
status VARCHAR2(20) CHECK (status IN ('Active', 'Resigned', 'On Leave'))
);
INSERT INTO employees VALUES (1, ' Osman ali ', 'IT', 7000, 500, TO_DATE('2015-05-10', 'YYYY-MM-DD'), 'Active');
INSERT INTO employees VALUES (2, 'Sara Ahmed', 'HR', 4000, NULL, TO_DATE('2021-03-20', 'YYYY-MM-DD'), 'On Leave');
INSERT INTO employees VALUES (3, 'Mona Khalid', 'Marketing', 0, 0, TO_DATE('2020-12-01', 'YYYY-MM-DD'), 'Resigned');
INSERT INTO employees VALUES (4, 'Hassan Yasser', 'IT', 6000, 600, TO_DATE('2023-01-15', 'YYYY-MM-DD'), 'Active');
INSERT INTO employees VALUES (5, 'Rawan Noor', NULL, 3000, 400, NULL, 'Active');
INSERT INTO employees VALUES (6, 'Tariq adil', 'Finance', 4500, 300, TO_DATE('2022-11-11', 'YYYY-MM-DD'), 'On Leave');
select *from employees;
UPDATE employees
SET salary = 4200
WHERE employee_id = 2;
select *from employees;
ROLLBACK;
select * from employees;
COMMIT;
SELECT full_name, salary FROM employees;
SELECT full_name, department, (salary + NVL(commission, 0)) AS total_income
FROM employees;
CREATE SEQUENCE emp_seq
START WITH 7
INCREMENT BY 1;
---INSERT INTO employees (employee_id, full_name, department, salary, commission, hire_date, status)
---VALUES (emp_seq.NEXTVAL, 'Roaa yasser', 'IS', 8800, 1000, TO_DATE('2024-01-01', 'YYYY-MM-DD'), 'Active');
---Select *from employees;
CREATE INDEX dept_idx ON employees(department);
CREATE INDEX hiredate_idx ON employees(hire_date);
Telegram: DB developers channel