Informix Guide to SQL: Tutorial
Chapter 2: Composing Simple SELECT Statements
Home
Contents
Index
Master Index
New Book
Summary
This chapter introduced sample syntax and results for basic kinds of
SELECT
statements that are used to query on a relational database. Earlier sections of the chapter showed how to perform the following actions:
Select all columns and rows from a table with the
SELECT
and
FROM
clauses
Select specific columns from a table with the
SELECT
and
FROM
clauses
Select specific rows from a table with the
SELECT
,
FROM
, and
WHERE
clauses
Use the
DISTINCT
or
UNIQUE
keyword in the
SELECT
clause to eliminate duplicate rows from query results
Sort retrieved data with the
ORDER
BY
clause and the
DESC
keyword
Select and order data that contains non-English characters
Use the
BETWEEN
,
IN
,
MATCHES
, and
LIKE
keywords and various relational operators in the
WHERE
clause to create a comparison condition
Create comparison conditions that include values, exclude values, find a range of values (with keywords, relational operators, and subscripting), and find a subset of values
Perform variable text searches by using exact-text comparisons, variable-length wildcards, and restricted and unrestricted wildcards
Use the logical operators
AND
,
OR
, and
NOT
to connect search conditions or Boolean expressions in a
WHERE
clause
Use the
ESCAPE
keyword to protect special characters in a query
Search for null values with the
IS
NULL
and
IS NOT NULL
keywords in the
WHERE
clause
Use arithmetic operators in the
SELECT
clause to perform computations on number fields and display derived data
Use substrings and subscripting to tailor your queries
Assign display labels to computed columns as a formatting tool for reports
Use the aggregate functions
COUNT
,
AVG
,
MAX
,
MIN
, and
SUM
in the
SELECT
clause to calculate and retrieve specific data
Include the time functions
DATE
,
DAY
,
MDY
,
MONTH
,
WEEKDAY
,
YEAR
,
CURRENT
, and
EXTEND
plus the
TODAY
,
LENGTH
, and
USER
functions in your
SELECT
statements
Include SPL routines in your
SELECT
statements
This chapter also introduced simple join conditions that enable you to select and display data from two or more tables. The section
"Multiple-Table SELECT Statements"
described how to perform the following actions:
Create a Cartesian product
Constrain a Cartesian product by including a
WHERE
clause with a valid join condition in your query
Define and create a natural join and an equi-join
Join two or more tables on one or more columns
Use aliases as a shortcut in multiple-table queries
Retrieve selected data into a separate, temporary table with the
INTO
TEMP
clause to perform computations outside the database
The next chapter explains more complex queries and subqueries; self-joins and outer joins; the
GROUP BY
and
HAVING
clauses; and the
UNION
,
INTERSECTION
, and
DIFFERENCE
set operations.
Informix Guide to SQL: Tutorial
, version 9.1
Copyright © 1998, Informix Software, Inc. All rights reserved.
