Oracle SQL: Jumpstart with Examples

by Gavin JT Powell, Carol McCullough-Dieter

Overview

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.


* Each of the hundreds of SQL code examples was tested on a working Oracle 10g database
* Invaluable everyday tool that provides an absolute plethora of properly tested examples of Oracle SQL code
* Authors have four decades of commercial experience between them as developers and database administrators

Product Details

ISBN-13:	9781555583231
Publisher:	Elsevier Science
Publication date:	10/07/2004
Pages:	688
Product dimensions:	1.44(w) x 7.50(h) x 9.25(d)

About the Author

Gavin Powell, BSc. Comp.Sci., OCP (Oracle8i Certified Professional) has fifteen years of computer industry experience. Diverse experience in database administration and database development in both relational and object databases. Applications development experience is procedural and object-oriented. He also has some systems administration experience. His extensive consulting experience includes software vendors, internet .COM’s (some unfortunately have met with their demise), accounting, banking, financial services, the travel industry, construction, retail, mining, shipping, education and general advisory capacity. Gavin Powell has worked with many software products, tools and programming languages. These tools include items in the list shown below. He has authored two successful books Oracle …High performance Tuning and the just released Oracle SQL

Carol McCullough-Dieter is a veteran database designer and trainer. She has written many books on Oracle including Oracle9i Database Administrator: Implementation and Administration. As an independent consultant for federal and state government projects in Wisconsin and Hawaii, she designed and implemented complex, secure, integrated web-based database systems. She has designed in-house and online training guides for Oracle SQL coding and performance tuning.

Table of Contents

	Foreword	xix
	Preface	xxi
	Acknowledgements	xxix
1	Introduction to Oracle SQL	1
1.1	A Little History	1
1.2	The Basics of Relational Data Modeling	8
1.3	Structured Query Language (SQL)	14
1.4	Software Useful for Reading this Book	16
1.5	Syntax Conventions Used in This Book	17
1.6	SQL Tools	19
1.7	The MUSIC Schema	34
2	New Features of Oracle SQL	39
2.1	New Features in Oracle Database 10g	39
2.2	New Features in Oracle Database 9i	46
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.4	Database Startup and Shutdown	65
3.5	Enhancing the Physical Architecture	67
4	The SELECT Statement	73
4.1	The Basic SELECT Statement	73
4.2	Types of SELECT Queries	81
4.3	Other Aspects of the SELECT Statement	88
5	Filtering Rows	97
5.1	WHERE Clause Syntax	97
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.2	Sorting and Null Values	113
6.3	Sorting Methods	117
7	Operators, Conditions, and Pseudocolumns	123
7.1	Precedence	124
7.2	Operators	124
7.3	Conditions	131
7.4	Pseudocolumns	134
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.4	Using iSQL*Plus	165
9	Single-Row Functions	175
9.1	Types of Functions	176
9.2	Single-Row Functions	176
9.3	Combining Functions	196
10	Joining Tables	205
10.1	Join Formats	206
10.2	Types of Joins	207
10.3	Examining Different Types of Joins	210
11	Grouping and Summarizing Data	235
11.1	GROUP BY Clause Syntax	235
11.2	Types of Group Functions	237
11.3	Special Grouping Function Behavior	245
11.4	Using the GROUP BY Clause	249
11.5	The SPREADSHEET (MODEL) Clause	260
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
13	Unusual Query Types	285
13.1	Composite Queries	285
13.2	Hierarchical Queries	289
13.3	Flashback and Versions Queries	292
13.4	Parallel Queries	297
14	Expressions	301
14.1	Types of Expressions	302
14.2	Regular Expressions	305
14.3	Oracle Expression Filter	309
15	Data Manipulation Language (DML)	315
15.1	What Is DML?	315
15.2	Transaction Control	317
15.3	Adding Data (INSERT)	324
15.4	Changing Data (UPDATE)	330
15.5	Deleting Data (DELETE)	334
15.6	Merging New and Old Data (MERGE)	336
16	Datatypes and Collections	339
16.1	Simple Datatypes	339
16.2	Complex and Object Datatypes	342
16.3	Special Datatypes	355
17	XML in Oracle	357
17.1	What Is XML?	357
17.2	Using XML in Oracle	360
17.3	Metadata Views	380
18	Tables	383
18.1	What Is a Table?	383
18.2	CREATE TABLE Syntax	387
18.3	Creating Different Table Types	388
18.4	Changing Table Structure	407
18.5	Dropping a Table	414
18.6	Adding Comments to Tables	416
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.4	Changing and Dropping Views	433
19.5	Working with Views	435
19.6	Metadata Views	441
19.7	Data Dictionary Views (Metadata)	442
20	Constraints	447
20.1	What Are Constraints?	448
20.2	Managing Constraints	449
20.3	Adding, Modifying, and Dropping Constraints	460
20.4	Metadata Views	469
21	Indexes and Clusters	471
21.1	Indexes	471
21.2	Clusters	484
21.3	Metadata Views	487
22	Sequences and Synonyms	489
22.1	Sequences	489
22.2	Synonyms	498
22.3	Metadata Views	501
23	Security	503
23.1	Users	503
23.2	Privileges	511
23.3	Grouping Privileges Using Roles	522
23.4	Metadata Views	530
24	Basic PL/SQL	531
24.1	What is PL/SQL?	531
24.2	Why Is PL/SQL a Programming Language?	532
24.3	Variables and Datatypes in PL/SQL	541
24.4	Retrieving Data in PL/SQL	543
24.5	Changing Data in PL/SQL	549
24.6	Dynamic SQL	550
24.7	Control Structures	553
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
Appendix C	Sources of Information	625
	Index	627

Editorial Reviews

"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 as this book.

One comes across very few books that make a significant difference in your fundamental understanding of the subject. This is one such book if you want to understand a core database skill – SQL. This book deserves a place in your library and you will find 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 find the real value hidden inside this book." — Ravi Sharma, Senior Principal Consultant

From the Publisher

The Barnes & Noble Review
Every Oracle professional needs a reliable source of information on Oracle SQL. This book is it. More than a comprehensive and well-written reference, it delivers bushels of Oracle 10g example code -- all tested and verified.

Gavin Powell and Carol McCullough-Dieter cover Oracle SQL from soup to nuts: SELECT, WHERE, ORDER BY, operators, conditions, pseudocolumns, SQL*PLUS, functions, joins, grouping, summarization, subqueries, expressions, DML, datatypes, collections, indexes, clusters, constraints, PL/SQL, and much more. You’ll also find detailed coverage of XML datatypes, generation, and retrieval; and managing security via users, privileges, groups, and roles.

Much of what’s in here applies to Oracle 9i as well. If you haven't upgraded yet, you can rely on this book now, and keep relying on it after you move up. Bill Camarda, from the January 2005 Read Only

bn.com