Slip 13 - C) Consider the following entities and their relationships. 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.

Solution:

SQL>Create table country13(cid int primary key,cname varchar(10),no_of_states varchar(10),area varchar(10),location varchar(10),population varchar(10));

SQL>insert into country13 values(1,'5','mj','ny','20,000');

SQL>Create table citizen13(id int primary key,name varchar(10),mother_tounge varchar(10),state_name  varchar(10),cid int);

SQL>insert into citizen13 values(1,'amit','hindi','goa',1);

1) Write a function which will display name of the country having minimum population.

SQL>set serveroutput on

SQL>create or replace function f1(dn in varchar)return varchar as dmn varchar(10);

  2 begin

  3 select(country13.cname)into dmn from country13 where population=(select min(population)from country13);

  4 if sql %found then

  5 return (dmn);

  6 else

  7 return null;

  8 end if;

  9 end f1;

 10  /

Function created.

SQL>begin

  2 dbms_output.put_line('name-'||f1('cname'));

  3 end;

  4 /

2) Write a cursor which will display county wise citizen details.

SQL>  set serveroutput on

SQL>  declare

  2   cursor c1 is select  name, state_name   from country13, citizen13 where  country13.cid= citizen13.cid order by cname;

  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. name ||''||c.state_name);

 10    end loop;

 11    close c1;

 12    end;

 13  /