SQL and Bible Studies:- Number of Chapters in each book

Background

As I play around a bit more with MySQL, and needed a database to play with, I sometimes return to a bible database.

ScrollMapper

bible_databases

GitHub

ScrollMapper’s bible_databases is available on GitHub.

Here is a link to the repository:-

scrollmapper / bible_databases
Link

Textual

bible-mysql.sql (MySQL) is the main database and most feature-oriented due to contributions from developers. It is suggested you use that for most things, or at least convert the information from it.
cross_references-mysql.sql (MySQL) is the cross-reference table. It has been separated to become an optional feature. This is converted from the project at http://www.openbible.info/labs/cross-references/.

Image

Explanation

  1. Interestingly enough, the MySQL implementation is the foundational and most feature-rich implementation.

Simple Query

Let us work on our SQL a bit and get a count of the number of chapters in each bible book.

SQL

SQL – Number of Chapters in a each book

select
          
           tblKE.b as 'bookID'
           
         , tblKE.n as 'bookName' 
         
         , tblKE.t as 'testament'
         
         , tblG.n  as 'genre'
         
         , max(tblB.c) as 'numberofChapters' 
    
from key_english tblKE 

inner join t_kjv tblB

        on tblKE.b = tblB.b
        
inner join key_genre_english tblG

    on tblKE.g = tblG.g

group by 
          tblKE.b
          
        , tblKE.t
            
        , tblKE.n

        , tblG.n
         
having  max(tblB.c)  = 1
                    
order by

      tblKE.b

SQL – Get books that only have a single chapter


use bible;

select
          
           tblKE.b as 'bookID'
           
         , tblKE.n as 'bookName' 
         
         , tblKE.t as 'testament'
         
         , tblG.n  as 'genre'
         
         , max(tblB.c) as 'numberofChapters' 
    
from key_english tblKE 

inner join t_kjv tblB

        on tblKE.b = tblB.b
        
inner join key_genre_english tblG

    on tblKE.g = tblG.g

group by 
          tblKE.b
          
        , tblKE.t
            
        , tblKE.n

        , tblG.n
         
having  max(tblB.c)  = 1
                    
order by

      tblKE.b



Output – Textural
Book Number Book Name Testament Genre Number of Verses
31 Obadiah OT Prophets 21
57 Philemon NT Epistles 25
63 2 John NT Epistles 13
64 3 John NT Epistles 14
65 Jude NT Epistles 25
Output – Image

Summary

So there you have it, there are five books with a single chapter.

Each of the cited five books have an average of twenty (20) verses.

One thought on “SQL and Bible Studies:- Number of Chapters in each book

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s