Training of the employees of Gazprom-Neft

Under the invitation of the company a series of lectures within 2 courses were held on introduction to the language of SQL and advanced techniques to use SQL for junior / middle data engineers and system analysts.

You are always a student, never a master. You have to keep moving forward.

Conrad Hall

There were 2 educational blocks separated by level.

Introduction into SQL (Practical exercises was supported by AWS Cloud)

  • What are Relational Databases?
  • CAP theorem
  • Database Designs
  • Strategies for analysts (Top-Down, Bottom-Up, Mixed)
  • Conceptual Design (~ Infological Design)
  • Logical Design (~ Datalogical Design)
  • Entity-Relationship Model
  • Notations from Peter Pin-Shan Chen
  • Relational Model Integrity
  • check constraints
  • unique / primary / foreign keys
  • analysis of existing constraints of the basic model of the company and demonstration of the gained knowledge
  • Working with entry-level SQL
  • WHERE clause with the use of AND / OR / NOT operators
  • Working with Sets (UNION [ALL], MINUS / EXCEPT [ALL], INTERSECT [ALL])
  • SQL Joins (from INNER to LATERAL including RECURSIVE and SELF-JOINS with hierarchy)
  • Introduction into OLAP (~ Multidimensional Models)
  • GROUP BY clause on examples of tables of the main company model
  • HAVING on examples of tables of the main company model
  • GROUPING SETS / ROLLUP / CUBE on examples of tables of the main company model

Advanced techniques with SQL (Practical exercises was supported by Yandex Cloud)

  • Explanation about supported Data Structures on Disk and Memory
  • Database Indices
  • How to speed up the query knowing the indexed columns of the tables
  • Obtaining information from the database metadata about existing indices in the company model
  • Analysis of existing indices of the main company model and demonstration of how to use the knowledge
  • The full text search
  • EXPLAIN and data statistics to improve optimisation plan
  • Window Functions and User SQL Aggregators
  • Advanced techniques with OLAP queries
  • SQL Transactions

Leave a Reply