Beginning Oracle SQL
Author
loveelok
Date
2017-01-19 12:49
Views
70240740
Contents at a Glance..........iii
Contents.............................iv
About the Authors...........xvii
Acknowledgments...........xix
Introduction.....................xxi
Chapter 1: Relational Database Systems and Oracle....1
Chapter 2: Introduction to SQL, AQL*Plus, and SQL Developer.............................25
Chapter 3: Data Definition, Part I................................71
Chapter 4: Retrieval: The Basics.................................83
Chapter 5: Retrieval: Functions................................117
Chapter 6: Data Manipulation...................................145
Chapter 7: Data Definition, Part II.............................163
Chapter 8: Retrieval: Multiple Tables and Aggregation......................................195
Chapter 9: Retrieval: Some Advanced Features.......233
Chapter 10: Views...........265
Chapter 11: Writing and Automating SQL*Plus Scripts......................................287
Chapter 12: Object-Relational Features....................329
Appendix A: The Seven Case Tables.........................349
Appendix B: Answers to the Exercises.....................359
Index...............................405
iii
CONTENTS
Contents
Contents at a Glance..........iii
Contents.............................iv
About the Authors...........xvii
Acknowledgments...........xix
Introduction.....................xxi
Chapter 1: Relational Database Systems and Oracle....1
1.1 Information Needs and Information Systems....................1
1.2 Database Design......................2
Entities and Attributes2
Generic vs. Specific....3
Redundancy................4
Consistency, Integrity, and Integrity Constraints..5
Data Modeling Approach, Methods, and Techniques.....................................6
Semantics...................7
Information Systems Terms Review.....................7
1.3 Database Management Systems.......................................7
DBMS Components.....8
Kernel....................8
Data Dictionary......8
Query Languages...8
DBMS Tools...........9
iv
CONTENTS
Database Applications9
DBMS Terms Review..9
1.4 Relational Database Management Systems....................10
1.5 Relational Data Structures.....10
Tables, Columns, and Rows...............................11
The Information Principle...................................12
Datatypes..................12
Keys..........................12
Missing Information and Null Values..................13
Constraint Checking.14
Predicates and Propositions...............................14
Relational Data Structure Terms Review............14
1.6 Relational Operators..............15
1.7 How Relational Is My DBMS?.16
1.8 The Oracle Software Environment...................................17
1.9 Case Tables...........................19
The ERM Diagram of the Case............................19
Table Descriptions....21
Chapter 2: Introduction to SQL, AQL*Plus, and SQL Developer.............................25
2.1 Overview of SQL....................25
Data Definition..........26
Data Manipulation and Transactions..................26
Retrieval...................27
Security....................29
Privileges and Roles.29
GRANT and REVOKE..31
2.2 Basic SQL Concepts and Terminology.............................32
Constants (Literals)...32
v
CONTENTS
vi
Variables...................34
Operators, Operands, Conditions, and Expressions......................................34
Arithmetic Operators.....................................35
The Alphanumeric Operator: Concatenation..35
Comparison Operators...................................35
Logical Operators36
Expressions.........36
Functions..................37
Database Object Naming....................................38
Comments................39
Reserved Words........39
2.3 Introduction to SQL*Plus........39
Entering Commands.40
Using the SQL Buffer41
Using an External Editor.....................................42
Using the SQL*Plus Editor..................................43
Using SQL Buffer Line Numbers....................46
Using the Ellipsis.48
SQL*Plus Editor Command Review................48
Saving Commands....49
Running SQL*Plus Scripts..................................51
Specifying Directory Path Specifications............52
Adjusting SQL*Plus Settings...............................53
Spooling a SQL*Plus Session..............................56
Describing Database Objects..............................57
Executing Commands from the Operating System.......................................57
Clearing the Buffer and the Screen....................57
SQL*Plus Command Review...............................57
CONTENTS
vii
2.4 Introduction to SQL Developer.........................................58
Installing and Configuring SQL Developer..........58
Connecting to a Database...................................61
Exploring Objects......62
Entering Commands.63
Run Statement.....64
Run Script............65
Saving Commands to a Script............................66
Running a Script.......67
Chapter 3: Data Definition, Part I................................71
3.1 Schemas and Users...............71
3.2 Table Creation........................72
3.3 Datatypes...............................73
3.4 Commands for Creating the Case Tables........................75
3.5 The Data Dictionary...............77
Chapter 4: Retrieval: The Basics.................................83
4.1 Overview of the SELECT Command.................................83
4.2 The SELECT Clause................85
Column Aliases.........86
The DISTINCT Keyword.......................................87
Column Expressions.87
The DUAL Table...88
Null Values in Expressions.............................90
4.3 The WHERE Clause................90
4.4 The ORDER BY Clause............91
4.5 AND, OR, and NOT..................94
The OR Operator.......94
The AND Operator and Operator Precedence Issues....................................95
CONTENTS
viii
The NOT Operator.....96
4.6 BETWEEN, IN, and LIKE..........98
The BETWEEN Operator......................................98
The IN Operator........99
The LIKE Operator...100
4.7 CASE Expressions................101
4.8 Subqueries...........................104
The Joining Condition.......................................105
When a Subquery Returns Too Many Values....106
Comparison Operators in the Joining Condition.........................................107
When a Single-Row Subquery Returns More Than One Row.....................108
4.9 Null Values...........................109
Null Value Display...109
The Nature of Null Values.................................109
The IS NULL Operator.......................................111
Null Values and the Equality Operator..............112
Null Value Pitfalls....113
4.10 Truth Tables.......................114
4.11 Exercises...........................116
Chapter 5: Retrieval: Functions................................117
5.1 Overview of Functions.........117
5.2 Arithmetic Functions............119
5.3 Text Functions.....................121
5.4 Regular Expressions............125
Regular Expression Operators and Metasymbols.......................................126
Regular Expression Function Syntax................127
Influencing Matching Behavior....................127
REGEXP_INSTR Return Value.......................128
CONTENTS
ix
REGEXP_LIKE..........128
REGEXP_INSTR.......129
REGEXP_SUBSTR....130
REGEXP_REPLACE..130
5.5 Date Functions.....................131
EXTRACT.................132
ROUND and TRUNC.133
MONTHS_BETWEEN and ADD_MONTHS...........133
NEXT_DAY and LAST_DAY................................134
5.6 General Functions................134
GREATEST and LEAST.......................................135
NVL.........................136
DECODE..................136
5.7 Conversion Functions..........137
TO_NUMBER and TO_CHAR..............................138
Conversion Function Formats...........................139
Datatype Conversion.........................................141
CAST.......................141
5.8 Stored Functions..................142
5.9 Exercises.............................143
Chapter 6: Data Manipulation...................................145
6.1 The INSERT Command.........146
Standard INSERT Commands...........................146
INSERT Using Subqueries.................................149
6.2 The UPDATE Command........151
6.3 The DELETE Command.........154
6.4 The MERGE Command.........157
6.5 Transaction Processing.......159
CONTENTS
x
6.6 Locking and Read Consistency......................................160
Locking...................160
Read Consistency...161
Chapter 7: Data Definition, Part II.............................163
7.1 The CREATE TABLE Command.......................................163
7.2 More on Datatypes...............165
Character Datatypes.........................................166
Comparison Semantics................................167
Column Data Interpretation.........................167
Numbers Revisited.167
7.3 The ALTER TABLE and RENAME Commands..................167
7.4 Constraints...........................170
Out-of-Line Constraints....................................170
Inline Constraints....172
Constraint Definitions in the Data Dictionary....173
Case Table Definitions with Constraints...........174
A Solution for Foreign Key References: CREATE SCHEMA..........................176
Deferrable Constraints......................................177
7.5 Indexes................................178
Index Creation.........179
Unique Indexes..180
Bitmap Indexes..180
Function-Based Indexes..............................180
Index Management.181
7.6 Performance Monitoring with SQL Developer AUTOTRACE......................................182
7.7 Sequences...........................185
7.8 Synonyms............................186
7.9 The CURRENT_SCHEMA Setting....................................188
CONTENTS
xi
7.10 The DROP TABLE Command........................................189
7.11 The TRUNCATE Command..191
7.12 The COMMENT Command..191
7.13 Exercises...........................193
Chapter 8: Retrieval: Multiple Tables and Aggregation......................................195
8.1 Tuple Variables....................195
8.2 Joins....................................197
Cartesian Products.198
Equijoins.................198
Non-equijoins.........199
Joins of Three or More Tables..........................200
Self-Joins...............201
8.3 The JOIN Clause...................202
Natural Joins..........203
Equijoins on Columns with the Same Name.....204
8.4 Outer Joins..........................205
Old Oracle-Specific Outer Join Syntax..............206
New Outer Join Syntax.....................................207
Outer Joins and Performance...........................208
8.5 The GROUP BY Component..208
Multiple-Column Grouping................................210
GROUP BY and Null Values...............................210
8.6 Group Functions...................211
Group Functions and Duplicate Values.............212
Group Functions and Null Values......................213
Grouping the Results of a Join.........................214
The COUNT(*) Function.....................................214
Valid SELECT and GROUP BY Clause Combinations....................................216
CONTENTS
xii
8.7 The HAVING Clause..............217
The Difference Between WHERE and HAVING..218
HAVING Clauses Without Group Functions........218
A Classic SQL Mistake......................................219
Grouping on Additional Columns......................220
8.8 Advanced GROUP BY Features.......................................222
GROUP BY ROLLUP..222
GROUP BY CUBE......223
CUBE, ROLLUP, and Null Values........................224
The GROUPING Function..............................224
The GROUPING_ID Function.........................225
8.9 Partitioned Outer Joins........226
8.10 Set Operators.....................228
8.11 Exercises...........................231
Chapter 9: Retrieval: Some Advanced Features........233
9.1 Subqueries Continued.........233
The ANY and ALL Operators..............................234
Defining ANY and ALL..................................235
Rewriting SQL Statements Containing ANY and ALL.............................236
Correlated Subqueries......................................237
The EXISTS Operator........................................238
Subqueries Following an EXISTS Operator..239
EXISTS, IN, or JOIN?....................................239
NULLS with NOT EXISTS and NOT IN...........242
9.2 Subqueries in the SELECT Clause..................................243
9.3 Subqueries in the FROM Clause....................................244
9.4 The WITH Clause..................245
CONTENTS
xiii
9.5 Hierarchical Queries............247
START WITH and CONNECT BY.........................248
LEVEL, CONNECT_BY_ISCYCLE, and CONNECT_BY_ISLEAF.......................249
CONNECT_BY_ROOT and SYS_CONNECT_BY_PATH..................................250
Hierarchical Query Result Sorting....................251
9.6 Analytical Functions.............252
Partitions................254
Function Processing.........................................257
9.7 Flashback Features.............259
AS OF......................260
VERSIONS BETWEEN.........................................262
FLASHBACK TABLE.262
9.8 Exercises.............................264
Chapter 10: Views...........265
10.1 What Are Views?................265
10.2 View Creation.....................266
Creating a View from a Query...........................267
Getting Information About Views from the Data Dictionary........................269
Replacing and Dropping Views.........................271
10.3 What Can You Do with Views?.....................................271
Simplifying Data Retrieval................................271
Maintaining Logical Data Independence..........273
Implementing Data Security.............................274
10.4 Data Manipulation via Views.......................................274
Updatable Join Views.......................................276
Nonupdatable Views.........................................277
The WITH CHECK OPTION Clause......................278
Disappearing Updated Rows.......................278
CONTENTS
xiv
Inserting Invisible Rows..............................279
Preventing These Two Scenarios................280
Constraint Checking....................................280
10.5 Data Manipulation via Inline Views..............................281
10.6 Views and Performance.....282
10.7 Materialized Views.............283
Properties of Materialized Views......................284
Query Rewrite.........284
10.8 Exercises...........................286
Chapter 11: Writing and Automating SQL*Plus Scripts......................................287
11.1 SQL*Plus Variables............288
SQL*Plus Substitution Variables.......................288
SQL*Plus User-Defined Variables.....................290
Implicit SQL*Plus User-Defined Variables...291
User-Friendly Prompting.............................292
SQL*Plus System Variables..............................293
11.2 Bind Variables....................298
Bind Variable Declaration.................................299
Bind Variables in SQL Statements....................300
11.3 SQL*Plus Scripts................301
Script Execution......301
Script Parameters...302
SQL*Plus Commands in Scripts........................304
The login.sql Script.305
11.4 Report Generation with SQL*Plus................................306
The SQL*Plus COLUMN Command....................307
The SQL*Plus TTITLE and BTITLE Commands...311
The SQL*Plus BREAK Command.......................312
CONTENTS
xv
The SQL*Plus COMPUTE Command..................315
The Finishing Touch: SPOOL.............................317
11.5 HTML in SQL*Plus..............318
HTML in SQL*Plus...318
11.6 Building SQL*Plus Scripts for Automation...................321
What Is a SQL*Plus Script?...............................321
Capturing and Using Input Parameter Values...322
Passing Data Values from One SQL Statement to Another.........................323
Mechanism 1: The NEW_VALUE Clause.......323
Mechanism 2: Bind Variables......................324
Handling Error Conditions.................................325
11.7 Exercises...........................326
Chapter 12: Object-Relational Features....................329
12.1 More Datatypes..................329
Collection Datatypes.........................................330
Methods..................330
12.2 Varrays...............................331
Creating the Array...331
Populating the Array with Values.....................333
Querying Array Columns...................................334
12.3 Nested Tables....................336
Creating Table Types........................................336
Creating the Nested Table................................336
Populating the Nested Table.............................337
Querying the Nested Table...............................338
12.4 User-Defined Types...........339
Creating User-Defined Types............................339
Showing More Information with DESCRIBE......340
CONTENTS
xvi
12.5 Multiset Operators.............341
Which SQL Multiset Operators Are Available?..341
Preparing for the Examples..............................342
Using IS NOT EMPTY and CARDINALITY............343
Using POWERMULTISET....................................344
Using MULTISET UNION....................................345
Converting Arrays into Nested Tables..............346
12.6 Exercises...........................346
Appendix A: The Seven Case Tables.........................349
ERM Diagram.............................349
Table Structure Descriptions.....350
Columns and Foreign Key Constraints.................................351
Contents of the Seven Tables....352
Hierarchical Employees Overview.......................................357
Course Offerings Overview........357
Appendix B: Answers to the Exercises.....................359
Chapter 4 Exercises...................359
Chapter 5 Exercises...................369
Chapter 7 Exercises...................374
Chapter 8 Exercises...................376
Chapter 9 Exercises...................386
Chapter 10 Exercises.................395
Chapter 11 Exercises.................397
Chapter 12 Exercises.................401
Index...............................405
Total 1,424
| Number | Title | Author | Date | Votes | Views |
| 1424 |
Byte of Python
tanthanh
|
2020.05.28
|
Votes 0
|
Views 68352072
|
tanthanh | 2020.05.28 | 0 | 68352072 |
| 1423 |
Surviving the Top Ten Challenges of Software Testing: A People-Oriented Approach (2)
^Software^
|
2019.07.22
|
Votes 0
|
Views 69520604
|
^Software^ | 2019.07.22 | 0 | 69520604 |
| 1422 |
Jmeter Cookbook (1)
VTB
|
2019.06.27
|
Votes 0
|
Views 70065246
|
VTB | 2019.06.27 | 0 | 70065246 |
| 1421 |
Java Testing : Maven - Reference (315 Pages) (1)
IT-Tester
|
2019.06.26
|
Votes 0
|
Views 69855999
|
IT-Tester | 2019.06.26 | 0 | 69855999 |
| 1420 |
Java Testing : Maven Example (154 Pages)
IT-Tester
|
2019.06.26
|
Votes 0
|
Views 69262755
|
IT-Tester | 2019.06.26 | 0 | 69262755 |
| 1419 |
AGILE TESTING - EBOOK (2)
HenryChuks
|
2019.05.31
|
Votes 0
|
Views 68615165
|
HenryChuks | 2019.05.31 | 0 | 68615165 |
| 1418 |
“Software Testing Career Package – A Software Tester’s Journey from Getting a Job to Becoming a Test Leader!”
aiitistqb
|
2018.10.16
|
Votes 0
|
Views 67964150
|
aiitistqb | 2018.10.16 | 0 | 67964150 |
| 1417 |
Practical Software Testing – New FREE eBook [Download] (2)
aiitistqb
|
2018.10.16
|
Votes 0
|
Views 69088236
|
aiitistqb | 2018.10.16 | 0 | 69088236 |
| 1416 |
The Pathologies of Failed Test Automation Projects
aiitistqb
|
2018.10.16
|
Votes 0
|
Views 68942498
|
aiitistqb | 2018.10.16 | 0 | 68942498 |
| 1415 |
Selenium WebDriver Practical Guide (4)
meo meo con con
|
2018.06.16
|
Votes 0
|
Views 68977063
|
meo meo con con | 2018.06.16 | 0 | 68977063 |
| 1414 |
Python for Informatics
melassiri
|
2018.06.04
|
Votes 0
|
Views 69915088
|
melassiri | 2018.06.04 | 0 | 69915088 |
| 1413 |
Hacking - The Art of Exploitation (7)
ravisk
|
2018.03.25
|
Votes 0
|
Views 68983730
|
ravisk | 2018.03.25 | 0 | 68983730 |
| 1412 |
Instant Penetration Testing Setting Up a Test Lab How-to (1)
ravisk
|
2018.03.24
|
Votes 0
|
Views 67762566
|
ravisk | 2018.03.24 | 0 | 67762566 |
| 1411 |
Practical-Guide-to-Software-System-Testing (3)
ravisk
|
2018.03.24
|
Votes 1
|
Views 69965401
|
ravisk | 2018.03.24 | 1 | 69965401 |
| 1410 |
EFFORT estimation software (1)
ravisk
|
2018.03.24
|
Votes 0
|
Views 68714494
|
ravisk | 2018.03.24 | 0 | 68714494 |
| 1409 |
Lee Copeland. A Practitioner's Guide to Software Test Design (19)
Unbroken
|
2017.12.15
|
Votes 0
|
Views 69312725
|
Unbroken | 2017.12.15 | 0 | 69312725 |
| 1408 |
http response codes (3)
SV369
|
2017.12.14
|
Votes 0
|
Views 70231278
|
SV369 | 2017.12.14 | 0 | 70231278 |
| 1407 |
«Hacking Mobile Exposed, Security secrets and solutions» (5)
Unbroken
|
2017.12.08
|
Votes 0
|
Views 69556779
|
Unbroken | 2017.12.08 | 0 | 69556779 |
| 1406 |
James A. Whittaker «Exploratory software testing» (8)
Unbroken
|
2017.12.08
|
Votes 1
|
Views 69795569
|
Unbroken | 2017.12.08 | 1 | 69795569 |
| 1405 |
FOUNDATIONS OF SOFTWARE TESTING (6)
marklouis
|
2017.12.05
|
Votes 0
|
Views 68845963
|
marklouis | 2017.12.05 | 0 | 68845963 |
| 1404 |
Python for informatics (2)
TesterQA
|
2017.12.01
|
Votes 0
|
Views 69778965
|
TesterQA | 2017.12.01 | 0 | 69778965 |
| 1403 |
Selenium Testing Tool Cookbook (11)
liliam001
|
2017.11.14
|
Votes 0
|
Views 68868253
|
liliam001 | 2017.11.14 | 0 | 68868253 |
| 1402 |
What is SQL Injection? (4)
ArifBaba
|
2017.10.28
|
Votes 0
|
Views 70011124
|
ArifBaba | 2017.10.28 | 0 | 70011124 |
| 1401 |
Oracle Middleware Tuning (4)
gpratikg
|
2017.10.08
|
Votes 0
|
Views 68620939
|
gpratikg | 2017.10.08 | 0 | 68620939 |
| 1400 |
Microsoft SQL Server 2012 (3)
yoshiharra
|
2017.10.08
|
Votes 0
|
Views 68894869
|
yoshiharra | 2017.10.08 | 0 | 68894869 |
| 1399 |
visual studio c sharp
vikasrao
|
2017.09.24
|
Votes 0
|
Views 69221936
|
vikasrao | 2017.09.24 | 0 | 69221936 |
| 1398 |
How to Break Web Software: Functional and Security Testing of Web Applications and Web Services (7)
vikasrao
|
2017.09.24
|
Votes 0
|
Views 68383212
|
vikasrao | 2017.09.24 | 0 | 68383212 |
| 1397 |
The Art of Unit Testing with Examples in .NET
vikasrao
|
2017.09.24
|
Votes 0
|
Views 69274086
|
vikasrao | 2017.09.24 | 0 | 69274086 |
| 1396 |
Scrum (2)
dhoanglong91
|
2017.09.23
|
Votes 1
|
Views 67936438
|
dhoanglong91 | 2017.09.23 | 1 | 67936438 |
| 1395 |
Python for Unix and Linux System Administration
Crismachado
|
2017.09.22
|
Votes 0
|
Views 68173158
|
Crismachado | 2017.09.22 | 0 | 68173158 |
| 1394 |
Ruby Best Practices (3)
Crismachado
|
2017.09.22
|
Votes 0
|
Views 68999694
|
Crismachado | 2017.09.22 | 0 | 68999694 |
| 1393 |
Python in Practice (2)
ManhAnh
|
2017.09.05
|
Votes 0
|
Views 69403082
|
ManhAnh | 2017.09.05 | 0 | 69403082 |
| 1392 |
Practical Object-Oriented Design in Ruby (2)
ManhAnh
|
2017.09.05
|
Votes 0
|
Views 67495656
|
ManhAnh | 2017.09.05 | 0 | 67495656 |
| 1391 |
Practical Cassandra (2)
ManhAnh
|
2017.09.05
|
Votes 0
|
Views 69163611
|
ManhAnh | 2017.09.05 | 0 | 69163611 |
| 1390 |
Development with the Force.com Platform, 3rd Edition (2)
ManhAnh
|
2017.09.05
|
Votes 0
|
Views 70536965
|
ManhAnh | 2017.09.05 | 0 | 70536965 |
| 1389 |
Apache Cordova 3 Programming (2)
ManhAnh
|
2017.09.05
|
Votes 0
|
Views 69146662
|
ManhAnh | 2017.09.05 | 0 | 69146662 |
| 1388 |
Software Testing - Ron Patton (4)
bugdetective
|
2017.09.04
|
Votes 0
|
Views 70033948
|
bugdetective | 2017.09.04 | 0 | 70033948 |
| 1387 |
The Art of Software Testing, 2rd Edition (1)
bugdetective
|
2017.09.04
|
Votes 0
|
Views 69134537
|
bugdetective | 2017.09.04 | 0 | 69134537 |
| 1386 |
Explore It!
bugdetective
|
2017.09.04
|
Votes 1
|
Views 68875645
|
bugdetective | 2017.09.04 | 1 | 68875645 |
| 1385 |
NoSQl (1)
getmedude
|
2017.08.27
|
Votes 0
|
Views 70215824
|
getmedude | 2017.08.27 | 0 | 70215824 |
| 1384 |
Art of testing (10)
dktzm89
|
2017.08.16
|
Votes 0
|
Views 68883869
|
dktzm89 | 2017.08.16 | 0 | 68883869 |
| 1383 |
Perl Book (1)
Ravish24
|
2017.08.15
|
Votes 0
|
Views 68667216
|
Ravish24 | 2017.08.15 | 0 | 68667216 |
| 1382 |
Automation Testing (5)
Ravish24
|
2017.08.15
|
Votes 1
|
Views 71130165
|
Ravish24 | 2017.08.15 | 1 | 71130165 |
| 1381 |
Prince2 model chart
AllGreen
|
2017.08.09
|
Votes 0
|
Views 68308823
|
AllGreen | 2017.08.09 | 0 | 68308823 |
| 1380 |
Prince2 for Dummies
AllGreen
|
2017.08.09
|
Votes 0
|
Views 69177498
|
AllGreen | 2017.08.09 | 0 | 69177498 |
| 1379 |
Unix and Linux testing (2)
pavan765
|
2017.08.01
|
Votes 0
|
Views 69929796
|
pavan765 | 2017.08.01 | 0 | 69929796 |
| 1378 |
Practical Software Testing (6)
Administrator
|
2017.07.24
|
Votes 0
|
Views 68626999
|
Administrator | 2017.07.24 | 0 | 68626999 |
| 1377 |
Selenium Notes (1)
masterofall
|
2017.07.24
|
Votes 0
|
Views 68775717
|
masterofall | 2017.07.24 | 0 | 68775717 |
| 1376 |
Practical Software Testing
masterofall
|
2017.07.24
|
Votes 0
|
Views 70143274
|
masterofall | 2017.07.24 | 0 | 70143274 |
| 1375 |
Lead Generation for Dummies (2)
uday bhaskar
|
2017.07.20
|
Votes 0
|
Views 69325954
|
uday bhaskar | 2017.07.20 | 0 | 69325954 |
good, thanks
thanks a lot.