Chapter 8 Home | Prev: 8.2 Sub-Languages of SQL | Next: 8.4 Aliases

8.3 - Basics of the SELECT statement

The Basics of SELECT

The SELECT statement is used to retrieve data from a database and is most ofter referred to as a Query. In this lesson, we will concentrate on retrieving data from a single table only.

You can think of a table as an entity (example: movies), and each row as a specific instance of that entity type (example: Avatar, Star Wars, Toy Story). Therefore, the columns represent the various properties of those entities (i.e. Title, Director, Year, Length).

The basic form of the SELECT statement, at this point in the course is:

SELECT <field list comma separated>
FROM <source name>
WHERE <one or more comparison expressions>
ORDER BY <field list comma separated>;

The SELECT and FROM parts of the statement are required, the WHERE and ORDER BY parts are optional.

Order of Execution

When we look at the basic SELECT statement, we might assume that it is either executed all at once, due to its' semi-colon syntax, or it is executed from top to bottom, like most sequential applications run.  However, this is not the case in SQL.  There is an order to the execution of the statement that must be understood, as it has consequences to how the statement can be used. 

For the four basic parts of the statement in this lesson, the order of execution is:

1. FROM
2. WHERE
3. SELECT
4. ORDER BY

In English, we might say "FROM this source, WHERE something is true, SELECT these fields and calculations, and ORDER BY the output by these fields."

We will refer back to the Order of Execution many times through this course when the lesson is impacted by the order of execution.

SELECT

Movies

Id	Title	Director	Year	Length_minutes
1	Toy Story	John Lasseter	1995	81
2	A Bug's Life	John Lasseter	1998	95
3	Toy Story 2	John Lasseter	1999	93
4	Monsters, Inc.	Pete Docter	2001	92
5	Finding Nemo	Andrew Stanton	2003	107
6	The Incredibles	Brad Bird	2004	116
7	Cars	John Lasseter	2006	117
8	Ratatouille	Brad Bird	2007	115

The basic query has 2 mandatory parts, the SELECT part and the source, FROM, part and is written as follows:

SELECT <column1>, <column2>, <columnN>
FROM <tablename>;

For example using the above table:

SELECT id, title, director
FROM movies;

A few points to note:

• SQL code is not case sensitive, but we often use case as a style guide for code readability
  • Keywords are entered in upper case
  • User defined words are entered in lower case
  • Carriage returns are used before each of the main components of an SQL statement
• column and table names must be spelled exactly
• column and table names must be contained inside double quotes ( " ) if there is a space in the name.  (this is a bad design practice)
• the columns are output in the same order as listed in the SELECT portion of the statements
• although we almost always include a semi-colon ( ; ) at the end of each statement, it is only required if there is another statement in the same file afterwards.

The results of this query are a table of rows and columns including only those columns requested in the statement.

If we wanted to retrieve an exact copy of ALL the data stored in a table, we use the * shorthand.  This retrieves all columns from the data table and the columns are presented in the same order as the way the table was created in the first place.

SELECT * FROM movies;

practice exercises

Queries when No Table is Involved

There are many times when you need to output something that is not in a table.  MS SQL allows you to execute a SELECT statement without the FROM clause. The FROM part of the SELECT statement is typically required, but you do not want to choose an existing table as it would load it into memory unnecessarily.

SELECT getdate();

outputs a single row, single column table with the value 'X' in it.

SELECT 2 * 7 AS number

is an example where no table is needed, we just need to value.

The removal of the FROM clause is used most when dealing with dates, especially when dates are relative to the current date: today, yesterday, tomorrow, next Monday, next week, last week, this year, etc.