1. The last name and the first name of all the persons in the table AUTHOR
SELECT LAST_NAME , FIRST_NAME
FROM AUTHOR
2. All the columns in the table COUNTRY. Sort the rows alphabetically, by name of the country.
SELECT *
FROM COUNTRY
ORDER BY NAME
3. All books published in between 1995 and 2005.
SELECT *
FROM BOOK
WHERE YEAR > 1995 AND YEAR <= 2005
4. Authors whose last name starts with the letter "B"
SELECT *
FROM AUTHOR
WHERE LAST_NAME LIKE 'B%'
5. All the genres, in alphabetic order
SELECT *
FROM GENRE
ORDER BY DESCRIPTION
6. A list of all the years that occur in the table BOOK, without duplicates.
SELECT DISTINCT YEAR
FROM BOOK
7. The year of the most recent title in the database.
SELECT MAX(YEAR)
FROM BOOK
8. The number of unique countries in the table AUTHOR
SELECT COUNT( DISTINCT NATIONALITY )
FROM AUTHOR
9. The average number of copies sold for all the books
SELECT AVG(SOLD)
FROM BOOK
10. The first name and the last name of all the authors, together with the full name of the countries in which these authors were born.
SELECT LAST_NAME , FIRST_NAME , NAME
FROM AUTHOR
LEFT JOIN COUNTRY
ON NATIONALITY = COUNTRY_ID
11. The title and the year of all the books, together with a full description of the genres (not only the codes).
SELECT TITLE , DESCRIPTION
FROM BOOK
LEFT JOIN GENRE
ON GENRE = GENRE_ID
12. The title and the name of the publisher of each book.
SELECT TITLE, NAME
FROM BOOK
LEFT JOIN PUBLISHER
ON PUBLISHER = PUBL_ID
13. What are the best-selling books in the genre "Popular Science"?
SELECT *
FROM BOOK
LEFT JOIN GENRE
ON GENRE = GENRE_ID
WHERE DESCRIPTION = 'Popular Science'
14. What is the average number of sales of a book written by J.K. Rowling?
SELECT LAST_NAME , FIRST_NAME , AVG( SOLD )
FROM BOOK
LEFT JOIN AUTHOR
ON AUTHOR = AUTHOR_ID
WHERE LAST_NAME = 'Rowling' and FIRST_NAME = 'J.K.'
15. For each year, show the total number of bestsellers
SELECT YEAR , COUNT(*)
FROM BOOK
GROUP BY YEAR
16. For each genre, show the average number of sales
SELECT DESCRIPTION , AVG( SOLD )
FROM BOOK
LEFT JOIN GENRE
ON GENRE = GENRE_ID
GROUP BY DESCRIPTION
17. For each author, show the total number of sales
SELECT FIRST_NAME , LAST_NAME , SUM( SOLD )
FROM BOOK
LEFT JOIN AUTHOR
ON AUTHOR = AUTHOR_ID
GROUP BY AUTHOR
18. Which authors have written more than one beststeller?
SELECT AUTHOR, LAST_NAME , FIRST_NAME , COUNT(*)
FROM AUTHOR
LEFT JOIN BOOK
ON AUTHOR = AUTHOR_ID
GROUP BY AUTHOR
HAVING COUNT(*) > 1
19. Which publishers are responsible for more than three bestsellers?
SELECT NAME , COUNT(*)
FROM BOOK
LEFT JOIN PUBLISHER
ON PUBLISHER = PUBL_ID
GROUP BY NAME
HAVING COUNT(*) > 3
20. For each genre, show the maximum number of books that have been sold.
SELECT DESCRIPTION, MAX(SOLD) AS 'HIGHEST_SALES'
FROM BOOK
LEFT JOIN GENRE
ON GENRE = GENRE_ID
GROUP BY DESCRIPTION
