writing sql query (Oracle 11g)

[p0qrs0]

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>
 AD_NUMBER  GROUP_NUMBER   
------------------ ---------------------------
AD-11   GROUP-101             
AD-12   GROUP-101            
AD-13   GROUP-102        
AD-14   GROUP-104           
AD-15   GROUP-104            
AD-16   GROUP-104             
AD-17   GROUP-107              

<ADGroup 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,
and thus has 3 ADs(AD-11,AD-12,AD-13) as in AD relation.
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).
AD-12 and AD-13 also have the same cost.

campaign 'C-103' has GROUP-104 as in ADGroup,
and thus has 3 ADs(AD-14,AD-15,AD-16) as in AD relation.
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)
AD-15 and AD-16 also have the same cost.

campaign 'C-107' has GROUP-107 as in ADGroup,
and thus has 1 AD(AD-17) as in AD relation.
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)

[peelsequal]

See: https://www.datacamp.com/community/tutorials/group-by-having-clause-sql

[peelsequal]

Never mind about HAVING CLAUSE...

Statements progress to desired results:

SELECT *
    FROM CAMPAIGN, ADGROUP, AD
WHERE     (AD.GROUP_NUMBER = ADGROUP.GROUP_NUMBER(+))
        AND (ADGROUP.CAMPAIGN_NUMBER(+) = CAMPAIGN.CAMPAIGN_NUMBER)
ORDER BY 1, 6 ASC;

SELECT CAMPAIGN.CAMPAIGN_NUMBER,
        AVG (BUDGET),
        COUNT (AD_NUMBER),
        AVG (BUDGET) / COUNT (AD_NUMBER) COST
    FROM CAMPAIGN, ADGROUP, AD
WHERE     (AD.GROUP_NUMBER = ADGROUP.GROUP_NUMBER)
        AND (ADGROUP.CAMPAIGN_NUMBER(+) = CAMPAIGN.CAMPAIGN_NUMBER)
GROUP BY CAMPAIGN.CAMPAIGN_NUMBER
ORDER BY 1 ASC;

SELECT CAMPAIGN.CAMPAIGN_NUMBER, AVG (BUDGET) / COUNT (AD_NUMBER) COST
    FROM CAMPAIGN, ADGROUP, AD
WHERE     (AD.GROUP_NUMBER = ADGROUP.GROUP_NUMBER)
        AND (ADGROUP.CAMPAIGN_NUMBER(+) = CAMPAIGN.CAMPAIGN_NUMBER)
GROUP BY CAMPAIGN.CAMPAIGN_NUMBER
ORDER BY 2 DESC;