book

Oracle Database 12c SQL

Name: Oracle Database 12c SQL
Author: Jason Price
ISBN: 9780071799362

by Jason Price

June 2013

Intermediate to advanced

688 pages

15h 2m

English

McGraw Hill Computing

Read now

Unlock full access

Cover
Title Page
Copyright Page
About the Author
Contents at a Glance
Contents
Introduction
Chapter 1: Introduction
What Is a Relational Database?Introducing Structured Query Language (SQL)Using SQL*PlusStarting SQL*PlusStarting SQL*Plus from the Command LinePerforming a SELECT Statement Using SQL*PlusUsing SQL DeveloperCreating the Store SchemaExamining the ScriptRunning the ScriptExamining the Store Data Definition Language StatementsAdding, Modifying, and Removing RowsAdding a Row to a TableModifying an Existing Row in a TableRemoving a Row from a TableConnecting to and Disconnecting from a DatabaseQuitting SQL*PlusIntroducing Oracle PL/SQLSummary
Chapter 2: Retrieving Information from Database Tables
Performing Single Table SELECT StatementsRetrieving All Columns from a TableLimiting Rows to Retrieve Using the WHERE ClauseRow IdentifiersRow NumbersPerforming ArithmeticPerforming Date ArithmeticUsing Columns in ArithmeticArithmetic Operator PrecedenceUsing Column AliasesCombining Column Output Using ConcatenationNull ValuesDisplaying Distinct RowsComparing ValuesUsing the Not Equal OperatorUsing the Greater Than OperatorUsing the Less Than Or Equal To OperatorUsing the ANY OperatorUsing the ALL OperatorUsing the SQL OperatorsUsing the LIKE OperatorUsing the IN OperatorUsing the BETWEEN OperatorUsing the Logical OperatorsUsing the AND OperatorUsing the OR OperatorLogical Operator PrecedenceSorting Rows Using the ORDER BY ClausePerforming SELECT Statements That Use Two TablesUsing Table AliasesCartesian ProductsPerforming SELECT Statements That Use More than Two TablesJoin Conditions and Join TypesNon-equijoinsOuter JoinsSelf JoinsPerforming Joins Using the SQL/92 SyntaxPerforming Inner Joins on Two Tables Using SQL/92Simplifying Joins with the USING KeywordPerforming Inner Joins on More than Two Tables Using SQL/92Performing Inner Joins on Multiple Columns Using SQL/92Performing Outer Joins Using SQL/92Performing Self Joins Using SQL/92Performing Cross Joins Using SQL/92Summary
Chapter 3: Using SQL*Plus
Viewing the Structure of a TableEditing SQL StatementsSaving, Retrieving, and Running FilesFormatting ColumnsSetting the Page SizeSetting the Line SizeClearing Column FormattingUsing VariablesTemporary VariablesDefined VariablesCreating Simple ReportsUsing Temporary Variables in a ScriptUsing Defined Variables in a ScriptPassing a Value to a Variable in a ScriptAdding a Header and FooterComputing SubtotalsGetting Help from SQL*PlusAutomatically Generating SQL StatementsDisconnecting from the Database and Exiting SQL*PlusSummary

Chapter 4: Using Simple Functions
Types of FunctionsUsing Single-Row FunctionsCharacter FunctionsNumeric FunctionsConversion FunctionsRegular Expression FunctionsUsing Aggregate FunctionsAVG()COUNT()MAX() and MIN()STDDEV()SUM()VARIANCE()Grouping RowsUsing the GROUP BY Clause to Group RowsIncorrect Usage of Aggregate Function CallsUsing the HAVING Clause to Filter Groups of RowsUsing the WHERE and GROUP BY Clauses TogetherUsing the WHERE, GROUP BY, and HAVING Clauses TogetherSummary
Chapter 5: Storing and Processing Dates and Times
Simple Examples of Storing and Retrieving DatesConverting Datetimes Using TO_CHAR() and TO_DATE()Using TO_CHAR() to Convert a Datetime to a StringUsing TO_DATE() to Convert a String to a DatetimeSetting the Default Date FormatHow Oracle Interprets Two-Digit YearsUsing the YY FormatUsing the RR FormatUsing Datetime FunctionsADD_MONTHS()LAST_DAY()MONTHS_BETWEEN()NEXT_DAY()ROUND()SYSDATETRUNC()Using Time ZonesTime Zone FunctionsThe Database Time Zone and Session Time ZoneObtaining Time Zone OffsetsObtaining Time Zone NamesConverting a Datetime from One Time Zone to AnotherUsing TimestampsUsing the Timestamp TypesTimestamp FunctionsUsing Time IntervalsUsing the INTERVAL YEAR TO MONTH TypeUsing the INTERVAL DAY TO SECOND TypeTime Interval FunctionsSummary
Chapter 6: Subqueries
Types of SubqueriesWriting Single-Row SubqueriesSubqueries in a WHERE ClauseUsing Other Single-Row OperatorsSubqueries in a HAVING ClauseSubqueries in a FROM Clause (Inline Views)Errors You Might EncounterWriting Multiple-Row SubqueriesUsing IN with a Multiple-Row SubqueryUsing ANY with a Multiple-Row SubqueryUsing ALL with a Multiple-Row SubqueryWriting Multiple-Column SubqueriesWriting Correlated SubqueriesA Correlated Subquery ExampleUsing EXISTS and NOT EXISTS with a Correlated SubqueryWriting Nested SubqueriesWriting UPDATE and DELETE Statements Containing SubqueriesWriting an UPDATE Statement Containing a SubqueryWriting a DELETE Statement Containing a SubqueryUsing Subquery FactoringSummary
Chapter 7: Advanced Queries
Using the Set OperatorsThe Example TablesUsing the UNION ALL OperatorUsing the UNION OperatorUsing the INTERSECT OperatorUsing the MINUS OperatorCombining Set OperatorsUsing the TRANSLATE() FunctionUsing the DECODE() FunctionUsing the CASE ExpressionUsing Simple CASE ExpressionsUsing Searched CASE ExpressionsHierarchical QueriesThe Example DataUsing the CONNECT BY and START WITH ClausesUsing the LEVEL Pseudo ColumnFormatting the Results from a Hierarchical QueryStarting at a Node Other than the RootUsing a Subquery in a START WITH ClauseTraversing Upward Through the TreeEliminating Nodes and Branches from a Hierarchical QueryIncluding Other Conditions in a Hierarchical QueryUsing Recursive Subquery Factoring to Query Hierarchical DataUsing the ROLLUP and CUBE ClausesThe Example TablesUsing the ROLLUP ClauseUsing the CUBE ClauseUsing the GROUPING() FunctionUsing the GROUPING SETS ClauseUsing the GROUPING_ID() FunctionUsing a Column Multiple Times in a GROUP BY ClauseUsing the GROUP_ID() FunctionUsing CROSS APPLY and OUTER APPLYCROSS APPLYOUTER APPLYUsing LATERALSummary
Chapter 8: Analyzing Data
Using Analytic FunctionsThe Example TableUsing the Ranking FunctionsUsing the Inverse Percentile FunctionsUsing the Window FunctionsUsing the Reporting FunctionsUsing the LAG() and LEAD() FunctionsUsing the FIRST and LAST FunctionsUsing the Linear Regression FunctionsUsing the Hypothetical Rank and Distribution FunctionsUsing the MODEL ClauseAn Example of the MODEL ClauseUsing Positional and Symbolic Notation to Access CellsAccessing a Range of Cells Using BETWEEN and ANDAccessing All Cells Using ANY and IS ANYGetting the Current Value of a Dimension Using CURRENTV()Accessing Cells Using a FOR LoopHandling Null and Missing ValuesUpdating Existing CellsUsing the PIVOT and UNPIVOT ClausesA Simple Example of the PIVOT ClausePivoting on Multiple ColumnsUsing Multiple Aggregate Functions in a PivotUsing the UNPIVOT ClausePerforming Top-N QueriesUsing the FETCH FIRST ClauseUsing the OFFSET ClauseUsing the PERCENT ClauseUsing the WITH TIES ClauseFinding Patterns in DataFinding V-Shaped Data Patterns in the all_sales2 TableFinding W-Shaped Data Patterns in the all_sales3 TableFinding V-Shaped Data Patterns in the all_sales3 TableSummary
Chapter 9: Changing Table Contents
Adding Rows Using the INSERT StatementOmitting the Column ListSpecifying a Null Value for a ColumnIncluding Quote Marks in a Column ValueCopying Rows from One Table to AnotherModifying Rows Using the UPDATE StatementReturning an Aggregate Function Value Using the RETURNING ClauseRemoving Rows Using the DELETE StatementDatabase IntegrityEnforcement of Primary Key ConstraintsEnforcement of Foreign Key ConstraintsUsing Default ValuesMerging Rows Using MERGEDatabase TransactionsCommitting and Rolling Back a TransactionStarting and Ending a TransactionSavepointsACID Transaction PropertiesConcurrent TransactionsTransaction LockingTransaction Isolation LevelsA SERIALIZABLE Transaction ExampleQuery FlashbacksGranting the Privilege for Using FlashbacksTime Query FlashbacksSystem Change Number Query FlashbacksSummary
Chapter 10: Users, Privileges, and Roles
A Very Short Introduction to Database StorageUsersCreating a UserChanging a User’s PasswordDeleting a UserSystem PrivilegesGranting System Privileges to a UserChecking System Privileges Granted to a UserMaking Use of System PrivilegesRevoking System Privileges from a UserObject PrivilegesGranting Object Privileges to a UserChecking Object Privileges MadeChecking Object Privileges ReceivedMaking Use of Object PrivilegesCreating SynonymsCreating Public SynonymsRevoking Object PrivilegesRolesCreating RolesGranting Privileges to RolesGranting Roles to a UserChecking Roles Granted to a UserChecking System Privileges Granted to a RoleChecking Object Privileges Granted to a RoleMaking Use of Privileges Granted to a RoleEnabling and Disabling RolesRevoking a RoleRevoking Privileges from a RoleDropping a RoleAuditingPrivileges Required to Perform AuditingAuditing ExamplesAudit Trail ViewsSummary
Chapter 11: Creating Tables, Sequences, Indexes, and Views
TablesCreating a TableGetting Information on TablesGetting Information on Columns in TablesAltering a TableRenaming a TableAdding a Comment to a TableTruncating a TableDropping a TableUsing the BINARY_FLOAT and BINARY_DOUBLE TypesUsing DEFAULT ON NULL ColumnsUsing Visible and Invisible Columns in a TableSequencesCreating a SequenceRetrieving Information on SequencesUsing a SequencePopulating a Primary Key Using a SequenceSpecifying a Default Column Value Using a SequenceUsing Identity ColumnsModifying a SequenceDropping a SequenceIndexesCreating a B-tree IndexCreating a Function-Based IndexRetrieving Information on IndexesRetrieving Information on the Indexes on a ColumnModifying an IndexDropping an IndexCreating a Bitmap IndexViewsCreating and Using a ViewModifying a ViewDropping a ViewUsing Visible and Invisible Columns in a ViewFlashback Data ArchivesSummary
Chapter 12: Introducing PL/SQL Programming
Block StructureVariables and TypesConditional LogicLoopsSimple LoopsWHILE LoopsFOR LoopsCursorsStep 1: Declare the Variables to Store the Column ValuesStep 2: Declare the CursorStep 3: Open the CursorStep 4: Fetch the Rows from the CursorStep 5: Close the CursorComplete Example: product_cursor.sqlCursors and FOR LoopsOPEN-FOR StatementUnconstrained CursorsExceptionsZERO_DIVIDE ExceptionDUP_VAL_ON_INDEX ExceptionINVALID_NUMBER ExceptionOTHERS ExceptionProceduresCreating a ProcedureCalling a ProcedureGetting Information on ProceduresDropping a ProcedureViewing Errors in a ProcedureFunctionsCreating a FunctionCalling a FunctionGetting Information on FunctionsDropping a FunctionPackagesCreating a Package SpecificationCreating a Package BodyCalling Functions and Procedures in a PackageGetting Information on Functions and Procedures in a PackageDropping a PackageTriggersWhen a Trigger FiresSetting up the Example TriggerCreating a TriggerFiring a TriggerGetting Information on TriggersDisabling and Enabling a TriggerDropping a TriggerAdditional PL/SQL FeaturesSIMPLE_INTEGER TypeSequences in PL/SQLPL/SQL Native Machine Code GenerationWITH ClauseSummary
Chapter 13: Database Objects
Introducing ObjectsRunning the Script to Create the Object SchemaCreating Object TypesUsing DESCRIBE to Get Information on Object TypesUsing Object Types in Database TablesColumn ObjectsObject TablesObject Identifiers and Object ReferencesComparing Object ValuesUsing Objects in PL/SQLThe get_products() FunctionThe display_product() ProcedureThe insert_product() ProcedureThe update_product_price() ProcedureThe get_product() FunctionThe update_product() ProcedureThe get_product_ref() FunctionThe delete_product() ProcedureThe product_lifecycle() ProcedureThe product_lifecycle2() ProcedureType InheritanceRunning the Script to Create the Second Object SchemaInheriting AttributesUsing a Subtype Object in Place of a Supertype ObjectSQL ExamplesPL/SQL ExamplesNOT SUBSTITUTABLE ObjectsOther Useful Object FunctionsIS OF()TREAT()SYS_TYPEID()NOT INSTANTIABLE Object TypesUser-Defined ConstructorsOverriding MethodsGeneralized InvocationRunning the Script to Create the Third Object SchemaInheriting AttributesSummary
Chapter 14: Collections
Introducing CollectionsRunning the Script to Create the Collection SchemaCreating Collection TypesCreating a Varray TypeCreating a Nested Table TypeUsing a Collection Type to Define a Column in a TableUsing a Varray Type to Define a Column in a TableUsing a Nested Table Type to Define a Column in a TableGetting Information on CollectionsGetting Information on a VarrayGetting Information on a Nested TablePopulating a Collection with ElementsPopulating a Varray with ElementsPopulating a Nested Table with ElementsRetrieving Elements from CollectionsRetrieving Elements from a VarrayRetrieving Elements from a Nested TableUsing TABLE() to Treat a Collection as a Series of RowsUsing TABLE() with a VarrayUsing TABLE() with a Nested TableModifying Elements of CollectionsModifying Elements of a VarrayModifying Elements of a Nested TableUsing a Map Method to Compare the Contents of Nested TablesUsing CAST() to Convert Collections from One Type to AnotherUsing CAST() to Convert a Varray to a Nested TableUsing CAST() to Convert a Nested Table to a VarrayUsing Collections in PL/SQLManipulating a VarrayManipulating a Nested TableUsing PL/SQL Collection MethodsCreating and Using Multilevel CollectionsRunning the Script to Create the Second Collection SchemaUsing Multilevel CollectionsOracle Database 10g Enhancements to CollectionsRunning the Script to Create the Third Collection SchemaCreating Associative ArraysChanging the Size of an Element TypeIncreasing the Number of Elements in a VarrayUsing Varrays in Temporary TablesUsing a Different Tablespace for a Nested Table’s Storage TableANSI Support for Nested TablesSummary
Chapter 15: Large Objects
Introducing Large Objects (LOBs)The Example FilesLarge Object TypesCreating Tables Containing Large ObjectsUsing Large Objects in SQLUsing CLOBs and BLOBsUsing BFILEsUsing Large Objects in PL/SQLAPPEND()CLOSE()COMPARE()COPY()CREATETEMPORARY()ERASE()FILECLOSE()FILECLOSEALL()FILEEXISTS()FILEGETNAME()FILEISOPEN()FILEOPEN()FREETEMPORARY()GETCHUNKSIZE()GETLENGTH()GET_STORAGE_LIMIT()INSTR()ISOPEN()ISTEMPORARY()LOADFROMFILE()LOADBLOBFROMFILE()LOADCLOBFROMFILE()OPEN()READ()SUBSTR()TRIM()WRITE()WRITEAPPEND()Example PL/SQL ProceduresLONG and LONG RAW TypesThe Example TablesAdding Data to LONG and LONG RAW ColumnsConverting LONG and LONG RAW Columns to LOBsOracle Database 10g Enhancements to Large ObjectsImplicit Conversion Between CLOB and NCLOB ObjectsUse of the :new Attribute When Using LOBs in a TriggerOracle Database 11g Enhancements to Large ObjectsEncrypting LOB DataCompressing LOB DataRemoving Duplicate LOB DataOracle Database 12c Enhancement to Large ObjectsSummary
Chapter 16: SQL Tuning
Introducing SQL TuningUse a WHERE Clause to Filter RowsUse Table Joins Rather than Multiple QueriesUse Fully Qualified Column References When Performing JoinsUse CASE Expressions Rather than Multiple QueriesAdd Indexes to TablesWhen to Create a B-Tree IndexWhen to Create a Bitmap IndexUse WHERE Rather than HAVINGUse UNION ALL Rather than UNIONUse EXISTS Rather than INUse EXISTS Rather than DISTINCTUse GROUPING SETS Rather than CUBEUse Bind VariablesNon-Identical SQL StatementsIdentical SQL Statements That Use Bind VariablesListing and Printing Bind VariablesUsing a Bind Variable to Store a Value Returned by a PL/SQL FunctionUsing a Bind Variable to Store Rows from a REFCURSORComparing the Cost of Performing QueriesExamining Execution PlansComparing Execution PlansPassing Hints to the OptimizerAdditional Tuning ToolsOracle Enterprise ManagerAutomatic Database Diagnostic MonitorSQL Tuning AdvisorSQL Access AdvisorSQL Performance AnalyzerDatabase ReplayReal-Time SQL MonitoringSQL Plan ManagementSummary
Chapter 17: XML and the Oracle Database
Introducing XMLGenerating XML from Relational DataXMLELEMENT()XMLATTRIBUTES()XMLFOREST()XMLAGG()XMLCOLATTVAL()XMLCONCAT()XMLPARSE()XMLPI()XMLCOMMENT()XMLSEQUENCE()XMLSERIALIZE()A PL/SQL Example That Writes XML Data to a FileXMLQUERY()Saving XML in the DatabaseThe Example XML FileCreating the Example XML SchemaRetrieving Information from the Example XML SchemaUpdating Information in the Example XML SchemaSummary
Appendix: Oracle Data Types
Oracle SQL TypesOracle PL/SQL Types
Index

Content preview from Oracle Database 12c SQL

CHAPTER

Storing and Processing Dates and Times

In this chapter, you’ll learn how to perform the following tasks:

Use a datetime to store a date and time. A datetime stores a year, month, day, hour, minute, and second.

Use a timestamp to store a date and time. A timestamp stores a year, month, day, hour, minute, second, fractional second, and time zone.

Use a time interval to store a length of time. An example time interval is 1 year 3 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780071799355

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Oracle Database 12c SQL

by Jason Price

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.