Ready Project |BCA Project | MCA Project | BBA Project | MBA Project |INDIA'S BIGGEST COLLEGE PROJECT STORE

8) Consider the following entities and their relationship.
University (u_no, u_name, u_city)
College (c_no, c_name, c_city, year_of_establishment )
Relationship between University and College is one-to-many
Constraints: primary key, foreign key,
u_name and c_name should not be null.
Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following:
Write a package, which consists of one procedure and one function. Pass university number as a parameter to procedure and display details of that university. Pass city as a parameter to a function and return total number of colleges in given city.
Write a cursor which will display university wise their college details. (Use parameterized cursor)


 create table University(u_no number primary key, u_name varchar2(20), u_city varchar2(20));

create table College (c_no number primary key, c_name varchar2(20), c_city varchar2(20), year number,u_no number references University(u_no));

insert into University values(1,'solapur university','solapur');

insert into University values(2,'pune university','pune');

insert into College values(101,'d.y.patil','pune',2000,1);

insert into College values(103,'Y.D.S','solapur',2001,2);

insert into College values(104,'D.Y.S','solapur',2002,2);

insert into College values(102 ,'Ramkirshna more','pune',2001,1);

Q1)


create or replace package pack2

as

procedure p1(uno in number);

function f1(c College.c_city%type )

return number;

end pack2;


pack body-



create or replace package body pack2

as

procedure p1(uno in number)

as

a University%rowtype;

begin 

select University.* into a from University 

where u_no=uno;

dbms_output.put_line(' .');

dbms_output.put_line(' .... OUTPUT .....');

dbms_output.put_line(' .');

dbms_output.put_line('uno     university_name     city');

dbms_output.put_line(a.u_no||'     '||a.u_name||'     '||a.u_city);

end p1;

function f1(c College.c_city%type)

return number

as cnt number;

begin

select count(*) into cnt from College where c_city=c;

return cnt;

end f1;

end pack2;


output-


SQL> set serveroutput on;

SQL>  declare

        n number;

        name College.c_city%type ;

        i number;

        begin

        n:=&n;

        pack2.p1(n);

        name:= '&name';

        i:=pack1.f1(name);

      dbms_output.put_line(' .');

      dbms_output.put_line(' .');

      dbms_output.put_line(' .');

      dbms_output.put_line('count of '||name||' is '||i);

      end;

Q2)


create or replace procedure d1(uno in number)

     as

     cursor c1 is select u.u_no,c.c_no,c.c_name,c.c_city,c.year from University u,College c

     where u.u_no=c.u_no  and c.u_no=uno;

     begin

     for x in c1 loop

     dbms_output.put_line('university no'||x.u_no ||'college no:'||x.c_no ||'college name:'||x.c_name ||'college City:' ||x.c_city ||'year:'||x.year);

     end loop;

     end;

Search

Project Categories

Recent Posts

Sign In