What you’ll learn

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

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

Retrieving Data with SQL

• 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
• 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

Using WHERE to Filter Results

• Working with Comparison Operators (=, >= etc)
• Numeric and String Based Filtering
• Filtering Based on Calculations
• Eliminating Duplicate Results with DISTINCT
• Extending Filters with AND and OR
• Range Filtering using BETWEEN and IN
• NULL and its Implications Explained
• Matching Patterns with LIKE

Getting Results From Multiple Tables

• Joins Explained
• The Different Types of Joins
• Creating an Inner Join: WHERE Syntax
• Creating an Inner Join: INNER JOIN Syntax
• Table Aliases
• Joining a Table to Itself
• Outer Joins

Using Standard SQL Functions

• How to Use Standard SQL Functions to Modify Results
• 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

• Using Aggregate Functions (MAX(), SUM(), AVG(), COUNT() etc)
• The Way Aggregate Functions Work
• Using GROUP BY to Group Results
• The Need for HAVING: Filtering the Result Table

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