SQL Data Manipulation Language (DML) Statements – DP-900 Guide
Why SQL DML Statements Matter
SQL Data Manipulation Language (DML) statements are the backbone of how users and applications interact with data stored in relational databases. Whether you are inserting new records, reading existing data, updating values, or removing rows, DML is the tool you use. For the DP-900 (Microsoft Azure Data Fundamentals) exam, understanding DML is essential because it tests your ability to distinguish DML from other SQL sub-languages (DDL, DCL) and to know what each statement does at a conceptual level.
What Are SQL DML Statements?
DML is a subset of SQL used to manipulate data within existing database objects (tables and views). It does not change the structure of the database itself — that is the job of Data Definition Language (DDL). The four core DML statements are:
1. SELECT
Retrieves data from one or more tables. It is the most commonly used SQL statement.
Example:
SELECT FirstName, LastName, Email FROM Customers WHERE Country = 'USA';
Key clauses to know:
- FROM – specifies the table(s)
- WHERE – filters rows based on a condition
- ORDER BY – sorts the result set (ASC for ascending, DESC for descending)
- GROUP BY – groups rows for aggregate functions (COUNT, SUM, AVG, MIN, MAX)
- HAVING – filters groups (used after GROUP BY)
- JOIN – combines rows from two or more tables based on a related column
- DISTINCT – removes duplicate rows from the result set
- TOP / LIMIT – restricts the number of rows returned
2. INSERT
Adds new rows of data into a table.
Example:
INSERT INTO Customers (FirstName, LastName, Email) VALUES ('Jane', 'Doe', 'jane@example.com');
Key points:
- You specify the target table and the columns you want to populate.
- The VALUES clause provides the actual data.
- If a column has a default value or is an identity column, you can omit it from the column list.
3. UPDATE
Modifies existing data in a table.
Example:
UPDATE Customers SET Email = 'newemail@example.com' WHERE CustomerID = 101;
Key points:
- The SET clause specifies which columns to change and their new values.
- The WHERE clause is critical — without it, all rows in the table will be updated.
4. DELETE
Removes rows from a table.
Example:
DELETE FROM Customers WHERE CustomerID = 101;
Key points:
- The WHERE clause determines which rows are removed.
- Without a WHERE clause, all rows will be deleted (but the table structure remains).
- DELETE is different from TRUNCATE (which is a DDL statement that removes all rows and cannot be easily rolled back in some systems) and DROP (which removes the entire table object).
How DML Works in Azure Context
In Azure, DML statements work the same way whether you use:
- Azure SQL Database (PaaS relational database)
- Azure SQL Managed Instance
- SQL Server on Azure VMs (IaaS)
- Azure Synapse Analytics (dedicated SQL pools)
You can execute DML statements through tools such as Azure Data Studio, SQL Server Management Studio (SSMS), the Azure Portal query editor, or programmatically via application code and APIs.
DML vs. DDL vs. DCL – Know the Difference
This is a very common exam topic:
- DML (Data Manipulation Language): SELECT, INSERT, UPDATE, DELETE — works with data inside tables.
- DDL (Data Definition Language): CREATE, ALTER, DROP, TRUNCATE — works with database objects (tables, views, indexes).
- DCL (Data Control Language): GRANT, REVOKE, DENY — manages permissions and access.
Aggregate Functions Used with SELECT
The exam may test your understanding of aggregate functions:
- COUNT() – returns the number of rows
- SUM() – returns the total of a numeric column
- AVG() – returns the average value
- MIN() – returns the smallest value
- MAX() – returns the largest value
These are used with GROUP BY to summarize data, and HAVING to filter grouped results.
JOINs – Combining Data from Multiple Tables
JOINs are part of the SELECT statement and are frequently tested:
- INNER JOIN – returns only matching rows from both tables
- LEFT (OUTER) JOIN – returns all rows from the left table, plus matches from the right
- RIGHT (OUTER) JOIN – returns all rows from the right table, plus matches from the left
- FULL (OUTER) JOIN – returns all rows from both tables, matching where possible
- CROSS JOIN – returns the Cartesian product of both tables
Exam Tips: Answering Questions on SQL Data Manipulation Language Statements
Tip 1: Categorize the Statement Immediately
When an exam question shows a SQL statement or asks which category it belongs to, quickly identify: Does it manipulate data (DML) or structure (DDL) or permissions (DCL)? SELECT, INSERT, UPDATE, DELETE = DML.
Tip 2: Remember SELECT Is DML
Some candidates mistakenly think SELECT is not DML because it doesn't change data. For the DP-900 exam, SELECT is classified as a DML statement. Some references call it DQL (Data Query Language), but Microsoft treats it as DML for this exam.
Tip 3: Watch for the WHERE Clause Trap
Questions may ask what happens if you run UPDATE or DELETE without a WHERE clause. The answer is that all rows in the table are affected.
Tip 4: Know DELETE vs. TRUNCATE vs. DROP
- DELETE (DML) removes specific rows and can use a WHERE clause.
- TRUNCATE (DDL) removes all rows but keeps the table structure.
- DROP (DDL) removes the entire table and its structure.
Tip 5: Understand JOIN Types Conceptually
You don't need to write complex JOIN queries, but you should know the difference between INNER JOIN (only matching rows) and LEFT JOIN (all rows from one table plus matches from the other).
Tip 6: Know Basic Aggregate Functions
Be able to identify which function to use: COUNT for counting rows, SUM for totals, AVG for averages. Know that GROUP BY is required when mixing aggregate and non-aggregate columns in a SELECT.
Tip 7: ORDER BY and Sorting
Remember that ORDER BY defaults to ascending (ASC). Use DESC for descending. This is a quick-answer topic on the exam.
Tip 8: INSERT Syntax Awareness
Know that INSERT INTO requires a table name, optionally a column list, and a VALUES clause. If a question asks how to add data to a table, the answer is INSERT — not CREATE or ALTER.
Tip 9: Focus on Purpose, Not Syntax Perfection
The DP-900 exam is a fundamentals exam. You are more likely to be asked what a statement does or which statement to use for a scenario rather than to write syntactically perfect SQL. Focus on understanding the purpose of each DML statement.
Tip 10: Practice with Scenario-Based Questions
If a question says 'A developer needs to change the email address of an existing customer,' the answer is UPDATE. If it says 'retrieve a list of all orders,' the answer is SELECT. Map real-world actions to the correct DML statement.
Summary
SQL DML statements — SELECT, INSERT, UPDATE, and DELETE — are fundamental to working with relational data in Azure. For the DP-900 exam, focus on understanding what each statement does, how it differs from DDL and DCL, and how to match real-world data tasks to the correct DML command. Mastering these concepts will help you confidently answer a significant portion of the exam questions related to relational data on Azure.
