CREATE TABLE candidate ( cand_id varchar(12) primary key, -- cand_id name varcha

Question

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.

Explanation / Answer

Below are the MySQL Scripts that created for each questions asked in requirement

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

CREATE VIEW c_summary AS
(SELECT c.name as cand_name, ctr.name as contbr_name, con.amount, ctr.zip
FROM candidate as c
inner join contribution as con on con.cand_id = c.cand_id
inner join contributor as ctr on ctr.contbr_id = con.contbr_id);

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

SELECT ctr.occupation as Occupation, AVG(con.amount) as AverageContribution
from contributor as ctr
inner join contribution as con on con.contbr_id = ctr.contbr_id
Group by ctr.occupation;

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

SELECT c.name as cand_name, SUM(con.amount) as TotalAmountContribution
FROM candidate as c
INNER JOIN contribution as con on con.cand_id = c.cand_id
INNER JOIN contributor as ctr on ctr.contbr_id = con.contbr_id
WHERE UPPER(ctr.occupation) like '%LAWYER%'
AND ctr.zip like '939%'
GROUP BY c.name;

Question 4

Do lawyers list their occupations in lots of different ways? List the distinct occupations that contain "LAWYER" within them?

SELECT DISTINCT Occupation FROM contributor
WHERE Occupation LIKE '%LAWYER%';

Question 5

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

SELECT Count(1) FROM contributor
WHERE Occupation LIKE '%LAWYER%';

Question 6

How many contributors have an occupation that contains "LAWYER"? Give just the count.

SELECT Count(1) FROM contributor
WHERE Occupation = 'LAWYER';

Question 7

Give occupation and number of contributors with that occupation for all occupations that contain the string "LAWYER". Order by decreasing count.

SELECT UPPER(ctr.occupation) as Occupation, Count(ctr.name) as NumberOfContributors
from contributor as ctr
inner join contribution as con on con.contbr_id = ctr.contbr_id
Where Occupation LIKE '%LAWYER%'
Group by ctr.occupation DESC;

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

SELECT contbr_id
,name
,city
,state
,zip
,employer
,occupation
FROM contributor
Where Occupation = 'LAWYER FOR THE OPPRESSED';

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

SELECT LEFT(ctr.zip, 5) as ZipCode, AVG(con.amount) as AvergaeContribution
FROM contributor as ctr
inner join contribution as con on con.contbr_id = ctr.contbr_id
Group by LEFT(ctr.zip, 5);

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

Select c.ZipCode, c.Contributors FROM
(SELECT LEFT(ctr.zip, 5) as ZipCode, ctr.name as Contributors, sum(con.amount) as contributionamount
FROM contributor as ctr
inner join contribution as con on con.contbr_id = ctr.contbr_id
group by LEFT(ctr.zip, 5), ctr.name) as c
Order by c.contributionamount Desc
LIMIT 20 OFFSET 0;

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

SELECT substr(ctr.name,instr(ctr.name,' ')+1) as LastName, ctr.zip as ZipCode, count(ctr.name) as ContributorCount
FROM contributor as ctr
inner join contribution as con on con.contbr_id = ctr.contbr_id
group by ctr.name, ctr.zip, substr(ctr.name,instr(ctr.name,' ')+1)
having count(substr(ctr.name,instr(ctr.name,' ')+1)) > 6;

Question 12

For each contributor that made more than 75 contributions, show the contributor name, total dollar amount contributed, and number of contributions.

SELECT ctr.name as ContributorName,
SUM(con.amount) as TotalContributionAmount,
COUNT(con.contb_id) as ContributionCount
FROM contributor as ctr
INNER JOIN contribution as con on con.contbr_id = ctr.contbr_id
Group by ctr.name
having count(con.contb_id) > 75;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects