Data Definition Language (DDL) is used to define the database structure or schema.
These SQL statements define the structure of a database, including rows, columns, tables, indexes, and database specifics such as file locations. DDL SQL statements are more part of the DBMS and have large differences between the SQL variations. DML SQL commands include the following:
CREATE – to create objects in the database
ALTER – alters the structure of the database
DROP – delete objects from the database
TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
COMMENT – add comments to the data dictionary
RENAME – rename an object
Data Manipulation Language (DML) is used for managing data within schema objects.
These SQL statements are used to retrieve and manipulate data. This category encompasses the most fundamental commands including DELETE, INSERT, SELECT, and UPDATE. DML SQL statements have only minor differences between SQL variations. DML SQL commands include the following:
SELECT – retrieve data from the a database
INSERT – insert data into a table
UPDATE – updates existing data within a table
DELETE – deletes all records from a table, the space for the records remain
MERGE – UPSERT operation (insert or update)
CALL – call a PL/SQL or Java subprogram
EXPLAIN PLAN – explain access path to data
LOCK TABLE – control concurrency
Data Control Language (DCL) it is used to control access to database by securing it.
These SQL statements control the security and permissions of the objects or parts of the database(s). DCL SQL statements are also more part of the DBMS and have large differences between the SQL variations. DML SQL commands include the following:
GRANT – gives user's access privileges to database
REVOKE – withdraw access privileges given with the GRANT command
DENY to disallow specified users from performing specified tasks.