SQL and data retrieval - course RUB 38,290. from Netology, training 2 months, Date: December 2, 2023.

Introduction to SQL
Find out why databases are needed. You will understand how a database management system (DBMS) works and become familiar with the PostgreSQL system. Learn what schemas, relationships, attributes and domains are. Install and configure the necessary software.
• What are the different data sources?
• What is IDE and DBeaver
• Creating connections in DBeaver
• Installing a local PostgreSQL Database Server
• Working with *.backup and *.sql
• DBeaver interface
• How to create queries
• ER diagram
• Primary and foreign key constraints
• Metadata: use and access

Relational Databases
You will learn what aliases are and how to use them when working with various schemas and tables in one query. Understand the basic PostgreSQL data types. Learn how to filter, sort, and transform data. You will understand how to work with strings and dates.
• Simple queries
• ROUND command
• Commands ORDER BY, LIMIT, OFFSET
• DISTINCT command
• Logical order SELECT
• WHERE clauses
• NULL and NOT NULL commands
• Concatenation
• LIKE and ILIKE commands
• Methods for working with strings
• Methods for working with dates

Key Features for Data Analysis with SQL
Write simple queries and learn complex ones. Apply joins (JOIN) and aggregate functions (SUM, COUNT, AVG). Understand the chronology of table linking. Master the basic commands: SELECT, WHERE, JOIN, ORDER BY, CASE, UNION, GROUP BY, HAVING. Learn what subqueries are.
• Join types: LEFT, RIGHT, INNER, FULL OUTER, CROSS JOIN
• JOIN when working with unique and non-unique values
• UNION and EXCEPT commands
• CASE command
• Aggregation
• Grouping
• Subqueries. Impact of position on resources spent

Deep dive into SQL. Advanced data operations
Learn how to work with tables using the CREATE TABLE, ALTER TABLE, DROP TABLE commands. Master the INSERT INTO, UPDATE TABLE, DELETE FROM commands. Understand what foreign keys are.
• Create a diagram
• Relationship building
• Formation of attributes, indication of data types and restrictions
• Modification of relationship structure
• Foreign keys
• Entering data into a relationship
• Data modification
• Deleting data and relationships

Working with PostgreSQL. Part 1
Learn to use the aggregate functions sum(), count(),avg(), min() and max() within a window function. Apply the analytical functions lead(), lag(), dense_rank(). Learn what common table expressions and recursive queries are. Learn to work with catalogs.
• Window functions: simple queries, accumulation, LEAD and LAG
• CTE Team
• Recursion. Application with catalogs
• GENERATE_SERIES command

Working with PostgreSQL. Part 2
Learn what representations are. You will understand how to speed up queries and use indexes. You will begin to navigate query patterns. Become familiar with complex data types.
• VIEW command
• MATERIALIZED VIEW command
• Application of representations in denormalization
• Query plan (EXPLAIN ANALYZE)
• JSON command
• ARRAY command

SQL PRO
Learn to create stored procedures and triggers. Learn what normalization and denormalization are. Learn the basics of design and understand what architectural data models are. Master sharding and partitioning. The cost of the module is 10,000 ₽
• Working with the console and interactive mode
• Creating stored procedures
• Creating trigger functions and triggers
• 1-5 Normal forms
• Denormalization
• Horizontal and vertical sharding
• Inheritance in PostgreSQL
• Connecting to clusters using the POSTGRES_FDW extension
• Architectural models
• Data models
• Database design steps
• Difference between OLTP and OLAP systems