The following methods can be used to search for data in a table:

• Searching for data within a specific range
• Searching for a specific character string
• Searching for data that is not the null value
• Searching for data that satisfies multiple conditions
• Search using a subquery

(a) SQL specification example for searching for data within a specific range

The following three methods are available for searching for data within a specific range:

• Comparison predicate (used for equivalence and size comparison)
• BETWEEN predicate (used for extracting data within a specific range)
• IN predicate (used for extracting only the data that matches multiple values that are specified)

An example of using a comparison predicate for a data search is explained as follows.

Example

In this example, the SELECT statement searches the stock table (STOCK) for the product codes (PCODE) and product names (PNAME) of products whose stock quantity (SQUANTITY) is 50 or less:

 
SELECT PCODE,PNAME FROM STOCK
WHERE SQUANTITY<=50

(b) SQL specification example for searching for a specific character string

An example of conducting a search for rows in which there is a column that contains a specific character string is explained as follows.

Example

The LIKE predicate is used in this example. The SELECT statement searches the stock table (STOCK) for the product names (PNAME) and stock quantities (SQUANTITY) of products whose product code (PCODE) has L as its second character:

 
SELECT PNAME,SQUANTITY FROM STOCK
  WHERE PCODE LIKE '_L%'

(c) SQL specification example for searching for data that is not the null value

An example of conducting a search for rows in which a specified column does not contain the null value is explained as follows.

Example

NOT of the NULL predicate is used in combination in this example. The SELECT statement searches the stock table (STOCK) for the product codes (PCODE) of products whose product name (PNAME) is not empty (not the null value):

 
SELECT PCODE FROM STOCK
  WHERE PNAME IS NOT NULL

(d) SQL specification example for searching for data that satisfies multiple conditions

An example of conducting a search for rows that contain data that satisfies a combination of multiple conditions is explained as follows.

Example

Boolean operators (AND, OR, and NOT) are used in this example. The SELECT statement searches the stock table (STOCK) for the product codes (PCODE) of products whose product name (PNAME) is blouse or polo shirt and whose stock quantity (SQUANTITY) is 50 or greater:

 
SELECT PCODE, SQUANTITY FROM STOCK
  WHERE (PNAME='blouse'
        OR PNAME='polo shirt')
        AND SQUANTITY=>50

(e) SQL specification example for a search using a subquery

You can code a complex query by specifying a retrieval result of a search as a condition in the SELECT statement. This is called a subquery. The following two subquery methods are available:

• Quantified predicate
  A quantified predicate is used to narrow the results of a subquery by determining whether the results of the subquery satisfy a specified comparison condition.
  
• EXISTS predicate
  The EXISTS predicate is used to determine whether the results of a subquery constitute an empty set.
  

An example of conducting a search that uses a subquery that contains a quantified predicate is explained as follows:

Example

In this example that uses a quantified predicate, the SELECT statement searches the stock table (STOCK) for the product codes (PCODE) and product names (PNAME) of products that have a greater stock quantity value than the stock quantity (SQUANTITY) value for blouse:

 
SELECT PCODE,PNAME FROM STOCK
   WHERE SQUANTITY>ALL
          (SELECT SQUANTITY FROM STOCK
                WHERE PNAME='blouse')