You are given database schema

BOOK(isbn, title, genre, publisher, price)

AUTHOR(name, address, age, salary)

WRITES(isbn, writer, year-written)

SALES(isbn, year-sold, number-sold)

where isbn is the primary key in BOOK and genre is one of biography, business, fiction, science; name is the primary key in AUTHOR, and salary is how much the author is paid to write a book (alone or with others); in WRITES, isbn is a foreign key to BOOK and writer a foreign key to AUTHOR, and together they form the primary key of that table (this implies that a book may be jointly written by several authors, and an author may have written, alone or together with others, several books). Finally, isbn is also a foreign key in SALES; the primary key of this table is (isbn, year-sold), since this table has the yearly sales for each book.

Answer the following questions.

Express the following questions as SQL queries. Use only the operators see in class. You cannot use OUTER JOIN, RANK, LIMIT, or WINDOW functions. You can use subqueries anywhere, CASE, WITH clauses, or declare temporary tables if you wish.

(a) List the names of authors who have written (alone or with someone else) a business book or a science book.

(b) List the names of authors who have written (alone or with someone else) a business book and a science book.

(c) List the total number of copies of books sold by each publisher.

(d) List the names of authors who have written all their books alone (without co-authors).

(e) The cost of a book is the sum of the salaries of all the authors of the book. The income of a book is the number of copies sold times its price. List the titles of books with an income higher than the cost.

(f) List the number of authors for each book that has sold more than 1,000 copies in any given year.

(g) List the number of authors for each book that has sold more than 10,000 copies in total (across all years).

(h) A book is a best-seller for a year if it sells the most copies that year (note that there can be ties!). Find the best-seller(s) in 2000.

(i) List the titles of books where all authors are older than 50 (note: this includes books with only one author).