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

1. Find the names of the sailors who have reserved a red or green boat. Do not repeat the names of the same sailors.
   [sample solution]

   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');  

2. 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.
   [sample solution]

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

1. Find the names of all customers who have a loan, an account, or both from the bank.
   [sample solution]

   (SELECT  customer_name 
   FROM    depositor) 
   UNION
   (SELECT customer_name
   FROM borrower);  

2. 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.
   [sample solution]

   (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

---

Copyright © 2024 Upsorn Praphamontripong

Released under the CC-BY-NC-SA 4.0 license.

Last updated 2024-02-20 20:48