DATABASE MANAGEMENT SYSTEM

PRACTICAL 1 (SELECT, WHERE, ORDER BY)

1. Create a query to display the last name and salary of employees earning more than 12000.

SELECT last_name, salary FROM employees WHERE salary>12000;

2. Create a query to display the employee last name and department number for employee number
176.

SELECT last_name, department_id FROM employees WHERE employee_id=176;

3. Display the last name and salary for all employees whose salary is not in the range of 5000 and
12000.

SELECT last_name, salary FROM employees WHERE salary NOT BETWEEN 5000 AND 12000;

4. Display the employee last name, job ID and start date of employees hired between February
20,1998 and May 01,1998. Order the query in ascending order by start date.

SELECT last_name, job_id, hire_date FROM employees WHERE hire_date BETWEEN '20-FEB-98' AND
'01-MAY-98' ORDER BY hire_date;

5. Display the last name and the department number of all the employees in department 50 and 60
in alphabetical order by name.

SELECT last_name, department_id FROM employees WHERE department_id IN (50,60) ORDER BY

last_name;

6. Display the last name and hire date of every employee who was hired in 1998.

SELECT last_name, hire_date FROM employees WHERE hire_date BETWEEN '01-JAN-98' AND '31-
DEC-98';

OR

SELECT last_name, hire_date FROM employees WHERE hire_date LIKE '%98';

7. Display the last name and job ID of all employees who do not have a manager.

SELECT last_name, job_id FROM employees WHERE manager_id IS NULL;

8. Display the last name, salary and commission for all employees who earn commission, sort the
salary and commission is descending order.

SELECT last_name, salary, commission_pct FROM employees WHERE commission_pct IS NOT NULL
ORDER BY salary DESC, commission_pct DESC;

9. Display the last name of all employees where the third letter of the name is 'A'.

SELECT last_name FROM employees WHERE last_name LIKE '__a%';

10. Display the last name of all employees who have an 'a' and an 'e' in their name.

SELECT last_name FROM employees WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';

11. Display the last name, job and salary for all employees whose job is sales representative or stock
clerk and whose salary is not equal to 2500, 3500 and 7000.

SELECT last_name, job_id, salary FROM employees WHERE job_id IN ('SA_REP','ST_CLERK') AND
salary NOT IN (2500,3500,7000);

12. Display the last name, salary and commission for all the employees whose commission amount is
20%. Label the columns as 'employee' and 'monthly salary'.

SELECT last_name AS "employee", salary AS "monthly salary", commission_pct FROM employees

WHERE commission_pct = 0.20;

PRACTICAL 2 (JOINS AND GROUP BY QUERIES)

1. Write a query to display the last name, department number and department name for all
employees.

select last_name, e.department_id, department_name from employees e, departments d where

e.department_id=d.department_id;

2. Create a unique listing of all jobs that are in department 80. Include the location of the
department in the output.

select distinct last_name, location_id from employees e, departments d where e.department_id=80

and e.department_id=d.department_id;
3. Write a query to display the employee last name, department name, location ID and city of all
employees who earn a commission.

select last_name, department_name, d.location_id, city from employees e, departments d, locations

l where e.department_id=d.department_id and d.location_id=l.location_id and commission_pct is
not null;

4. Display the employee last name and department name for all employees who have on a lowercase
in their last names.

select last_name, department_name from employees e, departments d where

e.department_id=d.department_id and last_name like '%a%';

5. Display the employee last name and employee number along with their manager's last name and
manager number. Label the columns Employee, Emp#, Manager and Mgr# respectively.

select e1.last_name as Employee, e1.employee_id as emp#, e2.last_name as Manager,

e2.employee_id as mgr# from employees e1,employees e2 where e1.manager_id=e2.employee_id;

6. Create a query to display the name and hire date of any employee hired after employee Davis.

select e1.last_name, e1.hire_date from employees e1, employees e2 where

e1.hire_date>e2.hire_date and e2.last_name='Davis';

7. Display the names and hire dates for all the employees who were hired before their managers,
along with their manager's name and hire dates. Label the columns Employee, Emp Hired, Manager,
and Mgr Hired, respectively.

select e1.last_name as Employee, e1.hire_date as "Emp Hired" , e2.last_name as Manager,

e2.hire_date as "Mgr Hired" from employees e1, employees e2 where
e1.manager_id=e2.employee_id and e1.hire_date<e2.hire_date;

8. Write a query to display the number of people with the same job.

select job_id,count(*) from employees group by job_id;

9. Write a query that displays the difference between the lowest and highest salaries. Label the
column DIFFERENCE.

select max(salary)-min(salary) as Difference from employees;

select max(salary) from employees;

select min(salary) from employees;

10. Determine the number of managers without listing them. Label the column number of managers.

select count(distinct manager_id) as "Number of Managers" from employees;

11. Display the highest, lowest, sum and average salary for each job type. Label the columns as
Maximum, Minimum, Sum and Average respectively. Round your results to the nearest whole
number.

select job_id, round(max(salary),0) as Maximum, round(min(salary),0) as Minimum,

round(sum(salary),0) as Sum, round(avg(salary),0) as Average from employees group by job_id;

12. Display the manager number and the salary of the lowest paid employee for that manager.
Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is 6000
or less. Sort the output in descending order of salary.

select manager_id, min(salary) from employees where manager_id is not null group by manager_id
having min(salary)>6000 order by min(salary) desc;

PRACTICAL 3 (DDL STATEMENTS – Create, Alter, Drop)

1. Create Products table (Combine the composite key mfr id and product id into a single column)

create table products(mfr_prod_id number(10) primary key, description varchar(20), price

number(10), oty_on_hand number(5));

2. Create salesreps table (without making rep office and manager as foreign key)

create table salesreps(emp_no number(5) primary key, name varchar2(25), age number(3),
rep_office number(3), title varchar2(15), hire_date date, manager number(5), quota number(10),
sales number(10));

3. Create Customers table

create table customers(cust_num char(5) primary key, company varchar2(25), cust_rep number(5)
references salesreps(emp_no), credit_limit number(10));

4. Create Orders table (Combine the composite key and prod into a single column)

create table orders(order_no number(5) primary key, order_date date, cust char(5) references
customers(cust_num), rep number(5) references salesreps(emp_no), mp_id number(10) references
products(mfr_prod_id), qty number(5), amt number(10));
5. Create offices table

create table offices(office number(5) primary key, city varchar2(10), region varchar2(20), mgr
number(5) references salesreps(emp_no), target number(10), sales number(10));

6. Alter salesreps table to now make rep office and manager as foreign key.

alter table salesreps modify(manager number(5) references salesreps(emp_no), rep_office

number(5) references offices(office));

7. Drop any table.

drop table <tablename>;

PRACTICAL 4 (DML STATEMENTS – Insert, Update, Delete)

1. Insert the rows into Products table.

insert into products values(11011,'Wasing Machine',50000,5);

insert into products values(11012,'Fridge',30000,8);

2. Insert the rows into Salesreps table (Insert null in rep office and manager)

insert into salesreps values(101,'Smitha',23,NULL,'Sales Rep','09-JAN-99',NULL,50000,30000);

insert into salesreps values(102,'Anand',40,NULL,'Stock Clerk','09-SEP-03',NULL,45000,60000);

insert into salesreps values(103,'Harish',35,NULL,'Sales Rep','09-MAY-05',NULL,60000,80000);

3. Insert the rows into Customers table.

insert into customers values('C1','ABC',102,100000);

insert into customers values('C2','XYZ',103,50000);

4. Insert the above given rows into Orders table.

insert into orders values(111,'05-MAR-20','C1',102,11011,2,100000);

insert into orders values(112,'10-AUG-20','C2',101,11012,1,30000);

5. Insert the rows into Office table.

insert into offices values(11,'Mumbai','Western',102,50000,30000);

insert into offices values(22,'Pune','Western',101,45000,60000)

6. Update the Null values in rep offices and manager column in salesreps table to actual values.

update salesreps set rep_office=11 where emp_no=101;

update salesreps set rep_office=22,manager=101 where emp_no=102;

update salesreps set rep_office=11,manager=102 where emp_no=103;

7. Delete all the Products.

delete from products;

8. Delete customers who have credit limit less than 1,00,000.

delete from customers where credit_limit<100000;

PRACTICAL 5 (SUBQUERIES)

1. Write a query to display the last name and hire date of any employee in the same department as
Zlotkey. Exlude Zlotkey.

select last_name, hire_date from employees where department_id=(select department_id from

employees where last_name='Zlotkey') and last_name<>'Zlotkey';

2. Create a query to display the employee numbers and last names of all the employees who earn
more than the average salary. Sort the results in ascending order of salary.

select employee_id, last_name from employees where salary>(select avg(salary) from employees)
order by salary;

3. Write a query that displays the employee numbers and last names of all employees who work in a
department with any employee whose last name

contains a 'u'.

select employee_id, last_name from employees where department_id in (select department_id

from employees where last_name like '%u%');
4. Display the last name, department number and job id of all employees whose department
location ID is 1700.

select last_name, department_id, job_id from employees where department_id in (select

department_id from departments where location_id=1700);

5. Display the last name and salary of every employee who reports to King.

select last_name, salary from employees where manager_id=(select employee_id from employees
where last_name='King');

6. Display the department number, last name and job ID for every employee in the Executive
Department.

select department_id, last_name, job_id from employees where department_id=(select

department_id from departments where department_name='Executive');

7. Create a query to display the employees who earn a salary that is higher than the salary of all the
sales managers (JOB_ID = 'ST_MAN'). Sort the results on salary from highest to lowest.

select last_name, salary from employees where salary>ALL(select salary from employees where
job_id='ST_MAN') order by salary desc;

8. Display the details of the employee ID, last name and department ID of those employees who live
in cities whose name begins with O. (Nested Query)

select employee_id, last_name, department_id from employees where department_id in (select

department_id from departments where location_id in (select location_id from locations where city
like 'O%'));

9. Write a query to display the last names of the employees who earn less than the average salary in
their departments. (Correlated Subquery)

select last_name from employees e1 where e1.salary<(select avg(e2.salary) from employees e2

where e2.department_id=e1.department_id);

PRACTICAL 6 (DATABASE OBJECTS)

1. Create a view called EMPLOYEES_VU based on the employee numbers, employee names and
department numbers from EMPLOYEES table.

create or replace view employees_vu as select employee_id, last_name "employee", department_id

from employees;
2. Display the contents of EMPLOYEES_VU view.

select * from employees_vu;

3. Using your EMPLOYEES_VU view, enter a query a display all employee names and department
numbers.

select "employee", department_id from employees_vu;

4. Create a view named DEPT50 that contains the employee numbers, employee last names and
department numbers for all employees in departments50. Label the view columns EMPNO,
EMPLOYEE and DEPTNO. Do not allow an employee to be reassigned to another department.

create view dept50 as select employee_id "empno", last_name "employee", department_id

"deptno" from employees where department_id=50 with check option;

5. Display the structure and contents of the DEPT50 view.

describe dept50;

select * from dept50;

6. Create a table DEPT with id as primary key and dname.

create table dept(id number(5) primary key, dname varchar2(15));

7. Create a sequence to be used with the primary key column of the DEPT table. The sequence
should start at 200 and have a maximum value of 1000. Have your sequence incremented by ten
numbers. Name the sequence DEPT_IN_SEQ.

create sequence dept_id_seq start with 200 increment by 10 maxvalue 1000;

8. Write a script to insert two rows into the DEPT table. Be sure to use the sequence that you
created for the ID column. Add two departments named Education and Administration. Confirm your
additions.

insert into dept values(dept_id_seq.nextval, 'Education');

insert into dept values(dept_id_seq.nextval, 'Administration');

select * from dept;

9. Create a synonym called emp on the employees table.

create synonym emp for employees;

10. Create a nonunique index on the foreign key column (department_id) in the EMP table.

create index emp_dept_id_idx on emp(department_id);

PRACTICAL 7 (PL/SQL)

1. Create a PL/SQL block that selects the maximum department number in the departments table.
Print the results on the screen.

declare

v_max_deptno number;

begin

select max(department_id) into v_max_deptno from departments;

dbms_output.put_line('The maximum department number is ' || v_max_deptno);

end;

2. Write a PL/SQL block to return the sum of salaries for all employees in the specified department.

declare

v_sum_sal number(10,2);

v_deptno number not null := 60;

begin

select sum(salary) into v_sum_sal from employees where department_id=v_deptno;

dbms_output.put_line('The sum of the salary for department ' || v_deptno || ' is ' || v_sum_sal);

end;

3. Write a PL/SQL block to increase the salary of all employees by 1000, who are stock clerks.

declare

v_sal_increase employees.salary%TYPE := 1000;

begin

update employees set salary=salary+v_sal_increase where job_id='ST_CLERK';

 dbms_output.put_line('Employees salary updated successfully');

end;

4. Write a PL/SQL block to demonstrate a basic loop. Insert three new locations IDs for the country
code of CA and the city of Montreal.

select * from locations;

declare

v_country_id locations.country_id%TYPE := 'CA';

v_city locations.city%TYPE := 'Montreal';

v_location_id locations.location_id%TYPE;

v_counter number(2) := 1;

begin

select max(location_id) into v_location_id from locations where country_id=v_country_id;

loop

insert into locations(location_id, city, country_id) values ((v_location_id + v_counter), v_city,

v_country_id);

v_counter := v_counter + 1;

exit when v_counter > 3;

end loop;

dbms_output.put_line('New location added');

end;

5. Write a PL/SQL block to demonstrate a while loop. Insert three new locations IDs for the country
code of CA and the city of Montreal.

declare

v_country_id locations.country_id%TYPE := 'CA';

v_city locations.city%TYPE := 'Montreal';

v_location_id locations.location_id%TYPE;

v_counter number(2) := 1;

begin

select max(location_id) into v_location_id from locations where country_id=v_country_id;

while v_counter <= 3 loop

 insert into locations(location_id, city, country_id) values ((v_location_id + v_counter), v_city,
v_country_id);

v_counter := v_counter + 1;

end loop;

dbms_output.put_line('New location added');

end;

6. Write a PL/SQL block to demonstrate a for loop. Insert three new locations IDs for the country
code of UK and the city of Manchester.

declare

v_country_id locations.country_id%TYPE := 'UK';

v_city locations.city%TYPE := 'Manchester';

v_location_id locations.location_id%TYPE;

begin

select max(location_id) into v_location_id from locations where country_id=v_country_id;

for i in 1..3 loop

insert into locations(location_id, city, country_id) values ((v_location_id + i), v_city, v_country_id);

end loop;

end;

PRACTICAL 8 (CURSORS, EXCEPTIONS)

1. Create the table, top_emp with two field names name and salary. Create a PL/SQL block using a
cursor to determine the top 'n' employees with respect to their salaries.

create table top_emp(name varchar(25), salary number(8,2));

DECLARE

v_num NUMBER(3):=5;

v_sal employees.salary%TYPE;

CURSOR emp_cursor IS

SELECT DISTINCT salary

FROM employees ORDER BY salary DESC;

BEGIN

OPEN emp_cursor;
 FETCH emp_cursor INTO v_sal;

WHILE emp_cursor%ROWCOUNT <= v_num AND emp_cursor%FOUND

LOOP

INSERT INTO top_emp(salary) VALUES (v_sal);

END LOOP;

CLOSE emp_cursor;

COMMIT;

END;

select * from top_emp;

2. Create a PL/SQL block using a cursor to do the following:-

a) Use a variable to provide the department_id.

b) In a PL/SQL block, retrieve the last_name,salary and manager_id of the employees working in that
department.

c) If the salary is less than 5000 and if the manager id is either 101 or 124, display the message,
display the message <<last_name>> due for a raise. Otherwise display the message<<last_name>>
not due for a raise.

d) Test the PL/SQL block for the following departments 10,50,60,80.

DECLARE

v_deptno NUMBER(4):=10;

v_ename employees.last_name%TYPE;

v_sal employees.salary%TYPE;

v_manager employees.manager_id%TYPE;

CURSOR emp_cursor IS

SELECT last_name,salary,manager_id

FROM employees WHERE department_id=v_deptno;

BEGIN

OPEN emp_cursor;

FETCH emp_cursor INTO v_ename,v_sal,v_manager;

WHILE emp_cursor%FOUND LOOP

IF v_sal< 5000 AND (v_manager = 101 OR v_manager = 124) THEN

DBMS_OUTPUT.PUT_LINE(v_ename||'due for a raise');

ELSE

DBMS_OUTPUT.PUT_LINE(v_ename||' not due for a raise');

END IF;

FETCH emp_cursor INTO v_ename,v_sal,v_manager;

END LOOP;

CLOSE emp_cursor;

END;

3. Write a cursor for loop to retrieve employees one by one and print out a list of those employees
currently working in the sales department (DEPARTMENT_ID = 80).

DECLARE

CURSOR emp_cursor IS

SELECT last_name, department_id FROM employees;

BEGIN

FOR emp_record IN emp_cursor LOOP

--implicit open and implicit fetch occur

IF emp_record.department_id = 80 THEN

DBMS_OUTPUT.PUT_LINE ('Employee ' || emp_record.last_name || ' works in the Sales Dept. ');

END IF;

END LOOP; --implicit close and implicit loop exit

END;

4. Write a PL/SQL block to select the name of the employee with a given salary value. Use a variable
to provide the salary.

a. If the salary entered returns more than one row, handle the exception with an appropriate
exception handler and insert into the messages table, The message "More than one employee with a
salary of <salary>.

b. If the salary entered does not return any rows, handle the exception with an appropriate
exceptional handler and insert into the messages table. the message

"No employee with a salary of <salary>.

c. If the salary entered returns only one row, insert into the messages table, the employee name and
the salary amount.

d. Handle any other exception handler with an appropriate exception handler and insert into the
messages table, the message "Some other error occurred."

create table messages(results varchar2(50));

DECLARE

v_ename employees.last_name%TYPE;

v_sal employees.salary%TYPE:= 6000;

BEGIN

SELECT last_name INTO v_ename FROM employees WHERE salary=v_sal;

INSERT INTO messages(results) VALUES (v_ename||' - ' ||v_sal);

EXCEPTION

WHEN no_data_found THEN

INSERT INTO messages(results)

VALUES('No employee with the salary of' || TO_CHAR(v_sal));

WHEN too_many_rows THEN

INSERT INTO messages(results)

VALUES('More than one employee with the salary of' || TO_CHAR(v_sal));

WHEN others THEN

INSERT INTO messages(results)

VALUES('Some other error occurred');

END;

5. Write a PL/SQL block to update the departments table to a given location for a given dept_id.
Write a user defined exception to pass a message to the user that the specified department_id does
not exist. Execute the PL/SQL block by entering a department that does not exist.

DECLARE

e_invalid_dept EXCEPTION;

v_deptno departments.department_id%TYPE := 60;

v_loc locations.location_id%TYPE := 1400;

BEGIN

UPDATE departments SET location_id=v_loc WHERE

department_id=v_deptno;

IF SQL%NOTFOUND THEN

raise e_invalid_dept;

ELSE DBMS_OUTPUT.PUT_LINE('Updated Successfully');

END IF;

EXCEPTION

WHEN e_invalid_dept THEN

DBMS_OUTPUT.PUT_LINE (' Department ' || TO_CHAR(v_deptno)|| ' is an invalid department ');

END;

PRACTICAL 9 (PROCEDURES, FUNCTIONS AND TRIGGERS)

1. Create the table, top_emp with two field names name and salary. Create a PL/SQL block using a
cursor to determine the top 'n' employees with respect to their salaries.

CREATE OR REPLACE PROCEDURE add_job

(p_jobid IN job_emp.job_id%TYPE, p_jobtitle IN job_emp.job_title%TYPE)

IS

BEGIN

INSERT INTO jobs(job_id,job_title) VALUES(p_jobid,p_jobtitle);

END add_job;

EXECUTE add_job('IT_DBA','Database Administrator');

2. Create a PL/SQL block using a cursor to do the following:-

a) Use a variable to provide the department_id.

b) In a PL/SQL block, retrieve the last_name,salary and manager_id of the employees working in that
department.
c) If the salary is less than 5000 and if the manager id is either 101 or 124, display the message,
display the message <<last_name>> due for a raise. Otherwise display the message<<last_name>>
not due for a raise.

d) Test the PL/SQL block for the following departments 10,50,60,80.

CREATE OR REPLACE PROCEDURE upd_job

(p_jobid IN job_emp.job_id%TYPE, p_jobtitle IN job_emp.job_title%TYPE)

IS

BEGIN

UPDATE job_emp SET job_title=p_jobtitle WHERE job_id=p_jobid;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20202,'no job updated');

END IF;

END upd_job;

EXECUTE upd_job('IT_DBA','DBMS Administrator');

3. Write a cursor for loop to retrieve employees one by one and print out a list of those employees
currently working in the sales department (DEPARTMENT_ID = 80).

CREATE AND REPLACE PROCEDURE del_job

(p_jobid IN jobs.job_id%TYPE)

IS

BEGIN

DELETE FROM jobs WHERE job_id=p_jobid;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR(-20202,'no job deleted');

END IF;

END del_job;

EXECUTE del_job('IT_DBA');
4. Write a PL/SQL block to select the name of the employee with a given salary value. Use a variable
to provide the salary.

a. If the salary entered returns more than one row, handle the exception with an appropriate
exception handler and insert into the messages table, The message "More than one employee with a
salary of <salary>.

b. If the salary entered does not return any rows, handle the exception with an appropriate
exceptional handler and insert into the messages table. the message "No employee with a salary of
<salary>.

c. If the salary entered returns only one row, insert into the messages table, the employee name and
the salary amount.

d. Handle any other exception handler with an appropriate exception handler and insert into the
messages table, the message "Some other error occurred."

CREATE OR REPLACE PROCEDURE query_emp

(p_empid IN employees.employee_id%TYPE,

p_sal OUT employees.salary%TYPE,

p_job OUT employees.job_id%TYPE)

IS

BEGIN

SELECT salary ,job_id INTO p_sal,p_job FROM employees

WHERE employee_id=p_empid;

END query_emp;

EXECUTE query_emp(100);

5. Write a PL/SQL block to update the departments table to a given location for a given dept_id.
Write a user defined exception to pass a message to the user that the specified department_id does
not exist. Execute the PL/SQL block by entering a department

CREATE OR REPLACE FUNCTION q_job

(p_jobid IN job_emp.job_id%TYPE)

RETURN VARCHAR2

IS

v_jobtitle job_emp.job_title%TYPE;

BEGIN
SELECT job_title INTO v_jobtitle FROM job_emp WHERE job_id=p_jobid;

RETURN (v_jobtitle);

END ;

SELECT q_job(‘SA_REP’) FROM job_emp;

6. Create and invoke a function annual_comp to return the annual salary by accepting two
parameters and employees monthly salary and commission. The function should address NULL
value.

Use the function in a SELECT statement against the employees table for department no 80.

CREATE OR REPLACE FUNCTION annual_comp

(p_sal IN employees.salary%TYPE,

p_comm IN employees.commission_pct%TYPE)

RETURN NUMBER

IS

BEGIN

RETURN (NVL(p_sal,0)*12 + (NVL(p_comm,0)*p_sal*12));

END annual_comp;

SELECT employee_id,last_name,annual_comp(salary,commission_pct) “Annual Compensation”

FROM employees WHERE department_id=80;

7. Create a trigger to restrict the data manipulation events on the employee table in business hours
from monday to friday.

CREATE OR REPLACE TRIGGER secure_emp

BEFORE INSERT OR UPDATE OR DELETE ON employees

BEGIN

IF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN')) OR

(TO_CHAR(SYSDATE,'HH24') NOT BETWEEN '08' AND '18')

THEN
IF DELETING THEN

RAISE_APPLICATION_ERROR(-20503, 'You may delete only during business hours');

ELSIF INSERTING THEN

RAISE_APPLICATION_ERROR(-20503, 'You may insert only during business hours');

ELSIF UPDATING ('salary') THEN

RAISE_APPLICATION_ERROR(-20503, 'You may update salary only during business

hours');

ELSE

RAISE_APPLICATION_ERROR(-20503, 'You may update only during business hours');

END IF;

END IF;

END;

8. Create a trigger to allow only certain employees (AD_PRES,AD_VP) to be able to earn a salary of
more than 50,000.

CREATE OR REPLACE TRIGGER restrict_salary

BEFORE INSERT OR UPDATE OF salary ON employees

FOR EACH ROW

BEGIN

IF NOT(:NEW.job_id IN ('AD_PRES','AD_VP')) AND :NEW.salary> 50000

THEN

RAISE_APPLICATION_ERROR(-20922,’Employee cannot earn this amount’);

END IF;

END;

Update employees set salary = 60000 where job_id = 'ST_CLERK';

--Error:-

--ORA-20922: Employee cannot earn this amount ORA-06512: at

"SQL_JFQYWIQLCMRKXSZSMMSKICKXZ.RESTRICT_SALARY", line 4 ORA-06512: at "SYS.DBMS_SQL",
line 1721
9.Write a trigger to check that salaries entered only increase and don’t decrease

CREATE OR REPLACE TRIGGER check_sal

BEFORE UPDATE OF salary ON employees

FOR EACH ROW

WHEN(NEW.salary<OLD.salary)

BEGIN

raise_application_error(-20002,’Salary must not be reduced’);

END check_sal; //End; is also fine

Update employees set salary = salary - 1000 where employee_id = 143;