CREATE TABLE candidate (

cand_id varchar(12) primary key, -- cand_id

name varchar(40) -- cand_nm

);

CREATE TABLE contributor (

contbr_id integer primary key,

name varchar(40), -- contbr_nm

city varchar(40), -- contbr_city

state varchar(40), -- contbr_st

zip varchar(20), -- contbr_zip

employer varchar(60), -- contbr_employer

occupation varchar(40) -- contbr_occupation

);

CREATE TABLE contribution (

contb_id integer primary key,

cand_id varchar(12), -- cand_id

contbr_id varchar(12), -- contbr_id

amount numeric(6,2), -- contb_receipt_amt

date varchar(20), -- contb_receipt_dt

election_type varchar(20), -- election_tp

tran_id varchar(20), -- tran_id

foreign key (cand_id) references candidate,

foreign key (contbr_id) references contributor

);

-- 1. Create a view 'c_summary' summarizing campaign contributions,

-- with four attributes: cand_name, contbr_name, amount, and zip. You

-- may use this view in following problems.

-- 2. For each of the occupations "STUDENT", "TEACHER", and "LAWYER",

-- show the occupation, and average size (in dollars) of contribution

-- from contributors with that occupation.

-- 3. Let's focus on lawyers. For each candidate, show the candidate

-- name and total dollar amount of contributions to that candidate,

-- but only for contributions from those with occupation "LAWYER" and

-- zip codes that start with 939.

-- 4. Do lawyers list their occupations in lots of different ways?

-- List the distinct occupations that contain "LAWYER" within them?

-- 5. How many contributors have occupation "LAWYER"? Give just the count.

-- 6. How many contributors have an occupation that contains "LAWYER"?

-- Give just the count.

-- 7. Give occupation and number of contributors with that occupation for

-- all occupations that contain the string "LAWYER". Order by decreasing

-- count.

-- 8. The occupation "LAWYER FOR THE OPPRESSED" has an unusual name.

-- Look at all fields of the contributor table for contributors who

-- list their occupation this way.

-- 9. What is the average number of contributions per zip code? Use only

-- the first five digits of the zip code. Your result should be a single number.

-- 10. Looking at only the first five digits of the zip code, show the 20

-- zip codes with the highest number of contributors (not contributions).

-- Give the five-digit form of the zip code plus the number of contributors

-- with that zip. Order by descending number of contributors.

-- 11. For each distinct last name/zip combination, list the last name,

-- zip, and number of contributors with that last name/zip

-- combination, but only for last name/zip combinations that occur more

-- 6 times. List by decreasing count. HINT: use 'subtr' and

-- 'instr' to get the last name from the name field.

-- 12. For each contributor that made more than 75 contributions,

-- show the contributor name, total dollar amount contributed,

-- and number of contributions.