Sign up for a free trial of our self-paced courses.

SQL Tutorial

Simple SELECTs

The SELECT statement is used to retrieve data from tables. SELECT statements can be used to perform simple tasks such as retrieving records from a single table or complicated tasks such as retrieving data from multiple tables with record grouping and sorting. In this lesson, we will look at several of the more basic ways to retrieve data from a single table.

Lesson Goals

  • Learn about the database we'll be using in class.
  • To comment your SQL code.
  • To understand SQL syntax.
  • To select all rows from a table.
  • To sort record sets.
  • To filter records.

Introduction to the Northwind Database

The Northwind database is a sample database used by Microsoft to demonstrate the features of some of its products, including SQL Server and Microsoft Access. The database contains the sales data for Northwind Traders, a fictitious specialty foods export-import company.

Although the code taught in this class is not specific to Microsoft products, we use the Northwind database for many of our examples because many people are already familiar with it and because there are many resources for related learning that make use of the same database.

The diagram below shows the table structure of the Northwind database.

The Northwind database has additional tables, but we will only be using the ones shown above. In this lesson, we will explore some of these tables.

Some Basics

Comments

The standard SQL comment is two hyphens (--). However, some databases use other forms of comments as shown in the table below.

SQL Comments
-- # /* */
Example -- Comment # Comment /* Comment */
ANSI YES NO NO
SQL Server YES NO YES
Oracle YES NO YES
MySQL YES YES YES

The code sample below shows some sample comments.

Code Sample:

SimpleSelects/Demos/Comments.sql
-- Single-line comment
/*
	Multi-line comment used in:
		 -SQL Server
		 -Oracle
		 -MySQL
*/

Whitespace and Semi-colons

Whitespace is ignored in SQL statements. Multiple statements are separated with semi-colons. The two statements in the sample below are equally valid.

Code Sample:

SimpleSelects/Demos/WhiteSpace.sql
SELECT * FROM Employees;

SELECT *
FROM Employees;

Case Sensitivity

SQL is not case sensitive. It is common practice to write reserved words in all capital letters. User-defined names, such as table names and column names may or may not be case sensitive depending on the operating system used.

SELECTing All Columns in All Rows

The following syntax is used to retrieve all columns in all rows of a table.

Syntax

SELECT table.*
FROM table;

	-- OR

SELECT *
FROM table;

Code Sample:

SimpleSelects/Demos/SelectAll.sql
--Retrieve all columns in the Region table
SELECT *
FROM Region;

The above SELECT statement will return the following results:

As you can see, the Region table has only two columns, RegionID and RegionDescription, and four rows.

SELECTing Specific Columns

The following syntax is used to retrieve specific columns in all rows of a table.

Syntax

SELECT table_name.column_name, table_name.column_name
FROM table;

	-- OR

SELECT column, column
FROM table;

Code Sample:

SimpleSelects/Demos/SelectCols.sql
/*
Select the FirstName and LastName columns from the Employees table.
*/
SELECT FirstName, LastName
FROM Employees;

The above SELECT statement will return the following results:

Sorting Records

The ORDER BY clause of the SELECT statement is used to sort records.

Sorting By a Single Column

To sort by a single column, simply name that column in the ORDER BY clause.

Syntax

SELECT column, column
FROM table
ORDER BY column;

Note that columns in the ORDER BY clause do not have to appear in the SELECT clause.

Code Sample:

SimpleSelects/Demos/OrderBy1.sql
/*
	Select the FirstName and LastName columns from the Employees table. 
	Sort by LastName.
*/

SELECT FirstName, LastName
FROM Employees
ORDER BY LastName;

The above SELECT statement will return the following results:

Sorting By Multiple Columns

To sort by multiple columns, comma-delimit the column names in the ORDER BY clause.

Syntax

SELECT column, column
FROM table
ORDER BY column, column;

Code Sample:

SimpleSelects/Demos/OrderBy2.sql
/*
Select the Title, FirstName and LastName columns from the Employees table.
Sort first by Title and then by LastName.
*/

SELECT Title, FirstName, LastName
FROM Employees
ORDER BY Title, LastName;

The above SELECT statement will return the following results:

Sorting By Column Position

It is also possible to sort tables by the position of a column in the SELECT list. To do so, specify the column numbers in the ORDER BY clause.

Syntax

SELECT column, column
FROM table
ORDER BY column_position, column_position;

Code Sample:

SimpleSelects/Demos/OrderBy3.sql
/*
Select the Title, FirstName and LastName columns from the Employees table.
Sort first by Title (position 1) and then by LastName (position 3).
*/

SELECT Title, FirstName, LastName
FROM Employees
ORDER BY 1,3;

The above SELECT statement will return the same results as the previous query:

Ascending and Descending Sorts

By default, when an ORDER BY clause is used, records are sorted in ascending order. This can be explicitly specified with the ASC keyword. To sort records in descending order, use the DESC keyword.

Syntax

SELECT column, column
FROM table
ORDER BY column_position DESC, column_position ASC;

Code Sample:

SimpleSelects/Demos/OrderBy4.sql
/*
	Select the Title, FirstName and LastName columns from the Employees table.
	Sort first by Title in ascending order and then by LastName 
	in descending order.
*/

SELECT Title, FirstName, LastName
FROM Employees
ORDER BY Title ASC, LastName DESC;

The above SELECT statement will return the following results:

The WHERE Clause and Operator Symbols

The WHERE clause is used to retrieve specific rows from tables. The WHERE clause can contain one or more conditions that specify which rows should be returned.

Syntax

SELECT column, column
FROM table
WHERE conditions;

The following table shows the symbolic operators used in WHERE conditions.

SQL Symbol Operators
Operator Description
= Equals
<> Not Equal
> Greater Than
< Less Than
>= Greater Than or Equal To
<= Less Than or Equal To

Note that non-numeric values (e.g, dates and strings) in the WHERE clause must be enclosed in single quotes. Examples are shown below.

Checking for Equality

Code Sample:

SimpleSelects/Demos/Where-Equal.sql
/*
Create a report showing the title and the first and last name
of all sales representatives.
*/

SELECT Title, FirstName, LastName
FROM Employees
WHERE Title = 'Sales Representative';

The above SELECT statement will return the following results:

Checking for Inequality

Code Sample:

SimpleSelects/Demos/Where-NotEqual.sql
/*
Create a report showing the first and last name of all employees
excluding sales representatives.
*/

SELECT FirstName, LastName
FROM Employees
WHERE Title <> 'Sales Representative';

The above SELECT statement will return the following results:

Checking for Greater or Less Than

The less than (<) and greater than (>) signs are used to compare numbers, dates, and strings.

Code Sample:

SimpleSelects/Demos/Where-GreaterThanOrEqual.sql
/*
Create a report showing the first and last name of all employees whose
last names start with a letter in the last half of the alphabet.
*/

SELECT FirstName, LastName
FROM Employees
WHERE LastName >= 'N';

The above SELECT statement will return the following results:

Checking for NULL

When a field in a row has no value, it is said to be NULL. This is not the same as having an empty string. Rather, it means that the field contains no value at all. When checking to see if a field is NULL, you cannot use the equals sign (=); rather, use the IS NULL expression.

Code Sample:

SimpleSelects/Demos/Where-Null.sql
/*
Create a report showing the first and last names of
all employees whose region is unspecified.
*/

SELECT FirstName, LastName
FROM Employees
WHERE Region IS NULL;

The above SELECT statement will return the following results:

Code Sample:

SimpleSelects/Demos/Where-NotNull.sql
/*
Create a report showing the first and last names of all
employees who have a region specified.
*/

SELECT FirstName, LastName
FROM Employees
WHERE Region IS NOT NULL;

The above SELECT statement will return the following results:

WHERE and ORDER BY

When using WHERE and ORDER BY together, the WHERE clause must come before the ORDER BY clause.

Code Sample:

SimpleSelects/Demos/Where-OrderBy.sql
/*
Create a report showing the first and last name of all employees whose 
last names start with a letter in the last half of the alphabet.
Sort by LastName in descending order.
*/

SELECT FirstName, LastName
FROM Employees
WHERE LastName >= 'N'
ORDER BY LastName DESC;

The above SELECT statement will return the following results:

The WHERE Clause and Operator Words

The following table shows the word operators used in WHERE conditions.

SQL Word Operators
Operator Description
BETWEEN Returns values in an inclusive range
IN Returns values in a specified subset
LIKE Returns values that match a simple pattern
NOT Negates an operation

The BETWEEN Operator

The BETWEEN operator is used to check if field values are within a specified inclusive range.

Code Sample:

SimpleSelects/Demos/Where-Between.sql
/*
Create a report showing the first and last name of all employees
whose last names start with a letter between "J" and "M". 
*/

SELECT FirstName, LastName
FROM Employees
WHERE LastName BETWEEN 'J' AND 'M';

-- The above SELECT statement is the same as the one below.

SELECT FirstName, LastName
FROM Employees
WHERE LastName >= 'J' AND LastName <= 'M';

The above SELECT statements will both return the following results:

Note that a person with the last name "M" would be included in this report.

The IN Operator

The IN operator is used to check if field values are included in a specified comma-delimited list.

Code Sample:

SimpleSelects/Demos/Where-In.sql
/*
Create a report showing the title of courtesy and the first and
last name of all employees whose title of courtesy is "Mrs." or "Ms.". 
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy IN ('Ms.','Mrs.');

-- The above SELECT statement is the same as the one below

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy = 'Ms.' OR TitleOfCourtesy = 'Mrs.';

The above SELECT statements will both return the following results:

The LIKE Operator

The LIKE operator is used to check if field values match a specified pattern.

The Percent Sign (%)

The percent sign (%) is used to match any zero or more characters.

Code Sample:

SimpleSelects/Demos/Where-Like1.sql
/*
Create a report showing the title of courtesy and the first
and last name of all employees whose title of courtesy begins with "M". 
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy LIKE 'M%';

The above SELECT statement will return the following results:

The Underscore (_)

The underscore (_) is used to match any single character.

Code Sample:

SimpleSelects/Demos/Where-Like2.sql
/*
Create a report showing the title of courtesy and the first and
last name of all employees whose title of courtesy begins with "M" and
is followed by any character and a period (.).
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE TitleOfCourtesy LIKE 'M_.';

The above SELECT statement will return the following results:

Wildcards and Performance

Using wildcards can slow down performance, especially if they are used at the beginning of a pattern. You should use them sparingly.

The NOT Operator

The NOT operator is used to negate an operation.

Code Sample:

SimpleSelects/Demos/Where-Not.sql
/*
Create a report showing the title of courtesy and the first and last name
of all employees whose title of courtesy is not "Ms." or "Mrs.".
*/

SELECT TitleOfCourtesy, FirstName, LastName
FROM Employees
WHERE NOT TitleOfCourtesy IN ('Ms.','Mrs.');

The above SELECT statement will return the following results:

Checking Multiple Conditions

AND

AND can be used in a WHERE clause to find records that match more than one condition.

Code Sample:

SimpleSelects/Demos/Where-And.sql
/*
Create a report showing the first and last name of all
sales representatives whose title of courtesy is "Mr.".
*/

SELECT FirstName, LastName
FROM Employees
WHERE Title = 'Sales Representative'
	AND TitleOfCourtesy = 'Mr.';

The above SELECT statement will return the following results:

OR

OR can be used in a WHERE clause to find records that match at least one of several conditions.

Code Sample:

SimpleSelects/Demos/Where-Or.sql
/*
	Create a report showing the first and last name and the city of all 
	employees who are from Seattle or Redmond.
*/

SELECT FirstName, LastName, City
FROM Employees
WHERE City = 'Seattle' OR City = 'Redmond';

The above SELECT statement will return the following results:

Order of Evaluation

By default, SQL processes AND operators before it processes OR operators. To illustrate how this works, take a look at the following example.

Code Sample:

SimpleSelects/Demos/Where-AndOrPrecedence.sql
/*
	Create a report showing the first and last name of all sales 
	representatives who are from Seattle or Redmond.
*/

SELECT FirstName, LastName, City, Title
FROM Employees
WHERE City = 'Seattle' OR City = 'Redmond'
	AND Title = 'Sales Representative';

The above SELECT statement will return the following results:

Notice that Laura Callahan is returned by the query even though she is not a sales representative. This is because this query is looking for employees from Seattle OR sales representatives from Redmond.

This can be fixed by putting the OR portion of the clause in parentheses.

Code Sample:

SimpleSelects/Demos/Where-AndOrPrecedence2.sql
/*
	Create a report showing the first and last name of all sales 
	representatives who are from Seattle or Redmond.
*/

SELECT FirstName, LastName, City, Title
FROM Employees
WHERE (City = 'Seattle' OR City = 'Redmond')
	AND Title = 'Sales Representative';

The parentheses specify that the OR portion of the clause should be evaluated first, so the above SELECT statement will return the same results minus Laura Callahan.

If only to make the code more readable, it's a good idea to use parentheses whenever the order of precedence might appear ambiguous.