Movie (mvno, mvname, releaseyear)
Actor (actno, actname)
*********************************************************************************
SQL> create table movie52(mv_no number(5) primary key,mv_name varchar2(10),
releasyear number(5));
Table
created.
SQL>
desc movie52;
SQL>
insert into movie52 values(1,'devdas',2000);
1
row created.
SQL>
insert into movie52 values(2,'gajani',2010);
1
row created.
SQL>
insert into movie52 values(3,'kick',2014);
1
row created.
SQL>
insert into movie52 values(4,'don',2011);
1
row created.
SQL>
insert into movie52 values(5,'indian',2012);
1
row created.
SQL>
select * from movie52;
SQL>
create table actor52(act_no number(5) primary key,act_name varchar2(10));
Table
created.
SQL> insert into actor52 values(11,'aishwarya');
1
row created.
SQL>
insert into actor52 values(12,'srk khan');
1
row created.
SQL>
insert into actor52 values(13,'salman');
1
row created.
SQL>
insert into actor52 values(14,'amir');
1
row created.
SQL>
insert into actor52 values(15,'sani');
1
row created.
SQL>
create table mv_act52(mv_no number(5) references movie52,act_no number(5)
references actor52,rate_of_actor
number(10));
Table
created.
SQL>
desc mv_act52;
SQL> insert into mv_act52 values(1,11,2000000);
1
row created.
SQL>
insert into mv_act52 values(1,14,2200000);
1
row created.
SQL>
insert into mv_act52 values(2,12,3000000);
1
row created.
SQL>
insert into mv_act52 values(3,13,2800000);
1
row created.
SQL>
insert into mv_act52 values(2,11,2500000);
1
row created.
SQL>
insert into mv_act52 values(4,11,2700000);
1
row created.
SQL>
insert into mv_act52 values(5,15,3000000);
1
row created.
SQL>
insert into mv_act52 values(1,12,3500000);
1
row created.
1)create or replace pl/sql function To return Total no of movie of aishwarya
SQL>
create or replace function f6
2
return number
3 IS
4
v_count number;
5 BEGIN
6
select count(movie52.mv_no) into v_count from movie52,actor52,mv_act52
7
where movie52.mv_no=mv_act52.mv_no and actor52.act_no=mv_act52.act_no
8 and
act_name='aishwarya';
9
return v_count;
10 END
f6;
11 /
Function
created.
SQL>
DECLARE
2
c number;
3 BEGIN
4
c:=f6();
5
dbms_output.put_line('\Total no of moive is-'||c);
6 END;
7 /
***OUTPUT***
Total
no of moive is-3
PL/SQL
procedure successfully completed.
