Beyond the Basics

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 they'll get an overview of many advanced possibilities of Oracle 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's why I created a course that meets this need. After just 1 day of intensive study, one has sufficient knowledge of the most important concepts to be able to independently work through the course book and to make the corresponding assignments. This still takes about 2 days. Real dexterity only comes with experience, of course, but the examples from the course book and the countless links to more extensive background information then offer sufficient starting points to actually apply what you have 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 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
ICT Services