Just SQL Part II – The Simple SELECT

At the core of most queries is an underlying table
structure. Part II discusses how we can issue the most basic of SELECT
statements to extract information from a database table.

A Database Table

Most often, we query information from a database where that
information is stored inside a table structure. Most explain a table as a
series of rows and columns. Much like a spreadsheet where the spreadsheet is
the TABLE, each line (horizontally across the page) in the spreadsheet is
considered a ROW, and then sets of lines (vertically down the page) are denoted
as a COLUMN. Each cell (the intersection of a ROW and COLUMN) is then
considered a VALUE of a COLUMN for a specific ROW. For example, look at Table
1
. The table is called DOG_ORIGIN, and represents the country a particular
bread of dog originates. There is also a designator for the size of the bread.
We will, at a latter date, discuss the types of VALUEs (or data) that can be
stored in each column. For now you can see there is just TEXT data; the COLUMNS
are COUNTRY, BREED, and SIZE. A ROW is composed of an occurrence of COUNTRY,
BREED, and SIZE.

We are not going into table design concepts or modeling
here. However, please note that there are repeated VALUES in the COUNTRY and
SIZE COLUMNS. Also, note that there is no pre-defined order of the rows in the
table. This is a key concept when writing SQL and is often times misunderstood.
Just because rows of information were created in a table, in a particular order,
does not mean they will be returned in that order when extracted with a SQL
statement.

Table 1.

DOG_ORIGIN table

Country

Breed

Size

Germany

German Shepherd Dog

Big

Germany

Dobermann

Big

Germany

Rottweiler

Big

USA

Siberian Husky

Medium

USA

Alaskan Malamute

Medium

USA

American Bulldog

Big

Switzerland

Bernese Mountain Dog

Big

Switzerland

Saint Bernard Dog

Big

Switzerland

Entlebuch Cattle Dog

Medium

Australia

Australian Cattle Dog

Medium

Australia

Jack Russell Terrier

Small

Often times a TABLE is defined textually by the following
notation.

DOG_ORIGIN(COUNTRY, BREED, SIZE)

There is nothing you can do with this notation but makes it
easy to read and communicate the columns of a table.

Often times the individual COLUMNs of the TABLE are denoted
by a dot. This ‘dot’ notation containing table_name.column_name is used quite
often in SQL and you should get familiar to this representation. There is an
example later in this article.

DOG_ORIGIN.COUNTRY

DOG_ORIGIN.BREED

DOG_ORIGIN.SIZE

Writing a simple SQL Query

The SELECT statement is the basic unit for extracting information from a
database table. While there are many options within the SELECT syntax, there
are only four required parts to form a valid SQL SELECT statement. Table 2
contains the required parts of a SELECT statement. These definitions are in
their simplest form; I will expand on them later in this article.

Table 2.

Required parts for a SELECT
statement

SQL required part

Description

SELECT

Key word that signifies you want to query a table.

<select_list>

The list of
COLUMNS, separated by commas if more than one COLUMN, from a table you would
like to have the VALUES selected from.

FROM

Key word that
denotes where the COLUMNS in the <select_list> will come from.

<table_reference>

The TABLE that the COLUMNS reside in.

Therefore, if we wanted to list all
the COUNTRY of origin and the BREED of the dog from the DOG_ORIGIN table we
would construct a SELECT statement as follows.

SELECT country, breed FROM dog_origin;

The query could also be written in
two different forms. The first uses the TABLE name as a prefix on the COLUMNs
that are being selected. The second uses an alias for the same purpose as the
first option. Alias can be any character string. Some people use letters of the
alphabet (a,b,c,…) while others use some abbreviation of the table name. It does
not matter what you use. Just try to make it consistent throughout your code so
that you do not confuse someone. The reason for using the table name or alias
is for more advanced queries where a query might be selecting from two
different tables that have a column name that is the same. The alias specifies
to the database engine which table you would like the column to be queried
from. This will become clearer in Part III of this series.

1. 
SELECT dog_origin.country, dog_origin.breed FROM dog_origin;

2. 
SELECT alias.country, alias.breed FROM dog_origin alias;

In addition, this is a good place
to introduce the concept of the wildcard ‘*’ (an asterisk). The asterisk can be
used instead of listing the individual COLUMNs of the table you are selecting
from. For instance, consider the following.

SELECT * FROM dog_origin;

The database engine at this point
will expand the asterisk to mean all of the columns in the dog_origin table.
While this might be a very handy and quick way to get everything, it is a very
discouraged practice. For many reasons I will not go into, the asterisk
basically is inflexible when trying to use it within a true database
application and typically causes applications to break when the table structure
changes by adding or removing columns. So it is always a good practice to list each
and every column you really want to be used in the query.

If this all is second nature to you, GREAT! Please be patient as there are
many without this basic understanding of SQL queries and we all need to start
somewhere. If this is still confusing to you then please send me an email. SQL
is one of those lost arts. We often just throw something together without
considering the ramifications to our applications and the database engine that
must crunch through the tables to get at the information we require. Bear with
me. This series will introduce more difficult SQL as time goes on. Just read
each and every part, try the SQL and let’s become the next SQL experts.

»


See All Articles by Columnist
James Koopmann

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles