s02 过滤和排序数据.ppt

Using the WHERE Clause In the example, the SELECT statement retrieves the name, job ID, and department number of all employees whose job ID is SA_REP. Note that the job title SA_REP has been specified in uppercase to ensure that it matches the job ID column in the EMPLOYEES table. Character strings are case sensitive. Character Strings and Dates Character strings and dates in the WHERE clause must be enclosed in single quotation marks (). Number constants, however, should not be enclosed in single quotation marks. All character searches are case sensitive. In the following example, no rows are returned because the EMPLOYEES table stores all the last names in mixed case: SELECT last_name, job_id, department_id FROM employees WHERE last_name = WHALEN; Oracle databases store dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default date display is DD-MON-RR. Note: Changing the default date format is covered in a subsequent lesson. Instructor Note Some students may ask how to override the case sensitivity. Later in the course, we cover the use of single-row functions such as UPPER and LOWER to override the case sensitivity. Comparison Conditions Comparison conditions are used in conditions that compare one expression to another value or expression. They are used in the WHERE clause in the following format: Syntax ... WHERE expr operator value For Example ... WHERE hire_date=01-JAN-95 ... WHERE salary=6000 ... WHERE last_name=Smith An alias cannot be used in the WHERE clause. Note: The symbol != and ^= can also represent the not equal to condition. Using the Comparison Conditions In the example, the SELECT statement retrieves the last name and salary from the EMPLOYEES table, where the employee salary is less than or equal to 3000. Note that there is an explicit value supplied to the WHERE clause. The explicit value of 3000 is compared to the salary value in the SALARY column