Database 1
Data Manipulation
- Prof. dr. Paul M.E. De Bra
- Dept. of Computer Science
- Eindhoven Univ. of Technology
NULL Values
- Null means no information (no value or an unknown value)
- This leads to a three-valued logic: true/false/unknown
- Conversion of three-valued to two-valued logic:
- when the “where” clause is unknown it is treated as false (meaning the tuple is not selected)
- in an aggregation function (max, avg, count...) unknown values are ignored (tuples are not included in the group)
- in count(*) unknown values are not ignored
- = NULL is always unknown, IS NULL is true or false
Example:
- (Library): give the name and department of every borrower who currently has books, together with the number of books not yet returned.
Example:
- (Library): give the name and department of every borrower who currently has books, together with the number of books not yet returned.
select b.name, b.department, count(b.barcode)
from borrow as b
where b.tot IS NULL
group by b.name, b.department
- This is not the same as: give the name and department of every borrower, together with the number of books not yet returned. What is this question in SQL?
Example:
- (Library): give the name and department of every borrower, together with the number of books not yet returned.
( select b.name, b.department, count(b.barcode)
from borrow as b
where b.tot IS NULL
group by b.name, b.department )
union
( select b.name, b.department, 0
from borrow as b
where not exists
( select *
from borrow as bb
where b.name=bb.name and b.department=bb.department and
bb.tot IS NULL ) )
When to use distinct and when not to
- Some operators (e.g. except) automatically eliminate doubles, some preserve them.
- Exercise: Give the name and department of borrowers who have never borrowed the same book more than twice. (use except)
When to use distinct and when not to
- Some operators (e.g. except) automatically eliminate doubles, some preserve them.
- Exercise: Give the name and department of borrowers who have never borrowed the same book more than twice.
- Tip: the same book does not mean the same copy!
When to use distinct and when not to
- Exercise: Give the name and department of borrowers who have never borrowed the same book more than twice.
- (select b.name, b.department
from borrow as b)
except
(select b.name, b.department
from borrow as b, copy as c
where b.barcode = c.barcode and
2 < ( select count(*)
from borrow as b1, copy as c1
where b1.barcode = c1.barcode and b1.name = b.name
and b1.department = b.department and c1.ISBN=c.ISBN ) )
Slide 9
Slide 10
Except all, no duplicate elimination
- List the name and department of borrowers who have or had more reservations than loans. (use except all)
Except all, no duplicate elimination
- List the name and department of borrowers who have or had more reservations than loans. (use except all)
- (select name, department
from reserve)
except all
(select name, department
from borrow)
- When someone has 5 reservations and 3 loans his name/department will appear in the answer twice.
Now try to eliminate that (without throwing out the except all clause).
Except all, no duplicate elimination
- List the name and department of borrowers who have or had more reservations than loans. (use except all)
- with list(name, department) as
( (select name, department
from reserve)
except all
(select name, department
from borrow) )
select distinct name, department
from list