Applied Database Systems - Tutorial 3

A proposed solution to last weeks exercise can be found here. This solution assumes that you saved the Munro Magic load file in your home directory. Please let me know if you have any questions regarding that exercise as you will need relation mountains for this lab session.

In last week's tutorial, we worked through some basic SQL queries, but in every case the query to use was suggested in the text. This week the exercises are all posed as problems, where you have to work out what query to perform in order to get the correct results. Don't worry if you don't get to the end of these - just do as many as you can in the time available. We will publish some example answers by the end of the week.

Note: There are always many ways of designing an SQL query to produce a particular result, so there aren't unique correct answers to these questions. Some of these queries will be more legible (or in some cases more efficient) than others, however.

 Introduction

In this lab session we will use data about mountains and hikers. Please make sure that you all have an instance of the mountains relation in your database. You can follow the proposed solution to last weeks exercise to generate one. Furthermore, load data into relation hikers which is defined as before. The file hikers.load contains a list of hikers in the following format: hid|hname|age|skill.

Now generate a simplified relation climbs as follows:

CREATE TABLE climbs (
         hid int NOT NULL,
         mid int NOT NULL,
         FOREIGN KEY (hid) REFERENCES hikers(hid),
         FOREIGN KEY (mid) REFERENCES mountains(mid),
         PRIMARY KEY (hid, mid)
);

Data for relation climbs is given in climbs.load. The format of the file is hid|mid.

Create a view munros that list all the Munros in mountains. The view should have the same attributes as mountains except for attribute type.

 Questions
Quesion 1

Find the names of all the hikers we have data for (in descending order).

Click here to reveal example output from such a query.

Example result for such a query:

Click here to hide example output

 hname
-------
 Tom
 ...
 Fiona
(7 rows)
Quesion 2

Find out how many Munros over 4000ft there are, and list their names and height (in feet) in descending order of their height.

Click here to reveal example output from such a query.

Example result for such a query:

Click here to hide example output

           mname            | heightf
----------------------------+---------
 Ben Nevis                  |    4409
 ...
 Carn Mor Dearg             |    4003
(9 rows)
Quesion 3

List the name of hikers that have climbed Munros having a rating that is below the hikers skill.

Click here to reveal example output from such a query.

Example result for such a query:

Click here to hide example output

 hname
-------
 Jane
 Paul
(2 rows)
Quesion 4

List the IDs and names of Munros that have the word "Dearg" in their name, using SQL's "LIKE" operator. ("Dearg" means "red" in Gaelic.)

Click here to reveal example output from such a query.

Example result for such a query:

Click here to hide example output

                mname                | mid
-------------------------------------+-----
 Beinn Dearg [Blair Atholl]          | 582
...
 Stob Dearg                          | 985
(11 rows)
Quesion 5

The names of the Munros are not unique. To avoid ambiguities, the non-unique names have information about thier region appended (see for example Beinn Dearg in the previous question). The same is not true for all the mountains. Write a query that produces a list of all the non-unique mountain names in one column and the number of times it occurs in another column.

Click here to reveal example output from such a query.

Example result for such a query:

Click here to hide example output

    mname    | count
-------------+-------
 An Caisteal |     2
 Stob Dubh   |     2
(2 rows)
Quesion 6

Find the IDs and names of all the Munros that have been climbed by at least four of the hikers (ordered by ID).

Click here to reveal example output from such a query.

Example result for such a query:

Click here to hide example output

 mid  |                mname
------+-------------------------------------
  505 | A' Bhuidheanach Beag
  ...
 1007 | Tom na Gruagaich
(147 rows)
Quesion 7

Rank the hikers by the number of different Munros they have climbed.

Click here to reveal example output from such a query.

Example result for such a query:

Click here to hide example output

 hname | count
-------+-------
 Tom   |   283
 ...
 Sue   |    50
(7 rows)
Quesion 8

Rank the Munros by the number of different hikers that have climbed them.

Click here to reveal example output from such a query.

Example result for such a query:

Click here to hide example output

                mname                | count
-------------------------------------+-------
 Stob Coire Easain                   |     5
 ...
 Ben MacDui                          |     2
(283 rows)
Quesion 9

List the names of the hikers that have climbed all the Munros.

Click here to reveal example output from such a query.

Example result for such a query:

Click here to hide example output

 hname
-------
 Tom
(1 row)
Quesion 10

Who is the oldest hiker to have climbed Ben Nevis?

Note: You may use the LIMIT n clause to limit the number of tuples returned by a query.

Click here to reveal example output from such a query.

Example result for such a query:

Click here to hide example output

 hname | age
-------+-----
 Sue   |  32
(1 row)
Quesion 11

Create a VIEW which lists the Munros that Jane has not climbed yet and SELECT * FROM that VIEW.

Click here to reveal example output from such a query.

Example result for such a query:

Click here to hide example output

 mid  |                mname
------+-------------------------------------
  506 | A' Chailleach [Fannaich region]
  ...
 1005 | Tom Buidhe
(143 rows)
  
Quesion 12

List the Munros that have been climbed by Mike but not by Paul.

Click here to reveal example output from such a query.

Example result for such a query:

Click here to hide example output

                mname
-------------------------------------
 A' Bhuidheanach Beag
 ...
 Tom na Gruagaich
(142 rows)
Quesion 13

Modify the previous query in such a way that it only lists those Munros that have a rating that is greater or equal to Pauls's skill.

Click here to reveal example output from such a query.

Example result for such a query:

Click here to hide example output

                mname
-------------------------------------
 A' Bhuidheanach Beag
 ...
 Tom na Gruagaich
(111 rows)
Quesion 14

List the average height (in feet and metres) for Munros within the Ordnance Survey grid square 'NN'.

Click here to reveal example output from such a query.

Example result for such a query:

Click here to hide example output

      avg_heightm      |      avg_heightf
-----------------------+-----------------------
 1033.9197080291970803 | 3392.1532846715328467
(1 row)
Quesion 15

List the average height (in metres) of Munros within the Ordnance Survey grid square 'NN' that have been climbed by the hiker who has climbed the least number of Munros within that grid square.

Click here to reveal example output from such a query.

Example result for such a query:

Click here to hide example output

          avg
-----------------------
 1067.8750000000000000
(1 row)
Quesion 16

Rank the hiker according to the difference between the highest and smalles Munro they have climbed.

Click here to reveal example output from such a query.

Example result for such a query:

Click here to hide example output

 hname | diff
-------+-----
 Tom   |  429
 Mike  |  381
 Jane  |  380
 Henry |  342
 Paul  |  282
 Fiona |  262
 Sue   |  244
(7 rows)
End of Tutorial 2. Please send any corrections or suggestions to Heiko Müller.