Question

CREATE TABLE campaign

(

cmte_id             varchar(12),

cand_id             varchar(12),

cand_nm             varchar(40),

contbr_nm           varchar(40),

contbr_city         varchar(40),

contbr_st           varchar(40),

contbr_zip          varchar(20),

contbr_employer     varchar(60),

contbr_occupation   varchar(40),

contb_receipt_amt   numeric(6,2),

contb_receipt_dt    varchar(20),

receipt_desc        varchar(40),

memo_cd             varchar(20),

memo_text           varchar(20),

form_tp               varchar(20),

file_num            varchar(20),

tran_id             varchar(20),

election_tp         varchar(20)

Write SQL queries using the campaign data table.

-- 4. show the candidate name and number of contributions, for each candidate

-- Order by decreasing number of contributions.

-- 5. show the candidate name and average contribution amount for each candidate,

-- looking at positive contributions only

-- Order by decreasing average contribution amount.

-- 6. show the candidate name and the total amount received by each candidate.

-- Order the output by total amount received.

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

Question 4:

SQl Query :

select cand_nm,count(contbr_nm) as 'number of contributions' from campaign group by cand_nm order by count(contbr_nm) desc

Explanation :Given query will count using the count() function and return result.

**************************

Question 5:

SQl Query :

select cand_nm,avg(contb_receipt_amt) as 'average contribution amount' from campaign where contb_receipt_amt >0 group by cand_nm order by avg(contb_receipt_amt) desc;

Explanation :Given query will find average of amount using the avg() function and return result.

**************************

Question 6:

SQl Query :

select cand_nm,sum(contb_receipt_amt) as 'total amount received' from campaign group by cand_nm order by sum(contb_receipt_amt);

Explanation :Given query will find sum of amount using the sum() function and return result.

**************************

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.