How Do I...

Example: SELECT statements

Use the SELECT Details window No tip for this topic No example for this topic Use the UPDATE Details window

SELECT statement 1

Suppose you have the following SQL SELECT statement:

SELECT EMPLOYEE.JOBCODE, WAGES.GROSSPAY, EMPLOYEE.NAME
   FROM EMPLOYEE, WAGES
   WHERE (EMPLOYEE.NUMBER = WAGES.EMPNUMBER) AND (EMPLOYEE.STATE = 'NC')
   ORDER BY EMPLOYEE.JOBCODE ASC, WAGES.GROSSPAY DESC
 

To specify the statement:

  1. In the Query name field, type a name for the SELECT statement.
  2. In the Description field, type a description for the SELECT statement.
  3. In the Tables/views list, select EMPLOYEE and WAGES.
  4. In the Columns list, select EMPLOYEE.JOBCODE, EMPLOYEE.NAME, and WAGES.GROSSPAY.
  5. In the Column sequence list, select EMPLOYEE.NAME.
  6. From the Column sequence pop-up menu, select Move after > WAGES.GROSSPAY.
  7. From the Clause menu, select WHERE.

Next, in the displayed WHERE Details window:

  1. In the Left operand field, select EMPLOYEE.NUMBER.
  2. In the Operator field, select =.
  3. In the Right operand field, select WAGES.EMPNUMBER.
  4. Select Add to operand lists.
  5. In the Left operand field, select EMPLOYEE.STATE.
  6. In the Operator field, select =.
  7. In the Right operand field, type
    NC
    
  8. From the Unary operator menu, select Right operand Submenu indicator String constant 'x'.
  9. Select Add to operand lists.
  10. In the Left operand field, select (EMPLOYEE.NUMBER = WAGES.EMPNUMBER).
  11. In the Operator field, select AND.
  12. In the Right operand field, select (EMPLOYEE.STATE = 'NC').
  13. Select Apply.

Finally, complete the statement:

  1. From the Clause menu, select ORDER BY.
  2. In the ORDER BY Details window, do the following:
    1. In the Columns list, select EMPLOYEE.JOBCODE and WAGES.GROSSPAY.
    2. In the ORDER BY sequence list, select WAGES.GROSSPAY.
    3. From the ORDER BY sequence pop-up menu, select Descending (DESC).
    4. Select Apply.

SELECT statement 2

Suppose you have the following SQL SELECT statement:

SELECT EMPLOYEE.JOBCODE, COUNT(*)
   FROM EMPLOYEE
   GROUP BY EMPLOYEE.JOBCODE
   HAVING MAX(EMPLOYEE.AGE) > 40
 

To specify the statement:

  1. In the Query name field, type a name for the SELECT statement.
  2. In the Description field, type a description for the SELECT statement.
  3. In the Tables/views list, select EMPLOYEE.
  4. In the Columns list, select EMPLOYEE.JOBCODE.
  5. From the Computed columns pop-up menu, select Create.
  6. In the Computed Column Details window, select COUNT(*) from the Unary operator menu; then select Apply.
  7. In the Computed columns list, select COUNT(*).
  8. From the Clause menu, select GROUP BY.
  9. In the GROUP BY Details window, select EMPLOYEE.JOBCODE in the Columns list, and select Apply.
  10. From the Clause menu, select HAVING.

Finally, in the HAVING Details window, do the following:

  1. In the Left operand field, select EMPLOYEE.AGE.
  2. From the Unary operator menu, select Left operand > MAX(x).
  3. In the Operator field, select >.
  4. In the Right operand field, type
    40
    
  5. Select Apply.


[ Top of Page | Previous Page | Next Page | Table of Contents ]