drop table bookings purge
ORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-00942
create table bookings ( hr int, room varchar2(10), who varchar2(10))
Table created.
insert into bookings values( 8, 'Room2' , 'PETE')
1 row(s) inserted.
insert into bookings values( 9, 'Room1' , 'JOHN')
1 row(s) inserted.
insert into bookings values( 11, 'Room1' , 'MIKE')
1 row(s) inserted.
insert into bookings values( 14, 'Room2' , 'JILL')
1 row(s) inserted.
insert into bookings values( 15, 'Room2' , 'JANE')
1 row(s) inserted.
insert into bookings values( 16, 'Room1' , 'SAM')
1 row(s) inserted.
drop table hrs purge
ORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-00942
create table hrs ( hr int )
Table created.
insert into hrs values ( 8)
1 row(s) inserted.
insert into hrs values ( 9)
1 row(s) inserted.
insert into hrs values (10)
1 row(s) inserted.
insert into hrs values (11)
1 row(s) inserted.
insert into hrs values (12)
1 row(s) inserted.
insert into hrs values (13)
1 row(s) inserted.
insert into hrs values (14)
1 row(s) inserted.
insert into hrs values (15)
1 row(s) inserted.
insert into hrs values (16)
1 row(s) inserted.
select * from bookings
HR | ROOM | WHO | 8 | Room2 | PETE | 9 | Room1 | JOHN | 11 | Room1 | MIKE | 14 | Room2 | JILL | 15 | Room2 | JANE | 16 | Room1 | SAM |
---|
select * from hrs
HR | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 |
---|
select hrs.hr, t1.room, t1.who
from hrs, bookings t1
where hrs.hr = t1.hr(+)
order by 1
HR | ROOM | WHO | 8 | Room2 | PETE | 9 | Room1 | JOHN | 10 | - | - | 11 | Room1 | MIKE | 12 | - | - | 13 | - | - | 14 | Room2 | JILL | 15 | Room2 | JANE | 16 | Room1 | SAM |
---|
select hrs.hr, t1.room, t1.who
from bookings t1
partition by (t1.room)
right outer join hrs on (hrs.hr = t1.hr)
order by 1,2
HR | ROOM | WHO | 8 | Room1 | - | 8 | Room2 | PETE | 9 | Room1 | JOHN | 9 | Room2 | - | 10 | Room1 | - | 10 | Room2 | - | 11 | Room1 | MIKE | 11 | Room2 | - | 12 | Room1 | - | 12 | Room2 | - | 13 | Room1 | - | 13 | Room2 | - | 14 | Room1 | - | 14 | Room2 | JILL | 15 | Room1 | - | 15 | Room2 | JANE | 16 | Room1 | SAM | 16 | Room2 | - |
---|