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. 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” 

2) Write a cursor which will display date wise investment details.

create table employee11(emp_id integer primary key not null,emp_name varchar2(30),emp_add varchar2(30));

create table investment11(inv_no integer primary key not null,inv_name varchar2(30),inv_date date,inv_amount integer,emp_id1 integer references employee(emp_id));
insert into employee11 values(1,'amit','bramati')
insert into employee11 values(2,'amol','pune')
insert into employee11 values(3,'rahul','satara')
insert into employee11 values(4,'suresh','sangali')

insert into investment11 values(101,'Fund','25-Jan-2019',30000,1)
insert into investment11 values(102,'mutual funds','25-Jan-2019',30000,2)
insert into investment11 values(103,'Debt mutual funds','25-Jan-2019',30000,3)
insert into investment11 values(104,'RBI Taxable Bonds','25-Jan-2019',30000,4)

2) Write a procedure which will display details of employees invested amount in “Mutual Fund” 

Consider the following entities and their relationships.  
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 function which will accept publication name from user and display total price of books of that publication. 
2) Write a cursor which will display library wise book details.(Use Parameterized Cursor)

Consider the following entities and their relationships.  
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: 

Answer : 

create table library11(Lno integer primary key not null, Lname varchar2(40), Location varchar2(40), Librarian varchar2(40), no_of_books integer);
insert into library11 values(1,'bbk','pune','amit',100)
insert into library11 values(2,'book know','satara','amol',120)
insert into library11 values(3,'about you','sangali','ramesh',200)

create table book(Bid integer primary key not null, Bname varchar2(40), Author_Name varchar2(40), Price integer not null, publication varchar2(40), Lno1 integer references library11(Lno));
insert into book values(101,'wsk','Dr.j.k.s',200,'sk publication',1)
insert into book values(102,'wk','Dr.s.s.s',500,'pk publication',2)
insert into book values(103,'ok','Dr.k.k',450,'nw publication',3)

1) Write a function which will accept publication name from user and display total price of books of that publication. 

create or replace function f2(publication1 in varchar)return number
is
str1 number;
begin
select Price into str1 from book where publication='nw publication';
return str1;
end f2;

select f2('nw publication') from dual;

declare
n2 varchar2(20);
n3 number;
begin
n2:=:n2;
n3:=f2(n2);
dbms_output.put_line('total price of books of that publication'||n3);
end;

2) Write a cursor which will display library wise book details.(Use Parameterized Cursor)

declare
cursor book_data(Lno2 dept.dno%type) is select Bid,Bname,Author_Name,Price,Publication from library11,book where library11.Lno=book.Lno1 and Lno=Lno;
Lno3 book.Bid%type;
Bid1 book.Bid%type;
Bname1 book.Bname%type;
Author_Name1 book.Author_Name%type;
Price1 book.Price%type;
Publication1 book.Publication%type;

begin
Lno3:=:Lno3;
open book_data(Lno3);
loop
fetch book_data into Bid1,Bname1,Author_Name1,Price1,Publication1;
dbms_output.put_line('the d no is'||Bid1);
dbms_output.put_line('the d name is'||Bname1);
dbms_output.put_line('the d loc is'||Author_Name1);
dbms_output.put_line('the d loc is'||Price1);
dbms_output.put_line('the d loc is'||Publication1);
exit when book_data %notfound;
end loop;
close book_data;
end;

Consider the following entities and their relationships.  

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 > 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. 

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)

Consider the following entities and their relationships.  

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 > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

Answer : 

create table client(client_no integer primary key not null, client_name varchar2(30), address varchar2(30), birthdate date);
insert into client values (11,'amit','pune','20-jan-2020');
insert into client values (12,'ramesh','satara','20-jun-2019');
insert into client values (13,'radh','baramati','1-jan-2020');
insert into client values (14,'sham','solapur','27-dec-2019');
create table policy_info(policy_no integer primary key not null, desc1 varchar2(30), maturity_amt integer, prem_amt integer, date1 date);
insert into policy_info values(21,'complet',10000,25000,'21-jan-2020');
insert into policy_info values(22,'incomplet',2000,10000,'30-jun-2020');
insert into policy_info values(23,'complet',100,200,'20-may-2020');
insert into policy_info values(24,'incomplet',1500,20050,'20-feb-2020');
create table info(info integer primary key not null, client_no1 integer references client(client_no), policy_no1 integer references policy_info(policy_no));
insert into info values(31,11,21);
insert into info values(32,14,24);
insert into info values(33,13,22);
insert into info values(34,12,23);

1) Write a procedure which will display all policy details having premium amount less than 5000. 

create or replace function point return number

is
total_count number;
begin
select maturity_amt into total_count from client,policy_info,info where client.client_no=info.client_no1 and policy_info.policy_no=info.policy_no1 and policy_no=21;
return total_count;
end;
call function-----------
1)By using select keyword
select point from dual
2)By using calling program

declare
total_count2 number;
begin

total_count2:=point;
dbms_output.put_line('Total maturity amount of policies of a particular client :- '||total_count2);
end;

2) Write a trigger which will fire before insert or update on policy_info having maturity amount less than premium amount.

declare
cursor p_startmonth is select policy_no,desc1,maturity_amt from policy_info where date1='30-jun-2020';
begin
for x in p_startmonth loop
dbms_output.put_line('the p no is:'||x.policy_no);
dbms_output.put_line('the p name is:'||x.desc1);
dbms_output.put_line('the p status is:'||x.maturity_amt);
end loop;
end;

Consider the following entities and their relationship.  

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 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). 

2) Write a procedure to calculate city wise total cost of each newspaper.

Relationship between Newspaper and Cities is many-to-many with descriptive attribute daily required Constraints: name and pincode primary key Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

Answer : 

create table newspaper(
name varchar2(30) primary key not null,
language varchar2(20),
publisher varchar2(30),
cost integer not null);

insert into newspaper values('sakal','Marahti','Rahul Gadpale',07)
insert into newspaper values('lokamat','Hindi','Rajendra Darda',5)
insert into newspaper values('Indian times','English',' Jaideep Bose',10)
insert into newspaper values('Malayala','Malayalam','Mammen Mathew',04)

create table cities(
pincode integer primary key not null,
city varchar2(20),
state varchar2(30));

insert into cities values(413101,' Pune','Maharashtra')
insert into cities values(400018,'Mumbai','Maharashtra')
insert into cities values(400017,'Mumbai','Maharashtra')
insert into cities values(189895,'Kottayam','Kerala')

create table details(
details1 integer primary key not null,
name1 varchar2(30) references newspaper(name),
pincode1 integer references cities(pincode));

insert into details values(111,'sakal',413101)
insert into details values(222,'lokamat',413101)
insert into details values(333,'Indian times',400018)
insert into details values(444,'Malayala',400017)
insert into details values(555,'sakal',189895)

Copy

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). 

Answer : 

create or replace procedure pubname(pub_name1 in newspaper.language%type)
as
cursor sau is select name, cost, qty
from newspaper, cities, details
where cities.pincode=details.pincode1 and newspaper.name=details.name1
order by cost desc;
prod number;
name varchar2(30);
cost number(10);
qty number(10);

begin
open sau;
loop
fetch sau into name,cost,qty;
exit when sau%notfound;
prod:=cost*qty;
DBMS_OUTPUT.PUT_LINE('customer_id =' || name);
DBMS_OUTPUT.PUT_LINE('quantity value =' || cost);
DBMS_OUTPUT.PUT_LINE('price =' || qty);
DBMS_OUTPUT.PUT_LINE('The total value of customer purchases is = ' || prod);
end loop;
close sau;
END

declare
pub_name5 newspaper.language%type;
begin
pubname(pub_name5);
end;

Copy

2) Write a procedure to calculate city wise total cost of each newspaper.

Answer : 

create or replace trigger pincode
before insert or update of pincode on cities
for each row
begin
if:new.pincode=6 then
raise_application_error(-20001,'insert valid price');
end if;
end;

Consider the following Item_Supplier database 

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 

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)

Answer : 

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:

CREATE TABLE Item11(
Ino int primary key ,
Iname varchar(25));
INSERT INTO Item11 VALUES (101,'Keyboard');
INSERT INTO Item11 VALUES (102,'Mouse');
INSERT INTO Item11 VALUES (103,'Sound');
INSERT INTO Item11 VALUES (104,'Printer');

CREATE TABLE Sup11(
Sno int primary key,
Sname varchar(25),
Address varchar(25),
City varchar(30) );

INSERT INTO Sup11 VALUES (201,'Ashish Lokhande','Hadapsar','Pune');
INSERT INTO Sup11 VALUES (202,'Patil','Indapur','Indapur');
INSERT INTO Sup11 VALUES (203,'Bhatia','Sarswatinagar','Indapur');
INSERT INTO Sup11 VALUES (204,'Baburao','Anandnagar','Baramati');

CREATE TABLE It_Sup11(
isno integer primary key,
Ino1 int references item11(Ino),
Sno1 int references Sup11(Sno),
Rate int ,
quantity int);

INSERT INTO It_Sup11 VALUES (301,101,201,500,10);
INSERT INTO It_Sup11 VALUES (302,102,202,100,5);
INSERT INTO It_Sup11 VALUES (303,103,203,3300,15);
INSERT INTO It_Sup11 VALUES (304,104,204,5300,15);
INSERT INTO It_Sup11 VALUES (305,101,201,510,17);
INSERT INTO It_Sup11 VALUES (306,104,202,1200,8);

Copy

 1) Write function to print the total number of suppliers of a particular item.

create or replace function tosup return number
is
total_count number;
begin
select count(*) into total_count from Item11,Sup11,It_Sup11 where Item11.Ino=It_Sup11.Ino1 and Sup11.Sno=It_Sup11.Sno1 and Iname='Keyboard';
return total_count;
end;

call function-----------
1)By using select keyword
select tosup from dual

2)By using calling program
declare
total_count2 number;
begin
total_count2:=tosup;
dbms_output.put_line('Total Number of suppler to provide KEYBOARD Is'||total_count2);
end;

Copy

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 Rate
before insert or update of Rate on It_Sup11
for each row
begin
if:new.Rate<=0then
raise_application_error(-20001,'insert valid rate');
end if;
end;

Copy

Consider the following entities and their relationships. 

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 > 0. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 

1) Write a function which will return total maturity amount of policies of a particular client. 

2) Write a cursor which will display policy date wise client details.