CS4750 - Database Systems

Activity: SQL Joins

(no submission)

Purpose:

Be familiar with database queries
Practice writing and interpreting SQL
Get ready to work on homework assignment and course project

You may make a copy of a worksheet and complete this activity, or simply type your answers in any text editor.

You may work alone or with 1-2 other students in this course (max size=3).

Import alldbs.sql into your database server (if you have not done so). Write SQL queries to solve the following problems. You will need to consider tables: Boats, Sailors, Reserves.

Find the names of sailors who have reserved boat 103

SELECT sname 
FROM Sailors NATURAL JOIN Reserves 
WHERE bid = 103;

Find the colors of the boats reserved by Guppy.

SELECT DISTINCT color 
FROM Boats NATURAL JOIN Reserves NATURAL JOIN Sailors 
WHERE sname = "Guppy";

Find the names of sailors who have reserved a red boat

SELECT DISTINCT sname 
FROM Sailors NATURAL JOIN Reserves NATURAL JOIN Boats
WHERE color = "Red";

Find the names of the sailors who have not reserved a boat

SELECT sname 
FROM Sailors LEFT JOIN Reserves ON Sailors.sid = Reserves.sid
WHERE bid IS NULL;

-- or --

SELECT sname 
FROM Sailors LEFT OUTER JOIN Reserves ON Sailors.sid = Reserves.sid
WHERE bid IS NULL;

Find the sid's of the sailors who have reserved a boat whose name (the boat's name) begins with the letter "M"
```
SELECT DISTINCT Sailors.sid
FROM Sailors NATURAL JOIN Reserves NATURAL JOIN Boats
WHERE bname LIKE "M%";   
```
Find the number of times each sailor (sid) reserved a boat whose name (the boat's name) begins with the letter "M"
```
SELECT Sailors.sid, COUNT(Sailors.sid)
FROM Sailors NATURAL JOIN Reserves NATURAL JOIN Boats
WHERE bname LIKE "M%"
GROUP BY Sailors.sid; 
```

Expand / Collapse all

CC-BY-NC-SA 4.0 license.
Last updated 2026-09-13 14:36