Please help me write the SQL query for the following questions. We are using SQL

Question

Please help me write the SQL query for the following questions. 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 and download 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.

You will see two C files: cache_sim.c and random_cache.c. Program cache_sim.c models the use of a cached data store. Program random_cache.c implements a cache with a random "cache replacement policy". Read the program to see how it works. The Makefile shows how to compile it. Then answer the following questions f. What is the hit ratio of the cache? (The hit ratio is the fraction of accesses for which there is a cache hit. It can be computed as the number of cache hits divided by the number of accesses, which is the sum of cache hits and cache misses.) You will need to add a print statement to cache sim.c to get this g. The cache size in the program is 10, and the size of the data store is 1000 (look at the constants defined near the top of cache_sim.c). Run the program with cache sizes of 3, 5, 10, 20, and 50. What is the hit ratio in each case? (Note: to run these tests you can either make cache size a command-line argument, or simply edit the CACHE_SIZE value and recompile.) Reset the cache size to 10 after these tests h. Look at the main program. It makes a bunch of memory accesses, always accessing the value at address j. After every access, it modifies j. It does this by randomly selecting a value out of array move_size1, and then adding that value to the current value of j. Within the loop over i in the main program of cache_sim.c, modify move_size1 to move_size2, and rerun the program. What is the new hit ratio value? i. Suppose that in 10,000 accesses, you get 6500 cache hits, and 3500 cache misses. Suppose also that time to perform an access is 0.1 ms if a cache hit, but 10 ms if a cache miss. What is the average time for an access? (Give answer in ms.) j. Repeat the same problem, but assume a poorer caching algorithm is used, and you have 4100 cache hits, and 5900 cache misses. What is the average time for an access in this case? (Give answer in ms.)

Explanation / Answer

note: wait for 15 minutes. I will send all the queries..

1. SELECT dc.name as cand_name, cb.name as contbr_name, ct.amount as amount, cb.zip as zip

FROM dbo.candidate dc

INNER JOIN dbo.contribution ct ON ct.cand_id = dc.cand_id

INNER JOIN dbo.contributor cb ON cb. contbr_id = ct.contbr_id

2.

select

dc.name as Name,

sum(db.amount) as amount

FROM dbo.candidate dc

JOIN dbo.contribution db

ON dc.cand_id = db.cand_id

JOIN dbo.contributor dt

ON db.contbr_id = dt.contbr_id

where dt.occupation like 'LAWYER'

AND DT.zip LIKE '939%'

GROUP BY dc.name

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

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