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. 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

Answer 1

We will first find out the total number of Peaks in each region. This information we can get from "PEAK" table by grouping on REGION column.

QUERY A:

SELECT REGION, COUNT(NAME) AS TOTAL_PEAKS FROM PEAK GROUP BY REGION

Next, we will find out the number of PEAKS climbed in each region. This information we can get by joining "CLIMBED" and "PEAK" tables and grouping by using REGION column as given below.

QUERY-B:

SELECT REGION, COUNT(DISTINCT PEAK) AS CLIMBED_PEAKS FROM CLIMBED A INNER JOIN PEAK B ON A.PEAK=B.NAME GROUP BY REGION

Now we can use the above two query results to get the number of PEAKS that are UNCLIMBED as given by below final query.

Final Query:

SELECT (TOTAL_PEAKS-CLIMBED_PEAKS) AS UNCLIMBED_PEAKS, TOTAL_PEAKS, CLIMBED_PEAKS

FROM

(

(SELECT REGION, COUNT(NAME) AS TOTAL_PEAKS FROM PEAK GROUP BY REGION) A

INNER JOIN

(SELECT REGION, COUNT(DISTINCT PEAK) AS CLIMBED_PEAKS FROM CLIMBED A INNER JOIN PEAK B ON A.PEAK=B.NAME GROUP BY REGION)B

ON A.REGION=B.REGION

)