ASA Getting Started
  Selecting Data from Database Tables

Selecting rows from a table

You can limit the rows that a SELECT statement retrieves by adding a WHERE clause to your query. This is sometimes called applying a restriction to the result set. The WHERE clause includes a search condition that specifies the rows to be returned. This SELECT statement has the following syntax:

SELECT column-name-1, column-name-2,...
FROM table-name
WHERE search-condition

where, as before, column-name-1, column-name-2, and table-name should be replaced with the names of the desired columns and table you are querying. The search-condition is described more below. If you use an ORDER BY clause, it must come after the WHERE clause.

List all products colored black

• In Interactive SQL, type the following in the SQL Statements pane:

  SELECT *
  FROM product
  WHERE color = 'black'

  id	name	description	size	color	quantity	unit_price
  302	Tee Shirt	Crew Neck	One size fits all	Black	75	14
  400	Baseball Cap	Cotton Cap	One size fits all	Black	112	9
  501	Visor	Plastic Visor	One size fits all	Black	28	7
  ...	...	...	...	...	...	...

Notes 

• The WHERE clause includes a search condition to select rows. In this case the search condition is color = 'black'. Other search conditions are described in the following sections.

  For information on search conditions, see Search conditions.

• The single quotes around black are required. They indicate that black is a character string. Double quotes have a different meaning. Double quotes can be used to make otherwise invalid strings valid for column names and other identifiers.

  For information about strings, see Strings.

• The sample database is not case sensitive, so you would get the same results whether you searched for BLACK, black, or Black.

• How you order clauses is important. The SELECT list is followed by the FROM clause, followed by the WHERE clause, and then the ORDER BY clause. Typing the clauses in a different order gives a syntax error.

Exercise 

Try some queries that combine what you have learned in this chapter. Here is one query that lists the names and birth dates of all employees named John.

SELECT ( emp_fname || ' ' || emp_lname) AS Name,
        birth_date
FROM employee
WHERE emp_fname = 'John'
ORDER BY birth_date

Comparing dates in search conditions
Pattern matching in search conditions
Matching rows by sound
Using compound search conditions
Shortcuts for compound search conditions