Advanced SQL Skills
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 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.
• 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.
- The building blocks of SQL
- SQL constructs
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
Row limiting clauses
Standard SQL functions
Conditional logic with CASE
Simple case expression
Searched case expression
Handling null values
Counting unique values
Too many rows
Different ways to achieve the same result
Correlations over more than one column
Creating auxiliary tables
Higher level aggregration with ROLLUP and CUBE
- Design Patterns
Comparing two data sets
Compare of a numeric column
Handling duplicate records
Distribution of values
Graphic frequency histogram
Second level aggregation
Transforming rows into columns and columns into rows
- Introduction to performance issues