Question

The goal is to write several SQL queries that will answer questions over the database used by the imaginary Sierra Peak Climbing Club (SPCC), an organization whose members climb the mountain peaks of California’s Sierra Nevada mountain range.

The database maintained by the SPCC has four tables:

PEAK (NAME, ELEV, DIFF, MAP, REGION)

CLIMBER (NAME, SEX)

PARTICIPATED (TRIP_ID, NAME)

CLIMBED (TRIP_ID, PEAK, WHEN_CLIMBED)

The database tables have the following semantics:

• PEAK gives information about the mountain peaks that the SPCC is interested in. This table lists the name of each peak, its elevation, its difficulty level for climbers (on a scale of 1 to 5), the map that it is located on, and the region of the Sierra Nevada that it is located in.

• CLIMBER lists the SPCC membership, and gives their name and gender.

• PARTICPATED gives the set of climbers who participated in each of the various SPCC-sponsored climbing trips. The number of participants in each trip varies.

• CLIMBED tells which peaks were climbed on each of the SPCC-sponsored climbing trips, along with the date that each peak was climbed.

Write SQL queries that answer the following question.

1. Compute the average number of peaks scaled by the men in the club and by the women in the club.

The easiest way to get started with this assignment is to use an online SQLite tool. For example, https://sqliteonline.com/. In this tool, you can load the database and perform your queries.

Here is the data base file to upload to the website

https://files.fm/u/pbfqg3rd

Answer 1

Hi,

This can be solved by firstly calculating the count of number of trips for both men and women and then taking the average of that value.

SELECT AVG(CNT) FROM(
SELECT SELECT SEX,COUNT(*) AS CNT FROM CLIMBER C
INNER JOIN PARTICIPATED P
ON C.NAME=P.NAME
INNER JOIN CLIMBED CL
ON P.TRIP_ID=CL.TRIP_ID
GROUP BY SEX);