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).

Table 1‒2: List of clauses, predicates, and functions usable in SELECT statements, with references to examples
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: See customer information for all customers. See all sales history information for a product.	--	1.2 Retrieving all the rows from a table
2	You want to sort retrieved results in ascending or descending order. Examples: Sort customer information by customer ID. Sort a product's sales history information by date.	`ORDER` `BY` clause	1.3 Sorting retrieval results (ORDER BY clause)
3	You want to specify a maximum number of rows for the retrieval results. Examples: See some but not all customer data. See some but not all sales history information for a product.	`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: Obtain yesterday's product sales history information. Obtain the product purchase history for a specific customer.	`WHERE` clause	1.5 Retrieving data with search conditions specified
5	You want to search within a specified range. Example: Obtain this week's product sales history information.	`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: Find customers who purchased a product of product code P001 or P003.	`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: Obtain customer information for customers with the last name Johnson. Obtain customer information for customers with a name whose initial letter is A.	`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: Obtain customer information for customers who purchased a product yesterday.	`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: Find the names of customers who purchased products. Obtain the product codes of items sold.	`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: Determine the total number of customers.	Set function `COUNT(*)`	1.11.1 Example 1: Determine the total number of customers
11	You want to determine the number of retrieved rows. Examples: Determine the number of people who purchased a product. Determine the number of product sales yesterday. Determine the number of times a product was purchased by a specific customer.	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: Determine the maximum quantity purchased of a product.	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: Determine the minimum quantity purchased of a product.	Set function `MIN`
14	You want to determine an average value from the retrieved data. Example: Determine the average quantity purchased of a product	Set function `AVG`
15	You want to determine the sum of the retrieved data. Examples: Determine the quantity purchased yesterday of a product. Determine the number of products purchased for a particular customer.	Set function `SUM`	1.12.2 Example 2: Determine the sum of quantities purchased)
16	You want to aggregate data into groups. Examples: For each customer, determine the number of times a product was purchased or the quantities purchased. For each product code, determine the number of sales or the quantities sold.	`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: Find information on the customer who purchased the greatest quantity of a product.	Subquery	1.14 Retrieving by specifying a SELECT statement in the search condition (subquery)

Legend: --: Not applicable.

To Page Top