likes: pretty complete on sql statements and functions dislikes: why organize it into "data definition" and "data manipulation" sections? Why not just put all the statements in one alphabetically organized section? missing section on operator precedence (at least, accding to the index. maybe it's in there somewhere) Oracle SQL......Page 2 Table of Contents......Page 8 Programming and Data Access Languages......Page 10 The Origins of SQL......Page 12 The SQL Language......Page 13 The Commercial Development of SQL through the 1980s......Page 15 The Evolution of SQL: the 1990s and Beyond......Page 19 Standardization of the SQL Language......Page 22 SQL: A Success Story......Page 26 Preface......Page 28 Versions of Oracle......Page 29 Contents of This Book......Page 30 Conventions Used in This Book......Page 31 Comments and Questions......Page 32 Acknowledgments......Page 33 Elements of SQL......Page 36 Lexical Conventions......Page 37 Naming in SQL......Page 38 Schema Objects......Page 39 General Syntax......Page 40 Character Data......Page 41 Large Objects......Page 42 Numeric Data......Page 43 Dates......Page 44 Locators......Page 45 Pseudo- Columns......Page 46 Data Conversion......Page 48 Arithmetic Operators......Page 49 Comparison Operators......Page 50 Logical Operators......Page 53 Set Operators......Page 54 The SQL Operation......Page 55 The Target......Page 56 The Condition......Page 58 SQL Statements......Page 59 SQL DDL Statements by Task......Page 67 SQL Statement Syntax......Page 73 SQL DML and Control Statements by Task......Page 141 SQL Statement Syntax......Page 142 Common SQL Elements......Page 168 SQL Functions......Page 180 GROUP BY......Page 181 DISTINCT and ALL......Page 182 Aggregate Functions and NULL Values......Page 183 Numeric Functions......Page 188 Character Functions......Page 197 Date Functions......Page 209 Conversion Functions......Page 216 Other Functions......Page 225 Command- Line Syntax......Page 239 Keywords......Page 240 Examples......Page 242 SQL* Plus Editing Commands......Page 243 Formatting SQL* Plus Output......Page 246 Miscellaneous SQL* Plus Commands......Page 256 SQL* Plus Variables and Related Commands......Page 273 SQL* Plus System Variables......Page 276 PL/ SQL......Page 297 The Structure of PL/ SQL......Page 298 Declaring Variables......Page 300 Declaring Records......Page 304 Index- by Tables......Page 306 Index- by Table Methods......Page 308 Variable- sized Arrays......Page 310 VARRAY Array Methods......Page 311 Declaring Cursors......Page 312 Declaring Exceptions......Page 313 Execution Section......Page 315 Expressions......Page 316 Control Statements......Page 317 SQL Statements......Page 323 Executing Cursors......Page 329 Referencing Cursor Attributes......Page 332 Exception Section......Page 333 Types of Exceptions......Page 334 Exception Handling......Page 335 Exception Handling Statements......Page 336 Procedures......Page 339 Functions......Page 344 Packages......Page 348 Triggers......Page 353 SQL Statement Tuning......Page 359 Using EXPLAIN PLAN......Page 360 Creating the Plan Table......Page 361 Explaining a Query......Page 362 Viewing the Execution Plan......Page 363 Interpreting the Results......Page 365 Using OracleTMs SQL Trace Facility......Page 369 Parameters to Set......Page 370 Enabling and Disabling SQL Trace......Page 371 Finding Trace Files......Page 373 Formatting Trace Output with tkprof......Page 374 tkprof Example......Page 376 Interpreting tkprof Output......Page 377 SET AUTOTRACE......Page 380 Timers......Page 384 Rewriting Queries......Page 386 Using Optimizer Hints......Page 387 Types of Hints......Page 390 Books......Page 396 Organizations......Page 397 Web Sites......Page 398 Index......Page 400 Oracle SQL 2 Table of Contents 8 Foreword 10 SQL: A Venerable History and a Vital Future 10 Programming and Data Access Languages 10 The Origins of SQL 12 The SQL Language 13 The Commercial Development of SQL through the 1980s 15 The Evolution of SQL: the 1990s and Beyond 19 Standardization of the SQL Language 22 SQL: A Success Story 26 Preface 28 Audience for This Book 29 Versions of Oracle 29 Contents of This Book 30 Conventions Used in This Book 31 Comments and Questions 32 Acknowledgments 33 Elements of SQL 36 Lexical Conventions 37 Naming in SQL 38 Schema Objects 39 General Syntax 40 Partition Syntax 41 Datatypes 41 Character Data 41 Large Objects 42 Numeric Data 43 Dates 44 NULL 45 Locators 45 Pseudo- Columns 46 Data Conversion 48 Relational Operators 49 Arithmetic Operators 49 Concatenation Operator 50 Comparison Operators 50 Logical Operators 53 Set Operators 54 Structure of a SQL Statement 55 The SQL Operation 55 The Target 56 The Condition 58 SQL Statements 59 Data Definition Statements 67 SQL DDL Statements by Task 67 SQL Statement Syntax 73 Data Manipulation and Control Statements 141 SQL DML and Control Statements by Task 141 SQL Statement Syntax 142 Common SQL Elements 168 SQL Functions 180 Aggregate Functions 181 GROUP BY 181 DISTINCT and ALL 182 Aggregate Functions and NULL Values 183 Numeric Functions 188 Character Functions 197 Date Functions 209 Conversion Functions 216 Other Functions 225 SQL* Plus 239 Command- Line Syntax 239 Keywords 240 Examples 242 SQL* Plus Editing Commands 243 Formatting SQL* Plus Output 246 Miscellaneous SQL* Plus Commands 256 SQL* Plus Variables and Related Commands 273 SQL* Plus System Variables 276 PL/ SQL 297 The Structure of PL/ SQL 298 Block Header 300 Declaration Section 300 Declaring Variables 300 Declaring Records 304 Index- by Tables 306 Index- by Table Methods 308 Variable- sized Arrays 310 VARRAY Array Methods 311 Declaring Cursors 312 Declaring Exceptions 313 Execution Section 315 Assignment Statements 316 Expressions 316 Control Statements 317 SQL Statements 323 Executing Cursors 329 Referencing Cursor Attributes 332 Exception Section 333 Types of Exceptions 334 Exception Handling 335 Exception Handling Statements 336 Procedures and Packages 339 Procedures 339 Functions 344 Packages 348 Triggers 353 SQL Statement Tuning 359 Using EXPLAIN PLAN 360 Creating the Plan Table 361 Explaining a Query 362 Viewing the Execution Plan 363 Interpreting the Results 365 Using OracleTMs SQL Trace Facility 369 Parameters to Set 370 Enabling and Disabling SQL Trace 371 Finding Trace Files 373 Formatting Trace Output with tkprof 374 tkprof Example 376 Interpreting tkprof Output 377 SQL* Plus Tuning Aids 380 SET AUTOTRACE 380 Timers 384 Improving Query Performance 386 Modifying Database Structure 386 Gathering Statistics 386 Rewriting Queries 386 Using Optimizer Hints 387 Types of Hints 390 SQL Resources 396 Books 396 Other Publications 397 Organizations 397 Web Sites 398 Index 400
SQL (Structured Query Language) is the heart of a relational database management system. It's the language used to query the database, to create new tables in the database, to update and delete database fields, and to set privileges in the database. Oracle SQL: The Essential Reference is for everyone who needs to access an Oracle database using SQL-developers, DBAs, designers, and managers.
SQL is based on research dating back to the late 1960s, but its first commercial release was in the RDBMS announced by the fledgling Oracle Corporation in 1979. Since that time, every other database vendor has adopted SQL, and ANSI and the ISO have made it a standard. Although vendors diverge in their extensions to SQL, the core language is standard across vendor boundaries.
Despite SQL's long history and relative simplicity, few developers and database administrators are truly masters of SQL. The constant stream of vendor enhancements, the hard-won experience in tuning SQL for best performance, and the requirements of particular operational environments mean that there is always more to learn about SQL.
Oracle SQL: The Essential Reference delivers all the information needed to keep ahead of the learning curve on standard SQL and Oracle's extensions to it. This single, concise reference volume will hold its own against a stack of Oracle manuals and even yield insights and examples not available in those manuals.
There are chapters on basic SQL elements (naming requirements, column types, pseudo-types, data conversion rules, operators); Data Definition Language (DDL) and Data Manipulation Language (DML); common language elements (constraints, storage clause, predicates); SQL functions; PL/SQL (including procedures, functions, and packages); SQL*Plus, and Oracle SQL optimization and tuning. The book covers Oracle 8i, release 8.1.6.
SQL (Structured Query Language) is the heart of a relational database management system. It's the language used to query the database, to create new tables in the database, to update and delete database fields, and to set privileges in the database. Oracle SQL: The Essential Reference is for everyone who needs to access an Oracle database using SQL--developers, DBAs, designers, and managers. SQL is based on research dating back to the late 1960s, but its first commercial release was in the RDBMS announced by the fledgling Oracle Corporation in 1979. Since that time, every other database vendor has adopted SQL, and ANSI and the ISO have made it a standard. Although vendors diverge in their extensions to SQL, the core language is standard across vendor boundaries. Despite SQL's long history and relative simplicity, few developers and database administrators are truly masters of SQL. The constant stream of vendor enhancements, the hard-won experience in tuning SQL for best performance, and the requirements of particular operational environments mean that there is always more to learn about SQL. Oracle SQL: The Essential Reference delivers all the information needed to keep ahead of the learning curve on standard SQL and Oracle's extensions to it. This single, concise reference volume will hold its own against a stack of Oracle manuals and even yield insights and examples not available in those manuals. There are chapters on basic SQL elements (naming requirements, column types, pseudo-types, data conversion rules, operators); Data Definition Language (DDL) and Data Manipulation Language (DML); common language elements (constraints, storage clause, predicates); SQL functions; PL/SQL (including procedures, functions, and packages); SQL*Plus, and Oracle SQL optimization and tuning. The book covers Oracle 8 i, release 8.1.6. "Oracle SQL: The Essential Reference is aimed at everyone who needs to access an Oracle database using SQL - developers, DBAs, designers, and general users. It contains all the information you'll need to know about standard SQL, and Oracle's extensions to it, in a single, concise reference volume. It's crammed full of quick-reference tables, performance hints, and summaries of statements, datatypes, and other useful SQL-related information (as well as the SQL Plus and PL/SQL information that SQL practitioners typically need to know), all packaged in an accessible format Covering standard SQL (Structured Query Language) and Oracle's extensions to it, this reference volume investigates basic SQL elements, Data Definition Language (DDL) and Data Manipulation Language (DML), SQL functions, PL/SQL, SQL*Plus, and Oracle SQL optimization and tuning.
