We are using SQLite3, so please make sure that the query you write will work wit

Question

We are using SQLite3, so please make sure that the query you write will work with that.

You can see the campaign-normal.sql here:

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

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

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

1) Create view statement is used to create given view. All the given three tables are joined to get the result.
CREATE VIEW c_summary
AS
SELECT can.name, con.name,cont.amount, con.zip
FROM candidate AS can
INNER JOIN contribution AS cont
ON cont.cand_id = can.can_id
INNER JOIN contributor AS con
ON cont.contbr_id = con.contbr_id;

2) Aggregate function AVG() is used to get the average amount along with group by clause.

SELECT con.occupation, AVG(cont.amount)
FROM contribution AS cont
INNER JOIN contributor AS con
ON cont.contbr_id = con.contbr_id
WHERE con.occupation IN ('STUDENT', 'TEACHER','LAWYER')
GROUP BY occupation;

3) Aggregate function SUM is used to get the sum of the contribution amount along with group by clause. In where clause LIKE is used to to get all the candidates who are contributed by lawyer.
SELECT can.name, SUM(cont.amount)
FROM candidate AS can
INNER JOIN contribution AS cont
ON cont.cand_id = can.can_id
INNER JOIN contributor AS con
ON cont.contbr_id = con.contbr_id
WHERE con.occupation = 'LAWYER'
AND cont.zip LIKE '939%';

4) In where clause LIKE is used to get all the occupation which contains LAWYER in them.
SELECT occupation
FROM contributor
WHERE occupation LIKE '%LAWYER%';

5) Aggregate function COUNT is used to get all the contributor.
SELECT COUNT(contbr_id)
FROM contributor
WHERE occupation = 'LAWYER';

6) Aggregate function count is used along with LIKE in where clause.
SELECT COUNT(contbr_id)
FROM contributor
WHERE occupation LIKE '%LAWYER%';

7)

SELECT con.occupation, COUNT(con.contbr_id)
FROM contributor AS con
WHERE con.occupation LIKE '%LAWYER%'
GROUP BY occupation;

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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