SSD7数据库系统实验Exercise2答案

本实验所用BDMS是postgresql.

-- 2.1 -->

select name,year 
from Title
where callnumber=(select callnumber 
         from Author

                  where lname='Churchill');

-- 2.2 --
select name 
from Title 
where callnumber in(select distinct callnumber
                    from Book
                    where borrowermemno in(select memno 
                                           from Member
                                           where fname='John' OR fname='Susan' ));

     

            

--2.3 -->    
select fname,lname,memno
from Member
where memno=some(select borrowermemno from Book where callnumber=(select callnumber from Title where name='Iliad'))and 
memno=some(select borrowermemno from Book where callnumber=(select callnumber from Title where name='Odyssey'))



--2.4-->
select fname,lname,memno 
from Member
where memno in(
select distinct borrowermemno 
from Book where callnumber =some
(select callnumber
 from Author
 where lname='Collins'))


--2.5-->
select phonenumber
from Member
where memno in(select borrowermemno 
               from Book
               where callnumber=(select callnumber 
                                 from Author
                                 where lname='Tanenbaum'))


                                 
-- 2.6-->
SELECT memno, fname, lname, COUNT(*)
FROM member, book
WHERE memno = borrowermemno
GROUP BY memno, fname, lname
HAVING COUNT(*) > 3
ORDER BY COUNT(*) DESC;
-->

-- 2.7-->
select * from member
where memno in
(select memno from member
except
select distinct  borrowermemno 
from book)

---  2.8-->
select * from member 
where memno in(
select memno from member 
where phonenumber like '412%')
order by fname