Please help me write the SQL query for the following questions. I had asked this

Question

Please help me write the SQL query for the following questions. I had asked this question before, but got a wrong answer. We are using SQLite3, so please make sure that the query you write will work with that. I already answered the first question.

You can see the campaign-normal.sql here:

https://drive.google.com/file/d/1OjJ754IzmXXVszTFUBG2AXtpL2VZz1wA/view?usp=sharing

These are the schemas of the tables:

CREATE TABLE candidate(
cand_id varchar(12) primary key,
name varchar(40)
);

CREATE TABLE contributor(
contbr_id integer primary key,
name varchar(40), city varchar(40), state varchar(40),
zip varchar(20),
employer varchar(60),
occupation varchar(40));

CREATE TABLE contribution(
contb_id integer primary key,
cand_id varchar(12),
contbr_id varchar(12),
amount numeric(6,2),
date varchar(20),
election_type varchar(20),
tran_id varchar(20),
foreign key (cand_id) references candidate,
foreign key (contbr_id) references contributor);

-------------------------------------------------------------------------------------
-- The following queries are based on the normalized CA campaign
-- contribution data. (campaign-normal.sql)

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

-- 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 candidate.name as cand_name, contributor.name as contbr_name, contribution.amount as amount, contributor.zip as zip from candidate left join contributor left join contribution;

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

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

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

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

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

Explanation / Answer

Hi,

All the queries are based on Subquery and function substr and instr. This will also help you to know the inline view

1.

I have added column on which it need to join each other.

create view c_summary

as select candidate.name as cand_name, contributor.name as contbr_name, contribution.amount as amount, contributor.zip as zip from candiadte cd, contributor cr, contribution ct

where cd.cand_id=ct.cand_id and ct.contbr_id=cr.contbr_id

2. Use of like operator

select cd.name, ct.amount

from candiadte cd, contributor cr, contribution ct

where cd.cand_id=ct.cand_id and ct.contbr_id=cr.contbr_id

and Occupation='LAWYER' and zip like '939%'

3. Use of subquery and inline views - Question asked average contribution per zip - so I have shown there, zip and the corresponding average

select cz.zip,Avg(cz.zipCon) from

(Select cs.zip,count(cs.zipcode) as zipCon from

( select zip, substr( zip, 1, 5 ) as zipcode

FROM contributor cr,

contribution ct

WHERE ct.contbr_id=cr.contbr_id

) cs

) cz

4. Use of group by and inline view

Select cz.zip, cz.zipCOunt from

(Select cs.zip , count(cs.zipCode) zipCount from

(select zip, substr( zip, 1, 5 ) as zipcode FROM contributor cr) cs

group by cs.zip ) cz

order by zipCOunt desc

LIMIt 20

5. Use of Substr and Instr

Select cs.lastName, cs.zip from

(SELECT distinct substr( name, instr(name,' ')) lastName, zip, count(*) cont

from contributor

group by lastName, zip

) cs

where cs.cont>6

6. Similary you can write query for the this one. Join contributor and contribution, get all three the column. use count to get the numbers.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.