Slip 13
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.
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 /
