Advanced Hands-on SQL Experience («ADVSQL»)

The workshop is 85% practical and has a minimal skill level requirement. There will be a pre-course virtual lab environment in which participants can test their skills and fill any missing gaps (see Requirements).

Topics:

SQL-Basics : Review Basics from the SQL Standard Course

• SELECT, Column Alias, Calculated Columns
• WHERE
• FROM, JOINS , Table Alias
• GROUP BY, HAVING
• CASE Statement (SIMPLE and SEARCHED)

Using COMMON TABLE EXPRESSIONS

• WITH Keyword

SET Operators

• UNION
• INTERSECT
• EXCEPT

TOP n/LIMIT Queries (ANSI SQL-2008)

• OFFSET
• FETCH FIRST/NEXT

DATATYPES, FUNCTIONS and CONVERSIONS

• Overview/Awareness Datatypes
• Working with Character Data, String Functions
• Working with Date and Time Data, DateTime Functions
• Using Data Type Conversion Functions, CAST (and CONVERT)
• Working with Collations

SQL WINDOWING Functions

• OVER, PARTITION BY Clause
• Windowing Functions : 
• RANK()
• DENSE_RANK()
• ROW_NUMBER()
• NTILE()
• LAG() 
• LEAD()
• FIRST_VALUE()
• LAST_VALUE()

Optional modules:

SUBQUERIES

• Stand-alone subqueries
• Correlated subqueries

RECURSIVE COMMON TABLE EXPRESSIONS

System Versioned Temporal Tables

• PERIOD FOR SYSTEM_TIME
• FOR SYSTEM_TIME AS OF

Inserting New Values

• IDENTITY
• SEQUENCES

PIVOT und UNPIVOT

MERGE (ANSI SQL-2003)

• MERGE Statement
• WHEN MATCHED
• WHEN NOT MATCHED

Basic Indexing to improve Query Peformance

• CLUSTERED INDEX
• NONCLUSTERED INDEX
• COVERING INDEX
• Query Execution Plans
• IMPLICIT Conversions and Functions in Predicates

Variables & Basic Control-of-flow

• DECLARE variables
• Assign Values
• Escaping Single Quotes

Stored Procedures

• Create Basic Stored Procedure
• Execute a Stored Procedure
• Stored Procedure OUT Parameters

Dynamic SQL

• EXEC()
• sp_executesql

APPLY Operator (MSSQL)

• CROSS Apply
• OUTER Apply
• Table-valued Functions
• Derived tables

Rolling Up Values

• Rolling up values from multiple rows into one
• FOR XML PATH
• STUFF Function

Reporting and SQL specialists as well as data experts who work with relational databases and need to formulate advanced SQL queries. Developers who want to work with an SQL-enabled database or build applications using the SQL capabilities of the database system.

Practical ability to write SQL Queries using the SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY clauses as well as INNER and OUTER JOINS when given a 'data question' in a relational database (covered in the «Standard SQL-3» course).

Once the workshop is guaranteed to take place, participants will get access to a virtual lab environment in which they can test their prerequisite SQL skills and have an opportunity to fill any gaps using specific exercises.

COURSE
SQL Fundamentals In-Depth («SQLV2»)
2 days
• Berne, Zürich
CHF
1'800.–
Learn more
COURSE
Standard SQL-3 («SQL»)
2 days
• Basel, Berne, Geneva, Lausanne, Virtual Training, Zürich
CHF
1'800.–
Learn more

This course is manufacturer-independent. So it does not matter if you work with Oracle, SQL Server, MySQL, DB2, PostgreSQL, MariaDB, TeraData or other relational database systems.