ERD and SQL query help ?

Question

Can you tell me if this is done right or if I need to improve some of them I have a hard time understanding the query's when there is an associative entity.

List all donors

SELECT* from Donor;

List the first and last names of all donors

SELECT dfname, dlname
FROM donor

List the phone numbers of donors number 106 and 125

SELECT dphone
FROM DONOR 
WHERE dphone = “106” 
AND dphone = “125”; 

List the amount given by each donor for each year

SELECT year 
FROM YEAR IN (
SELECT donor, amount
FROM GIFT);

List the donors who have made a donation every year

SELECT dfname, dlname 
FROM DONOR
WHERE IN( SELECT * FROM 
YEAR)
AND amount != null;

List the names of donors who live in Georgia or North Carolina

SELECT dfname, dlname
FROM donor
WHERE state = “GA” 
OR  state = “NC”;

List the names of donors whose last name is Williams and who live in Athens, GA

SELECT dfname, dlname 
FROM donor 
WHERE dlname = “Williams”
AND city = “Athens”
AND state = “GA”;

Id like to thank every one who helped and I wish I could give everyone a green check mark as all answers entered provided a solution. I appreciate the help and maybe one day I can contribute to a question you may have in the future.

Answer 1

corrected queries:

List the phone numbers of donors number 106 and 125

SELECT dphone
FROM DONOR 
WHERE donor in (106, 125)

List the amount given by each donor for each year

SELECT donor, year, sum(amount) as amount
FROM gift
group by donor, year

List the donors who have made a donation every year

This should be clarified: What is mean "every year"?

Answer 2

List the donors who have made a donation every year

    Select dfname, dlastName 
     from
    Donor d
    INNER JOIN 
    (SELECT Donor, count(year) cyear
    FROM DONOR d
    INNER JOIN gift g
    on d.donor = g.donor
    GROUP BY donor
) donorYear
    ON d.donor = donorYear.donor
    INNER JOIN 
    (SELECT count(year) cyear
    from Year) years
    on years.cyear = donarYear.cyear

Answer 3

In your ERD you might want to put a primary key on your GIFT table. If there's only one gift per donor per year then the primary key could be DONOR and YEAR. Otherwise adding an artificial key would be a good idea.

List the phone numbers of donors number 106 and 125 - I think you want to use a different field in the WHERE clause. You're supposed to find the phone number of these donors (dphone), but the donor ID is not the phone number.

List the donors who have made a donation every year - I don't think your query will do what you want. A way to approach this would be to produce a list of donors and the years that they gave, then find the years where they didn't give. Donors who don't have any entries in the "years they didn't give" list would be the ones that gave every year. HINT: involves using outer joins.

Share and enjoy.

Answer 4

Donors who have made a donation every year (caveat: not tested):

SELECT dfname, dlname
FROM   DONOR
WHERE NOT EXISTS 
   (SELECT * FROM YEAR WHERE NOT EXISTS 
      (SELECT * FROM GIFT WHERE year = YEAR.year AND donor = DONOR.donor));

(Assuming that a row in GIFT means a donation was made, i.e. that amount > 0 always)