SQL Training Advanced

This advanced SQL training course will further develop your use of SQL to manage a relational database. By the end of this course you will be a very capable SQL user confident to use its more complex commands when viewing and managing tables in a relational database. You will also be able to run complex queries.

To develop the delegate’s use of SQL to and advanced level. this course introduces data manipulation language and database definition language.

After this course, you will have a good understanding of all DML statements (SELECT, INSERT, UPDATE, DELETE) and all of the DDL statements needed to build database objects (CREATE, DROP, ALTER). This course also covers the use of views, access control, transaction control, advanced select queries and using SQL scripts.

Delegates for this course should have a good basic understanding of SQL select statements. This course assumes knowledge of SQL to the level of our Introduction course.

Our courses run from 9.30 am to approximately 4.30pm. We aim to include everything that you will need for a productive day’s training. When you book a course with use you get the following:
  • A full colour advanced SQL course manual.
  • The exercises that you have worked on during your course to allow you to practice afterwards.
  • USB stick to take the exercises away (where relevant).
  • Freshly prepared lunch in our onsite cafeteria.
  • Refreshments though out the day.
  • Training in fully air conditioned facilities from an experienced SQL instructor.

We do not have a dress code. Please come wearing whatever your are most comfortable wearing.

We start with a few key concepts. We look at how relational databases work and how SQL fits into that framework. We also have a look at the ANSI (American National Standards Institute) standards that were introduced in the 89 and 92 which codified SQL.

The topics trained include:

  • Relational Database Concepts
  • The role of SQL
  • ANSI 89 & 92 standards

This session covers SQL SELECT statements very quickly. It aims to ensure that all delegates are at the same level. It also tries to get rid of any bad habits that delegates may have picked up!

The key topics trained include:

  • SELECT statement syntax
  • WHERE clause
  • Single row functions
  • Joining Tables
  • ORDER BY clause
  • GROUP BY clause
  • Aggregate functions

A SQL script is simply a set of SQL commands which can be saved in a file. This means that you don’t have to retype queries that you use repeatedly. This session introduces scripts as well as using parameters in your query. Finally it looks at query output presentation. There is no point in generating a very useful query if the output isn’t usefully presented.

The key topics trained include:

  • Parameterised queries
  • Producing more readable output
  • Creating script files

In this section we take a look at the more advanced SQL syntax around editing your database. We look at the more advanced applications of the INSERT, UPDATE and DELETE commands. We also introduce transaction processing although we return to this later in the course.

The key topics trained include:

  • Working with INSERT
  • Working with UPDATE
  • Working with DELETE
  • Using begin transactions with commit and rollback

The ability to run functions in SQL queries is enormously useful. It saves subsequent processing of the data that a query has produced. It also allows for more complex data querying as the product of functions can be used within queries. This section of the course covers working with functions in detail. It includes working with dates which can be a little tricky to the uninitiated.

The key topic trained is:

  • Date/String/Maths

Larger databases with concurrent users need to manage that access to ensure that the data they contain remains consistent. For example if two updates to the database are run simultaneously how is that managed by the database software. Transaction control is what allows and manages concurrent database access. In this section we look at implicit and explicit locking which controls what resources are available to which users of a database and when. We also look at the COMMIT command which is used to save changes to the database.

The key topics trained include:

  • Principles of transaction processing
  • Read Consistency, implicit and explicit locking
  • COMMIT

A SQL query of any complexity is likely to need to draw data from more than one table. This section of the course looks at the various ways that data from multiple tables can be combined when creating a query. There are a number of different ways that data from different tables can be queried using JOINs. We look at how and when to use the various JOIN commands.

They key topics trained include:

  • Joining multiple tables in a SELECT statement
  • Different join types
    Using INNER Join and WHERE to create Joins

This section covers all aspects of table management. It is likely that once your database is set up you will want to update the way that it holds data, or the data that it holds. We look in detail at how to tables are defined and how to change the definition. We also look at constraints on a table, along with indexes. Constraints can be applied at a column or row level and help ensure data integrity in a working database. We finally look at SQL datatypes.

The key topics trained include:

  • Viewing the definition of tables
  • Creating tables
  • SQL data types
  • Altering table definition
  • Table and column constraints
  • The role of Indexes

Views are very useful practical tool. A view is simply the output of a query. However, it is saved so that if a query that needs to be run repeatedly can be saved as a query, saving repeatedly creating the query. Views can also be useful for updating data, and for security. Database users can be given access to views where you would not want to give them access to the underlying tables.

The key topics trained are:

  • Creating a view
  • Selecting data using a view
  • Update, Insert and Delete through views
  • Dropping views
  • Altering view definition
  • Inline views

Intersect and Except are very useful when carrying out two or more SELECT queries. Intersect will only show the results which occur in both queries. Except will return the rows from the first query that are not returned by the second SELECT query. We also look at working with sub-queries and using Exists and IN.

The key topics trained include:

  • Using Intersect
  • Using Except
  • Exists vs. IN

The final section of this course looks at the most advanced ways of controlling SELECT queries with SQL. We look at ordering the results of a query and also producing sub-totals from it. We also spent some time on sub-queries. These allow you to control the data returned from your main query with the results from another query (known as a sub-query).  We finally finish by looking at applying conditions to your queries using Case and other operators.

The key topics trained include:

  • Using set operators
  • Controlling the order of rows
  • Extensions to GROUP BY clause
  • Advanced sub-queries
  • Correlated sub-queries
  • Using Case
  • Using Except, Intercept and Exists.