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
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_namet='mutual';
2 c c1 %rowtype;
3 begin
4 open c1;
5
dbms_output.put_line('e_name'||''||'inv_name');
6 loop
7 fetch c1 into c;
8 exit when c1 %notfound;
9 if(c.inv_name=n) then
10
dbms_output.put_line(c.e_name||''||c.inv_name);
11 end if;
12 end loop;
13 close c1;
14 end;
15 /
Procedure created.
SQL> begin
2 p1('mutual');
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
