考虑以下关系模式：eployee(empno,name,office,age)books(isbn,title,authors,publisher)loan(empno,isbn,date)用关系代数写出下列查询：a、找出借了任何由McGraw-Hill出版的书的员工的姓名.b、找出借了由McGraw-Hill出

Question

考虑以下关系模式：
eployee(empno,name,office,age)
books(isbn,title,authors,publisher)
loan(empno,isbn,date)
用关系代数写出下列查询：
a、找出借了任何由McGraw-Hill出版的书的员工的姓名.
b、找出借了由McGraw-Hill出版的所有的书的员工的姓名.
c、找出借了至少5本不同的由McGraw-Hill出版的书的员工的姓名
d、对每个出版商,找出借了至少5本该出版商的书的员工的姓名.
要数理逻辑表达方式的~

Answer 1

a、简单得很,多表自然连接
select distinct e.name from eployee E, books B, loan L
where L.isbn = B.isbn
and L.empno = E.empno
and B.publisher = "McGraw-Hill"
b、思路：有那么些人,不存在McGraw-Hill出版的书而他们没有借
select name from employee E
where not exists( select 1 from loan L
where L.empno = E.empno
and not exists( select 1 from books B
where B.isbn = L.isbn
and B.publisher = "McGraw-Hill" ) )
c、技巧：返回单值的子查询可用于逻辑判断；不同的书——distinct isbn
select name from employee E
where exists( select 1 from loan L
where L.empno = E.empno
and ( select count( distinct isbn ) from books B
where B.isbn = L.isbn
and B.publisher = "McGraw-Hill" ) >= 5 )
d、
select E.name from employee E
where exists( select 1 from loan L
where E.empno = L.empno
and ( select count(*) from books B
where B2.isbn = L.isbn
and publisher = L.publisher ) >= 5 )