I would like to check that what I did is right and to know the ones I couldnt get.

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.

select occupation, amount from contributor, contribution where contributor.contbr_id = contribution.contbr_id order by amount desc limit 20;

-- 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

select substr(date, 4, 3) as month, substr(date, 8,2) as year, sum(round(amount)) from contribution group by date order by sum(round(amount));

-- 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.

select name, sum(round(amount)) from candidate, contribution where candidate.cand_id = contribution.cand_id group by name order by sum(round(amount)) desc;

-- 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.

select city, count(amount) from contributor, contribution where contributor.contbr_id = contribution.contbr_id and amount > 5000 group by city order by count(amount) desc limit 10;

-- 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.

select name, avg(round(amount,1)) from contributor, contribution where contributor.contbr_id = contribution.contbr_id group by name;

-- 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'

.output '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.

.schema candidate

-- 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 .output