CS4750 - Database Systems

POTD 3: SQL Join

Due 20-Feb-2024, 12pm/noon EST

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 at most 2 other students (max size=3) in this course.

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.

Expand / Collapse all

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

Grading rubric

[Total: 10 points]: Done (or provide evidence of your attempt, full or reasonable effort)

(5 points) — Providing evidence of your attempt, minimal effort

(-2.5 points) for 24 hours late (submitted after 20-Feb-2024 12pm EST, by 21-Feb-2024 12pm EST)
(-5 points) for 48 hours late (submitted after 21-Feb-2024 12pm EST, by 22-Feb-2024 12pm EST)

Submission

[optional] Take a selfie (or picture) of your team and submit it with your POTD
Submit all your SQL code
Include all team member names and computingIDs in the report.
You may do one of the following:
- take picture(s) of your POTD, or
- save your POTD as a .txt, .sql, or .pdf file — No Word document.
Each team submits only one copy.
Upload your report as a PDF to POTD 3 on Gradescope. Make sure you connect your partner to your group on Gradescope so that everyone receives credit.
Please verify that your POTD is accessible. Making your submission available to instructors and course staff is your responsibility; if we cannot access or open your file(s), we have to assign a zero grade. Be sure to test access to your file(s) before the due date.

CC-BY-NC-SA 4.0 license.
Last updated 2024-02-17 22:31