RDBMS Slip No.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. 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.

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: 

Answer :

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)

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

Answer :

declare cursor invest1 is select inv_no,inv_name,inv_date,inv_amount from investment11,employee11 where employee11.emp_id=investment11.emp_id1 and inv_date='25-Jan-2019';

begin
for x in invest1 loop
dbms_output.put_line('the comp_no is:'||x.inv_no);
dbms_output.put_line('the comp_name is:'||x.inv_name);
dbms_output.put_line('the comp_type is:'||x.inv_date);
dbms_output.put_line('the comp_type is:'||x.inv_amount);
end loop;
end;

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

Answer :

create or replace procedure inv1
is
cursor disdata1 is select emp_id,emp_name,emp_add from employee11,investment11 where inv_name='mutual funds';
begin
for x in disdata1 loop
dbms_output.put_line('The Employee Id'|| x.emp_id);
dbms_output.put_line('The Employee Name'|| x.emp_name);
dbms_output.put_line('The Empolyee Address'|| x.emp_add);
end loop;
end;

calling program----
begin
inv1;
end;