Just SQL Part II – The Simple SELECT

Author : James F. Koopmann

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.

James F. Koopmann

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 down the page) in the spreadsheet is considered a ROW, and then sets of columns (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, take a look at Table 1. The table is called DOG_OFIGIN 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. But for now you can see there is just TEXT data. So the COLUMNS are COUNTRY, BREED, and SIZE. A ROW is composed of an occurrence of COUNTRY, BREED, and SIZE.

We are not going into a table design concepts or modeling here. But 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. There is nothing you can do with this notation but makes it easy to read and communicate the columns of a table.

DOG_ORIGIN(COUNTRY, BREED, SIZE)

Often times the individual COLUMNs of the TABLE are denoted by following: 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 and I will expand on them latter 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 from where the COLUMNS reside in.

So 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 the following.

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 doesn’t matter what you use. Just try and make it consistent throughout your code so as to 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;

Also, 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 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 won’t 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 out 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 lets become the next SQL experts.