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

SUMMARY

620

Download the JDK
Create a Connection
Create a New User
Create a Connection with New User
Create a Table
Insert Some Data
LiveSQL
Part II: Viewing Data
Chapter 3: Retrieving Data
How Can You See the Data?
What Is the SELECT Statement?
A Few Things to Note
Our First SELECT Statement
Running a Statement in SQL Developer
Results
Run Script
Errors in SQL
Missing Keyword
Table Does Not Exist
Chapter 4: Selecting Specific Columns
Selecting a Single Column
Selecting Multiple Columns
Selecting All Columns
SELECT Columns or SELECT *?
Chapter 5: Restricting the Results
Why Restrict Data?
What is the WHERE Clause?
Example with a Text Value
Example with a Number Value
Finding Rows Not Equal to a Value
Greater Than
Greater Than or Equal To
Less Than
Less Than or Equal To
Best Practices
Specify Columns on the Left
Select Only What You Need
Chapter 6: Comparing Data
What Is a Partial Match?
Syntax of LIKE
The Percent Wildcard Character
The Underscore Wildcard Character
Searching for Wildcard Characters
Performance
Chapter 7: Applying Multiple Filters
Why Use Multiple Filters?
The AND Keyword
Finding Records That Match a Last Name and a Salary
Finding Records that Match Greater Than and Less Than on Two Columns
The OR Keyword
Finding Records that Match a Last Name or a Salary
Finding Records that Match Greater Than or Less Than on Two Columns
More Than Two Conditions
Multiple AND Conditions
Multiple OR Conditions
Combining Multiple AND and OR Conditions
Specifying the Order with Multiple Conditions
Chapter 8: Working with Nulls
Missing Data
What is NULL?
How Should NULL Be Treated?
Examples of Working with NULL
Adding More Data
Selecting the Records
Restricting the NULL Values
Hiding Null Values
Multiple Queries in One SQL Window
Chapter 9: Removing Duplicate Results
Add New Records
Select the Data
What is DISTINCT?
Finding Unique Last Name Values
Finding Unique Salary Values
Finding Unique Combinations of Values
Chapter 10: Applying Filters on Lists and Ranges of Values
Too Many Conjunctions
The IN Keyword
Example of Using IN
A Longer Value List
Using IN with Text Values
Using IN with Other Clauses
IN with AND?
IN with LIKE?
Filtering on Ranges of Values
The BETWEEN Operator
Using Between with Two Salary Values
Using BETWEEN When Values Don’t Match
Using BETWEEN with Text Values
An Example of an Inclusive and Exclusive Check
Should You Use BETWEEN?
Chapter 11: Ordering Your Data
Results Are Not Ordered
Ordering Results with ORDER BY
ORDER BY Examples
Order by a Text Value
Order by a Number Value
Order by a Column Not in the SELECT Clause
Order by a Number
ORDER BY and NULLs
Order by Multiple Columns
Do You Really Need to Order Your Data?
Chapter 12: Applying Table and Column Aliases
What is a Table Alias?
Example of a Table Alias
Intellisense or AutoComplete
Longer Table Aliases
What If I Don’t Use a Table Alias?
What Is a Column Alias?
Example of a Column Alias
The AS Keyword
Mathematical Operations and Column Aliases
Addition
Subtraction
Multiplication
Division
Column Aliases with Table Aliases
Part III: Adding,Updating,Deleting Data
Chapter 13: Understanding the Data Types
What is a Data Type?
Why Do We Have Different Data Types?
What Are the Different Data Types?
Text Data Types
CHAR
VARCHAR2
NCHAR
NVARCHAR2
RAW
LONG RAW
Number Data Types
NUMBER
INTEGER
FLOAT
DECIMAL
BINARY_FLOAT
BINARY_DOUBLE
Date Data Types
DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
Other Data Types
BLOB
CLOB
Data Type Recommendations
Chapter 14: Creating a Table
Creating Tables Using SQL Code
The CREATE TABLE Statement
Our Employee Table
Storing Office Details
The Office Table
The Sales Meeting Table
Primary Key
Recording the Employee Again
Foreign Key
Chapter 15: Adding Data to a Table
The INSERT Statement
Running an INSERT Statement
Inserting More Data
Inserting Date Values
Saving and Undoing Changes
Best Practices for Inserting Data
Chapter 16: Updating and Removing Data
The UPDATE Statement
Update to a New Value
Checking an Update Statement Before Running It
Update a NULL Value
Update Based on Existing Value
Update a Date Value
Viewing and Updating the Date Format
Update Two Columns
Update Without WHERE Clause
Removing Data from a Table
Deleting a Record
Check What Records Will Be Deleted
Delete All Records in a Table
Chapter 17: Updating or Deleting a Table
Why Update the Structure of a Table?
The ALTER TABLE Statement
Example: Adding a Column
Example: Change a Data Type
Example: Add a Primary Key
Example: Add a Foreign Key
Example: Rename a Column
Example: Remove a Column
Example: Rename a Table
Removing a Table with DROP TABLE
Part IV: Joining Tables
Chapter 18: Inner Join
Multiple Tables
What is a Join?
An Example of a Join
Joins,Formatting,and Table Aliases
An INNER JOIN
Chapter 19: Outer Join
What is an Outer Join?
Writing a Left Outer Join
Showing All Employees with a Left Outer Join
Writing a Right Outer Join
When Would You Use a Right Outer Join?
Writing a Full Outer Join
Using a Full Outer Join on Our Tables
Chapter 20: Other Join Types
The USING Keyword
Updating the Office Table
Writing a Query with the USING Keyword
What is a Natural Join?
Writing a Query with a Natural Join
What is a Cross Join?
Using a CROSS JOIN
Alternative Join Syntax
Inner Join
Outer Join
Why You Shouldn’t Use the Alternative Join Syntax
Chapter 21: Joining Many Tables
Joining Three Tables
Joining Our Tables Together
Using Outer Join Types
Mixing Join Types
Joining Four or More Tables
Part V: Functions
Chapter 22: Using functions in SQL
What is a Function?
Where Can You Use Functions?
Simple Numeric Calculations
The DUAL Table
Number Functions
Concatenation of Strings
String Functions
Changing Case
Checking for Matches on the Same Case
Getting Part of a String
Calculations on Dates
Date Functions
Current Date and Time
Adding Months
Chapter 23: Writing Conditional Logic
The CASE Statement
Simple Case Statement
Searched Case Statement
The DECODE Function
CASE or DECODE?
Chapter 24: Understanding Aggregate Functions
Aggregate Functions
The SUM Function
A Simple SUM Example
SUM with WHERE
SUM with Expression
SUM with DISTINCT
The COUNT Function
Counting All Records
Count a Specific Column
Count with DISTINCT
Count with WHERE
The AVG Function
Average of All Values
Average with DISTINCT
Average with WHERE
The MIN Function
Minimum of All Records
Minimum with WHERE
The MAX Function
Maximum of All Records
Maximum with WHERE
Chapter 25: Grouping Your Results
Grouping Your Data
The GROUP BY Keyword
GROUP BY with a Join
GROUP BY and SUM
GROUP BY with WHERE
Restricting Results After Grouping
The HAVING Clause
GROUP BY,HAVING,and SUM
Finding Duplicate Records
Chapter 26: What Are Indexes?
What Is an Index?
How to Create an Index
Example of Creating an Index
Why Is Performance Important?
Web Application
Overnight Batch Jobs
Reporting System
Index Using a Join
Creating an Index
The Disadvantages of Indexes
Best Practices for Creating Indexes
Part VI: Command Line
Chapter 27: Using the Command Line
What is SQL*Plus and Why Would You Use It?
It’s Fast
It’s Easy to Run Scripts
It’s Available on Every Oracle Database
You Don’t Always Have Access to SQL Developer
How to Start SQL*Plus
Alternative Login Syntax
Login with Two Steps
Log in with One Step
Running a Query in SQL*Plus
Formatting Output in SQL*Plus
Copy and Paste into SQL*Plus
The Forward Slash Character
Exiting SQL*Plus
What About SQLcl?
How to Download and Run SQLcl
Part VII: Appendixes
Appendix: How to Find and Navigate the Oracle SQL Reference
Finding the Oracle SQL Reference
The Oracle SQL Reference
Find What You Need Using Search
Download the JDK
Create a Connection
Create a New User
Create a Connection with New User
Create a Table
Insert Some Data
LiveSQL
Part II: Viewing Data
Chapter 3: Retrieving Data
How Can You See the Data?
What Is the SELECT Statement?
A Few Things to Note
Our First SELECT Statement
Running a Statement in SQL Developer
Results
Run Script
Errors in SQL
Missing Keyword
Table Does Not Exist
Chapter 4: Selecting Specific Columns
Selecting a Single Column
Selecting Multiple Columns
Selecting All Columns
SELECT Columns or SELECT *?
Chapter 5: Restricting the Results
Why Restrict Data?
What is the WHERE Clause?
Example with a Text Value
Example with a Number Value
Finding Rows Not Equal to a Value
Greater Than
Greater Than or Equal To
Less Than
Less Than or Equal To
Best Practices
Specify Columns on the Left
Select Only What You Need
Chapter 6: Comparing Data
What Is a Partial Match?
Syntax of LIKE
The Percent Wildcard Character
The Underscore Wildcard Character
Searching for Wildcard Characters
Performance
Chapter 7: Applying Multiple Filters
Why Use Multiple Filters?
The AND Keyword
Finding Records That Match a Last Name and a Salary
Finding Records that Match Greater Than and Less Than on Two Columns
The OR Keyword
Finding Records that Match a Last Name or a Salary
Finding Records that Match Greater Than or Less Than on Two Columns
More Than Two Conditions
Multiple AND Conditions
Multiple OR Conditions
Combining Multiple AND and OR Conditions
Specifying the Order with Multiple Conditions
Chapter 8: Working with Nulls
Missing Data
What is NULL?
How Should NULL Be Treated?
Examples of Working with NULL
Adding More Data
Selecting the Records
Restricting the NULL Values
Hiding Null Values
Multiple Queries in One SQL Window
Chapter 9: Removing Duplicate Results
Add New Records
Select the Data
What is DISTINCT?
Finding Unique Last Name Values
Finding Unique Salary Values
Finding Unique Combinations of Values
Chapter 10: Applying Filters on Lists and Ranges of Values
Too Many Conjunctions
The IN Keyword
Example of Using IN
A Longer Value List
Using IN with Text Values
Using IN with Other Clauses
IN with AND?
IN with LIKE?
Filtering on Ranges of Values
The BETWEEN Operator
Using Between with Two Salary Values
Using BETWEEN When Values Don’t Match
Using BETWEEN with Text Values
An Example of an Inclusive and Exclusive Check
Should You Use BETWEEN?
Chapter 11: Ordering Your Data
Results Are Not Ordered
Ordering Results with ORDER BY
ORDER BY Examples
Order by a Text Value
Order by a Number Value
Order by a Column Not in the SELECT Clause
Order by a Number
ORDER BY and NULLs
Order by Multiple Columns
Do You Really Need to Order Your Data?
Chapter 12: Applying Table and Column Aliases
What is a Table Alias?
Example of a Table Alias
Intellisense or AutoComplete
Longer Table Aliases
What If I Don’t Use a Table Alias?
What Is a Column Alias?
Example of a Column Alias
The AS Keyword
Mathematical Operations and Column Aliases
Addition
Subtraction
Multiplication
Division
Column Aliases with Table Aliases
Part III: Adding,Updating,Deleting Data
Chapter 13: Understanding the Data Types
What is a Data Type?
Why Do We Have Different Data Types?
What Are the Different Data Types?
Text Data Types
CHAR
VARCHAR2
NCHAR
NVARCHAR2
RAW
LONG RAW
Number Data Types
NUMBER
INTEGER
FLOAT
DECIMAL
BINARY_FLOAT
BINARY_DOUBLE
Date Data Types
DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
Other Data Types
BLOB
CLOB
Data Type Recommendations
Chapter 14: Creating a Table
Creating Tables Using SQL Code
The CREATE TABLE Statement
Our Employee Table
Storing Office Details
The Office Table
The Sales Meeting Table
Primary Key
Recording the Employee Again
Foreign Key
Chapter 15: Adding Data to a Table
The INSERT Statement
Running an INSERT Statement
Inserting More Data
Inserting Date Values
Saving and Undoing Changes
Best Practices for Inserting Data
Chapter 16: Updating and Removing Data
The UPDATE Statement
Update to a New Value
Checking an Update Statement Before Running It
Update a NULL Value
Update Based on Existing Value
Update a Date Value
Viewing and Updating the Date Format
Update Two Columns
Update Without WHERE Clause
Removing Data from a Table
Deleting a Record
Check What Records Will Be Deleted
Delete All Records in a Table
Chapter 17: Updating or Deleting a Table
Why Update the Structure of a Table?
The ALTER TABLE Statement
Example: Adding a Column
Example: Change a Data Type
Example: Add a Primary Key
Example: Add a Foreign Key
Example: Rename a Column
Example: Remove a Column
Example: Rename a Table
Removing a Table with DROP TABLE
Part IV: Joining Tables
Chapter 18: Inner Join
Multiple Tables
What is a Join?
An Example of a Join
Joins,Formatting,and Table Aliases
An INNER JOIN
Chapter 19: Outer Join
What is an Outer Join?
Writing a Left Outer Join
Showing All Employees with a Left Outer Join
Writing a Right Outer Join
When Would You Use a Right Outer Join?
Writing a Full Outer Join
Using a Full Outer Join on Our Tables
Chapter 20: Other Join Types
The USING Keyword
Updating the Office Table
Writing a Query with the USING Keyword
What is a Natural Join?
Writing a Query with a Natural Join
What is a Cross Join?
Using a CROSS JOIN
Alternative Join Syntax
Inner Join
Outer Join
Why You Shouldn’t Use the Alternative Join Syntax
Chapter 21: Joining Many Tables
Joining Three Tables
Joining Our Tables Together
Using Outer Join Types
Mixing Join Types
Joining Four or More Tables
Part V: Functions
Chapter 22: Using functions in SQL
What is a Function?
Where Can You Use Functions?
Simple Numeric Calculations
The DUAL Table
Number Functions
Concatenation of Strings
String Functions
Changing Case
Checking for Matches on the Same Case
Getting Part of a String
Calculations on Dates
Date Functions
Current Date and Time
Adding Months
Chapter 23: Writing Conditional Logic
The CASE Statement
Simple Case Statement
Searched Case Statement
The DECODE Function
CASE or DECODE?
Chapter 24: Understanding Aggregate Functions
Aggregate Functions
The SUM Function
A Simple SUM Example
SUM with WHERE
SUM with Expression
SUM with DISTINCT
The COUNT Function
Counting All Records
Count a Specific Column
Count with DISTINCT
Count with WHERE
The AVG Function
Average of All Values
Average with DISTINCT
Average with WHERE
The MIN Function
Minimum of All Records
Minimum with WHERE
The MAX Function
Maximum of All Records
Maximum with WHERE
Chapter 25: Grouping Your Results
Grouping Your Data
The GROUP BY Keyword
GROUP BY with a Join
GROUP BY and SUM
GROUP BY with WHERE
Restricting Results After Grouping
The HAVING Clause
GROUP BY,HAVING,and SUM
Finding Duplicate Records
Chapter 26: What Are Indexes?
What Is an Index?
How to Create an Index
Example of Creating an Index
Why Is Performance Important?
Web Application
Overnight Batch Jobs
Reporting System
Index Using a Join
Creating an Index
The Disadvantages of Indexes
Best Practices for Creating Indexes
Part VI: Command Line
Chapter 27: Using the Command Line
What is SQL*Plus and Why Would You Use It?
It’s Fast
It’s Easy to Run Scripts
It’s Available on Every Oracle Database
You Don’t Always Have Access to SQL Developer
How to Start SQL*Plus
Alternative Login Syntax
Login with Two Steps
Log in with One Step
Running a Query in SQL*Plus
Formatting Output in SQL*Plus
Copy and Paste into SQL*Plus
The Forward Slash Character
Exiting SQL*Plus
What About SQLcl?
How to Download and Run SQLcl
Part VII: Appendixes
Appendix: How to Find and Navigate the Oracle SQL Reference
Finding the Oracle SQL Reference
The Oracle SQL Reference
Find What You Need Using Search