DMS

Practical 15:- Implement triggers for given database
Create a trigger which invokes on updation of record in Department table
create table t1(id number,name varchar(20));

create trigger auto
after update
on t1
for each row
begin
dbms_output.put_line('Trigger updated');
end;
/
insert into t1 values(1,'Prerana'),(2,'Sukanya'),(3,'Shrawani');
select * from t1;
update t1 set name='Rajnandini' where id=3;

select * from t1;
-------------------------------------------------------------------
Practical 12:- Implement PL/SQL program using Sequential Control
Write a PL/SQL program that prints numbers from 1 to 10, but skips printing the
number 5 [Use
GOTO statement]
declare
i number:=1;
begin
loop
if i=5 then
i:=i+1;
goto skip;
end if;
dbms_output.put_line(i);
i:=i+1;
<<skip>>
EXIT when i>10;
END loop;
end;
/
------------------------------------------
Practical 13:- Implement PL/SQL program based on Exception Handling (Pre -defined
exceptions)
application.
Write a PL/SQL program that asks the user to input two numbers and divide the first
number by
the second. Handle the predefined exception for division by zero and display an
appropriate message if
it occurs.
declare
a number:=10;
b number :=0;
c number;
begin
c:=a/b;
dbms_output.put_line('Division: '||c);
exception
when ZERO_DIVIDE then
dbms_output.put_line('Error: Can not divide by zero');
end;
/
----------------------------------------------
Practical 14:- Implement PL/SQL program based on Exception Handling (User -defined
exceptions).
Write a PL/SQL program that asks for customer Id, when user enters invalid Id, the
exception Invalid-
Id is raised.
declare
id number:=-1;
invalid_id exception;
begin
if id<0 then
raise invalid_id;
else
dbms_output.put_line('Login');
end if;
exception
when invalid_id then
dbms_output.put_line('Error: Id can not be negative');
end;
/
---------------------------------------------------
Practical 16:-Create Implicit and Explicit Cursor
Implicit cursor
create table customers(id number,name varchar(20),salary number);
insert into customers values(1,'Prerana',80000),(2,'Dnyaneshwari',90000);
UPDATE customers set salary=100000 where id=1;
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 5000;
IF sql%notfound THEN
dbms_output.put_line('no customers updated');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers updated ');
END IF;
END;
/
=========================================
Explict
create table customers(id number,name varchar(20),salary number,address
varchar(20));
insert into customers values(1,'Prerana',80000,'Shirbhavi'),
(2,'Dnyaneshwari',90000,'Bardi');
DECLARE
c_id customers.id%type;
c_name customers.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
----------------------------------------------
Practical 17:-Create Function for Given database
Write a Function to calculate factorial of given no.
create function factorial(num in number)

return number is
fact number:=1;
begin
for i in 1..num loop
fact:=fact*i;
end loop;
return fact;
end;
/
DECLARE
result number;
BEGIN
result:=factorial(5);
dbms_output.put_line('Factorial :'||result);
END;
/
---------------------------------------------------
Practical 10:- Implement PL/SQL program using Conditional Statements.
Write a PL/SQL program that asks the user for their age and then prints “You can
vote" if they are
over 18, and "You cannot vote" otherwise.
declare
age int:=&age;
begin
if age>=18 then
dbms_output.put_line('You can vote');
else
dbms_output.put_line('You can not vote');
end if;
end;
/
--------------------------------------------------
Practical 11: Implement PL/SQL program using Iterative Statements.

Write a PL/SQL program to calculate factorial of 10 by using PL/SQL FOR LOOP
statement.
declare
num int:=&num;
fact int:=1;
begin
for i in 1..num loop
fact:=fact*i;
end loop;
dbms_output.put_line('Factorail: '||fact);
end;
/
------------------------------------------------------
Practical 1:- Execute DDL commands to manage Database using SQL.
1. Create a table for stud using attributes Rollno, Studname, Percentage apply
primary key for rollno.
2. Change the stud table structure by adding column City.
3. Increase the size by 10 of studentname column.
create table stud(rollno int primary key,name varchar(20),salary int);

desc stud;
alter table stud add column city varchar(20);
desc stud;
alter table stud modify column name varchar(20);
desc stud;
------------------------------------------------
Practical 2:- Execute DML Commands to manipulate data using SQL.
1.Create table EMP with attributes EMPNO, ENAME, DNAME, JOB.
2.Insert any 4 records.
3.Change the job of any employee to Manager.
4.Display all records.
create table emp(empno int,empname varchar(20),dept varchar(20),job varchar(20));

insert into emp values(1,'prerana','IF','Manager'),(2,'Sukanya','CO','CEO'),
(3,'Sayali','IF','Accountant'),(4,'Pooja','EJ','CEO');
select * from emp;
update emp set job='manager' where empno=4;
select * from emp;
-------------------------------------------------------
Practical 9:- Create and manage Views for faster access to relations.
1. Write query of the following :
i. Create view emp_view with attribute emp_no, ename, salary on EMP table.
ii. Update view set ename to Jay having emp_no 101.
iii. Delete the record of the employee where emp_no is 102.
iv. Drop created view.
create table emp(empno int,empname varchar(20),dept varchar(20),job varchar(20));

insert into emp values(101,'prerana','IF','Manager'),(102,'Sukanya','CO','CEO'),
(103,'Sayali','IF','Accountant'),(104,'Pooja','EJ','CEO');
select * from emp;
create view emp_view as select empno,empname,dept,job from emp ;

select * from emp_view;
update emp_view set empname='jay' where empno=101;
select * from emp_view;

DMS

Uploaded by

Copyright:

Available Formats

DMS

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

DMS

Uploaded by

Copyright:

Available Formats

Practical 15:- Implement triggers for given database

Create a trigger which invokes on updation of record in Department table

create table t1(id number,name varchar(20));

update t1 set name='Rajnandini' where id=3;

Practical 12:- Implement PL/SQL program using Sequential Control

EXIT when i>10;

create function factorial(num in number)

Practical 11: Implement PL/SQL program using Iterative Statements.

create table stud(rollno int primary key,name varchar(20),salary int);

create table emp(empno int,empname varchar(20),dept varchar(20),job varchar(20));

create table emp(empno int,empname varchar(20),dept varchar(20),job varchar(20));

create view emp_view as select empno,empname,dept,job from emp ;

You might also like