Oracle9i-SQL-1 Writing Basic SQL SELECT Statements

1Copyright©OracleCorporation,2001.Allrightsreserved.WritingBasicSQLSELECTStatements1-2Copyright©OracleCorporation,2001.Allrightsreserved.ObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:•ListthecapabilitiesofSQLSELECTstatements•ExecuteabasicSELECTstatement•DifferentiatebetweenSQLstatementsandiSQL*Pluscommands1-3Copyright©OracleCorporation,2001.Allrightsreserved.CapabilitiesofSQLSELECTStatementsSelectionProjectionTable1Table2Table1Table1Join1-4Copyright©OracleCorporation,2001.Allrightsreserved.BasicSELECTStatementSELECT*|{[DISTINCT]column|expression[alias],...}FROMtable;SELECT*|{[DISTINCT]column|expression[alias],...}FROMtable;•SELECTidentifieswhatcolumns•FROMidentifieswhichtable1-5Copyright©OracleCorporation,2001.Allrightsreserved.SELECT*FROMdepartments;SelectingAllColumns1-6Copyright©OracleCorporation,2001.Allrightsreserved.SelectingSpecificColumnsSELECTdepartment_id,location_idFROMdepartments;1-7Copyright©OracleCorporation,2001.Allrightsreserved.WritingSQLStatements•SQLstatementsarenotcasesensitive.•SQLstatementscanbeononeormorelines.•Keywordscannotbeabbreviatedorsplitacrosslines.•Clausesareusuallyplacedonseparatelines.•Indentsareusedtoenhancereadability.1-9Copyright©OracleCorporation,2001.Allrightsreserved.ArithmeticExpressionsCreateexpressionswithnumberanddatedatabyusingarithmeticoperators.Operator+-*/DescriptionAddSubtractMultiplyDivide1-10Copyright©OracleCorporation,2001.Allrightsreserved.UsingArithmeticOperatorsSELECTlast_name,salary,salary+300FROMemployees;…1-11Copyright©OracleCorporation,2001.Allrightsreserved.OperatorPrecedence•Multiplicationanddivisiontakepriorityoveradditionandsubtraction.•Operatorsofthesamepriorityareevaluatedfromlefttoright.•Parenthesesareusedtoforceprioritizedevaluationandtoclarifystatements.***///+++___1-12Copyright©OracleCorporation,2001.Allrightsreserved.OperatorPrecedenceSELECTlast_name,salary,12*salary+100FROMemployees;…1-13Copyright©OracleCorporation,2001.Allrightsreserved.UsingParenthesesSELECTlast_name,salary,12*(salary+100)FROMemployees;…1-14Copyright©OracleCorporation,2001.Allrightsreserved.DefiningaNullValue•Anullisavaluethatisunavailable,unassigned,unknown,orinapplicable.•Anullisnotthesameaszeroorablankspace.SELECTlast_name,job_id,salary,commission_pctFROMemployees;……1-15Copyright©OracleCorporation,2001.Allrightsreserved.SELECTlast_name,12*salary*commission_pctFROMemployees;NullValuesinArithmeticExpressionsArithmeticexpressionscontaininganullvalueevaluatetonull.……1-16Copyright©OracleCorporation,2001.Allrightsreserved.DefiningaColumnAliasAcolumnalias:•Renamesacolumnheading•Isusefulwithcalculations•Immediatelyfollowsthecolumnname-therecanalsobetheoptionalASkeywordbetweenthecolumnnameandalias•Requiresdoublequotationmarksifitcontainsspacesorspecialcharactersoriscasesensitive1-17Copyright©OracleCorporation,2001.Allrightsreserved.UsingColumnAliasesSELECTlast_nameName,salary*12AnnualSalaryFROMemployees;SELECTlast_nameASname,commission_pctcommFROMemployees;……1-18Copyright©OracleCorporation,2001.Allrightsreserved.ConcatenationOperatorAconcatenationoperator:•Concatenatescolumnsorcharacterstringstoothercolumns•Isrepresentedbytwoverticalbars(||)•Createsaresultantcolumnthatisacharacterexpression1-19Copyright©OracleCorporation,2001.Allrightsreserved.UsingtheConcatenationOperatorSELECTlast_name||job_idASEmployeesFROMemployees;…1-20Copyright©OracleCorporation,2001.Allrightsreserved.LiteralCharacterStrings•Aliteralisacharacter,anumber,oradateincludedintheSELECTlist.•Dateandcharacterliteralvaluesmustbeenclosedwithinsinglequotationmarks.•Eachcharacterstringisoutputonceforeachrowreturned.1-21Copyright©OracleCorporation,2001.Allrightsreserved.UsingLiteralCharacterStringsSELECTlast_name||'isa'||job_idASEmployeeDetailsFROMemployees;…1-22Copyright©OracleCorporation,2001.Allrightsreserved.DuplicateRowsThedefaultdisplayofqueriesisallrows,includingduplicaterows.SELECTdepartment_idFROMemployees;SELECTdepartment_idFROMemployees;…1-23Copyright©OracleCorporation,2001.Allrightsreserved.EliminatingDuplicateRowsEliminateduplicaterowsbyusingtheDISTINCTkeywordintheSELECTclause.SELECTDISTINCTdepartment_idFROMemployees;1-24Copyright©OracleCorporation,2001.Allrightsreserved.SQLandiSQL*PlusInteractionSQLstatementsOracleserverQueryresultsQueryresultsiSQL*PluscommandsClientFormattedreportInternetInternetBrowserBrowseriiSQL*PlusSQL*Plus1-25Copyright©OracleCorporation,2001.Allrightsreserved.SQLStatementsVersusiSQL*PlusCommandsSQLSQLstatementsstatementsSQL•Alanguage•ANSIstandard•Keywordcannotbeabbreviated•StatementsmanipulatedataandtabledefinitionsinthedatabaseiiSQL*PlusSQL*Plus•Anenvironment•Oracleproprietary•Keywordscanbeabbreviated•Commandsdonotallowmanipulationofvaluesinthedatabase•Runsonabrowser•Centrallyloaded,doesnothavetobeimplementedoneachmachineiiSQL*PlusSQL*Pluscommandscommands1-26Copyright©OracleCorporation,2001.Allrightsreserved.OverviewofiSQL*PlusAfteryoulogintoiSQL*Plus,youcan:•Describethetablestructure•EdityourSQLstatement•ExecuteSQLfromiSQL*Plus•SaveSQLstatementstofilesand