Learning SQL : A Step-by-Step Guide Using Oracle

by Earp, Richard; Bagui, Sikha

• ISBN13: 9780201773637
• ISBN10: 0201773635
• Format: Paperback
• Copyright: 2003-01-01
• Publisher: Addison Wesley

Summary

Starts with very simple Oracle SQL concepts and slowly builds into more complex query development and PL/SQL. Covers SQL as it is invoked via SQL Plus, a command-line system to launch interactive queries. Provides an appendix for UNIX users of Oracle. Softcover.

Table of Contents

Preface	p. xiii
The Software Engineering Process and Relational Databases	p. 1
What Is a Database?	p. 1
Database Models	p. 2
The Hierarchical Model	p. 3
Other Cardinalities	p. 4
Connecting Records	p. 5
The Network Model	p. 6
Connecting Records	p. 7
Contemporary Databases: The Relational Model	p. 7
The First Normal Form	p. 9
The Second Normal Form	p. 11
The Third Normal Form	p. 14
What Is the Software Engineering Process?	p. 16
References	p. 18
Getting Started with Oracle 8 in the Windows Environment	p. 19
Getting Started on Oracle	p. 19
Signing onto Oracle	p. 20
Setting Your System Parameters	p. 22
Setting the PAUSE Parameter	p. 22
Setting the Prompt Parameter	p. 22
Showing Timing Statistics	p. 24
Viewing a List of System Parameters	p. 24
Printing and Using HOST	p. 24
Signing Off from Oracle	p. 26
Using Oracle Commands	p. 27
Using SELECT Statement Syntax	p. 28
Re-Executing a Command	p. 28
Accessing Tables	p. 28
Adding Comments in Statements	p. 29
A Few More Examples and Further Comments about Case	p. 30
Editing SQL Statements	p. 30
Editing SQL Statements Using an Editor	p. 31
Saving a Query	p. 32
Running a Stored Query	p. 32
Naming Statements	p. 33
Using the GET Command	p. 33
Editing SQL Statements Using SQLPLUS	p. 33
Using the CHANGE Command	p. 34
Using the LIST Command	p. 34
Using Other Commands	p. 35
Displaying the "Student-Course" Database	p. 36
Displaying the Course Table (the Course Relation)	p. 37
Creating a Synonym for the Course Table	p. 38
Deleting a Synonym	p. 38
Introducing the Oracle Data Dictionary	p. 39
Using DESC	p. 41
Employing a Convention for Writing SQL Statements	p. 42
Chapter 1 Exercises	p. 42
More "Beginning" SQL Commands	p. 45
An Extended SELECT Statement	p. 45
SELECTing Attributes (Columns)	p. 46
Using ORDER BY	p. 46
SELECTing Tuples (Rows)	p. 47
A Simple CREATE TABLE Command	p. 48
Inserting Values into a Created Table	p. 49
INSERT INTO .. VALUES	p. 49
INSERT INTO .. SELECT	p. 51
The UPDATE Command	p. 53
The DELETE Command	p. 54
ROLLBACK, COMMIT, and SAVEPOINT	p. 55
The ALTER TABLE Command	p. 61
Data Types	p. 61
Number Data Types	p. 61
CHAR Data Type	p. 62
VARCHAR2 Data Type	p. 62
LONG, RAW, LONG RAW, and BOOLEAN Data Types	p. 62
Large Object (LOB) Data Types	p. 63
Abstract Data Types	p. 63
The DATE Data Type and Type Conversion Functions	p. 64
Entering Four-Digit Years	p. 67
Chapter 2 Exercises	p. 69
The Cartesian Product, the Join, and Aliases	p. 71
The Cartesian Product	p. 71
The Join	p. 73
Theta Joins	p. 74
Qualifiers	p. 74
Queries Involving Multiple Tables: Using Table Aliases	p. 74
More on Comments	p. 75
Join Conditions and Comments	p. 76
Outer Joins	p. 78
Column Aliases	p. 79
Scripting	p. 81
COUNT and Rownum	p. 82
Chapter 3 Exercises	p. 85
References	p. 87
Functions and Matching (LIKE)	p. 89
The COUNT Function	p. 89
SELECTing and COUNTing with DISTINCT	p. 91
Additional Basic Functions	p. 92
More Aggregate Functions	p. 92
The NVL Function	p. 94
String Functions	p. 95
Matching Substrings: Using LIKE	p. 100
LIKE as an Existence Match	p. 101
LIKE as a Positioned Match and a Wildcard	p. 102
The UPPER and LOWER Functions	p. 103
The Data Dictionary Revisited	p. 104
Chapter 4 Exercises	p. 105
Query Development, Privileges, and Derived Structures	p. 109
Query Development	p. 110
Using SAVE and EDIT	p. 110
Deleting a Query	p. 113
Parentheses in SQL Expressions	p. 114
Derived Structures	p. 116
Views	p. 116
Using the CREATE OR REPLACE VIEW Statements	p. 116
Adding ORDER BY to CREATE OR REPLACE VIEW Statements	p. 118
Developing a Query Using Views	p. 119
Creating Special View Column Names	p. 120
GRANTing and REVOKEing Privileges on Tables and Views	p. 121
Query Development and Derived Structures	p. 122
Develop a Query Step by Step	p. 123
Using a Derived Structure	p. 126
Make Your Query a View	p. 126
Create a Temporary Table	p. 127
Use an Inline View	p. 129
Use a Snapshot	p. 130
Chapter 5 Exercises	p. 131
Set Operations	p. 135
Introducing Set Operations	p. 135
Example of a Set Operation in Use	p. 136
Example of Using a UNION	p. 136
The IN and NOT..IN Predicates	p. 137
Using IN	p. 138
Using NOT..IN	p. 142
The Difference Operation	p. 145
Chapter 6 Exercises	p. 150
Optional Exercise	p. 152
Joins versus Subqueries	p. 153
The IN Subquery	p. 153
The Subquery as a Join	p. 155
When the Join Cannot Be Turned into a Subquery	p. 156
More Examples Involving Joins and IN	p. 158
Example 1	p. 158
Example 2	p. 160
Example 3	p. 161
Subqueries with Operators	p. 162
Chapter 7 Exercises	p. 164
Optional Exercise	p. 166
GROUP BY and HAVING	p. 167
Aggregates/Column Functions	p. 167
The GROUP BY Clause	p. 168
GROUP BY and ORDER BY	p. 170
The HAVING Clause	p. 172
HAVING and WHERE	p. 173
GROUP BY and HAVING: Aggregates of Aggregates	p. 174
Auditing IN Subqueries	p. 176
Nulls Revisited	p. 179
Chapter 8 Exercises	p. 182
Optional Exercise	p. 183
Correlated Subqueries	p. 185
Non-Correlated Subqueries	p. 185
Correlated Subqueries	p. 187
Existence Queries and Correlation	p. 189
EXISTS	p. 191
From IN to EXISTS	p. 197
NOT EXISTS	p. 198
SQL Universal and Existential Qualifiers: The "For All" Query	p. 201
Example 1	p. 202
Example 2	p. 204
Example 3	p. 208
Chapter 9 Exercises	p. 209
References	p. 213
CREATE TABLEs and SQLLOADER	p. 215
The "Simple" CREATE TABLE	p. 215
The NOT NULL Constraint	p. 216
PRIMARY KEY Constraints	p. 218
Creating the PRIMARY KEY Constraint	p. 218
Adding a Concatenated Primary Key	p. 219
The UNIQUE Constraint	p. 220
The CHECK Constraint	p. 221
Referential Integrity	p. 221
Defining the Referential Integrity CONSTRAINT	p. 223
Adding the Foreign Key After Tables Are Created	p. 224
Using Delete and the Referential Constraint	p. 225
ON DELETE RESTRICT	p. 225
ON DELETE CASCADE	p. 225
ON DELETE SET NULL	p. 226
More on CONSTRAINT Names	p. 226
SQLLOADER	p. 227
SQLLOADER Example 1	p. 228
Another SQLLOADER Example	p. 231
Chapter 10 Exercises	p. 231
Multiple Commands, START Files, and Reports in SQLPLUS	p. 235
Creating a File (a START Table) and STARTing It	p. 235
A START File with Editing Features	p. 236
Using the DECODE, GREATEST, and LEAST Functions	p. 239
DECODE	p. 239
GREATEST and LEAST	p. 240
Adding Reporting Commands to a START File	p. 242
A New and Improved Script	p. 243
Using START Files with ACCEPT and PROMPT	p. 247
Using START Files with Positional Input	p. 249
Chapter 11 Exercises	p. 250
Beginning PL/SQL Examples: Anonymous BLOCKS Procedures, Functions, and Packages	p. 253
Anonymous Blocks	p. 254
Elementary Procedures with Sequence Structures	p. 255
A Simple Example of a Procedure	p. 256
Re-Using a Procedure	p. 257
Deleting a Procedure	p. 257
Adding a Parameter List to a Procedure	p. 258
Performing More than One Action in a Procedure	p. 258
Procedures with Selection and Iteration	p. 260
Example of a Procedure with Selection	p. 260
Example of a Procedure with Iteration	p. 260
Functions	p. 262
Example of a Function	p. 262
Deleting a Function	p. 263
Packages	p. 263
Creating a Package	p. 264
Another Approach to Creating This Package	p. 266
Deleting a Package	p. 267
Defining a PL/SQL INDEX BY Table	p. 267
Using a PL/SQL Table	p. 269
Chapter 12 Exercises	p. 270
References	p. 270
Introduction to Triggers	p. 271
What Is a Trigger?	p. 271
A Simple Trigger Example	p. 272
How the Trigger Worked	p. 274
Row-Level Triggers versus Statement-Level Triggers	p. 274
Enabling and Disabling Triggers	p. 275
Enabling All Triggers for a Table	p. 275
Deleting Triggers	p. 275
Values in the Trigger	p. 275
Using WHEN	p. 276
Performance Issues Using WHEN	p. 277
A Trigger Where One Table Affects Another Trigger	p. 277
Mutating Tables	p. 280
Chapter 13 Exercises	p. 281
References	p. 283
Getting Started with Oracle in the UNIX System	p. 285
Getting Started on Oracle in UNIX	p. 285
Signing onto Oracle in UNIX	p. 285
The HELP Command with Oracle Under UNIX	p. 286
Editing SQL Commands in UNIX	p. 287
Editing SQL Commands Using an Editor	p. 288
Defining an Editor	p. 288
Editing the Buffer	p. 289
Saving the Buffer	p. 290
Using GET	p. 290
Using a Script File to Save Your Query	p. 291
Editing SQL Commands Using SQLPLUS	p. 291
Using the CHANGE Command	p. 291
Using the LIST Command	p. 292
Using Other Commands	p. 293
Some UNIX Commands	p. 294
Summary Table	p. 296
Other Miscellaneous Commands	p. 296
Editors	p. 297
Using vi as Your Editor	p. 297
Other vi Commands	p. 298
Using Joe as Your Editor	p. 298
Appendix 1 Exercises	p. 298
The Data Dictionary	p. 299
Beginning to Explore the Data Dictionary	p. 300
A Paradigm for Choosing a View from the Dictionary	p. 302
Describing the Dictionary Itself	p. 303
Choosing the View You Want to See	p. 303
Describing the View You Want to See (DESC ALL_CATALOG)	p. 304
Finding the Right Attributes	p. 305
Finding out How Many Rows There Are in the View	p. 305
Views of TABLES	p. 306
Other Objects: Tablespaces and Constraints	p. 306
Views of Tablespaces	p. 306
Views of Constraints	p. 308
Appendix 2 Exercises	p. 309
References	p. 310
The Student Database and Other Tables Used in this Book	p. 311
The Student-course Database	p. 311
Entity Relationship Diagram of the Student-course Database	p. 312
Other Tables Used in this Book	p. 313
Improvements in Oracle 8i and 9i	p. 315
Improvements in Oracle 8i	p. 315
Improvements in Data Dictionary Features in Oracle 8i	p. 316
Packages Added in Oracle 8i	p. 317
Improvements in Oracle 9i	p. 318
Improvements in Data Dictionary Features in Oracle 9i	p. 319
Packages Added in Oracle 9i	p. 319
References	p. 319
Glossary of Terms	p. 321
Glossary of Important Commands and Functions	p. 329
Index of Terms	p. 337
Index of Important Commands and Functions	p. 339
Index	p. 341
Table of Contents provided by Syndetics. All Rights Reserved.