Loading...
The companion to SQL Fundamentals that turns confident SQL developers into people who understand how databases actually work. Covers schema design and normalisation, concurrency and isolation, indexing and storage internals, the architecture decisions behind production databases (memory allocation, multi-tenancy, backup/recovery, high availability), how application code should integrate with the database (ORMs, SQLCLR, caching, migrations) and security including encryption at rest. The primary dialect remains T-SQL (SQL Server) with PostgreSQL, MySQL and Oracle comparison sidebars. Course 2 of the SQL and Database Engineering learning path; assumes Course 1 (SQL Fundamentals) or equivalent SQL fluency.
JP Instructor
Already have an account? Sign in
Normalisation through 1NF, 2NF, 3NF and BCNF with worked examples, denormalisation as a deliberate choice, OLTP vs OLAP design including star and snowflake schemas, surrogate vs natural keys (IDENTITY, SEQUENCE, GUID), and the design patterns that recur in production: soft deletes, audit columns, temporal tables, type tables and polymorphic associations. 5 lessons + quiz.
16 minutes
14 minutes
14 minutes
14 minutes
14 minutes
10 minutes
Lock types (shared, exclusive, update, intent), lock granularity and escalation, blocking vs deadlocks and reading deadlock graphs, isolation levels in depth (READ UNCOMMITTED through SERIALIZABLE plus SNAPSHOT), when NOLOCK and READ UNCOMMITTED are appropriate and when they bite, hints and concurrency diagnostics with DMVs. 5 lessons + quiz.
14 minutes
14 minutes
16 minutes
12 minutes
12 minutes
10 minutes
Clustered vs non-clustered indexes, B-tree basics, covering indexes and included columns, when indexes hurt write performance, the engine-level storage mechanics for VARCHAR(MAX) including in-row, row-overflow and LOB pages plus PostgreSQL TOAST, LOB indexing constraints and full-text search as the workaround, reading query plans and the DMVs and Query Store tools that surface slow queries. 6 lessons + quiz.
14 minutes
16 minutes
14 minutes
16 minutes
14 minutes
14 minutes
10 minutes
Single database vs many with multi-tenancy patterns, memory allocation in depth (buffer pool, plan cache, min and max server memory, NUMA, Lock Pages In Memory), partitioning and filegroups, FILESTREAM and SECUREFILE, backup and recovery strategies with point-in-time recovery, high availability and disaster recovery (Always On AGs, log shipping, replication), and DBA maintenance with DBCC, statistics updates and index maintenance. 5 lessons + quiz.
14 minutes
15 minutes
14 minutes
15 minutes
14 minutes
10 minutes
Where business logic lives (stored procedures vs ORM vs application code), the N+1 query problem and connection pooling, calling C# from SQL Server with SQLCLR, read replicas and CQRS for read/write separation, ORMs in depth (Entity Framework, Prisma, Hibernate, SQLAlchemy), caching layers with Redis and materialised views, migrations and schema evolution, testing strategies for database-backed apps and the anti-patterns that hurt the most. 6 lessons + quiz.
14 minutes
13 minutes
14 minutes
14 minutes
13 minutes
14 minutes
10 minutes
Authentication options (SQL auth, Windows / Entra ID, IAM), authorisation with roles and GRANT/REVOKE, row-level security and dynamic data masking, SQL injection prevention from the database side, encryption at rest with Transparent Data Encryption and the key hierarchy (SMK to DMK to Certificate to DEK), Always Encrypted for column-level protection, TLS in transit, audit logs (SQL Server Audit, pgaudit), backup encryption and secrets handling. 5 lessons + quiz.
14 minutes
13 minutes
12 minutes
16 minutes
13 minutes
10 minutes
End-to-end project. Design a normalised schema for a moderately complex domain, add appropriate indexes and explain the choices, write the access layer (views, stored procedures, parameterised queries), performance-test and read the execution plans, add a security layer with roles and encrypted columns and audit, and write a one-page architecture decision document covering single vs multi-DB, caching strategy and backup approach. 6-step PROJECT capstone.
360 minutes
Join thousands of students mastering SQL with HardCoded
Sign Up to Get Started