This document contains 10 practical assignments on PL/SQL basics, control structures, conditional statements, sequences, procedures, functions, triggers, exceptions, and packages. Each practical contains multiple questions demonstrating the relevant PL/SQL concept through code examples like creating blocks to display messages, accept user input, perform calculations, create tables and sequences, write procedures and functions, handle exceptions, and create packages. The questions progress from basic concepts to more advanced topics like control flow, DML operations, and modularization.
This document contains 10 practical assignments on PL/SQL basics, control structures, conditional statements, sequences, procedures, functions, triggers, exceptions, and packages. Each practical contains multiple questions demonstrating the relevant PL/SQL concept through code examples like creating blocks to display messages, accept user input, perform calculations, create tables and sequences, write procedures and functions, handle exceptions, and create packages. The questions progress from basic concepts to more advanced topics like control flow, DML operations, and modularization.
This document contains 10 practical assignments on PL/SQL basics, control structures, conditional statements, sequences, procedures, functions, triggers, exceptions, and packages. Each practical contains multiple questions demonstrating the relevant PL/SQL concept through code examples like creating blocks to display messages, accept user input, perform calculations, create tables and sequences, write procedures and functions, handle exceptions, and create packages. The questions progress from basic concepts to more advanced topics like control flow, DML operations, and modularization.
This document contains 10 practical assignments on PL/SQL basics, control structures, conditional statements, sequences, procedures, functions, triggers, exceptions, and packages. Each practical contains multiple questions demonstrating the relevant PL/SQL concept through code examples like creating blocks to display messages, accept user input, perform calculations, create tables and sequences, write procedures and functions, handle exceptions, and create packages. The questions progress from basic concepts to more advanced topics like control flow, DML operations, and modularization.
Q1 Write a PL/SQL block to display the message welcome
Begin Dbms_output.put_line(‘welcome to PL/SQL’); End; / Q2 Write a PL/SQL block which will record number from the user and display it on the screen declare x int:=25; begin dbms_output.put_line(‘your no is:’||x); end; / Q3 Write a PL/SQL block to read a message from user and display it Declare x varchar(10); Begin x:=’&n’; dbms_output.put_line(‘your message is’||x); End; / Q4 Write a PL/SQL block to display area of rectangle where length and breadth are accepted from the user Declare l float; b float; Begin l:=&l; b:=&b; dbms_output.put_line(‘area of rectangle is:’||(l*b)); end; / Q5 Write a PL/SQL block to display total number of employees declare n int; begin select count(*) into n from emp_2223; dbms_output.put_line(‘no of employees is:’||n); end; / Q6 Write a PL/SQL block to print the name and job of an employees who is working as a clerk and earning salary of 1300 Declare en name.ename%type; j emp.job%type; begin select ename,job,int en,j from emp where job=’Clerk ad sal=1300; dbms_output.put_line(‘employee name is:’||en||’his job is’||j); end; / Q7 Write a PL/SQL block to print sum of 2 number accepted from the user Declare A int; P int; Begin A:=&A; P:=&P; Dbms_output.put_line(‘sum is :’||(A+P)); End; / Q8 Write a PL/SQL block to print the message ‘you can lead a horse to water but you can’t make him drink’ Begin Dbms_output.put_line(‘you can lead a horse to water but you can’t make him drink’); End; / Q9 Write a PL/SQL block to calculate the area of circle and store the radius and area in a table AOC(area of circle) Create table AOC ( radius float,area float); Declare r AOC.radius%type; a AOC.radius%type; begin a:=&r; a:=3.14*r*r; insert into AOC values(r,a); end; / PRACTICAL NO-2 CONTROL STRUCTURE IN PL/SQL: Q1 Write a PL/SQL block to display sum of first 10 integer declare i int; s int; begin s:=0; for i in 1..10 loop s:=s+i; end loop; dbms_output.put_line('sum of 10 numbers is:'||s); end; / Q2 write a PL/SQL block to display sum of first 10 odd number declare i int; s int; begin s:=0; i:=1; while i<20 loop s:=s+i; i:=i+2; end loop; dbms_output.put_line('sum of 10 odd numbers is:'||s); end; / Q3 write a PL/SQL block to find factorial of number declare n int; i int; f int; begin n:=&n; i:=1; f:=1; while i<=n loop f:=f*i; i:=i+1; end loop; dbms_output.put_line('factorial of'||n||'is:'||f); end; / Q4 write a PL/SQL block to display fibonacci series declare a int; b int; c int; i int; begin a:=0; b:=1; dbms_output.put_line(a); dbms_output.put_line(b); for i in 1..5 loop c:=a+b; dbms_output.put_line(c); a:=b; b:=c; end loop; end; / Q5 write a PL/SQL to calculate area of circle insert area and radius of aoc table till radius is less than 10 Create table aoc_78(radius float,area float) declare r aoc_78.radius%type; a aoc_78.area%type; begin for r in 1..9 loop a:=3.14*r*r; insert into aoc_78 values(r,a); end loop; end; / select*from aoc_78; PRACTICAL NO-3 CONDITIONAL STATEMENT USING PL/SQL Q1 count number of employess in department 10 print the count if it is greater than 3 declare counter int; begin select count(*)into counter from emp_2223; where deptno=10; if counter>3 then dbms_output.put_line('no of employees in deptno 10 :'||counter); else dbms_output.put_line('less employees in deptno 10 :'); end if; end; / Q2 Program to read the salary of employees with emp_no- 104 and display an appropriate message if the salary range is between 1000 and 5000 Declare Salary int; Begin Select sal into salary from emp_2223 Where empno=104; If salary>=1000 and salary<=5000 then Dbms_output.put_line(‘salary lies in range from 1000-5000’); Else Dbms_output.put_line(‘salary does not lie in range from 1000-5000’); End if; End; / Q3 Write a PL/SQL program to find number is even or odd accept the value from user Declare n int; begin n:=&n; if mod(n,2)=0 then dbms_output.put_line(‘number is even’); else dbms_output.put_line(‘number is odd’); end if; end; / Q4 Write a PL/SQL program accept an alphabet whether it is vowel or consonant Declare ap char(1)1;=’&a’; begin case ap when ‘a’ then dbms_output.put_line(‘vowel’); when ‘e’ then dbms_output.put_line(‘vowel’); when ‘i’ then dbms_output.put_line(‘vowel’); when ‘o’ then dbms_output.put_line(‘vowel’); when ‘u’ then dbms_output.put_line(‘vowel’); else dbms_output.put_line(‘consonant’); end case; end; / Q5 Write a PL/SQL program to calculate student percentage by considering marks obtain in 2 subject outoff 100 Declare r int:=&r; m1 int:=&m1; m2 int:=&m2; grade char(1); percent float; begin percent:=[(m1+m2)/200]*100; if percent>70 then grade:=’A’; elsif percent>60 and percent<=70 then grade:=’B’; else grade:=’c’; end if; insert into marks_2023 values (r,m1,m2,grade); end; / PRACTICAL NO:4 CREATION OF SEQUENCE Q1 Write a PL/SQL block to create a sequence by using cycle alter the sequence and insert values in table Create sequence s Maxvalue 10 cycle cache 6; alter sequence s increment by 3 maxvalue 10 cycle cache 3 order; Q2 Write a PL/SQL block to create a sequence from 10 to 100 and bind it with table product having two columns product no and product name(10,20,30) Create table product (product_no int,product_name varchar(10)); Create sequence seq1 Start with 10 Increment by 10 Maxvalue 100 Order; Q3 Write a PL/SQL block to create a sequence with maxvalue as 40 increment by 4 starting from 1 forming a cycle Create sequence seq2 Start with 1 Increment by 4 Maxvalue 40 Cycle Order; PRACTICAL NO:6 CREAETION OF PROCEDURES IN PL/SQL Q1 Create a procedure to display square of number create or replace procedure proc1(a in int, b out int) as begin b:=a*a; end; / Declare x int; y int; begin x :=&x; proc1(x,y); dbms_output.put_line(‘square of no is’||y); end; / Q2 Create a procedure to display greatest among two numbers create or replace procedure proc1(a in int, b in int) as begin if (a>b) then dbms_output.put_line(a||’is greater than’||b); elsif (a<b) then dbms_output.put_line(b||’is greater than’||a); else dbms_output.put_line(‘both are equal’); end if; end; / Declare x int; y int; begin x:=&x; y:=&y; proc1(x,y); end; / Q3 Create a procedure to display swapping of two numbers create or replace procedure proc1(a in out int, b in out int) as c int; begin c:=a a:=b; b:=c; end; / Declare x int; y int; begin x:=&x; y:=&y; dbms_output.put_line(‘before swap:’||x||’ ‘||y); proc1(x,y); dbms_output.put_line(‘after swap:’||x|| ‘||y); end; / Q4 Create a procedure to display sum of salary of the employees whose designation is accepted from user. create or replace procedure proc1(j in varchar) as s int; begin select sum (sal)into s from emp_2223 where designation=j; dbms_output.put_line(‘sum of salaries is:’||s); end; / Declare d varchar(15); begin d:=’&d’; proc1(d); end; / PRACTICAL NO:7 FUNCTION IN PL/SQL Q1 Create a function to swap 2 numbers create or replace procedure fun2 return varchar as a int:=10; b int:=15; c int; begin c:=a; a:=b; b:=c; return(‘a is now:’||b||’and b is now’||b); end; / Q2 Create a function to display greatest among two numbers create or replace procedure fun3 return varchar as a int:=10; b int:=15; begin if(a>b) then return(a||’is greatest’); elsif(b>a) then return(b||’b is greatest’); else return(‘both are equal’); end; / Q3 Create a function two display minimum of two numbers Create or replace function fmin(x int,y int) Return int; As Begin If(x<y) then return x; else return y; end if; end; / Declare a int:=&a; b int:=&b; c int; begin c:=fmin(a,b); dbms_output.put_line(‘minimum is:’||c); end; / PRACTICAL NO:8 CREATION OF TRIGGER Q1 create trigger to insert new of the employee in upper case in a table Select*from emp_2223; Create or replace trigger trig_upper Before insert On emp_2223 For each row Begin :NEW.ename:=upper(:NEW.ename); End; / insert into emp_2223 values(102, ’utsav’,1580,30,’manager’); Q2 create a trigger to update the salary of employee and show the difference between old salary and new salary Create or replce trigger trig_diff After update On emp_2223 For each row Begin Dbms_output.put_line(‘difference is:’||(:NEW.sal-:OLD.sal)); End; / Q3 create a trigger to delete a nos of employee and insert the deleted record in another table Create or replce trigger trig_up Before delete On emp_2223 For each row Begin Insert into del_emp2223 values (:OLD.empno, :OLD.ename, :OLD.sal, :OLD.deptno, :OLD.designation) End; / Delete from emp _2223 where empno_107; PRACTICAL NO:9 HANDLING EXCEPTIONS Q1 Demostrate two_many_rows exceptions Declare s int; begin select sal into s from emp_2223 where deptno=10; exception when too_many_rows then dbms_output.put_line(‘query returns more than 1 rows’); end; / Q2 Demostrate no_data_found exceptions Declare s int; begin select sal into s from emp_2223 where deptno=1101; exception when no_data_found then dbms_output.put_line(‘no such records’); end; /
Q3 Demostrate zero_divide_error exceptions
declare a int:=&a; b int:=&b; c int; begin c:=a/b; dbms_output.put_line(c); exception when zero_divide then dbms_output.put_line(‘value of b must be greater than 0’); end; / Q4 Demostrate raise_application_error exceptions Declare s_age int:=&s_age; s_age_ude ; exception begin if s.age<0 then raise s_age_ude; end if; exception when s_age_ude then raise_applicatin_error(-20001,’age cannot be less than 0’); end; / PRACTICAL NO:10 CREATING PACKAGES IN PL/SQL Q1 create a package with function and procedure to find sum od first 10 natural numbers create or replace package natural_nos as procedure sum_no(a in int); function sum_no(a int) return int; end natural_nos / Create or replace package body natural_nos As Procedure sum_no(a in int) As Begin Dbms_output.put_line((a*(a+1))/2); End; Function sum_no(a_int) return int as begin return(a*(a+1)/2); end; end natural_nos ; / Execute natural_nos sum_no(10); Select natural_nos.sum_no(10) from dual;