A Foundation in SQL

An Overview of Relational Databases

• The Role of the Database Server
• Interacting with a Database Server: The Client
• Databases, Schemas, Tables, Rows and Columns
• Primary Keys and Foreign Keys Explained
• Introducing Data Types: Character, Numeric, Date and Time
• The Basics of Database Normalization

Introducing SQL

• Creating and Edit SQL
• About Statements, Batches and Scripts
• Executing and Parsing SQL Scripts
• SQL Syntax and The Rules of SQL
• About Keywords, Identifiers. Operators, Whitespace and Case
• About the Semi Colon
• SQL Conventions and Good Practice

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
• Working with Execution Order
• 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

Getting Results From Multiple Tables

• Qualifying Column Names
• Joins Explained
• The Different Types of Joins
• Creating an Inner Join: WHERE Syntax
• Creating an Inner Join: INNER JOIN Syntax
• Table Aliases: The Need
• Working with Self Joins
• Outer Joins: An Example
• How to Simplify Joins: An Approach

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
• Manipulating 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
• The Need for HAVING: Filtering the Result Table

Working with Subqueries

• Subqueries Explained
• Where you can Use Subqueries
• How to Successfully Construct Subqueries
• Subqueries for Filtering
• Subqueries to Create Derived Columns

Working with Views

• Views Explained
• Advantages of Views
• How to use Views to Simplify your Work
• Creating Views
• Dropping Views

Inserting, Updating and Deleting Data

• Inserting Single Rows
• Inserting Multiple Rows
• Inserting Rows by Column Position
• Inserting Rows by Column Name
• Dealing with Auto-Incrementing Values
• Dealing with Nulls when Inserting
• Inserting Data from one Table into Another
• Updating Data
• Deleting Data
• Modifying Data through a View

Inserting, Updating and Deleting in a Transaction Environment

• Transactions Explained
• Why Use Transactions?
• Protecting Yourself with Transactions
• How to Setup a Transaction Environment
• Checking Your Work
• Undoing your Changes with ROLLBACK
• Committing the Transaction

Creating and Modifying Tables

• Using CREATE TABLE
• Specifying Primary and Foreign Keys
• Using DEFAULT values
• Constraining Input
• Using Temporary Tables
• Creating a New Table From an Existing Table
• Altering and Dropping Tables

On Site Requirements

Remember, we provide all equipment and software required to deliver a course at your premises. Aside from this, we need a suitably quiet and equipped room with enough work space for each attendee and a whiteboard or flipchart. Most courses involve the use of a PC projector and we bring our own. But either a projector screen, or usually just a clear wall, would be very helpful.

Other Courses to Consider

• LSQ-60: Database Querying with SQL
  Constructing successful SQL queries to extract data from relational database servers like: SQL Server, Oracle, DB2, MySQL and PostgreSQL.
• LSQ-61: Introduction to SQL
  An introduction to SQL for those who need to interact with relational database servers.

on site training courses available in:  

• London
• , Birmingham
• , Edinburgh
• , Manchester
• , Scotland
• , Glasgow
• , Nottingham
• , Midlands
• , Bristol
• , Wales
• , Cardiff
• , Dublin
• , Belfast
• , Leeds
• , Liverpool
• , Sheffield
• , Reading
• , Oxford
• , Cambridge
• , Southampton
• , Newcastle
• , Durham
• , Warrington.

and across the UK and Ireland