Oracle SQL Jumpstart with Examples- P1

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

123
lượt xem 17
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle SQL Jumpstart with Examples- P1: Review "As a consultant with more than 12 years of experience working with Oracle databases on a daily basis, reviewing this book was a unique and enjoyable experience. The SQL language is without doubt one of the most critical database skills and it is best learned by example. This book addresses that crucial need. Mr. Powell does an excellent job of clarifying the concepts by using meaningful and easy to understand examples. Frankly, I have not come across any other book on SQL that is as good a compilation of SQL concepts in a single...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Oracle SQL Jumpstart with Examples- P1

Please purchase PDF Split-Merge on www.verypdf.com to remo
Oracle SQL Jumpstart with Examples Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
This page intentionally left blank Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Oracle SQL Jumpstart with Examples Gavin Powell Carol McCullough-Dieter AMSTERDAM • BOSTON • HEIDELBERG • LONDON NEW YORK • OXFORD • PARIS • SAN DIEGO• SAN FRANCISCO • SINGAPORE • SYDNEY • TOKYO Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Elsevier Digital Press 200 Wheeler Road, Burlington, MA 01803, USA Linacre House, Jordan Hill, Oxford OX2 8DP, UK Copyright © 2005, Elsevier Inc. All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the prior written permission of the publisher. Permissions may be sought directly from Elsevier’s Science & Technology Rights Department in Oxford, UK: phone: (+44) 1865 843830, fax: (+44) 1865 853333, e-mail: permissions@elsevier.com.uk. You may also complete your request on-line via the Elsevier homepage (http://elsevier.com), by selecting “Customer Support” and then “Obtaining Permissions.” Recognizing the importance of preserving what has been written, Elsevier prints its books on acid-free paper whenever possible. Library of Congress Cataloging-in-Publication Data Application submitted. ISBN: 1-55558-323-7 British Library Cataloguing-in-Publication Data A catalogue record for this book is available from the British Library. For information on all Digital Press publications visit our Web site at www.digitalpress.com and www.bh.com/digitalpress 04 05 06 07 08 09 10 9 8 7 6 5 4 3 2 1 Printed in the United States of America Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Contents at a Glance Foreword xix Preface xxi Acknowledgements xxix 1 Introduction to Oracle SQL 1 2 New Features of Oracle SQL 39 3 Oracle Database Architecture 51 4 The SELECT Statement 73 5 Filtering Rows 97 6 Sorting Rows 109 7 Operators, Conditions, and Pseudocolumns 123 8 Using SQL*Plus 137 9 Single-Row Functions 175 10 Joining Tables 205 11 Grouping and Summarizing Data 235 12 Subqueries 267 13 Unusual Query Types 285 14 Expressions 301 15 Data Manipulation Language (DML) 315 16 Datatypes and Collections 339 v Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
vi Contents at a Glance 17 XML in Oracle 357 18 Tables 383 19 Views 425 20 Constraints 447 21 Indexes and Clusters 471 22 Sequences and Synonyms 489 23 Security 503 24 Basic PL/SQL 531 Appendix A 569 Appendix B 623 Appendix C 625 Index 627 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Table of Contents Foreword xix Preface xxi Acknowledgements xxix 1 Introduction to Oracle SQL 1 1.1 A Little History 1 1.1.1 The Evolution of Database Modeling 1 1.1.2 The History of Relational Databases 5 1.1.3 The Evolution of Oracle Database 6 1.2 The Basics of Relational Data Modeling 8 1.2.1 Normalization 8 1.2.1.1 Referential Integrity 10 1.2.2 Denormalization 11 1.2.3 Different Forms of the Relational Data Model 13 1.3 Structured Query Language (SQL) 14 1.3.1 The Humble Origins of SQL 14 1.3.2 What Is Oracle SQL? 15 1.3.2.1 ANSI Standards and Oracle 15 1.4 Software Useful for Reading this Book 16 1.5 Syntax Conventions Used in This Book 17 1.6 SQL Tools 19 1.6.1 SQL*Plus in Command-Line Mode 19 1.6.2 SQL*Plus in Windows Mode 24 1.6.3 SQL*Plus Worksheet 28 1.6.4 iSQL*Plus 31 1.7 The MUSIC Schema 34 1.7.1 The MUSIC Schema Sales Data Warehouse 36 vii Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
viii Table of Contents 2 New Features of Oracle SQL 39 2.1 New Features in Oracle Database 10g 39 2.1.1 Oracle SQL Improvements in Oracle Database 10g 39 2.1.2 PL/SQL Improvements in Oracle Database 10g 44 2.1.2.1 Java Improvements in Oracle Database 10g 45 2.1.3 XML Improvements in Oracle Database 10g 45 2.1.4 Some Utility Improvements in Oracle Database 10g 45 2.1.5 Database Object Improvements in Oracle 10g 46 2.2 New Features in Oracle Database 9i 46 2.2.1 Oracle SQL Improvements in Oracle Database 9i 47 2.2.2 New PL/SQL Features in Oracle Database 9i 48 3 Oracle Database Architecture 51 3.1 The Basic Concepts 51 3.2 The Oracle Instance 58 3.3 Oracle Database Physical Architecture 60 3.3.1 Dataﬁles, Tablespaces, and Objects 60 3.3.2 Controlﬁles, Logging, and Archiving 61 3.3.3 Rollback and Undo 62 3.3.4 Temporary Sort Space 64 3.4 Database Startup and Shutdown 65 3.5 Enhancing the Physical Architecture 67 3.5.1 Oracle Managed Files 67 3.5.2 Partitioning 67 3.5.3 Replication 68 3.5.4 Standby Databases 69 3.5.5 Clustering and Oracle RAC 70 4 The SELECT Statement 73 4.1 The Basic SELECT Statement 73 4.1.1 Uses of the SELECT Statement 73 4.1.2 Syntax Conventions 74 4.1.3 Some Simple Example SELECT Statements 76 4.2 Types of SELECT Queries 81 4.2.1 Simple Query 82 4.2.2 Filtered Query 82 4.2.3 Sorted Query 83 4.2.4 Grouping or Aggregated Query 83 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Table of Contents ix 4.2.5 Join Query 84 4.2.6 Subquery 85 4.2.7 Table or View Creation Query 85 4.2.8 Hierarchical Query 86 4.2.9 Composite Queries 88 4.3 Other Aspects of the SELECT Statement 88 4.3.1 The DUAL Table 89 4.3.2 Using Functions 90 4.3.3 Arithmetic Operations 91 4.3.4 Using DISTINCT 92 4.3.5 Null Values 93 4.3.6 Using Pseudocolumns 94 5 Filtering Rows 97 5.1 WHERE Clause Syntax 97 5.1.1 Some Simple WHERE Clause Examples 98 5.2 WHERE Clause Expression Conditions 101 5.3 Logical Operators in the WHERE Clause 105 5.4 Top-N Queries 105 6 Sorting Rows 109 6.1 ORDER BY Clause Syntax 109 6.1.1 Some Simple ORDER BY Clause Examples 111 6.2 Sorting and Null Values 113 6.3 Sorting Methods 117 6.3.1 Sorting by Position 117 6.3.2 Sorting by Expression 119 7 Operators, Conditions, and Pseudocolumns 123 7.1 Precedence 124 7.2 Operators 124 7.2.1 Arithmetic Operators 125 7.2.2 Logical Operators 126 7.2.3 The Concatenation Operator 128 7.2.4 Hierarchical Query Operators 128 7.2.5 Set Operators 129 7.2.6 Multiset Operators 129 7.2.7 User-Deﬁned Operators 131 7.3 Conditions 131 7.4 Pseudocolumns 134 Contents Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
x Table of Contents 8 Using SQL*Plus 137 8.1 Environmental Settings 137 8.2 Using Scripts and Variables 148 8.3 Formatting Query Output in SQL*Plus 153 8.3.1 Column Formatting and Headings 154 8.3.1.1 Formatting Dates 158 8.3.2 Lines, Pages, and Breaks 160 8.4 Using iSQL*Plus 165 8.4.1 Embedding Scripts in HTML 168 8.4.2 iSQL*Plus versus SQL*Plus 171 8.4.3 Troubleshooting iSQL*Plus 171 8.4.4 Customizing iSQL*Plus Display 172 9 Single-Row Functions 175 9.1 Types of Functions 176 9.2 Single-Row Functions 176 9.2.1 String Functions 180 9.2.2 Number Functions 182 9.2.2.1 Binary Floating-Point Number Functions 185 9.2.3 Date Functions 186 9.2.4 Datatype Conversion Functions 190 9.2.4.1 Number Conversion Function Formats 190 9.2.4.2 Date Conversion Function Formats 191 9.2.5 Miscellaneous Functions 194 9.3 Combining Functions 196 10 Joining Tables 205 10.1 Join Formats 206 10.1.1 Oracle’s Proprietary Format 206 10.1.2 ANSI Format 206 10.2 Types of Joins 207 10.3 Examining Different Types of Joins 210 10.3.1 Cross-Join or Cartesian Product 210 10.3.2 Natural or Inner Join 212 10.3.2.1 The USING clause 213 10.3.2.2 The ON clause 215 10.3.3 Outer Join 217 10.3.3.1 Left Outer Join 218 10.3.3.2 Right Outer Join 223 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Table of Contents xi 10.3.3.3 Full Outer Join 224 10.3.4 Self-Join 225 10.3.4.1 Grouping Self-Join 226 10.3.4.2 Hierarchical (Fishhook) Self-Join 228 10.3.5 Equi-Joins, Anti-Joins, and Range Joins 230 10.3.6 Mutable and Complex Joins 230 11 Grouping and Summarizing Data 235 11.1 GROUP BY Clause Syntax 235 11.2 Types of Group Functions 237 11.2.1 Aggregate Functions 237 11.2.1.1 Simple Summary Functions 238 11.2.1.2 Statistical Function Calculators 238 11.2.1.3 Statistical Distribution Functions 240 11.2.1.4 Ranking Functions 240 11.2.1.5 Grouping Functions 240 11.2.2 Enhancing Grouping Functions for Analysis 241 11.2.2.1 The OVER Clause 242 11.3 Special Grouping Function Behavior 245 11.3.1 Group Functions and Null Values 245 11.3.2 Selecting DISTINCT or ALL in Group Functions 246 11.4 Using the GROUP BY Clause 249 11.4.1 Grouping Rows 250 11.4.2 Filtering Grouped Results with the Having Clause 253 11.4.3 Extending the GROUP BY Clause Further 255 11.4.3.1 The ROLLUP Clause 256 11.4.3.2 The CUBE Clause 257 11.4.3.3 The GROUPING SETS Clause 258 11.5 The SPREADSHEET (MODEL) Clause 260 11.5.1 SPREADSHEET (MODEL) Clause Syntax 261 11.5.2 SPREADSHEET (MODEL) Clause Functions 261 11.5.3 Using the SPREADSHEET (MODEL) Clause 262 12 Subqueries 267 12.1 Types of Subqueries 267 12.2 Where Can Subqueries Be Used? 269 12.3 Comparison Conditions and Subqueries 269 12.4 Demonstrating Subqueries 270 12.4.1 Single-Row Subqueries 270 12.4.2 Multiple-Row Subqueries 272 Contents Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
xii Table of Contents 12.4.3 Multiple-Column Subqueries 276 12.4.4 Regular versus Correlated Subqueries 279 12.4.5 Nested Subqueries 280 12.4.6 Inline Views 281 12.4.7 Other Uses for Subqueries 282 13 Unusual Query Types 285 13.1 Composite Queries 285 13.1.1 Set Operators 285 13.1.2 Using Composite Queries 286 13.2 Hierarchical Queries 289 13.2.1 Hierarchical Query Operators 290 13.2.2 Hierarchical Query Pseudocolumns 290 13.2.3 Using Hierarchical Queries 290 13.3 Flashback and Versions Queries 292 13.3.1 Flashback Query Syntax 293 13.3.2 Versions Query Pseudocolumns 294 13.3.3 Using Flashback Queries 294 13.4 Parallel Queries 297 14 Expressions 301 14.1 Types of Expressions 302 14.2 Regular Expressions 305 14.2.1 Regular Expression Functions 305 14.2.2 Regular Expression Patterns 306 14.2.3 Using Regular Expressions 307 14.3 Oracle Expression Filter 309 15 Data Manipulation Language (DML) 315 15.1 What Is DML? 315 15.2 Transaction Control 317 15.2.1 Locks 318 15.2.2 The SET TRANSACTION Command 319 15.2.3 The SAVEPOINT Command 322 15.3 Adding Data (INSERT) 324 15.3.1 Inserting One Row 325 15.3.2 Inserting with a Subquery 326 15.3.3 The Multiple-Table INSERT Command 327 15.4 Changing Data (UPDATE) 330 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Table of Contents xiii 15.4.1 Updating One Row 331 15.4.2 Updating Many Rows 331 15.5 Deleting Data (DELETE) 334 15.5.1 Deleting One Row 334 15.5.2 Deleting Many Rows 334 15.5.3 Deleting All Rows 336 15.6 Merging New and Old Data (MERGE) 336 15.6.1 How To Use MERGE 337 16 Datatypes and Collections 339 16.1 Simple Datatypes 339 16.2 Complex and Object Datatypes 342 16.2.1 Binary Object Datatypes 342 16.2.2 Reference Pointer Datatypes 343 16.2.2.1 Using the REF Datatype 344 16.2.2.2 Using the BFILE Datatype 345 16.2.3 User-Deﬁned Datatypes 347 16.2.4 Object Collection Datatypes 348 16.2.4.1 Using VARRAY Collections 349 16.2.4.2 Using Nested Table Collections 350 16.2.5 Object Collection Functions 352 16.2.6 Metadata Views 354 16.3 Special Datatypes 355 17 XML in Oracle 357 17.1 What Is XML? 357 17.1.1 What Is XSL? 360 17.2 Using XML in Oracle 360 17.2.1 Creating XML Documents 361 17.2.1.1 The XMLType Datatype 361 17.2.1.2 Generating XML from Tables 362 The SQL/XML Standard 363 The SYS_XMLGEN Function 372 17.2.2 XML and the Database 373 17.2.2.1 New XML Documents 373 17.2.2.2 Retrieving from XML Documents 374 17.2.2.3 Changing and Removing XML Document Content 378 17.3 Metadata Views 380 Contents Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
xiv Table of Contents 18 Tables 383 18.1 What Is a Table? 383 18.1.1 Types of Tables 383 18.1.2 Methods of Creating Tables 384 18.1.2.1 Scripted Method 385 18.1.2.2 CREATE TABLE ... AS Subquery 386 18.1.2.3 Tools 386 18.2 CREATE TABLE Syntax 387 18.3 Creating Different Table Types 388 18.3.1 Creating Relational Tables 388 18.3.2 Creating Object Tables 390 18.3.3 Creating Temporary Tables 393 18.3.4 Creating Index-Organized Tables (IOTs) 397 18.3.5 Creating External Tables 398 18.3.6 Creating Partitioned Tables 402 18.3.6.1 What Are the Types of Partitions? 402 Partition Indexing 402 18.3.6.2 CREATE TABLE Partition Syntax 403 CREATE TABLE Range Partition Syntax 403 CREATE TABLE List Partition Syntax 403 CREATE TABLE Hash Partition Syntax 404 CREATE TABLE Range-Hash Partition Syntax 405 CREATE TABLE Range-List Partition Syntax 405 18.4 Changing Table Structure 407 18.4.1 Adding, Modifying, and Removing Columns 408 18.4.2 Rebuilding a Table 412 18.4.3 Renaming a Table 413 18.5 Dropping a Table 414 18.5.1 Truncating Instead of Dropping Tables 415 18.6 Adding Comments to Tables 416 18.6.1 Adding Comments to Schema Objects 416 18.6.2 Scripting and SQL Code Comments 419 18.7 The Recycle Bin 420 18.8 Metadata Views 421 19 Views 425 19.1 What Is a View? 425 19.2 Types and Uses of Views 426 19.3 CREATE VIEW Syntax 427 19.3.1 Creating Simple Views 428 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Table of Contents xv 19.3.2 Creating Constraint Views 429 19.3.3 Creating Complex Views 430 19.3.3.1 Views with Joins 430 19.3.3.2 Inline Subquery Views 432 19.4 Changing and Dropping Views 433 19.5 Working with Views 435 19.5.1 Querying a View 435 19.5.2 Views and DML Commands 437 19.5.2.1 DML and Views with Joins 440 19.6 Metadata Views 441 19.7 Data Dictionary Views (Metadata) 442 20 Constraints 447 20.1 What Are Constraints? 448 20.1.1 Types and Uses of Constraints 448 20.2 Managing Constraints 449 20.2.1 CREATE TABLE Syntax 450 20.2.1.1 Primary Key and Unique Constraints 451 20.2.1.2 Foreign Key Constraints 452 Out-of-Line Primary and Foreign Keys 453 20.2.1.3 Check Constraints 456 20.2.1.4 REF Constraints 459 20.2.2 CREATE VIEW Syntax 460 20.3 Adding, Modifying, and Dropping Constraints 460 20.3.1 ALTER TABLE Syntax 460 20.3.2 ALTER VIEW Syntax 461 20.3.3 Working with Constraints and ALTER TABLE 461 20.3.3.1 Adding a Constraint to an Existing Table 462 20.3.3.2 Modifying Constraints on Existing Tables 463 20.3.3.3 Constraint States 463 20.3.4 Renaming a Constraint 464 20.3.5 Dropping Constraints 465 20.3.5.1 Dropping Constraints with CASCADE 466 20.4 Metadata Views 469 21 Indexes and Clusters 471 21.1 Indexes 471 21.1.1 What Is an Index? 471 21.1.2 Types of Indexes 474 21.1.2.1 Index Attributes 476 Contents Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
xvi Table of Contents 21.1.3 Creating Indexes 477 21.1.4 Changing and Dropping Indexes 482 21.1.5 More Indexing Reﬁnements 483 21.2 Clusters 484 21.2.1 What is a Cluster? 484 21.2.2 Types of Clusters 485 21.2.3 Creating Clusters 485 21.3 Metadata Views 487 22 Sequences and Synonyms 489 22.1 Sequences 489 22.1.1 Creating Sequences 490 22.1.2 Changing and Dropping Sequences 493 22.1.3 Using Sequences 494 22.1.3.1 Using the CURRVAL and NEXTVAL Pseudocolumns 495 22.1.3.2 Using Sequences in an INSERT Statement 496 22.1.3.3 Other Uses of Sequences 497 22.2 Synonyms 498 22.2.1 Creating Public Synonyms 499 22.2.2 Creating Private Synonyms 500 22.2.3 Using Synonyms 500 22.3 Metadata Views 501 23 Security 503 23.1 Users 503 23.1.1 Users Provided by Oracle 504 23.1.2 Creating Users 505 23.1.3 Modifying User Passwords 508 23.1.4 Dropping Users 510 23.2 Privileges 511 23.2.1 Granting Privileges 511 23.2.2 Revoking Privileges 518 23.2.2.1 Revoked System Privileges DO NOT Cascade 520 23.2.2.2 Revoked Object Privileges DO Cascade 521 23.3 Grouping Privileges Using Roles 522 23.3.1 Creating and Altering Roles 523 23.3.2 Granting and Revoking Privileges on Roles 524 23.3.3 Setting User Roles 527 23.3.4 Dropping Roles 529 23.4 Metadata Views 530 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Table of Contents xvii 24 Basic PL/SQL 531 24.1 What is PL/SQL? 531 24.2 Why Is PL/SQL a Programming Language? 532 24.2.1 Blocks and Exception Trapping 533 24.2.2 Procedures, Functions, Triggers, and Packages 534 24.2.2.1 Using Named Procedures 535 24.2.2.2 Using Functions 535 24.2.2.3 Using Triggers 537 24.2.2.4 Using Packages 539 24.3 Variables and Datatypes in PL/SQL 541 24.4 Retrieving Data in PL/SQL 543 24.4.1 Explicit Cursors 543 24.4.2 Implicit Cursors 544 24.4.2.1 The Internal SQL Implicit Cursor 545 24.4.2.2 Single-Row SELECT Implicit Cursor 547 24.4.2.3 Cursor FOR Loop Implicit Cursor 547 24.5 Changing Data in PL/SQL 549 24.6 Dynamic SQL 550 24.6.1 Building Cursors Dynamically 552 24.7 Control Structures 553 24.7.1 Selection 554 24.7.1.1 The IF Statement 554 24.7.1.2 The CASE Statement 556 CASE Statement Search Condition 557 CASE Statement Selector and Expression 558 24.7.2 Iteration or Repetition 560 24.7.2.1 The FOR Loop 561 24.7.2.2 The WHILE Loop 564 24.7.2.3 The LOOP...END LOOP Construct 564 24.7.2.4 The FORALL Command 565 24.7.3 Sequence Controls 565 24.7.3.1 The GOTO Statement 566 24.7.3.2 The NULL Command 566 24.8 Objects and Methods 567 24.9 Oracle-Provided Packages 567 24.10 Metadata Views 568 Appendix A Schema Scripting 569 Appendix B Utility Scripts 623 Contents Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
xviii Table of Contents Appendix C Sources of Information 625 Index 627 Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Foreword As a consultant with more than 12 years of experience working with Oracle databases on a daily basis, reviewing this book was a unique and enjoyable experience. The SQL language is without doubt one of the most critical database skills and it is best learned by example. This book addresses that crucial need. Mr. Powell does an excellent job of clarifying the concepts by using meaningful and easy to understand examples. Frankly, I have not come across any other book on SQL that is as good a compilation of SQL concepts in a single source. Oracle SQL Jumpstart with Examples will be a very useful reference and should be a hit for anyone who may be using Oracle SQL. This book should become very popular not only with Developers and DBAs but also Database Managers, Designers and System Managers. Even busy executives can use the book to quickly write queries on an occasional basis. Addition- ally, the examples in the book provide a good reference for functional peo- ple, (such as systems engineers and project leaders,) who want a better understanding of the true capabilities of Oracle SQL, allowing for better articulation and understanding of user and system requirements. One comes across very few books that make a signiﬁcant difference in the fundamental understanding of a subject. This is one such book if you want to understand a core database skill – Oracle SQL. This book deserves a place in your secret library and you will ﬁnd it a great reference not only for learning SQL but also for learning data relationships, data organization, data analysis possibilities and so forth. I feel that the title, Oracle SQL Jumpstart with Examples, might be too simplistic to describe the content. Read on, you will ﬁnd the real value hidden inside this book. – Ravi Sharma—Senior Principal Consultant xix Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.