8. Doing more with queries

We have already seen some basic queries, how to order the results of a query and how to put conditions on the query output. Let us now see more examples of how we can modify our SELECT statements to suit our needs.

8.1 Counting the records in a table

Sometimes we just wish to know how many records exist in a table without actually outputting the entire contents of these records. This can be achieved through the use of a SQL function called COUNT. Let us first see the contents of the proglang_tbl table.

1 SELECT COUNT(*) FROM proglang_tbl;

The output returned will be a single record with a single field with the value as 4. The function COUNT took one argument i.e. what to count and we provided it with * which means the entire record. Thus we achieved our purpose of counting records in a table.

What would happen if instead of giving an entire record to count, we explicitly specify a column? And what if the column had null values? Let’s see this scenario by counting on the standard field of the table.

1 SELECT COUNT(standard) FROM proglang_tbl;

The output in this case would be the value 2, because we only have two records with non-null values in the standard field.

8.2 Column Aliases

Queries are frequently consumed directly as reports since SQL provides enough functionality to give meaning to data stored inside a RDBMS. One of the features allowing this is Column Aliases, which let you rename column headings in the resultant output. The general syntax for creating a column alias is given below.

1 SELECT <column1> <alias1>, <column2> <alias2> ... from <table>;

For example, we wish to output our programming languages table with a few columns only. But we do not wish to call the authors of the language as authors. The person wanting the report wishes they be called creators. This can be simply done by using the query below.

1 SELECT id, language, author creator from proglang_tbl;

While creating a column alias will not permanantly rename a field, it will show up in the resultant output.

8.3 Order of execution of SELECT queries

A query is not evaluated from left to right, there is a specific sequence in which its various parts are evaluated as given below.

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

There is an interesting corollary of having the SELECT evaluation being lower (read later) than the WHERE clause. Most database management systems, like Microsoft SQL Server will not allow you to use a column alias in the filtering conditions. So a query like the one given below would not work.

1 SELECT author Scientist FROM authors_tbl WHERE Scientist = 'Wall';

However, this works fine in SQLite. Yet another example of how there exists subtle differences between DBMSs’.

8.4 Using the LIKE operator

While putting conditions on a query using WHERE clauses, we have already seen comparison operators = and IS NULL. Now we take a look at the LIKE operator which will help us with wildcard comparisons. For matching we are provided with two wilcard characters to use with LIKE.

We will first use the % character for wildcard matching. Let us suppose we wish to list out languages that start with the letter P.

1 SELECT * FROM proglang_tbl WHERE language LIKE 'P%';

The output of the above query should be all language records whose name begins with the letter capital P. Note that this would not include any language that starts with the small letter p.

We can see that using the % wildcard allowed us to match multiple characters like erl in the case of Perl. But what if we wanted to restrict how many characters we wished to match? What if our goal was to write a query which displays the languages ending in the letter l, but are only 3 characters in length? The first condition could have been satisfied using the pattern %l, but to satisfy both conditions in the same query we use the _ wildcard. A pattern like %l would result in returning both Perl and Tcl but we modify our pattern suitably to return only the latter.

1 SELECT * FROM proglang_tbl WHERE language LIKE '__l';

Note that the result did not include Perl since we explicitly gave two underscores to match 2 characters only. Also it did not match APL since SQL data is case sensitive and l is not equal to L.