create table star_wars_characters (
id integer primary key,
name varchar2(100)
)Table created.
create table appearance_in_episode (
character_fk integer not null
references star_wars_characters ( id )
on delete cascade,
episode_no number(2,0) not null
)Table created.
insert into star_wars_characters
values ( 1, 'Darth Vader' )1 row(s) inserted.
insert into star_wars_characters
values ( 2, 'Luke Skywalker' )1 row(s) inserted.
insert into star_wars_characters
values ( 3, 'Rey' )1 row(s) inserted.
insert into appearance_in_episode values ( 1, 3 )1 row(s) inserted.
insert into appearance_in_episode values ( 1, 4 )1 row(s) inserted.
insert into appearance_in_episode values ( 1, 5 )1 row(s) inserted.
insert into appearance_in_episode values ( 1, 6 )1 row(s) inserted.
insert into appearance_in_episode values ( 2, 4 )1 row(s) inserted.
insert into appearance_in_episode values ( 2, 5 )1 row(s) inserted.
insert into appearance_in_episode values ( 2, 7 )1 row(s) inserted.
insert into appearance_in_episode values ( 2, 9 )1 row(s) inserted.
insert into appearance_in_episode values ( 3, 8 )1 row(s) inserted.
insert into appearance_in_episode values ( 3, 9 )1 row(s) inserted.
create or replace view all_movie_characters
as
select
sw_char.id,
sw_char.name
from
star_wars_characters sw_char
inner join appearance_in_episode ep
on sw_char.id = ep.character_fk
group by sw_char.id, sw_char.name View created.
create or replace function appears_in_movie(
i_character_id integer
) return integer result_cache
as
l_count integer;
begin
select count(*)
into l_count
from appearance_in_episode
where character_fk = i_character_id
and episode_no between 4 and 6;
if l_count > 0 then
return 1;
else
return 2;
end if;
end; Function created.
create or replace view all_movie_character_2
as
select
sw_char.id,
sw_char.name
from
star_wars_characters sw_char
where appears_in_movie(sw_char.id) = 1 View created.
select * from all_movie_characters
minus
select * from all_movie_character_2
union all
select * from all_movie_character_2
minus
select * from all_movie_charactersno data found
select * from all_movie_character_2| ID | NAME | 1 | Darth Vader | 2 | Luke Skywalker |
|---|
select * from all_movie_characters
minus
select * from all_movie_character_2| ID | NAME | 3 | Rey |
|---|
select * from all_movie_characters
minus
select * from all_movie_character_2
union all
select * from all_movie_character_2| ID | NAME | 3 | Rey | 1 | Darth Vader | 2 | Luke Skywalker |
|---|
select * from all_movie_characters
minus
select * from all_movie_character_2
union all
select * from all_movie_character_2
minus
select * from all_movie_charactersno data found
select * from
(
select * from all_movie_characters
minus
select * from all_movie_character_2
)
union all
(
select * from all_movie_character_2
minus
select * from all_movie_characters
)| ID | NAME | 3 | Rey |
|---|