SQL Overview

The SELECT command in SQL provides a robust means for retrieving data from a database. More specifically, SELECT returns a result set of zero or more rows from the database, and this result set corresponds to the query that was executed. The result set is calculated by the query optimizer inside the database system, based on the information inside the database. SELECT operates on base tables, temporary tables, functions, and views in a database.

Basic SQL SELECT Queries

The fundamental idea behind a SELECT query is to ask the database to show the user a set of data that fits certain criteria.

Figure 1 shows a SELECT query that asks the database to return books written by Dan Brown. This example displays every fundamental feature of the SELECT command. We can dissect this query and figure out how the database interpreted it.

The very first part of the query, SELECT *, instructs the database that the user wants to view all the columns associated with the resulting rows. This is why the result set contains the date, title, and author columns, even though they were not specified in the query.

After SELECT * comes FROM books, which tells the database the name of the table in which it should try to find the data. books is the name of a table in this database.

The WHERE author = ‘Brown, Dan’ clause lets the database know the user only wants to see books written by Dan Brown.

Two rows matched the requirements of the query, and they were returned to the user in a result set after less than a millisecond of processing.

The output order of the rows and columns in the result set is not necessarily deterministic; that is, SELECT * FROM books could output the rows and columns in any order. In PostgreSQL, the d [table_name] command will show the default column order, but it should not be considered reliable. Other database systems have similar commands.

Figure 2 shows that the order of the columns in the output can be altered by explicitly specifying the order. This only affects the output and does not change the order of the columns as they are stored in the database.

Figure 3 shows another similar query in which the date column is omitted from the result set.

Another basic but important function of SELECT is sorting the result set. This is accomplished by using the ORDER BY keyword. In most SQL databases, the default behavior is to sort in ascending order, but this can be explicitly set in the SELECT command with the ASC and DESC keywords. These two sorting methods can be seen in Figure 4.

{qbapagebreak title=Advanced SQL SELECT Queries}

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.

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.

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.