CMIS 320

If you haven't made the "japan" table and data file:
Generate a 1,000,000 record "japanese" random data file using the
createDB program. 
Create a table using the Create Table statement in createDB.cpp file
on the web site.
Load the data file into the table using the web interface Load Text File.


Answer these queries. Use one query each.

How many rows are there?
SELECT Count(*) FROM japan;
For each column (where the datatype is appropriate), what is the sum,
average, min, max, and number of different values?
SELECT Count(DISTINCT bigint),Sum(bigint),Min(bigint),Max(bigint),Avg(bigint) FROM japan;
SELECT Count(DISTINCT littleint),Sum(littleint),Min(littleint),Max(littleint),Avg(littleint) FROM japan;
SELECT Count(DISTINCT bigword),Min(bigword),Max(bigword) FROM japan;
SELECT Count(DISTINCT grade),Min(grade),Max(grade) FROM japan;
SELECT Count(DISTINCT smallword),Min(smallword),Max(smallword) FROM japan;
SELECT Count(DISTINCT doub1),Sum(doub1),Min(doub1),Max(doub1),Avg(doub1),Median(doub1) FROM japan;
SELECT Count(DISTINCT doub2),Sum(doub2),Min(doub2),Max(doub2),Avg(doub2),Median(doub2) FROM japan;

What is the average number of rows that a bigword value is in?
(i.e. the average number of times each bigword occurs)
SELECT Count(*)/Count(DISTINCT bigword) FROM japan;

Display the records whose 6-letter word starts with zu, small
integer is between 50 and 60, grade is F, doub1 is less than .3
and 4-letter word starts with tu.
SELECT * FROM japan WHERE bigword LIKE 'zu%' AND littleint BETWEEN 50 AND 60 AND grade='F' AND doub1<.3 AND smallword LIKE 'tu%';
Moral of this example: one million is a lot.  If the display is 4
records per inch, then one million records is about 4 miles long.  If
60 records are printed per page, then one million records is about 55
reams of paper (about 3 miles long).

Display the record with the largest first field.  hint: subquery
SELECT * FROM japan WHERE bigint=(SELECT Max(bigint) FROM japan);

How many records have their small integer field equal to the integer
part of the average value of the small integer field?  hint: subquery
and Round()
SELECT Count(*) FROM japan WHERE littleint=(SELECT Round(Avg(littleint)) FROM japan);

How many rows have bigword 'gagaga'?
SELECT Count(*) FROM japan WHERE bigword='gagaga';
How many rows have bigword that starts with 'gaga'?
SELECT Count(*) FROM japan WHERE bigword LIKE 'gaga__';
How many rows have bigword that starts with 'gaga' but is not 'gagaga'?
SELECT Count(*) FROM japan WHERE bigword LIKE 'gaga__' AND bigword!='gagaga';
How many rows have bigword that starts with 'ga'?
SELECT Count(*) FROM japan WHERE bigword LIKE 'ga____';
How many rows have bigword that starts with 'ga' but does not have any
other ga's in bigword?
SELECT Count(*) FROM japan WHERE bigword LIKE 'ga____' AND bigword NOT LIKE '__%ga%';

How many of the records has its 4-letter word as the prefix of its
6-letter word?  hint: Substr()
SELECT Count(*) FROM japan WHERE Substr(bigword,1,4)=smallword;



oops, don't do this.  need changing random sequence version of
createDB.cpp
Generate another "japan" random data file, this one of 10,000 records.
Load it into a copy of the original table named japan2.
Display the join of the two tables on the first field, displaying the
first field and the 6-letter words from each table.