CS4750 - Database Systems

Activity: SQL Subqueries

(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 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.

Expand / Collapse all

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.

SELECT DISTINCT sname
FROM    Sailors NATURAL JOIN Reserves NATURAL JOIN Boats
WHERE   color='Red' OR color='Green';  

-- another solution -- 

(SELECT DISTINCT sname
FROM    Sailors NATURAL JOIN Reserves NATURAL JOIN Boats
WHERE   color='Red')
UNION
(SELECT sname 
FROM    Sailors NATURAL JOIN Reserves NATURAL JOIN Boats
WHERE   color='Green');

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.

(SELECT DISTINCT sname 
 FROM Sailors NATURAL JOIN Reserves NATURAL JOIN Boats 
 WHERE color='Red') 
INTERSECT
(SELECT sname 
 FROM Sailors NATURAL JOIN Reserves NATURAL JOIN Boats 
 WHERE color='Blue');  

-- another solution --

SELECT  DISTINCT sname
FROM    Sailors NATURAL JOIN Reserves NATURAL JOIN Boats
WHERE   color='Red' 
    AND sname IN 
        (SELECT sname 
        FROM    Sailors NATURAL JOIN Reserves NATURAL JOIN Boats
        WHERE   color='Blue');  
        
-- another solution --

SELECT DISTINCT (sname)
FROM Boats B1, Boats B2, Reserves R1, Reserves R2, Sailors S
WHERE R1.bid = B1.bid AND R2.bid = B2.bid AND
      B1.color = 'red' AND B2.color = 'blue' AND
      R1.sid = R2.sid AND R1.sid = S.sid;

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.
```
(SELECT  customer_name 
FROM    depositor) 
UNION
(SELECT customer_name
FROM borrower);  
```

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.

(SELECT  DISTINCT customer_name 
FROM    borrower NATURAL JOIN loan
WHERE   loan.branch_name='Perryridge')
EXCEPT
(SELECT  customer_name 
FROM     depositor);

-- another solution --

SELECT  DISTINCT customer_name 
FROM    borrower NATURAL JOIN loan
WHERE   loan.branch_name='Perryridge' 
    AND customer_name NOT IN
                        (SELECT  customer_name 
                        FROM     depositor);

Expand / Collapse all

CC-BY-NC-SA 4.0 license.
Last updated 2025-09-28 20:48