deHaan / Morton / Gorman Beginning Oracle SQL

1. ed
ISBN: 978-1-4302-7196-3
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 459 Seiten

is your introduction to the interactive query tools and specific dialect of SQL used with Oracle Database. The book is a revision of the classic by Lex de Haan, and has been updated to cover developments in Oracle's version of the SQL query language. Written in an easygoing and example-based style, is the book that will get you started down the path to successfully writing SQL statements and getting results from Oracle database.

• Takes an example-based approach, with clear and authoritative explanations
• Introduces both SQL and the query tools used to execute SQL statements
• Shows how to create tables, populate them with data, and then query that data to generate business results

Autoren/Hrsg.

deHaan, Lex

Morton, Karen

Gorman, Tim

Jorgensen, Inger

Fink, Daniel

Morton, Andrew

Inhaltsverzeichnis

1;Titlte Page ;1
2;Copyright Page;2
3;Contents at a Glance;3
4;Table of Contents;4
5;About the Authors;17
6;Acknowledgments;19
7;Introduction;21
7.1;About this Book;21
7.2;About the Chapters of this Book;22
7.3;About the Case Tables;23
8;Chapter 1: Relational Database Systems and Oracle;24
8.1;1.1 Information Needs and Information Systems;24
8.2;1.2 Database Design;25
8.2.1;Entities and Attributes;25
8.2.2;Generic vs. Specific;26
8.2.3;Redundancy;27
8.2.4;Consistency, Integrity, and Integrity Constraints;28
8.2.5;Data Modeling Approach, Methods, and Techniques;29
8.2.6;Semantics;30
8.2.7;Information Systems Terms Review;30
8.3;1.3 Database Management Systems;30
8.3.1;DBMS Components;31
8.3.1.1;Kernel;31
8.3.1.2;Data Dictionary;31
8.3.1.3;Query Languages;31
8.3.1.4;DBMS Tools;32
8.3.2;Database Applications;32
8.3.3;DBMS Terms Review;32
8.4;1.4 Relational Database Management Systems;33
8.5;1.5 Relational Data Structures;33
8.5.1;Tables, Columns, and Rows;34
8.5.2;The Information Principle;35
8.5.3;Datatypes;35
8.5.4;Keys;35
8.5.5;Missing Information and Null Values;36
8.5.6;Constraint Checking;37
8.5.7;Predicates and Propositions;37
8.5.8;Relational Data Structure Terms Review;37
8.6;1.6 Relational Operators;38
8.7;1.7 How Relational Is My DBMS?;39
8.8;1.8 The Oracle Software Environment;40
8.9;1.9 Case Tables;42
8.9.1;The ERM Diagram of the Case;42
8.9.2;Table Descriptions;44
9;Chapter 2: Introduction to SQL, AQL*Plus, and SQL Developer;48
9.1;2.1 Overview of SQL;48
9.1.1;Data Definition;49
9.1.2;Data Manipulation and Transactions;49
9.1.3;Retrieval;50
9.1.4;Security;52
9.1.4.1;Privileges and Roles;52
9.1.4.2;GRANT and REVOKE;54
9.2;2.2 Basic SQL Concepts and Terminology;55
9.2.1;Constants (Literals);55
9.2.2;Variables;57
9.2.3;Operators, Operands, Conditions, and Expressions;57
9.2.3.1;Arithmetic Operators;58
9.2.3.2;The Alphanumeric Operator: Concatenation;58
9.2.3.3;Comparison Operators;58
9.2.3.4;Logical Operators;59
9.2.3.5;Expressions;59
9.2.4;Functions;60
9.2.5;Database Object Naming;61
9.2.6;Comments;62
9.2.7;Reserved Words;62
9.3;2.3 Introduction to SQL*Plus;62
9.3.1;Entering Commands;63
9.3.2;Using the SQL Buffer;64
9.3.3;Using an External Editor;65
9.3.4;Using the SQL*Plus Editor;66
9.3.4.1;Using SQL Buffer Line Numbers;69
9.3.4.2;Using the Ellipsis;71
9.3.4.3;SQL*Plus Editor Command Review;71
9.3.5;Saving Commands;72
9.3.6;Running SQL*Plus Scripts;74
9.3.7;Specifying Directory Path Specifications;75
9.3.8;Adjusting SQL*Plus Settings;76
9.3.9;Spooling a SQL*Plus Session;79
9.3.10;Describing Database Objects;80
9.3.11;Executing Commands from the Operating System;80
9.3.12;Clearing the Buffer and the Screen;80
9.3.13;SQL*Plus Command Review;80
9.4;2.4 Introduction to SQL Developer;81
9.4.1;Installing and Configuring SQL Developer;81
9.4.2;Connecting to a Database;84
9.4.3;Exploring Objects;85
9.4.4;Entering Commands;86
9.4.4.1;Run Statement;87
9.4.4.2;Run Script;88
9.4.5;Saving Commands to a Script;89
9.4.6;Running a Script;90
10;Chapter 3: Data Definition, Part I;93
10.1;3.1 Schemas and Users;93
10.2;3.2 Table Creation;94
10.3;3.3 Datatypes;95
10.4;3.4 Commands for Creating the Case Tables;97
10.5;3.5 The Data Dictionary;99
11;Chapter 4: Retrieval: The Basics;104
11.1;4.1 Overview of the SELECT Command;104
11.2;4.2 The SELECT Clause;106
11.2.1;Column Aliases;107
11.2.2;The DISTINCT Keyword;108
11.2.3;Column Expressions;108
11.2.3.1;The DUAL Table;109
11.2.3.2;Null Values in Expressions;111
11.3;4.3 The WHERE Clause;111
11.4;4.4 The ORDER BY Clause;112
11.5;4.5 AND, OR, and NOT;115
11.5.1;The OR Operator;115
11.5.2;The AND Operator and Operator Precedence Issues;116
11.5.3;The NOT Operator;117
11.6;4.6 BETWEEN, IN, and LIKE;119
11.6.1;The BETWEEN Operator;119
11.6.2;The IN Operator;120
11.6.3;The LIKE Operator;121
11.7;4.7 CASE Expressions;122
11.8;4.8 Subqueries;125
11.8.1;The Joining Condition;126
11.8.2;When a Subquery Returns Too Many Values;127
11.8.3;Comparison Operators in the Joining Condition;128
11.8.4;When a Single-Row Subquery Returns More Than One Row;129
11.9;4.9 Null Values;130
11.9.1;Null Value Display;130
11.9.2;The Nature of Null Values;130
11.9.3;The IS NULL Operator;132
11.9.4;Null Values and the Equality Operator;133
11.9.5;Null Value Pitfalls;134
11.10;4.10 Truth Tables;135
11.11;4.11 Exercises;137
12;Chapter 5: Retrieval: Functions;138
12.1;5.1 Overview of Functions;138
12.2;5.2 Arithmetic Functions;140
12.3;5.3 Text Functions;142
12.4;5.4 Regular Expressions;146
12.4.1;Regular Expression Operators and Metasymbols;147
12.4.2;Regular Expression Function Syntax;148
12.4.2.1;Influencing Matching Behavior;148
12.4.2.2;REGEXP_INSTR Return Value;149
12.4.3;REGEXP_LIKE;149
12.4.4;REGEXP_INSTR;150
12.4.5;REGEXP_SUBSTR;151
12.4.6;REGEXP_REPLACE;151
12.5;5.5 Date Functions;152
12.5.1;EXTRACT;153
12.5.2;ROUND and TRUNC;154
12.5.3;MONTHS_BETWEEN and ADD_MONTHS;154
12.5.4;NEXT_DAY and LAST_DAY;155
12.6;5.6 General Functions;155
12.6.1;GREATEST and LEAST;156
12.6.2;NVL;157
12.6.3;DECODE;157
12.7;5.7 Conversion Functions;158
12.7.1;TO_NUMBER and TO_CHAR;159
12.7.2;Conversion Function Formats;160
12.7.3;Datatype Conversion;162
12.7.4;CAST;162
12.8;5.8 Stored Functions;163
12.9;5.9 Exercises;164
13;Chapter 6: Data Manipulation;166
13.1;6.1 The INSERT Command;167
13.1.1;Standard INSERT Commands;167
13.1.2;INSERT Using Subqueries;170
13.2;6.2 The UPDATE Command;172
13.3;6.3 The DELETE Command;175
13.4;6.4 The MERGE Command;178
13.5;6.5 Transaction Processing;180
13.6;6.6 Locking and Read Consistency;181
13.6.1;Locking;181
13.6.2;Read Consistency;182
14;Chapter 7: Data Definition, Part II;183
14.1;7.1 The CREATE TABLE Command;183
14.2;7.2 More on Datatypes;185
14.2.1;Character Datatypes;186
14.2.1.1;Comparison Semantics;186
14.2.1.2;Column Data Interpretation;187
14.2.2;Numbers Revisited;187
14.3;7.3 The ALTER TABLE and RENAME Commands;187
14.4;7.4 Constraints;190
14.4.1;Out-of-Line Constraints;190
14.4.2;Inline Constraints;192
14.4.3;Constraint Definitions in the Data Dictionary;193
14.4.4;Case Table Definitions with Constraints;194
14.4.5;A Solution for Foreign Key References: CREATE SCHEMA;196
14.4.6;Deferrable Constraints;197
14.5;7.5 Indexes;198
14.5.1;Index Creation;199
14.5.1.1;Unique Indexes;200
14.5.1.2;Bitmap Indexes;200
14.5.1.3;Function-Based Indexes;200
14.5.2;Index Management;201
14.6;7.6 Performance Monitoring with SQL Developer AUTOTRACE;202
14.7;7.7 Sequences;205
14.8;7.8 Synonyms;206
14.9;7.9 The CURRENT_SCHEMA Setting;208
14.10;7.10 The DROP TABLE Command;209
14.11;7.11 The TRUNCATE Command;211
14.12;7.12 The COMMENT Command;211
14.13;7.13 Exercises;213
15;Chapter 8: Retrieval: Multiple Tables and Aggregation;214
15.1;8.1 Tuple Variables;214
15.2;8.2 Joins;216
15.2.1;Cartesian Products;217
15.2.2;Equijoins;217
15.2.3;Non-equijoins;218
15.2.4;Joins of Three or More Tables;219
15.2.5;Self-Joins;220
15.3;8.3 The JOIN Clause;221
15.3.1;Natural Joins;222
15.3.2;Equijoins on Columns with the Same Name;223
15.4;8.4 Outer Joins;224
15.4.1;Old Oracle-Specific Outer Join Syntax;225
15.4.2;New Outer Join Syntax;226
15.4.3;Outer Joins and Performance;227
15.5;8.5 The GROUP BY Component;227
15.5.1;Multiple-Column Grouping;229
15.5.2;GROUP BY and Null Values;229
15.6;8.6 Group Functions;230
15.6.1;Group Functions and Duplicate Values;231
15.6.2;Group Functions and Null Values;232
15.6.3;Grouping the Results of a Join;233
15.6.4;The COUNT(*) Function;233
15.6.5;Valid SELECT and GROUP BY Clause Combinations;235
15.7;8.7 The HAVING Clause;236
15.7.1;The Difference Between WHERE and HAVING;237
15.7.2;HAVING Clauses Without Group Functions;237
15.7.3;A Classic SQL Mistake;238
15.7.4;Grouping on Additional Columns;239
15.8;8.8 Advanced GROUP BY Features;241
15.8.1;GROUP BY ROLLUP;241
15.8.2;GROUP BY CUBE;242
15.8.3;CUBE, ROLLUP, and Null Values;243
15.8.3.1;The GROUPING Function;243
15.8.3.2;The GROUPING_ID Function;244
15.9;8.9 Partitioned Outer Joins;245
15.10;8.10 Set Operators;247
15.11;8.11 Exercises;250
16;Chapter 9: Retrieval: Some Advanced Features;252
16.1;9.1 Subqueries Continued;252
16.1.1;The ANY and ALL Operators;253
16.1.1.1;Defining ANY and ALL;254
16.1.1.2;Rewriting SQL Statements Containing ANY and ALL;255
16.1.2;Correlated Subqueries;256
16.1.3;The EXISTS Operator;257
16.1.3.1;Subqueries Following an EXISTS Operator;258
16.1.3.2;EXISTS, IN, or JOIN?;258
16.1.3.3;NULLS with NOT EXISTS and NOT IN;261
16.2;9.2 Subqueries in the SELECT Clause;262
16.3;9.3 Subqueries in the FROM Clause;263
16.4;9.4 The WITH Clause;264
16.5;9.5 Hierarchical Queries;266
16.5.1;START WITH and CONNECT BY;267
16.5.2;LEVEL, CONNECT_BY_ISCYCLE, and CONNECT_BY_ISLEAF;268
16.5.3;CONNECT_BY_ROOT and SYS_CONNECT_BY_PATH;269
16.5.4;Hierarchical Query Result Sorting;270
16.6;9.6 Analytical Functions;271
16.6.1;Partitions;273
16.6.2;Function Processing;276
16.7;9.7 Flashback Features;278
16.7.1;AS OF;279
16.7.2;VERSIONS BETWEEN;281
16.7.3;FLASHBACK TABLE;281
16.8;9.8 Exercises;283
17;Chapter 10: Views;284
17.1;10.1 What Are Views?;284
17.2;10.2 View Creation;285
17.2.1;Creating a View from a Query;286
17.2.2;Getting Information About Views from the Data Dictionary;288
17.2.3;Replacing and Dropping Views;290
17.3;10.3 What Can You Do with Views?;290
17.3.1;Simplifying Data Retrieval;290
17.3.2;Maintaining Logical Data Independence;292
17.3.3;Implementing Data Security;293
17.4;10.4 Data Manipulation via Views;293
17.4.1;Updatable Join Views;295
17.4.2;Nonupdatable Views;296
17.4.3;The WITH CHECK OPTION Clause;297
17.4.3.1;Disappearing Updated Rows;297
17.4.3.2;Inserting Invisible Rows;298
17.4.3.3;Preventing These Two Scenarios;299
17.4.3.4;Constraint Checking;299
17.5;10.5 Data Manipulation via Inline Views;300
17.6;10.6 Views and Performance;301
17.7;10.7 Materialized Views;302
17.7.1;Properties of Materialized Views;303
17.7.2;Query Rewrite;303
17.8;10.8 Exercises;305
18;Chapter 11: Writing and Automating SQL*Plus Scripts;306
18.1;11.1 SQL*Plus Variables;307
18.1.1;SQL*Plus Substitution Variables;307
18.1.2;SQL*Plus User-Defined Variables;309
18.1.2.1;Implicit SQL*Plus User-Defined Variables;310
18.1.2.2;User-Friendly Prompting;311
18.1.3;SQL*Plus System Variables;312
18.2;11.2 Bind Variables;317
18.2.1;Bind Variable Declaration;318
18.2.2;Bind Variables in SQL Statements;319
18.3;11.3 SQL*Plus Scripts;320
18.3.1;Script Execution;320
18.3.2;Script Parameters;321
18.3.3;SQL*Plus Commands in Scripts;323
18.3.4;The login.sql Script;324
18.4;11.4 Report Generation with SQL*Plus;325
18.4.1;The SQL*Plus COLUMN Command;326
18.4.2;The SQL*Plus TTITLE and BTITLE Commands;330
18.4.3;The SQL*Plus BREAK Command;331
18.4.4;The SQL*Plus COMPUTE Command;334
18.4.5;The Finishing Touch: SPOOL;336
18.5;11.5 HTML in SQL*Plus;337
18.5.1;HTML in SQL*Plus;337
18.6;11.6 Building SQL*Plus Scripts for Automation;340
18.6.1;What Is a SQL*Plus Script?;340
18.6.2;Capturing and Using Input Parameter Values;341
18.6.3;Passing Data Values from One SQL Statement to Another;342
18.6.3.1;Mechanism 1: The NEW_VALUE Clause;342
18.6.3.2;Mechanism 2: Bind Variables;343
18.6.4;Handling Error Conditions;344
18.7;11.7 Exercises;345
19;Chapter 12: Object-Relational Features;347
19.1;12.1 More Datatypes;347
19.1.1;Collection Datatypes;348
19.1.2;Methods;348
19.2;12.2 Varrays;349
19.2.1;Creating the Array;349
19.2.2;Populating the Array with Values;351
19.2.3;Querying Array Columns;352
19.3;12.3 Nested Tables;354
19.3.1;Creating Table Types;354
19.3.2;Creating the Nested Table;354
19.3.3;Populating the Nested Table;355
19.3.4;Querying the Nested Table;356
19.4;12.4 User-Defined Types;357
19.4.1;Creating User-Defined Types;357
19.4.2;Showing More Information with DESCRIBE;358
19.5;12.5 Multiset Operators;359
19.5.1;Which SQL Multiset Operators Are Available?;359
19.5.2;Preparing for the Examples;360
19.5.3;Using IS NOT EMPTY and CARDINALITY;361
19.5.4;Using POWERMULTISET;362
19.5.5;Using MULTISET UNION;363
19.5.6;Converting Arrays into Nested Tables;364
19.6;12.6 Exercises;364
20;Appendix A: The Seven Case Tables;366
20.1;ERM Diagram;366
20.2;Table Structure Descriptions;367
20.3;Columns and Foreign Key Constraints;368
20.4;Contents of the Seven Tables;369
20.5;Hierarchical Employees Overview;374
20.6;Course Offerings Overview;374
21;Appendix B: Answers to the Exercises;376
21.1;Chapter 4 Exercises;376
21.2;Chapter 5 Exercises;386
21.3;Chapter 7 Exercises;391
21.4;Chapter 8 Exercises;393
21.5;Chapter 9 Exercises;403
21.6;Chapter 10 Exercises;412
21.7;Chapter 11 Exercises;414
21.8;Chapter 12 Exercises;418
22;Index;422