Oracle-OCP-SQL-15-Using-SET-Operators

Copyright©OracleCorporation,2001.Allrightsreserved.UsingSETOperators15-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•DescribeSEToperators•UseaSEToperatortocombinemultiplequeriesintoasinglequery•Controltheorderofrowsreturned15-3Copyright©OracleCorporation,2001.Allrightsreserved.TheSETOperatorsABUNION/UNIONALLABABINTERSECTABMINUS15-4Copyright©OracleCorporation,2001.Allrightsreserved.TablesUsedinThisLessonThetablesusedinthislessonare:•EMPLOYEES:Providesdetailsregardingallcurrentemployees•JOB_HISTORY:Recordsthedetailsofthestartdateandenddateoftheformerjob,andthejobidentificationnumberanddepartmentwhenanemployeeswitchesjobs15-7Copyright©OracleCorporation,2001.Allrightsreserved.TheUNIONOperatorTheUNIONoperatorreturnsresultsfrombothqueriesaftereliminatingduplications.AB15-8Copyright©OracleCorporation,2001.Allrightsreserved.UsingtheUNIONOperatorDisplaythecurrentandpreviousjobdetailsofallemployees.Displayeachemployeeonlyonce.SELECTemployee_id,job_idFROMemployeesUNIONSELECTemployee_id,job_idFROMjob_history;……15-10Copyright©OracleCorporation,2001.Allrightsreserved.TheUNIONALLOperatorABTheUNIONALLoperatorreturnsresultsfrombothqueries,includingallduplications.15-11Copyright©OracleCorporation,2001.Allrightsreserved.UsingtheUNIONALLOperatorDisplaythecurrentandpreviousdepartmentsofallemployees.SELECTemployee_id,job_id,department_idFROMemployeesUNIONALLSELECTemployee_id,job_id,department_idFROMjob_historyORDERBYemployee_id;……15-12Copyright©OracleCorporation,2001.Allrightsreserved.TheINTERSECTOperatorAB15-13Copyright©OracleCorporation,2001.Allrightsreserved.UsingtheINTERSECTOperatorDisplaytheemployeeIDsandjobIDsofemployeeswhocurrentlyhaveajobtitlethattheyheldbeforebeginningtheirtenurewiththecompany.SELECTemployee_id,job_idFROMemployeesINTERSECTSELECTemployee_id,job_idFROMjob_history;15-14Copyright©OracleCorporation,2001.Allrightsreserved.TheMINUSOperatorAB15-15Copyright©OracleCorporation,2001.Allrightsreserved.TheMINUSOperatorDisplaytheemployeeIDsofthoseemployeeswhohavenotchangedtheirjobsevenonce.SELECTemployee_id,job_idFROMemployeesMINUSSELECTemployee_id,job_idFROMjob_history;…15-16Copyright©OracleCorporation,2001.Allrightsreserved.SETOperatorGuidelines•TheexpressionsintheSELECTlistsmustmatchinnumberanddatatype.•Parenthesescanbeusedtoalterthesequenceofexecution.•TheORDERBYclause:–Canappearonlyattheveryendofthestatement–Willacceptthecolumnname,aliasesfromthefirstSELECTstatement,orthepositionalnotation15-17Copyright©OracleCorporation,2001.Allrightsreserved.TheOracleServerandSETOperators•DuplicaterowsareautomaticallyeliminatedexceptinUNIONALL.•Columnnamesfromthefirstqueryappearintheresult.•TheoutputissortedinascendingorderbydefaultexceptinUNIONALL.15-18Copyright©OracleCorporation,2001.Allrightsreserved.MatchingtheSELECTStatementsUsingtheUNIONoperator,displaythedepartmentID,location,andhiredateforallemployees.SELECTdepartment_id,TO_NUMBER(null)location,hire_dateFROMemployeesUNIONSELECTdepartment_id,location_id,TO_DATE(null)FROMdepartments;…15-19Copyright©OracleCorporation,2001.Allrightsreserved.MatchingtheSELECTStatement•UsingtheUNIONoperator,displaytheemployeeID,jobID,andsalaryofallemployees.SELECTemployee_id,job_id,salaryFROMemployeesUNIONSELECTemployee_id,job_id,0FROMjob_history;…15-20Copyright©OracleCorporation,2001.Allrightsreserved.ControllingtheOrderofRowsProduceanEnglishsentenceusingtwoUNIONoperators.COLUMNa_dummyNOPRINTSELECT'sing'ASMydream,3a_dummyFROMdualUNIONSELECT'I''dliketoteach',1FROMdualUNIONSELECT'theworldto',2FROMdualORDERBY2;15-21Copyright©OracleCorporation,2001.Allrightsreserved.SummaryInthislesson,youshouldhavelearnedhowto:•UseUNIONtoreturnalldistinctrows•UseUNIONALLtoreturnsallrows,includingduplicates•UseINTERSECTtoreturnallrowssharedbybothqueries•UseMINUStoreturnalldistinctrowsselectedbythefirstquerybutnotbythesecond•UseORDERBYonlyattheveryendofthestatement15-22Copyright©OracleCorporation,2001.Allrightsreserved.Practice15OverviewThispracticecoversusingtheOracle9idatetimefunctions.