SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table books (
  id integer primary key,
  name text not null
);
create table chapters (
  id integer primary key,
  num integer not null,
  book_id integer not null references books(id),

  unique(book_id, num)
);
create table verses (
  id integer primary key,
  num integer not null,
  content text not null,
  chapter_id integer not null references chapters(id),

  unique(chapter_id, num)
);
insert into books (id, name) values (1, 'Genesis'), (2, 'Exodus');
-- Add some chapters to Genesis
insert into chapters (num, book_id) values (1, 1), (2, 1), (3, 1);
-- And one to Exodus
insert into chapters (num, book_id) values (1, 2), (2, 2);
-- And a couple verses to Genesis 1
insert into verses (num, content, chapter_id) values
  (1, 'let there be cheese', 1), (2, 'and it was gouda', 1);
select
  books.name, count(chapters.id)
from books
join chapters on chapters.book_id = books.id
group by books.id;


name
count(chapters.id)


Genesis
3

Exodus
2


select
  books.name, count(verses.id)
from books
join chapters on chapters.book_id = books.id
join verses on verses.chapter_id = chapters.id
group by books.id


name
count(verses.id)


Genesis
2


select
  printf("%s %d:%d", books.name, chapters.num, verses.num) as heading,
  verses.content
from books
join chapters on chapters.book_id = books.id
join verses on verses.chapter_id = chapters.id


heading
content


Genesis 1:1
let there be cheese

Genesis 1:2
and it was gouda