WIXLIB

ORACLEOracle PressOracle Database 12cSQLJason PriceMcGrawHillEducationNew YorkLondonChicagoMadridNew DelhiSan to

Introduction1xxiIntroduction1What Is2aRelational Database?Introducing Structured Query Language (SQL)Using SQL*Plus34Starting SQL*Plus4Starting SQL*Plus from the Command LinePerforming a SELECT Statement Using SQL*PlusSQLUsingDeveloperCreating the Store SchemaExaminingRunningatheScriptScriptRow toModifyingana9TablefromRow inaaTableTable2022DatabaseOracle PL/SQL23242425RetrievingInformation from Database TablesPerforming Single ovingConnecting to and Disconnecting fromQuitting SQL*Plus2911Existinga RowIntroducing7theExamining the Store Data Definition LanguageAdding, Modifying, and Removing RowsAdding55SELECT StatementsAll Columns fromRowstoRetrieveaTableUsingthe WHERE Clause27282929Row Identifiers29Row Numbers30 VII

viiiOracle DatabasePerformingSQL12c31Arithmetic31Date ArithmeticPerformingUsing Columns33in Arithmetic33Operator PrecedenceArithmetic34Using Column AliasesCombiningOutput UsingColumn35Concatenation35Null Values37Displaying Distinct RowsComparing ValuesUsing the Not Equal OperatorUsing the Greater Than OperatorUsing the Less Than Or Equal To Operator3838393939Using the ANY Operatorthe ALLUsingUsing the SQL40LIKEOperatorOperatorBETWEEN OperatorUsingthe IN42Usingthe43the43Logical OperatorsUsingthe ANDUsingthe OR Operator44Precedence44the ORDER BY Clause45SELECT Statements That Use Two Tables46RowsPerforming43OperatorLogical OperatorSorting40OperatorsUsing theUsing40OperatorUsingUsing Table Aliases48Cartesian Products49PerformingSELECT Statements That Use More than Two Tables50Non-equijoins51Outer Joins52Self Joins55Performing Joins UsingtheSQL/92 Syntax57Performing Inner Joins on Two Tables Using SQL/92Simplifying Joins with the USING KeywordJoinsPerformingInnerPerformingInner JoinsPerformingPerformingOuter JoinsSelf JoinsPerformingCross Joinson MoreonUsing SQL792Columns Using SQL/92Using SQL/92Using SQL/92Using SQL/925757585959616161Using SQL*PlusViewing the Structure of aEditing SQL Statementsthan Two TablesMultipleSummary349Join Conditions and Join Types63TableSaving, Retrieving, and Running6465Files66

ContentsFormatting ColumnsSetting the Page Size7072the Line Size73Clearing Column Formatting74SettingUsing VariablesTemporary Variables7474Defined VariablesCreating Simple ReportsUsing Temporary7780Variables inDefined Variables inUsingPassingAddingValue toaaVariableaScriptScriptin a Scripta808181Header and Footera82Computing SubtotalsGetting Help from SQL*PlusAutomatically Generating SQLDisconnecting from48485Statementsthe Database and86Exiting SQL*Plus87Summary87Using Simple FunctionsTypes of Functions8990Functions90Character Functions90Using Single-RowNumeric Functions100Conversion FunctionsUsing Aggregate106FunctionsRegular Expression118Functions126AVG()126COUNTQ127MAX() and Using129the GROUP BY Clause toIncorrectUsageofAggregateGroupRows129Function Calls133Using the HAVING Clause to Filter Groups of RowsUsing the WHERE and GROUP BY Clauses TogetherUsing the WHERE,Summary5IXGROUP BY, and HAVING ClausesConvertingUsingTO CHAR()Using TO CHAR() to Convert a DatetimeUsing TO DATE()Together135137138and TO DATE()to Convert a134135Storing and Processing Dates and TimesSimple Examples of Storing and Retrieving DatesDatetimes133StringtoaStringto a Datetime139140145

XOracle DatabaseSetting12c149Interprets Two-Digit YearsUsingUsingUsing148the Default Date FormatHow OracleUsingSQLthe YY Format149the RR Format150Datetime FunctionsADD MONTHS()152LAST DAY()153MONTHS BETWEEN()153NEXT DAY()154ROUNDO154SYSDATE155TRUNCO155Time Zones156157Time Zone FunctionsThe Database Time Zone and157Session Time Zone159Zone OffsetsObtaining TimeObtaining Time Zone NamesConverting a Datetime from One Time159Zone to AnotherTimestampUsing160164Functions170Time IntervalsUsing theUsingINTERVAL YEAR TO MONTHthe INTERVAL DAY TO SECOND170TypeType1 731 75Time Interval Writing Single-Row SubqueriesSubqueries in a WHERE178178Clause178Using Other Single-Row OperatorsSubqueries in a HAVING ClauseSubqueries in a FROM Clause (InlineErrors YouMight1 79180Views)181Encounter182Writing Multiple-Row SubqueriesUsingUsingUsingIN withANY withALL with183Multiple-Row Subqueryaaa183Multiple-Row Subquery184Multiple-Row Subquery185Writing Multiple-Column SubqueriesWriting Correlated SubqueriesA CorrelatedUsingWriting160160Using TimestampsUsing the Timestamp Types185Subquery ExampleEXISTS and NOT EXISTS 9

ContentsWritingUPDATE and DELETE StatementsWritingWriting7UPDATE StatementanaDELETE StatementContaining 191191191Using Subquery Factoring192Summary193Advanced Queries195Using the196SetTheOperatorsExample TablesUsing theUsing the196UNION ALLUNIONOperatorOperator198199Using the INTERSECT OperatorUsing the MINUS e TRANSLATED Function202the DECODEf) Function204Usingthe CASE205ExpressionUsing Simple CASE ExpressionsUsingSearched CASE206207ExpressionsHierarchical QueriesTheExample208Data208Using the CONNECT BY and START WITH ClausesUsing211the Results from212FormattingStarting at aUsingaaHierarchical QueryNode Other than the RootSubqueryin a START WITHIncludingUsingOther Conditions inRecursive212ClauseTraversing Upward Through the TreeEliminating Nodes and Branches fromUsing210the LEVEL Pseudo Column213213aHierarchicalQueryHierarchical QueryaSubquery FactoringtoQueryHierarchical Datathe ROLLUP and CUBE Clauses214215215220The Example Tables220Using theUsing theROLLUP Clause222CUBE Clause224Usingthe GROUPINCO Function226Using the GROUPING SETS ClauseUsing the GROUPINGJDQ FunctionUsingUsingaColumnMultipleTimesthe GROUPJDO FunctionUsing CROSS APPLY and OUTER APPLYCROSS APPLYOUTER APPLYLATERALUsingSummaryinaGROUP BY Clause229229231232233234234235235

XliOracle Database 12c8SQL237Analyzing DataUsing Analytic238FunctionsTheTableExampleUsing the Ranking238246Usingthe Inverse Percentile FunctionsUsingUsingUsingthe Window Functions247the254the LAG() and LEADO Functions257Usingthe FIRST and LAST Functions257the gUsing the Hypothetical RankUsing239Functionsand Distribution Functionsthe MODEL ClauseAnExample260of the MODEL ClauseUsing Positional andAccessingaAccessingAll CellsRangeSymbolicof CellsUsing260NotationUsingtoAccess Cells262BETWEEN and AND262ANY and IS ANY263Getting the Current Value of a Dimension Using CURRENTVOAccessing Cells Using a FOR LoopNull andHandlingMissing ValuesSimple Example269ColumnsPivotingMultipleUsing Multiple AggregateUsing269of the PIVOT ClauseonFunctions in270aPivot272the UNPIVOT Clause273PerformingTop-N Queries274Usingthe FETCH FIRST Clause274Usingthe OFFSET Clause275Using the PERCENT ClauseUsing the WITH TIES ClauseFinding276277Patterns in Data277Finding V-ShapedData Patterns in theFinding W-ShapedFinding V-Shaped Dataall sales2 Table278Data Patterns in the all sales3 TablePatterns in the281all sales3 Table282Summary9284Changing Table ContentsAdding264268the PIVOT and UNPIVOT ClausesA263266Updating Existing CellsUsing259RowsUsingOmitting285the INSERT Statement286the Column ListSpecifyinga287Null Value forIncluding QuoteMarks inColumnaa287Column ValueCopying Rows from One Table to AnotherModifying Rows Using the UPDATE StatementReturning an Aggregate Function Value Using the RETURNINGRemoving Rows Using the DELETE Statement288288288Clause290290

ContentsDatabaseIntegrity291Enforcement ofEnforcement ofPrimary KeyConstraints291Foreign KeyConstraints291Using Default ValuesMergingRowsUsing292MERGE293Database Transactions296andRolling Back a TransactionEnding a TransactionCommittingStarting andSavepoints298Concurrent lashbacksGrantingTime304thePrivilege for UsingFlashbacksQuery FlashbacksNumberSystem Change306307Users, Privileges, and RolesVery304304Query FlashbacksSummaryA297300A SERIALIZABLE Transaction10296ACID Transaction PropertiesTransaction Isolation LevelsQueryxi'li309Short Introduction to DatabaseStorage310Users310Creating a UserChanging a User'sDeleting310Password312a User312System PrivilegesGranting System Privileges to a UserChecking System Privileges Granted toMakingUse of312313aSystem PrivilegesRevoking System Privileges from a UserObject PrivilegesGranting Object Privileges to a UserChecking Object Privileges MadeChecking Object Privileges ReceivedMaking Use of Object PrivilegesCreating ingSynonymsRevoking Object Privileges321322Roles323Creating RolesGranting PrivilegesGranting Roles to aChecking323to Roles324UserRoles Granted to324aUser324

XlVOracle Database 12c SQLChecking System Privileges GrantedChecking Object Privileges GrantedUse ofMakingEnablingRevokingandPrivileges GrantedDisabling326Role326to aaRoleRoles330Revoking Privileges fromaRole330Rolea330330Privileges RequiredAuditing Examplesto PerformAuditing331331Audit Trail Views333Summary11328329RoleaDroppingAuditingto a Roleto334Creating Tables, Sequences, Indexes,and ngGetting InformationAltering a TableRenamingAddingaaonTables338onColumns in Tables338349Comment toTruncatinga339TableaTable349Table350Dropping a Table350Using the BINARY FLOAT and ESINARY DOUBLE TypesUsing DEFAULT ON NULL ColumnsUsing Visible and Invisible Columns in a pulating a Primary Key Using a SequenceSpecifying a Default Column Value Using a SequenceUsing Identity ColumnsModifying a CreatingaB-tree Index364Creating a Function-Based IndexRetrieving Information on IndexesRetrieving InformationModifying an IndexDropping an IndexCreating a BitmaponIndexthe Indexes365366on aColumn367367368368

ContentsViewsXV369Creating and UsingModifying a ViewView370377ViewDroppingUsing Visibleaa378and Invisible Columns ina View378Flashback Data Archives379Summary382Introducing PL/SQL Programming383Block Structure384Variables and TypesConditional Logic386Loops387386Simple LoopsWHILE387Loops388FOR Loops389Cursors390Declare the VariablesStep1:StepStep2: Declare the CursorStepStep4: Fetch3:Opento Store the Column Valuesthe Cursorthe Rows from the Cursor5: Close the CursorComplete Example: product cursor.sqlCursors and FORLoops390390391391392392393OPEN-FOR Statement394Unconstrained Cursors396Exceptions398ZERO DIVIDEException398DUP VAL ON INDEX Exception401INVALID NUMBER g a ProcedureCalling a ProcedureGetting Information onDropping a ProcedureErrors 408CreatingCalling 41041 aPackage Specification410aPackage Body411

XviOracle Database 12cSQLFunctions and Procedures inCallingGetting Information onDropping a Packagea412PackageFunctions and Procedures inaPackage414414Trig8ersTrigger FiresSetting up the Example TriggerCreating a TriggerWhena414415417Firing a TriggerGetting Information on TriggersDisabling and Enabling a TriggerDropping a TriggerAdditional PL/SQL Features417419420420420SIMPLEJNTEGER TypeSequences421in PL/SQLPL/SQL Native Machine Code Generation422WITH Clause423424Summary13Database425ObjectsIntroducing ObjectsRunning the Script to426Create theObject426Schema427Creating Object TypesUsing DESCRIBE to Get Information on Object TypesUsing Object Types in Database TablesColumn428429430ObjectsObject TablesObject Identifiers433andComparing ObjectUsing ObjectsThe get products()Object436ReferencesValues439441in PL/SQLTheTheTheTheTheFunction442Proceduredisplay product()insert product() Procedureupdate product price() Procedureget product() te product()get product ref()Thedelete product() ProcedureTheproduct lifecycle()The447Procedureproduct lifecycle2()448Procedure449Type bjectAttributesSubtype ObjectSQL ExamplesPL/SQLto Create the Second451451in Place ofaSupertype Object452453ExamplesNOT SUBSTITUTABLESchema454Objects455