ex2.sql

本人的练习2的答案

1.
select name, year
from (author NATURAL JOIN title)
where lname = 'Churchill';

2.
select distinct name
from ((book join member on BorrowerMemNo = memno) natural join title)
where fname in('Susan', 'John');

3.
(select distinct fname || lname as name, memno
from ((book join member on BorrowerMemNo = memno) natural join title)
where name = 'Iliad' 
)
intersect
(select distinct fname || lname as name, memno
from ((book join member on BorrowerMemNo = memno) natural join title)
where name = 'Odyssey' 
);

4.
Select subfrom.f|| subfrom.l as name, memno
from 	(select distinct member.fname as f, member.lname as l, memno, title.name, author.fname, author.lname, author.callnumber
	from ((Book JOIN Member ON Book.BorrowerMemNo = Member.MemNo) join (author natural join title) on book.callnumber = title.callnumber)
	where author.lname = 'Collins')as subfrom
Group by subfrom.f || subfrom.l, memno
HAVING COUNT(memno) = (SELECT COUNT(CallNumber)
                            FROM Author
                            WHERE Author.Lname = 'Collins'
                            GROUP BY Author.Lname);

5.
Select phonenumber 
From book join member on borrowermemno = memno, author
where author.callnumber = book.callnumber AND author.lname = 'Tanenbaum';

6.
Select member.fname || member.lname as name, memno, count (*)
From book join member on borrowermemno = memno
Group by member.fname || member.lname, memno
Having count(*) > 3
order by count(*) desc;

7.
Select member.fname || member.lname as name, member.memno
From member
where member.memno NOT IN (Select memno
			   From book join member on book.borrowermemno = memno
			   );

8.
Select Distinct subFrom.fname
From	(Select fname, phonenumber
	 From member
	 where member.memno NOT IN (Select member.memno
				    From member join book on book.borrowermemno = member.memno, title
				    Where title.callnumber = book.callnumber AND title.name = 'Pitt Roads')				 
	) as subFrom
Where subFrom.phonenumber like '412%'
Order by subFrom.fname;