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

);

----------------------------------------------------------------------------

-- Part 3 - write SQL queries on the three new tables

----------------------------------------------------------------------------

-- 11. Show occupation, and total contribution amount for all occupations.

-- Order output by decreasing total contribution amount, and limit output to

-- 20 rows.

-- 12. Show the month, year, and sum of all contributions for that month and year,

-- for all month, year combinations found in the data.

-- Order your output by total contributions. Round total contributions to nearest dollar.

-- Hint: month and year are substrings of the contribution date

-- 13. Show the candidate name and sum of contributions for all candidates.

-- Order by total contribution amount in decreasing order, round total contributions

-- to nearest dollar.

-- 14. Show the city and number of contributions of more than $5,000 for the 10 cities with the

-- most contributions > $5,000. Order output by number of > $5,000 contributions, decreasing.

-- 15. Show the average number of contributions per contributor. Show answer rounded

-- with one digit to the right of the decimal point.

-- Hint: did you know that this is legal in SQL: 'select 10/3;'? Also, you can write

-- 'select (10 + 0.0)/3;'. Try them out.

-- Extra Credit question!

-- Did any contributors contribute to more than one candidate?

-- Show the contributor ID, and the number of candidates to which the

-- contributor made contributions, for all contributors who

-- contributed to multiple candidates. Give output by number of

-- candidates contributed to, in decreasing order. Limit your output

-- to 20 rows.

----------------------------------------------------------------------------

-- Part 4 - write the three new tables to a single SQL file.

----------------------------------------------------------------------------

-- 16. set the SQLite output to be a file named 'campaign-normal.sql'

-- 17. output the candidate schema, and then all candidate rows as SQL

-- insert statements.

-- Hint: the SQLite .mode command allows you to select that you want

-- rows of a query to be output as SQL insert statements, and the

-- table name to be specified.

-- 18. output the candidate schema, and then all contributor rows as SQL

-- insert statements.

-- 19. output the contribution schema, and then all contribution rows as SQL

-- insert statements.

-- 20. set the SQL output so that it no longer goes to a file