/************************************************************************
 *      a. Print the birthplace and citizenship of Sellis               *
 ************************************************************************/
a. select bplace, citizenship
   from   author
   where  name = 'Sellis'


/************************************************************************
 *      b. Print the names of the San Francisco and New York libraries  *
 *                                                                      *
 * Clarif: Since library name is assumed to be unique over all the      *
 *         cities I have just printed the library name and not the city *
 *         alongwith it.                                                *
 ************************************************************************/
b. select lname
   from library
   where  (city = 'San Francisco') or
          (city = 'New York')

/************************************************************************
 *      c. Print the title of every book some edition of which has      *
 *         between 2 and 5 (2 <= quantity <= 5) copies in stock in      *
 *         a New York library.                                          *
 *                                                                      *
 * Assumption:  There can be more than 1 books with different isbn      *  
 *              but the same title. That's why I have printed the       *
 *              isbn along with each title.                             *
 *                                                                      *
 * Distinct:     Also, the distinct is  needed because there can be     *
 *               multiple libraries in New York which  have between     *
 *               2 and 5 copies of the same book.                       *
 ************************************************************************/
c. select distinct book.isbn, book.title
   from   book, in_stock, library
   where  (book.isbn = in_stock.isbn) and
          (in_stock.lib_name = library.lname) and
          (library.city = 'New York') and
          (in_stock.quantity >= 2) and
          (in_stock.quantity <= 5)


/************************************************************************
 *      d. Print the name of every author that has a book that,         *
 *         independent of edition, is carried by at least two           *
 *         different libraries in Berkeley.                             *
 *                                                                      *
 * Distinct:    distinct is needed because there can be more than       *
 *              1 book by the same author that is carried by >=2        *
 *              libraries in Berkeley.  Also, there might be more than  *
 *              2 libraries in Berkeley carrying the same book.         *
 ************************************************************************/
d. select distinct book.author
   from    book, library lib1, library lib2, in_stock in1, in_stock in2
   where  (book.isbn = in1.isbn) and
          (book.isbn = in2.isbn) and
          (in1.lib_name = lib1.lname) and
          (in2.lib_name = lib2.lname) and
          (lib1.lname != lib2.lname) and
          (lib1.city = 'Berkeley') and
          (lib2.city = 'Berkeley')

/************************************************************************
 *      e. Print the title of every autobiography (subject = authorname)*
 *         carried by a library in the author's birthplace.             *
 *                                                                      *
 * Assumption:  There can be more than 1 books with different isbn      *  
 *              but the same title. That's why I have printed the       *
 *              isbn along with each title.                             *
 *                                                                      *
 * Distinct:    distinct is needed because there can be more than       *
 *              1 edition of the autobiography in the library.          *
 *              (multiple entries in in_stock relation)                 *
/************************************************************************/
e. select distinct book.isbn, book.title
   from   book, author, bindex, in_stock, library
   where  (author.name = book.author) and
          (book.isbn = bindex.isbn) and
          (author.name = bindex.subject) and
          (book.isbn = in_stock.isbn) and
          (in_stock.lib_name = library.lname) and
          (library.city = author.bplace)


/************************************************************************
 *      f. Check if some edition of "Why I Went to Stanford" is         *
 *         carried by a San Francisco Bay Area library.                 *
 *                                                                      * 
 *                                                                      *
 ************************************************************************/
f.
(select 'Present'
 where  exists (select *
                from in_stock, library, book
                where (book.title = 'Why I Went to Stanford') and
                      (book.isbn = in_stock.isbn) and
                      (in_stock.lib_name = library.lname) and
                      ((library.city = 'Berkeley') or
                       (library.city = 'San Francisco'))))
 union
(select 'Not Present'
 where  not exists (select *
                    from in_stock, library, book
                    where (book.title = 'Why I Went to Stanford') and
                          (book.isbn = in_stock.isbn) and
                          (in_stock.lib_name = library.lname) and
                          ((library.city = 'Berkeley') or
                           (library.city = 'San Francisco'))))


/************************************************************************
 *      g. Print the titles of every pair of books by the same          *
 *         author, if the two books have at least two subjects          *
 *         in common. Make sure that every pair of titles is printed    *
 *         exactly once, and that a title is not paired up with itself. *
 *                                                                      *
 * Clarif: The b1.isbn > b2.isbn eliminates cases like (isbn1, isbn1)   *
 *         as well as (isbn1,isbn2) and (isbn2, isbn1).                 *
 *         So, there is no need for a distinct and also we won't        *
 *         get redundant info like (i1,i2) and (i2,i1).                 *
 ************************************************************************/
g.select b1.title, b2.title, b1.author
  from   book b1, book b2
  where (b1.author = b2.author) and
        (b1.isbn > b2.isbn) and
        exists (select *
                from   bindex bi1, bindex bi2, bindex bi3, bindex bi4
                where  (bi1.isbn = b1.isbn) and
                       (bi2.isbn = b2.isbn) and
                       (bi3.isbn = b1.isbn) and
                       (bi4.isbn = b2.isbn) and
                       (bi1.subject != bi3.subject) and
                       (bi4.subject = bi3.subject) and
                       (bi1.subject = bi2.subject))

/************************************************************************
 *      h. For each French author, print their name and the number      *
 *         of books they have written (different editions of the        *
 *         same book do not count separately).                          *
 * Assumption: If an author has written more than 1 book with the same  *
 *         title, they are considered to be different editions of the   *
 *         same book if and only if they have same isbn.                *
 *         isbn is considered to be the key for book relation.          *
 * Distinct: Due to the above assumption, we don't need a distinct.     *
 ************************************************************************/
h. select author.name, count (*)
   from   author, book
   where  (book.author = author.name) and
          (author.citizenship = 'France')
   group by author.name

/************************************************************************
 *      i. For every country that has exactly two authors as citizens,  *
 *         print the total number of subjects of all the books written  *
 *         by both authors of that country.                             *
 * Disinct: distinct is needed because the two authors could have       *
 *          written on the same subject also.                           *
 ************************************************************************/
i.select author.citizenship, count(distinct bindex.subject)
  from   author, book, bindex
  where  (author.name = book.author) and
         (book.isbn = bindex.isbn) and
          author.citizenship in
                (select citizenship
                 from   author
                 group  by citizenship
                 having count(*) = 2
                 )
  group by author.citizenship
                 
/************************************************************************
 *      j. Print the title of every book that, independent of edition   *
 *         is carried by all libraries in Boston.                       *
 * Clarif: This solution prints titles of all the books  in the book    *
 *         relation if there are no libraries in Boston.                *
 ************************************************************************/
j. select title
   from book
   where not exists
        (select library.lname
         from library
         where  (city = 'Boston') and
                lname not in
                        (select lib_name
                         from  in_stock
                         where (in_stock.isbn = book.isbn)))