1.16 List of references by purpose
The table below lists the clauses, predicates, and functions that can be used in SELECT statements, as well as references to examples, organized according to the purpose of the retrieval.
Note that the descriptions given as examples assume that you are using the example tables used above, the sales history table (SALESLIST) and the customer table (USERSLIST).
No. |
Purpose of retrieval |
Clause, predicate, or function to use |
Reference to example |
---|---|---|---|
1 |
You want to see all the data in a table. Examples:
|
-- |
|
2 |
You want to sort retrieved results in ascending or descending order. Examples:
|
ORDER BY clause |
|
3 |
You want to specify a maximum number of rows for the retrieval results. Examples:
|
LIMIT clause |
1.4 Specifying the maximum number of rows of retrieval results (LIMIT clause) |
4 |
You want to limit the search by specifying conditions. Examples:
|
WHERE clause |
|
5 |
You want to search within a specified range. Example:
|
BETWEEN predicate |
1.6 Retrieving data with a search range specified (BETWEEN predicate) |
6 |
You want to search for data that matches any of multiple conditions. Example:
|
IN predicate |
1.7 Retrieving data that meets one of multiple conditions (IN predicate) |
7 |
You want to search for data that contains a specific character string. Examples:
|
LIKE predicate |
1.8 Retrieving data that contains a specific character string (LIKE predicate) |
8 |
You want to retrieve data from multiple tables (table join). Example:
|
WHERE clause |
1.9 Retrieving data with multiple tables specified (table join), 1.10.1 Example: Retrieve customers who purchased products, and 1.13.5 Example 5: Aggregate data from the sales history table and customer table |
9 |
You want to eliminate duplication in the retrieval results. Examples:
|
SELECT DISTINCT |
1.10 Eliminating duplication in retrieval results (SELECT DISTINCT) |
10 |
You want to determine the total number of data items in a table. Example:
|
Set function COUNT(*) |
|
11 |
You want to determine the number of retrieved rows. Examples:
|
Set function COUNT(*) |
1.11.2 Example 2: Determine the number of people who purchased a product |
12 |
You want to determine a maximum value from the retrieved data Example:
|
Set function MAX |
1.12.1 Example 1: Determine the maximum, minimum, and average quantities purchased |
13 |
You want to determine a minimum value from the retrieved data. Example:
|
Set function MIN |
|
14 |
You want to determine an average value from the retrieved data. Example:
|
Set function AVG |
|
15 |
You want to determine the sum of the retrieved data. Examples:
|
Set function SUM |
1.12.2 Example 2: Determine the sum of quantities purchased) |
16 |
You want to aggregate data into groups. Examples:
|
GROUP BY clause HAVING clause |
1.13 Aggregating retrieved data by group (GROUP BY clause, HAVING clause) |
17 |
You want to retrieve data based on the results of another SELECT statement specified in a search condition. Example:
|
Subquery |
1.14 Retrieving by specifying a SELECT statement in the search condition (subquery) |
Legend: --: Not applicable.