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.
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.
Copyright © 2024 Upsorn Praphamontripong
Released under the
CC-BY-NC-SA 4.0 license.