Shakespeare in a database

Shakespeare in a database

To learn more about this page, read the blog post. Geek note: this is all happening in your web browser, thanks to clever work by others (credits at the bottom of the page).

There are four tables in the database: works, chapters, paragraphs and characters. Rather than list out the structure, just go ahead and issue DESCRIBE works or SELECT * FROM works, and so on. Read the example below: if you know SQL, you should get the idea. If you like, download the whole database.

Try and predict the results of these examples before you run the query:

Longest-speaking characters The Tempest Common characters Longest speeches Longest plays

-- Which 10 characters appear in the most scenes? SELECT name, COUNT(*) AS scene_count FROM ( SELECT character_id, chapter_id, characters.name FROM paragraphs INNER JOIN characters on characters.id = paragraphs.character_id GROUP BY character_id, chapter_id ) AS t1 GROUP BY character_id ORDER BY scene_count DESC LIMIT 10;

Execute Save the db Load an SQLite database file: