SQL Plus - Alias type question.

Question

I need to find the book code and book title for each book whose price is greater than the book price for every book that has the type 'HOR'. My table looks like this

CREATE TABLE BOOK (
   BOOK_CODE CHAR(4) CONSTRAINT BOOK_BOOK_CODE_PK PRIMARY KEY, 
   TITLE VARCHAR2(40) CONSTRAINT BOOK_TITLE_NN NOT NULL, 
   PUBLISHER_CODE CHAR(2) CONSTRAINT BOOK_PUBLISHER_CODE_FK REFERENCES PUBLISHER(PUBLISHER_CODE),
   TYPE CHAR(3), (this is where the 'HOR' is located)
   PRICE NUMBER(4,2), 
   PAPERBACK CHAR(1) 
);

I have tried several different ways but I am at a loss. I am assuming I will have to use an alias?

Answer 1

Not necessarily. You could use a nested SELECT like this:

SELECT
    BOOK_CODE,
    TITLE
FROM
    BOOK
WHERE
    PRICE > (SELECT MAX(PRICE) FROM BOOK WHERE TYPE = 'HOR'))

Answer 2

SELECT a.book_code, a.title
FROM book a, book b
WHERE a.price > b.price
AND b.type = 'HOR'

Possibly?