Practical Guide To Using Sql In Oracle

by Earp, Richard Walsh

• ISBN13: 9781598220636
• ISBN10: 1598220632
• Edition: 1st
• Format: Paperback
• Copyright: 2008-09-05
• Publisher: Jones & Bartlett Learning

Summary

Structured Query Language has become the standard for generating, manipulating, and retrieving database information. The dramatic increase in the popularity of relational databases, coupled with Oracle’s having the largest market share, has created a demand for programmers who can write SQL code correctly and efficiently. This book provides a systematic approach to learning SQL in Oracle. Each chapter is written in a step-by-step manner and includes examples that can be run using Oracle. Using the sample tables and data provided, readers will be able to perform the examples to gain hands-on experience with Oracle programming.Gain an understanding of basic SQL principles. Learn to generate, store, and edit SQL queries in Oracle. Develop joins, subqueries, and correlated subqueries. Work with XML and Oracle databases. Test your SQL knowledge with the exercises at the end of each chapter!

Author Biography

Dr. Richard Walsh Earp is a former chair of and former associate professor in the Department of Computer Science and former dean of the College of Science and Technology at the University of West Florida in Pensacola Dr. Sikha Saha Bagui is an associate professor in the department of Computer Science at the University of West Florida. She teaches a variety of computer science and database courses, and her research areas of concentration are database design, webdatabases, data mining, and statistical computing

Table of Contents

Preface	p. xiii
Acknowledgments	p. xvii
Prologue: The Software Engineering Process and Relational Databases	p. 1
What Is a Database?	p. 2
Database Models	p. 3
The Hierarchical Model	p. 3
Other Cardinalities	p. 6
The Network Model	p. 8
Contemporary Databases: The Relational Model	p. 9
The First, Second, and Third Normal Forms	p. 11
The First Normal Form	p. 12
Non-1NF to First Normal Form (1NF)	p. 13
The Second Normal Form	p. 14
Non-2NF to 2NF	p. 16
The Third Normal Form	p. 18
Non-3NF to 3NF	p. 19
What Is the Software Engineering Process?	p. 20
Getting Started with Oracle	p. 25
Getting Started with Oracle in UNIX	p. 26
Signing on to Oracle in UNIX	p. 26
Setting Your System Parameters	p. 27
Setting the PAUSE Parameter	p. 28
Setting the Prompt Parameter	p. 28
Showing Timing Statistics	p. 29
Viewing a List of System Parameters	p. 29
Oracle's HELP Command in UNIX	p. 30
Using Oracle Commands	p. 31
Understanding SQL and Its Sublanguage	p. 31
Using SELECT Statement Syntax	p. 32
Re-executing a Command	p. 33
Accessing Tables	p. 34
Adding Comments to Statements	p. 35
A Few More Examples and Further Comments about Case	p. 36
Editing SQL Statements	p. 36
Editing SQL Statements Using an Editor	p. 37
Defining an Editor	p. 37
Editing the Buffer	p. 37
Saving the Buffer	p. 39
Using GET	p. 40
Using a Script File to Save Your Query	p. 40
Editing SQL Statements or Queries Using SQLPLUS	p. 41
Using the CHANGE Command	p. 41
Using the LIST Command	p. 42
Using the APPEND Command	p. 43
Using the INPUT Command	p. 45
Using INPUT to Insert a Line	p. 47
Using the DELETE Command	p. 49
Displaying the Student-Course Database	p. 50
Displaying the Course Table (the Course Relation)	p. 50
Creating a Synonym for the Course Table	p. 51
Deleting a Synonym	p. 52
Introducing the Oracle Data Dictionary	p. 52
Using DESC	p. 54
Using a Convention for Writing SQL Statements	p. 55
Printing Query Results and Using Host	p. 56
Signing Off from Oracle	p. 58
Exercises for Chapter 1	p. 59
More "Beginning" SQL Commands	p. 63
An Extended SELECT Statement	p. 63
SELECTing Attributes (Columns)	p. 65
Using ORDER BY	p. 66
SELECTing Rows	p. 70
Using AND	p. 71
Using OR	p. 72
Using BETWEEN	p. 73
A Simple CREATE TABLE Command	p. 75
Inserting Values into an Existing Table	p. 76
INSERT INTO .. VALUES	p. 76
INSERT INTO .. SELECT	p. 79
The UPDATE Command	p. 82
The DELETE Command	p. 84
Deleting a Table	p. 84
ROLLBACK, COMMIT, and SAVEPOINT	p. 85
The ALTER TABLE Command	p. 92
Data Types	p. 94
Common Number Data Types	p. 95
CHAR Data Type	p. 97
VARCHAR2 Data Type	p. 98
NCHAR and NVARCHAR2 Data Types	p. 98
LONG, RAW, LONG RAW, and BOOLEAN Data Types	p. 99
Large Object (LOB) Data Types	p. 99
Abstract Data Types	p. 100
The XML Data Type	p. 100
The DATE Data Type and Type Conversion Functions	p. 101
Entering Four-Digit Years	p. 105
Exercises for Chapter 2	p. 107
Joins	p. 111
The Cartesian Product	p. 112
The Join	p. 114
Join Using ANSI Join Syntax	p. 115
Theta Joins	p. 116
Qualifiers	p. 116
Table Aliases and an Introduction to Multi-table Joins	p. 118
More on Comments	p. 119
More on Multiple Table Joins and Join Conditions	p. 121
Column Aliases	p. 123
Scripting	p. 126
COUNT and Rownum	p. 128
Using COUNT	p. 129
Using Rownum	p. 130
Outer Joins	p. 130
Left Outer Join	p. 131
Right Outer Join	p. 132
Handling Full Outer Joins	p. 133
Outer Join with an AND Condition	p. 135
Chaining Outer Joins	p. 136
Self Joins	p. 138
Self Join and Outer Join	p. 139
Exercises for Chapter 3	p. 139
Functions	p. 145
The COUNT Function	p. 146
Using SELECT and COUNT with DISTINCT	p. 148
More Basic Functions	p. 149
Aggregate Functions	p. 150
Row-level Functions	p. 151
The NVL Function	p. 151
String Functions	p. 152
The SUBSTR and INSTR Functions	p. 153
The RPAD and LPAD Functions	p. 157
The LTRIM and RTRIM Functions	p. 158
The LENGTH Function	p. 158
Matching Substrings Using LIKE	p. 159
LIKE as an Existence Match	p. 159
LIKE with a Positioned Match and a Wildcard	p. 163
The UPPER and LOWER Functions	p. 164
The Data Dictionary Revisited	p. 165
Exercises for Chapter 4	p. 167
Query Development, Privileges, and Derived Structures	p. 175
Query Development	p. 176
Using SAVE and EDIT	p. 176
Deleting a Query	p. 181
Parentheses in SQL Expressions	p. 182
Derived Structures	p. 184
Views	p. 184
Using the CREATE OR REPLACE VIEW Statements	p. 185
Adding ORDER BY to CREATE OR REPLACE VIEW Statements	p. 186
Developing a Query Using Views	p. 188
Creating Special View Column Names	p. 188
Grating and Revoking Privileges on Tables and Views	p. 190
Query Development and Derived Structures	p. 191
Developing a Query Step-by-Step	p. 192
Using a Derived Structure	p. 195
Make Your Query a View	p. 195
Create a Temporary Table	p. 196
Use an Inline View	p. 197
Use a Snapshot	p. 199
Exercises for Chapter 5	p. 200
Set Operations	p. 205
UNION Operations	p. 206
The IN and NOT..IN Predicates	p. 208
Using IN	p. 209
Using NOT..IN	p. 212
The Difference Operation	p. 217
Exercises for Chapter 6	p. 222
Subqueries versus Joins	p. 227
The IN Subquery	p. 228
The Subquery as a Join	p. 230
When the Join Cannot Be Turned into a Subquery	p. 232
More Examples Involving Joins and IN	p. 234
	p. 234
	p. 236
	p. 238
Subqueries with Operators	p. 240
Exercises for Chapter 7	p. 242
GROUP BY and HAVING	p. 247
Aggregates/Column Functions	p. 247
The GROUP BY Clause	p. 248
GROUP BY and ORDER BY	p. 251
The HAVING Clause	p. 253
HAVING and WHERE	p. 254
GROUP BY and HAVING: Aggregates of Aggregates	p. 256
Auditing IN Subqueries	p. 258
Nulls Revisited	p. 262
Exercises for Chapter 8	p. 265
Correlated Subqueries	p. 269
Non-Correlated Subqueries	p. 269
Correlated Subqueries	p. 271
Existence Queries and Correlation	p. 274
EXISTS	p. 276
From IN to EXISTS	p. 282
NOT EXISTS	p. 284
SQL Universal and Existential Qualifiers -the "for all" Query	p. 291
	p. 291
	p. 295
	p. 300
Exercises for Chapter 9	p. 301
CREATE TABLE and SQLLOADER	p. 307
The "Simple" CREATE TABLE	p. 308
The NOT NULL Constraint	p. 309
PRIMARY KEY Constraints	p. 312
Creating the PRIMARY KEY Constraint	p. 313
At the Column Level	p. 313
At the Table Level	p. 313
Using the ALTER TABLE Command	p. 314
Adding a Concatenated Primary Key	p. 314
Another Example of Adding a Concatenated Primary Key	p. 315
The UNIQUE Constraint	p. 316
The CHECK Constraint	p. 317
Referential Integrity	p. 317
Defining the Referential Integrity Constraint	p. 320
Adding the Foreign Key after Tables Are Created	p. 321
Using DELETE and the Referential Constraint	p. 322
ON DELETE RESTRICT	p. 322
ON DELETE CASCADE	p. 323
ON DELETE SET NULL	p. 324
More on Constraint Names	p. 324
SQLLOADER	p. 327
SQLLOADER Example 1	p. 327
Another SQLLOADER Example	p. 330
Exercises for Chapter 10	p. 331
Multiple Commands, START Files, and Reports in SQLPLUS	p. 335
Creating a File (a START Table) and Starting It	p. 336
A START File (Script) with Editing Features	p. 337
Using the DECODE, GREATEST, and LEAST Functions	p. 342
DECODE	p. 342
GREATEST and LEAST	p. 345
Adding Reporting Features to a START File	p. 347
A New and Improved Script	p. 349
Using START Files with ACCEPT and PROMPT	p. 354
Using START Files with Positional Input	p. 355
Exercises for Chapter 11	p. 357
Beginning PL/SQL: Anonymous Blocks, Procedures, Functions, and Packages	p. 361
Anonymous Blocks	p. 363
Elementary Procedures with Sequence Structures	p. 364
A Simple Example of a Procedure	p. 365
Reusing a Procedure	p. 367
Deleting a Procedure	p. 367
Adding a Parameter List to a Procedure	p. 368
Performing More than One Action in a Procedure	p. 369
Procedures with Selection and Iteration Control Structures	p. 371
Example of a Procedure with Selection	p. 371
Example of a Procedure with Interation	p. 372
Functions	p. 374
Example of a Function	p. 374
Deleting a Function	p. 375
Packages	p. 375
Creating a Package	p. 376
Another Approach to Creating This Package	p. 379
Deleting a Package	p. 380
Defining a PL/SQL INDEX BY Table	p. 380
Using a PL/SQL Table	p. 382
Exercises for Chapter 12	p. 384
Introduction to Triggers	p. 387
What Is a Trigger?	p. 387
A Simple Trigger Example	p. 389
How the Trigger Worked	p. 391
Row-Level Triggers versus Statement-Level Triggers	p. 392
Enabling and Disabling Triggers	p. 392
Enabling All Triggers for a Table	p. 393
Deleting Triggers	p. 393
Values in the Trigger	p. 393
Using WHEN	p. 394
Performance Issues Using WHEN	p. 395
A Trigger Where One Table Affects Another Trigger	p. 395
Mutating Tables	p. 399
Exercises for Chapter 13	p. 400
SQL and XML	p. 405
Overview of XML	p. 406
Oracle and XML	p. 407
XMLFOREST	p. 408
Using XMLELEMENT	p. 409
Using XMLATTRIBUTES	p. 412
Creating a Table Using the XML Type Data Type	p. 414
Inserting Values into Tables with an XML Type Data Type	p. 415
Extracting Information Using XPATH	p. 417
Using EXTRACTIVALUE	p. 417
Using EXISTSNODE	p. 417
Exercises for Chapter 14	p. 418
Some UNIX Commands	p. 421
Commonly Used UNIX Commands	p. 421
Summary Table	p. 424
Other Miscellaneous Commands	p. 424
Editors	p. 425
Using vi as Your Editor	p. 425
Other vi Commands	p. 426
Using joe as Your Editor	p. 427
The Data Dictionary	p. 429
Beginning to Explore the Data Dictionary	p. 430
Choosing a View from the Dictionary	p. 434
Choosing the View You Want to See	p. 434
Describing the View You Want to See	p. 435
Finding the "Right" Columns	p. 436
Finding out How Many Rows Are in the View	p. 436
Views of TABLES	p. 439
Other Objects - Tablespaces and Constraints	p. 440
Views of Tablespaces	p. 441
Views of Constraints	p. 443
Exercises for Appendix B	p. 445
The Student Database and Other Tables Used in This Book	p. 447
The Student-Course Database	p. 447
Entity Relationship Diagram of the Student-Course Database	p. 449
Other Tables Used in This Book	p. 450
Glossary of Terms	p. 453
Important Commands and Functions	p. 467
Index	p. 479
Table of Contents provided by Ingram. All Rights Reserved.