SUMMARY

SEARCH

Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.

BOOK ORACLE 12C SQL 3ED

SUMMARY

406

Preface xiv
Chapter 1 Overview of Database Concepts 1
Chapter 2 Basic SQL SELECT Statements 25
Chapter 3 Table Creation and Management 57
Chapter 4 Constraints 103
Chapter 5 Data Manipulation and Transaction Control 141
Chapter 6 Additional Database Objects 181
Chapter 7 User Creation and Management 225
Chapter 8 Restricting Rows and Sorting Data 255
Chapter 9 Joining Data from Multiple Tables 295
Chapter 10 Selected Single-Row Functions 347
Chapter 11 Group Functions 401
Chapter 12 Subqueries and MERGE Statements 449
Chapter 13 Views 495
Appendix A Tables for the JustLee Books Database 539
Appendix B SQL*Plus and SQL Developer Overview 547
Appendix C Oracle Resources 555
Appendix D SQL*Loader 557
Appendix E SQL Tuning Topics 561
Appendix F SQL in Various Databases 579
Glossary 583
Index 589
Preface xiv
Chapter 1 Overview of Database Concepts 1
Introduction 2
Database Terminology 2
Database Management System 3
Database Design 4
Entity-Relationship (E-R) Model 5
Database Normalization 6
Relating Tables in the Database 10
Structured Query Language (SQL) 12
Databases Used in This Textbook 13
Basic Assumptions 13
Tables in the JustLee Books Database 14
Topic Sequence 16
Software Used in This Textbook 16
Chapter Summary 17
Review Questions 17
Multiple Choice 18
Hands-On Assignments 21
Advanced Challenge 22
Case Study: City Jail 22
Chapter 2 Basic SQL SELECT Statements 25
Introduction 26
Creating the JustLee Books Database 27
SELECT Statement Syntax 30
Selecting All Data in a Table 31
Selecting One Column from a Table 33
Selecting Multiple Columns from a Table 34
Operations in the SELECT Statement 36
Using Column Aliases 36
Using Arithmetic Operations 39
NULL Values 40
Using DISTINCT and UNIQUE 42
Using Concatenation 44
Chapter Summary 49
Chapter 2 Syntax Summary 49
Review Questions 50
Multiple Choice 51
Hands-On Assignments 54
Advanced Challenge 55
Case Study: City Jail 55
Chapter 3 Table Creation and Management 57
Introduction 58
Table Design 59
Table Creation 63
Defining Columns 63
Viewing a List of Tables: USER_TABLES 65
Viewing Table Structures: DESCRIBE 66
Table Creation with Subqueries 70
CREATE TABLE … AS Command 70
Modifying Existing Tables 72
ALTER TABLE … ADD Command 73
ALTER TABLE … MODIFY Command 74
ALTER TABLE … DROP COLUMN Command 79
ALTER TABLE … SET UNUSED/DROP UNUSED COLUMNS Command 80
Renaming a Table 83
Truncating a Table 85
Deleting a Table 86
Chapter Summary 91
Chapter 3 Syntax Summary 92
Review Questions 93
Multiple Choice 94
Hands-On Assignments 97
Advanced Challenge 97
Case Study: City Jail 98
Chapter 4 Constraints 103
Introduction 104
Creating Constraints 105
Creating Constraints at the Column Level 106
Creating Constraints at the Table Level 106
Using the PRIMARY KEY Constraint 107
Using the FOREIGN KEY Constraint 110
Using the UNIQUE Constraint 115
Using the CHECK Constraint 116
Using the NOT NULL Constraint 119
Including Constraints During Table Creation 121
Adding Multiple Constraints on a Single Column 125
Viewing Constraint Information 126
Disabling and Dropping Constraints 128
Using DISABLE/ENABLE 128
Dropping Constraints 129
Chapter Summary 131
Chapter 4 Syntax Summary 131
Review Questions 133
Multiple Choice 134
Hands-On Assignments 137
Advanced Challenge 139
Case Study: City Jail 139
Chapter 5 Data Manipulation and Transaction Control 141
Introduction 142
Inserting New Rows 143
Using the INSERT Command 143
Handling Virtual Columns 150
Handling Single Quotes in an INSERT Value 152
Inserting Data from an Existing Table 154
Modifying Existing Rows 156
Using the UPDATE Command 156
Using Substitution Variables 158
Deleting Rows 162
Using Transaction Control Statements 163
COMMIT and ROLLBACK Commands 164
SAVEPOINT Command 165
Using Table Locks 168
LOCK TABLE Command 168
SELECT … FOR UPDATE Command 169
Chapter Summary 171
Chapter 5 Syntax Summary 172
Review Questions 173
Multiple Choice 173
Hands-On Assignments 176
Advanced Challenge 177
Case Study: City Jail 178
Chapter 6 Additional Database Objects 181
Introduction 182
Sequences 183
Creating a Sequence 184
Using Sequence Values 189
Setting and Altering Sequence Definitions 192
Altering Sequence Definitions 194
Removing a Sequence 196
Use Identity Columns Instead of Sequences for Primary Key Columns 197
Indexes 199
B-Tree Indexes 200
Bitmap Indexes 206
Function-Based Indexes 208
Index Organized Tables 209
Verifying an Index 210
Altering or Removing an Index 211
Synonyms 212
Deleting a Synonym 215
Chapter Summary 216
Chapter 6 Syntax Summary 217
Review Questions 218
Multiple Choice 219
Hands-On Assignments 222
Advanced Challenge 223
Case Study: City Jail 224
Chapter 7 User Creation and Management 225
Introduction 226
Data Security 227
Creating a User 228
Creating Usernames and Passwords 228
Assigning User Privileges 230
System Privileges 230
Granting System Privileges 231
Object Privileges 232
Granting Object Privileges 232
Managing Passwords 236
Using Roles 237
Creating and Assigning Roles 238
Using Predefined Roles 240
Using Default Roles 241
Enabling Roles After Login 242
Viewing Privilege Information 242
Removing Privileges and Users 244
Revoking Privileges and Roles 244
Dropping a Role 246
Dropping a User 246
Chapter Summary 247
Chapter 7 Syntax Summary 247
Review Questions 249
Multiple Choice 249
Hands-On Assignments 253
Advanced Challenge 253
Case Study: City Jail 254
Chapter 8 Restricting Rows and Sorting Data 255
Introduction 256
Where Clause Syntax 257
Rules for Character Strings 258
Rules for Dates 260
Comparison Operators 260
BETWEEN ... AND Operator 267
IN Operator 268
LIKE Operator 270
Logical Operators 274
Treatment of Null Values 277
ORDER by Clause Syntax 279
Secondary Sort 282
Sorting by SELECT Order 284
Chapter Summary 285
Chapter 8 Syntax Summary 286
Review Questions 288
Multiple Choice 288
Hands-On Assignments 292
Advanced Challenge 293
Case Study: City Jail 293
Chapter 9 Joining Data from Multiple Tables 295
Introduction 296
Cartesian Joins 297
Cartesian Join: Traditional Method 298
Cartesian Join: JOIN Method 300
Equality Joins 301
Equality Joins: Traditional Method 303
Equality Joins: JOIN Method 308
Non-Equality Joins 314
Non-Equality Joins: Traditional Method 315
Non-Equality Joins: JOIN Method 316
Self-Joins 317
Self-Joins: Traditional Method 318
Self-Joins: JOIN Method 319
Outer Joins 320
Outer Joins: Traditional Method 321
Outer Joins: JOIN Method 324
Set Operators 326
Chapter Summary 334
Chapter 9 Syntax Summary 335
Review Questions 337
Multiple Choice 338
Hands-On Assignments 344
Advanced Challenge 345
Case Study: City Jail 345
Chapter 10 Selected Single-Row Functions 347
Introduction 348
Case Conversion Functions 349
The LOWER Function 349
The UPPER Function 350
The INITCAP Function 351
Character Manipulation Functions 352
The SUBSTR Function 352
The INSTR Function 354
The LENGTH Function 356
The LPAD and RPAD Functions 357
The LTRIM and RTRIM Functions 358
The REPLACE Function 359
The TRANSLATE Function 360
The CONCAT Function 360
Number Functions 361
The ROUND Function 361
The TRUNC Function 362
The MOD Function 363
The ABS Function 364
The POWER Function 365
Date Functions 365
The MONTHS_BETWEEN Function 367
The ADD_MONTHS Function 367
The NEXT_DAY and LAST_DAY Functions 368
The TO_DATE Function 369
Rounding Date Values 371
Truncating Date Values 372
CURRENT_DATE Versus SYSDATE 373
Regular Expressions 374
Other Functions 377
The NVL Function 377
The NVL2 Function 380
The NULLIF Function 381
The TO_CHAR Function 383
The DECODE Function 385
The CASE Expression 387
The SOUNDEX Function 387
The TO_NUMBER Function 388
The DUAL Table 389
Chapter Summary 390
Chapter 10 Syntax Summary 390
Review Questions 394
Multiple Choice 395
Hands-On Assignments 399
Advanced Challenge 399
Case Study: City Jail 400
Chapter 11 Group Functions 401
Introduction 402
Group Functions 403
The SUM Function 404
The AVG Function 406
The COUNT Function 408
The MAX Function 411
The MIN Function 412
Grouping Data 413
Restricting Aggregated Output 417
Nesting Functions 421
Statistical Group Functions 422
The STDDEV Function 422
The VARIANCE Function 423
Enhanced Aggregation for Reporting 424
The GROUPING SETS Expression 427
The CUBE Extension 428
The ROLLUP Extension 431
Pattern Matching 437
Chapter Summary 439
Chapter 11 Syntax Summary 439
Review Questions 441
Multiple Choice 442
Hands-On Assignments 446
Advanced Challenge 447
Case Study: City Jail 447
Chapter 12 Subqueries and MERGE Statements 449
Introduction 450
Subqueries and Their Uses 451
Single-Row Subqueries 451
Single-Row Subquery in a WHERE Clause 451
Single-Row Subquery in a HAVING Clause 456
Single-Row Subquery in a SELECT Clause 457
Multiple-Row Subqueries 459
The IN Operator 460
The ALL and ANY Operators 461
Multiple-Row Subquery in a HAVING Clause 465
Multiple-Column Subqueries 467
Multiple-Column Subquery in a FROM Clause 467
Multiple-Column Subquery in a WHERE Clause 469
NULL Values 471
NVL in Subqueries 471
IS NULL in Subqueries 472
Correlated Subqueries 473
Nested Subqueries 475
Subquery Factoring Clause 477
DML Actions Using Subqueries 478
MERGE Statements 479
Chapter Summary 484
Chapter 12 Syntax Summary 484
Review Questions 486
Multiple Choice 486
Hands-On Assignments 492
Advanced Challenge 493
Case Study: City Jail 493
Chapter 13 Views 495
Introduction 496
Creating a View 498
Creating a Simple View 500
DML Operations on a Simple View 504
Creating a Complex View 508
DML Operations on a Complex View with an Arithmetic Expression 508
DML Operations on a Complex View Containing Data from Multiple Tables 513
DML Operations on a Complex View Containing Functions or Grouped Data 515
DML Operations on a Complex View Containing DISTINCT or ROWNUM 517
Summary Guidelines for DML Operations on a Complex View 519
Dropping a View 519
Creating an Inline View 520
CROSS and OUTER APPLY Methods for Joins 520
TOP-N Analysis 522
Creating a Materialized View 527
Chapter Summary 531
Chapter 13 Syntax Summary 532
Review Questions 533
Multiple Choice 533
Hands-On Assignments 537
Advanced Challenge 538
Case Study: City Jail 538
Appendix A Tables for the JustLee Books Database 539
CUSTOMERS Table 539
BOOKS Table 540
ORDERS Table 541
ORDERITEMS Table 542
AUTHOR Table 543
BOOKAUTHOR Table 544
PUBLISHER Table 545
PROMOTION Table 546
Appendix B SQL*Plus and SQL Developer Overview 547
Introduction 547
SQL*Plus 547
SQL Developer 551
Appendix C Oracle Resources 555
Oracle Academic Initiative (OAI) 555
Oracle Certification Program (OCP) 555
Oracle Technology Network (OTN) 555
International Oracle Users Group (IOUG) 556
Appendix D SQL*Loader 557
Introduction 557
Read a Fixed File Format 557
Read a Delimited File 559
Appendix E SQL Tuning Topics 561
Introduction 561
Tuning Concepts and Issues 561
Identifying Problem Areas in Coding 561
Processing and the Optimizer 563
The Explain Plan 565
Timing Feature 570
Selected SQL Tuning Guidelines and Examples 571
Avoiding Unnecessary Column Selection 572
Index Suppression 573
Concatenated Indexes 575
Subqueries 576
Optimizer Hints 577
Appendix F SQL in Various Databases 579
Introduction 579
Suppressing Duplicates 579
Locating a Value in a String 580
Displaying the Current Date 580
Specifying a Default Date Format 580
Replacing NULL Values in Text Data 581
Adding Time to Dates 581
Extracting Values from a String 581
Concatenating 582
Data Structures 582
Glossary 583
Index 589