Joins Test Page

Overview

These tests show how three relations are joined together in various ways. The three relations show how sailors of various skills make reservatons to rent different boats at the club.

sailors (sid, sname, rating, age) - int, string, int, real
boats (bid, bname, bcolor) - int, string, string
reserves (sid, bid, date) - int, int, string

The joins demonstrated include sort-merge, nested-loops, and index nested-loops. Sorting, duplicate elimination, projection and selection are all demonstrated as well. All the plans are created "by hand" also (the optimizer, parser and planner are not used).

Query 1

Query: Find the names of sailors who have reserved boat number 1.

SELECT S.sname, R.date
FROM Sailors S, Reserves R
WHERE S.sid = R.rid AND R.bid = 1

This query uses a sort merge join with sailors as the outer relation and reserves as the inner relation. The sailor name and reservation date are projected at the end of the join.

Query 2

Query: Find the names of sailors who have reserved a red boat. and return then in alphabetical order.

SELECT S.sname
FROM Sailors S, Boats B, Reserves R
WHERE S.sid = R.rid AND R.bid = B.bid AND B.color = 'red'
ORDER BY S.sname

Plan used:

Sort (Pi(sname) (Sigma(B.color='red') |><| Pi(sname, bid) (S |><| R)))

Two nested loops joins are used, with the result of the sailors & reserves join forming the outer input of the second join (boats being the inner relation).

Tests filescan, projection, index selection, simple nested-loop joins, and sorting.

Query 3

Query: Find the names of sailors who have reserved a boat.

SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.rid

Tests filescan, projection, and sort-merge join.

Query 4

Query: Find the names of sailors who have reserved a boat. and print each name once.

SELECT DISTINCT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.rid

Tests filescan, projection, sort-merge join and duplication elimination.

Query 5

Query: Find the names of old sailors or sailors with a rating less than 7, who have reserved a boat, (perhaps to increase the amount they have to pay to make a reservation.)

SELECT S.sname, S.rating, S.age
FROM Sailors S, Reserves R
WHERE S.sid = R.rid and (S.age > 40 OR S.rating < 7)

Tests filescan, multiple selection, projection, and sort-merge join.

Query 6

Query: Find the names of sailors with a rating greater than 7 who have reserved a red boat, and print them out in sorted order.

SELECT S.sname
FROM Sailors S, Boats B, Reserves R
WHERE S.sid = R.rid AND S.rating > 7 AND R.bid = B.bid
AND B.color = 'red'
ORDER BY S.name

Plan used:

Sort(Pi(sname) (Sigma(B.color='red') |><| Pi(sname, bid) (Sigma(S.rating > 7) |><| R)))

Tests filescan, multiple selection, projection, sorting, and index-nested-loop join.

Query 7

Query: Find the names of sailors who have reserved a boat.

SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid

Tests filescan, projection, and index-only index-nested-loop join.

Joins Page
Back to the Components Page
Back to the Minibase Home Page