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 Californias 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 questions, and provide the extended relational algebra expression tree as part of the answer.

7. Sort the various maps according to the average height of the peaks on the map, and give the average height of the peaks on each map.

8. Which peaks have been climbed by Mark and Mary?

9. How many peaks remain unclimbed in each region?

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