SELECT examples

The following is a basic query that selects an entire table:

SELECT * FROM BIOLIFE

The following examples show simple SELECTs:

SELECT NAME, PHONE FROM CUSTOMER WHERE STATE_PROV = "CA"

SELECT CUSTOMER_NO FROM CUSTOMER WHERE LAST_NAME = "Johnson"

SELECT PART_NO, SUM(QUANTITY) AS PQTY FROM PARTS GROUP BY PART_NO

The following example illustrates the ORDER BY with a DESCENDING clause:

SELECT DISTINCT CUSTOMER_NO ;

FROM "C:/DATA/CUSTOMER" ; 

ORDER BY CUSTOMER_NO DESCENDING 

The following example illustrates how the SELECT statement is supported as an equivalent to a JOIN:

SELECT DISTINCT P.PART_NO, P.QUANTITY, G.CITY ;

FROM PARTS P, GOODS G ; 

WHERE P.PART_NO = G.PART_NO ; 

AND P.QUANTITY > 20 ; 

ORDER BY P.QUANTITY, G.CITY, P.PART_NO 

Sub-select queries are supported. The following example illustrates this syntax:

SELECT P.PART_NO ;

FROM PARTS P ; 

WHERE P.QUANTITY IN ; 

(SELECT I.QUANTITY ; 

FROM INVENTORY I ; 

WHERE I.PART_NO = 'AA9393') 

The following example shows a join in which fields from each table are involved in some type of equality check and require a WHERE clause:

SELECT DISTINCT PARTS.PART_NO, PARTS.QUANTITY, GOODS.CITY ;

FROM PARTS, GOODS ; 

WHERE PARTS.PART_NO = GOODS.PART_NO AND PARTS.QUANTITY > 20 ; 

ORDER BY PARTS.QUANTITY, GOODS.CITY, PARTS.PART_NO 

The following example shows the use of the DESCENDING keyword in the ORDER BY clause. Note that in this case you must also specify DISTINCT.

SELECT DISTINCT CUSTOMER_NO ;

FROM CUSTOMER ; 

ORDER BY CUSTOMER_NO DESCENDING