What Are Ddl and Dml

What are DDL and DML?

SQL command can be divided into three subgroups, DDL, DML and DCL

The following is adapted from here MySQL What is DDL, DML and DCL?:

DDL

DDL is short name of Data Definition Language, which deals with
database schemas and descriptions, of how the data should reside in
the database.

  • CREATE – to create database and its objects like (table, index, views, store procedure, function and triggers).
  • ALTER – alters the structure of the existing database.
  • DROP – delete objects from the database.
  • TRUNCATE – remove all records from a table; also, all spaces allocated for the records are removed.
  • COMMENT – add comments to the data dictionary.
  • RENAME – rename an object.

DML

DML is short name of Data Manipulation Language which deals with data
manipulation, and includes most common SQL statements such SELECT,
INSERT, UPDATE, DELETE etc, and it is used to store, modify, retrieve,
delete and update data in database.

  • SELECT – retrieve data from one or more tables.
  • INSERT – insert data into a table.
  • UPDATE – updates existing data within a table.
  • DELETE – delete all records from a table.
  • MERGE – UPSERT operation (insert or update)
  • CALL – call a PL/SQL or Java subprogram.
  • EXPLAIN PLAN – interpretation of the data access path.
  • LOCK TABLE – concurrency control.

DCL

DCL is short name of Data Control Language which includes commands
such as GRANT, and mostly concerned with rights, permissions and other
controls of the database system.

  • GRANT – allow users access privileges to database.
  • REVOKE – withdraw users access privileges given by using the GRANT command.

TCL

TCL is short name of Transaction Control Language which deals with
transaction within a database.

  • COMMIT – commits a transaction.
  • ROLLBACK – rollback a transaction in case of any error occurs.
  • SAVEPOINT – a point inside a transaction that allows rollback state to what it was at the time of the savepoint.
  • SET TRANSACTION – specify characteristics for the transaction.

How can Delete be both a DDL and a DML statement

I agree with you, DELETE is DML. Moreover, I dare say, TRUNCATE should also be considered DML, since logically is equivalent to a DELETE statement. The fact that TRUNCATE is a DROP and CREATE is not enough in my opinion to justify assigning it to DDL, since the two together, carried out as one atomic operation, do not affect the schema of the database.

Why alter command is referred as DDL and not DML?

ALTER command is used to alter the structure of the database. And this is what DDL does i.e., DDL statements are used to define the database structure or schema.

Whereas DML statement is used to manage data within schema objects.

Is Select a DML or DDL?

  1. Inserting data that is selected from TABLE1 to TABLE 2
  2. It is data manipulation
  3. From the look of it they are the same

EDIT:
To answer the question title as well, SELECT is not a DDL (Data definition language). DDL statements modify database layout, like CREATE TABLE. DML statements are queries data, like SELECT; or modifies data, like INSERT.

Another edit:
It seems SELECT INTO is not fully supported in mysql. Even though answer is still valid for general SQL. For me, SELECT INTO's table creation aspect is a side effect. thus it is still a DML.

Why are SQL statements divided into DDL, DML, DCL and TCL statements?

These are classifications, they exits for the same reason every other classification on anything else exists...

Regarding use cases, I hardly see a situation where you would use those terms but maybe if you are the Business Analyst for a SQL Server management tool you could say that you dont want users to have access to any sort of DDL statments, meaning the users cant create objects on the DB...

When would you call java's thread.run() instead of thread.start()?

You might want to call run() in a particular unit test that is concerned strictly with functionality and not with concurrency.

How to find whether the SQL query type is DML or DDL?

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
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) statements are used for managing data within schema objects. Some examples:
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



Related Topics



Leave a reply



Submit