1.       List the author number and last name for every author.

2.      List the name of every publisher not located in Boston.

3.      List the book code and title of every book that has the type SFI and that is a paperback.

4.      List the book code and title of every book that has the type SFI, HOR, or ART.

5.      Calculate the average price of each type of book. List the book type and average price.

6.      For every book, list the book code, book title, publisher code, and publisher name.

7.      (more challenging) For authors that have written more than one book, list their names (first, last) and the number of books they have written.

SELECT AuthorNum, AuthorLast

FROM Author;

 

SELECT PublisherName

FROM Publisher

WHERE City<>'Boston';

 

SELECT BookCode, Title

FROM Book

WHERE Type='SFI' AND Paperback=Yes;

 

SELECT BookCode, Title

FROM Book

WHERE Type IN ('SFI', 'HOR', 'ART');

 

SELECT Book.Type, Avg(Book.Price) AS AvgPrice

FROM Book

GROUP BY Book.Type;

 

SELECT BookCode, Title, Book.PublisherCode, PublisherName

FROM Book, Publisher

WHERE Book.PublisherCode=Publisher.PublisherCode;

 

SELECT AuthorFirst, AuthorLast, NumBooks

FROM Author, [SELECT AuthorNum, Count(*) AS NumBooks

FROM Wrote

GROUP BY AuthorNum

HAVING Count(*) > 1]. AS Temp

WHERE Author.AuthorNum=Temp.AuthorNum;

 

Q1    N = 25

q3henry1
AuthorNum	AuthorLast
1	Morrison
2	Solotaroff
3	Vintage
4	Francis
5	Straub

Q2    N = 26

q3henry4
PublisherName
Arkham House
Arcade Publishing
Basic Books
Back Bay Books
Fawcett Books

 

 

Q3    N = 1

q3henry7
BookCode	Title
0180	A Deepness in the Sky

 

 

Q4    N = 9

q3henry12
BookCode	Title
0180	A Deepness in the Sky
0189	Magic Terror
0378	Venice
1351	Dreamcatcher: A Novel
1382	Treasure Chests
2226	Harry Potter and the Prisoner of Azkaban
2281	Van Gogh and Gauguin
7443	Harry Potter and the Goblet of Fire
9611	Black House

 

 

Q5    N = 12

q3henry15
Type	AvgPrice
ART	$23.32
CMP	$37.95
FIC	$10.52

 

Q6    N = 33

q3henry16
BookCode	Title	PublisherCode	PublisherName
8092	Godel, Escher, Bach	BA	Basic Books
3350	Group: Six People in Search of a Life	BP	Berkley Publishing
3906	The Soul of a New Machine	BY	Back Bay Books

 

 

Q7    N = 8

Q7
AuthorFirst	AuthorLast	NumBooks
Toni	Morrison	3
Dick	Francis	3
Peter	Straub	2
Stephen	King	2
J.K.	Rowling	2
J.D.	Salinger	3
Albert	Camus	2
John	Steinbeck	4