SQL Query - 2 queries involving COUNT() and the owner's of a certain trip

Question

My Databases look like so:

PEAK (NAME, ELEV, DIFF, MAP, REGION)
CLIMBER (NAME, SEX)
PARTICIPATED (TRIP_ID, NAME)
CLIMBED (TRIP_ID, PEAK, WHEN)

• PEAK gives info about the mountain peaks that the user is interested in. The table lists the name of each peak, it elevation(in ft), its difficulty level(on a scale of 1-5), the map that it is located on, and the region of the Sierra Nevada that it is located in.
• CLIMBER lists the members of club, and gives their name and gender.
• PARTICIPATED gives the set of climbers who participated in each of the various climbing trips. The number of participants in each trip varies.
• CLIMBED tells which peaks were climbed on each climbing trip, along w/ the data that each peak was climbed.

I need help w/ writing an SQL query for these 2 example scenarios:

• Which peaks have been climbed by Mark and Mary?
• On which trips did the total elevation gained by all participants exceed 500,000 feet?

This is what I have for the first query:

SELECT PEAK
FROM CLIMBED
WHERE TRIP_ID IN
    (SELECT TRIP_ID
     FROM PARTICIPATED
     WHERE NAME IN ('MARK','MARY')
     GROUP BY TRIP_ID
     HAVING COUNT(*) = 2
    );

The problem w/ this query is it only gives me all of the peaks that Mark and Mary have climbed during the same trip they took together. I need to somehow get the peaks that they both have climbed, but that they weren't together for.

For the second query I have no clue how to get the COUNT() of each peak that all the participants climbed during the specific TRIP_ID.

Answer 1

For your first question, you should just be able to remove the Having clause to get climbs where either Mark or Mary participated.

SELECT PEAK
FROM CLIMBED
WHERE TRIP_ID IN
    (SELECT TRIP_ID
     FROM PARTICIPATED
     WHERE NAME IN ('MARK','MARY')
     GROUP BY TRIP_ID
    );

Leaving the Having clause there means that you need both Mark and Mary to be in the Participated table for a particular trip for the trip_id to give you the 2 rows mandated by the having clause.

To get the peaks where one has climbed, but not the other, use your original query, but change the having clause to be 1:

SELECT PEAK
FROM CLIMBED
WHERE TRIP_ID IN
    (SELECT TRIP_ID
     FROM PARTICIPATED
     WHERE NAME IN ('MARK','MARY')
     GROUP BY TRIP_ID
     HAVING COUNT(*) = 1
    );

This works because given the where condition, Count(*) will be:

• 0 if nither of them climbed (hypothetical - the where condition will not allow this row to show)
• 1 if one of them climbed
• 2 if both of them climbed

Having clauses limit queries based on conditions after grouping, so usually are based on aggregates like Count(*), which will give you the number of records that are "contained" within each grouping.

The second question is a little tougher, but if I understand it correctly you should be able to use something like this:

SELECT climbed.trip_id, sum(peak.elev) 
FROM climbed LEFT JOIN participated ON climbed.trip_id = participated.trip_id
LEFT JOIN peak ON climbed.peak = peak.name
GROUP BY climbed.trip_id
HAVING sum(peak.elev) > 500000;

This works because using the left join, the elevation for each climber is duplicated; then when you sum for each trip, it adds the elevation for each climber.