SQL Queries

In SQL syntax, the SELECT keyword is the defining part of a query. The simplest query involves retrieving data from one the tables within a database. For example, Figure 1 shows how to retrieve an entire table’s data. This example is based on a table called products that was developed for an article in this series entitled What Is A Database?

The SELECT keyword is the most complex keyword available in SQL, as it has many additional options and related keywords. To give you an idea of how powerful a SELECT statement can be, see Figure 2 for the PostgreSQL syntax guide to the SELECT keyword.

As you can see, many operations can be performed with a query, and the SELECT command is capable of presenting the results of those operations in many different ways.

An example of a more complicated SQL statement can be seen in Figure 3.

In this query, a number of things are occurring. First, the columns name and price are being retrieved from the products table, but they are being temporarily renamed, for the purpose of this query only, to Product Name and Product Price, respectively.

The output is filtered to include only rows that have an id that matches the regular expression “7”. This regular expression is not particularly special; it simply matches any id that contains the number 7. Further, the query sorts, in ascending order by default, the result set by the value of the price column.

Of the three rows in the products table, two have an id that contains the number 7, as seen in Figure 1. Their name and price columns are shown, and the rows are sorted in ascending order by price.

The SELECT instruction is perhaps most powerful when used in conjunction with the JOIN operator. With the pairing of SELECT and JOIN, SELECT can retrieve data from any number of objects in the database and present them in infinitely varied ways.

The JOIN operator is covered in another article in this series.

Other Basic SQL Commands

While SELECT is the most common instruction used in SQL, a database cannot be used to its full extent with that keyword alone. More commands will be covered later in this series of articles on SQL, but for now, it would be beneficial to briefly mention some of the other important commands.

Tables are created with the CREATE TABLE command; INSERT is used to add rows to a table; UPDATE modifies records in a table; DELETE FROM removes data from a table; BEGIN WORK, COMMIT, and ROLLBACK are used to control transactions; and GRANT and REVOKE are used to manage users’ access to a database.

Some databases also allow the creation of user-defined functions, which the database developer can customize to fit a specific application. One example of this type of a function might be to convert English measurements into metric measurements “in place” in a query.