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