Consider the following database that contains data about mountain climbers. The
ID: 3560249 • Letter: C
Question
Consider the following database that contains data about mountain climbers. The domains are self-explanatory (height in meters). The CD table shows mountains a clumber has climbed with date.
CLIMBER
MOUNTAIN
CD
WRITE each query in SQL or Relational Algebra
a) Retrieve the names of mountains climbed by people making less than 70k.
b) Retrieve the names of climbers who did not climb any mountains that are higher than 1100 meters.
c) Retrieve a summary that shows the average income for the climbers who were born at the same BirthYEAR.
SSN NAME INCOME BIRTHYEAR 100 TOM 50K 1950 120 ANN 100K 1950 110 ALEX 60K 1970 200 TIM 20k 1980Explanation / Answer
a) SELECT MNAME FROM CD WHERE SSN IN (SELECT SSN FROMCLIMBER WHERE INCOME < 70K );
b) SELECT NAME FROM CLIMBER WHERE WHERE SSN IN (SELECT SSN FROM CD WHERE MNAME IN (SELECT MNAME FROM MOUNTAIN WHERE HEIGHT >1100) );
C) SELECT BIRTHYEAR,AVG(INCOME) AS AVG_INCOME FROM CLIMBER GROUP BY BIRTHYEAR;