Database代写:INFSCI 2710 Relational Algebra

根据提供的schema,代写SQL查询语句来回答所给的问题,所有的SQL查询需要用一条语句完成。

Requirement

Note: Use MySQL to answer all questions. For each question you need to provide the SQL query and also the screen shot of the output of that query from either phpMyAdmin, MySQL Workbench or from the terminal (if from terminal, make sure it is formatted properly).

Preparations: Download hwk2-codes.txt from the class page and load data into MySQL.

Consider the following schema:

author (ID, FirstName, LastName, YearOfBirth, Gender, LivingCityID)
book (ID, Name, Type, YearPublished, PublisherID, SoldBookCount)
writes (BookID, AuthorID)
city (ID, CityName, Country)
publisher (ID, PublisherName, PublisherCityID)

Underlines attributes in bold are the primary keys. Assume that one book can be published by only one publisher; one author can write several books; one book may have several authors.

Q1

Specify an SQL expression to find all owners whose first name starts with “P” and last name ends with “h”. Please display their ID.

Q2

Specify an SQL expression to find author ID and counts of all the books that she/he published.

Q3

Specify an SQL expression to find the ID of all authors who published more than two books.

Q4

Specify an SQL expression to find the ID of the author who has published the most of books.

Q5

Specify an SQL expression to find the ID of all books that are not published in the residence cities of its authors. NT: one book may have several authors.

Q6

Specify an SQL expression to find the ID of the publisher who published books before year 1600 or after year 1900, but did not publish any book between year 1600 and year 1900.

Q7

Specify an SQL expression to find the ID of the publisher who published books before year 1600 and after year 1900, but did not publish any book between year 1600 and year 1900.

Q8

Specify an SQL expression to find the total count of sold books per each author. Show the author ID and book count.

Q9

Specify an SQL expression to find the total sold book count per each book type and author. Show author ID, book type and sold book count.

Q10

Specify an SQL expression to find the author who have sold the most books. Show the author ID and sold book count.

Q11

Specify an SQL expression to find the author and book type pair, whose total sold book count is the biggest. Show author ID, book Type, and sold book count.