Simple SQL Queries (3)

Simple SQL Queries (3)

What Have We Learned about SQL?

? Basic SELECT-FROM-WHERE structure ? Selecting from multiple tables ? String operations ? Set operations ? Aggregate functions and group-by queries ? Null values

CMPT 354: Database I -- Simple SQL (3)

2

Nested Subqueries

? When a select-from-where expression is insufficient to express a (complex) query, a subquery as a select-from-where expression can be nested within another query

? Common use of subqueries

? Test for set membership ? Set comparisons ? Set cardinality

CMPT 354: Database I -- Simple SQL (3)

3

Set Membership

? Find all customers who have both an account and a loan at the bank

select distinct customer_name from borrower

where customer_name in

(select customer_name from depositor )

? Find all customers who have a loan at the bank but do not have an account at the bank

select distinct customer_name from borrower

where customer_name not in

(select customer_name from depositor)

? Can be written without sub-queries (how?)

CMPT 354: Database I -- Simple SQL (3)

4

A More Complex Example

? Find all customers who have both an account and a loan at the Perryridge branch

select distinct customer_name from borrower, loan where borrower.loan_number = loan.loan_number and

branch_name = `Perryridge' and (branch_name, customer_name ) in

(select branch_name, customer_name from depositor, account where depositor.account_number =

account.account_number )

? Can you write a simpler query?

CMPT 354: Database I -- Simple SQL (3)

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download