Consider the following schema for a database that contains information about books, authors,
awards, and publishers.
BOOK (isbn, title, publisher, year)
AUTHOR (ssn, name, city)
PUBLISHER (name, city)
WROTE (isbn, ssn)
AWARD(ssn, award_name, year)
Every book has a unique isbn (standardized book number) and every author has a unique
ssn. We also assume that each publisher has a unique name and is located in a single city. The
WROTE relation connects author ssn(s) with the isbns of books that they wrote, either as a single
author or as one of several co-authors. Finally, the AWARD relation stores an (literary) award
for an author and the year that the author received this award. Each award has a unique
award_name (for example, “Nobel Prize”, “Booker Prize”, etc) and an author can get the same
award multiple years.
Write the following queries in SQL:
1) Find and print the titles and the years of all the books written by “Stephen King”.
2) Find all the authors that have received multiple times (more than once) the same award and
print the author name and the award name.
3) Find and print the name of the authors that have written at least one book for each publisher
(they have published at least one book for all publishers).
0 comments