Compound Predicate

Compound predicates use the operators OR and AND. For example, to display the SNO column values for all suppliers in "PARIS" with a STATUS column value greater than 20, use the following query:

SELECT sno
  FROM supplier
  WHERE city = "PARIS"
    AND status > 20

Shown next is the same query written with the OR operator rather than the AND operator. This SELECT command statement retrieves PNO column values for those parts that are either "RED" or weigh more than 15 pounds.

SELECT pno
  FROM part
  WHERE color = "RED"
    OR weight > 15

In complex queries, parentheses can be used to indicate the order of evaluation. The condition(s) surrounded by the innermost pair of parentheses are applied first.

SELECT pno, pname
  FROM part
  WHERE color = "green"
    OR (city = 'LONDON'
    AND weight < 15)

This last query retrieves PNO and PNAME column values of all parts that are either green or are both made in London, and have a weight less than 15.

The query result is displayed below:

pno pname
P1 NUT
P2 BOLT
P4 SCREW