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

¨C Test for set membership

¨C Set comparisons

¨C 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