RDBMS_PRACT_5_B

Assignment 5 Cursor : SET B

postgres=# \c company4

psql (8.4.5)

You are now connected to database "company4".

company4=# create table company(name varchar(20) primary key,add varchar(50),phone varchar(20),share_value money);NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "company_pkey" for table "company"

CREATE TABLE

company4=# create table person(pname varchar(30) primary key,pcity varchar(20));NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "person_pkey" for table "person"

CREATE TABLE

company4=# create table cp(name varchar(30) references company(name) on delete cascade,pname varchar(30) references person(pname) on delete cascade,nos_of_share int);

CREATE TABLE

company4=# \d

          List of relations

 Schema |  Name   | Type  |  Owner  

--------+---------+-------+----------

 public | company | table | postgres

 public | cp      | table | postgres

 public | person  | table | postgres

(3 rows)

company4=# insert into company values('infosys','abc','020-665544','$5000000');

INSERT 0 1

company4=# insert into company values('infosystem','abc','020-665545','$6000000');

INSERT 0 1

company4=# insert into company values('info','pune','020-665545','$7000000');

INSERT 0 1

company4=# insert into company values('infotec','pune','020-665543','$5500000');

INSERT 0 1

company4=# insert into company values('wipro','pune','020-665345','$6500000');

INSERT 0 1

company4=# insert into person values('Rahul','pune');

INSERT 0 1

company4=# insert into person values('Vidhyadhar','pune');

INSERT 0 1

company4=# insert into person values('sachin','mumbai');

INSERT 0 1

company4=# insert into person values('darshan','mumbai');

INSERT 0 1

company4=# insert into person values('shiva','mumbai');

INSERT 0 1

company4=# insert into cp values('infosys','Rahul',10);

INSERT 0 1

company4=# insert into cp values('infosystem','Vidhyadhar',25);

INSERT 0 1

company4=# insert into cp values('infotec','darshan',5);

INSERT 0 1

company4=# insert into cp values('info','sachin',15);

INSERT 0 1

company4=# insert into cp values('wipro','shiva',12);

INSERT 0 1

company4=# select * from company;

    name    | add  |   phone    |  share_value 

------------+------+------------+---------------

 infosys    | abc  | 020-665544 | $5,000,000.00

 infosystem | abc  | 020-665545 | $6,000,000.00

 info       | pune | 020-665545 | $7,000,000.00

 infotec    | pune | 020-665543 | $5,500,000.00

 wipro      | pune | 020-665345 | $6,500,000.00

(5 rows)

company4=# select * from person;

   pname    | pcity 

------------+--------

 Rahul      | pune

 Vidhyadhar | pune

 sachin     | mumbai

 darshan    | mumbai

 shiva      | mumbai

(5 rows)

company4=# select * from cp;

    name    |   pname    | nos_of_share

------------+------------+--------------

 infosys    | Rahul      |           10

 infosystem | Vidhyadhar |           25

 infotec    | darshan    |            5

 info       | sachin     |           15

 wipro      | shiva      |           12

(5 rows)

company4=# select count(pname),sum(share_value) from person,company;

 count |       sum      

-------+-----------------

    25 | $150,000,000.00

(1 row)

company4=# select sum(share_value) from person,company;

       sum      

-----------------

 $150,000,000.00

(1 row)

company4=# select count(cp.pname),sum(share_value) from person,company,cp where person.pname=cp.pname and company.name=cp.name;

 count |      sum      

-------+----------------

     5 | $30,000,000.00

(1 row)

2)

company4=# create function f11()returns void as '

declare c10 cursor for select count(cp.name),sum(share_value) from person,company,cp where person.pname=cp.pname and company.name=cp.name;

cnt int;

sm money;

begin

open c10;

loop

fetch c10 into cnt,sm;

exit when not found;

raise notice''Count : %'',cnt;

raise notice''Sm : %'',sm;

end loop;

close c10;

end '

language 'plpgsql';

CREATE FUNCTION

company4=# select f11();

NOTICE:  Count : 5

NOTICE:  Sm : $30,000,000.00

 f11

-----

(1 row)

1)

company4=# create function b11()returns void as '

declare cb1 cursor for select pname,nos_of_share from cp where pname=''sachin'';

p varchar(30);

n int;

begin

open cb1;

loop

fetch cb1 into p,n;

exit when not found;

update cp set nos_of_share=nos_of_share+n where pname=''Rahul'';

update cp set nos_of_share=nos_of_share-n where pname=p;

end loop;

close cb1;

end '

language 'plpgsql';

CREATE FUNCTION

company4=# select b11();

 b11

-----

(1 row)

company4=# select * from cp;

    name    |   pname    | nos_of_share

------------+------------+--------------

 infosystem | Vidhyadhar |           25

 infotec    | darshan    |            5

 wipro      | shiva      |           12

 infosys    | Rahul      |           25

 info       | sachin     |            0

(5 rows)

company4=#