Reference no: EM13360404
1. Create a function that returns the day of the week for a specified date.
Create [or replace] function func_name
https://www.youtube.com/watch?v=iHwjUKfhAAs
2. Create a procedure that accepts an employee number and a job. In the procedure, determine if the employee has the specified job or not. If the employee has the job, display the employee's ID number. If the employee does not have that job, display the employee's name and his or her actual job title.
3. Create a trigger to store a copy of any record deleted from the employee table into a table called Emp_temp. Assume that the Emp_temp table has the same structure as the employee table.
4. Create a trigger that displays the message "Emp table updated" when an update to the employee table increases the employee's basic salary.
-----------------------
Schema.sql keeps
------------------------
-- DESCRIPTION
-- This script creates the SQL*Plus demonstration tables in the
-- current schema. It should be STARTed by each user wishing to
-- access the tables.
SET TERMOUT ON
PROMPT Building demonstration tables. Please wait.
SET TERMOUT OFF
DROP TABLE EMPLOYEES;
DROP TABLE DEPARTMENTS;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
CREATE TABLE EMPLOYEES
(EMPLOYEE_ID NUMBER(2) NOT NULL,
EMPLOYEE_NAME VARCHAR2(20),
JOB_ID VARCHAR2(2),
JOB_DESCRIPTION VARCHAR2(20),
HOD NUMBER(2),
HIREDATE DATE,
SALARY NUMBER(10, 2),
DEPARTMENT_ID NUMBER(2));
INSERT INTO EMPLOYEES VALUES
(1, 'SMITH', 'J1', 'CLERK', 5,
TO_DATE('17-DEC-2007', 'DD-MON-YYYY'), 5800, 20);
INSERT INTO EMPLOYEES VALUES
(2, 'ALLEN', 'J2', 'SALESMAN', 3,
TO_DATE('20-FEB-2008', 'DD-MON-YYYY'), 7600, 30);
INSERT INTO EMPLOYEES VALUES
(3, 'WARD', 'J2', 'SALESMAN', 5,
TO_DATE('22-FEB-2008', 'DD-MON-YYYY'), 8250, 30);
INSERT INTO EMPLOYEES VALUES
(4, 'JONES','J3', 'MANAGER', 7,
TO_DATE('2-APR-2008', 'DD-MON-YYYY'), 4900, 20);
INSERT INTO EMPLOYEES VALUES
(5, 'JACK', 'J2','SALESMAN', 7,
TO_DATE('28-SEP-2008', 'DD-MON-YYYY'), 6700, 10);
INSERT INTO EMPLOYEES VALUES
(6, 'BLAKE', 'J3', 'MANAGER', 7,
TO_DATE('1-MAY-2008', 'DD-MON-YYYY'), 12850,30);
INSERT INTO EMPLOYEES VALUES
(7, 'CLARK', 'J3', 'MANAGER', NULL,
TO_DATE('9-JUN-2008', 'DD-MON-YYYY'), 22450, 10);
INSERT INTO EMPLOYEES VALUES
(8, 'SCOTT', 'J4', 'ANALYST', 5,
TO_DATE('09-DEC-2008', 'DD-MON-YYYY'), 13000,20);
INSERT INTO EMPLOYEES VALUES
(9, 'TURNER', 'J2','SALESMAN', 3,
TO_DATE('8-SEP-2007', 'DD-MON-YYYY'), 5500, 30);
INSERT INTO EMPLOYEES VALUES
(10, 'ADAMS', 'J1','CLERK', 5,
TO_DATE('12-JAN-2007', 'DD-MON-YYYY'), 9100, 20);
CREATE TABLE DEPARTMENTS
(DEPARTMENT_ID NUMBER(2),
DEPARTMENT_NAME VARCHAR2(20),
LOCATION VARCHAR2(20) );
INSERT INTO DEPARTMENTS VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPARTMENTS VALUES (20, 'PRODUCTION','MINNESOTA');
INSERT INTO DEPARTMENTS VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPARTMENTS VALUES (40, 'OPERATIONS', 'BOSTON');
CREATE TABLE BONUS
(ENAME VARCHAR2(10),
JOB VARCHAR2(9),
SAL NUMBER,
COMM NUMBER);
CREATE TABLE SALGRADE
(GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER);
INSERT INTO SALGRADE VALUES (1, 3000, 8000);
INSERT INTO SALGRADE VALUES (2, 8001, 14000);
INSERT INTO SALGRADE VALUES (3, 14001, 20000);
INSERT INTO SALGRADE VALUES (4, 20001, 25000);
INSERT INTO SALGRADE VALUES (5, 25001, 30000);
COMMIT;
SET TERMOUT ON
PROMPT Demonstration table build is complete.