Introduction to SQL: Structured Query Language

Structured Query Language, or SQL for short, is the standard method used to retrieve information from relational databases such as Access, Oracle, or MySQL. It was developed at IBM in the early 1970s and first offered commercially by Relational Software, now known as Oracle. SQL was originally developed under the acronym SEQUEL (Structured English QUEry Language) and many people still pronounce it that way.

To understand how SQL works, it is necessary to know how a relational database stores information. A database is a collection of tables, each consisting of a number of rows that are divided into columns. (It is also quite common to refer to columns as fields.) You can imagine a table as a spreadsheet, with each row containing information about a single item and that information split into several columns.

The central command in SQL is the SELECT statement, which has the following format:
SELECT (column list) FROM (table list) WHERE (conditions) ORDER BY (column list)

It is standard practice, although not required, that the words which are part of the SQL language appear in uppercase while the column names, table names and other variable parts appear in lower case or mixed case. In those places where a list may appear, you may enter a single item or a list of items separated by commas.

Imagine you had a database about your company's employees. It might have a table called employees that includes each employee's ID number, name and date of hire, as well as other information. If you wanted to find all employees hired after December 31, 1999 in order by hire date, you could use the following command, or "query" as they are usually called when working with SQL:

SELECT empl_id, name, hire_date FROM employees WHERE hire_date > 1999-12-31 ORDER BY hire_date

That is useful enough, but you could find the same information by sorting columns in any spreadsheet program. The real power of SQL and relational databases is the ability to retrieve more information by linking data from two different sources.

Imagine that your database contains another table called jobs that lists the employees who work on different jobs. If the job table contains the employee ID number as a column, you can link the job table and the employee table together by the employee ID number. You could modify your earlier query to find all of the jobs that involved employees hired after December 31, 1999 in sequence by job name and employee name.

SELECT jobs.name, employees.empl_id, employees.name, employees.hire_date
FROM employees, jobs
WHERE jobs.empl_id = employees.empl_id AND employees.hire_date > 1999-12-31
ORDER BY jobs.name, employees.name

This query looks very different from the earlier one. First of all, the parts of the query have been provided on separate lines to make it easier to see what is going on. You can do this because an SQL processor knows how to process a query no matter what kind of spacing is between the different parts.

You will also notice that the column names look different now. When you are working with multiple tables, you can add the table name and a period to the beginning of the column name to avoid confusion about which column comes from which table.

Another difference is that we have more than one criterion specified in the WHERE portion of the statement. The two different criteria are joined by the word AND which tells SQL that both parts must be true for a record to be selected. If you separate criteria with the word OR it means that a record can be selected if either the part before or the part after the word OR is true.

The first part of this new WHERE portion states that the column empl_id must be the same in both the jobs table and the employees table. This is how we are able to link the two tables together and ensure that the data between the two is associated by employee ID number.

There is much more to SQL than the SELECT statement, and there are more options for the SELECT statement than were discussed here, but this should give you a good starting point to start using SQL to pull information from your database.