FYBBA(CA) RDBMS Practical Slips Solution
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
Q3 Consider the following entities and their relationships. [40]
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.
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 Fund”
Slip 7
Q3 Consider the following entities and their relationships. [40]
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:
1) Write a procedure to display menu details having price between 200 to 500 which
were order on ‘Saturday’ .
2) Write a trigger which will fire before insert or update on Menu having price less than
or equal to zero. (Raise user defined exception and give appropriate message)
Slip 8
Q3 Consider the following entities and their relationships. [40]
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.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a function which will accept plan number from user and display all the details of the selected plan
2) Write a cursor which will display customer wise plan details.(Use Parameterized
Cursor)
Slip 9
Q3 Consider the following entities and their relationships. [40]
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:
1) Write a function which accept department name and display total number of projects
whose status is “p”(progressive).
2) Write a cursor which will display status wise project details of each department.
Slip 10
Q3 Consider the following entities and their relationships. [40]
Gym (Name, city, charges, scheme)
Member (ID, Name, phoneNo, address)
Relation between Gym and member is one to many.
Constraint: Primary Key, charges must be greater than 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a function which will accept member id and scheme from user and display charges paid
by that member.
2) Write a trigger which will fire before insert or update on Gym having charges less than
1000. (Raise user defined exception and give appropriate message)
Slip 11
Q3 Consider the following entities and their relationships. [40]
Student (rollno, sname, class, timetable)
Lab (LabNo, LabName, capacity, equipment)
Relation between Student and Lab is Many to One.
Constraint: Primary Key, capacity should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a function which will accept Lab number from user and display total number of student
allocated in that lab.
2) Write a cursor which will display lab wise student details.
Slip 12
Q3 Consider the following entities and their relationships. [40]
Wholesaler (w_no, w_name, address, city)
Product (product_no, product_name, rate)
Relation between Wholesaler and Product is Many to
Many with quantity as descriptive attribute.
Constraint: Primary key, rate should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a function which will accept wholesaler name from user and will display total
number of items supplied by him.
2) Write a trigger which will fire before insert or update on product having rate less than or
equal to zero (Raise user defined exception and give appropriate message)
Slip 13
Q3 Consider the following entities and their relationships. [40]
Country (CId, CName , no_of_states, area, location, population)
Citizen( Id, Name, mother_toung, state_name)
Relation between Country and Citizen is one to many.
Constraint: Primary key, area should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a function which will display name of the country having minimum population.
2) Write a cursor which will display county wise citizen details.
Slip 14
Q3 Consider the following entities and their relationships. [40]
College (code, college_name, address)
Teacher (teacher_id, teacher_name, Qualification, specialization, salary, Desg)
Relation between Teacher and College is Many to One.
Constraint: Primary Key, qualification should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a procedure which will accept teacher name from user and display his/her college details.
2) Write a trigger which will fire before insert or update on Teacher having salary less than or
equal to zero (Raise user defined exception and give appropriate message)
Slip 15
Q3 Consider the following entities and their relationships. [40]
Driver (driver_id, driver_name, address)
Car (license_no, model, year)
Relation between Driver and Car is Many to Many with date and time
as descriptive attribute.
Constraint: Primary key, driver_name should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a function which will display the total number of person who are using “Swift” car
2) Write a trigger which will fire before insert or update on year. If year value is more than
current year. (Raise user defined exception and give appropriate message)
Slip 16
Q3 Consider the following entities and their relationships. [40]
Game (game_name, no_of_players, coach_name)
Player (pid, pname, address, club_name)
Relation between Game and Player is Many to Many.
Constraint: Primary key, no_of_players 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 games details having number of players more than
5.
2) Write a trigger which will fire before insert or update on Game having no_of_players
less than or equal to zero. (Raise user defined exception and give appropriate message)
Slip 17
Q3. Consider the following Item_Supplier database [40]
Company (name , address , city , phone , share_value)
Person (pname ,pcity )
Relationship between Company and Person is M to M relationship with descriptive
attribute No_of_shares i
Constraints: name,pname primary key
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a trigger before insert or update on No_of_shares field should not be
zero.(Raise user defined exception and give appropriate message)
2) Write a function to display total no_of_shares of a specific person.
Slip 18
Q3. Consider the following entities and their relationship. [40]
Student (s_reg_no, s_name, s_class)
Competition (comp_no, comp_name, comp_type)
Relationship between Student and Competition is many-to-many with descriptive
attribute rank and year.
Constraints: primary key, foreign key, primary key for third table(s_reg_no, comp_no,
year),s_name and comp_name should not be null,comp_type can be sports or academic.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a function which will accept s_reg_no of student and returns total number of competition in which student has participated in a given year.
2) Write a cursor which will display year wise details of competitions.
(Use parameterized cursor)
Slip 19
Q3 Consider the following entities and their relationships. [40]
Game (game_name, no_of_players, coach_name)
Player (pid, pname, address, club_name)
Relation between Game and Player is Many to Many.
Constraint: Primary key, no_of_players should be > 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a function which will return total number of football players of “Sports Club”.
2) Write a cursor which will display club wise details of players.
Slip 20
Q3 Consider the following entities and their relationships. [40]
Driver (driver_id, driver_name, address)
Car (license_no, model, year)
Relation between Driver and Car is Many to Many with date and time
as descriptive attribute.
Constraint: Primary key, driver_name should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a procedure to display car details used on specific day.
2) Write a cursor which will display driver wise car details in the year 2018.
Slip 21
Q3 Consider the following entities and their relationships. [40]
College (code, college_name, address)
Teacher (teacher_id, teacher_name, Qualification, specialization, salary, Desg)
Relation between Teacher and College is Many to One.
Constraint: Primary Key, qualification should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a function which will accept college name from user and display total number of “Ph.D”
qualified teachers.
2) Write a cursor which will display college wise teacher details.
Slip 22
Q3 Consider the following entities and their relationships. [40]
Country (CId, CName , no_of_states, area, location, population)
Citizen( Id, Name, mother_toung, state_name)
Relation between Country and Citizen is one to many.
Constraint: Primary key, area should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a procedure to display name of citizens having mother toung “Marathi “ and from
“India”;
2) Write a trigger which will fire before insert or update on country having no_of_state less
than equal to zero. (Raise user defined exception and give appropriate message)
Slip 23
Q3 Consider the following entities and their relationships. [40]
Wholesaler (w_no, w_name, address, city)
Product (product_no, product_name, rate)
Relation between Wholesaler and Product is Many to
Many with quantity as descriptive attribute.
Constraint: Primary key, rate 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 details of products supplied by “Mr. Patil”
2) Write a cursor which will display wholesaler wise product details.(Use Parameterized cursor)
Slip 24
Q3 Consider the following entities and their relationships. [40]
Student (rollno, sname, class, timetable)
Lab (LabNo, LabName, capacity, equipment)
Relation between Student and Lab is Many to One.
Constraint: Primary Key, capacity should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a procedure to display details of students which perform practical sessions in a given Lab.
2) Write a trigger which will fire before delete on Lab (Raise user defined exception and give
appropriate message)
Slip 25
Q3 Consider the following entities and their relationships. [40]
Gym (Name, city, charges, scheme)
Member (ID, Name, phoneNo, address)
Relation between Gym and member is one to many.
Constraint: Primary Key, charges must be greater than 0.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a procedure to display member details of gym located at “Pimpri’”
2) Write a cursor which will display gym wise member details.(Use Parametrized Cursor)
Slip 26
Q3 Consider the following entities and their relationships. [40]
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:
1) Write a procedure to display the name of HOD who has completed maximum project.
2) Write a trigger which will fire before insert or update on project having budget less than
or equal to zero. (Raise user defined exception and give appropriate message)
Slip 27
Q3 Consider the following entities and their relationships. [40]
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.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a procedure to display the plan having minimum response.
2) Write a trigger which will fire before insert or update on mobile number having length
less than or greater than10. (Raise user defined exception and give appropriate message)
Slip 28
Q3 Consider the following entities and their relationships. [40]
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:
1) Write a function which accept a table number and display total amount of bill for a
specific table
2) Write a cursor which will display table wise menu details.
Slip 29
Q3 Consider the following entities and their relationships. [40]
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.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a function which will return total investment amount of a particular client.
2) Write a trigger which will fire before insert or update on Investment having investment
amount less than 50000. (Raise user defined exception and give appropriate message)
Slip 30
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 a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
1) Write a procedure to display names of book written by “Mr. Patil” and are from “DPU
Library”.
2) Write a trigger which will fire before insert or update on book having price less than
or equal to zero. (Raise user defined exception and give appropriate message)
Comments
Post a Comment