文本教程分析les.pptxVIP

Restricting and Sorting Data ObjectivesAfter completing this lesson, you should be able to do the following:Limit the rows that are retrieved by a querySort the rows that are retrieved by a queryUse ampersand substitution to restrict and sort output at run timeLesson AgendaLimiting rows with:The WHERE clauseThe comparison conditions using =, =, BETWEEN, IN, LIKE, and NULL conditionsLogical conditions using AND, OR, and NOT operatorsRules of precedence for operators in an expressionSorting rows using the ORDER BY clauseSubstitution variablesDEFINE and VERIFY commandsLimiting Rows Using a SelectionEMPLOYEES…“retrieve allemployees in department 90”Limiting the Rows That Are SelectedRestrict the rows that are returned by using the WHERE clause:The WHERE clause follows the FROM clause.SELECT *|{[DISTINCT] column|expression [alias],...}FROMtable[WHERE condition(s)];Using the WHERE ClauseSELECT employee_id, last_name, job_id, department_idFROM employeesWHERE department_id = 90 ;注：题53Character Strings and DatesCharacter strings and date values are enclosed with single quotation marks.Character values are case-sensitive and date values are format-sensitive.The default date display format is DD-MON-RR.SELECT last_name, job_id, department_idFROM employeesWHERE last_name = Whalen ;SELECT last_name FROM employeesWHERE hire_date = 17-FEB-96 ;Comparison OperatorsOperatorMeaning=Equal toGreater than=Greater than or equal toLess than=Less than or equal toNot equal toBETWEEN...AND...Between two values (inclusive)IN(set)Match any of a list of values LIKEMatch a character pattern IS NULLIs a null value Using Comparison OperatorsSELECT last_name, salaryFROM employeesWHERE salary = 3000 ;注：题54Range Conditions Using the BETWEEN OperatorUse the BETWEEN operator to display rows based on a range of values:SELECT last_name, salaryFROM employeesWHERE salary BETWEEN 2500 AND 3500 ;Lower limitUpper limitMembership Condition Using the IN OperatorUse the IN operator to test for valu