deHaan Mastering Oracle SQL and SQL*Plus

1. ed
ISBN: 978-1-4302-0000-0
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 488 Seiten

*Ideal for anyone who wants to learn SQL programming for Oracle database. *Author has 25 years of teaching experience; 14 years of curriculum develoment experience; 14 years of experience with the Orcle database. *Book can be used as collateral/handouts for SQL training courses at universities/ high schools.

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

Inhaltsverzeichnis

1;Contents;5
2;Foreword;9
3;About the Author;10
4;About the Technical Reviewers;11
5;Acknowledgments;12
6;Introduction;13
7;Chapter 1 Relational Database Systems and Oracle;17
7.1;1.1 Information Needs and Information Systems;17
7.2;1.2 Database Design;18
7.2.1;Entities and Attri;19
7.2.2;Generic vs. Specific;20
7.2.3;Redundancy;20
7.2.4;Consistency, Integrity, and Integrity Constraints;21
7.2.5;Data Modeling Approach,Methods, and Techniques;22
7.2.6;Semantics;23
7.2.7;Information Systems Terms Review;23
7.3;1.3 Database Management Systems;24
7.3.1;DBMS Components;25
7.3.2;Database Applications;26
7.3.3;DBMS Terms Review;26
7.4;1.4 Relational Database Management Systems;26
7.5;1.5 Relational Data Structures;27
7.5.1;Tables, Columns, and Rows;27
7.5.2;The Information Principle;28
7.5.3;Datatypes;28
7.5.4;Keys;29
7.5.5;Missing Information and Null Values;29
7.5.6;Constraint Checking;30
7.5.7;Predicates and Propositions;30
7.5.8;Relational Data Structure Terms Review;30
7.6;1.6 Relational Operators;31
7.7;1.7 How Relational Is My DBMS?;32
7.8;1.8 The Oracle Software Environment;34
7.9;1.9 Case Tables;35
7.9.1;The ERM Diagram of the Case;35
8;Chapter 2 Introduction to SQL, iSQL*Plus,and SQL*Plus;41
8.1;2.1 Overview of SQL;41
8.1.1;Data Definition;42
8.1.2;Data Manipulation and Transactions;42
8.1.3;Retrieval;43
8.1.4;Security;45
8.2;2.2 Basic SQL Concepts and Terminology;48
8.2.1;Constants (Literals);48
8.2.2;Variables;50
8.2.3;Operators,Operands, Conditions, and Expressions;50
8.2.4;Functions;53
8.2.5;Database Object Naming;53
8.2.6;Comments;54
8.2.7;Reserved Words;54
8.3;2.3 Introduction to iSQL*Plus;55
8.4;2.4 Introduction to SQL*Plus;59
8.4.1;Entering Commands;60
8.4.2;Using the SQL Buffer;61
8.4.3;Using an External Editor;62
8.4.4;Using the SQL*Plus Editor;63
8.4.5;Saving Commands;70
8.4.6;Running SQL*Plus Scripts;71
8.4.7;Adjusting SQL*Plus Settings;73
8.4.8;Describing Database Objects;78
8.4.9;Executing Commands from the Operating System;79
8.4.10;Clearing the Buffer and the Screen;79
8.4.11;SQL*Plus Command Review;79
9;Chapter 3 Data Definition, Part I;81
9.1;3.1 Schemas and Users;81
9.2;3.2 Table Creation;82
9.3;3.3 Datatypes;83
9.4;3.4 Commands for Creating the Case Tables;85
9.5;3.5 The Data Dictionary;87
10;Chapter 4 Retrieval: The Basics;92
10.1;4.1 Overview of the SELECT Command;92
10.2;4.2 The SELECT Clause;94
10.2.1;Column Aliases;95
10.2.2;The DISTINCT Keyword;96
10.2.3;Column Expressions;97
10.3;4.3 The WHERE Clause;100
10.4;4.4 The ORDER BY Clause;101
10.5;4.5 AND, OR, and NOT;104
10.5.1;The OR Operator;104
10.5.2;The AND Operator and Operator Precedence Issues;105
10.5.3;The NOT Operator;106
10.6;4.6 BETWEEN, IN, and LIKE;108
10.6.1;The BETWEEN Operator;108
10.6.2;The IN Operator;109
10.6.3;The LIKE Operator;110
10.7;4.7 CASE Expressions;112
10.8;4.8 Subqueries;115
10.8.1;The Joining Condition;116
10.8.2;When a Subquery Returns Too Many Values;117
10.8.3;Comparison Operators in the Joining Condition;118
10.8.4;When a Single-Row Subquery Returns More Than One Row;119
10.9;4.9 Null Values;120
10.9.1;Null Value Display;120
10.9.2;The Nature of Null Values;121
10.9.3;The IS NULL Operator;122
10.9.4;Null Values and the Equality Operator;123
10.9.5;Null Value Pitfalls;124
10.10;4.10 Truth Tables;125
10.11;4.11 Exercises;126
11;Chapter 5 Retrieval: Functions;128
11.1;5.1 Overview of Functions;128
11.2;5.2 Arithmetic Functions;130
11.3;5.3 Text Functions;132
11.4;5.4 Regular Expressions;136
11.4.1;Regular Expression Operators and Metasymbols;137
11.4.2;Regular Expression Function Syntax;138
11.4.3;REGEXP_LIKE;139
11.4.4;REGEXP_INSTR;140
11.4.5;REGEXP_SUBST;141
11.4.6;REGEXP_REPLACE;141
11.5;5.5 Date Functions;142
11.5.1;EXTRACT;143
11.5.2;ROUND and TRUNC;143
11.5.3;MONTHS_BETWEEN and ADD_MONTHS;144
11.5.4;NEXT_DAY and LAST_DAY;144
11.6;5.6 General Functions;145
11.6.1;GREATEST and LEAST;146
11.6.2;NVL;146
11.6.3;DECODE;147
11.7;5.7 Conversion Functions;147
11.7.1;TO_NUMBER and TO_CHAR;148
11.7.2;Conversion Function Formats;149
11.7.3;Datatype Conversion;151
11.8;5.8 Stored Functions;152
11.9;5.9 Exercises;154
12;Chapter 6 Data Manipulation;155
12.1;6.1 The INSERT Command;155
12.1.1;Standard INSERT Commands;155
12.1.2;Multitable INSERT Commands;158
12.2;6.2 The UPDATE Command;159
12.3;6.3 The DELETE Command;161
12.4;6.4 The MERGE Command;163
12.5;6.5 Transaction Processing;165
12.5.1;The SQL*Plus AUTOCOMMIT Option;166
12.5.2;Transaction Design;167
12.5.3;Savepoints;167
12.6;6.6 Locking and Read Consistency;168
12.6.1;Locking;169
12.6.2;Read Consistency;169
13;Chapter 7 Data Definition, Part II;172
13.1;7.1 The CREATE TABLE Command;172
13.2;7.2 More on Datatypes;174
13.2.1;Character Datatypes;175
13.2.2;Numbers Revisited;176
13.3;7.3 The ALTER TABLE and RENAME Commands;176
13.4;7.4 Constraints;179
13.4.1;Out-of-Line Constraints;179
13.4.2;Inline Constraints;181
13.4.3;Constraint Definitions in the Data Dictionary;182
13.4.4;Case Table Definitions with Constraints;183
13.4.5;A Solution for Foreign Key References: CREATE SCHEMA;185
13.4.6;Deferrable Constraints;186
13.5;7.5 Indexes;187
13.6;7.6 Performance Monitoring with SQL*Plus AUTOTRACE;191
13.7;7.7 Sequences;194
13.8;7.8 Synonyms;196
13.9;7.9 The CURRENT_SCHEMA Setting;198
13.10;7.10 The DROP TABLE Command;199
13.11;7.11 The TRUNCATE Command;201
13.12;7.12 The COMMENT Command;201
13.13;7.13 Exercises;202
14;Chapter 8 Retrieval: Multiple Tables and Aggregation;204
14.1;8.1 Tuple Variables;205
14.2;8.2 Joins;207
14.2.1;Cartesian Products;207
14.2.2;Equijoin;208
14.2.3;Non-equijoins;209
14.2.4;Joins of Three or More Tables;210
14.2.5;Self-Joins;211
14.3;8.3 Alternative ANSI/ISO Standard Join Syntax;212
14.3.1;Natural Joins;213
14.3.2;Equijoins on Columns with the Same Name;214
14.4;8.4 Outer Joins;215
14.4.1;New Outer Join Syntax;217
14.4.2;Outer Joins and Performance;218
14.5;8.5 The GROUP BY Component;219
14.5.1;Multiple-Column Grouping;220
14.5.2;GROUP BY and Null Values;221
14.6;8.6 Group Functions;222
14.6.1;Group Functions and Duplicate Values;223
14.6.2;Group Functions and Null Values;223
14.6.3;Grouping the Results of a Join;225
14.6.4;The COUNT(*) Function;225
14.6.5;Valid SELECT and GROUP BY Clause Combinations;227
14.7;8.7 The HAVING Clause;228
14.7.1;HAVING Clauses Without Group Functions;229
14.7.2;A Classic SQL Mistake;230
14.7.3;Grouping on Additional Columns;231
14.8;8.8 Advanced GROUP BY Features;233
14.8.1;GROUP BY ROLLUP;234
14.8.2;GROUP BY CUBE;234
14.8.3;CUBE, ROLLUP, and Null Values;235
14.9;8.9 Partitioned Outer Joins;238
14.10;8.10 Set Operators;240
14.11;8.11 Exercises;244
15;Chapter 9 Retrieval: Some Advanced Features;245
15.1;9.1 Subqueries Continued;245
15.1.1;The ANY and ALL Operators;247
15.1.2;Correlated Subqueries;249
15.1.3;The EXISTS Operator;250
15.2;9.2 Subqueries in the SELECT Clause;253
15.3;9.3 Subqueries in the FROM Clause;254
15.4;9.4 The WITH Clause;256
15.5;9.5 Hierarchical Queries;257
15.5.1;START WITH and CONNECT BY;258
15.5.2;LEVEL, CONNECT_BY_ISCYCLE, and CONNECT_BY_ISLEAF;260
15.5.3;CONNECT_BY_ROOT and SYS_CONNECT_BY_PATH;261
15.5.4;Hierarchical Query Result Sorting;262
15.6;9.6 Analytical Functions and Windows;263
15.6.1;Analytical Window Specification;264
15.6.2;Analytical Window Ordering;265
15.6.3;Partitioned Analytical Windows;267
15.7;9.7 Flashback Features;269
15.8;9.8 Exercises;274
16;Chapter 10 Views;276
16.1;10.1 What Are Views?;276
16.2;10.2 View Creation;277
16.2.1;Creating a View from a Query;279
16.2.2;Getting Information About Views from the Data Dictionary;280
16.2.3;Replacing and Dropping Views;282
16.3;10.3 What Can You Do with Views?;282
16.3.1;Simplifying Data Retrieval;282
16.3.2;Maintaining Logical Data Independence;285
16.3.3;Implementing Data Security;285
16.4;10.4 Data Manipulation via Views;285
16.4.1;Updatable Join Views;287
16.4.2;Nonupdatable Views;288
16.4.3;The WITH CHECK OPTION Clause;290
16.5;10.5 Data Manipulation via Inline Views;293
16.6;10.6 Views and Performance;294
16.7;10.7 Materialized Views;295
16.7.1;Properties of Materialized Views;296
16.7.2;Query Rewrite;296
16.8;10.8 Exercises;297
17;Chapter 11 SQL*Plus and iSQL*Plus;299
17.1;11.1 SQL*Plus Variables;300
17.1.1;SQL*Plus Substitution Variables;300
17.1.2;SQL*Plus User-Defined Variables;302
17.1.3;SQL*Plus System Variables;305
17.2;11.2 Bind Variables;310
17.2.1;Bind Variable Declaration;311
17.2.2;Bind Variables in SQL Statements;312
17.3;11.3 SQL*Plus Scripts;313
17.3.1;Script Execution;313
17.3.2;Script Parameters;315
17.3.3;SQL*Plus Commands in Scripts;316
17.3.4;The login.sql Script;318
17.4;11.4 Report Generation with SQL*Plus;318
17.4.1;The SQL*Plus COLUMN Command;319
17.4.2;The SQL*Plus TTITLE and BTITLE Commands;323
17.4.3;The SQL*Plus BREAK Command;324
17.4.4;The SQL*Plus COMPUTE Command;327
17.4.5;The Finishing Touch: SPOOL;329
17.5;11.5 HTML in SQL*Plus and iSQL*Plus;330
17.5.1;HTML in SQL*Plus;330
17.5.2;HTML in iSQL*Plus;333
17.6;11.6 Exercises;335
18;Chapter 12 Object-Relational Features;336
18.1;12.1 More Datatypes;336
18.1.1;Collection Datatypes;337
18.1.2;Methods;337
18.2;12.2 Varrays;338
18.2.1;Creating the Array;338
18.2.2;Populating the Array with Values;340
18.2.3;Querying Array Columns;341
18.3;12.3 Nested Tables;343
18.3.1;Creating Table Types;343
18.3.2;Creating the Nested Table;344
18.3.3;Populating the Nested Table;345
18.3.4;Querying the Nested Table;346
18.4;12.4 User-Defined Types;347
18.4.1;Creating User-Defined Types;347
18.4.2;Showing More Information with DESCRIBE;348
18.5;12.5 Multiset Operators;349
18.5.1;Which SQL Multiset Operators Are Available?;349
18.5.2;Preparing for the Examples;350
18.5.3;Using IS NOT EMPTY and CARDINALITY;352
18.5.4;Using POWERMULTISET;352
18.5.5;Using MULTISET UNION;354
18.5.6;Converting Arrays into Nested Tables;354
18.6;12.6 Exercises;355
19;APPENDIX A Quick Reference to SQL and SQL*Plus;357
19.1;Syntax Conventions Used in This Appendix;358
19.2;Starting and Stopping;359
19.3;Entering and Executing Commands;359
19.4;Working With the SQL*Plus Editor;360
19.5;Manipulating SQL*Plus Scripts;361
19.6;SQL*Plus Interactivity Commands;361
19.7;Variables and Parameters;362
19.8;Formatting Query Results;363
19.9;SQL: Data Manipulation (DML), Transactions, and Queries;366
19.10;SQL: Data Definition (DDL);367
19.11;SQL: Other Commands;369
19.12;SQL: Operators;370
19.13;SQL: Functions;371
19.14;SQL: Regular Expressions;377
19.15;Rules for Naming Oracle Database Objects;378
19.16;SQL: Reserved Words;379
20;APPENDIX B Data Dictionary Overview;380
20.1;General Data Dictionary Views;381
20.2;ALL Views: Information About Accessible Objects;381
20.3;USER Views: Information About Your Own Data;382
20.4;DBA Views: Full Database Information;384
20.5;V$ Views: Dynamic Performance Views;385
21;APPENDIX C The Seven Case Tables;387
21.1;ERM Diagram;388
21.2;Table Structure Descriptions;389
21.3;Columns and Foreign Key Constraints;390
21.4;Contents of the Seven Tables;391
21.5;Hierarchical Employees Overview;396
21.6;Course Offerings Overview;397
22;APPENDIX D Answers to the Exercises;398
22.1;Chapter 4 Exercises;399
22.2;Chapter 5 Exercises;409
22.3;Chapter 7 Exercises;414
22.4;Chapter 8 Exercises;416
22.5;Chapter 9 Exercises;427
22.6;Chapter 10 Exercises;437
22.7;Chapter 11 Exercises;439
22.8;Chapter 12 Exercises;443
23;APPENDIX E Oracle Documentation, Web Sites, and Bibliography;448
23.1;Oracle Documentation;448
23.2;Oracle Web Sites;451
23.3;Bibliography;452
24;Index;453