Now, with GROUPBY clauses come the HAVING filters. The HAVING filter is to be used only with a GROUPBY clause, and taking the previous queries as an example, say we want to group by the number of states in our table, but we only care about states that appear a certain number of times. With the HAVING filter, we can essentially phrase our query such that it groups by the number of states, and then only returns those states having a total count greater or less than some value.

Let's take a look at the following code and pay close attention to how I structure my query (it will look very similar to the GROUPBY query but with an extra filter at the end):

/*
* HAVING Filter
*/

System.out.println("METHOD 1");

// METHOD #1 - SQLITEDATABASE RAWQUERY()
String colName = "COUNT(" + StudentTable.STATE + ")";

c = sqdb.rawQuery("SELECT " + StudentTable.STATE + "," + colName + " from " + StudentTable.TABLE_NAME + " GROUP BY " + StudentTable.STATE + " HAVING " + colName + " > 1", null);

while (c.moveToNext()) {
  int colid = c.getColumnIndex(StudentTable.STATE);
  int colid2 = c.getColumnIndex(colName);

}

System.out.println("METHOD 2");

// METHOD #2 - SQLITEDATABASE QUERY()
c = sqdb.query(StudentTable.TABLE_NAME, new String[] { StudentTable.STATE, colName }, null, null, StudentTable.STATE,colName + " > 1", null);


System.out.println("METHOD 3");

// METHOD #3 - SQLITEQUERYBUILDER
query = SQLiteQueryBuilder.buildQueryString(false, StudentTable.TABLE_NAME, new String[] { StudentTable.STATE,  colName }, null, StudentTable.STATE, colName + " > 1", null, null);
System.out.println(query);
c = sqdb.rawQuery(query, null);

And so you have it. Again, notice the structure of my query in the first method and notice how it translates into the HAVING parameter of the query convenience methods in the second and third methods. Let's see now how the query did and whether or not it eliminated AR from the output:

Perfect – pretty straightforward. Earlier we ran into the COUNT() aggregate function, which along with SUM() and AVG() are amongst the most popular of the aggregate functions (see here for the full list: http://www.sqlite.org/lang_aggfunc.html). These functions, like their names suggest, either count the total number of rows returned in a particular column of your subtable, or sum of the values of that column in your subtable, or average of the values of that column in your subtable, and so on. First, let's examine some of these aggregate functions, listed as follows (notice how the column names change):

/*
* SQL Functions - MIN/MAX/AVG
*/

System.out.println("METHOD 1");

// METHOD #1 - SQLITEDATABASE RAWQUERY()
String colName = "MIN(" + StudentTable.GRADE + ")";

c = sqdb.rawQuery("SELECT " + colName + " from " + StudentTable.TABLE_NAME, null);
while (c.moveToNext()) {
  int colid = c.getColumnIndex(colName);
  int minGrade = c.getInt(colid);
  System.out.println("MIN GRADE " + minGrade);
}

System.out.println("METHOD 2");

// METHOD #2 - SQLITEDATABASE QUERY()
colName = "MAX(" + StudentTable.GRADE + ")";

c = sqdb.query(StudentTable.TABLE_NAME, new String[] { colName }, null, null, null, null, null);


System.out.println("METHOD 3");

// METHOD #3 - SQLITEQUERYBUILDER
colName = "AVG(" + StudentTable.GRADE + ")";

query = SQLiteQueryBuilder.buildQueryString(false, StudentTable.TABLE_NAME, new String[] { colName }, null,
null, null, null, null);
System.out.println(query);
c = sqdb.rawQuery(query, null);
while (c.moveToNext()) {
  int colid = c.getColumnIndex(colName);
  double avgGrade = c.getDouble(colid);
  System.out.println("AVG GRADE " + avgGrade);
}

So, here we use each of the three methods to test out a different aggregate function. The results are shown as follows:

After referencing the state of the table from earlier, you can quickly validate the outputted numbers and confirm that the functions are indeed doing as they should. Outside of aggregate functions (which are typically used for numerical-typed columns), SQLite also provides you with an assortment of other core functions that help you manipulate everything from Strings to Date types, and so on. A complete list of these core functions can be found http://www.sqlite.org/lang_corefunc.html but for now, let's just take a look at a couple:

/*
* SQL Functions - UPPER/LOWER/SUBSTR
*/

System.out.println("METHOD 1");

// METHOD #1 - SQLITEDATABASE RAWQUERY()
String colName = "UPPER(" + StudentTable.NAME + ")";

c = sqdb.rawQuery("SELECT " + colName + " from " + StudentTable.TABLE_NAME, null);
while (c.moveToNext()) {
  int colid = c.getColumnIndex(colName);
  String upperName = c.getString(colid);
  System.out.println("GOT STUDENT " + upperName);
}

System.out.println("METHOD 2");

// METHOD #2 - SQLITEDATABASE QUERY()
colName = "LOWER(" + StudentTable.NAME + ")";

c = sqdb.query(StudentTable.TABLE_NAME, new String[] { colName }, null, null, null, null, null);


System.out.println("METHOD 3");

// METHOD #3 - SQLITEQUERYBUILDER
colName = "SUBSTR(" + StudentTable.NAME + ",1,4)";

query = SQLiteQueryBuilder.buildQueryString(false, StudentTable.TABLE_NAME, new String[] { colName }, null,
null, null, null, null);
System.out.println(query);
c = sqdb.rawQuery(query, null);

Again, here is the associated output of these core functions:

Now, as far as how much of a performance boost running some of these functions in SQLite as opposed to just doing them on the Java side, this is debatable and is highly dependent on the size of your database and the function you are calling. For instance, some string manipulation functions may not offer as much of a performance boost as other more complex aggregate functions. In fact, this SQLite to Java comparison is something we'll look more into in the next section, but regardless, it's always better to be aware of the functions available to you in SQLite and add them to your arsenal of weapons!

And lastly, it's about time we looked at the benefits of using the SQLiteQueryBuilder (until now, much of the syntax was very similar to just the query() method in SQLiteDatabase) and see how we can leverage this convenience class to perform more complicated joins:

/*
* SQL JOINS
*/

SQLiteQueryBuilder sqb = new SQLiteQueryBuilder();

// NOTICE THE SYNTAX FOR COLUMNS IN JOIN QUERIES
String courseIdCol = CourseTable.TABLE_NAME + "." + CourseTable.ID;

String classCourseIdCol = ClassTable.TABLE_NAME + "." + ClassTable.COURSE_ID;

String classIdCol = ClassTable.TABLE_NAME + "." + ClassTable.ID;

sqb.setTables(ClassTable.TABLE_NAME + " INNER JOIN " + CourseTable.TABLE_NAME + " ON (" + classCourseIdCol + " = " + courseIdCol + ")");

String[] cols = new String[] { classIdCol, ClassTable.COURSE_ID, CourseTable.NAME };

query = sqb.buildQuery(cols, null, null, null, null, null, null);

System.out.println(query);
c = sqdb.rawQuery(query, null);
while (c.moveToNext()) {
  int colid = c.getColumnIndex(0);
  int colid2 = c.getColumnIndex(cols[1]);
  int colid3 = c.getColumnIndex(cols[2]);
  int rowId = c.getInt(colid);
  int courseId = c.getInt(colid2);
  String courseName = c.getString(colid3);
  System.out.println(rowId + " || COURSE ID " + courseId + "|| " + courseName);
}

First, let me point out a couple of things specific to JOIN statements. In essence, a JOIN statement allows you to connect two tables based on some column values. For example, in our case, our schema was built so that we would have a table for classes and each class would be a mapping between the student ID and the course ID. But, let's say that we want to quickly know not just what the class mappings are but also the course's name for each mapping (that is, the name of the course and who is taking that class). Instead of having to return all the class mappings as well as the course listings (that is, asking for two tables back) and then manually doing these lookups, we can use an SQL JOIN statement to return a joint table.

Now, because when doing JOIN statements we are asking for multiple tables back, oftentimes when you ask for specific columns to return, you'll need to specify what table the column comes from. For instance, consider a situation where both tables have ID fields – in this case, simply asking for the ID column will cause an error, as it's ambiguous which table's ID column you really want. This is what we're doing when we initiate the strings courseIdCol, classIdCol, and classCourseIdCol, and the syntax is simply as follows:

table_name.column_name

Then in our SQLiteQueryBuilder class, we use the method setTables() to format our JOIN statement. Again, you can see the exact syntax that we used in the previous example, but the general format is first you specify the two tables that you want to join and then you tell the query what kind of JOIN you want (in our case, we want to use an INNER JOIN). Afterwards, you need to tell the query what two columns to perform the JOIN on, and again, in our case, we want to connect the two tables by the course ID, and so we specify the course ID column of our Class table and also specify the corresponding course ID column of our Course table. By doing this, the JOIN statement knows that for each class mapping, it should take the course ID and then go to the Course table and find that corresponding course ID and append that row of the table to the Class table. For an in-depth discussion on both the different kinds of JOINs as well as the syntax for each, I invite you to look at http://www.w3schools.com/sql/sql_join.asp and read through the documentation. The output for the previous JOIN statement is as follows:

And so you can immediately see both the syntax of the query as well as the results.