Query Techniques for SQL
As mentioned in Chapter 1, SQL is the development language used to build, access, and manipulate relational databases. The American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) recognizes SQL as a standard language. While ANSI SQL is the standard that all major relational database vendors adhere to, most of them extend the language with functionality custom to their relational database products. For example, T-SQL is the Microsoft extension of ANSI SQL that is native to SQL Server, Azure SQL, Azure SQL Database, Azure SQL MI, and Azure Synapse Analytics.
DDL vs. DML Commands
Standard ANSI SQL commands can be broken down into two primary categories: Data Definition Language (DDL) and Data Manipulation Language (DML). DDL commands are used to define relational database objects such as databases, tables, views, stored procedures, and triggers. DML commands are used to manipulate data stored in a relational database. The following sections describe common commands and statement structure used by these categories.
Chapter 1 describes two additional SQL categories with DDL and DML commands that are specific to T-SQL. These include Data Control Language (DCL) commands that can be used to manage permissions and Transaction Control Language (TCL) commands that are used to control transaction execution.
Data Definition Language (DDL)
DDL statements are used to define database objects. They can be used to create new objects, modify existing ones, or remove objects that are no longer required. DDL statements start with a command that indicates which of these actions the statement is performing. Table 2.9 includes a list of these commands and the common statement structures associated with each of them.
TABLE 2.9 DDL commands
| Command | Description | Statement Structure |
| CREATE | Create a new object in a database. | CREATE TABLE <table name> ( <list of columns> ) |
| ALTER | Modify the structure of an existing database object. | ALTER TABLE <table name> ADD COLUMN <column name> |
| DROP | Remove an object from a database. | DROP TABLE <table name> |
| RENAME | Rename an existing object. | EXEC sp_rename <old name>, <new name> |
| TRUNCATE | Remove all rows from a table. | TRUNCATE TABLE <table name> |