SQL and Database Administration (DBA) Course

Lesson 1 • Entity-Relationship Modelling

Covers ER diagrams, entity types, attributes, and cardinality notation. Connects conceptual design to physical schema creation in later sections.

Lesson 2 • Data Types and Constraints

Surveys numeric, string, date, and Boolean data types alongside NOT NULL, UNIQUE, CHECK, and DEFAULT constraints. Ensures data integrity from the schema level.

Lesson 3 • Introduction to SQL Standards

Explains ANSI SQL history, dialect differences, and the DDL/DML/DCL/TCL command categories. Sets expectations for cross-platform SQL usage in the course.

Lesson 4 • Normalisation and Schema Design

Teaches 1NF through BCNF normalisation rules to eliminate redundancy and anomalies. Provides the schema design skills required for all subsequent SQL work.

Lesson 1 • Joining Multiple Tables

Explains INNER, LEFT, RIGHT, FULL OUTER, and CROSS joins with practical examples. Directly applies the foreign-key relationships defined in Chapter 1.

Lesson 2 • String, Date, and Numeric Functions

Surveys built-in functions for text manipulation, date arithmetic, and numeric formatting. Equips students to transform raw data into presentation-ready output.

Lesson 3 • Subqueries and Set Operations

Covers correlated and non-correlated subqueries plus UNION, INTERSECT, and EXCEPT. Expands query expressiveness for complex filtering and data comparison.

Lesson 4 • Aggregate Functions and Grouping

Teaches COUNT, SUM, AVG, MIN, MAX, GROUP BY, and HAVING for summarising data. Enables analytical queries required in reporting and dashboarding tasks.

Lesson 1 • Views and Materialised Views

Covers creating, updating, and dropping views and materialised views for abstraction and performance. Introduces reusable query objects used in reporting layers.

Lesson 2 • Sequences, Synonyms, and Schemas

Teaches auto-increment sequences, object synonyms, and schema namespacing for organised database design. Prepares students for multi-schema enterprise environments.

Lesson 3 • Inserting, Updating, and Deleting Data

Teaches INSERT, UPDATE, DELETE, and TRUNCATE with safe execution practices. Builds the DML skills needed for application data management and ETL processes.

Lesson 4 • Indexes: Creation and Strategy

Explains B-tree, hash, and composite indexes and when to apply each type. Directly improves query performance covered in Chapter 2.

Lesson 1 • COMMIT, ROLLBACK, and Savepoints

Covers TCL commands for committing, rolling back, and partially undoing transactions. Enables safe, recoverable data modification workflows.

Lesson 2 • Optimistic vs. Pessimistic Concurrency

Contrasts optimistic concurrency control using versioning with pessimistic locking strategies. Connects concurrency theory to application-level design decisions.

Lesson 3 • Locking Mechanisms and Deadlocks

Covers shared, exclusive, and row-level locks plus deadlock detection and resolution strategies. Prepares DBAs to diagnose and fix concurrency bottlenecks.

Lesson 4 • ACID Properties and Transactions

Defines atomicity, consistency, isolation, and durability with real-world examples. Establishes the theoretical basis for all transaction control commands.

Lesson 1 • Auditing and Activity Logging

Covers audit trail configuration, login tracking, and DDL/DML change logging. Provides the monitoring foundation required for security compliance.

Lesson 2 • Row-Level and Column-Level Security

Explains row security policies and column masking to restrict data visibility at a granular level. Supports compliance with data privacy requirements.

Lesson 3 • Encryption and Data Protection

Teaches transparent data encryption, column-level encryption, and secure connection protocols. Protects data at rest and in transit from unauthorized access.

Lesson 4 • Privileges, Roles, and GRANT/REVOKE

Teaches object-level and system-level privileges, role creation, and GRANT/REVOKE syntax. Enables least-privilege access design for enterprise environments.

Lesson 1 • Query Rewriting Techniques

Teaches rewriting subqueries as joins, eliminating redundant operations, and using CTEs effectively. Directly improves query plans without changing database configuration.

Lesson 2 • Index Optimisation Strategies

Covers partial, functional, and covering indexes plus index maintenance tasks. Extends the basic indexing knowledge from Chapter 3 to advanced tuning scenarios.

Lesson 3 • Statistics and the Query Optimizer

Explains how the optimizer uses table statistics, histograms, and row estimates to choose plans. Teaches manual statistics updates to correct bad plan choices.

Lesson 4 • Database Configuration and Caching

Covers memory allocation, buffer pool sizing, connection pooling, and I/O tuning parameters. Complements query-level tuning with system-level performance improvements.

Lesson 1 • Backup Types and Strategies

Covers full, incremental, differential, and logical backups with scheduling best practices. Establishes the recovery foundation that all subsequent HA topics depend on.

Lesson 2 • Failover and Switchover Procedures

Teaches planned switchover and unplanned failover steps, including promotion of a replica. Prepares DBAs to execute controlled transitions with minimal data loss.

Lesson 3 • Point-in-Time Recovery

Explains write-ahead logs, binary logs, and archive logs for restoring to a specific moment. Enables recovery from accidental data deletion or corruption events.

Lesson 4 • Clustering and Load Balancing

Introduces shared-disk clusters, shared-nothing clusters, and connection load balancers. Extends replication knowledge to full high-availability cluster architectures.

Lesson 1 • Database Migration and Upgrades

Covers schema migration tools, version upgrade paths, and zero-downtime migration techniques. Prepares DBAs to execute major changes with minimal service disruption.

Lesson 2 • Capacity Planning and Growth Management

Explains storage forecasting, tablespace management, and growth trend analysis. Prevents outages caused by unexpected disk or resource exhaustion.

Lesson 3 • Table Partitioning Strategies

Covers range, list, hash, and composite partitioning with partition pruning benefits. Enables management of very large tables with improved query and maintenance performance.

Lesson 4 • Job Scheduling and Automation

Covers built-in schedulers, cron-based jobs, and scripted automation for routine DBA tasks. Reduces manual effort and ensures consistent execution of maintenance operations.