SQL

1
SQL

• Additional Slide
• For
• CS 4347

2
Basic

• SQL can be regarded as relational algebra with a
  more readable format.
• select A1, A2, ... from r1, r2, ... where P
• The result of an SQL query is a relation.

3
Running Example

• branch (bname, city, assets)
• customer (cname,city)
• account (anum, bname, balance)
• loan (lnum, bname, amount)
• depositor (cname, anum)
• borrower (cname, lnum)

4
Select Clause

• corresponds to projection in relational
  algebra.
• Find the names of all branches that issued
  loans select bname from loan
• An asterisk denotes all attributes.
• select from loan

5
Select Clause (Cont.)

• SQL allows duplicates in query results (different
  from algebra).
• To eliminate duplicates, use distinct.
• Find the names of all branches that issued loans.
• select distinct bname from loan

6
Select Clause (Cont.)

• can contain arithmetic expressions involving ,
  , ?, /, etc.
• select lnum, bname,
  amount ? 100 from loan
• Returns a relation same as loan, except that the
  attribute amount is multiplied by 100.

7
Where Clause

• Corresponds to selection in relational algebra.
  
• The find the loan number of loans at the
  Perryridge branch that have amounts greater than
  1200.
• select lnum from loan where bname
  Perryridge and amount gt 1200

8
From Clause

• corresponds to Cartesian product in relational
  algebra.
• Find the Cartesian product borrower x
  loan select ? from borrower, loan

9
From Clause (cont.)

• For each customer that borrowed a loan from bank
  Perryridge, display her/his names, and the loan
  number/amount of the loan involved.
• select cname, borrower.lnum, amount from
  borrower, loan where borrower.lnum loan.lnum
  and bname Perryridge

10
Running Example

• branch (bname, city, assets)
• customer (cname,city)
• account (anum, bname, balance)
• loan (lnum, bname, amount)
• depositor (cname, anum)
• borrower (cname, lnum)

11
Rename Operation

• Find each loan-customers, display her/his name,
  and the number and amount of her/his loan. Rename
  column lnum as loan-id.select cname,
  borrower.lnum as loan-id, amountfrom borrower,
  loanwhere borrower.lnum loan.lnum

12
Tuple Variables

• For each loan-customer, find her/his name, and
  the amount of the loan.
• select B.cname, L,amount
• from borrower as B, loan as L
  where B.lnum L.lnum

13
Tuple Variables (cont.)

• Find the names of all branches that have greater
  assets than some branch located in Brooklyn.
• select distinct T.bname from branch as
  T, branch as S where T.assets gt S.assets and
  S.city Brooklyn

14
Ordering the Output Tuples

• List in alphabetic order the names of all
  customers having a loan in Perryridge branch
• select distinct cname from borrower,
  loan where borrower.lnum loan.lnum and bname
  Perryridge order by customer-name
• We may specify desc for descending order.
• E.g. order by customer-name desc

15
Set Operations

• Union, intersect, and except correspond to the
  relational algebra operations ??????and ?,
  respectively.
• Each of the above operations automatically
  eliminates duplicates
• to retain duplicates, use union all, intersect
  all and except all.

16
Running Example

• branch (bname, city, assets)
• customer (cname,city)
• account (anum, bname, balance)
• loan (lnum, bname, amount)
• depositor (cname, anum)
• borrower (cname, lnum)

17
Set Operations (cont.)

• Find the names of all customers who have a loan,
  an account, or both
• (select cname from depositor) union (select
  cname from borrower)
• Find the names of all customers who have both a
  loan and an account.
• (select cname from depositor) intersect (selec
  t cname from borrower)
• Find the names of all customers who have an
  account but no loan.
• (select cname from depositor) except (select
  cname from borrower)

18
Aggregate Functions

• avg average value min minimum value max
  maximum value sum sum of values count
  number of values

19
Running Example

• branch (bname, city, assets)
• customer (cname,city)
• account (anum, bname, balance)
• loan (lnum, bname, amount)
• depositor (cname, anum)
• borrower (cname, lnum)

20
Aggregate Functions (cont.)

• Find the average account balance at the
  Perryridge branch.
• select avg (balance) from account where bname
  Perryridge
• Find the number of tuples in the customer
  relation.
• select count () from customer
• Find the number of depositors in all banks.
• select count (distinct cname) from depositor

21
Group By

• Corresponds to the group-by operation in
  relational algebra.
• Find the average balance for each branch.
• select bname, avg(balance) from account group
  by bname
• Find the number of accounts in each branch.
• select bname, count(anum) from account group
  by bname
• Find the number of depositing transactions in
  each branch.
• select bname, count(cname) from depositor,
  account where depositor.anum
  account.anum group by bname

22
Important Constraint in Group-by

• Every attribute after select must appear in
  either the group-by clause or a aggregate
  function.
• The following is incorrect.
• select branch-name, account-number
• from account
• group by branch-name
• A correct query.
• select branch-name, count(account-number)
• from account
• group by branch-name

23
Running Example

• branch (bname, city, assets)
• customer (cname,city)
• account (anum, bname, balance)
• loan (lnum, bname, amount)
• depositor (cname, anum)
• borrower (cname, lnum)

24
Having Clause

• Find the names of branches where the average
  account balance is more than 1,200.
• select bname, avg (balance) from
  account group by bname having avg (balance) gt
  1200
• Having is applied after group-by.
• It operates on groups.
• Where is applied before group-by.
• It operates on tuples.

25
Mis-used Where

• Find the names of branches where the average
  account balance is more than 1,200.
• A wrong answer
• select bname, avg (balance) from
  account where avg (balance) gt 1200
• group by bname
• No aggregate function in where!

26
Nested Sub-queries

• SQL provides a mechanism for nesting sub-queries.
• Find all customers who have both an account and a
  loan.
• select distinct cname from borrower where
  cname in (select cname from depositor)
• Find customers who have a loan but not any
  account.
• select distinct cname from borrower where
  cname not in (select cname from depositor)

27
Nested Sub-queries

• Find all branches that have greater assets than
  all branches located in Brooklyn.
• select distinct T.bname from branch T where
  T.assets gt (select max(assets)
• from branch S
• where S.city
  Brooklyn)
• A wrong answer.
• select distinct T.bname from
  branch T where T.assets gt (select assets
• from branch S
• where S.city
  Brooklyn)

28
Some Clause

• Find all branches that have greater assets than
  some branch located in Brooklyn.
• select distinct T.bname from branch as T,
  branch as S where T.assets gt S.assets and
  S.city Brooklyn
• Same query using some clause
• select bname from branch where assets gt
  some (select assets from branch
  where city Brooklyn)

29
Running Example

• branch (bname, city, assets)
• customer (cname,city)
• account (anum, bname, balance)
• loan (lnum, bname, amount)
• depositor (cname, anum)
• borrower (cname, lnum)

30
Examples of Some Clause
(5lt some
) true
0
) true
(5gt some
5
0
) true
(5 some
5
0
(5 ? some
) true (since 0 ? 5)
5
31
All Clauses

• Find all branches that have greater assets than
  all branches located in Brooklyn.
• select distinct T.bname from branch T where
  T.assets gt (select max(assets)
• from branch S
• where S.city
  Brooklyn)
• Same query using all clause
• select bname from branch where assets gt all
  (select assets from branch
  where city Brooklyn)

32
Examples of ALL Clause
(5gt all
) false
6
) true
(5lt all
10
4
) false
(5 all
5
4
(5 ? all
) true (since 5 ? 4 and 5 ? 6)
6
33
Test for Empty Relations

• Find the names of all customers with accounts.
• select S.cname from customer as S where
  exists (select from depositor D where
  D.cname S.cname)
• Find the names of all customers with no account.
• select S.cname from customer as S where not
  exists (select from depositor D where
  D.cname S.cname)

34
Exist Clause (cont.)

• Find all customers who have accounts in all
  branches located in Brooklyn (a division in
  algebra).
• select distinct S.cname from depositor as
  S where (select bname from depositor as T,
  account as R where T.anum R.anum and S.cname
  T.cname)
• contains
• (select bname from branch where city
  Brooklyn)

35
Exist Clause (cont.)

• Find all customers who have accounts in all
  branches located in Brooklyn (a division in
  algebra).
• select distinct S.cname from depositor as
  S where not exists ( (select bname from
  branch where city Brooklyn)
  except (select bname from depositor as T,
  account as R where T.anum R.anum and S.cname
  T.cname))

36
Test for Duplicate Tuples

• The unique clause tests whether a query has any
  duplicate tuples in its result.
• Find all customers who have at most one account
  at the Perryridge branch.
• select T.cname
• from depositor as T
• where exists unique (
• select R.cname from account A, depositor
  as R where T.cname R.cname and R.anum
  A.anum and A.bname Perryridge)

37
Unique Clause (cont.)

• Find all customers who have at least two accounts
  at the Perryridge branch.
• select T.cname
• from depositor as T, account as A as T
• where T.anum A.anum and A.bname
  Perryridge
• group by T.cname
• having count () gt1

38
Unique Clause (cont.)

• Find all customers who have at least two accounts
  at the Perryridge branch.
• select T.cname
• from depositor as T
• where not exists unique (
• select R.cname from account A, depositor
  as R where T.cname R.cname and R.anum
  A.anum and A.bname Perryridge)