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

Beginning Oracle SQL 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 Mastering Oracle SQL and SQL*Plus 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, Beginning Oracle SQL 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

Lex de Haan studied applied mathematics at the University of Technology in Delft, the Netherlands. His experience with Oracle goes back to the mid-1980s, version 4. He worked for Oracle Corporation from 1990 to 2004 in various education-related roles, ending up in Server Technologies (product development) as senior curriculum manager for the advanced database administration curriculum. In that role, he was involved in the development of Oracle9i and Oracle Database 10g. In March 2004, he decided to go independent and founded Natural Join B.V. (http://www.naturaljoin.nl). From 1999 until his passing in 2006, he was involved in the ISO SQL language standardization process, as a member of the Dutch national body. He was also one of the founding members of the OakTable network (http://www.oaktable.net).

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