By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create type beertype as object(
name char(20),
seller char(20)
);
create type menu as object(
beer ref beertype,
price float
);
create table sells of menu;
create table beer of beertype;
BEGIN
insert into beer (name, seller) values ('Heineken', 'KTC');
insert into beer (name, seller) values ('Tuborg', 'Plodine');
insert into sells (beer, price) values ((select ref(b) from beer b where name = 'Heineken'), 10);
insert into sells (beer, price) values ((select ref(b) from beer b where name = 'Tuborg'), 2.5);
END;
/
1 rows affected
select DEREF(s.beer).name AS name, s.price
from sells s
where s.price > 3.5;
| NAME | PRICE |
|---|---|
| Heineken | 10 |
select s.beer.name AS name, s.price
from sells s
where s.price > 3.5;
| NAME | PRICE |
|---|---|
| Heineken | 10 |