Practical Guide to using SQL in Oracle

by Dr. Richard Earp, Dr. Sikha Bagui

Overview

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!

Product Details

ISBN-13:	9781598220636
Publisher:	Jones & Bartlett Learning
Publication date:	09/05/2008
Series:	Wordware Applications Library
Pages:	485
Product dimensions:	6.00(w) x 8.90(h) x 1.10(d)

About the Author

Associate Professor of Computer Science, University of West Florida

Table of Contents

Preface xiii

Acknowledgments xvii

Prologue: The Software Engineering Process and Relational Databases 1

What Is a Database? 2

Database Models 3

The Hierarchical Model 3

Other Cardinalities 6

The Network Model 8

Contemporary Databases: The Relational Model 9

The First, Second, and Third Normal Forms 11

The First Normal Form 12

Non-1NF to First Normal Form (1NF) 13

The Second Normal Form 14

Non-2NF to 2NF 16

The Third Normal Form 18

Non-3NF to 3NF 19

What Is the Software Engineering Process? 20

Chapter 1 Getting Started with Oracle 25

Getting Started with Oracle in UNIX 26

Signing on to Oracle in UNIX 26

Setting Your System Parameters 27

Setting the PAUSE Parameter 28

Setting the Prompt Parameter 28

Showing Timing Statistics 29

Viewing a List of System Parameters 29

Oracle's HELP Command in UNIX 30

Using Oracle Commands 31

Understanding SQL and Its Sublanguage 31

Using SELECT Statement Syntax 32

Re-executing a Command 33

Accessing Tables 34

Adding Comments to Statements 35

A Few More Examples and Further Comments about Case 36

Editing SQL Statements 36

Option 1 Editing SQL Statements Using an Editor 37

Defining an Editor 37

Editing the Buffer 37

Saving the Buffer 39

Using GET 40

Using a Script File to Save Your Query 40

Option 2 Editing SQL Statements or Queries Using SQLPLUS 41

Using the CHANGE Command 41

Using the LIST Command 42

Using the APPEND Command 43

Using the INPUT Command 45

Using INPUT to Insert a Line 47

Using the DELETE Command 49

Displaying the Student-Course Database 50

Displaying the Course Table (the Course Relation) 50

Creating a Synonym for the Course Table51

Deleting a Synonym 52

Introducing the Oracle Data Dictionary 52

Using DESC 54

Using a Convention for Writing SQL Statements 55

Printing Query Results and Using Host 56

Signing Off from Oracle 58

Exercises for Chapter 1 59

Chapter 2 More "Beginning" SQL Commands 63

An Extended SELECT Statement 63

SELECTing Attributes (Columns) 65

Using ORDER BY 66

SELECTing Rows 70

Using AND 71

Using OR 72

Using BETWEEN 73

A Simple CREATE TABLE Command 75

Inserting Values into an Existing Table 76

INSERT INTO .. VALUES 76

INSERT INTO .. SELECT 79

The UPDATE Command 82

The DELETE Command 84

Deleting a Table 84

ROLLBACK, COMMIT, and SAVEPOINT 85

The ALTER TABLE Command 92

Data Types 94

Common Number Data Types 95

CHAR Data Type 97

VARCHAR2 Data Type 98

NCHAR and NVARCHAR2 Data Types 98

LONG, RAW, LONG RAW, and BOOLEAN Data Types 99

Large Object (LOB) Data Types 99

Abstract Data Types 100

The XML Data Type 100

The DATE Data Type and Type Conversion Functions 101

Entering Four-Digit Years 105

Exercises for Chapter 2 107

Chapter 3 Joins 111

The Cartesian Product 112

The Join 114

Join Using ANSI Join Syntax 115

Theta Joins 116

Qualifiers 116

Table Aliases and an Introduction to Multi-table Joins 118

More on Comments 119

More on Multiple Table Joins and Join Conditions 121

Column Aliases 123

Scripting 126

COUNT and Rownum 128

Using COUNT 129

Using Rownum 130

Outer Joins 130

Left Outer Join 131

Right Outer Join 132

Handling Full Outer Joins 133

Outer Join with an AND Condition 135

Chaining Outer Joins 136

Self Joins 138

Self Join and Outer Join 139

Exercises for Chapter 3 139

Chapter 4 Functions 145

The COUNT Function 146

Using SELECT and COUNT with DISTINCT 148

More Basic Functions 149

Aggregate Functions 150

Row-level Functions 151

The NVL Function 151

String Functions 152

The SUBSTR and INSTR Functions 153

The RPAD and LPAD Functions 157

The LTRIM and RTRIM Functions 158

The LENGTH Function 158

Matching Substrings Using LIKE 159

LIKE as an Existence Match 159

LIKE with a Positioned Match and a Wildcard 163

The UPPER and LOWER Functions 164

The Data Dictionary Revisited 165

Exercises for Chapter 4 167

Chapter 5 Query Development, Privileges, and Derived Structures 175

Query Development 176

Using SAVE and EDIT 176

Deleting a Query 181

Parentheses in SQL Expressions 182

Derived Structures 184

Views 184

Using the CREATE OR REPLACE VIEW Statements 185

Adding ORDER BY to CREATE OR REPLACE VIEW Statements 186

Developing a Query Using Views 188

Creating Special View Column Names 188

Grating and Revoking Privileges on Tables and Views 190

Query Development and Derived Structures 191

Step 1 Developing a Query Step-by-Step 192

Step 2 Using a Derived Structure 195

Option 1 Make Your Query a View 195

Option 2 Create a Temporary Table 196

Option 3 Use an Inline View 197

Option 4 Use a Snapshot 199

Exercises for Chapter 5 200

Chapter 6 Set Operations 205

UNION Operations 206

The IN and NOT..IN Predicates 208

Using IN 209

Using NOT..IN 212

The Difference Operation 217

Exercises for Chapter 6 222

Chapter 7 Subqueries versus Joins 227

The IN Subquery 228

The Subquery as a Join 230

When the Join Cannot Be Turned into a Subquery 232

More Examples Involving Joins and IN 234

Example 1 234

Example 2 236

Example 3 238

Subqueries with Operators 240

Exercises for Chapter 7 242

Chapter 8 GROUP BY and HAVING 247

Aggregates/Column Functions 247

The GROUP BY Clause 248

GROUP BY and ORDER BY 251

The HAVING Clause 253

HAVING and WHERE 254

GROUP BY and HAVING: Aggregates of Aggregates 256

Auditing IN Subqueries 258

Nulls Revisited 262

Exercises for Chapter 8 265

Chapter 9 Correlated Subqueries 269

Non-Correlated Subqueries 269

Correlated Subqueries 271

Existence Queries and Correlation 274

EXISTS 276

From IN to EXISTS 282

NOT EXISTS 284

SQL Universal and Existential Qualifiers -the "for all" Query 291

Example 1 291

Example 2 295

Example 3 300

Exercises for Chapter 9 301

Chapter 10 CREATE TABLE and SQLLOADER 307

The "Simple" CREATE TABLE 308

The NOT NULL Constraint 309

PRIMARY KEY Constraints 312

Creating the PRIMARY KEY Constraint 313

At the Column Level 313

At the Table Level 313

Using the ALTER TABLE Command 314

Adding a Concatenated Primary Key 314

Another Example of Adding a Concatenated Primary Key 315

The UNIQUE Constraint 316

The CHECK Constraint 317

Referential Integrity 317

Defining the Referential Integrity Constraint 320

Adding the Foreign Key after Tables Are Created 321

Using DELETE and the Referential Constraint 322

ON DELETE RESTRICT 322

ON DELETE CASCADE 323

ON DELETE SET NULL 324

More on Constraint Names 324

SQLLOADER 327

SQLLOADER Example 1 327

Another SQLLOADER Example 330

Exercises for Chapter 10 331

Chapter 11 Multiple Commands, START Files, and Reports in SQLPLUS 335

Creating a File (a START Table) and Starting It 336

A START File (Script) with Editing Features 337

Using the DECODE, GREATEST, and LEAST Functions 342

DECODE 342

GREATEST and LEAST 345

Adding Reporting Features to a START File 347

A New and Improved Script 349

Using START Files with ACCEPT and PROMPT 354

Using START Files with Positional Input 355

Exercises for Chapter 11 357

Chapter 12 Beginning PL/SQL: Anonymous Blocks, Procedures, Functions, and Packages 361

Anonymous Blocks 363

Elementary Procedures with Sequence Structures 364

A Simple Example of a Procedure 365

Reusing a Procedure 367

Deleting a Procedure 367

Adding a Parameter List to a Procedure 368

Performing More than One Action in a Procedure 369

Procedures with Selection and Iteration Control Structures 371

Example of a Procedure with Selection 371

Example of a Procedure with Interation 372

Functions 374

Example of a Function 374

Deleting a Function 375

Packages 375

Creating a Package 376

Another Approach to Creating This Package 379

Deleting a Package 380

Defining a PL/SQL INDEX BY Table 380

Using a PL/SQL Table 382

Exercises for Chapter 12 384

Chapter 13 Introduction to Triggers 387

What Is a Trigger? 387

A Simple Trigger Example 389

How the Trigger Worked 391

Row-Level Triggers versus Statement-Level Triggers 392

Enabling and Disabling Triggers 392

Enabling All Triggers for a Table 393

Deleting Triggers 393

Values in the Trigger 393

Using WHEN 394

Performance Issues Using WHEN 395

A Trigger Where One Table Affects Another Trigger 395

Mutating Tables 399

Exercises for Chapter 13 400

Chapter 14 SQL and XML 405

Overview of XML 406

Oracle and XML 407

XMLFOREST 408

Using XMLELEMENT 409

Using XMLATTRIBUTES 412

Creating a Table Using the XML Type Data Type 414

Inserting Values into Tables with an XML Type Data Type 415

Extracting Information Using XPATH 417

Using EXTRACTIVALUE 417

Using EXISTSNODE 417

Exercises for Chapter 14 418

Appendix A Some UNIX Commands 421

Commonly Used UNIX Commands 421

Summary Table 424

Other Miscellaneous Commands 424

Editors 425

Using vi as Your Editor 425

Other vi Commands 426

Using joe as Your Editor 427

Appendix B The Data Dictionary 429

Beginning to Explore the Data Dictionary 430

Choosing a View from the Dictionary 434

Choosing the View You Want to See 434

Describing the View You Want to See 435

Finding the "Right" Columns 436

Finding out How Many Rows Are in the View 436

Views of TABLES 439

Other Objects - Tablespaces and Constraints 440

Views of Tablespaces 441

Views of Constraints 443

Exercises for Appendix B 445

Appendix C The Student Database and Other Tables Used in This Book 447

The Student-Course Database 447

Entity Relationship Diagram of the Student-Course Database 449

Other Tables Used in This Book 450

Appendix D Glossary of Terms 453

Appendix E Important Commands and Functions 467

Index 479