Longlist (Part 2)

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/1/longlist-part-2.zip

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

Then execute

unzip longlist-part-2.zip

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

rm longlist-part-2.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-part-2/ $

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:

authors table
  • id, which uniquely identifies each author
  • name, which is the author’s name
  • country, which is the author’s home country
  • birth, which is the author’s birth year
authored table
  • author_id, which references the author who wrote the book
  • book_id, which references the book written by that author
books table
  • id, which uniquely identifies each book
  • isbn, which is the International Standard Book Number for the book
  • title, which is the title of the book
  • publisher_id, which references the publisher of the book
  • format, which indicates the format or medium of the book (e.g., paperback, hardcover)
  • pages, which indicates how many pages the book has
  • published, which indicates the book’s publication date
  • year, which is the year the book was long listed
publishers table
  • id, which uniquely identifies each publisher
  • publisher, which is the name of the publishing company
ratings table
  • book_id, which references the book that received a rating
  • rating, which is the rating a single user gave the book
translators table
  • id, which uniquely identifies each translator
  • name, which is the name of the translator
translated table
  • translator_id, which references the translator who worked on the book
  • book_id, which references the book that was translated

Specification

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

  1. In 1.sql, write a query to find all books published by Yale University Press.
  2. In 2.sql, write a query to find the average rating of The Years.
  3. In 3.sql, write a query to find all books written by Han Kang.
  4. In 4.sql, write a query to find all books translated by Sophie Hughes.
  5. In 5.sql, write a query of your choice that joins two tables.