Bespoke SQL in Oracle

There are no specific requirements needed to attend this course.

An Overview of Relational Databases

• The Role of the Database Server
• Interacting with a Database Server: The Client
• Using an Integrated development Environment (IDE)
• Databases, Schemas, Tables, Rows and Columns

Introducing SQL

• Creating and Editing SQL
• About Statements, Batches and Scripts
• Executing and Parsing SQL Scripts
• Using Comments
• SQL Syntax and The Rules of SQL
• About Keywords, Identifiers, Operators, Whitespace and Case
• About the Semi Colon
• SQL Conventions and Good Practice
• Working to Minimise, Trap and Find Errors

Retrieving Data with SQL: First Steps

• Introducing Queries: The SELECT Statement
• The Clauses of the SELECT Statement
• About Optional Clauses and Mandatory Clauses
• Using FROM to Specify the Source Table(s)
• Retrieving Entire Tables
• Retrieving Specific Columns
• The Importance of Clause Order
• How to Build Successful Queries
• Types of Output: About the Result Set
• Using Column Aliases to Rename Columns
• Performing Calculations
• Using Numeric and String Operators to Create Derived Output
• Ways of Limiting the Output
• Using ORDER BY to Sort the Output
• Ways of Working: Some Tips

Using WHERE to Filter Results

• Working with Comparison Operators (=, >= etc)
• Numeric and String Based Filtering
• Filtering Based on Calculations
• Eliminating Duplicate Results with DISTINCT
• The Execution Order and its Traps
• Column Aliases: Where You Can and Cannot Use Them
• Extending Filters with AND and OR
• Solving AND/OR Difficulties with Brackets
• Excluding Results with NOT: Some Tips
• Range Filtering using BETWEEN and IN
• NULL and its Implications Explained
• Catering for NULL
• Matching Patterns with LIKE

Learning to Join

• Qualifying Column Names
• Joins Explained
• The Different Types of Joins
• Mastering the Inner Join: WHERE Syntax
• Mastering the Inner Join: INNER JOIN Syntax
• Table Aliases: Why We Need Them
• Joining Two Tables
• Joining Multiple tables
• How to Simplify Joins: An Approach

Getting Confident with SQL Joins

• The Importance of the Database Diagram
• What if there is No Database Diagram?
• Primary Keys and Foreign Keys Explained
• Locating Primary Keys and Foreign Keys
• Do we Need Primary Keys and Foreign Keys?
• Approaching Joins the Right Way
• Avoiding Common Join Errors

Using Standard SQL Functions

• How to Use Standard SQL Functions to Modify Results
• How to Find the Right Function
• Mathematical, String and Conversion Functions
• Functions for Modifying and Calculating Dates
• Formatting Numbers to Two Decimal Places
• Replacing NULL with a Specific Value
• Using Standard Functions in WHERE
• Using CASE to Specify Output Conditions

Working with Dates

• Understanding How Dates are Stored
• Introducing Date Functions
• Converting Text Dates To Date Format
• Establishing Today's Date
• Extracting Parts of a Date (Day, Month, Year)
• Displaying Dates in Specific Date Formats
• Filtering with Dates

Grouping and Summarizing Results

• The difference Between Tabular and Scalar Results
• Using Aggregate Functions (MAX(), SUM(), AVG(), COUNT() etc)
• The Way Aggregate Functions Work
• Where to Use and Where Not to Use Aggregate Functions
• Using GROUP BY to Group Results
• Get GROUP BY Right Every Time!
• The Need for HAVING: Filtering the Result Table