Sql (oracle 12c): How To Select Results From Another Select Statement (nesting?/sub--query?)

I'm working on a homework problem for an intro SQL class that I'm taking. This week's topic is Summary Functions. Here is the problem: Determine the average retail price of books b

Solution 1:

When you use aggregate function (like avg),you must use group by on other columns, And when you use group by ,you must use having for set where clause on aggregated column. Use Having for this:

select p.name, b. category, avg(b.retail)
from books b, publisher p 
where b.pubid = p.pubid
and b.category in ('CHILDREN','COMPUTER')
groupby p.name, b.category 
havingavg(b.retail) >50

Solution 2:

Add an HAVING clause to your query. HAVING applies to the group functions:

select p.name, b. category, avg(b.retail)
from books b, publisher p 
where b.pubid = p.pubid
and b.category in ('CHILDREN','COMPUTER')
groupby p.name, b.category
havingavg(b.retail) >50;