Create the following queries for the “books” database. For each query make sure to only include those tables that are necessary to answer the question. Your answers for each of these questions should consist of the SQL query that will produce these lists, NOT the list itself.
Create the following queries for the “books”
-
For each type of book (e.g. biography, children, etc), show the number of those books that are 450 pages or longer. The column heading should be '# of long books'.
-
Modify the answer to the previous question so that only those types for which there are at least 2 long books are displayed
-
For each publisher, list the name of the publisher and total number of pages that the publisher has published in all their books (ie. add up all the pages in all books for each publisher). Show the publishers who have published the most pages at the top of the result list.
-
Modify the previous query so that only publishers who have published at least 1250 pages will show up.
-
List each publisher's name and the numbers of pages in their longest and shortest books. Sort the results by the publisher's name.
-
List the authors first and last names, the author's state, the title name, the publisher name and the publishers state for all books.
-
List the title, number of pages, authors first and last names and author's state for all books that are longer than 150 pages and whose author lives in NY or CA.
-
List the authors first and last names, the author's state, the title name, the publisher name and the publishers state only for those books where the author and publisher are from the same state. (HINT: specify authors.state [note: there is a period between author and state] in the criteria for the publisher's state).
Trending now
This is a popular solution!
Step by step
Solved in 3 steps
