yanglei.txt

Run the SQL script given to you to create a Library database. Note that each row in the Book table d

1. 
SELECT name,year
FROM title
WHERE callnumber=(SELECT callnumber
                  FROM   author
                  WHERE  lname='Churchill');
------------------------------------------------------------------------------
2.
select title.name,book.callnumber
from title,book
where title.callnumber=book.callnumber
and book.borrowermemno=(
select memno
from member
where fname='John' 
) 
union
select title.name,book.callnumber
from title,book
where title.callnumber=book.callnumber
and book.borrowermemno=(
select memno
from member
where fname='Susan' 
) 
order by callnumber
------------------------------------------------------------------------------------
3.
select member.fname,member.lname
from member
where memno=(
select borrowermemno
from book
where callnumber=(select callnumber
from title
where name='Iliad'  
)
intersect
select borrowermemno
from book
where callnumber=(select callnumber
from title
where name='Odyssey'
)
)
--------------------------------------------------------------------------------------
4.
select author.lname,book.callnumber,book.borrowermemno,title.name,member.fname
from title,author,book,member
where title.callnumber=author.callnumber
and author.lname='Collins'
and book.callnumber=title.callnumber
and member.memno=book.borrowermemno
---------------------------------------------------------------------------------------
5.
select author.lname,book.callnumber,book.borrowermemno,title.name,member.phonenumber
from title,author,book,member
where title.callnumber=author.callnumber
and author.lname='Tanenbaum'
and book.callnumber=title.callnumber
and member.memno=book.borrowermemno
-------------------------------------------------------------------------------------
6.
select borrowermemno,memno,lname,count(borrowermemno)as mycount                           
       from book,member
       where member.memno=book.borrowermemno
       group by borrowermemno,memno,lname
       having count(borrowermemno)>=3                                
       order by borrowermemno,memno
-----------------------------------------------------------------------------
7.
select memno,fname,lname
from member
where not exists(
select *
from book
where book.borrowermemno=member.memno
) 
-------------------------------------------------------------------------------
8.
select memno,fname,address
from member
where not exists(
   select *
   from book
   where memno=borrowermemno and callnumber=(
                                         select callnumber
                                         from title
                                         where name='Pitt Roads'
                                         )
)
and address like '%Pittsburgh%' 
and phonenumber like'412%'