SQL and Bible Studies:- Books in each Genre

Background

The Bible is a collection of books.

One can categorize the books in so many different ways.

In this post, we will use someone else work.

He has categorized bible books into genres.

 

SQL

The work avails a couple of database platforms.

In this post, we will provide working samples for Transact SQL ( Microsoft SQL Server ) and MySQL.

 

SQL Server – Transact SQL


use bible;

select 

          tblKGE.g as id

        , tblKGE.n as genre
        
        , [books]
            = STRING_AGG
              ( 
                    tblKE.n 
                  , ', ' 
              ) 
              within group
              (
                order by
                  tblKE.b                      
              )
                      
        , [numberofBooks]
            = count
                (
                    tblKE.b
                )                 
              
        , [bookIDRange]
            = concat(
                          cast(min(tblKE.b) as varchar(2))
                        , ' - to- '
                        , cast(max(tblKE.b) as varchar(2))
                    ) 
                      
from   key_english tblKE

inner join key_genre_english tblKGE

        on tblKE.g = tblKGE.g
        
group BY 
      tblKGE.g
    , tblKGE.n
    
order BY 
      tblKGE.g
    , tblKGE.n    
;

MySQL


use bible;

select 

          tblKGE.g as id

        , tblKGE.n as genre
        
        , GROUP_CONCAT( 
                        tblKE.n 
                        ORDER BY tblKE.b  
                        SEPARATOR ', ' 
                      ) as "books"
                      
        , count(
                    tblKE.b
                )  as "numberofBooks"                      
                      
        , concat(
                    cast(min(tblKE.b) as char)
                    , ' - to- '
                    , cast(max(tblKE.b) as char)
                )  as "bookIDRange"
        
from   key_english tblKE

inner join key_genre_english tblKGE

        on tblKE.g = tblKGE.g
        
group BY 
      tblKGE.g
    , tblKGE.n
    
order BY 
      tblKGE.g
    , tblKGE.n    
;

Output – Textural
# Genre books book ID Range
1 Law Genesis, Exodus, Leviticus, Numbers, Deuteronomy 1 to 5
2 History Joshua, Judges, Ruth, 1 Samuel, 2 Samuel, 1 Kings, 2 Kings, 1 Chronicles, 2 Chronicles, Ezra, Nehemiah, Esther 6 – to- 17
3 Wisdom Job, Psalms, Proverbs, Ecclesiastes, Song of Solomon 18 – to- 22
4 Prophets Isaiah, Jeremiah, Lamentations, Ezekiel, Daniel, Hosea, Joel, Amos, Obadiah, Jonah, Micah, Nahum, Habakkuk, Zephaniah, Haggai, Zechariah, Malachi 23 – to- 39
5 Gospels Matthew, Mark, Luke, John 40 – to- 43
6 Acts Acts 44 – to- 44
7 Epistles Romans, 1 Corinthians, 2 Corinthians, Galatians, Ephesians, Philippians, Colossians, 1 Thessalonians, 2 Thessalonians, 1 Timothy, 2 Timothy, Titus, Philemon, Hebrews, James, 1 Peter, 2 Peter, 1 John, 2 John, 3 John, Jude 45 – to- 65
8 Apocalyptic Revelation 66 – to- 66
Output – Image

Summary

Nothing new here.

The work is already done by Scrollmapper.

It is on GitHub and available here:-

https://github.com/scrollmapper/bible_databases ( Link )

 

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