SUMMARY

SEARCH

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

BOOK BEGINNING ORACLE SQL FOR ORACLE DATABASE 18C FROM NOVICE TO PROFESSIONAL

SUMMARY

345

Download the JDK 24
Create a Connection 28
Create a New User 31
Create a Connection with New User 36
Create a Table 37
Insert Some Data 40
LiveSQL 44
Summary 49
Part II: Viewing Data 51
Chapter 3: Retrieving Data 53
How Can You See the Data? 53
What Is the SELECT Statement? 53
A Few Things to Note 54
Our First SELECT Statement 55
Running a Statement in SQL Developer 56
Results 60
Run Script 61
Errors in SQL 62
Missing Keyword 62
Table Does Not Exist 63
Summary 64
Chapter 4: Selecting Specific Columns 65
Selecting a Single Column 66
Selecting Multiple Columns 71
Selecting All Columns 73
SELECT Columns or SELECT *? 75
Summary 76
Chapter 5: Restricting the Results 77
Why Restrict Data? 77
What is the WHERE Clause? 78
Example with a Text Value 78
Example with a Number Value 81
Finding Rows Not Equal to a Value 82
Greater Than 83
Greater Than or Equal To 84
Less Than 85
Less Than or Equal To 86
Best Practices 87
Specify Columns on the Left 87
Select Only What You Need 87
Summary 88
Chapter 6: Comparing Data 89
What Is a Partial Match? 89
Syntax of LIKE 90
The Percent Wildcard Character 90
The Underscore Wildcard Character 92
Searching for Wildcard Characters 95
Performance 96
Summary 97
Chapter 7: Applying Multiple Filters 99
Why Use Multiple Filters? 99
The AND Keyword 100
Finding Records That Match a Last Name and a Salary 100
Finding Records that Match Greater Than and Less Than on Two Columns 101
The OR Keyword 102
Finding Records that Match a Last Name or a Salary 102
Finding Records that Match Greater Than or Less Than on Two Columns 103
vi
More Than Two Conditions 104
Multiple AND Conditions 104
Multiple OR Conditions 105
Combining Multiple AND and OR Conditions 105
Specifying the Order with Multiple Conditions 106
Summary 108
Chapter 8: Working with Nulls 109
Missing Data 109
What is NULL? 111
How Should NULL Be Treated? 111
Examples of Working with NULL 112
Adding More Data 112
Selecting the Records 118
Restricting the NULL Values 120
Hiding Null Values 122
Multiple Queries in One SQL Window 123
Summary 125
Chapter 9: Removing Duplicate Results 127
Add New Records 128
Select the Data 130
What is DISTINCT? 131
Finding Unique Last Name Values 132
Finding Unique Salary Values 133
Finding Unique Combinations of Values 134
Summary 135
Chapter 10: Applying Filters on Lists and Ranges of Values 137
Too Many Conjunctions 137
The IN Keyword 139
Example of Using IN 139
A Longer Value List 140
vii
Using IN with Text Values 141
Using IN with Other Clauses 142
IN with AND? 142
IN with LIKE? 143
Filtering on Ranges of Values 144
The BETWEEN Operator 145
Using Between with Two Salary Values 146
Using BETWEEN When Values Don’t Match 147
Using BETWEEN with Text Values 147
An Example of an Inclusive and Exclusive Check 148
Should You Use BETWEEN? 149
Summary 149
Chapter 11: Ordering Your Data 151
Results Are Not Ordered 151
Ordering Results with ORDER BY 152
ORDER BY Examples 153
Order by a Text Value 153
Order by a Number Value 154
Order by a Column Not in the SELECT Clause 156
Order by a Number 156
ORDER BY and NULLs 158
Order by Multiple Columns 159
Do You Really Need to Order Your Data? 161
Summary 162
Chapter 12: Applying Table and Column Aliases 163
What is a Table Alias? 163
Example of a Table Alias 164
Intellisense or AutoComplete 165
Longer Table Aliases 167
What If I Don’t Use a Table Alias? 168
What Is a Column Alias? 168
Example of a Column Alias 169
The AS Keyword 171
Mathematical Operations and Column Aliases 172
Addition 173
Subtraction 175
Multiplication 176
Division 177
Column Aliases with Table Aliases 178
Summary 179
Part III: Adding, Updating, Deleting Data 181
Chapter 13: Understanding the Data Types 183
What is a Data Type? 183
Why Do We Have Different Data Types? 184
What Are the Different Data Types? 185
Text Data Types 185
CHAR 186
VARCHAR2 186
NCHAR 187
NVARCHAR2 188
RAW 189
LONG RAW 189
Number Data Types 189
NUMBER 190
INTEGER 190
FLOAT 191
DECIMAL 191
BINARY_FLOAT 191
BINARY_DOUBLE 192
Date Data Types 192
ix
DATE 192
TIMESTAMP 192
TIMESTAMP WITH TIME ZONE 193
TIMESTAMP WITH LOCAL TIME ZONE 193
INTERVAL YEAR TO MONTH 193
INTERVAL DAY TO SECOND 194
Other Data Types 194
BLOB 194
CLOB 195
Data Type Recommendations 195
Summary 195
Chapter 14: Creating a Table 197
Creating Tables Using SQL Code 197
The CREATE TABLE Statement 198
Our Employee Table 199
Storing Office Details 200
The Office Table 203
The Sales Meeting Table 206
Primary Key 206
Recording the Employee Again 209
Foreign Key 209
Summary 212
Chapter 15: Adding Data to a Table 213
The INSERT Statement 213
Running an INSERT Statement 214
Inserting More Data 216
Inserting Date Values 218
Saving and Undoing Changes 221
Best Practices for Inserting Data 223
Summary 224
x
Chapter 16: Updating and Removing Data 225
The UPDATE Statement 225
Update to a New Value 226
Checking an Update Statement Before Running It 228
Update a NULL Value 229
Update Based on Existing Value 230
Update a Date Value 231
Viewing and Updating the Date Format 232
Update Two Columns 233
Update Without WHERE Clause 234
Removing Data from a Table 235
Deleting a Record 236
Check What Records Will Be Deleted 237
Delete All Records in a Table 238
Summary 238
Chapter 17: Updating or Deleting a Table 239
Why Update the Structure of a Table? 239
The ALTER TABLE Statement 240
Example: Adding a Column 241
Example: Change a Data Type 242
Example: Add a Primary Key 243
Example: Add a Foreign Key 245
Example: Rename a Column 246
Example: Remove a Column 247
Example: Rename a Table 248
Removing a Table with DROP TABLE 249
Summary 250
xi
Part IV: Joining Tables 251
Chapter 18: Inner Join 253
Multiple Tables 253
What is a Join? 254
An Example of a Join 255
Joins, Formatting, and Table Aliases 257
An INNER JOIN 260
Summary 262
Chapter 19: Outer Join 263
What is an Outer Join? 263
Writing a Left Outer Join 264
Showing All Employees with a Left Outer Join 266
Writing a Right Outer Join 268
When Would You Use a Right Outer Join? 270
Writing a Full Outer Join 270
Using a Full Outer Join on Our Tables 272
Summary 273
Chapter 20: Other Join Types 275
The USING Keyword 275
Updating the Office Table 276
Writing a Query with the USING Keyword 277
What is a Natural Join? 280
Writing a Query with a Natural Join 280
What is a Cross Join? 282
Using a CROSS JOIN 284
Alternative Join Syntax 285
Inner Join 285
Outer Join 286
Why You Shouldn’t Use the Alternative Join Syntax 288
Summary 289
Chapter 21: Joining Many Tables 291
Joining Three Tables 291
Joining Our Tables Together 293
Using Outer Join Types 294
Mixing Join Types 296
Joining Four or More Tables 297
Summary 299
Part V: Functions 301
Chapter 22: Using functions in SQL 303
What is a Function? 303
Where Can You Use Functions? 304
Simple Numeric Calculations 304
The DUAL Table 308
Number Functions 311
Concatenation of Strings 314
String Functions 317
Changing Case 317
Checking for Matches on the Same Case 319
Getting Part of a String 322
Calculations on Dates 323
Date Functions 327
Current Date and Time 327
Adding Months 328
Summary 330
Chapter 23: Writing Conditional Logic 331
The CASE Statement 331
Simple Case Statement 333
Searched Case Statement 337
The DECODE Function 342
CASE or DECODE? 344
Summary 345
Chapter 24: Understanding Aggregate Functions 347
Aggregate Functions 348
The SUM Function 348
A Simple SUM Example 349
SUM with WHERE 350
SUM with Expression 350
SUM with DISTINCT 351
The COUNT Function 351
Counting All Records 352
Count a Specific Column 352
Count with DISTINCT 354
Count with WHERE 356
The AVG Function 357
Average of All Values 357
Average with DISTINCT 358
Average with WHERE 359
The MIN Function 360
Minimum of All Records 360
Minimum with WHERE 362
The MAX Function 362
Maximum of All Records 362
Maximum with WHERE 363
Summary 364
xiv
Chapter 25: Grouping Your Results 365
Grouping Your Data 365
The GROUP BY Keyword 367
GROUP BY with a Join 370
GROUP BY and SUM 370
GROUP BY with WHERE 371
Restricting Results After Grouping 372
The HAVING Clause 374
GROUP BY, HAVING, and SUM 376
Finding Duplicate Records 376
Summary 378
Chapter 26: What Are Indexes? 379
What Is an Index? 379
How to Create an Index 380
Example of Creating an Index 380
Why Is Performance Important? 384
Web Application 384
Overnight Batch Jobs 384
Reporting System 385
Index Using a Join 385
Creating an Index 387
The Disadvantages of Indexes 388
Best Practices for Creating Indexes 388
Summary 389
Part VI: Command Line 391
Chapter 27: Using the Command Line 393
What is SQL*Plus and Why Would You Use It? 394
It’s Fast 394
It’s Easy to Run Scripts 394
It’s Available on Every Oracle Database 395
You Don’t Always Have Access to SQL Developer 395
How to Start SQL*Plus 395
Alternative Login Syntax 397
Login with Two Steps 397
Log in with One Step 398
Running a Query in SQL*Plus 399
Formatting Output in SQL*Plus 401
Copy and Paste into SQL*Plus 404
The Forward Slash Character 404
Exiting SQL*Plus 405
What About SQLcl? 406
How to Download and Run SQLcl 407
Summary 410
Part VII: Appendixes 411
Appendix: How to Find and Navigate the Oracle SQL Reference 413
Finding the Oracle SQL Reference 413
The Oracle SQL Reference 415
Find What You Need Using 417
Find What You Need Using Search 419
Index 423
Table of Conten