Question [SQL]: How can I list all books with prices greather than the average price of books of the S

Question: How can I list all books with prices greather than the average price of books of the same type?
In database pubs, have a table named titles, its column named Price mean the price of the book, and another named type mean the type of books.
Now I want to get the result as below:

Program Code
Type title price
-----------------------------------------------------------------------------------------------------------------
Business the busy executive's database guide 19.9900
...
...
...

...

 

Answer:

Select A. [ Type ] ,. [ Title ] ,. [ Price ]
From   [ Pubs ] . [ DBO ] . [ Titles ] A,
( Select   [ Type ] , [ Price ] = AVG ( [ Price ] ) From   [ Pubs ] . [ DBO ] . [ Titles ]   Group   By   [ Type ] ) B
Where A. [ Type ] = B. [ Type ]   And A. [ Price ] > B. [ Price ]

 

Here is a similar question:

The first question is relatively simple. to query the sales table, use oneSQLStatement.

SalesTable

Orderid	Region	Total
1	A	100.00
2	C	80.00
3	A	130.00
4	B	90.00
5	B	100.00
6	C	120.00
7	A	90.00
8	C	90.00
9	B	80.00

SQLStatement:

Select   *   From Sales As S
Inner   Join  
( Select   AVG (Total) As Avge, Region From Sales Group   By Region) avgtable
On S. Region = Avgtable. Region
Where Total > Avgtable. avge