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 ) )

Careful with what is logically equivalent and what is not!

What is the difference with
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 )

Give name and faculty of borrowers, booktitles, ISBN numbers for the books that have been borrowed more than 5 times in 2003 by that borrower, plus the number of times that specific book has been borrowed by that borrower

SELECT b.name, b.department, bk.title, bk.ISBN, count(b.from)
FROM borrow AS b, copy AS c, book AS bk
WHERE b.barcode = c.barcode AND bk.ISBN=c.ISBN
AND b.from <= 20030101 AND b.from <= 20031231
GROUP BY b.name, b.department, bk.title, bk.ISBN
HAVING count(b.from) > 5

What would be different if we replace “more than 5” by “less than 5”?

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

In this colstruction we are going to futrher discuss properties of constraints. We are going to play with canonical covers and with completeness of rules.

Note:

Except automatically removes duplicates so we do not have to write distinct.

Note:

The subquery construct count(*) does not eliminate duplicates.

Note: the previous query selected everyone except the frequent borrowers, so people with 0 loans are listed. In the new query people with 0 loans do not appear in the subquery so the where clause 2>= ... evaluated to false!

Note that it makes no difference whether we write 2>= ( select...

or 2>= some (select... or 2>= all (select ...

as the subquery returns 1 value as its result.

Databases 1, Colstructie 16

Data Manipulation, null values

Paul De Bra

2003-04-01T22:52:54

Paul De Bra