Prowid logo — programming courses PROWIDIT Education Academy

SQL & Databases

SQL & Databases course — Prowid
Course duration
2 months / 16 lessons
Individual
Per month 200 AZN
Group
Per month 150 AZN
Paying for the full course in a group
15% discount
Installments
50/50
Subscription
from 30 AZN a month
Every backend application is built on a database. In this course you will learn to design relational databases and write SQL queries in PostgreSQL and MySQL.

Enrollment for a new group is open — send a request to reserve your seat

Lessons are taught live in Azerbaijani and Russian.

Enroll in the course Free trial lesson

Or study on your own in your personal account → with a subscription from 30 AZN a month

Course Curriculum

Introduction to databases: relational and NoSQL
PostgreSQL, MySQL and MS SQL Server: differences, installation, DBeaver
Creating tables: data types and constraints
CRUD: SELECT, INSERT, UPDATE, DELETE
Filtering, sorting and pagination: WHERE, ORDER BY, LIMIT
Aggregate functions and grouping: GROUP BY, HAVING
Joining tables: types of JOIN
Subqueries and CTEs
Window functions
Database design: ER diagrams, normalization, relationships
Indexes and query optimization (EXPLAIN)
Transactions and ACID
Views, stored procedures and triggers
Users, access permissions and backups
Security: SQL injection and parameterized queries
Getting started with NoSQL: MongoDB and Redis
Final project: an online store database
How do we teach?

Learning stages

01

Webinar

Students join the lesson live. At the end of each lesson, students are sent a video recording of the class.

02

Homework

Every assignment is based on the topic covered in the webinar. Homework helps you reinforce the material and understand it better.

03

Working with a mentor

The teacher stays in touch with the group both during the course and outside class. Any questions get answered right away.

04

Portfolio

During the course you'll complete several projects on different topics — they'll show employers what you can do.

Self-paced learning

The full curriculum in your personal account

122 topics with theory, examples and practice. The first 4 are free, the rest come with a subscription. Interactive lessons with slides and coding trainers: 108.

Start for free Subscription from 30 AZN a month
Introduction and Setup8 topics
  1. What Is a Database and a DBMS
  2. The Relational Model: Tables, Rows, Columns and Keys
  3. The SQL Language: Standard, Dialects and Command Groups
  4. First Queries in SQLite Without a Server
  5. Running PostgreSQL and MySQL in Docker
  6. Command-Line Clients: psql and mysql
  7. GUI Clients: DBeaver, pgAdmin and MySQL Workbench
  8. The Demo Online Shop Database: Structure and Setup
Tables, Data Types and Constraints12 topics
  1. Creating a Database, a Schema and Your First Table
  2. Numeric Types: Integers, NUMERIC and Floating Point
  3. String Types: CHAR, VARCHAR, TEXT and Encodings
  4. Date and Time Types: DATE, TIMESTAMP, TIMESTAMPTZ and INTERVAL
  5. Special Types: BOOLEAN, ENUM, UUID and JSON
  6. Primary Keys and Auto-Increment: IDENTITY, SERIAL, AUTO_INCREMENT
  7. NOT NULL and DEFAULT: Required Fields and Default Values
  8. UNIQUE Constraints: Single-Column and Composite
  9. CHECK Constraints: Validating Data in the Database
  10. Foreign Keys: Linking Tables
  11. ON DELETE and ON UPDATE: CASCADE, SET NULL, RESTRICT
  12. ALTER TABLE: Changing an Existing Table
Inserting, Updating and Deleting Data7 topics
  1. INSERT: Adding One or Many Rows
  2. INSERT … SELECT, RETURNING and Getting the New ID
  3. UPDATE: Changing Data Safely
  4. DELETE and TRUNCATE: Removing Rows
  5. UPSERT: INSERT … ON CONFLICT and ON DUPLICATE KEY UPDATE
  6. Importing and Exporting Data: CSV, COPY and LOAD DATA
  7. Project: Library Database — Schema and Data
Querying Data: SELECT, Filtering and Sorting9 topics
  1. SELECT: Columns, Expressions and Aliases
  2. WHERE: Comparison Operators and AND, OR, NOT
  3. IN and BETWEEN: Lists and Ranges
  4. LIKE and ILIKE: Pattern Matching
  5. NULL and Three-Valued Logic: IS NULL and IS DISTINCT FROM
  6. ORDER BY: Sorting Results
  7. LIMIT, OFFSET and Pagination
  8. DISTINCT and DISTINCT ON: Unique Rows
  9. Project: Queries for an Online Shop Catalog
Expressions and Built-in Functions7 topics
  1. Numbers in Queries: Arithmetic, Rounding and Type Casting
  2. String Functions: Concatenation, Length, Case and Substrings
  3. Regular Expressions and String Parsing
  4. Date Functions: Current Time, Extracting Parts and Formatting
  5. Date Arithmetic, Intervals and Time Zones
  6. Conditional Expressions: CASE, COALESCE and NULLIF
  7. Project: Cleaning and Standardizing Customer Data
Aggregation and Grouping6 topics
  1. Aggregate Functions: COUNT, SUM, AVG, MIN, MAX
  2. GROUP BY: Grouping Rows
  3. HAVING and the Logical Order of Query Execution
  4. Conditional Aggregation: FILTER and SUM(CASE …)
  5. STRING_AGG, GROUP_CONCAT and ARRAY_AGG: Collecting Values into a List
  6. ROLLUP, CUBE and GROUPING SETS: Subtotals
Joining Tables: JOIN9 topics
  1. INNER JOIN: Combining Related Tables
  2. LEFT JOIN: Keeping All Rows from the Left Table
  3. RIGHT JOIN and FULL OUTER JOIN
  4. CROSS JOIN and Non-Equi Joins
  5. Self-Join: Joining a Table to Itself
  6. Joining Three or More Tables
  7. JOIN with GROUP BY: Reports Across Related Tables
  8. UPDATE and DELETE with JOIN
  9. Project: Querying the Library Database
Subqueries, CTEs and Set Operations8 topics
  1. UNION, INTERSECT and EXCEPT: Set Operations
  2. Scalar and Correlated Subqueries
  3. Subqueries with IN, ANY and ALL
  4. EXISTS and NOT EXISTS: Checking for Related Rows
  5. Subqueries in FROM and LATERAL Joins
  6. CTEs: Common Table Expressions with WITH
  7. Recursive CTEs: Trees, Hierarchies and Sequences
  8. Project: Sales Analytics Report
Window Functions8 topics
  1. Window Functions: OVER and PARTITION BY
  2. ROW_NUMBER, RANK and DENSE_RANK: Numbering and Ranking
  3. LAG and LEAD: Comparing with Previous and Next Rows
  4. Running Totals and Moving Averages
  5. Window Frames: ROWS, RANGE, GROUPS and FIRST_VALUE/LAST_VALUE
  6. NTILE, PERCENT_RANK and Percentiles: Segmenting Data
  7. Window Function Patterns: Gaps, Islands and Sessions
  8. Project: Shop Analytics with Window Functions
Database Design9 topics
  1. ER Diagrams: Entities, Attributes and Relationships
  2. One-to-Many and One-to-One Relationships
  3. Many-to-Many Relationships and Junction Tables
  4. Normalization: Anomalies and First Normal Form
  5. Second and Third Normal Forms
  6. Denormalization: When to Duplicate Data on Purpose
  7. Schema Design Conventions and Common Patterns
  8. Schema Migrations: Versioning and Safe Changes
  9. Project: Blog Database with Comments and Tags
Indexes and Query Optimization9 topics
  1. How Indexes Work: B-Tree and CREATE INDEX
  2. Composite Indexes and Column Order
  3. Covering, Partial and Expression Indexes
  4. Other Index Types: Hash, GIN, GiST and BRIN
  5. When an Index Is Not Needed or Not Used
  6. EXPLAIN: Reading a Query Plan
  7. EXPLAIN ANALYZE: Actual Timing and Statistics
  8. Query Optimization: Common Techniques and Finding Slow Queries
  9. Project: Speeding Up the Shop's Slow Queries
Transactions and Concurrency7 topics
  1. Transactions: BEGIN, COMMIT, ROLLBACK and SAVEPOINT
  2. ACID: What Guarantees a Database Provides
  3. Concurrency Anomalies: Dirty Reads, Lost Updates and More
  4. Isolation Levels and MVCC in PostgreSQL and MySQL
  5. Row Locks: SELECT FOR UPDATE, SKIP LOCKED and Optimistic Locking
  6. Deadlocks and Lock Diagnostics
  7. Project: Race-Free Checkout and Stock Reservation
Views, Functions, Procedures and Triggers6 topics
  1. Views: Saved Queries
  2. Materialized Views
  3. User-Defined Functions: CREATE FUNCTION
  4. Stored Procedures and PL/pgSQL: Variables, Conditions, Loops
  5. Triggers: Automatic Actions on Data Changes
  6. Project: Audit Log and Trigger-Based Automation
JSON and Full-Text Search5 topics
  1. Querying JSON: Operators, Paths and Indexes
  2. Building and Modifying JSON, JSON_TABLE
  3. Full-Text Search in PostgreSQL: tsvector and tsquery
  4. Fuzzy Search: pg_trgm and FULLTEXT in MySQL
  5. Project: Product Search and Attribute Filters
Administration: Privileges, Backups and Maintenance5 topics
  1. Users, Roles and Privileges: GRANT, REVOKE and RLS
  2. Backups: pg_dump, pg_restore and mysqldump
  3. Physical Backups, WAL and Point-in-Time Recovery
  4. Maintenance and Monitoring: VACUUM, Statistics and Active Queries
  5. Project: Access Control and Backups for the Shop
SQL in Applications, NoSQL and the Final Project7 topics
  1. SQL from Code: PHP PDO, Python and Node.js
  2. SQL Injection: How the Attacks Work
  3. Preventing Injection: Parameterized Queries
  4. ORMs and Query Builders vs Raw SQL
  5. NoSQL: MongoDB, Redis and When SQL Is Not the Best Choice
  6. Final Project, Part 1: Online Shop Schema
  7. Final Project, Part 2: Reports, Optimization and Security

Group and individual classes include a teacher, homework review and a certificate. The subscription gives you materials, exercises and progress tracking at your own pace, without teacher feedback.

SQL & databases course: how to enroll

Fill in the form and we will tell you the start date of the next group, the schedule and payment terms. Or come to a free trial lesson first.

+994 77 333 38 36 Or message us on WhatsApp right away info@prowid.com

By submitting the form, you agree that your data will be used only to contact you.

WhatsApp