Advanced SQL SELECT Queries

Because SELECT is the Swiss Army Knife of data retrieval, only a handful of features can be mentioned in a single article. The best places to learn about the true power of SELECT are SQL reference books and database manuals, but in this section, a handful of the more common advanced features of SELECT will be presented.

Ads

A useful feature implemented in most SQL database is the COUNT() function. This function, rather than returning a set of rows, counts the rows that match the query and returns the count. In Figure 4, we saw that the books table contains four rows in which the author is Terry Goodkind. If we only wanted to know how many books in the table were written by Goodkind, it would be a good idea to simply make use of the COUNT() function. This can be seen in Figure 5.

The concept of a view in SQL is another useful feature for database developers. A view is a virtual table that can be used to see only a particular subset of data. When the underlying data in the database is altered, the changes also appear in the view. A simple example of using a view to see only the titles of books by Terry Goodkind is shown in Figure 6.

Essentially, a view is helpful for giving a result set permanence. After using CREATE VIEW, the view remains persistent in the database until a DROP VIEW command is executed. As shown in Figure 6, SELECT operations can be performed on a view during the lifetime of the view.

Data manipulation within a SELECT command is also possible and is quite useful if the user or application requires a value that is easily calculated from data already in the database.

Using a table with some data from the football game between the Pittsburgh Steelers and Cleveland Browns on November 11, 2007, we can use SELECT and CAST to determine the percentage of the quarterbacks’ attempted passes that were completed. This calculation is based only on the raw integers for attempted passes (passes_attempted) and completed passes (passes_completed) from the underlying table, called football. This interesting calculation is shown in Figure 7.

In this example, passes_completed and passes_attempted are CAST to FLOAT values. Then, a percentage is computed by dividing the two values, and for ease of readability, the result is multiplied by 100. This value is cast again to NUMERIC(3, 1), which further improves readability by limiting the places after the decimal point to one. The resulting value is shown in the result set AS the percent_completed column. The SQL data types and the CAST operator are discussed separately in this series of articles.

Ads

These few examples merely depict the basic feature of the SELECT command. In other articles in this series, we cover more powerful features of SELECT. Especially of interest should be the article about the JOIN operator, which provides a method for neatly and efficiently integrating data from many objects within a database.

First Page: SQL Overview