SQLize | PHPize | SQLtest

CREATE TABLE employees ( employee_id NUMBER PRIMARY KEY, full_name VARCHAR2(50) NOT NULL, department VARCHAR2(30) NOT NULL, salary NUMBER DEFAULT 0 CHECK (salary >= 0), commission NUMBER, hire_date DATE DEFAULT SYSDATE, status VARCHAR2(15) CHECK (status IN ('Active', 'Resigned', 'On Leave')) ); -- 2. Insert sample data INSERT INTO employees VALUES (1, 'Momen Khaled', 'IT', 5000, 500, TO_DATE('2022-01-15', 'YYYY-MM-DD'), 'Active'); INSERT INTO employees VALUES (2, 'Fatema Ahmed', 'HR', 4000, NULL, TO_DATE('2021-11-10', 'YYYY-MM-DD'), 'On Leave'); INSERT INTO employees VALUES (3, 'Salma Youssef', 'Marketing', 4500, 300, TO_DATE('2020-05-23', 'YYYY-MM-DD'), 'Active'); INSERT INTO employees VALUES (4, 'Khaled Tamer', 'Sales', 3800, 200, TO_DATE('2019-03-12', 'YYYY-MM-DD'), 'Resigned'); INSERT INTO employees VALUES (5, 'Omar Nasser', 'IT', 5200, NULL, TO_DATE('2023-02-01', 'YYYY-MM-DD'), 'Active'); INSERT INTO employees VALUES (6, 'Reem Adel', 'Finance', 4700, 150, TO_DATE('2018-07-30', 'YYYY-MM-DD'), 'On Leave'); -- 3. Select all rows from the table SELECT * FROM employees; -- 4. Update one employee’s salary UPDATE employees SET salary = salary + 500 WHERE full_name = 'Momen Khaled'; -- 5. Delete one employee with status 'Resigned' DELETE FROM employees WHERE status = 'Resigned'; -- 6. Use ROLLBACK to undo a change UPDATE employees SET commission = 400 WHERE full_name = 'Fatema Ahmed'; ROLLBACK; -- 7. Use COMMIT to save another change UPDATE employees SET salary = salary + 1000 WHERE full_name = 'Omar Nasser'; COMMIT; -- 8. Simple view showing only full_name and salary SELECT full_name, salary FROM employees; -- 9. Complex view displaying full_name, department, and total income SELECT full_name, department, (salary + NVL(commission, 0)) AS total_income FROM employees; -- 10. Create a sequence for generating employee_id CREATE SEQUENCE emp_seq START WITH 100 INCREMENT BY 1 NOCACHE; -- Example usage: -- INSERT INTO employees VALUES (emp_seq.NEXTVAL, 'New Emp', 'IT', 3000, NULL, SYSDATE, 'Active'); -- 11. Create indexes on department and hire_date CREATE INDEX idx_department ON employees(department); CREATE INDEX idx_hire_date ON employees(hire_date); -- 12. Create public and private synonyms CREATE PUBLIC SYNONYM emp_syn FOR employees; CREATE SYNONYM my_emp FOR employees;