book

Oracle Database 11g SQL

Name: Oracle Database 11g SQL
Author: Jason Price
ISBN: 9780071596138

by Jason Price

November 2007

Intermediate to advanced

656 pages

15h 9m

English

McGraw Hill Computing

Read now

Unlock full access

Cover Page
Oracle Database 11g SQL
Copyright Page
Dedication Page
About the Technical Editor
Contents
Acknowledgments
Introduction
1 Introduction
What Is a Relational Database?Introducing the Structured Query Language (SQL)Using SQL*PlusStarting SQL*PlusStarting SQL*Plus from the Command LinePerforming a SELECT Statement Using SQL * PlusSQL DeveloperCreating the Store SchemaRunning the SQL*Plus Script to Create the Store SchemaData Definition Language (DDL) Statements Used to Create the Store SchemaAdding, Modifying, and Removing RowsAdding a Row to a TableModifying an Existing Row in a TableRemoving a Row from a TableThe BINARY_FLOAT and BINARY_DOUBLE TypesBenefits of BINARY_FLOAT and BINARY_DOUBLEUsing BINARY_FLOAT and BINARY_DOUBLE in a TableSpecial ValuesQuitting SQL*PlusIntroducing Oracle PL/SQLSummary
2 Retrieving Information from Database Tables
Performing Single Table SELECT StatementsRetrieving All Columns from a TableSpecifying Rows to Retrieve Using the WHERE ClauseRow IdentifiersRow NumbersPerforming ArithmeticPerforming Date ArithmeticUsing Columns in ArithmeticUsing Column AliasesCombining Column Output Using ConcatenationNull ValuesDisplaying Distinct RowsComparing ValuesUsing the SQL OperatorsUsing the LIKE OperatorUsing the IN OperatorUsing the BETWEEN OperatorUsing the Logical OperatorsOperator 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

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
4 Using Simple Functions
Using 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
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
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 SubquerySummary
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 the Extended GROUP BY 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 the 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 ClauseSummary
8 Changing Table Contents
Adding Rows Using the INSERT StatementOmitting the Column ListSpecifying a Null Value for a ColumnIncluding Single and Double Quotes in a Column ValueCopying Rows from One Table to AnotherModifying Rows Using the UPDATE StatementThe 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
9 Users, Privileges, and Roles
UsersCreating 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 PrivilegesSynonymsPublic 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 RoleDefault RolesRevoking a RoleRevoking Privileges from a RoleDropping a RoleAuditingPrivileges Required to Perform AuditingAuditing ExamplesAudit Trail ViewsSummary
10 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 TableSequencesCreating a SequenceRetrieving Information on SequencesUsing a SequencePopulating a Primary Key Using a SequenceModifying 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 ViewFlashback Data ArchivesSummary
11 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 FiresSet Up for the Example TriggerCreating a TriggerFiring a TriggerGetting Information on TriggersDisabling and Enabling a TriggerDropping a TriggerNew Oracle Database 11g PL/SQL FeaturesSIMPLE_INTEGER TypeSequences in PL/SQLPL/SQL Native Machine Code GenerationSummary
12 Database Objects
Introducing ObjectsCreating 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 InheritanceUsing 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 InvocationSummary
13 Collections
Introducing CollectionsCreating 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 TablePL/SQL Collection MethodsMultilevel CollectionsOracle Database 10g Enhancements to CollectionsAssociative 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
14 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()GET_STORAGE_LIMIT()GETLENGTH()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 DataSummary
15 Running SQL Using Java
Getting StartedConfiguring Your ComputerSetting the ORACLE_HOME Environment VariableSetting the JAVA_HOME Environment VariableSetting the PATH Environment VariableSetting the CLASSPATH Environment VariableSetting the LD_LIBRARY_PATH Environment VariableThe Oracle JDBC DriversThe Thin DriverThe OCI DriverThe Server-Side Internal DriverThe Server-Side Thin DriverImporting the JDBC PackagesRegistering the Oracle JDBC DriversOpening a Database ConnectionConnecting to the Database Using getConnection()The Database URLConnecting to the Database Using an Oracle Data SourceCreating a JDBC Statement ObjectRetrieving Rows from the DatabaseStep 1: Create and Populate a ResultSet ObjectStep 2: Read the Column Values from the ResultSet ObjectStep 3: Close the ResultSet ObjectAdding Rows to the DatabaseModifying Rows in the DatabaseDeleting Rows from the DatabaseHandling NumbersHandling Database Null ValuesControlling Database TransactionsPerforming Data Definition Language StatementsHandling ExceptionsClosing Your JDBC ObjectsExample Program: BasicExample1.javaCompile BasicExample1Run BasicExample1Prepared SQL StatementsExample Program: BasicExample2.javaThe Oracle JDBC ExtensionsThe oracle.sql PackageThe oracle.jdbc PackageExample Program: BasicExample3.javaSummary
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 TablesUse 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 Manager Diagnostics PackAutomatic Database Diagnostic MonitorSummary
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
A Oracle Data Types
Oracle SQL TypesOracle PL/SQL Types
Index

Content preview from Oracle Database 11g SQL

Index

Symbols

− (minus), 31

# (pound), 107

% (percent), 40–41

% TYPE, 342, 346

() (parentheses), 34, 168

* (asterisk)

in arithmetic, 31

column retrieval, 29

customers table, 15

metacharacters, 110 / (forward slash)

/ (forward slash)

in arithmetic, 32–34

editing SQL statements, 65–66

PL/SQL statements, 341

SQL directory paths, 10

\ (backward slash), 10

: new attribute, 524–525

; (semicolon), 6, 341

? (question mark), 564

! = (not equal), 38–39

@ filename, 67

_ (underscore), 40–41

|| (concatenation operator), 34

+ (plus)

in arithmetic, 31

outer joins, 51–55

< (less than)

comparing object values, 393

non-equijoins, 50

value comparison, 38–39

<= (less than or equal)

comparing object values, 393

non-equijoins, 50

value comparison, 38–39

<> (not equal) ...

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: 9780071498500

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Oracle Database 11g SQL

by Jason Price

Index

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.