CMIS 320
Name:_________________________


Download the bible.zip file, unzip it to extract the bible_kjv.txt

Create the Bible table using bible.sql (which loads bible_kjv.txt).

Note that it puts a Fulltext index on the verse column.

What is the size of the bible_kjv.txt in KB, MB and bytes:__________

Answer these queries:

0. When the text file is loaded, how many records are inserted:_______
   When the indexes are added to the table, how many records are
   affected and how much time does it take:_________________
   How does DESCRIBE indicate the Indexes:_____________
   How does SHOW CREATE TABLE indicate the various indexes: __________
   ____________________
   What is the engine type:____________

Paste SHOW CREATE TABLE bible\G


1. The number of verses, the total length of the verses, the average, min,
and max lengths (in number of characters) of the verses.
a. How many verses and characters in the Old Testament:
b. How many verses and characters in the New Testament:

2. The row with the shortest verse.

3. The book, chapter and verse number of the longest verse.

4. The book with the most verses.

5. The book with the most characters.

6. The book with the shortest average verse length.

7. In how many verses does 'Jesus' appear in the Old Testament?

8. The number of verses containing Magdalene.

9. The number of verses a fulltext search for Magdalene returns.

10. The relevance scores of those matches.


11. The number of verses containing Ethiopia.

12. The verses that contain Ethiopia more than once.

13. The longest verse that contains Ethiopia.

14. The number of verses a fulltext search for Ethiopia returns.

15. The relevance scores of those matches and the lengths of them.
    What relation between these does there seem to be?

16. The book with the most number of chapters.

17. The book(s) with one chapter.