Using Pubs (or Pubs2) sample tables to create SQL statements (1 per question) to accomplish the following (command and output required): 

 

1. List the first name, last name and author ID of the authors in the city of OAKLAND order by last name.  
2. List all book titles that have the word "computer" anywhere in their titles.
3. List any titles, and the price of any titles that do not have a price assigned.
4. Repeat #3 but in the output, list a price of zero for all those that do not have a price assigned (do not modify the data, only the output changes).  
5. List each title with its current cost and with the projected cost increase of 35%.
6. A sentence for each author that looks like this: 

 

First name = ________  Last Name = _________  Initials = 

 

Example:  

First name = Harry  Last Name = Shasho  Initials = HS

 

Part II - (Aggregate Functions / Joins / Subqueries) - Using your PUBs sample tables, create SQL statements (1 per question) to accomplish the following (command and output required)

 

1. What is the maximum price of all the books?
2. What is the average price of the books for each type?

 

1. What is the count of books between $8 and $22
2. What is the name of the author who wrote the book with the title id MC2222?
3. For each book (title), list the publisher (the name of the publisher), order by book (title).
4. What books are priced above the current average?

Diagram of the pubs2 database This diagram shows the tables in the pubs2 database and some of the relationships among them. Figure A-1: Diagram of the pubs2 database TITLE AUTHOR au_ord royaltyper N au_id 1 au_id AUTHORS au_Iname au_fname phone address city state country postalcode 1 au_id 1 1 au_id AU PIX pic format_type bytesize title_id title_id N 1 BLURBS pixwidth_hor pixawidth_vert au_id au_id copy TITLES title type pubold price advance total_sales notes pubdate contract 1 N SALESDETAIL ord num qty discount N stor_id 1 DISCOUNTS discounttype stor_id sto: lowaty highqty discount title_id title_id title_id title_id 1 pub_id pub_id N N1 stor_id stor_id N stor_id stor_id lord_num ord_num 1 ROYSCHED lorange hirange royalty PUBLISHERS pub pub_name city state SALES stor id N and num date stor_id 1 STORES city state stor_id stor_name stor_address country postalcode payterms