SQL: You can download the table from this link: https://drive.google.com/file/d/
ID: 3589207 • Letter: S
Question
SQL:
You can download the table from this link:
https://drive.google.com/file/d/0B0kVn308bqFuZkFULTBSU1lzdEk/view?usp=sharing
1. List the contributions to each candidate from contributors that give "IRVINE" as their city. Show candidate name, and total contributed amount. Order the output by total contributed amount, descending.
NOTE: In the following questions, by "local", I mean in the zip codes that begin with 92602, 92606, 92614, 92618, or 92657.
........................................
2. For each of the local zip codes, and each of the candidates, show the number of contributions (if at least one contribution was made). Order by zip code and then number of contributions.
........................................
3. Who are the top 10 local contributors in terms of total amount contributed? Show the contributor names and the amount they spent.
........................................
4. Show date and amount for all contributions from 'BATTS, ERIC'. Order by amount, decreasing.
........................................
5. On average, how many contributions did each contributor make? Give a single number, rounded to one digit to the right of the decimal point.
........................................
Explanation / Answer
1. select c.name, sum(cn.amount) from candidate c,contribution cn where city='IRVINE' order by cn.amount desc;
2. select c.name, count(cn.contb_id) contributorno from candidate c,contribution cn,contributor cb where cb.contbr_id=cn.contb.id and cb.zip like '926%' order by cb.zip,contributorno;
3. select contb_id,amount from contribution where rownum<=10;
4. select cn.date,cn.amount from contribution cn,contributor cb where cn.contb_id=cb.contbr_id and cb.name='BATTS, ERIC' order by cn.amount desc;
According to chegg guidelines I am able to answer 4 questions only. Hope you understand
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.