SQL DML and DDL: SELECT, INSERT, CREATE, ALTER

SQL (Structured Query Language) is divided into sublanguages based on the type of operation. Data Definition Language (DDL) deals with the structure of database objects — creating, altering, and dropping tables, views, indexes, and schemas. Data Manipulation Language (DML) deals with the data within those objects — inserting, selecting, updating, and deleting rows. The exam focuses on the four core commands: CREATE (DDL), ALTER (DDL), SELECT (DML), and INSERT (DML). Understanding the difference is crucial because DDL commands are auto-committed (they cannot be rolled back in most contexts), while DML commands are transactional (they can be committed or rolled back).

The CREATE statement is used to create new database objects such as tables, views, indexes, stored procedures, and databases. The most common use is creating a table:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    HireDate DATE DEFAULT GETDATE()
);

Key components: - Column definitions: Each column has a name, data type, and optional constraints (PRIMARY KEY, NOT NULL, UNIQUE, DEFAULT, CHECK). - Data types: Common types include INT, BIGINT, DECIMAL(p,s), NVARCHAR(n), VARCHAR(n), DATE, DATETIME2, BIT. - Constraints: Enforce data integrity. PRIMARY KEY uniquely identifies each row. FOREIGN KEY links to another table. UNIQUE ensures no duplicate values. CHECK validates data against a condition. DEFAULT provides a value when none is specified. - Table options: You can specify filegroup, compression, and partitioning for performance.

In Azure SQL Database, CREATE TABLE works similarly but with some limitations (e.g., no filegroup specification). The exam may ask about the purpose of constraints or which data type to use for a given scenario.

ALTER is used to change the structure of an existing object without dropping and recreating it. Common operations include adding, modifying, or dropping columns; adding or dropping constraints; and changing table properties.

Examples:

-- Add a column
ALTER TABLE Employees ADD Email NVARCHAR(100) NULL;

-- Modify a column data type
ALTER TABLE Employees ALTER COLUMN Salary DECIMAL(10,2) NOT NULL;

-- Add a constraint
ALTER TABLE Employees ADD CONSTRAINT CK_Age CHECK (Age >= 18);

-- Drop a column
ALTER TABLE Employees DROP COLUMN TempColumn;

Important considerations:

SELECT is the most used DML command. It retrieves rows from one or more tables, optionally filtered, sorted, and aggregated.

Basic syntax:

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column ASC|DESC;

Key clauses: - SELECT list: Specifies columns to return. Use * for all columns (avoid in production). - FROM: Specifies the source table(s). Can include JOINs (INNER, LEFT, RIGHT, FULL). - WHERE: Filters rows based on predicates (e.g., =, <>, >, <, LIKE, IN, BETWEEN). - GROUP BY: Groups rows for aggregation (e.g., COUNT, SUM, AVG, MIN, MAX). - HAVING: Filters groups after aggregation (like WHERE for groups). - ORDER BY: Sorts the result set. - TOP/OFFSET-FETCH: Limits rows (e.g., SELECT TOP 10, or OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY).

Execution order (conceptual): FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> TOP/OFFSET-FETCH. Understanding this helps debug unexpected results.

Example:

SELECT DepartmentID, COUNT(*) AS EmployeeCount
FROM Employees
WHERE HireDate >= '2020-01-01'
GROUP BY DepartmentID
HAVING COUNT(*) > 5
ORDER BY EmployeeCount DESC;

INSERT adds one or more rows to a table.

Syntax variations:

-- Insert a single row with values for all columns
INSERT INTO Employees VALUES (1, 'John', 'Doe', '2023-01-15');

-- Insert with explicit column list (recommended)
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES (2, 'Jane', 'Smith', '2023-02-20');

-- Insert multiple rows
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
VALUES 
    (3, 'Bob', 'Brown', '2023-03-10'),
    (4, 'Alice', 'Jones', '2023-04-05');

-- Insert from another table
INSERT INTO Employees (EmployeeID, FirstName, LastName, HireDate)
SELECT EmployeeID, FirstName, LastName, HireDate
FROM TempEmployees
WHERE Active = 1;

Key points:

DDL statements (CREATE, ALTER, DROP) are auto-committed in SQL Server and Azure SQL Database, meaning they cannot be rolled back. DML statements (SELECT, INSERT, UPDATE, DELETE) are transactional — they can be wrapped in BEGIN TRANSACTION, COMMIT, and ROLLBACK. This distinction is often tested.

Example of a transaction:

BEGIN TRANSACTION;
INSERT INTO Employees VALUES (5, 'Test', 'User', GETDATE());
UPDATE Employees SET LastName = 'Updated' WHERE EmployeeID = 5;
COMMIT; -- or ROLLBACK;

If a transaction is rolled back, the INSERT and UPDATE are undone. DDL statements executed inside a transaction are still auto-committed, so they persist even if the transaction is rolled back.

In Azure SQL Database, the same syntax applies, but there are some differences:

The exam expects you to know that the core SQL commands are the same across SQL Server and Azure SQL Database, but some advanced features differ.

| Feature | DDL | DML | |---------|-----|-----| | Commands | CREATE, ALTER, DROP, TRUNCATE | SELECT, INSERT, UPDATE, DELETE | | Affects | Structure of objects | Data within objects | | Transactional | Auto-committed (cannot rollback) | Transactional (can rollback) | | Common exam topics | Syntax for CREATE TABLE, ALTER TABLE, constraints | SELECT clauses, INSERT variations, filtering |

The exam will test your ability to write correct SQL and understand the implications of each command.

A large e-commerce company needs to manage millions of products across multiple categories. They use DDL to create tables like Products, Categories, and Inventory. The Products table has columns: ProductID (INT IDENTITY), ProductName (NVARCHAR(200)), Price (DECIMAL(10,2)), CategoryID (INT FK), and CreatedDate (DATETIME2 DEFAULT GETDATE()). They use ALTER TABLE to add columns like DiscountPercent and IsActive as business requirements evolve. Data is inserted via ETL processes using BULK INSERT or INSERT INTO...SELECT from staging tables. SELECT queries are heavily optimized with indexes (CREATE INDEX) and use WHERE clauses to filter by category, price range, or search terms. Performance considerations: indexing strategy is critical; too many indexes slow down INSERTs. They use partitioning (CREATE PARTITION FUNCTION) to manage large tables. Common misconfigurations: forgetting to add NOT NULL constraints leads to NULL values in critical columns; adding a column with a default value to a large table causes long-running operations and potential blocking.

A hospital database stores patient information with strict compliance requirements (HIPAA). They use DDL to create tables with constraints: PatientID (INT PK), SSN (NVARCHAR(11) UNIQUE), FirstName, LastName, DateOfBirth (DATE), and DiagnosisCode (NVARCHAR(10) FK to DiagnosisCodes). ALTER TABLE is used to add columns for new data points (e.g., COVID-19 vaccination status). INSERT operations are performed by front-end applications, often within transactions to ensure data consistency. SELECT queries are used for reporting and analytics, often with JOINs across multiple tables. Security is paramount: permissions are granted using GRANT SELECT, INSERT, etc. Common issues: accidentally dropping a column (DDL) without understanding dependencies; inserting duplicate SSNs due to missing UNIQUE constraint; SELECT queries that return too many rows causing performance degradation.

A bank processes millions of transactions daily. They use DDL to create tables with precise data types: TransactionID (BIGINT IDENTITY), AccountID (INT FK), Amount (DECIMAL(18,2)), TransactionDate (DATETIME2), and Status (TINYINT). ALTER TABLE is used to add indexes for faster querying. INSERT operations are done within transactions that include multiple tables (e.g., debit from one account, credit to another). SELECT queries are used for balance inquiries and fraud detection, often with aggregation and filtering by date range. Performance considerations: use of appropriate data types (e.g., DECIMAL for money, not FLOAT); indexing on AccountID and TransactionDate. Common pitfalls: inserting values that violate CHECK constraints (e.g., negative amount); altering a column data type from INT to BIGINT without checking for overflow; SELECT queries that cause table scans due to missing indexes.