SQL Engine
The DBMS subsystems manage Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL), and Transaction Control Language (TCL) commands. Occasionally, SELECT statements (queries) are removed from the DML group and placed in a Data Query Language (DQL). The argument goes that a query that doesn’t lock data for transactions isn’t manipulating it, it is only viewing it. The argument against that logic is that queries manipulate data to look at results. While an interesting topic, most certification material treats a SELECT statement as a DML statement.
Data Manipulation Language (DML) →
This gives you a brief description of the SELECT, INSERT, DELETE, UPDATE, and MERGE statements. These statements let you query data and transact against data in the database.
- SELECT – Queries data.
- INSERT – Inserts data into tables or objects.
- UPDATE – Updates data in tables or objects.
- DELETE – Deletes data from tables or objects.
- MERGE – Inserts or updates data based on conditions within the statement into tables or objects. It is part of the ANSI SQL:2003 standard. MySQL implements it as the
ON DUPLICATE KEYclause, and you can find a comparison of the OracleMERGEto it in this blog post.
Data Definition Language (DDL) →
This gives you a brief description of the CREATE, ALTER, DROP, RENAME, COMMENT, and TRUNCATE statements. These statements let you create, modify, and remove structures from the database.
- CREATE – Creates an object, which can be a table, view, or other structure supported by the DBMS implementation.
- ALTER – Alters the structure of an object, which can be a table, view, or other structure supported by the DBMS implementation.
- DROP – Drops a structure from the database, which may be constrained when there are other objects with a dependency on the target object.
- RENAME – Renames a structure, which can be a table, view, or subcomponent like a column name or data type of a table.
- TRUNCATE – Truncate deletes data like the
DELETEstatement with a twist. It preserves the structure of the table but deletes the contents without logging the deletes for recovery. Implementations vary between products, and Oracle’s flashback technology does provide for recovery of truncated objects.
- COMMENT – Lets you add a comment about a table, or a comment about a column in a specific table.
