CS4750 - Database Systems

POTD 3: SQL Subqueries

Due 8-October-2026 10-October-2026, 10am

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.

Sailor database

Boats (bid, bname, color)
Sailors (sid, sname, rating, age)
Reserves (sid, bid, day)     -- note: no PK

Find the names of the sailors who have reserved a red or green boat. Do not repeat the names of the same sailors.
Find the names of the sailors who have reserved a red boat and a blue boat. Do not repeat the names of the same sailors.

Bank database

account (account_number, branch_name, balance)
borrower (customer_name, loan_number)            -- note: no PK
branch (branch_name, branch_city, assets)
depositor (customer_name, account_number)      -- note: no PK
loan (loan_number, branch_name, amount)

Find the names of all customers who have a loan, an account, or both from the bank.
Find the names of all customers who have a loan at the Perryridge branch, but no account at any branch in the bank. Do not repeat the name of the same customer.

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 8-Oct-2026 10am, by 9-Oct-2026 10am)
(-5 points) for 48 hours late (submitted after 9-Oct-2026 10am, by 10-Oct-2026 10am)

(-2.5 points) for 24 hours late (submitted after 10-Oct-2026 10am, by 11-Oct-2026 10am)
(-5 points) for 48 hours late (submitted after 11-Oct-2026 10am, by 12-Oct-2026 10am)

Submission

[optional] Take a selfie (or picture) of your team and submit it with your POTD
Include all team member names and computingIDs in the report.
Submit your SQL queries
You may do one of the following:
- Write on the paper and take picture(s) of your POTD, or
- Type and save your POTD as a .sql, .pdf, or .txt file. — No Word document.
Each team submits only one copy.
Upload your file(s) 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 2026-10-04 19:52