by Joseph Love

Created on: May 23, 2008   Last Updated: July 14, 2011

Throughout 20+ years in the Information field, I've taught several developers how to interact with a database. Although I could teach a 3 week course on the subtleties of SQL, I believe that it is best to introduce a developer to SQL and let them try it out for a while before moving on to the more advanced concepts.

In this article, I will discuss the basics of retrieving data from a database using an SQL query. SQL is a "Structured Query Language" used for retrieving and manipulating data within a database a database. For simplicity's sake, I will focus only on retrieving data from a single table. The command used to retrieve data is the "select" command.

SQL commands are designed to be read in a manner similar to standard English. A sample Select statement might read as follows:

Select first_name, last_name
from human_resource
where last_name='SMITH';

This statement will return the first and last names for all records in the human_resource table that have the last name of SMITH.

Let's examine that statement in more detail. First realize that the statement can be written on one line or many lines, it is simply put on multiple lines for ease of reading. The statement starts with the Select command and ends with a semi-colon.

The basic SELECT statement will always have a SELECT and FROM keyword. Most statements will also have WHERE and ORDER BY clauses.

The SELECT part (clause) of the statement in this example is: Select first_name, last_name

The select part of the SQL statement contains a list of the things to be selected from the database. I say things because the items selected from the table can be a column in the database, or the results of a math problem. The items selected are often renamed or "aliased" within the SELECT statement using the AS keyword. For example:

select first_name as fn, last_name ln
from human_resource where last_name='SMITH';

The uses for the AS keyword become more apparent when selecting items that are not directly columns, for example:

select sum(price) total_price
from shopping_cart_items

In this statement, we are using something called a "group function" that will sum up all PRICE fields within the shopping_cart_items table and return it in a "pseudo column" (alias) called total_price.

In a single table query, the FROM clause simply states the name of the table being queried. for example: from human_resource means that the query will be looking at the human_resource table only.

Although the from clause can get incredibly complex, defining complex relationships between multiple tables, in queries using one table, this is as complex as it gets.

The last clause we cover is the WHERE clause. The where clause is an optional clause that is criteria of what records are going to be returned. Examples could include: where last_name='SMITH' where last_name like 'S%' where last_name='SMITH' or last_name='WILKINSON'

This statement can contain a complex set of criteria of what records are going to be returned. In learning SQL, much time can be spent on learning the various intricacies of the Where statement and it is recommended you study the more advanced uses of the where clause.

Additional clauses include ORDER BY, which orders results in a certain order, GROUP BY, which will group results together such as summing results or averaging.

Additionally, SELECT statements have modifiers that can be used to eliminate duplicate results, or add results of one query to the results of another, and an almost limitless set of operators that can be used to manipulate the sets of data returned.

For more information, I highly recommend the book "SAMS Teach Yourself SQL in 24 Hours"

Learn more about this author, Joseph Love.
Click here to send this author comments or questions.