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