The Structured Query Language (SQL) is the computer language in which a user or an application communicates with an SQL-capable database system. SQL database systems include PostgreSQL, MySQL, Oracle, and many others in popular use.

The language is standardized by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO), but many database systems provide their own extensions to the standard or do not fully implement the standard. These extensions typically extend SQL functionality into an altogether different programming language or markup language.

SQL was first developed in the early 1970s for use with System R, a database system created by IBM.

Using SQL

Most database systems use a client-server model to facilitate interaction between an application and the database. The server is essentially a “front end” to the database, which interprets SQL and directs the database to perform certain operations.

The client can be anything from a relatively simple command line program like PostgreSQL’s psql to a web site or a complex software application that manages something like government personnel and technology infrastructure.

The client usually sends SQL instructions to the server via a network socket, if the database server is running on another computer or is running Microsoft Windows. If the client and the server are both running on UNIX or a UNIX-like operating system, they may communicate via a UNIX socket.

Due to the strictly local nature of a UNIX socket, it generally provides a much more secure method for database access. Some database systems provide facilities for secure, encrypted access over a network.

When the server receives the SQL instructions from the client, it performs the requested action on the database and returns a result to the client. The result can be as simple as a statement meaning “OK, that worked” or as complicated as a million-line set of data that was retrieved from the database.

Parts of SQL

SQL is composed of a number of different elements: queries, statements, expressions, predicates, and clauses. By far, the most commonly used of these elements is the query. Of particular note in SQL syntax are two symbols, the asterisk (“*”), which is a wildcard that means “everything”, and the semicolon (“;”), which is always used at the end of a command. The semicolon terminator is not required on all SQL platforms.

{qbapagebreak title=SQL Queries}

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.