SQL (Structured Query Language) Training

You can connect to relational databases using tools like DBeaver and write basic SELECT queries with filtering and sorting.

1. How relational databases work90 min
   • ACID basics
   • tables and schemas
   • rows and columns
   • client-server architecture
2. Tables, rows, columns and keys90 min
   • primary keys
   • foreign keys
   • data constraints
   • NULL handling
3. Your first SELECT queries120 min
   • SELECT statement
   • FROM clause
   • column aliases
   • DISTINCT keyword
4. WHERE, ORDER BY and LIMIT120 min
   • WHERE clause
   • ORDER BY ASC/DESC
   • LIMIT and OFFSET
   • operator precedence
5. Setting up MySQL and PostgreSQL120 min
   • DBeaver connection
   • CLI tools (psql, mysql)
   • loading sample schemas
   • error troubleshooting

Hands-on project: Connect to a MySQL instance and write queries to extract customer lists matching specific criteria, ordering results and limiting output.

You can clean, transform and filter string, numeric and date data using built-in SQL functions and conditional expressions.

1. Comparison and logical operators120 min
   • AND, OR, NOT operators
   • IN and NOT IN
   • BETWEEN operator
   • operator precedence rules
2. Pattern matching with LIKE120 min
   • wildcard characters (% and _)
   • ILIKE in PostgreSQL
   • regular expressions in SQL
   • performance impact of leading wildcards
3. Working with NULLs120 min
   • IS NULL and IS NOT NULL
   • COALESCE function
   • NULLIF function
   • three-valued logic in SQL
4. Built-in string, number and date functions120 min
   • CONCAT, SUBSTRING, LENGTH
   • ROUND, ABS, CEIL, FLOOR
   • DATE_ADD, DATEDIFF, EXTRACT
   • timezone conversions
5. CASE expressions120 min
   • searched CASE syntax
   • simple CASE syntax
   • nesting conditional logic
   • handling missing values

Hands-on project: Write a query script that standardizes customer contact details, parses timestamps, and categorizes transaction sizes using CASE expressions.

You can summarize large datasets using aggregate functions, group rows, and filter summary results with HAVING.

1. Aggregate functions: COUNT, SUM, AVG, MIN, MAX180 min
   • COUNT(*) vs COUNT(column)
   • SUM and AVG calculations
   • MIN and MAX on dates and strings
   • ignoring NULL values in aggregates
2. GROUP BY180 min
   • GROUP BY clause syntax
   • grouping by multiple columns
   • aggregate column ordering
   • common grouping errors
3. Filtering groups with HAVING180 min
   • WHERE vs HAVING execution order
   • filtering aggregate results
   • combining WHERE and HAVING
   • performance considerations
4. Combining filters and aggregates180 min
   • conditional aggregation with FILTER
   • CASE inside aggregate functions
   • complex metric extraction
   • optimizing aggregation queries
5. Summary reporting180 min
   • ROLLUP and CUBE operators
   • GROUPING SETS
   • formatting tabular output
   • exporting report results

Hands-on project: Build an analytical report summarizing monthly sales revenue, average order values, and filtering out low-volume product categories.

You can combine data across multiple normalized tables using inner, outer, self and cross joins to answer complex business questions.

1. Inner joins120 min
   • INNER JOIN syntax
   • ON clause matching
   • implicit vs explicit joins
   • handling duplicate column names
2. Left, right and full outer joins120 min
   • LEFT OUTER JOIN mechanics
   • RIGHT OUTER JOIN usage
   • FULL OUTER JOIN syntax
   • identifying unmatched records
3. Self joins and cross joins120 min
   • self join for hierarchical data
   • manager-employee relationships
   • CROSS JOIN for Cartesian products
   • generating test data matrices
4. Joining multiple tables120 min
   • chaining three or more tables
   • mixing inner and outer joins
   • table aliases for readability
   • debugging join results
5. Common join pitfalls120 min
   • accidental Cartesian products
   • duplicating rows via 1-to-many joins
   • filtering in ON vs WHERE clause
   • handling NULL keys in joins

Hands-on project: Write a multi-table join query that reconciles orders, inventory, shipments, and customer records to identify unfulfilled orders.

You can modularize complex queries using subqueries and Common Table Expressions (CTEs), including recursive hierarchies and set operations.

1. Scalar and correlated subqueries120 min
   • scalar subqueries in SELECT
   • correlated subqueries with EXISTS
   • NOT EXISTS vs NOT IN
   • subquery performance implications
2. Subqueries in WHERE, FROM and SELECT120 min
   • derived tables in FROM clause
   • filtering with subquery lists
   • column-level subquery lookups
   • subquery scoping rules
3. Common Table Expressions (CTEs)120 min
   • WITH clause syntax
   • naming and referencing CTEs
   • multiple CTEs in a single query
   • readability benefits over subqueries
4. Recursive CTEs120 min
   • anchor member and recursive member
   • UNION ALL in recursion
   • traversing bill-of-materials
   • preventing infinite recursion
5. UNION, INTERSECT and EXCEPT120 min
   • UNION vs UNION ALL
   • finding common records with INTERSECT
   • finding differences with EXCEPT
   • column matching requirements

Hands-on project: Refactor a monolithic reporting query into clean, readable CTEs and write a recursive CTE to traverse organizational reporting lines.

You can perform advanced analytical calculations like running totals, moving averages and ranking without collapsing result rows.

1. The OVER clause and partitions120 min
   • PARTITION BY clause
   • ORDER BY inside window functions
   • frame specifications (ROWS vs RANGE)
   • difference between GROUP BY and windowing
2. ROW_NUMBER, RANK and DENSE_RANK120 min
   • ROW_NUMBER() for pagination
   • RANK() and gap handling
   • DENSE_RANK() for consecutive ranks
   • deduplicating rows with window functions
3. Running totals and moving averages120 min
   • SUM() OVER (ORDER BY date)
   • sliding window frames
   • UNBOUNDED PRECEDING
   • CURRENT ROW and trailing frames
4. LAG, LEAD and offsets120 min
   • LAG() for previous row values
   • LEAD() for next row lookup
   • default offset values
   • calculating period-over-period growth
5. Real analytical queries120 min
   • first and last value extraction
   • NTH_VALUE function
   • percentile calculations
   • cohort retention analysis with windows

Hands-on project: Construct an analytics script calculating rolling 30-day revenue averages, employee salary ranks within departments, and lag metrics.

You can design normalized relational database schemas, define constraints, and manage table structures and data manipulation safely.

1. CREATE, ALTER and DROP120 min
   • CREATE TABLE syntax
   • ALTER TABLE add/drop columns
   • dropping and truncating tables
   • managing database schemas
2. Data types and constraints120 min
   • numeric, string and temporal types
   • NOT NULL and UNIQUE constraints
   • CHECK constraints
   • DEFAULT values
3. Primary and foreign keys120 min
   • composite primary keys
   • foreign key cascade rules
   • ON DELETE CASCADE / SET NULL
   • enforcing referential integrity
4. INSERT, UPDATE and DELETE120 min
   • INSERT INTO syntax and batch inserts
   • UPDATE statements with WHERE
   • DELETE FROM with conditions
   • RETURNING clause in PostgreSQL
5. Designing tables and relationships120 min
   • one-to-one relationships
   • one-to-many relationships
   • many-to-many mapping tables
   • entity-relationship (ER) diagramming

Hands-on project: Create a fully constrained schema for an e-commerce platform with products, orders and customers, enforcing referential integrity.

You can optimize slow queries by creating indexes, reading execution plans, and abstracting complex logic into database views.

1. Creating and using views180 min
   • standard VIEW creation
   • updating views
   • materialized views in PostgreSQL
   • security and abstraction benefits
2. How indexes work180 min
   • B-Tree index structure
   • clustered vs non-clustered indexes
   • composite index column order
   • index maintenance and overhead
3. Reading a query execution plan180 min
   • EXPLAIN and EXPLAIN ANALYZE
   • sequential scans vs index scans
   • cost estimation metrics
   • identifying expensive join algorithms
4. Diagnosing and fixing slow queries180 min
   • common query anti-patterns
   • functions on indexed columns
   • rewriting OR to UNION
   • table statistics and VACUUM
5. Optimization best practices180 min
   • selective filtering strategies
   • avoiding SELECT *
   • partitioning large tables
   • connection pooling considerations

Hands-on project: Diagnose a slow-running reporting query using EXPLAIN, add appropriate multi-column indexes, and create materialised views for dashboards.

You can guarantee data consistency with ACID transactions, encapsulate business logic in stored procedures, and normalize database designs.

1. Transactions and ACID properties120 min
   • Atomicity, Consistency, Isolation, Durability
   • implicit vs explicit transactions
   • transaction lifecycle
   • concurrency anomalies
2. COMMIT, ROLLBACK and isolation120 min
   • COMMIT and ROLLBACK commands
   • SAVEPOINT creation
   • transaction isolation levels
   • handling deadlocks and locks
3. Stored procedures and functions120 min
   • PL/pgSQL and MySQL procedure syntax
   • input and output parameters
   • conditional logic and loops
   • exception handling
4. Triggers120 min
   • BEFORE and AFTER triggers
   • ROW and STATEMENT triggers
   • audit logging with triggers
   • performance risks of triggers
5. Normalization and schema design120 min
   • First, Second and Third Normal Forms (1NF, 2NF, 3NF)
   • functional dependencies
   • when to denormalize for performance
   • anti-patterns in schema design

Hands-on project: Write a stored procedure wrapping a multi-table financial transaction with error rollback handling, and normalize an unformatted spreadsheet schema.

You can read an execution plan and make a slow query fast for a stated reason.

1. How the optimizer chooses a plan90 min
   • Cost estimation
   • Statistics and cardinality
   • Stale statistics
2. Reading EXPLAIN output90 min
   • Seq scan vs index scan
   • Nested loop vs hash join
   • Estimated vs actual rows
3. Index strategy90 min
   • Composite column order
   • Covering indexes
   • Why an index is skipped
4. Rewrites that beat indexes90 min
   • SARGable predicates
   • Avoiding functions on columns
   • EXISTS vs IN

Hands-on project: Take three supplied slow queries on a million-row schema, capture EXPLAIN ANALYZE for each, add or correct indexes, and report the plan change and measured runtime before and after for every one.

You can explain and reproduce a deadlock, then design it out of the schema.

1. ACID and isolation levels90 min
   • Dirty and phantom reads
   • READ COMMITTED vs REPEATABLE READ
   • Choosing a level
2. Locking and MVCC90 min
   • Row vs gap locks
   • Snapshot reads
   • SELECT FOR UPDATE
3. Deadlocks in practice90 min
   • Reading the deadlock log
   • Consistent access order
   • Retry on 1213
4. Long transactions and their cost90 min
   • Lock hold time
   • Undo and vacuum pressure
   • Batching large writes

Hands-on project: Reproduce a deadlock with two concurrent sessions, read it from the engine's deadlock log, fix it by reordering access and narrowing the transaction, then demonstrate the difference between READ COMMITTED and REPEATABLE READ on the same data.

You can change a schema on a large live table without taking the application down.

1. Backup, restore and PITR90 min
   • Logical vs physical backup
   • Binlog and WAL replay
   • Testing the restore
2. Replication and read scaling90 min
   • Async vs semi-sync
   • Replica lag
   • Read-your-writes
3. Online schema change90 min
   • Blocking DDL risks
   • gh-ost and pt-online-schema-change
   • Backfill in batches
4. Partitioning and archival90 min
   • Range partitioning
   • Dropping a partition
   • Retention policy

Hands-on project: Write a reversible migration that adds a NOT NULL column with a backfill to a large table, apply it with a tool that avoids a blocking lock, verify replica lag stayed within budget, and demonstrate a point-in-time restore.

You can design, build, optimize and query an end-to-end relational database system for a real-world application scenario.

1. Design a normalized schema120 min
   • requirements analysis
   • entity relationship modeling
   • defining primary and foreign keys
   • applying normalization rules
2. Load and clean real data120 min
   • bulk loading CSV files
   • handling data type mismatches
   • cleaning dirty records with SQL
   • staging table patterns
3. Answer business questions with advanced queries120 min
   • complex joins and aggregations
   • window functions for trend analysis
   • CTEs for hierarchical reporting
   • view creation for BI tools
4. Tune the database for performance120 min
   • running EXPLAIN ANALYZE on reports
   • adding strategic indexes
   • refactoring inefficient subqueries
   • ensuring transaction safety
5. Portfolio, resume, mock interviews and career prep120 min
   • SQL interview question patterns
   • live technical coding rounds
   • portfolio documentation on GitHub
   • career guidance and placement mock reviews

Hands-on project: Design a complete relational database schema for an online marketplace, load raw CSV datasets, write advanced reporting queries, optimize slow execution plans, and present your solution.