Topic 2 of 5
SQL statements
SQL is the language you use to talk to a relational database. The fundamentals exam doesn't ask you to write complex queries, but it does ask you to recognize what each statement does and which group it belongs to: DDL, DCL, or DML. It's one of the most repeated questions.
Contents
What is SQL?
Structured Query Language is the standard language for relational database management systems. Its statements update data, retrieve it, and handle almost any other database task.
It's used by Microsoft SQL Server, Azure SQL Database, Azure SQL Managed Instance, SQL Server on Azure virtual machines, MySQL, PostgreSQL, and Oracle, among others.
Standard and dialects
SQL was standardized by ANSI in 1986 and by ISO in 1987, and the standard has been extended several times. On top of that, each vendor adds its own extensions (security, stored procedure and trigger programming), which has produced dialects:
Transact-SQL (T-SQL)
SQL Server, Azure SQL Database, Azure SQL Managed Instance, and SQL Server on Azure VMs.
PL/pgSQL
PostgreSQL's procedural dialect.
PL/SQL
Oracle's dialect (Procedural Language/SQL).
Copilot in Azure lets you ask questions about an Azure SQL Database and generate T-SQL queries in natural language.
The three statement groups
| Group | Purpose | Statements |
|---|---|---|
| DDL · Data Definition Language | Create, modify, and remove tables and other objects | CREATE, ALTER, DROP, RENAME |
| DCL · Data Control Language | Manage access permissions on objects | GRANT, DENY, REVOKE |
| DML · Data Manipulation Language | Query and insert, modify, or delete rows | SELECT, INSERT, UPDATE, DELETE |
To remember it
DDL = the structure (Definition). DCL = who can (Control). DML = the row data (Manipulation). Database administrators mainly use DCL.
DDL: defining objects
It creates, modifies, and removes database objects: tables, stored procedures, views…
CREATE TABLE Product
(
ID INT PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
Price DECIMAL NULL
);- Each column goes inside the parentheses: name, data type, whether it's mandatory (NOT NULL), and whether it identifies the row (PRIMARY KEY).
- Every table should have a primary key, though SQL doesn't enforce it.
- Common types: INT (integer), DECIMAL (decimal), and VARCHAR (variable-length text).
Careful with DROP
DROP permanently removes the object. Dropping a table loses all its rows; without a backup, they can't be recovered.
DCL: controlling access
Administrators use it to grant, deny, or revoke permissions for users or groups.
GRANT
Grants permission for specific actions
DENY
Denies permission for specific actions
REVOKE
Removes a previously granted permission
GRANT SELECT, INSERT, UPDATE
ON Product
TO user1;This example lets user1 read, insert, and modify data in the Product table.
DML: manipulating rows
SELECT
Read rows
INSERT
Insert new rows
UPDATE
Modify existing rows
DELETE
Delete rows
By default, SELECT, UPDATE, and DELETE apply to every row in the table. You usually add a WHERE clause with a criterion so they only affect the matching rows.
Queries with SELECT
-- All columns for customers in Seattle
SELECT *
FROM Customer
WHERE City = 'Seattle';
-- Only some columns, sorted by last name
SELECT FirstName, LastName, Address, City
FROM Customer
WHERE City = 'Seattle'
ORDER BY LastName;
-- Join orders and customers on the key
SELECT o.OrderNo, o.OrderDate, c.Address, c.City
FROM [Order] AS o
JOIN Customer AS c
ON o.Customer = c.ID;- * selects every column; you can also list only the ones you need.
- Without ORDER BY, rows don't come back in any particular order.
- JOIN says how the rows of two tables connect; typically a foreign key is matched with its primary key. Aliases (o, c) shorten the names.
- ORDER is a reserved keyword in T-SQL, which is why the table name is in square brackets.
INSERT, UPDATE, and DELETE
UPDATE Customer
SET Address = '123 High St.'
WHERE ID = 1;
DELETE FROM Product
WHERE ID = 162;
INSERT INTO Product(ID, Name, Price)
VALUES (99, 'Drill', 4.99);No WHERE, every row
SQL doesn't ask "are you sure?". An UPDATE without WHERE modifies every row, and a DELETE without WHERE removes them all.
INSERT adds one row by default; some dialects, like T-SQL, allow several value lists to add multiple rows in one statement.
Exam scenarios
| Need | Statement | Why |
|---|---|---|
| Create a new table | CREATE (DDL) | It defines an object. |
| Add a column to an existing table | ALTER (DDL) | It modifies the structure. |
| Remove a table entirely | DROP (DDL) | It removes the object and its rows. |
| Let a user read the Product table | GRANT (DCL) | It grants permissions. |
| Remove a previously granted permission | REVOKE (DCL) | It takes away an existing permission. |
| Read customers in Seattle | SELECT with WHERE (DML) | It queries rows that match a criterion. |
| Change the address of customer ID 1 | UPDATE with WHERE (DML) | It modifies existing rows. |
| Combine orders with their customer details | SELECT with JOIN | It joins tables on foreign and primary keys. |
Common exam traps
❓ Is SELECT a DDL statement?
No. SELECT is DML: it manipulates (queries) rows.
❓ Does GRANT belong to DML?
No. GRANT, DENY, and REVOKE are DCL, for controlling permissions.
❓ Which group includes CREATE, ALTER, and DROP?
DDL, which defines object structure.
❓ What happens if I run DELETE FROM Product without WHERE?
Every row in the table is deleted. SQL doesn't ask for confirmation.
❓ Which dialect does Azure SQL Database use?
Transact-SQL (T-SQL), like SQL Server and SQL Managed Instance.
❓ Do SELECT results come back sorted by default?
No. To sort them you add ORDER BY.
❓ Do DROP TABLE and DELETE do the same thing?
No. DROP (DDL) removes the whole table; DELETE (DML) removes rows and the table still exists.
Did you get this topic?
Test what you just learned
An administrator wants a user to stop being able to modify the Product table by removing a permission granted earlier. Which statement do they use, and which group does it belong to?
Sign in to save your progress.