csH2019paper

Explore

section 2

q-13

You can run and edit code on this page to explore the question and better understand the concepts

aggregate functions,

group by,

column alias

testing

⁠

NOTE: how to use date formats in SQL-lite

date('YYYY-MM-DD')

select * from bookings where lessonDate = date('2019-01-01')

⁠

A charity called Animal Help has a website that allows people to raise funds through sponsorship. Details entered are stored in a relational database. Fundraisers can see their total donations from all of their sponsors and Animal Help can view the funds being raised on their behalf. The database has the following tables.

table design

Fundraiser Donation Sponsor

-------------+---------------+-----------

fundraiserID donationID sponsorID

name fundraiserID* name

email sponsorID* email

date

amount

fundraiser

fundraiserID name email

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

1 Frazier Allston allston@fastmail.co.uk

2 Axel Ginie aginie@freemail.com

3 Lucilia Hurworth lhurworth@cmu.edu

donation

donationID fundraiserID sponsorID donationDate amount

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

DON0001 1 4 2019-05-01 25

DON0002 2 2 2019-05-05 10

DON0003 3 3 2019-05-12 10

DON0004 2 2 2019-05-14 25

DON0005 1 1 2019-05-19 5

DON0006 1 3 2019-05-19 10

DON0007 1 2 2019-05-19 15

DON0008 3 1 2019-05-20 15

sponsor

sponsorID name email

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

1 Mata Dwyer mdwyer@printfriendly.com

2 Evy Reymers ereymers@yolasite.com

3 Bob Towey btowey@behance.net

4 John Walker jwalker@clickbank.net

⁠

part c

Animal Help is a charity. They want to produce a report showing the average donation made to each fundraiser.

Create an SQL statement to produce the output shown below.

fundraiserID Average donation (£)

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

1 13.75

2 17.5

3 12.5

(edit and run the code in queryc.sql)

⁠

part d

Animal Help requires a report on the largest single donation made to each fund raiser. Like this:

name Largest donation (£)

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

Axel Ginie 25

Frazier Allston 25

Lucilia Hurworth 15

The following code is executed but does not match the expected output.

Identify 3 errors in the code:

SELECT name,COUNT(amount)

FROM Fundraiser,Donation

WHERE Fundraiser.fundraiserID = Donation.fundraiserID

(You can copy and paste the code into queryd.sql to test it)

-- describe the three errors here

⁠

code

⁠

Loading…⁠

⁠

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.