SQL – Queries – Do you know what you can do with a simple SELECT-Statement |
Abstract: |
Today SQL Queries are not only used for ad-hoc Queries, but also replace more and more
native I/O. The SQL Select statement is much more powerful than we can imagine.
Currently SQL provides around 180 scalar functions and 20 aggregate functions. Besides
various ways of joining tables together, data from multiple tables can be merged. Since
Release 6.1 all kinds of Sub- and Grand Totals can be built. With recursive Common Table
expressions or the hierarchical query clause hierarchical and bi-directional dependencies
between the data can be analyzed. With release 7.1 new functions for building XML
documents and for accessing XML data located in either a column of a table or in the IFS
were introduced. And it’s even consuming Web-Services is possible with SQL. … In this
workshop you will learn multiple ways for solving your problems by using a SQL Select
statement. |
Learning Objectives: |
Expand your SQL know-how with not-widely known scalar functions for string handling or
numeric rounding. Handle different conditions with case-clauses within a single SELECT-
Statement. Use multi-dimensional grouping for building totals. Join and merge tables and
views. Instead of coding several SQL statements or building temporary tables, use subselects
and/or common table expressions (CTE) including recursive CTE. Learn how
recursive data can be analyzed. Create XML documents and pick data out of XML
documents and convert them into a relational form. And finally consume Web-Services
directly with SQL. |
Audience: |
Advanced SQL users and programmers who want to enhance their SQL query know-how. |
Agenda: |
- SELECT Statement
- Like Predicate
- Distinct
- Distinct Predicate
- Distinct and Aggregate Functions
- Distinct and NULL Values
- Scalar Functions
- String Manipulation
- Calculation with numeric Values
- Date and Time Calculation
- OLAP Ranking Functions
- Case Clause
- Multi-dimensional Grouping
- Sub-Totals and Grouping Sets
- Joining Tables and Views
- Inner, Left/Right Outer, Full, Left/Right Exception and Cross Join
- Common Table Expressions
- Recursive Common Table Expressions
- Hierarchical Query Clause
- Consuming XML Documents
- Consuming RESTfull Web-Services
|
Level: | Advanced |
Duration: | Full-day |