Restricting and Sorting Data

Copyright©2009,Oracle.Allrightsreserved.RestrictingandSortingDataCopyright©2009,Oracle.Allrightsreserved.2-2ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•Limittherowsthatareretrievedbyaquery•Sorttherowsthatareretrievedbyaquery•UseampersandsubstitutiontorestrictandsortoutputatruntimeCopyright©2009,Oracle.Allrightsreserved.2-3LimitingRowsUsingaSelection“retrieveallemployeesindepartment90”EMPLOYEES…Copyright©2009,Oracle.Allrightsreserved.2-4LimitingtheRowsThatAreSelected•RestricttherowsthatarereturnedbyusingtheWHEREclause:•TheWHEREclausefollowstheFROMclause.SELECT*|{[DISTINCT]column|expression[alias],...}FROMtable[WHEREcondition(s)];Copyright©2009,Oracle.Allrightsreserved.2-5UsingtheWHEREClauseSELECTemployee_id,last_name,job_id,department_idFROMemployeesWHEREdepartment_id=90;Copyright©2009,Oracle.Allrightsreserved.2-6SELECTlast_name,job_id,department_idFROMemployeesWHERElast_name='Whalen';CharacterStringsandDates•Characterstringsanddatevaluesareenclosedinsinglequotationmarks.•Charactervaluesarecasesensitive,anddatevaluesareformatsensitive.•ThedefaultdateformatisDD-MON-RR.Copyright©2009,Oracle.Allrightsreserved.2-7ComparisonConditionsOperatorMeaning=EqualtoGreaterthan=GreaterthanorequaltoLessthan=LessthanorequaltoNotequaltoBETWEEN...AND...Betweentwovalues(inclusive)IN(set)MatchanyofalistofvaluesLIKEMatchacharacterpatternISNULLIsanullvalueCopyright©2009,Oracle.Allrightsreserved.2-8SELECTlast_name,salaryFROMemployeesWHEREsalary=3000;UsingComparisonConditionsCopyright©2009,Oracle.Allrightsreserved.2-9SELECTlast_name,salaryFROMemployeesWHEREsalaryBETWEEN2500AND3500;UsingtheBETWEENConditionUsetheBETWEENconditiontodisplayrowsbasedonarangeofvalues:LowerlimitUpperlimitCopyright©2009,Oracle.Allrightsreserved.2-10SELECTemployee_id,last_name,salary,manager_idFROMemployeesWHEREmanager_idIN(100,101,201);UsingtheINConditionUsetheINmembershipconditiontotestforvaluesinalist:Copyright©2009,Oracle.Allrightsreserved.2-11SELECTfirst_nameFROMemployeesWHEREfirst_nameLIKE'S%';UsingtheLIKECondition•UsetheLIKEconditiontoperformwildcardsearchesofvalidsearchstringvalues.•Searchconditionscancontaineitherliteralcharactersornumbers:–%denoteszeroormanycharacters.–_denotesonecharacter.Copyright©2009,Oracle.Allrightsreserved.2-12UsingtheLIKECondition•Youcancombinepattern-matchingcharacters:•YoucanusetheESCAPEidentifiertosearchfortheactual%and_symbols.SELECTlast_nameFROMemployeesWHERElast_nameLIKE'_o%';Copyright©2009,Oracle.Allrightsreserved.2-13SELECTlast_name,manager_idFROMemployeesWHEREmanager_idISNULL;UsingtheNULLConditionsTestfornullswiththeISNULLoperator.Copyright©2009,Oracle.Allrightsreserved.2-14LogicalConditionsOperatorMeaningANDReturnsTRUEifbothcomponentconditionsaretrueORReturnsTRUEifeithercomponentconditionistrueNOTReturnsTRUEifthefollowingconditionisfalseCopyright©2009,Oracle.Allrightsreserved.2-15UsingtheANDOperatorANDrequiresbothconditionstobetrue:SELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREsalary=10000ANDjob_idLIKE'%MAN%';Copyright©2009,Oracle.Allrightsreserved.2-16UsingtheOROperatorORrequireseitherconditiontobetrue:SELECTemployee_id,last_name,job_id,salaryFROMemployeesWHEREsalary=10000ORjob_idLIKE'%MAN%';Copyright©2009,Oracle.Allrightsreserved.2-17SELECTlast_name,job_idFROMemployeesWHEREjob_idNOTIN('IT_PROG','ST_CLERK','SA_REP');UsingtheNOTOperatorCopyright©2009,Oracle.Allrightsreserved.2-18RulesofPrecedenceYoucanuseparenthesestooverriderulesofprecedence.OperatorMeaning1Arithmeticoperators2Concatenationoperator3Comparisonconditions4IS[NOT]NULL,LIKE,[NOT]IN5[NOT]BETWEEN6Notequalto7NOTlogicalcondition8ANDlogicalcondition9ORlogicalconditionCopyright©2009,Oracle.Allrightsreserved.2-19SELECTlast_name,job_id,salaryFROMemployeesWHEREjob_id='SA_REP'ORjob_id='AD_PRES'ANDsalary15000;RulesofPrecedenceSELECTlast_name,job_id,salaryFROMemployeesWHERE(job_id='SA_REP'ORjob_id='AD_PRES')ANDsalary15000;12Copyright©2009,Oracle.Allrightsreserved.2-20UsingtheORDERBYClause•SortretrievedrowswiththeORDERBYclause:–ASC:ascendingorder,default–DESC:descendingorder•TheORDERBYclausecomeslastintheSELECTstatement:SELECTlast_name,job_id,department_id,hire_dateFROMemployeesORDERBYhire_date;…Copyright©2009,Oracle.Allrightsreserved.2-21Sorting•Sortingindescendingorder:•Sortingbycolumnalias:•Sortingbymultiplecolumns:SELECTlast_name,job_id,department_id,hire_dateFROMemployeesORDERBYhire_dateDESC;1SELECTemployee_id,last_name,salary*12annsalFROMemployeesORDERBYannsal;2SELECTlast_name,department_id,salaryFROMemployeesORDERBYdepartment_id,salaryDESC;3Copyright©2009,Oracle.Allrightsreserved.2-22SubstitutionVariables...salary=?……department_id=?…...last_name=?...Iwanttoquerydifferentvalues.Copyright©2009,Oracle.Allrightsreserved.2-23SubstitutionVariables•Usesubstitutionvariablesto:–Temporarilystorevalueswithsingle-ampersand(&)anddouble-ampersand(&&)substitution•Usesubstitutionvariablestosupplementthefollowing:–WHEREconditions–ORDERBYclauses–Columnexpressions–Tablenames–EntireSELECTstatementsCopyright©2009,Oracle.Allrightsre