Oracle SQL

Note: Supplemental materials are not guaranteed with Rental or Used book purchases.

ISBN: 9781555583231 | 1555583237
Cover: Paperback
Copyright: 9/23/2004

Buy New

Print on Demand: 2-4 Weeks. This item cannot be cancelled or returned.

$87.97
eBook

Available Instantly

Online: 1825 Days

Downloadable: Lifetime Access

*To support the delivery of the digital material to you, a digital delivery fee of $3.99 will be charged on each digital item.

$101.94*

Developers and DBAs use Oracle SQL coding on a daily basis, whether for application development, finding problems, fine-tuning solutions to those problems, or other critical DBA tasks. Oracle SQL: Jumpstart with Examples is the fastest way to get started and to quickly locate answers to common (and uncommon) questions. It includes all the basic queries: filtering, sorting, operators, conditionals, pseudocolumns, single row functions, joins, grouping and summarizing, grouping functions, subqueries, composite queries, hierarchies, flashback queries, parallel queries, expressions and regular expressions, DML, datatypes (including collections), XML in Oracle, DDL for basic database objects such as tales, views and indexes, Oracle Partitioning, security, and finally PL/SQL.

Foreword

xix

Preface

xxi

Acknowledgements

xxix

Introduction to Oracle SQL

(38)

A Little History

(7)

The Evolution of Database Modeling

(4)

The History of Relational Databases

(1)

The Evolution of Oracle Database

(2)

The Basics of Relational Data Modeling

(6)

Normalization

(2)

Referential Integrity

(1)

Denormalization

(2)

Different Forms of the Relational Data Model

(1)

Structured Query Language (SQL)

(2)

The Humble Origins of SQL

(1)

What Is Oracle SQL?

(1)

ANSI Standards and Oracle

(1)

Software Useful for Reading this Book

(1)

Syntax Conventions Used in This Book

(2)

SQL Tools

(15)

SQL*Plus in Command-Line Mode

(5)

SQL*Plus in Windows Mode

(4)

SQL*Plus Worksheet

(3)

iSQL*Plus

(3)

The MUSIC Schema

(5)

The MUSIC Schema Sales Data Warehouse

(3)

New Features of Oracle SQL

(12)

New Features in Oracle Database 10g

(7)

Oracle SQL Improvements in Oracle Database 10g

(5)

PL/SQL Improvements in Oracle Database 10g

(1)

Java Improvements in Oracle Database 10g

(1)

XML Improvements in Oracle Database 10g

(1)

Some Utility Improvements in Oracle Database 10g

(1)

Database Object Improvements in Oracle 10g

(1)

New Features in Oracle Database 9i

(5)

Oracle SQL Improvements in Oracle Database 9i

(1)

New PL/SQL Features in Oracle Database 9i

(3)

Oracle Database Architecture

(22)

The Basic Concepts

(7)

The Oracle Instance

(2)

Oracle Database Physical Architecture

(5)

Datafiles, Tablespaces, and Objects

(1)

Controlfiles, Logging, and Archiving

(1)

Rollback and Undo

(2)

Temporary Sort Space

(1)

Database Startup and Shutdown

(2)

Enhancing the Physical Architecture

(6)

Oracle Managed Files

(1)

Partitioning

(1)

Replication

(1)

Standby Databases

(1)

Clustering and Oracle RAC

(3)

The SELECT Statement

(24)

The Basic SELECT Statement

(8)

Uses of the SELECT Statement

(1)

Syntax Conventions

(2)

Some Simple Example SELECT Statements

(5)

Types of SELECT Queries

(7)

Simple Query

(1)

Filtered Query

(1)

Sorted Query

(1)

Grouping or Aggregated Query

(1)

Join Query

(1)

Subquery

(1)

Table or View Creation Query

(1)

Hierarchical Query

(2)

Composite Queries

(1)

Other Aspects of the SELECT Statement

(9)

The DUAL Table

(1)

Using Functions

(1)

Arithmetic Operations

(1)

Using DISTINCT

(1)

Null Values

(1)

Using Pseudocolumns

(3)

Filtering Rows

(12)

WHERE Clause Syntax

(4)

Some Simple WHERE Clause Examples

(3)

WHERE Clause Expression Conditions

101

(4)

Logical Operators in the WHERE Clause

105

(1)

Top-N Queries

105

(4)

Sorting Rows

109

(14)

ORDER BY Clause Syntax

109

(4)

Some Simple ORDER BY Clause Examples

111

(2)

Sorting and Null Values

113

(4)

Sorting Methods

117

(6)

Sorting by Position

117

(2)

Sorting by Expression

119

(4)

Operators, Conditions, and Pseudocolumns

123

(14)

Precedence

124

(1)

Operators

124

(7)

Arithmetic Operators

125

(1)

Logical Operators

126

(2)

The Concatenation Operator

128

(1)

Hierarchical Query Operators

128

(1)

Set Operators

129

(1)

10g Multiset Operators

129

(2)

User-Defined Operators

131

(1)

Conditions

131

(3)

Pseudocolumns

134

(3)

Using SQL*Plus

137

(38)

Environmental Settings

137

(11)

Using Scripts and Variables

148

(5)

Formatting Query Output in SQL*Plus

153

(12)

Column Formatting and Headings

154

(4)

Formatting Dates

158

(2)

Lines, Pages, and Breaks

160

(5)

Using iSQL*Plus

165

(10)

Embedding Scripts in HTML

168

(3)

iSQL*Plus versus SQL*Plus

171

(1)

Troubleshooting iSQL*Plus

171

(1)

Customizing iSQL*Plus Display

172

(3)

Single-Row Functions

175

(30)

Types of Functions

176

(1)

Single-Row Functions

176

(20)

String Functions

180

(2)

Number Functions

182

(3)

10g Binary Floating-Point Number Functions

185

(1)

Date Functions

186

(4)

Datatype Conversion Functions

190

(1)

Number Conversion Function Formats

190

(1)

Date Conversion Function Formats

191

(3)

Miscellaneous Functions

194

(2)

Combining Functions

196

(9)

Joining Tables

205

(30)

Join Formats

206

(1)

Oracle's Proprietary Format

206

(1)

ANSI Format

206

(1)

Types of Joins

207

(3)

Examining Different Types of Joins

210

(25)

Cross-Join or Cartesian Product

210

(2)

Natural or Inner Join

212

(1)

The USING clause

213

(2)

The ON clause

215

(2)

Outer Join

217

(1)

Left Outer Join

218

(5)

Right Outer Join

223

(1)

Full Outer Join

224

(1)

Self-Join

225

(1)

Grouping Self-Join

226

(2)

Hierarchical (Fishhook) Self-Join

228

(2)

Equi-Joins, Anti-Joins, and Range Joins

230

(1)

Mutable and Complex Joins

230

(5)

Grouping and Summarizing Data

235

(32)

GROUP BY Clause Syntax

235

(2)

Types of Group Functions

237

(8)

Aggregate Functions

237

(1)

Simple Summary Functions

238

(1)

Statistical Function Calculators

238

(2)

Statistical Distribution Functions

240

(1)

Ranking Functions

240

(1)

Grouping Functions

240

(1)

Enhancing Grouping Functions for Analysis

241

(1)

The OVER Clause

242

(3)

Special Grouping Function Behavior

245

(4)

Group Functions and Null Values

245

(1)

Selecting DISTINCT or ALL in Group Functions

246

(3)

Using the GROUP BY Clause

249

(11)

Grouping Rows

250

(3)

Filtering Grouped Results with the Having Clause

253

(2)

Extending the GROUP BY Clause Further

255

(1)

The ROLLUP Clause

256

(1)

The CUBE Clause

257

(1)

The Grouping Sets Clause

258

(2)

10g The Spreadsheet (Model) Clause

260

(7)

Spreadsheet (Model) Clause Syntax

261

(1)

Spreadsheet (Model) Clause Functions

261

(1)

Using the Spreadsheet (Model) Clause

262

(5)

Subqueries

267

(18)

Types of Subqueries

267

(2)

Where Can Subqueries Be Used?

269

(1)

Comparison Conditions and Subqueries

269

(1)

Demonstrating Subqueries

270

(15)

Single-Row Subqueries

270

(2)

Multiple-Row Subqueries

272

(4)

Multiple-Column Subqueries

276

(3)

Regular versus Correlated Subqueries

279

(1)

Nested Subqueries

280

(1)

Inline Views

281

(1)

Other Uses for Subqueries

282

(3)

Unusual Query Types

285

(16)

Composite Queries

285

(4)

Set Operators

285

(1)

Using Composite Queries

286

(3)

Hierarchical Queries

289

(3)

Hierarchical Query Operators

290

(1)

Hierarchical Query Pseudocolumns

290

(1)

Using Hierarchical Queries

290

(2)

Flashback and Versions Queries

292

(5)

Flashback Query Syntax

293

(1)

10g Versions Query Pseudocolumns

294

(1)

Using Flashback Queries

294

(3)

Parallel Queries

297

(4)

Expressions

301

(14)

Types of Expressions

302

(3)

10g Regular Expressions

305

(4)

Regular Expression Functions

305

(1)

Regular Expression Patterns

306

(1)

Using Regular Expressions

307

(2)

10g Oracle Expression Filter

309

(6)

Data Manipulation Language (DML)

315

(24)

What Is DML?

315

(2)

Transaction Control

317

(7)

Locks

318

(1)

The Set Transaction Command

319

(3)

The Savepoint Command

322

(2)

Adding Data (Insert)

324

(6)

Inserting One Row

325

(1)

Inserting with a Subquery

326

(1)

The Multiple-Table Insert Command

327

(3)

Changing Data (Update)

330

(4)

Updating One Row

331

(1)

Updating Many Rows

331

(3)

Deleting Data (Delete)

334

(2)

Deleting One Row

334

(1)

Deleting Many Rows

334

(2)

Deleting All Rows

336

(1)

10g Merging New and Old Data (Merge)

336

(3)

How To Use MERGE

337

(2)

Datatypes and Collections

339

(18)

Simple Datatypes

339

(3)

Complex and Object Datatypes

342

(13)

Binary Object Datatypes

342

(1)

Reference Pointer Datatypes

343

(1)

Using the REF Datatype

344

(1)

Using the BFILE Datatype

345

(2)

User-Defined Datatypes

347

(1)

Object Collection Datatypes

348

(1)

Using VARRAY Collections

349

(1)

Using Nested Table Collections

350

(2)

Object Collection Functions

352

(2)

Metadata Views

354

(1)

Special Datatypes

355

(2)

XML in Oracle

357

(26)

What Is XML?

357

(3)

What Is XSL?

360

(1)

Using XML in Oracle

360

(20)

Creating XML Documents

361

(1)

The XMLType Datatype

361

(1)

Generating XML from Tables

362

(1)

The SQL/XML Standard

363

(9)

The SYS_XMLGEN Function

372

(1)

XML and the Database

373

(1)

New XML Documents

373

(1)

Retrieving from XML Documents

374

(4)

Changing and Removing XML Document Content

378

(2)

Metadata Views

380

(3)

Tables

383

(42)

What Is a Table?

383

(4)

Types of Tables

383

(1)

Methods of Creating Tables

384

(1)

Scripted Method

385

(1)

Create Table ...As Subquery

386

(1)

Tools

386

(1)

Create Table Syntax

387

(1)

Creating Different Table Types

388

(19)

Creating Relational Tables

388

(2)

Creating Object Tables

390

(3)

Creating Temporary Tables

393

(4)

Creating Index-Organized Tables (IOTs)

397

(1)

Creating External Tables

398

(4)

Creating Partitioned Tables

402

(1)

What Are the Types of Partitions?

402

(1)

Partition Indexing

402

(1)

Create Table Partition Syntax

403

(1)

Create Table Range Partition Syntax

403

(1)

Create Table List Partition Syntax

403

(1)

Create Table Hash Partition Syntax

404

(1)

Create Table Range-Hash Partition Syntax

405

(1)

Create Table Range-List Partition Syntax

405

(2)

Changing Table Structure

407

(7)

Adding, Modifying, and Removing Columns

408

(4)

Rebuilding a Table

412

(1)

Renaming a Table

413

(1)

Dropping a Table

414

(2)

Truncating Instead of Dropping Tables

415

(1)

Adding Comments to Tables

416

(4)

Adding Comments to Schema Objects

416

(3)

Scripting and SQL Code Comments

419

(1)

10g The Recycle Bin

420

(1)

Metadata Views

421

(4)

Views

425

(22)

What Is a View?

425

(1)

Types and Uses of Views

426

(1)

Create View Syntax

427

(6)

Creating Simple Views

428

(1)

Creating Constraint Views

429

(1)

Creating Complex Views

430

(1)

Views with Joins

430

(2)

Inline Subquery Views

432

(1)

Changing and Dropping Views

433

(2)

Working with Views

435

(6)

Querying a View

435

(2)

Views and DML Commands

437

(3)

DML and Views with Joins

440

(1)

Metadata Views

441

(1)

Data Dictionary Views (Metadata)

442

(5)

Constraints

447

(24)

What Are Constraints?

448

(1)

Types and Uses of Constraints

448

(1)

Managing Constraints

449

(11)

Create Table Syntax

450

(1)

Primary Key and Unique Constraints

451

(1)

Foreign Key Constraints

452

(1)

Out-of-Line Primary and Foreign Keys

453

(3)

Check Constraints

456

(3)

REF Constraints

459

(1)

Create View Syntax

460

(1)

Adding, Modifying, and Dropping Constraints

460

(9)

ALTER TABLE Syntax

460

(1)

ALTER VIEW Syntax

461

(1)

Working with Constraints and ALTER TABLE

461

(1)

Adding a Constraint to an Existing Table

462

(1)

Modifying Constraints on Existing Tables

463

(1)

Constraint States

463

(1)

Renaming a Constraint

464

(1)

Dropping Constraints

465

(1)

Dropping Constraints with CASCADE

466

(3)

Metadata Views

469

(2)

Indexes and Clusters

471

(18)

Indexes

471

(13)

What Is an Index?

471

(3)

Types of Indexes

474

(2)

Index Attributes

476

(1)

Creating Indexes

477

(5)

Changing and Dropping Indexes

482

(1)

More Indexing Refinements

483

(1)

Clusters

484

(3)

What is a Cluster?

484

(1)

Types of Clusters

485

(1)

Creating Clusters

485

(2)

Metadata Views

487

(2)

Sequences and Synonyms

489

(14)

Sequences

489

(9)

Creating Sequences

490

(3)

Changing and Dropping Sequences

493

(1)

Using Sequences

494

(1)

Using the CURRVAL and NEXTVAL Pseudocolumns

495

(1)

Using Sequences in an INSERT Statement

496

(1)

Other Uses of Sequences

497

(1)

Synonyms

498

(3)

Creating Public Synonyms

499

(1)

Creating Private Synonyms

500

(1)

Using Synonyms

500

(1)

Metadata Views

501

(2)

Security

503

(28)

Users

503

(8)

Users Provided by Oracle

504

(1)

Creating Users

505

(3)

Modifying User Passwords

508

(2)

Dropping Users

510

(1)

Privileges

511

(11)

Granting Privileges

511

(7)

Revoking Privileges

518

(2)

Revoked System Privileges DO NOT Cascade

520

(1)

Revoked Object Privileges DO Cascade

521

(1)

Grouping Privileges Using Roles

522

(8)

Creating and Altering Roles

523

(1)

Granting and Revoking Privileges on Roles

524

(3)

Setting User Roles

527

(2)

Dropping Roles

529

(1)

Metadata Views

530

(1)

Basic PL/SQL

531

(38)

What is PL/SQL?

531

(1)

Why Is PL/SQL a Programming Language?

532

(9)

Blocks and Exception Trapping

533

(1)

Procedures, Functions, Triggers, and Packages

534

(1)

Using Named Procedures

535

(1)

Using Functions

535

(2)

Using Triggers

537

(2)

Using Packages

539

(2)

Variables and Datatypes in PL/SQL

541

(2)

Retrieving Data in PL/SQL

543

(6)

Explicit Cursors

543

(1)

Implicit Cursors

544

(1)

The Internal SQL Implicit Cursor

545

(2)

Single-Row SELECT Implicit Cursor

547

(1)

Cursor FOR Loop Implicit Cursor

547

(2)

Changing Data in PL/SQL

549

(1)

Dynamic SQL

550

(3)

Building Cursors Dynamically

552

(1)

Control Structures

553

(14)

Selection

554

(1)

The IF Statement

554

(2)

The CASE Statement

556

(1)

CASE Statement Search Condition

557

(1)

CASE Statement Selector and Expression

558

(2)

Iteration or Repetition

560

(1)

The FOR Loop

561

(3)

The WHILE Loop

564

(1)

The LOOP...END LOOP Construct

564

(1)

The FORALL Command

565

(1)

Sequence Controls

565

(1)

The GOTO Statement

566

(1)

The NULL Command

566

(1)

Objects and Methods

567

(1)

Oracle-Provided Packages

567

(1)

Metadata Views

568

(1)

Appendix A Schema Scripting

569

(54)

Appendix B Utility Scripts

623

(2)

Appendix C Sources of Information

625

(2)

Index

627

Please wait while the item is added to your bag...

FREE SHIPPING

Rent More, Save More! Use code: BBRENTAL

5% off 1 book, 7% off 2 books, 10% off 3+ books

Oracle SQL

Summary

Author Biography

Table of Contents

Supplemental Materials

FREE SHIPPING

Rent More, Save More! Use code: BBRENTAL

5% off 1 book, 7% off 2 books, 10% off 3+ books

Oracle SQL

Summary

Author Biography

Table of Contents

Supplemental Materials

Digital License