Null Values • It is possible for tuples to have a null value for some of their attributes • The predicate ‘is null’ can be used to check for null values. • E.g. Find all loan number which appear in the loan relation with null values for amount. select loan-numberfrom loanwhere amount is null • The result of any arithmetic expression involving null is null • E.g. 5 + null returns null
Null Values and Three Valued Logic • Any comparison with null returns unknown • E.g. 5 < null or null <> null or null = null • Three-valued logic using the truth value unknown • OR: (unknownortrue) = true (unknownorfalse) = unknown (unknown or unknown) = unknown • AND: (true and unknown) = unknown (false and unknown) = false (unknown and unknown) = unknown • NOT: (not unknown) = unknown • “P is unknown” If predicate P evaluates to unknown true • where clause predicate • If evaluates to unknown false
Null Values and Aggregates • Total all loan amounts select sum (amount)from loan • Above statement ignores null amounts • Result is null If there is no non-null amount • All aggregate operations except count(*) ignore tuples with null values on the aggregated attributes. • The count of an empty collection is defined to be 0. All other aggregates return null value.
Nested Subqueries • SQL provides a mechanism for the nesting of subqueries. • A subquery is a select-from-where expression that is nested within another query. • A common use of subqueries is to perform tests for • Set membership • Set comparisons • Set cardinality
Set Membership • Find all customers who have both an account and a loan at the bank. select distinct customer-namefrom borrowerwhere customer-name in (select customer-namefromdepositor) • Find all customers who have a loan at the bank but do not have an account at the bank select distinct customer-namefrom borrowerwhere customer-name not in (select customer-namefrom depositor)
Membership in Arbitrary Relation • Find all customers who have both an account and a loan at the Perryridge branch select distinctcustomer-namefrom borrower, loanwhere borrower.loan-number = loan.loan-number andbranch-name = “Perryridge” and(branch-name, customer-name)in(select branch-name, customer-namefrom depositor, accountwhere depositor.account-number = account.account-number) • in and not in can used on enumerate sets. • Find all customers who have a loan at the bank, and whose names are neither Smith nor Jones select distinct customer-namefrom borrowerwhere customer-name not in (‘Smith’, ‘Jones’)
Set Comparison • Find all branches that have greater assets than some branch located in Brooklyn. select distinct T.branch-namefrom branch as T, branch as Swhere T.assets > S.assets andS.branch-city = ‘Brooklyn’ • using ‘> some’ clause select branch-namefrom branchwhere assets > some (select assetsfrom branchwhere branch-city = ‘Brooklyn’)
0 5 6 Definition of Some Clause • F <comp> some r t r s.t. (F <comp> t)where <comp> can be <, <=, >=, =, <> • ‘= some’ is identical to in • ‘<> some’ is not the same as not in 0 ) = false (5< some (5< some ) = true 5 0 0 (5 <> some ) = true ) = true (5 = some 5 5
0 5 6 Definition of All Clause • F <comp> all r t r (F <comp> t) • ‘<> all’ is identical to not in 6 (5< all ) = false ) = true (5< all 10 4 4 (5 all ) = true ) = false (5 = all 6 5
Example Query • Find the names of all branches that have greater assets than all branches located in Brooklyn. select branch-namefrom branchwhere assets > all (select assetsfrom branchwhere branch-city = ‘Brooklyn’)
Test for Empty Relations • The exists construct returns the value true if the argument subquery is nonempty. • exists r r Ø • not exists r r = Ø • Find all customers who have both an account and a loan at the bank select customer-namefrom borrowerwhere exists (select *from depositorwhere depositor.customer-name = borrower.customer-name)
Example Query • Find all customers who have an account at all branches located in Brooklyn. select distinct S.customer-namefrom depositor as Swhere not exists ((select branch-namefrom branchwhere branch-city = ‘Brooklyn’)except(select R.branch-namefrom depositor as T, account as Rwhere T.account-number = R.account-number andS.customer-name = T.customer-name))
Test for Absence of Duplicate Tuples • The unique construct tests whether a subquery has any duplicate tuples in its result.
Example Query • Find all customers who have at most one account at the Perryridge branch. select T.customer-name from depositor as T where unique ( select R.customer-namefrom account, depositor as Rwhere T.customer-name = R.customer-name andR.account-number = account.account-number andaccount.branch-name = ‘Perryridge’) • Find all customers who have at least two accounts at the Perryridge branch. select distinct T.customer-name from depositor T where not unique ( select R.customer-name from account, depositor as R where T.customer-name = R.customer-name and R.account-number = account.account-number and account.branch-name = ‘Perryridge’)
Views • Provide a mechanism to hide certain data from the view of certain users. • To create a view: create view v as<query expression> where • <query expression> : any legal expression • v : view name • To delete a view: drop view v
Example Query • A view consisting of branches and their customers create view all-customer as(select branch-name, customer-namefrom depositor, accountwhere depositor.account-number = account.account-number) union(select branch-name, customer-namefrom borrower, loanwhere borrower.loan-number = loan.loan-number) • Find all customers of the Perryridge branch select customer-namefrom all-customerwhere branch-name = ‘Perryridge’
Complex Queries • Two ways of composing multiple SQL blocks to express a complex query: • derived relations • with clause
Derived Relations • SQL allows a subquery expression to be used in the from clause • Find the average account balance of those branches where the average account balance is greater than $1200. select branch-name, avg-balancefrom (select branch-name, avg (balance)from accountgroup by branch-name)as result (branch-name, avg-balance)where avg-balance > 1200
With Clause • with clause allows views to be defined locally to a query, rather than globally. • Find all accounts with the maximum balance withmax-balance(value) asselect max(balance)fromaccountselectaccount-numberfromaccount, max-balancewhereaccount.balance = max-balance.value
Example Query • Find all branches where the total account deposit is greater than the average of the total account deposits at all brancheswithbranch-total (branch-name, value) asselectbranch-name, sum (balance)fromaccountgroupbybranch-namewithbranch-total-avg(value) asselectavg (value)frombranch-totalselect branch-namefrombranch-total, branch-total-avgwherebranch-total.value >= branch-total-avg.value
