request@systematix.co.uk
supermarketBasket
Call Us On:
0118 324 3000

Exciting News! Systematix is rebranding to Future Savvy. Same great services, with a fresh new look. Learn More

banner image

Database Querying with SQL

2 Day Instructor-led
course icon

Course Information

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.

Course Outline

Download PDF Outline
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

Dates & Availability

We currently don’t have any dates scheduled for this training course but offer it as a corporate, private course for your own team.

Please use the enquire button below.

Private Courses

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:
On-Site At Your Offices
Virtual Online Virtual Online
Training Centres Training Centres

What Our Clients Think

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

Very good at tailoring the course to my needs. Very individual and attentive.

Andrew Griffin - Blackfin

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

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

Rowanne Kiehl - SSE

Course is well structured and instructor delivered the content, and more, perfectly. No question left unanswered, no theory unexplained.

Aidan Hamill - Intact Software

Related Courses

Next on Tue 4 Feb

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.

Database Querying with SQL

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.