Suchen und Finden
Contents
5
Foreword
9
About the Author
10
About the Technical Reviewers
11
Acknowledgments
12
Introduction
13
Chapter 1 Relational Database Systems and Oracle
17
1.1 Information Needs and Information Systems
17
1.2 Database Design
18
Entities and Attri
19
Generic vs. Specific
20
Redundancy
20
Consistency, Integrity, and Integrity Constraints
21
Data Modeling Approach,Methods, and Techniques
22
Semantics
23
Information Systems Terms Review
23
1.3 Database Management Systems
24
DBMS Components
25
Database Applications
26
DBMS Terms Review
26
1.4 Relational Database Management Systems
26
1.5 Relational Data Structures
27
Tables, Columns, and Rows
27
The Information Principle
28
Datatypes
28
Keys
29
Missing Information and Null Values
29
Constraint Checking
30
Predicates and Propositions
30
Relational Data Structure Terms Review
30
1.6 Relational Operators
31
1.7 How Relational Is My DBMS?
32
1.8 The Oracle Software Environment
34
1.9 Case Tables
35
The ERM Diagram of the Case
35
Chapter 2 Introduction to SQL, iSQL*Plus,and SQL*Plus
41
2.1 Overview of SQL
41
Data Definition
42
Data Manipulation and Transactions
42
Retrieval
43
Security
45
2.2 Basic SQL Concepts and Terminology
48
Constants (Literals)
48
Variables
50
Operators,Operands, Conditions, and Expressions
50
Functions
53
Database Object Naming
53
Comments
54
Reserved Words
54
2.3 Introduction to iSQL*Plus
55
2.4 Introduction to SQL*Plus
59
Entering Commands
60
Using the SQL Buffer
61
Using an External Editor
62
Using the SQL*Plus Editor
63
Saving Commands
70
Running SQL*Plus Scripts
71
Adjusting SQL*Plus Settings
73
Describing Database Objects
78
Executing Commands from the Operating System
79
Clearing the Buffer and the Screen
79
SQL*Plus Command Review
79
Chapter 3 Data Definition, Part I
81
3.1 Schemas and Users
81
3.2 Table Creation
82
3.3 Datatypes
83
3.4 Commands for Creating the Case Tables
85
3.5 The Data Dictionary
87
Chapter 4 Retrieval: The Basics
92
4.1 Overview of the SELECT Command
92
4.2 The SELECT Clause
94
Column Aliases
95
The DISTINCT Keyword
96
Column Expressions
97
4.3 The WHERE Clause
100
4.4 The ORDER BY Clause
101
4.5 AND, OR, and NOT
104
The OR Operator
104
The AND Operator and Operator Precedence Issues
105
The NOT Operator
106
4.6 BETWEEN, IN, and LIKE
108
The BETWEEN Operator
108
The IN Operator
109
The LIKE Operator
110
4.7 CASE Expressions
112
4.8 Subqueries
115
The Joining Condition
116
When a Subquery Returns Too Many Values
117
Comparison Operators in the Joining Condition
118
When a Single-Row Subquery Returns More Than One Row
119
4.9 Null Values
120
Null Value Display
120
The Nature of Null Values
121
The IS NULL Operator
122
Null Values and the Equality Operator
123
Null Value Pitfalls
124
4.10 Truth Tables
125
4.11 Exercises
126
Chapter 5 Retrieval: Functions
128
5.1 Overview of Functions
128
5.2 Arithmetic Functions
130
5.3 Text Functions
132
5.4 Regular Expressions
136
Regular Expression Operators and Metasymbols
137
Regular Expression Function Syntax
138
REGEXP_LIKE
139
REGEXP_INSTR
140
REGEXP_SUBST
141
REGEXP_REPLACE
141
5.5 Date Functions
142
EXTRACT
143
ROUND and TRUNC
143
MONTHS_BETWEEN and ADD_MONTHS
144
NEXT_DAY and LAST_DAY
144
5.6 General Functions
145
GREATEST and LEAST
146
NVL
146
DECODE
147
5.7 Conversion Functions
147
TO_NUMBER and TO_CHAR
148
Conversion Function Formats
149
Datatype Conversion
151
5.8 Stored Functions
152
5.9 Exercises
154
Chapter 6 Data Manipulation
155
6.1 The INSERT Command
155
Standard INSERT Commands
155
Multitable INSERT Commands
158
6.2 The UPDATE Command
159
6.3 The DELETE Command
161
6.4 The MERGE Command
163
6.5 Transaction Processing
165
The SQL*Plus AUTOCOMMIT Option
166
Transaction Design
167
Savepoints
167
6.6 Locking and Read Consistency
168
Locking
169
Read Consistency
169
Chapter 7 Data Definition, Part II
172
7.1 The CREATE TABLE Command
172
7.2 More on Datatypes
174
Character Datatypes
175
Numbers Revisited
176
7.3 The ALTER TABLE and RENAME Commands
176
7.4 Constraints
179
Out-of-Line Constraints
179
Inline Constraints
181
Constraint Definitions in the Data Dictionary
182
Case Table Definitions with Constraints
183
A Solution for Foreign Key References: CREATE SCHEMA
185
Deferrable Constraints
186
7.5 Indexes
187
7.6 Performance Monitoring with SQL*Plus AUTOTRACE
191
7.7 Sequences
194
7.8 Synonyms
196
7.9 The CURRENT_SCHEMA Setting
198
7.10 The DROP TABLE Command
199
7.11 The TRUNCATE Command
201
7.12 The COMMENT Command
201
7.13 Exercises
202
Chapter 8 Retrieval: Multiple Tables and Aggregation
204
8.1 Tuple Variables
205
8.2 Joins
207
Cartesian Products
207
Equijoin
208
Non-equijoins
209
Joins of Three or More Tables
210
Self-Joins
211
8.3 Alternative ANSI/ISO Standard Join Syntax
212
Natural Joins
213
Equijoins on Columns with the Same Name
214
8.4 Outer Joins
215
New Outer Join Syntax
217
Outer Joins and Performance
218
8.5 The GROUP BY Component
219
Multiple-Column Grouping
220
GROUP BY and Null Values
221
8.6 Group Functions
222
Group Functions and Duplicate Values
223
Group Functions and Null Values
223
Grouping the Results of a Join
225
The COUNT(*) Function
225
Valid SELECT and GROUP BY Clause Combinations
227
8.7 The HAVING Clause
228
HAVING Clauses Without Group Functions
229
A Classic SQL Mistake
230
Grouping on Additional Columns
231
8.8 Advanced GROUP BY Features
233
GROUP BY ROLLUP
234
GROUP BY CUBE
234
CUBE, ROLLUP, and Null Values
235
8.9 Partitioned Outer Joins
238
8.10 Set Operators
240
8.11 Exercises
244
Chapter 9 Retrieval: Some Advanced Features
245
9.1 Subqueries Continued
245
The ANY and ALL Operators
247
Correlated Subqueries
249
The EXISTS Operator
250
9.2 Subqueries in the SELECT Clause
253
9.3 Subqueries in the FROM Clause
254
9.4 The WITH Clause
256
9.5 Hierarchical Queries
257
START WITH and CONNECT BY
258
LEVEL, CONNECT_BY_ISCYCLE, and CONNECT_BY_ISLEAF
260
CONNECT_BY_ROOT and SYS_CONNECT_BY_PATH
261
Hierarchical Query Result Sorting
262
9.6 Analytical Functions and Windows
263
Analytical Window Specification
264
Analytical Window Ordering
265
Partitioned Analytical Windows
267
9.7 Flashback Features
269
9.8 Exercises
274
Chapter 10 Views
276
10.1 What Are Views?
276
10.2 View Creation
277
Creating a View from a Query
279
Getting Information About Views from the Data Dictionary
280
Replacing and Dropping Views
282
10.3 What Can You Do with Views?
282
Simplifying Data Retrieval
282
Maintaining Logical Data Independence
285
Implementing Data Security
285
10.4 Data Manipulation via Views
285
Updatable Join Views
287
Nonupdatable Views
288
The WITH CHECK OPTION Clause
290
10.5 Data Manipulation via Inline Views
293
10.6 Views and Performance
294
10.7 Materialized Views
295
Properties of Materialized Views
296
Query Rewrite
296
10.8 Exercises
297
Chapter 11 SQL*Plus and iSQL*Plus
299
11.1 SQL*Plus Variables
300
SQL*Plus Substitution Variables
300
SQL*Plus User-Defined Variables
302
SQL*Plus System Variables
305
11.2 Bind Variables
310
Bind Variable Declaration
311
Bind Variables in SQL Statements
312
11.3 SQL*Plus Scripts
313
Script Execution
313
Script Parameters
315
SQL*Plus Commands in Scripts
316
The login.sql Script
318
11.4 Report Generation with SQL*Plus
318
The SQL*Plus COLUMN Command
319
The SQL*Plus TTITLE and BTITLE Commands
323
The SQL*Plus BREAK Command
324
The SQL*Plus COMPUTE Command
327
The Finishing Touch: SPOOL
329
11.5 HTML in SQL*Plus and iSQL*Plus
330
HTML in SQL*Plus
330
HTML in iSQL*Plus
333
11.6 Exercises
335
Chapter 12 Object-Relational Features
336
12.1 More Datatypes
336
Collection Datatypes
337
Methods
337
12.2 Varrays
338
Creating the Array
338
Populating the Array with Values
340
Querying Array Columns
341
12.3 Nested Tables
343
Creating Table Types
343
Creating the Nested Table
344
Populating the Nested Table
345
Querying the Nested Table
346
12.4 User-Defined Types
347
Creating User-Defined Types
347
Showing More Information with DESCRIBE
348
12.5 Multiset Operators
349
Which SQL Multiset Operators Are Available?
349
Preparing for the Examples
350
Using IS NOT EMPTY and CARDINALITY
352
Using POWERMULTISET
352
Using MULTISET UNION
354
Converting Arrays into Nested Tables
354
12.6 Exercises
355
APPENDIX A Quick Reference to SQL and SQL*Plus
357
Syntax Conventions Used in This Appendix
358
Starting and Stopping
359
Entering and Executing Commands
359
Working With the SQL*Plus Editor
360
Manipulating SQL*Plus Scripts
361
SQL*Plus Interactivity Commands
361
Variables and Parameters
362
Formatting Query Results
363
SQL: Data Manipulation (DML), Transactions, and Queries
366
SQL: Data Definition (DDL)
367
SQL: Other Commands
369
SQL: Operators
370
SQL: Functions
371
SQL: Regular Expressions
377
Rules for Naming Oracle Database Objects
378
SQL: Reserved Words
379
APPENDIX B Data Dictionary Overview
380
General Data Dictionary Views
381
ALL Views: Information About Accessible Objects
381
USER Views: Information About Your Own Data
382
DBA Views: Full Database Information
384
V$ Views: Dynamic Performance Views
385
APPENDIX C The Seven Case Tables
387
ERM Diagram
388
Table Structure Descriptions
389
Columns and Foreign Key Constraints
390
Contents of the Seven Tables
391
Hierarchical Employees Overview
396
Course Offerings Overview
397
APPENDIX D Answers to the Exercises
398
Chapter 4 Exercises
399
Chapter 5 Exercises
409
Chapter 7 Exercises
414
Chapter 8 Exercises
416
Chapter 9 Exercises
427
Chapter 10 Exercises
437
Chapter 11 Exercises
439
Chapter 12 Exercises
443
APPENDIX E Oracle Documentation, Web Sites, and Bibliography
448
Oracle Documentation
448
Oracle Web Sites
451
Bibliography
452
Index
453
Alle Preise verstehen sich inklusive der gesetzlichen MwSt.