Every IT person has a basic knowledge of SQL. But I've noticed that most analysts, developers and testers do not know much more than that, and have not bothered to boost their SQL knowledge. That is a shame, because they're losing lots of precious time. If they would have had more SQL skills they would become much more productive in all situations where they have to query the database.

With this one-day course you will get an overview of many advanced possibilities of SQL, and get acquainted with a number of important design patterns. Nowadays it is of course possible to find this kind of information on the internet, but as long as one doesn't know what to look for then one cannot search for it. There are certainly enough books available about SQL, but almost nobody reads these. Everyone always reads books about his own field (developers read books about programming, testers read books about testing, analysts read books about requirements analysis, etc.), but I never meet people who have also read books about SQL. Most of what they know comes from some SQL Fundamentals course from a long time ago, plus some techniques they learned on the job by trial and error or from more advanced colleagues. But even quite experienced IT professionals I often still see muddling along with their basic primitive skill set, so they spend hours extracting certain data from the database, which they could have done within 10 minutes as well, if they would have had some extra knowledge. A huge waste of time and energy (and therefore also of money)!

That is why I have created a course that meets this need. On this day we will take stock of what kind of problems you run into in your work with relational databases, give you an overview of what else is possible, and how that could help you to be more productive. You will then know which subjects from the course book are relevant to you. Then you can later work through the course book independently and complete the associated exercises at your own pace. This will of course take some time. Real dexterity only comes with experience, but the examples from the course book and the countless links to more extensive background information provide sufficient starting points to actually apply what has been learned.

In addition to the generic course, there are tailored versions for Oracle, SQL Server and PostgreSQL, which attention to implementation-specific peculiarities.

Subjects:
• lesser known SQL constructs (65%)
• design patterns (30%)
• performance issues (5%)

Target group: anyone who regularly runs queries on a relational database.
Required foreknowledge: basic SQL knowledge.

The course is preferably given on location at your company.
All I need is a beamer.

Costs: 666, - per participant (excluding VAT) plus travel allowance.

Duration: one day from 9 to 5 including breaks.
Optionally, the course can also be split up in 2x half a day.

Each participant receives a digital version of the course book.

Contents

1. The building blocks of SQL
2. SQL constructs
         Joins
         WITH clause versus in line views
                Using WITH for updates and deletes
         ORDER BY Clause
                Place of the ORDER BY clause
                Different ways of referencing
                Ordering by invisible columns
                Ordering by complex expressions
                Sort order
         Row limiting clauses
         Standard SQL functions
                Overview
                Scalar functions
                Aggregate functions
                Window functions
         Conditional logic with CASE
                Simple case expression
                Searched case expression
                Handling null values
                Removing duplicates
                Counting unique values
                Too many rows
         Window functions
                Aggregate functions
                Ranking functions
                Range functions
                Different ways to achieve the same result
         Scalar subqueries
         Correlations over more than one column
         Creating auxiliary tables
         Regular expressions
         Recursive queries
         Hierarchical queries
         Higher level aggregration with ROLLUP and CUBE
         User-defined functions
         Dynamic SQL
3. Design Patterns
        Conditional summation
         Comparing two data sets
                Counts
                Symmetric difference
                Compare of a numeric column
         Handling duplicate records
         Distribution of values
                Graphic frequency histogram
                Buckets
                Second level aggregation
         Transforming rows into columns and columns into rows
4. Introduction to performance issues