User Tools

Site Tools


sample_queries

Sample Queries

Here's some sample queries to get someone started if they are looking to do some more advanced lookups. Each of these uses the SQLite export, and does JOINs across all the tables instead of using the normalized scriptures table.

In each query, it's important to remember to order the output by the verse IDs, since the natural ordering may shift around if you've made changes to your database. That possibility is rare, but it's going to keep your queries safe.

If you think of some cool queries, or want help writing some, contact me and it'd be fun to figure out!

Synoptic Gospels

Query all the verses from the Synoptic Gospels (Matthew, Mark, and Luke):

SELECT b.book_title || ' ' || c.chapter_number || ':' || v.verse_number AS verse_title, v.scripture_text FROM verses v JOIN chapters c ON v.chapter_id = c.id JOIN books b ON c.book_id = b.id JOIN volumes vo ON b.volume_id = vo.id WHERE b.book_title IN ('Matthew', 'Mark', 'Luke') ORDER BY v.id;

Partial result set:

Matthew 1:1|The book of the generation of Jesus Christ, the son of David, the son of Abraham.
Matthew 1:2|Abraham begat Isaac; and Isaac begat Jacob; and Jacob begat Judas and his brethren;
Matthew 1:3|And Judas begat Phares and Zara of Thamar; and Phares begat Esrom; and Esrom begat Aram;
Matthew 1:4|And Aram begat Aminadab; and Aminadab begat Naasson; and Naasson begat Salmon;
Matthew 1:5|And Salmon begat Booz of Rachab; and Booz begat Obed of Ruth; and Obed begat Jesse;
Matthew 1:6|And Jesse begat David the king; and David the king begat Solomon of her that had been the wife of Urias;
Matthew 1:7|And Solomon begat Roboam; and Roboam begat Abia; and Abia begat Asa;
Matthew 1:8|And Asa begat Josaphat; and Josaphat begat Joram; and Joram begat Ozias;
Matthew 1:9|And Ozias begat Joatham; and Joatham begat Achaz; and Achaz begat Ezekias;
Matthew 1:10|And Ezekias begat Manasses; and Manasses begat Amon; and Amon begat Josias;
Matthew 1:11|And Josias begat Jechonias and his brethren, about the time they were carried away to Babylon:
Matthew 1:12|And after they were brought to Babylon, Jechonias begat Salathiel; and Salathiel begat Zorobabel;
Matthew 1:13|And Zorobabel begat Abiud; and Abiud begat Eliakim; and Eliakim begat Azor;
Matthew 1:14|And Azor begat Sadoc; and Sadoc begat Achim; and Achim begat Eliud;
Matthew 1:15|And Eliud begat Eleazar; and Eleazar begat Matthan; and Matthan begat Jacob;

Random Book of Psalms

Display the verses from one chapter of the book of Psalms chosen randomly:

SELECT v.scripture_text FROM verses v WHERE v.chapter_id IN (SELECT c.id FROM chapters c JOIN books b ON b.id = c.book_id WHERE b.book_title = 'Psalms' ORDER BY RANDOM() LIMIT 1) ORDER BY v.id;

Result:

Praise ye the Lord. Praise ye the Lord from the heavens: praise him in the heights.
Praise ye him, all his angels: praise ye him, all his hosts.
Praise ye him, sun and moon: praise him, all ye stars of light.
Praise him, ye heavens of heavens, and ye waters that be above the heavens.
Let them praise the name of the Lord: for he commanded, and they were created.
He hath also stablished them for ever and ever: he hath made a decree which shall not pass.
Praise the Lord from the earth, ye dragons, and all deeps:
Fire, and hail; snow, and vapour; stormy wind fulfilling his word:
Mountains, and all hills; fruitful trees, and all cedars:
Beasts, and all cattle; creeping things, and flying fowl:
Kings of the earth, and all people; princes, and all judges of the earth:
Both young men, and maidens; old men, and children:
Let them praise the name of the Lord: for his name alone is excellent; his glory is above the earth and heaven.
He also exalteth the horn of his people, the praise of all his saints; even of the children of Israel, a people near unto him. Praise ye the Lord.
sample_queries.txt · Last modified: 2019/03/24 18:28 by steve