First page Back Continue Last page Graphics
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 ) )
Notes:
Note:
Except automatically removes duplicates so we do not have to write distinct.
Note:
The subquery construct count(*) does not eliminate duplicates.