book

Oracle Database 10g SQL

Name: Oracle Database 10g SQL
Author: Jason Price
ISBN: 9780071706704

by Jason Price

March 2004

Beginner

608 pages

13h 27m

English

McGraw Hill Computing

Read now

Unlock full access

Cover Page
Oracle Database 10g SQL
Copyright Page
Dedication
About the Authors
Contents at a Glance
Contents
Acknowledgments
Introduction
1 Introduction
What Is a Relational Database?Introducing the Structured Query Language (SQL)Using SQL*PlusStarting the Windows Version of SQL*PlusStarting the Command-Line Version of SQL*PlusPerforming a SELECT Statement Using SQL*PlusThe SQL*Plus WorksheetCreating 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 New Oracle 10g 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 StatementsSelecting All Columns from a TableUnderstanding Row IdentifiersPerforming ArithmeticUsing Date ArithmeticUsing Columns in ArithmeticUsing Column AliasesMerging Column Output Using ConcatenationUnderstanding Null ValuesDisplaying Distinct RowsFiltering Rows Using the WHERE ClauseUsing Comparison OperatorsUsing the SQL OperatorsUsing the Logical OperatorsUnderstanding Operator PrecedenceSorting Rows Using the ORDER BY ClausePerforming SELECT Statements that Use Two TablesSupplying Table AliasesCartesian ProductsPerforming SELECT Statements that Use More than Two TablesUnderstanding Join Conditions and Join TypesUnderstanding Non-equijoinsUnderstanding Outer JoinsUnderstanding Self 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 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
4 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()SYSDATE()TRUNC()Understanding Time ZonesTime Zone-Related 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-Related FunctionsUsing Time IntervalsUsing the INTERVAL YEAR TO MONTH TypeUsing the INTERVAL DAY TO SECOND TypeTime Interval-Related FunctionsSummary
5 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 SubtotalsAutomatically Generating SQL StatementsSummary
6 Subqueries
Types of SubqueriesWriting Single Row SubqueriesSubqueries in a WHERE ClauseSubqueries in a HAVING ClauseSubqueries in a FROM Clause (Inline Views)A Couple of 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 CellsSummary
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 Database Security
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 RoleSummary
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 SequenceGetting Information on SequencesUsing a SequencePopulating a Primary Key Using a SequenceModifying a SequenceDropping a SequenceIndexesCreating an IndexCreating a Function-Based IndexGetting Information on IndexesGetting Information on the Indexes on a ColumnModifying an IndexDropping an IndexViewsCreating and Using a ViewModifying a ViewDropping a ViewSummary
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 LoopsExceptionsZERO_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 RunsSet Up for the Example TriggerCreating a TriggerFiring a TriggerGetting Information on TriggersDisabling and Enabling TriggerDropping a TriggerSummary
12 Database Objects
Introducing ObjectsCreating Object TypesUsing DESCRIBE to Get Information on Object TypesUsing Object Types to Define Column Objects and Object TablesObject References and Object IdentifiersPerforming DML on the products TableInserting Rows into the products TableSelecting Rows from the products TableUpdating a Row in the products TableDeleting a Row from the products TablePerforming DML on the object_products TableInserting Rows into the object_products TableSelecting Rows from the object_products TableUpdating a Row in the object_products TableDeleting a Row from the object_products TablePerforming DML on the object_customers TableInserting Rows into the object_customers TableSelecting Rows from the object_customers TablePerforming DML on the purchases TableInserting a Row into the purchases TableSelecting a Row from the purchases TableUpdating a Row in the purchases TableUsing Objects in PL/SQLType InheritanceNOT INSTANTIABLE Object TypesUser-Defined ConstructorsSummary
13 Collections
Introducing CollectionsVarraysCreating a Varray TypeUsing a Varray Type to Define a Column in a TableGetting Information on VarraysPopulating a Varray with ElementsSelecting Varray ElementsModifying Varray ElementsNested TablesCreating a Nested Table TypeUsing a Nested Table Type to Define a Column in a TableGetting Information on Nested TablesPopulating a Nested Table with ElementsSelecting Nested Table ElementsModifying Nested Table ElementsMultilevel Collection TypesUsing Collections in PL/SQLManipulating VarraysManipulating Nested TablesCollection MethodsOracle10g Enhancements to CollectionsAssociative ArraysChanging the Size or Precision 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 FilesUnderstanding the Large Object TypesCreating Tables Containing Large ObjectsUsing Large Objects in SQLUsing CLOBs and BLOBsUsing BFILEsUsing Large Objects in PL/SQLREAD()WRITE()APPEND()CLOSE()COMPARE()COPY()CREATETEMPORARY()ERASE()FILECLOSE()FILECLOSEALL()FILEEXISTS()FILEGETNAME()FILEISOPEN()FILEOPEN()FREETEMPORARY()GETCHUNKSIZE()GET_STORAGE_LIMIT()GETLENGTH()INSTR()ISOPEN()ISTEMPORARY()LOADFROMFILE()LOADBLOBFROMFILE()LOADCLOBFROMFILE()OPEN()SUBSTR()TRIM()WRITEAPPEND()Understanding the LONG and LONG RAW TypesThe Example TablesUsing LONG and LONG RAW ColumnsOracle Database 10g Enhancements to Large ObjectsImplicit Conversion Between CLOB and NCLOB ObjectsUse of the :new Attribute When Using LOBs in a TriggerSummary
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 the getConnection() Method of the DriverManager ClassConnecting 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 High Performance 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 Bind VariablesNon-Identical SQL StatementsDefining Identical SQL Statements Using a Bind VariableListing 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 ToolsStatspack PackageOracle Enterprise Manager Diagnostics PackAutomatic Database Diagnostic MonitoringSummary
A Oracle Data Types
Oracle SQL TypesOracle PL/SQL Types
Index

Overview

This volume will take you from novice to master, covering not only how to use SQL but also the Oracle extensions to SQL. The examples feature the sample schemas available with Oracle Database 10g, and free code will be available for download.

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

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills