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 view all_movie_characters_3
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 View created.
select * from all_movie_characters_3| ID | NAME | 1 | Darth Vader | 1 | Darth Vader | 1 | Darth Vader | 1 | Darth Vader | 2 | Luke Skywalker | 2 | Luke Skywalker | 2 | Luke Skywalker | 2 | Luke Skywalker | 3 | Rey | 3 | Rey |
|---|
select * from
(
select * from all_movie_characters
minus
select * from all_movie_characters_3
)
union all
(
select * from all_movie_characters_3
minus
select * from all_movie_characters
)no data found
select 'distinct' type, count(*)
from (
select distinct *
from all_movie_characters_3
)
union all
select 'all', count(*) from all_movie_characters_3| TYPE | COUNT(*) | distinct | 3 | all | 10 |
|---|
with old as (
select id, name, count(*) number_of_equals
from all_movie_characters
group by id, name
),
new as (
select id, name, count(*) number_of_equals
from all_movie_characters_3
group by id, name
)
(
select * from old
minus
select * from new
)
union all
(
select * from new
minus
select * from old
)| ID | NAME | NUMBER_OF_EQUALS | 1 | Darth Vader | 1 | 2 | Luke Skywalker | 1 | 3 | Rey | 1 | 1 | Darth Vader | 4 | 2 | Luke Skywalker | 4 | 3 | Rey | 2 |
|---|
select id, name, sum(old_cnt), sum(new_cnt)
from (
select id, name, 1 old_cnt, 0 new_cnt
from all_movie_characters source
union all
select id, name, 0 old_cnt, 1 new_cnt
from all_movie_characters_3 target
)
group by id, name
having sum(old_cnt) != sum(new_cnt)| ID | NAME | SUM(OLD_CNT) | SUM(NEW_CNT) | 3 | Rey | 1 | 2 | 1 | Darth Vader | 1 | 4 | 2 | Luke Skywalker | 1 | 4 |
|---|