Oracle SQL

by Powell; McCullough-Dieter

• ISBN13: 9781555583231
• ISBN10: 1555583237
• Format: Paperback
• Copyright: 2004-09-23
• Publisher: Elsevier Science

Summary

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.

Author Biography

Gavin Powell, BSc. Comp.Sci., OCP (Oracle Certified Professional), has fifteen years of diverse experience in database administration and database development in both relational and object databases Carol McCullough-Dieter is a veteran database designer and trainer. She has written many books on Oracle including Oraclegi for Dummies and the college textbook Oraclegi Database Administrator: Implementation and Administration

Table of Contents

Foreword	xix
Preface	xxi
Acknowledgements	xxix
Introduction to Oracle SQL	1	(38)
A Little History	1	(7)
The Evolution of Database Modeling	1	(4)
The History of Relational Databases	5	(1)
The Evolution of Oracle Database	6	(2)
The Basics of Relational Data Modeling	8	(6)
Normalization	8	(2)
Referential Integrity	10	(1)
Denormalization	11	(2)
Different Forms of the Relational Data Model	13	(1)
Structured Query Language (SQL)	14	(2)
The Humble Origins of SQL	14	(1)
What Is Oracle SQL?	15	(1)
ANSI Standards and Oracle	15	(1)
Software Useful for Reading this Book	16	(1)
Syntax Conventions Used in This Book	17	(2)
SQL Tools	19	(15)
SQL*Plus in Command-Line Mode	19	(5)
SQL*Plus in Windows Mode	24	(4)
SQL*Plus Worksheet	28	(3)
iSQL*Plus	31	(3)
The MUSIC Schema	34	(5)
The MUSIC Schema Sales Data Warehouse	36	(3)
New Features of Oracle SQL	39	(12)
New Features in Oracle Database 10g	39	(7)
Oracle SQL Improvements in Oracle Database 10g	39	(5)
PL/SQL Improvements in Oracle Database 10g	44	(1)
Java Improvements in Oracle Database 10g	45	(1)
XML Improvements in Oracle Database 10g	45	(1)
Some Utility Improvements in Oracle Database 10g	45	(1)
Database Object Improvements in Oracle 10g	46	(1)
New Features in Oracle Database 9i	46	(5)
Oracle SQL Improvements in Oracle Database 9i	47	(1)
New PL/SQL Features in Oracle Database 9i	48	(3)
Oracle Database Architecture	51	(22)
The Basic Concepts	51	(7)
The Oracle Instance	58	(2)
Oracle Database Physical Architecture	60	(5)
Datafiles, Tablespaces, and Objects	60	(1)
Controlfiles, Logging, and Archiving	61	(1)
Rollback and Undo	62	(2)
Temporary Sort Space	64	(1)
Database Startup and Shutdown	65	(2)
Enhancing the Physical Architecture	67	(6)
Oracle Managed Files	67	(1)
Partitioning	67	(1)
Replication	68	(1)
Standby Databases	69	(1)
Clustering and Oracle RAC	70	(3)
The SELECT Statement	73	(24)
The Basic SELECT Statement	73	(8)
Uses of the SELECT Statement	73	(1)
Syntax Conventions	74	(2)
Some Simple Example SELECT Statements	76	(5)
Types of SELECT Queries	81	(7)
Simple Query	82	(1)
Filtered Query	82	(1)
Sorted Query	83	(1)
Grouping or Aggregated Query	83	(1)
Join Query	84	(1)
Subquery	85	(1)
Table or View Creation Query	85	(1)
Hierarchical Query	86	(2)
Composite Queries	88	(1)
Other Aspects of the SELECT Statement	88	(9)
The DUAL Table	89	(1)
Using Functions	90	(1)
Arithmetic Operations	91	(1)
Using DISTINCT	92	(1)
Null Values	93	(1)
Using Pseudocolumns	94	(3)
Filtering Rows	97	(12)
WHERE Clause Syntax	97	(4)
Some Simple WHERE Clause Examples	98	(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