Database Querying with SQL

Call Us On:
0118 324 3000

Overview

This SQL training course is for those who need to extract data from relational database servers like Microsoft SQL Server, Oracle, IBM DB2, PostgreSQL and MySQL using ANSI standard Structured Query Language (SQL). While attendees gain much experience designing complex SQL queries using standard functions, aggregate functions and joins, SQL action statements which facilitate data or database modification do not form part of this course. So as to master the syntax of the language, the course is highly practical in nature and the focus throughout is on coding SQL by hand. On completion, a comprehensive set of course notes, examples, tutor and attendee scripts are made available online for each attendee.

Prerequisites

No prior SQL training or relational database experience is assumed.

1. 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

2. 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

3. Retrieving Data with SQL: First Steps

Introducing Queries: The SELECT Statement
The Clauses of the SELECT Statement
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

4. 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

5. 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

6. 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

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

8. 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

9. 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

We can arrange your own private SQL course.

Tailored

Have us build a custom private course tailored to your needs.

Cost Effective

If you are looking to training a group of people private courses can be very cost effective.

Post Course Support

Unlimited post course email support on the course topics.

Delivery Options:

At Your Offices

Virtual Online

Training Centres

This course was great, the trainer was very knowledgeable and has improved my knowledge considerably. The facilities are fantastic. Thank You!

Kurtis Fleming - HM Revenue & Customs

Very informative course, very useful tips on how to work with SQL Management Studio. A lot of SQL quirks explained, a lot of good examples.

Magdalena Piekarska - Veolia

Really good course. lots of hands on practice and clear explanations.

fiona dawe - Covetrus Global Software Services

A great course. The trainers experience and knowledge is a great asset.

Nancy Shering - Equinix Group Ltd

I took so much away from this course and felt Stuart's teaching method was exceptional.

Rowanne Kiehl - SSE

The trainer did a fantastic job of delivering the course and providing understanding

Niall Simmons - Haven Power

Next on Mon 3 Jun

SQL Introduction to Querying

Format:

Virtual or Classroom

Length:

2 Days

Public Course Dates Available!

This comprehensive SQL course will teach you the all the basics on extracting data from an SQL database as well as adding data, removing data and creating new databases.

SQL Fundamentals of Querying

Format:

Virtual or Classroom

Length:

1 Day

Students will learn what SQL is and how it can help them query a database to answer business questions.

SQL Advanced Querying

Format:

Virtual or Classroom

Length:

1 Day

Students will learn advanced queries and how to add, update, and delete data, tables, views, and indexes.

Format:

Virtual or Classroom

Length:

2 Days

Introductory ANSI SQL training for developers or users who only need to extract (not modify) data stored in database servers like: SQL Server, Oracle and MySQL.

Payments processed by card

SQL Introduction to Querying

SQL Fundamentals of Querying

SQL Advanced Querying