Course Curriculum
MODULE 01 Database Fundamentals & Architecture
WHY THIS MODULE

Every application stores its data somewhere. Before you can test a system or write a single query, you need to understand what a database is and how it actually works on the inside. Testers who understand database architecture can find root causes of bugs much faster — instead of just saying "data is wrong," you will understand exactly where it went wrong and why.

Key Topics Covered
DBMS vs RDBMS Relational Model E.F. Codd Rules Database Objects OLTP vs OLAP Storage Manager Query Processor Transaction Manager Buffer Manager 3-Tier Architecture
What You Will Be Able to Do
  • Explain the difference between DBMS and RDBMS and why relational databases dominate enterprise software
  • Describe how the Storage Manager and Query Processor work together to execute your SQL
  • Identify all core database objects: tables, views, indexes, stored procedures, and triggers
  • Distinguish between OLTP (daily app transactions) and OLAP (reporting and analytics) databases
  • Understand the 3-tier architecture that separates your app from the physical data storage
MODULE 02 MS SQL Server Setup & Data Types
WHY THIS MODULE

You cannot practice SQL without a working database server. This module gets you fully set up with the same tools used in real industry projects. Choosing the wrong data type is one of the most common causes of bugs — storing a phone number as an integer, for example, silently drops leading zeros. Getting this right from the start prevents entire categories of defects in your applications.

Key Topics Covered
SQL Server Installation SSMS Setup CREATE DATABASE CHAR / VARCHAR NVARCHAR INT / BIGINT DECIMAL(p,s) DATE / DATETIME BIT Precision & Scale NULL Handling CHAR vs VARCHAR Memory
What You Will Be Able to Do
  • Install MS SQL Server and connect to it using SSMS without any errors
  • Create and navigate databases using the graphical management interface
  • Choose between CHAR, VARCHAR, INT, DECIMAL, DATE, and other types for any column
  • Explain why CHAR uses fixed memory while VARCHAR only uses what it needs
  • Handle NULL values correctly so queries never return unexpected empty results
MODULE 03 DDL — Creating & Managing Tables
WHY THIS MODULE

Tables are the foundation of every database — all your data lives in them. As a tester, you will regularly need to create tables for test data, modify existing table structures to reproduce bugs, and clean up test data between test runs. Understanding DDL commands also helps you communicate clearly with developers when reviewing database change scripts during deployments.

Key Topics Covered
CREATE TABLE ALTER TABLE ADD ALTER TABLE MODIFY ALTER TABLE DROP COLUMN DROP TABLE TRUNCATE TABLE RENAME DDL vs DML TRUNCATE vs DROP IDENTITY columns
What You Will Be Able to Do
  • Create a complete table with correct column definitions and data types from scratch
  • Add, modify, or remove columns from an existing table without losing stored data
  • Understand TRUNCATE (wipe rows fast, keep structure) versus DROP (delete everything including structure)
  • Use IDENTITY columns to auto-generate sequential ID values
  • Correctly categorise SQL commands: DDL builds structure, DML handles data
MODULE 04 Constraints
WHY THIS MODULE

A missing or incorrectly defined constraint is one of the most common root causes of data bugs found in testing. Without a PRIMARY KEY, duplicate records can appear. Without a FOREIGN KEY, orphaned records accumulate silently. Without a CHECK constraint, invalid values like negative prices or future birth dates can be saved. Testers who understand constraints can find and report data integrity defects precisely — not just "the data is wrong" but exactly which rule is missing.

Key Topics Covered
PRIMARY KEY FOREIGN KEY CHECK UNIQUE NOT NULL DEFAULT Referential Integrity Composite Keys ON DELETE CASCADE ALTER TABLE + Constraints
What You Will Be Able to Do
  • Define PRIMARY KEY to uniquely identify every row in a table
  • Link two tables using FOREIGN KEY so orphaned data cannot be created
  • Write CHECK constraints to enforce business rules at the database level
  • Use UNIQUE to prevent duplicate values in non-primary-key columns
  • Apply NOT NULL and DEFAULT to ensure data completeness automatically
  • Add constraints to existing tables using ALTER TABLE without recreating them
MODULE 05 SELECT & Filtering
WHY THIS MODULE

SELECT is the most-used SQL command you will ever write — it is simply how you ask the database questions. As a tester, you will use SELECT queries every single day: to check what data was stored after submitting a form, to verify that a deletion actually removed the right rows, and to debug why a feature is displaying incorrect values. Mastering filtering means you can pinpoint any record in any database in seconds.

Key Topics Covered
SELECT * / SELECT columns WHERE DISTINCT LIKE (%, _) BETWEEN...AND IN / NOT IN IS NULL / IS NOT NULL AND / OR / NOT Column Aliases (AS) CASE WHEN
What You Will Be Able to Do
  • Retrieve specific columns and filter rows to find exactly the data you need
  • Search for text patterns using LIKE with % (any text) and _ (any single character) wildcards
  • Filter by ranges (BETWEEN), lists (IN), and missing values (IS NULL)
  • Combine multiple conditions correctly using AND, OR, NOT with proper operator priority
  • Write conditional column output using CASE WHEN for on-the-fly data transformation
MODULE 06 Sorting & Grouping
WHY THIS MODULE

Applications constantly display aggregated data — total sales by region, number of active users per month, average order value. When you test these features, you need to independently calculate the same numbers from the database to verify the application is computing them correctly. GROUP BY and aggregate functions are exactly how you do that. This module gives you the power to validate any dashboard, report, or summary view in your application.

Key Topics Covered
ORDER BY ASC / DESC GROUP BY HAVING COUNT() SUM() AVG() MIN() / MAX() WHERE vs HAVING Multi-column Grouping NULL in Aggregates
What You Will Be Able to Do
  • Sort query results by any column in ascending or descending order
  • Group rows by a category and calculate summary statistics per group
  • Use COUNT, SUM, AVG, MIN, MAX to verify aggregated values in the application UI
  • Filter groups using HAVING (e.g. only show departments with more than 5 employees)
  • Clearly explain the difference: WHERE filters rows before grouping, HAVING filters groups after
MODULE 07 DML — INSERT, UPDATE & DELETE
WHY THIS MODULE

Every form submission, every profile update, every deleted record in your application runs one of these three commands behind the scenes. As a tester, you need DML commands constantly: to create test data before a test runs, to manually set up edge cases the UI cannot reach, and to clean up data after tests complete. Without knowing DML, you are testing blindfolded — with it, you control the entire database state during testing.

Key Topics Covered
INSERT INTO VALUES INSERT INTO SELECT Multiple Row Insert UPDATE SET WHERE DELETE FROM WHERE DELETE vs TRUNCATE DML & Transactions IDENTITY / AUTOINCREMENT
What You Will Be Able to Do
  • Insert one or many rows of test data into a table quickly using INSERT INTO
  • Copy data from one table into another using INSERT INTO … SELECT
  • Update specific rows safely using UPDATE with WHERE — never accidentally updating everything
  • Delete only the rows you want using DELETE with a precise WHERE condition
  • Set up and tear down test data efficiently as part of your testing workflow
MODULE 08 JOINs
WHY THIS MODULE

Real applications never store all their data in a single table. Customers, orders, products, and payments each have their own table — and JOINs are how you bring them together. If you cannot write JOINs, you can only test one table at a time. With JOINs, you can verify entire end-to-end workflows in a single query: did the order get created, linked to the right customer, and charged correctly? This is the skill that elevates a tester from basic to advanced.

Key Topics Covered
INNER JOIN LEFT OUTER JOIN RIGHT OUTER JOIN FULL OUTER JOIN CROSS JOIN SELF JOIN Table Aliases 3-Table JOINs NULL rows in Outer JOINs Venn Diagram Reading
What You Will Be Able to Do
  • Join two tables and return only rows that match in both using INNER JOIN
  • Include unmatched rows from one table with NULLs for the missing side using LEFT/RIGHT JOIN
  • Find records that exist in one table but have no related record in another — a critical testing check
  • Write 3-table JOIN queries for complex end-to-end data verification
  • Choose the right JOIN type by reading a Venn diagram representation
MODULE 09 Subqueries & Set Operations
WHY THIS MODULE

Some testing questions are two-step: "Are there any users whose account balance is below the minimum allowed threshold?" requires first knowing what the threshold is, then finding the users. Subqueries let you nest one question inside another to answer these in a single database call. Set operations like UNION and EXCEPT let you compare two lists — perfect for verifying that a migration brought all records across, or that a filter is not missing any expected results.

Key Topics Covered
Non-correlated Subquery Correlated Subquery Subquery in WHERE / FROM / SELECT EXISTS / NOT EXISTS UNION UNION ALL INTERSECT EXCEPT CTE (WITH clause)
What You Will Be Able to Do
  • Write subqueries to answer two-step data verification questions in a single SQL statement
  • Use EXISTS to efficiently check whether a related record exists without pulling all data
  • Stack two query results together using UNION (distinct) or UNION ALL (all rows)
  • Find records in both result sets using INTERSECT or only in one using EXCEPT
  • Use CTEs (WITH clause) to break complex queries into readable, named building blocks
MODULE 10 Relationships & Normalization
WHY THIS MODULE

Poorly normalised databases cause a whole class of bugs that appear only at scale: updating a customer's address in one place but not another, records becoming inconsistent over time, or the same data stored in three different formats. Understanding normalisation rules lets you identify design defects in a schema during review — before they make it to production. This is where testers go from reactive (finding bugs) to proactive (preventing them).

Key Topics Covered
One-to-One / One-to-Many / Many-to-Many ER Diagrams Cardinality Junction Tables 1NF (Atomic Values) 2NF (Partial Dependency) 3NF (Transitive Dependency) BCNF Denormalization
What You Will Be Able to Do
  • Read ER diagrams to understand how a real application database is structured
  • Model one-to-one, one-to-many, and many-to-many relationships using correct table designs
  • Apply 1NF: ensure each cell holds one value and there are no repeating column groups
  • Apply 2NF: every non-key column depends on the whole primary key, not just part of it
  • Apply 3NF: non-key columns cannot depend on other non-key columns
  • Identify when a performance trade-off justifies intentional denormalization
MODULE 11 Views, Indexes, Stored Procedures & Functions
WHY THIS MODULE

Real enterprise databases are not just tables — they are built with views, stored procedures, and indexes that the application relies on. When a stored procedure has a bug, you need to understand how to call it, inspect its logic, and verify its output. When a query is slow, you need to understand how indexes affect performance. This module takes you from knowing SQL to understanding how production databases are actually organised and maintained.

Key Topics Covered
CREATE VIEW Updatable vs Read-Only Views Clustered Index Non-Clustered Index CREATE / DROP INDEX CREATE PROCEDURE Input / Output Parameters EXEC Scalar & Table-Valued Functions sp_help / sp_helptext
What You Will Be Able to Do
  • Create views to save and reuse complex queries as if they were tables
  • Explain the difference between a clustered index (affects row storage order) and non-clustered index (a separate lookup structure)
  • Add indexes to dramatically speed up slow queries and drop them when no longer needed
  • Write stored procedures with input parameters and execute them using EXEC
  • Create scalar functions that return a single calculated value for use in queries
  • Use sp_help and sp_helptext to inspect database object definitions in SQL Server
MODULE 12 Transactions, Triggers & Interview Prep
WHY THIS MODULE

Transactions are the safety net of your database — they guarantee that either all of a multi-step operation succeeds, or none of it does. Understanding ACID properties is essential for testing financial, medical, or any high-stakes application. Triggers run automatically when data changes and are often used for audit logging or cascading updates — and if they have bugs, the effects are invisible to the UI and can only be found at the database level. This final module also prepares you for the SQL questions that come up in every QA interview.

Key Topics Covered
BEGIN TRANSACTION COMMIT ROLLBACK SAVEPOINT ACID Properties CREATE TRIGGER AFTER / INSTEAD OF DML Triggers Test Data Generation Data Validation Queries for QA
What You Will Be Able to Do
  • Group multiple SQL commands into one transaction that either fully succeeds or fully fails
  • Undo all changes in a transaction using ROLLBACK when an error occurs mid-way
  • Set a partial undo checkpoint inside a transaction using SAVEPOINT
  • Explain ACID properties (Atomicity, Consistency, Isolation, Durability) confidently in interviews
  • Create triggers that fire automatically after data is inserted, updated, or deleted
  • Write test-support SQL queries to generate test data and verify application database state

Interview Preparation Tips

DELETE vs TRUNCATE vs DROP

DELETE is transactional — it fires triggers and can be rolled back. TRUNCATE removes all rows fast with no rollback and does not fire row-level triggers. DROP removes the table completely — structure and all data gone.

WHERE vs HAVING

WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY. You cannot use aggregate functions (COUNT, SUM) in a WHERE clause — that is exactly what HAVING is for.

INNER JOIN vs LEFT JOIN

INNER JOIN returns only rows with a match in both tables. LEFT JOIN returns all rows from the left table, with NULL for the right side where no match exists — essential for finding missing related records.

Normalisation in QA

Testers must understand 1NF/2NF/3NF to write correct database validation queries, verify data integrity rules, and identify schema design defects before they cause production bugs.

UNION vs UNION ALL

UNION combines two result sets and removes duplicates (slower). UNION ALL combines them and keeps all rows including duplicates (faster). Both require matching column counts and compatible data types.

ACID in Testing

Atomicity — all or nothing. Consistency — valid state before and after. Isolation — concurrent transactions do not interfere. Durability — committed data survives crashes. Test all four properties in data-critical features.

SQL Fundamentals — Revision Topics
  • Basic SELECT syntax review
  • Data types recap (CHAR vs VARCHAR)
  • DDL commands quick reference
  • DML commands quick reference
  • JOIN types visual recap
  • Aggregate functions cheat sheet
  • TCL commands (COMMIT / ROLLBACK / SAVEPOINT)
  • Constraint types summary