26.oracleocp題庫dba經(jīng)典輔助學(xué)習(xí)les

1、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 in iSQL*Plus to restrict and sort output at run time Limiting Rows Using a Sel

2、ection“retrieve allemployees in department 90”EMPLOYEESLimiting 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,.FROM tableWHERE condition(s);SELECT employee_id, last_name, job_

3、id, department_idFROM employeesWHERE department_id = 90 ;Using the WHERE ClauseSELECT last_name, job_id, department_idFROM employeesWHERE last_name = Whalen ;Character Strings and DatesCharacter strings and date values are enclosed by single quotation marks.Character values are case-sensitive, and d

4、ate values are format-sensitive.The default date format is DD-MON-RR.Comparison ConditionsOperatorMeaning=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 patte

5、rn IS NULLIs a null value SELECT last_name, salaryFROM employeesWHERE salary =10000AND job_id LIKE %MAN% ;Using the AND OperatorAND requires both conditions to be true:SELECT employee_id, last_name, job_id, salaryFROM employeesWHERE salary = 10000OR job_id LIKE %MAN% ;Using the OR OperatorOR require

6、s either condition to be true:SELECT last_name, job_idFROM employeesWHERE job_id NOT IN (IT_PROG, ST_CLERK, SA_REP) ;Using the NOT OperatorRules of PrecedenceYou can use parentheses to override rules of precedence.OperatorMeaning1Arithmetic operators2Concatenation operator3Comparison conditions4IS N

7、OT NULL, LIKE, NOT IN5NOT BETWEEN6Not equal to7NOT logical condition8AND logical condition9OR logical conditionSELECT last_name, job_id, salaryFROM employeesWHERE job_id = SA_REPOR job_id = AD_PRESAND salary 15000;Rules of PrecedenceSELECT last_name, job_id, salaryFROM employeesWHERE (job_id = SA_RE

8、POR job_id = AD_PRES)AND salary 15000;12Using the ORDER BY ClauseSort retrieved rows with the ORDER BY clause:ASC: ascending order, defaultDESC: descending orderThe ORDER BY clause comes last in the SELECT statement:SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date ;

9、SortingSorting in descending order:Sorting by column alias:Sorting by multiple columns:SELECT last_name, job_id, department_id, hire_dateFROM employeesORDER BY hire_date DESC ;1SELECT employee_id, last_name, salary*12 annsalFROM employeesORDER BY annsal ;2SELECT last_name, department_id, salaryFROM

10、employeesORDER BY department_id, salary DESC;3Substitution Variables. salary = ? department_id = ? . last_name = ? .I want to query different values.Substitution VariablesUse iSQL*Plus substitution variables to: Temporarily store values with single-ampersand (&) and double-ampersand (&) substitution

11、Use substitution variables to supplement the following:WHERE conditionsORDER BY clausesColumn expressionsTable namesEntire SELECT statementsSELECT employee_id, last_name, salary, department_idFROM employeesWHERE employee_id = &employee_num ;Using the & Substitution VariableUse a variable prefixed wi

12、th an ampersand (&) to prompt the user for a value:Using the & Substitution Variable10112SELECT last_name, department_id, salary*12FROM employeesWHERE job_id = &job_title ;Character and Date Values with Substitution VariablesUse single quotation marks for date and character values:Specifying Column

13、Names, Expressions, and TextSELECT employee_id, last_name, job_id,&column_nameFROM employeesWHERE &conditionORDER BY &order_column ;salarysalary 15000last_nameSELECT employee_id, last_name, job_id, &column_nameFROM employeesORDER BY &column_name ;Using the & Substitution VariableUse the double amper

14、sand (&) if you want to reuse the variable value without prompting the user each time:Using the iSQL*Plus DEFINE CommandUse the iSQL*Plus DEFINE command to create and assign a value to a variable.Use the iSQL*Plus UNDEFINE command to remove a variable. DEFINE employee_num = 200SELECT employee_id, la

15、st_name, salary, department_idFROM employeesWHERE employee_id = &employee_num ;UNDEFINE employee_numold 3: WHERE employee_id = &employee_numnew 3: WHERE employee_id = 200SET VERIFY ONSELECT employee_id, last_name, salary, department_idFROM employeesWHERE employee_id = &employee_num;Using the VERIFY

16、CommandUse the VERIFY command to toggle the display of the substitution variable, both before and after iSQL*Plus replaces substitution variables with values:SELECT *|DISTINCT column|expression alias,.FROM tableWHERE condition(s)ORDER BY column, expr, alias ASC|DESC ;SummaryIn this lesson, you shoul

17、d have learned how to: Use the WHERE clause to restrict rows of output:Use the comparison conditionsUse the BETWEEN, IN, LIKE, and NULL conditionsApply the logical AND, OR, and NOT operatorsUse the ORDER BY clause to sort rows of output:Use ampersand substitution in iSQL*Plus to restrict and sort output at run time Practice 2: Overvie