Sumário Itens Encontrados: 40613 Unusual Query Types 28512.4.7 Other Uses for Subqueries 28212.4.6 Inline Views 28112.4.5 Nested Subqueries 28012.4.4 Regular versus Correlated Subqueries 27912.4.3 Multiple-Column Subqueries 27612.4.2 Multiple-Row Subqueries 27212.4.1 Single-Row Subqueries 27012.4 Demonstrating Subqueries 27012.3 Comparison Conditions and Subqueries 26912.2 Where Can Subqueries Be Used? 26912.1 Types of Subqueries 26712 Subqueries 26711.5.3 Using the SPREADSHEET (MODEL) Clause 26211.5.2 SPREADSHEET (MODEL) Clause Functions 26111.5.1 SPREADSHEET (MODEL) Clause Syntax 26111.5 The SPREADSHEET (MODEL) Clause 26011.4.3.3 The GROUPING SETS Clause 25811.4.3.2 The CUBE Clause 25711.4.3.1 The ROLLUP Clause 25611.4.3 Extending the GROUP BY Clause Further 25511.4.2 Filtering Grouped Results with the Having Clause 25311.4.1 Grouping Rows 25011.4 Using the GROUP BY Clause 24911.3.2 Selecting DISTINCT or ALL in Group Functions 24611.3.1 Group Functions and Null Values 24511.3 Special Grouping Function Behavior 24511.2.2.1 The OVER Clause 24211.2.2 Enhancing Grouping Functions for Analysis 24111.2.1.5 Grouping Functions 24011.2.1.4 Ranking Functions 24011.2.1.3 Statistical Distribution Functions 24011.2.1.2 Statistical Function Calculators 23811.2.1.1 Simple Summary Functions 23811.2.1 Aggregate Functions 23711.2 Types of Group Functions 23711.1 GROUP BY Clause Syntax 23511 Grouping and Summarizing Data 23510.3.6 Mutable and Complex Joins 23010.3.5 Equi-Joins, Anti-Joins, and Range Joins 23010.3.4.2 Hierarchical (Fishhook) Self-Join 22810.3.4.1 Grouping Self-Join 22610.3.4 Self-Join 22510.3.3.3 Full Outer Join 22410.3.3.2 Right Outer Join 22310.3.3.1 Left Outer Join 21810.3.3 Outer Join 21710.3.2.2 The ON clause 21510.3.2.1 The USING clause 21310.3.2 Natural or Inner Join 21210.3.1 Cross-Join or Cartesian Product 21010.3 Examining Different Types of Joins 21010.2 Types of Joins 20710.1.2 ANSI Format 20610.1.1 Oracleâs Proprietary Format 20610.1 Join Formats 20610 Joining Tables 2059.3 Combining Functions 1969.2.5 Miscellaneous Functions 1949.2.4.2 Date Conversion Function Formats 1919.2.4.1 Number Conversion Function Formats 1909.2.4 Datatype Conversion Functions 1909.2.3 Date Functions 1869.2.2.1 Binary Floating-Point Number Functions 1859.2.2 Number Functions 1829.2.1 String Functions 1809.2 Single-Row Functions 1769.1 Types of Functions 1769 Single-Row Functions 1758.4.4 Customizing iSQL*Plus Display 1728.4.3 Troubleshooting iSQL*Plus 1718.4.2 iSQL*Plus versus SQL*Plus 1718.4.1 Embedding Scripts in HTML 1688.4 Using iSQL*Plus 1658.3.2 Lines, Pages, and Breaks 1608.3.1.1 Formatting Dates 1588.3.1 Column Formatting and Headings 1548.3 Formatting Query Output in SQL*Plus 1538.2 Using Scripts and Variables 1488.1 Environmental Settings 1378 Using SQL*Plus 1377.4 Pseudocolumns 1347.3 Conditions 1317.2.7 User-Defined Operators 1317.2.6 Multiset Operators 1297.2.5 Set Operators 1297.2.4 Hierarchical Query Operators 1287.2.3 The Concatenation Operator 1287.2.2 Logical Operators 1267.2.1 Arithmetic Operators 1257.2 Operators 1247.1 Precedence 1247 Operators, Conditions, and Pseudocolumns 1236.3.2 Sorting by Expression 1196.3.1 Sorting by Position 1176.3 Sorting Methods 1176.2 Sorting and Null Values 1136.1.1 Some Simple ORDER BY Clause Examples 1116.1 ORDER BY Clause Syntax 1096 Sorting Rows 1095.4 Top-N Queries 1055.3 Logical Operators in the WHERE Clause 1055.2 WHERE Clause Expression Conditions 1015.1.1 Some Simple WHERE Clause Examples 985.1 WHERE Clause Syntax 975 Filtering Rows 974.3.6 Using Pseudocolumns 944.3.5 Null Values 934.3.4 Using DISTINCT 924.3.3 Arithmetic Operations 914.3.2 Using Functions 904.3.1 The DUAL Table 894.3 Other Aspects of the SELECT Statement 884.2.9 Composite Queries 884.2.8 Hierarchical Query 864.2.7 Table or View Creation Query 854.2.6 Subquery 854.2.5 Join Query 844.2.4 Grouping or Aggregated Query 834.2.3 Sorted Query 834.2.2 Filtered Query 824.2.1 Simple Query 824.2 Types of SELECT Queries 814.1.3 Some Simple Example SELECT Statements 764.1.2 Syntax Conventions 744.1.1 Uses of the SELECT Statement 734.1 The Basic SELECT Statement 734 The SELECT Statement 733.5.5 Clustering and Oracle RAC 703.5.4 Standby Databases 693.5.3 Replication 683.5.2 Partitioning 673.5.1 Oracle Managed Files 673.5 Enhancing the Physical Architecture 673.4 Database Startup and Shutdown 653.3.4 Temporary Sort Space 643.3.3 Rollback and Undo 623.3.2 Controlfiles, Logging, and Archiving 613.3.1 Datafiles, Tablespaces, and Objects 603.3 Oracle Database Physical Architecture 603.2 The Oracle Instance 583.1 The Basic Concepts 513 Oracle Database Architecture 512.2.2 New PL/SQL Features in Oracle Database 9i 482.2.1 Oracle SQL Improvements in Oracle Database 9i 472.2 New Features in Oracle Database 9i 462.1.5 Database Object Improvements in Oracle 10g 46Database 10g 452.1.4 Some Utility Improvements in Oracle2.1.3 XML Improvements in Oracle Database 10g 452.1.2.1 Java Improvements in Oracle Database 10g 452.1.2 PL/SQL Improvements in Oracle Database 10g 442.1.1 Oracle SQL Improvements in Oracle Database 10g 392.1 New Features in Oracle Database 10g 392 New Features of Oracle SQL 391.7.1 The MUSIC Schema Sales Data Warehouse 361.7 The MUSIC Schema 341.6.4 iSQL*Plus 311.6.3 SQL*Plus Worksheet 281.6.2 SQL*Plus in Windows Mode 241.6.1 SQL*Plus in Command-Line Mode 191.6 SQL Tools 191.5 Syntax Conventions Used in This Book 171.4 Software Useful for Reading this Book 161.3.2.1 ANSI Standards and Oracle 151.3.2 What Is Oracle SQL? 151.3.1 The Humble Origins of SQL 141.3 Structured Query Language (SQL) 141.2.3 Different Forms of the Relational Data Model 131.2.2 Denormalization 111.2.1.1 Referential Integrity 101.2.1 Normalization 81.2 The Basics of Relational Data Modeling 81.1.3 The Evolution of Oracle Database 61.1.2 The History of Relational Databases 51.1.1 The Evolution of Database Modeling 11.1 A Little History 11 Introduction to Oracle SQL 1viiIndex 627Appendix C 625Appendix B 623Appendix A 56924 Basic PL/SQL 53123 Security 50322 Sequences and Synonyms 48921 Indexes and Clusters 47120 Constraints 44719 Views 42518 Tables 38317 XML in Oracle 35716 Datatypes and Collections 33915 Data Manipulation Language (DML) 31514 Expressions 30113.1 Composite Queries 28513.1.1 Set Operators 28513.1.2 Using Composite Queries 28613.2 Hierarchical Queries 28913.2.1 Hierarchical Query Operators 29013.2.2 Hierarchical Query Pseudocolumns 29013.2.3 Using Hierarchical Queries 29013.3 Flashback and Versions Queries 29213.3.1 Flashback Query Syntax 29313.3.2 Versions Query Pseudocolumns 29413.3.3 Using Flashback Queries 29413.4 Parallel Queries 29714.1 Types of Expressions 30214.2 Regular Expressions 30514.2.1 Regular Expression Functions 30514.2.2 Regular Expression Patterns 30614.2.3 Using Regular Expressions 30714.3 Oracle Expression Filter 30915.1 What Is DML? 31515.2 Transaction Control 31715.2.1 Locks 31815.2.2 The SET TRANSACTION Command 31915.2.3 The SAVEPOINT Command 32215.3 Adding Data (INSERT) 32415.3.1 Inserting One Row 32515.3.2 Inserting with a Subquery 32615.3.3 The Multiple-Table INSERT Command 32715.4 Changing Data (UPDATE) 33015.4.1 Updating One Row 33115.4.2 Updating Many Rows 33115.5 Deleting Data (DELETE) 33415.5.1 Deleting One Row 33415.5.2 Deleting Many Rows 33415.5.3 Deleting All Rows 33615.6 Merging New and Old Data (MERGE) 33615.6.1 How To Use MERGE 33716.1 Simple Datatypes 33916.2 Complex and Object Datatypes 34216.2.1 Binary Object Datatypes 34216.2.2 Reference Pointer Datatypes 34316.2.2.1 Using the REF Datatype 34416.2.2.2 Using the BFILE Datatype 34516.2.3 User-Defined Datatypes 34716.2.4 Object Collection Datatypes 34816.2.4.1 Using VARRAY Collections 34916.2.4.2 Using Nested Table Collections 35016.2.5 Object Collection Functions 35216.2.6 Metadata Views 35416.3 Special Datatypes 35517.1 What Is XML? 35717.1.1 What Is XSL? 36017.2 Using XML in Oracle 36017.2.1 Creating XML Documents 36117.2.1.1 The XMLType Datatype 36117.2.1.2 Generating XML from Tables 362The SQL/XML Standard 363The SYS_XMLGEN Function 37217.2.2 XML and the Database 37317.2.2.1 New XML Documents 37317.2.2.2 Retrieving from XML Documents 37417.2.2.3 Changing and Removing XML Document Content 37817.3 Metadata Views 38018.1 What Is a Table? 38318.1.1 Types of Tables 38318.1.2 Methods of Creating Tables 38418.1.2.1 Scripted Method 38518.1.2.2 CREATE TABLE AS Subquery 38618.1.2.3 Tools 38618.2 CREATE TABLE Syntax 38718.3 Creating Different Table Types 38818.3.1 Creating Relational Tables 38818.3.2 Creating Object Tables 39018.3.3 Creating Temporary Tables 39318.3.4 Creating Index-Organized Tables (IOTs) 39718.3.5 Creating External Tables 39818.3.6 Creating Partitioned Tables 40218.3.6.1 What Are the Types of Partitions? 402Partition Indexing 40218.3.6.2 CREATE TABLE Partition Syntax 403CREATE TABLE Range Partition Syntax 403CREATE TABLE List Partition Syntax 403CREATE TABLE Hash Partition Syntax 404CREATE TABLE Range-Hash Partition Syntax 405CREATE TABLE Range-List Partition Syntax 40518.4 Changing Table Structure 40718.4.1 Adding, Modifying, and Removing Columns 40818.4.2 Rebuilding a Table 41218.4.3 Renaming a Table 41318.5 Dropping a Table 41418.5.1 Truncating Instead of Dropping Tables 41518.6 Adding Comments to Tables 41618.6.1 Adding Comments to Schema Objects 41618.6.2 Scripting and SQL Code Comments 41918.7 The Recycle Bin 42018.8 Metadata Views 42119.1 What Is a View? 42519.2 Types and Uses of Views 42619.3 CREATE VIEW Syntax 42719.3.1 Creating Simple Views 42819.3.2 Creating Constraint Views 42919.3.3 Creating Complex Views 43019.3.3.1 Views with Joins 43019.3.3.2 Inline Subquery Views 43219.4 Changing and Dropping Views 43319.5 Working with Views 43519.5.1 Querying a View 43519.5.2 Views and DML Commands 43719.5.2.1 DML and Views with Joins 44019.6 Metadata Views 44119.7 Data Dictionary Views (Metadata) 44220.1 What Are Constraints? 44820.1.1 Types and Uses of Constraints 44820.2 Managing Constraints 44920.2.1 CREATE TABLE Syntax 45020.2.1.1 Primary Key and Unique Constraints 45120.2.1.2 Foreign Key Constraints 452Out-of-Line Primary and Foreign Keys 45320.2.1.3 Check Constraints 45620.2.1.4 REF Constraints 45920.2.2 CREATE VIEW Syntax 46020.3 Adding, Modifying, and Dropping Constraints 46020.3.1 ALTER TABLE Syntax 46020.3.2 ALTER VIEW Syntax 46120.3.3 Working with Constraints and ALTER TABLE 46120.3.3.1 Adding a Constraint to an Existing Table 46220.3.3.2 Modifying Constraints on Existing Tables 46320.3.3.3 Constraint States 46320.3.4 Renaming a Constraint 46420.3.5 Dropping Constraints 46520.3.5.1 Dropping Constraints with CASCADE 46620.4 Metadata Views 46921.1 Indexes 47121.1.1 What Is an Index? 47121.1.2 Types of Indexes 47421.1.2.1 Index Attributes 47621.1.3 Creating Indexes 47721.1.4 Changing and Dropping Indexes 48221.1.5 More Indexing Refinements 48321.2 Clusters 48421.2.1 What is a Cluster? 48421.2.2 Types of Clusters 48521.2.3 Creating Clusters 48521.3 Metadata Views 48722.1 Sequences 48922.1.1 Creating Sequences 49022.1.2 Changing and Dropping Sequences 49322.1.3 Using Sequences 49422.1.3.1 Using the CURRVAL and NEXTVAL Pseudocolumns 49522.1.3.2 Using Sequences in an INSERT Statement 49622.1.3.3 Other Uses of Sequences 49722.2 Synonyms 49822.2.1 Creating Public Synonyms 49922.2.2 Creating Private Synonyms 50022.2.3 Using Synonyms 50022.3 Metadata Views 50123.1 Users 50323.1.1 Users Provided by Oracle 50423.1.2 Creating Users 50523.1.3 Modifying User Passwords 50823.1.4 Dropping Users 51023.2 Privileges 51123.2.1 Granting Privileges 51123.2.2 Revoking Privileges 51823.2.2.1 Revoked System Privileges DO NOT Cascade 52023.2.2.2 Revoked Object Privileges DO Cascade 52123.3 Grouping Privileges Using Roles 52223.3.1 Creating and Altering Roles 52323.3.2 Granting and Revoking Privileges on Roles 52423.3.3 Setting User Roles 52723.3.4 Dropping Roles 52923.4 Metadata Views 53024.1 What is PL/SQL? 53124.2 Why Is PL/SQL a Programming Language? 53224.2.1 Blocks and Exception Trapping 53324.2.2 Procedures, Functions, Triggers, and Packages 53424.2.2.1 Using Named Procedures 53524.2.2.2 Using Functions 53524.2.2.3 Using Triggers 53724.2.2.4 Using Packages 53924.3 Variables and Datatypes in PL/SQL 54124.4 Retrieving Data in PL/SQL 54324.4.1 Explicit Cursors 54324.4.2 Implicit Cursors 54424.4.2.1 The Internal SQL Implicit Cursor 54524.4.2.2 Single-Row SELECT Implicit Cursor 54724.4.2.3 Cursor FOR Loop Implicit Cursor 54724.5 Changing Data in PL/SQL 54924.6 Dynamic SQL 55024.6.1 Building Cursors Dynamically 55224.7 Control Structures 55324.7.1 Selection 55424.7.1.1 The IF Statement 55424.7.1.2 The CASE Statement 556CASE Statement Search Condition 557CASE Statement Selector and Expression 55824.7.2 Iteration or Repetition 56024.7.2.1 The FOR Loop 56124.7.2.2 The WHILE Loop 56424.7.2.3 The LOOP .END LOOP Construct 56424.7.2.4 The FORALL Command 56524.7.3 Sequence Controls 56524.7.3.1 The GOTO Statement 56624.7.3.2 The NULL Command 56624.8 Objects and Methods 56724.9 Oracle-Provided Packages 56724.10 Metadata Views 568Appendix ASchema Scripting 569Appendix BUtility Scripts 623Appendix CSources of Information 625