Retrieve objects in Oracle

Question

I have a Oracle object called Menu which has a object type as an attribute.

menu type

Create Type Menu as Object( beer REF beertype, price float )

beertype

Create Type beertype as object( name char(20), seller char(20) )

table created using menu type

Create table Sells of Menu

table created using beertype

Create table beer of beertype

Now what I want to do is I want to write a select query which retrieves the name of the beer which has price more than 3.5.

I tried

SELECT beer.name 
FROM Sells; 

and

SELECT Sells.beer.name 
FROM Sells; 

Answer 1

Here's an example: test case first:

SQL> create type beertype as object(
  2    name char(20),
  3    seller char(20)
  4  );
  5  /

Type created.

SQL> create type menu as object(
  2    beer ref beertype,
  3    price float
  4  );
  5  /

Type created.

SQL> create table sells of menu;

Table created.

SQL> create table beer of beertype;

Table created.

SQL> insert into beer (name, seller) values ('Heineken', 'KTC');

1 row created.

SQL> insert into beer (name, seller) values ('Tuborg', 'Plodine');

1 row created.

SQL> insert into sells (beer, price) values ((select ref(b) from beer b where name = 'Heineken'), 10);

1 row created.

SQL> insert into sells (beer, price) values ((select ref(b) from beer b where name = 'Tuborg'), 2.5);

1 row created.

Query which selects beer name whose price is larger than 3.5:

SQL> select b.name, s.price
  2  from sells s join beer b on value(b) = deref(s.beer)
  3  where s.price > 3.5;

NAME                      PRICE
-------------------- ----------
Heineken                     10

SQL>

Here's a nice article, if you want to read more: Using REF Values for Retrieval of Data Values