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;
