Longlist

Booker Prize Logo

Problem to Solve

Each year, judges for The Booker Prize create a longlist of books that could be named “the best single work of sustained fiction written in the English language, which was published in the United Kingdom or Ireland.” Given a database of the long listed works, answer questions about them!

Distribution Code

For this problem, you’ll need to download longlist.db, along with several .sql files in which you’ll write your queries.

Download the distribution code

Log into cs50.dev, click on your terminal window, and execute cd by itself. You should find that your terminal window’s prompt resembles the below:

$

Next execute

wget https://cdn.cs50.net/sql/2025/spring/sections/0/longlist.zip

in order to download a ZIP called longlist.zip into your codespace.

Then execute

unzip longlist.zip

to create a folder called longlist. You no longer need the ZIP file, so you can execute

rm longlist.zip

and respond with “y” followed by Enter at the prompt to remove the ZIP file you downloaded.

Now type

cd longlist

followed by Enter to move yourself into (i.e., open) that directory. Your prompt should now resemble the below.

longlist/ $

If all was successful, you should execute

ls

and see a database named longlist.db alongside several .sql files. Executing sqlite3 longlist.db should open the database in sqlite3, via which you’ll execute SQL queries. If not, retrace your steps and see if you can determine where you went wrong!

Schema

Each database has some “schema”—the tables and columns into which the data is organized. In longlist.db you’ll find a single table, longlist. In the longlist table, you’ll find the following columns:

  • isbn, which uniquely identifies each book
  • title, which is the book’s title
  • author, which is the book’s author
  • translator, which is the book’s translator
  • format, which is the book’s format (either “paperback” or “hardcover”)
  • pages, which is the book’s length in pages
  • publisher, which is the book’s publisher
  • published, which is the date on which the book was published
  • year, which is the year in which the book was long listed
  • votes, which is the number of votes the book has received on Goodreads
  • rating, which is the book’s average rating on Goodreads

Specification

For each of the following questions, you should write a single SQL query that finds its answer.

  1. What are the titles of all long listed books? Write your query in 1.sql.
  2. Which books were released in paperback? Write your query in 2.sql.
  3. Which books were translated by Sophie Hughes? Write your query in 3.sql.
  4. How many books are longer than 400 pages? Write your query in 4.sql.
  5. What’s the average length of a long listed book, in terms of pages? Write your query in 5.sql.
  6. Which books, long listed in 2023, were under 200 pages? Write your query in 6.sql.
  7. List all authors in alphabetical order. Write your query in 7.sql.
  8. What are the top 10 longest books? Write your query in 8.sql.
  9. List all unique publishers in alphabetical order. Write your query in 9.sql.