SQL for BI and Data Science (EN/NL/FR)

During this 2 day course, you learn how to use SQL for analysing data. We dive into a thorough discussion on the statistical, analytical and OLAP functions offered by SQL. Important ones are discussed and demonstrated; labs allow the participant to get an idea of the applicability of these SQL functions and options. The SQL syntax covered in this course is platform independent.

In order to have the full benefit of this course, it is important to be really proficient with SQL; course targets everyone involved in the development of business reports, by means of SQL.

Remark: Course description in English; Dutch and French versions are available on the ABIS website. Courses are planned in Dutch, English, and French. Consult the ABIS website for alternate course formats (standard classical, virtual classroom, individual, ...)

Main topics:

• Data warehouse, business intelligence, Data Science -- positioning
  • Extract / Transform / Load (ETL)
  • the dimensional model
• Statistics and analytics -- support in SQL
  • Purpose and context -- multi-platform support.
  • AVG, CORRELATION, COUNT, COVARIANCE, MAX, MIN, RAND, STDDEV, SUM, VARIANCE, MEDIAN, ...
• Online analytical processing in SQL
  • Purpose and context -- multi-platform support.
  • RANK, DENSE_RANK, ROW_NUMBER, PARTITION BY, ORDER BY, ROWS, RANGE, GROUP BY, GROUPING SETS, ROLLUP, CUBE, OVER
  • WINDOWING functions: SUM ... OVER ..., LEAD; LAG and similar aggregation functions, and their application in a.o. trend analysis
  • Modelling: histograms; linear regression; normality testing; trend analysis; confidence intervals; ...
  • Text analytics; regular expressions

Intended for/Audience: This course targets everyone involved in the development of business reports, by means of SQL statements on relational database systems or Hadoop (Hive) or Spark SQL.

Background/Prerequisites: In order to have the full benefit of this course, it is important to be really proficient with SQL (level SQL workshop and being familiar with complex queries; having followed the SQL advanced course is an advantage). Furthermore it's helpful to have a basic understanding of data warehouses (see e.g. our course Modern data warehousing and business intelligence) or Big Data (see e.g. Big data architecture and infrastructure) and be aware of some elementary statistical terminology (see e.g. Statistics fundamentals).

Training Method/Didactics: Class training alternated with exercises. These can be done on Db2, Oracle, SQL Server, Hive, or Spark. 

Duration: 2 days.