Module Descriptions

K0100 – SQL Basics

M0101 – Database theory

This modul introduces you to the topic of database and basic concepts and terms. You will learn about what a relational database is, what components a database consists of, etc. Also a lot of concepts and terms like keys (primary, foreign), relationships(1:1, 1:n, n:m), foreign-key-constraings, normalisation, transactions.

Time needed: 2,00h

 

M0102 – DML + DDL

In this module you will learn how to create database structures (DDL) like talbes, columns, views, etc. Furthermore you learn how to manipulate data (DML) like inserting, updating and deleting rows in a table.

Time needed: 7,00h

 

M0103 – Create one table queries

This module explains you the basics of each SQL query. You will learn how to select a table, how to chose the needed columns and how to create conditions to restrict the shown rows. Furthermore you learn how to do calculations and how to used built-in functions. The last topic is how to order the rows. At the end of this module you are able to create simple queries based on a single table.

Time needed: 5,00h

M0104 – Query multiple tables (Joins)

Usually the data is distributed to multiple tables in a database. In order to answer more complex business questions you need to combine data from different tables. This can be done via Joins, i.e. how to combine the rows from different tables based on conditions. E.g. in one table you have sales orders with a product-no. Also you have the amount of ordered products here. In the product table you have the price. To calculate the turnover you have to multiply the price in one table with the amount in the other table. In order to do this you have to combine those two tables through the product-no. The term for this is Join. In this module you will learn how to work with a join and when to use what kind of Join (INNER, OUTER, FULL, CrossProduct).

Time needed: 5,00h

 

M0105 – Aggregation & Grouping

If there are a lot of rows (thousands or millions) in the result of your query you will often have the requirement to add them one to one or multiple total lines. If you want to create a list with your products and how much you’ve saled of each you will sum all those million sales lines up to one line per product. For this you need grouping and aggregation and this is what you will learn in this module.

Time needed: 5,00h

 

M0106 – Sub Queries

In this module you will learn about so called SubQueries. These are neede for very complex queires. The basic concept is that you can access the result of one query in another query. With that you can build very complex calculations. These subqueries can be used in different locations within a query (FROM, WHERE, …).

Time needed: 8,00h

 

M0107 – Set operations

With Set operations it’s possible to combine result sets of two or more queries. You can do some union sets, interception sets or exception sets.

Time needed: 2,00h

 

K0200 – SQL Advanced

M0201 – Analytical functions

Analytical functions are availbale in modern databases like MS SQL, Oracle, DB2, etc. (unfortunatly not in MySQL). They give you some OLAP similar abilties on relational database. One thing you can do is to define a separate grouping level for each calculation using aggregation fucntions (SUM, AVG, …) . You can then calculate a product total and a report toal within one and the same query without using subqueries. This make the SQLs more handy and also you gain some performance advantages as large datasets don’t have to be fetched several times from disk. Besides this basic functionality it’s possible to define some dynamic windows in order to access rows before or behind the current row. That’s also not possible without extensive use of sub queries. In my opinion this is a must have for every sql developer.

Time needed: 10,00h

 

M0202 – Group By extensions

With the normal Group By it’s only possible to have one level of grouping. With the help of the Group By extensions ROLLUP, CUBE and GROUPING SETS you can have multiple Group By definitions in one query. you then receive more rows. In this module you will learn how to use these extensions and also how to work with the functions GROUP_ID, GROUPING, GROUPING_ID which are used when working with Group By extensions.

Time needed: 5,00h

 

M0203 – Miscellaneous

This modul contains several smaller topics:

  • PIVOT/UNPIVOT
  • WITH Statement
  • regular Expressions
  • Trigger Functions
  • Hierarchical SQL queries

Time needed: 2,00h

 

M0204 – Basics in SQL tuning

This module is an introduction to the huge topic SQL tuning and performace optimization. First we will discuss different concepts and terms like Optimizer, costs, statistics, execute plans, etc. Beside that you will also learn the theory behind some pysical database concepts like Index, Materialized Views and Partitioning. The last part is about some rules you should keep in mind when creating SQLs.

Time needed: 5,00h