1.

Insert Record using PLSQL Block

Create a PL/SQL block to insert a new record into the Department table.
Fetch the maximum department id from the Department table and add 10 to it;
take this value for department id; 'TESTING' is the value for department name and
CHN-102 is the value for Location ID.

Table name : Department

Column name

Data type

Constraints

DEPARTMENT_ID

NUMBER(5)

PK

DEPARTMENT_NAME

VARCHAR2(25)

NOT NULL

LOCATION_ID

VARCHAR2(15)
Sample Output:
DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID

---------------------- ------------------------------ ------------------

XXXX TESTING CHN-102

1. Answer

DECLARE

l_department_id department.department_id%TYPE;

BEGIN

SELECT MAX (department_id) + 10 INTO l_department_id FROM department;

INSERT INTO department (department_id, department_name, location_id)
VALUES (l_department_id, 'TESTING', 'CHN-102');

END;
 2. Update Location

Create a PL/SQL block to update the location ID for an existing department, which
has location ID preceded with 'HQ' as

'HQ-BLR-101'.

Table name : Department

Column name

Data type

Constraints

DEPARTMENT_ID

NUMBER(5)

PK

DEPARTMENT_NAME

VARCHAR2(25)
NOT NULL
LOCATION_ID
VARCHAR2(15)
Sample Output:
DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID

---------------------- ------------------------------ ------------------

xxxx xxxxx HQ-BLR-101

2.Answer

BEGIN

update department

set LOCATION_ID='HQ-BLR-101'

WHERE LOCATION_ID LIKE 'HQ%';

END;

3. Area of a Circle
Write a PL/SQL block to calculate the area of a circle for the radius ranging from
3 to 7 .
Store the radius and corresponding area into the Circle table.

Circle :

Radius Number(5)
Area Number(7,2)

Assume that the circle table has been already created.

3. Answer

DECLARE

r number(5);
area number(14,2);
pi constant number (4,2):=3.14;

BEGIN

r:=3;
while r<=7
loop
area:=pi*power(r,2);
insert into areas values(r,area );
r:=r+1;
end loop;

END;

4. Insert credit - Procedure

Create a procedure named 'insert_credit' to insert the values in the credit_card

table by passing 5 inputs as parameters.

Hints: Procedure name: insert_credit Input parameter : credit_id with data type
as number,credit_card_number with data type as
varchar,credit_card_expire with data type as varchar,holder_name with data type as
varchar
and card_type with data type as varchar Table used: credit_card

4.Answer

CREATE OR REPLACE PROCEDURE insert_credit(

credit_id IN credit_card.id%TYPE,
credit_card_number IN credit_card.card_number%TYPE,
credit_card_expire IN credit_card.card_expire%TYPE,
holder_name IN credit_card.name%TYPE,
card_type IN credit_card.cc_type%TYPE) AS

BEGIN

INSERT INTO credit_card(id,card_number,card_expire,name,cc_type)

VALUES(credit_id,credit_card_number,credit_card_expire,holder_name,card_type);

END ;

5. Select city - Procedure

Create a procedure named 'select_city' which accepts one input parameter user_id of
type number
and one output parameter city_details of type varchar.
This procedure is used to display the city_details of user.
If the user is from Bangalore then display the city_details as 'User is from
Bangalore',or if the user is from Chennai then
display the city_details as 'User is from Chennai', else display the city_details
as 'User is from other cities'.

Hints: Data is case sensitive.

Procedure name: select_city
Input parameter : user_id with data type as number
Output parameter: city_details with data type as varchar.

5. Answer

CREATE or REPLACE procedure select_city(user_id number) is

city_details varchar2(50);

BEGIN

if(user_id=1) then
city_details:='User is from Bangalore';
dbms_output.put_line(city_details);
elsif(user_id=2) then
city_details:='User is from Chennai';
dbms_output.put_line(city_details);
else
city_details:='User is from other city';
dbms_output.put_line(city_details);
end if;

end select_city;

DECLARE

user_ids number;
begin
user_ids:= &user_ids;
select_city(user_ids);

END;

6. Insert a Record - Triggers

Create a PL/SQL Trigger to display the message “NEW EMPLOYEE DETAILS INSERTED”,
whenever a new record is inserted into Employee table.

Column name

Data type

Constraints

EMP_ID

NUMBER(5)

PK

EMP_NAME

VARCHAR2(25)

NOT NULL

SALARY

NUMBER(10,2)

Note: Use '/' to terminate your query before compilation and evaluation

6. Answer

CREATE or REPLACE trigger t1

after insert or update of EMP_ID,EMP_NAME,SALARY,NUMBER on employee
for each row

BEGIN

dbms_output.put_line('NEW EMPLOYEE DETAILS INSERTED');

END;

7. Package with a Procedure to update

salary

Create a PL/SQL Package with Procedure in it. Procedure will take designation and
incentive as input and
 update the employee salary by adding the incentive for the given designation.
Display the number of employee records that have got updated, e.g. ‘3 employee
record(s) are updated’.

Employee:

Column name

Data type

Constraints

EMP_ID

NUMBER(5)

PK

EMP_NAME

VARCHAR2(25)

NOT NULL

SALARY

NUMBER(10,2)

DESIGNATION

VARCHAR2(25)

EMP_ID EMP_NAME SALARY DESIGNATION

101 Mathew 45000 PROGRAMMER
102 Sam 54000 MANAGER
103 John 35000 TEAM LEAD
104 James 48000 PROGRAMMER
105 Josh 25000 TESTER

Functional Requirements:
Package name as EMP_DESIGNATION, and
Procedure signature:
EMP_DETAILS(design employee.designation%TYPE, incentive number);

7 . Answer

PACKAGE SPECIFICATION

create or replace package emp_designation as

procedure emp_details (design employee.designation%type, incentive number);

end;

PACKAGE BODY

CREATE or REPLACE package body emp_designation as

procedure emp_details (design employee.designation%type, incentive number) as

updatedEmpCount number;
pemp_id employee.emp_id%type;
pempSal employee.salary%type;
cursor c1 is select emp_id, salary from employee where designation = design;

BEGIN

open c1;
updatedEmpCount := 0;
loop
fetch c1 into pemp_id, pempSal;
exit when c1%notfound;
pempSal := pempSal + incentive;
update employee set salary = pempSal where emp_id = pemp_id;
updatedEmpCount := updatedEmpCount + 1;
end loop;
close c1;
dbms_output.put_line(updatedEmpCount || ' record(s) are updated');

END;

8. Display department names using Cursors

Create a PL/SQL block to display all the department names from the Department table
using cursors.
The department names should be displayed in ascending order.

Column name

Data type

Constraints

DEPARTMENT_ID

NUMBER(5)

PK

DEPARTMENT_NAME

VARCHAR2(25)

NOT NULL
LOCATION_ID

VARCHAR2(15)

Sample Output:
Department Names are :
ADMIN
DEVELOPMENT

8 . Answer
DECLARE

cursor c1 is select department_name from department order by department_name ASC;

pdepname department.department_name%type;

BEGIN
open c1;
loop
fetch c1 into pdepname;
exit when c1%notfound;
dbms_output.put_line(pdepname);
end loop;
close c1;

END ;

9. Procedure with Exception Handling

Create a PL/SQL Procedure to insert employee details into Employee table. Before
inserting, check whether the employee age is eligible or not. Employee age should
be 18 or greater. Values are passed as argument to the procedure.

If age valid, insert employee record into table and print the message "Age valid -
Record inserted", else print the message "Age invalid - Record not inserted" by
raising an exception.

Table: EMPLOYEE

Column name

Data type

Constraints

EMP_ID

NUMBER(5)
PK

EMP_NAME

VARCHAR2(25)

NOT NULL

AGE

NUMBER(3)

Functional Requirement:

PROCEDURE CHECK_AGE_ELIGIBILITY(

v_id IN EMPLOYEE.EMPID%TYPE,

v_name IN EMPLOYEE.EMPNAME%TYPE,

v_age IN EMPLOYEE.AGE%TYPE)

Sample Input 1 :

CHECK_AGE_ELIGIBILITY(103, 'Robert', 24 ) ;

Sample Output 1:

Age valid - Record inserted

Sample Input 2:
CHECK_AGE_ELIGIBILITY(104,'Riya', 4 );

Sample Output 2:

Age invalid - Record not inserted

9. Answer

CREATE OR REPLACE PROCEDURE CHECK_AGE_ELIGIBILITY(

v_id IN EMPLOYEE.EMPID%TYPE,
v_name IN EMPLOYEE.EMPNAME%TYPE,
v_age IN EMPLOYEE.AGE%TYPE)AS

BEGIN

INSERT INTO Employee(EMPID,EMPNAME,AGE)Values(103,'Robert',24);

select age from Employee;
IF AGE >=18
dbms_output.put_line('Age valid -Record inserted');
else
dbms_output.put_line('Age invalid -Record not inserted');
END;