RDBMS_slip
RDBMS Practical Slips Solution
Slip 1
Q3. Consider the following entities and their relationships. [40]
Client (client_no, client_name, address, birthdate)
Policy_info (policy_no, desc, maturity_amt, prem_amt, date)
Relation between Client and Policy_info is Many to Many
Constraint: Primary key, prem_amt and maturity_amt should be >
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Client (c_no, c_name, c_addr, birth_date)
Policy_info (p_no, p_name, maturity_amt, prem_amt, policy_dt)
Policy_info (p_no, p_name, maturity_amt, prem_amt, policy_dt)
cp_info(c_no,p_no)
1) Write a function which will return total maturity amount of policies of a particular client.
create or replace function f1(cno IN number)
return number is
tot number(15);
begin
select sum(policy_info maturity_amt) into tot
from policy_info,cp_info,client
where policy_info.p_no=cp_info.p_no and client.c_no= cp_info.c_no and client.c_no=cno ;
return tot;
end;
return number is
tot number(15);
begin
select sum(policy_info maturity_amt) into tot
from policy_info,cp_info,client
where policy_info.p_no=cp_info.p_no and client.c_no= cp_info.c_no and client.c_no=cno ;
return tot;
end;
calling function
begin
dbms_output.put_line(‘total maturity amt of policies of given
client’||f1(2));
end;
2) Write a cursor which will display policy date wise client details.
Declare
cursor c1 (dt IN varchar) is
select c_no, c_name, c_addr, birth_date
from client,policy,clientp
where client.c_no=clientp.c_no
and policy.p_no=clientp.p_no
and policy_dt=dt;
r1 c1%rowtype;
Begin
open c1(‘&dt’);
loop
fetch c1 into r1;
exit when c1%notfound;
dbms_output.put_line(r1.c_name||’ ’||r1.c_addr||’ ’||r1.birth_date);
end loop;
close c1;
end;
cursor c1 (dt IN varchar) is
select c_no, c_name, c_addr, birth_date
from client,policy,clientp
where client.c_no=clientp.c_no
and policy.p_no=clientp.p_no
and policy_dt=dt;
r1 c1%rowtype;
Begin
open c1(‘&dt’);
loop
fetch c1 into r1;
exit when c1%notfound;
dbms_output.put_line(r1.c_name||’ ’||r1.c_addr||’ ’||r1.birth_date);
end loop;
close c1;
end;
-------------------------------------------------------------------------------------------
Slip 2
Q3. Consider the following Item_Supplier database [40]
Item (itemno, itemname )
Supplier (supplier_No , supplier_name, address, city )
Relationship between Item and Supplier is many-to-many with descriptive attribute
rate and quantity
Constraints: itemno ,supplier_No primary key
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write function to print the total number of suppliers of a particular item
create or replace function f1(itno IN number) return number as xyz number;
begin
select count(supplier.sno) into xyz from item,supplier,is1 where item.ino=is1.ino and supplier.sno=is1.sno and item.ino=itno;
return (xyz);
end f1;
begin
dbms_output.put_line (' total no of supplier for item=' ||f1(1));
end;
2) Write a trigger which will fire before insert or update on rate and quantity less than or
equal to zero. (Raise user defined exception and give appropriate message)
create or replace trigger t3
before insert or update on is1
for each row
Begin
if(:new.rate<=0 or:new.quantity<=0) then
raise_application_error(-20001,'rate and qunatity should be > zero');
End if;
End;
calling trigger
insert into is1 values(1,101,0,1);
-----------------------------------------------------------------------------------------
Slip 3
Q3. Consider the following entities and their relationship. [40]
Newspaper (name,language , publisher , cost )
Cities (pincode , city, state)
Relationship between Newspaper and Cities is many-to-many with descriptive attribute daily required
Constraints: name and pincode primary key
create table newspaper (name char(20) primary key,language char(20) , publisher varchar(20) , cost number(5) );
create table cities (pincode number(20) primary key , city char(20), state char(20));
create table nc (name char(20) references newspaper(name), pincode number(20) references cities(pincode), dailyr number(20) );
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a trigger which will fire before insert on the cities table which check that the pincode must be of 6 digit. (Raise user defined exception and give appropriate message).
create or replace trigger t8
before insert on cities
for each row
Begin
if(length(:new.pincode) !=6) then
raise_application_error(-20001,'pincode length should be 6');
End if;
End;
before insert on cities
for each row
Begin
if(length(:new.pincode) !=6) then
raise_application_error(-20001,'pincode length should be 6');
End if;
End;
calling trigger
insert into cities values(1125,'pune','mh');
2) Write a procedure to calculate city wise total cost of each newspaper
Write a procedure to calculate city wise total cost of each newspaper
CREATE OR REPLACE Procedure tc(ecity IN char)
IS
tot number;
BEGIN
select sum(cost) into tot from newspaper,cities,nc where newspaper.name=nc.name and cities.pincode=nc.pincode and city=ecity;
dbms_output.put_line('citiwise total cost of newspapaper='||tot);
END;
Calling procedure
begin
tc('pune');
end;
-----------------------------------------------------------------------------------------
Slip4
Q3 Consider the following entities and their relationships. [40]
Client (client_no, client_name, address, birthdate)
Policy_info (policy_no, desc, maturity_amt, prem_amt, date)
create table client(cno int primary key,cname varchar(10),addr varchar(15),bdate varchar(15));
create table policy(pno int primary key,disc varchar(10),mamt int,pamt int,pdate varchar(15));
create table cp(cno int references client(cno),pno int references policy(pno));
Relation between Client and Policy_info is Many to Many
Constraint: Primary key, prem_amt and maturity_amt should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a procedure which will display all policy details having premium amount less than 5000.
create or replace procedure p4 as cursor c4 is select pamt,pdate from client,policy,cp where client.cno=cp.cno and policy.pno=cp.pno and pamt< 5000;
c c4 %rowtype;
begin
open c4;
loop
fetch c4 into c;
exit when c4%notfound;
dbms_output.put_line(c.pamt||' '||c.pdate);
end loop;
close c4;
end;
Calling procedure
begin
p4();
end;
2) Write a trigger which will fire before insert or update on policy_info having maturity amount less than premium amount. (Raise user defined exception and give appropriate
message)
create or replace trigger t5
before insert or update on policy
for each row
Begin
if(:new.mamt < :new.pamt) then
raise_application_error(-20001,'mamt should > pamt');
End if;
End;
calling trigger
insert into policy values(2,'lic','20000','30000','10/5/2020');
Slip 5
Q3 Consider the following entities and their relationships. [40]
Library(Lno, Lname, Location, Librarian, no_of_books)
Book(Bid, Bname, Author_Name, Price, publication)
Relation between Library and Book is one to many.
Constraint: Primary key, Price should not be null.
create table library(l_no number(3) primary key,lname varchar(20),location varchar(20),librarian varchar(20),no_of_book number(3) );
create table books(b_id number(3) primary key,b_name varchar(20),author_name varchar(20),price number(3),publication varchar(20),l_no number(3) references library(l_no));
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a function which will accept publication name from user and display total price of
books of that publication.
create or replace function funl(pn in varchar) return number as pm number;
begin
select sum(books.price) into pm from library,books where library.l_no=books.l_no and publication='vision';
if sql %found then return(pm);
else return null;
end if;
end;
calling function
begin
dbms_output.put_line('price='||funl('vision'));
end;
2) Write a cursor which will display library wise book details.(Use Parameterized Cursor)
Declare
cursor c1 (lib IN varchar) is
select b_name ,author_name ,price ,publication
from library,books
where library.l_no=books.l_no
and lname=lib;
r1 c1%rowtype;
Begin
open c1(:lib);
loop
fetch c1 into r1;
exit when c1%notfound;
dbms_output.put_line(r1.b_name||' '||r1.author_name ||' '||r1.price ||' '||r1.publication );
end loop;
close c1;
end;
Slip 6
Consider the following entities and their relationships.
Employee (emp_id, emp_name, address)
Investment (inv_no, inv_name, inv_date, inv_amount)
Relation between Employee and Investment is One to Many.
Constraint: Primary key, inv_amount should be > 0.
SQL>create table employee(eid int primary key,ename varchar(10),address varchar(10));
SQL>create table investment(inv_no int primary key, inv_name varchar(10),inv_date
varchar(10),inv_amount int,eid int);
SQL>insert intoemployee values(1,'reshma','koregoan');
SQL>insert into investment values(1,'house','15thaug','50000',1);
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1)Write a procedure which will display details of employees invested amount in “Mutual e which will display details of employees invested amount in “Mutual
Fund”
SQL> set serveroutput on;
SQL> create or replace procedure p1(n in varchar) as cursor c1 is select e_name,inv_amount
from employee,investment where employee.e_id=investment.e_id and inv_amount=50000;
2 c c1 %rowtype;
3 begin
4 open c1;
5 dbms_output.put_line('e_name'||''||'inv_amount');
6 loop
7 fetch c1 into c;
8 exit when c1 %notfound;
9 if(c.inv_amount=n) then
10 dbms_output.put_line(c.e_name||''||c.inv_amount);
11 end if;
12 end loop;
13 close c1;
14 end;
15 /
Procedure created.
SQL> begin
2 p1('50000');
3 end;
4 /
e_nameinv_amount
reshma50000
2)Write a cursor which will display date wise investment details
SQL> set serveroutput on;
SQL> declare
2 cursor c1 is select inv_date,inv_no,inv_name,inv_amount from employee,investment
where employee.e_id=investment.e_id;
3 c c1 %rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into c;
8 exit when c1 %notfound;
9 dbms_output.put_line(c.inv_date||''||c.inv_no||''||c.inv_name||''||c.inv_amount);
10 end loop;
11 close c1;
12 end;
13 /
15thaug1house50000
20thsept2land60000
25thoct3vehicle70000
Slip 7
Consider the following entities and their relationships.
Bill (billno, day, tableno, total)
Menu (dish_no, dish_desc, price)
The relationship between Bill and Menu is Many to Many with quantity as descriptive
attribute.
Constraint: Primary key, price should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
SQL>create table bill(bill_no int primary key,day varchar(10),table_no int,total int);
SQL>create table menu(dish_no int primary key,dish_description varchar(10),price int);
SQL>create table bm(bill_no int,dish_no int);
SQL>insert into bill values( 1,'monday','101','240');
SQL>insert into menu values(1,'paneer','200');
SQL>insert into bm values(1,1);
1)Write a pr rite a procedure to display menu e to display menu details having price between 200 to 500 which wer details having price between 200 to 500 which were
order on ‘Saturday’ .
SQL> set serveroutput on;
SQL> create or replace procedure p1(a in varchar) as cursor c1 is select
menu.dish_no,menu.price,bil
l.day from bill,menu,bm where bill.bill_no=bm.bill_no and menu.dish_no=bm.dish_no and
price between
200 and 500 and day='saturday';
2 c c1 %rowtype;
3 begin
4 open c1;
5 dbms_output.put_line('dish_no'||''||'price'||''||'day');
6 loop
7 fetch c1 into c;
8 exit when c1 %notfound;
9 if(c.day=a)then
10 dbms_output.put_line(c.dish_no||''||c.price||''||c.day);
11 end if;
12 end loop;
13 close c1;
14 end;
15 /
Procedure created.
SQL> begin
2 p1('saturday');
3 end;
4 /
dish_nopriceday
3400saturday
2)Write a trigger which will fir rite a trigger which will fire before insert or update on Menu having price less than e insert or update on Menu having price less than
or equal to zero. (Raise user defined exception and give appropriate message)
SQL> set serveroutput on;
SQL> create or replace trigger t1 before insert or update on menu
2 for each row
3 begin
4 if(:new.price<=0)then
5 raise_application_error(-20001,'price>0');
6 end if;
7 end;
8 /
Trigger created
SQL> insert into menu values(6,'rice',0);
insert into menu values(6,'rice',0)
*
ERROR at line 1:
ORA-20001: price>0
ORA-06512: at "SCOTT.T1", line 3
ORA-04088: error during execution of trigger 'SCOTT.T
Slip 8
Consider the following entities and their relationships.
Plan (plan_no, plan_name, nooffreecalls, freecalltime, fix_amt)
Customer (cust_no, cust_name, mobile_no)
Relation between Plan and Customer is One to Many. Constraint: Primary key, fix_amt should
be greater than 0.
SQL>Create table plan(pno int primary key,pname varchar(15),nc varchar(15), fct
varchar(15),f_amt varchar(15));
SQL>Insert into plan values(1,’summer’,’10’,’10min’,’500’);
SQL>Create table cust(cno int primary key,cname varchar(15),mno int,pno int);
SQL>Insert into cust values(1,’swarup’,9763162617,1);
1)Write a function which will accept plan number from user and display all the details
of the selected plan
SQL>set serveroutput on
SQL>create or replace function fun1(nocomp in varchar)return varchar as detalis varchar(10);
2 begin
3 select ( plan.pname)into detalis from plan,cust where plan.pno=cust.pno and plan.pno='1';
4 if sql %found then
5 return(detalis);
6 else
7 return null;
8 end if;
9 end fun1;
10 /
Function created.
SQL>begin
2 dbms_output.put_line('detalis-'||fun1('1'));
3 end;
4 /
2)Write a cursor which will display customer wise plan details.(Use Parameterized
Cursor)
SQL> set serveroutput on
SQL> declare
2 cursor c1(yyyy cust.cname %type)is select cname,pname from plan,cust where
plan.pno=cust.pno order by cname;
3 c c1%rowtype;
4 begin
5 open c1('&cname ');
6 loop
7 fetch c1 into c;
8 exit when c1%notfound;
9 dbms_output.put_line(c.cname||''||c.pname);
10 end loop;
11 close c1;
12 end;
13
14 /
Enter value for cname: anita;
old 5: open c1('&cname ');
new 5: open c1('anita; ');
anitadiwali
manoharwinter
swarupsummer
Slip9
Consider the following entities and their relationships.
Project (pno, pname, start_date, budget, status)
Department (dno, dname, HOD, loc)
The relationship between Project and Department is Many to One.
Constraint: Primary key.
Project Status Constraints:C – Completed,P - Progressive, I – Incomplete
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
SQL> create table department(dno int primary key,dname varchar(20),HOD varchar(20),loc
varchar(20));
SQL> create table project(pno int primary key,pname varchar(20),s_date varchar(25),budget
varchar(15),status varchar(10),dno int);
SQL> insert into department values(1,'computer','amit','pune');
SQL> insert into project values(101,'java','10-2-2015','10,000','P',1);
1)Write a function which accept department name and display total number of projects
whose status is “p”(progressive).
SQL> set serveroutput on
SQL> create or replace function f1(xyz in varchar)return number as abc number;
2 begin
3 select count(project.pno)into abc from department,project where
department.dno=project.dno and department.dname='computer' and project.status='P';
4 if sql %found then
5 return(abc);
6 else
7 return null;
8 end if;
9 end f1;
10 /
Function created.
SQL> begin
2 dbms_output.put_line('project-'||f1('computer'));
3 end;
4 /
project-3
PL/SQL procedure successfully completed.
2)Write a cursor which will display status wise project details of each department.SQL> set serveroutput on
SQL> declare
2 cursor c1 is select cname, address,pdate from client,poly,cp where
client.cno=cp.cno and poly.pno=cp.pno order by pdate;
3 c c1%rowtype;
4 begin
5 open c1;
6 loop
7 fetch c1 into c;
8 exit when c1%notfound;
9 dbms_output.put_line(c.cname||''||c.address||''||c.pdate);
10 end loop;
11 close c1;
12 end;
13 /
Slip 10
Consider the following entities and their relationships.
Gym (Name, city, charges, scheme)
Member (ID, Name, phoneNo, address)
Relation between Gym and member is one to many Relation between Gym and member is one to many. Constraint: Primary Key Constraint: Primary Key, charges must be ges must be
greater than 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
SQL> create table gym(gno int primary key,gname varchar(20),city varchar(20),charges
int,scheme varchar(20));
SQL> create table member(mid int primary key,mname varchar(20),phone int,addr
varchar(20),gno int);
SQL> insert into gym values(101,'aaa','pune',1000,'xyz');
SQL> insert into member values(001,'mmm',1122334455,'goa',101);
1)Write a function which will accept member id and scheme from user and display
charges paid by that member.
SQL> set serveroutput on
SQL> create or replace function f3(abc in varchar) return number as xyz number;
2 begin
3 select gym.charges into xyz from gym,member where gym.gno=member.gno and mid='2';
4 if sql %found then
5 return(xyz);
6 else
7 return null;
8 end if;
9 end f3;
10 /
Function created.
SQL> begin
2 dbms_output.put_line('gym-'||f3 ('2'));
3 end;
4 /
gym-2000
PL/SQL procedure successfully completed.
2)Write a trigger which will fir a trigger which will fire befor e before insert or update on Gym having charges less e insert or update on Gym having charges less
SQL> set serveroutput on
SQL> create or replace trigger t1 before insert or update on gym
2 for each row
3 begin
4 if(:new.charges<=1000)then
5 raise_application_error(-20001,'charges>1000');
6 end if;
7 end;
8 /
Trigger created
SQL> insert into gym values(104,'ddd','pune',-1000,'mmm');
insert into gym values(104,'ddd','pune',-1000,'mmm')
* *
ERROR at line 1:
ORA-20001: charges>0
ORA-06512: at "SCOTT.T1", line 3
ORA-04088: error during execution of trigger 'SCOTT.T1
than 1000. (Raise user defined exception and give appropriate message)
0 Comments