Domain 2 of the official DP-900 skills outline includes "Identify common structured query language (SQL) statements" and "Identify features of relational data." The real questions do not ask you to write SQL. They ask you to classify and apply rules: "Is this statement DDL or DML?" or "What stops an order from referencing a customer ID that does not exist?" Get the concepts right and you can answer them without any development experience.
This post is a supplementary entry to Relational Data Fundamentals in our DP-900 study series. Building on the SELECT, INSERT, UPDATE, DELETE, JOIN, keys, and normalization covered there, it walks through SQL statement categories, the order in which a query is processed, constraints and the three kinds of integrity, NULL, index types, and denormalization, in roughly the order they show up on the exam.
---
Why Is SQL Split into Several Groups?
Think about putting up a new building. First you draw the blueprints and create floors and rooms, then people and furniture move in and out, and key cards decide who can enter which floor. SQL works the same way: some statements build the structure, some put data in and change it, and some decide who has access.
SQL (Structured Query Language) is the standard language for communicating with relational databases. It was standardized by ANSI in 1986 and by ISO in 1987. Each database product, however, adds its own extensions to the standard, which results in different dialects.
| Dialect | Used by | |------|------| | Transact-SQL (T-SQL) | SQL Server, Azure SQL Database, Azure SQL Managed Instance, SQL Server on Azure VMs | | PL/pgSQL | PostgreSQL | | PL/SQL | Oracle |
Exam tip: "The SQL dialect used by Azure SQL Database and SQL Managed Instance" → Transact-SQL (T-SQL). "The organizations that standardized SQL" → ANSI and ISO.
---
DDL, DML, DCL, and TCL
Microsoft Learn groups SQL statements into three categories: DDL, DML, and DCL. Exam questions sometimes add TCL, which handles transactions, so learn all four together.
!SQL statement types at a glance
DDL: Statements That Define Structure
DDL (Data Definition Language) creates, modifies, and removes database objects such as tables, views, and stored procedures. CREATE, ALTER, DROP, and RENAME are the main statements, and the T-SQL documentation also classifies TRUNCATE TABLE as DDL. DROP removes an object permanently, so without a backup every row in a dropped table is gone too.
In this example, PRIMARY KEY marks the column that uniquely identifies each row, and NOT NULL marks a mandatory column that must always have a value.
DML: Statements That Work with Rows
DML (Data Manipulation Language) reads rows (SELECT), adds them (INSERT), changes them (UPDATE), and removes them (DELETE). SELECT, UPDATE, and DELETE apply to every row by default, so you normally narrow them with a WHERE clause. SQL has no "are you sure?" prompt, so an UPDATE or DELETE without WHERE changes or removes the whole table.
DCL: Statements That Manage Permissions
DCL (Data Control Language) grants permissions to users or groups (GRANT), denies them (DENY), or removes previously granted permissions (REVOKE). Database administrators are the main users.
TCL: Statements That Commit or Undo Transactions
TCL (Transaction Control Language) groups several operations into one transaction and either confirms them with COMMIT or reverses them with ROLLBACK. In T-SQL you start with BEGIN TRANSACTION and can set an intermediate savepoint with SAVE TRANSACTION. It is used for work such as a funds transfer, where the withdrawal and the deposit must succeed together or be undone together.
| Category | What it does | Main statements | Exam keywords | |------|------|------|------| | DDL | Defines, changes, and removes object structure | CREATE, ALTER, DROP, RENAME, TRUNCATE TABLE | Create a table, add a column, change structure | | DML | Reads, adds, changes, and deletes rows | SELECT, INSERT, UPDATE, DELETE | Query data, add rows, modify values | | DCL | Grants, denies, and revokes permissions | GRANT, DENY, REVOKE | Permissions, allow access | | TCL | Commits or undoes transactions | COMMIT, ROLLBACK, SAVE TRANSACTION | Confirm, roll back |
Exam tip: "Add a column to an existing table" → ALTER (DDL). "Give a user SELECT permission" → GRANT (DCL). "COMMIT and ROLLBACK" → TCL, not DCL. Answer options that play on the similar names DDL, DCL, and DML are common.
---
In What Order Is a Query Processed?
The order in which you write a SELECT statement is not the order in which the database processes it. Once you know the processing order, the difference between similar clauses such as WHERE and HAVING becomes obvious.
| Processing order | Clause | Role | |------|------|------| | 1 | FROM (including JOIN) | Which tables to read from | | 2 | WHERE | Filters rows before aggregation | | 3 | GROUP BY | Groups rows with the same values | | 4 | HAVING | Filters aggregated results (groups) | | 5 | SELECT | Columns and calculations to return | | 6 | ORDER BY | Sorts the results (ascending ASC by default, descending DESC) |
For aggregate functions used with GROUP BY, remember these five: COUNT, SUM, AVG, MIN, and MAX. Without ORDER BY, the order of result rows is not guaranteed.
Object names are written with dots, narrowing the scope from left to right. In SQL Server–based databases the order is database.schema.table (for example, SalesDB.dbo.Customer).
Exam tip: "Filter rows before aggregating" → WHERE. "Filter the aggregated groups by a condition" → HAVING. "Sort the results" → ORDER BY.
---
Constraints: Rules That Keep Bad Data Out
Think about the rules at a concert entrance. You need a ticket (NOT NULL), each seat number is used by only one person (UNIQUE), the ticket must actually have been sold (FOREIGN KEY), and age limits apply (CHECK). Constraints are how a database enforces rules like these automatically.
| Constraint | What it does | NULL | Number per table | |------|------|------|------| | PRIMARY KEY | Uniquely identifies each row | Not allowed | 1 | | FOREIGN KEY | Only allows values from another table's primary key (or UNIQUE column) | Allowed | Many | | UNIQUE | Prevents duplicate values in a column | SQL Server allows only one NULL per column | Many | | NOT NULL | A value must always be present | Not allowed | Many | | CHECK | Values must satisfy a condition (for example, Price >= 0) | NULL evaluates to UNKNOWN and passes | Many | | DEFAULT | Inserts a default value when none is given | Not applicable | 1 per column |
A few more points are worth remembering.
Composite key: when one column cannot tell rows apart, two or more columns are combined into the primary key. Order number plus line number, or flight number plus seat number, are typical examples. Referential actions: when a parent row is deleted, you can choose to delete the child rows too (ON DELETE CASCADE) or set their foreign key to NULL (SET NULL). Self-referencing relationship: a foreign key points to the primary key of its own table to represent a hierarchy, such as a manager ID in an employee table that refers to another employee's ID.
Exam tip: "Primary key vs UNIQUE" → both prevent duplicates, but a primary key does not allow NULL and there is only one per table. "Price must be zero or more" → CHECK.
---
The Three Kinds of Integrity, and NULL
Integrity means data stays accurate and consistent. Relational databases use constraints to protect three kinds of integrity.
| Integrity | Meaning | How it is enforced | |------|------|------| | Entity integrity | Every row is uniquely identified | Primary key (no duplicates, no NULL) | | Referential integrity | Relationships between tables are always valid | Foreign key (no values missing from the parent) | | Domain integrity | Column values stay within the allowed range | Data types, CHECK, NOT NULL, DEFAULT |
Without referential integrity you get orphan records, such as orders that point to customers who do not exist. A foreign key constraint blocks such rows and also blocks, or handles, attempts to delete a parent row that child rows still reference. Non-relational databases usually do not support foreign key constraints, so the application has to manage that consistency itself.
NULL Is Neither Zero Nor an Empty String
NULL is a marker for a value that is missing or unknown. The number 0 and a zero-length string ('') are actual values, so they are not NULL. Comparing anything with NULL yields UNKNOWN rather than true or false, so to find rows with NULL you use IS NULL or IS NOT NULL instead of = NULL.
Exam tip: "Prevent orders from containing customer IDs that are not in the Customer table" → referential integrity (foreign key). "A primary key cannot be NULL" → entity integrity. "The condition that finds rows with no value" → IS NULL.
---
Index Types and the Denormalization Trade-off
Clustered vs Nonclustered Indexes
An index helps you find rows quickly, like the index at the back of a book. SQL Server–based databases have two kinds.
| Aspect | Clustered index | Nonclustered index | |------|------|------| | Structure | Stores the table rows themselves sorted by the key | Stores sorted keys plus pointers to the actual rows separately | | Number per table | 1 (rows can be stored in only one order) | Many | | Created automatically when | You create a primary key (if no clustered index exists yet) | You create a UNIQUE constraint | | Analogy | A dictionary, where the content itself is in alphabetical order | The index at the back of a book |
Indexes speed up queries, but they are not free. They use storage, and every insert, update, or delete must also maintain the indexes, which can slow down writes. On a table with only a few rows, an index offers little benefit.
Normalization vs Denormalization
Normalization reduces duplication, which favors writes and integrity. Denormalization deliberately allows some duplication to cut down