Create the kjv Bible table using the kjv.sql and kjv.txt files. Answer these queries: The number of verses, the total length of the verses, the average, min, and max lengths (in number of characters) of the verses. SQL> select count(*),sum(length(vtext)),min(length(vtext)),max(length(vtext)),avg(length(vtext)) from kjv; COUNT(*) SUM(LENGTH(VTEXT)) MIN(LENGTH(VTEXT)) MAX(LENGTH(VTEXT)) AVG(LENGTH(VTEXT)) ---------- ------------------ ------------------ ------------------ ------------------ 31102 4156678 11 534 133.646647 The row with the shortest verse. SQL> select * from kjv where length(vtext)=(select min(length(vtext)) from kjv); BSE BNAME BNUM CNUM VNUM VTEXT --- --------------- ---------- ---------- ---------- --------------------------------- N John 43 11 35 Jesus wept. The row with the longest verse. SQL> select * from kjv where length(vtext)=(select max(length(vtext)) from kjv); BSE BNAME BNUM CNUM VNUM --- --------------- ---------- ---------- ---------- VTEXT ---------------------------------------------------------------------------------------------------- O Esther 17 8 9 Then were the king's scribes called at that time in the third month, that [is], the month Sivan, on the three and twentieth [day] thereof; and it was written according to all that Mordecai commanded u nto the Jews, and to the lieutenants, and the deputies and rulers of the provinces which [are] from India unto Ethiopia, an hundred twenty and seven provinces, unto every province according to the wri ting thereof, and unto every people after their language, and to the Jews according to their writing , and according to their language. The book with the most verses. SQL> select * from (select bname,count(*) from kjv group by bname order by count(*) desc) where rownum<=1; BNAME COUNT(*) --------------- ---------- Psalms 2461 The book with the fewest characters. SQL> select * from (select bname,sum(length(vtext)) from kjv group by bname order by sum(length(vtext))) where rownum<=1; BNAME SUM(LENGTH(VTEXT)) --------------- ------------------ 2 John 1546 The book with the shortest average verses. SQL> select * from (select bname,avg(length(vtext)) from kjv group by bname order by avg(length(vtext))) where rownum<=1; BNAME AVG(LENGTH(VTEXT)) --------------- ------------------ Proverbs 88.1715847 How many times does 'Jesus' appear in the Old Testament? SQL> select count(*) from kjv where bsect='O' and vtext like '%Jesus%'; COUNT(*) ---------- 0 The number of verses containing Ethiopia. SQL> select count(*) from kjv where vtext like '%Ethiopia%'; COUNT(*) ---------- 38 The verses in which Ethiopia occurs more than once. SQL> select * from kjv where vtext like '%Ethiopia%Ethiopia%'; BSE BNAME BNUM CNUM VNUM --- --------------- ---------- ---------- ---------- VTEXT ---------------------------------------------------------------------------------------------------- O Numbers 4 12 1 And Miriam and Aaron spake against Moses because of the Ethiopian woman whom he had married: for he had married an Ethiopian woman. O 2 Chronicles 14 14 12 So the LORD smote the Ethiopians before Asa, and before Judah; and the Ethiopians fled. N Acts 44 8 27 And he arose and went: and, behold, a man of Ethiopia, an eunuch of great authority under Candace qu een of the Ethiopians, who had the charge of all her treasure, and had come to Jerusalem for to wors hip, The number of words..... SQL> select sum(word_count(vtext)) from kjv; SUM(WORD_COUNT(VTEXT)) ---------------------- 790868 use the word_count function The most wordy verse. SQL> select * from kjv where word_count(vtext)=(select max(word_count(vtext)) from kjv); BSE BNAME BNUM CNUM VNUM --- --------------------------------------------- ---------- ---------- ---------- VTEXT ------------------------------------------------------------------------------------------------------------------------------------------------- O Esther 17 8 9 Then were the king's scribes called at that time in the third month, that [is], the month Sivan, on the three and twentieth [day] thereof; and it was written according to all that Mordecai commanded unto the Jews, and to the lieutenants, and the deputies and rulers of the provinces which [ are] from India unto Ethiopia, an hundred twenty and seven provinces, unto every province according to the writing thereof, and unto every people after their language, and to the Jews according to their writing, and according to their language. The most wordy book. SQL> select * from (select bname,sum(word_count(vtext)) from kjv group by bname order by sum(word_count(vtext)) desc) where rownum<=1; BNAME SUM(WORD_COUNT(VTEXT)) --------------------------------------------- ---------------------- Psalms 43738 The average, median, standard deviation of number of words per verse. SQL> select avg(word_count(vtext)),stddev(word_count(vtext)),median(word_count(vtext)) from kjv; AVG(WORD_COUNT(VTEXT)) STDDEV(WORD_COUNT(VTEXT)) MEDIAN(WORD_COUNT(VTEXT)) ---------------------- -------------------------------------------------- 25.428204 11.0723121 24 The correlation between the number of characters and the number of words of teh verses. SQL> select corr(length(vtext),word_count(vtext)) from kjv; CORR(LENGTH(VTEXT),WORD_COUNT(VTEXT)) ------------------------------------- .984035156 The ratio of the average verse length of New Testament books to average verse length of Old Testament books. SQL> select (select avg(length(vtext)) from kjv where bsect='N')/(select avg(length(vtext)) from kjv where bsect='O') from dual; (SELECTAVG(LENGTH(VTEXT))FROMKJVWHEREBSECT='N')/(SELECTAVG(LENGTH(VTEXT))FROMKJVWHEREBSECT='O') ---------------------------------------------------------------------------- .860934389