I'm studying SQL with Oracle 11gR2 and I'm trying to write sql query to solve this problem.

Q. Assuming that campaign's budget is divided by the number of ADs included in the campaign,
show the campaign that contains the AD that consumes the largest amount of budget
and the campaign that contains the AD that consumes the smallest amount of budget
(do not consider AD with 0 budget)

Expected result is as below :(or in some other form)

``````campaign_number  AD_cost
--------------------   ----------
C-107                  350
C-101                  166.67``````

To solve this problem, I think I might have to first find out how many ADs each campaign contains ,
then divide(how?) each campaign's budget with the number of ADs it contains
and then find the campaign with the highest average budget and the campaing with the lowest average budget.(several if duplicate)

However, I couldn't figure out what proper SQL query could do these procedures.
Could anyone help me writing proper SQL query??

The given relations are as below:

``````<AD relation>
------------------ ---------------------------

GROUP_NUMBER   CAMPAIGN_NUMBER
-------------------------   ------------------------------
GROUP-101   C-101
GROUP-102   C-101
GROUP-103    C-102
GROUP-104    C-103
GROUP-105    C-104
GROUP-106    C-104
GROUP-107    C-107

<Campaign relation>
CAMPAIGN_NUMBER   BUDGET
------------------------------  -------------
C-101   500
C-102   400
C-103   900
C-104   700
C-105   750
C-106   700
C-107   350``````

To elaborate, campaign 'C-101' has GROUP-101 and GROUP-102 as in ADGroup,
So the budget(cost) of AD-11 is 166.67 which is 500(budget of campaign C-101) / 3 (number of ADs in campaign C-101).

campaign 'C-103' has GROUP-104 as in ADGroup,
So the budget(cost) of AD-14 is 300 which is 900(budget of campaign C-103) / 3 (number of ADs in campaign C-103)

campaign 'C-107' has GROUP-107 as in ADGroup,
So the budget(cost) of AD-17 is 350 which is 350(budget of campaign C-107) / 1 (number of ADs in campaign C-107)

For campaigns 'C-102','C-104','C-105','C-106' which have no AD as in AD relation, consider them as having 0 budget.
(or not considered in the first place as they have no AD)

Statements progress to desired results:

``````SELECT *
ORDER BY 1, 6 ASC;

SELECT CAMPAIGN.CAMPAIGN_NUMBER,
AVG (BUDGET),
AVG (BUDGET) / COUNT (AD_NUMBER) COST