DDL and DML Commands in SQL Transaction Control Language (TCL) Commands

DDL Commands

Syntax

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name (
    column1 datatype [optional_attributes],
    column2 datatype [optional_attributes],
    column3 datatype [optional_attributes],
   ....
);

ALTER TABLE table_name
ADD COLUMN new_column_name datatype;

DROP TABLE [IF EXISTS] table_name;

Example

CREATE TABLE students (
   id INT PRIMARY KEY,
   name TEXT,
   age INT
);

ALTER TABLE students
ADD COLUMN gender TEXT;

DROP TABLE students;

Output

The CREATE command will create a table with the specified columns and data types. The ALTER command will add a new column to an existing table. The DROP command will delete the specified table.

Explanation

• CREATE command is used to create a new table in the database.
• ALTER command is used to modify an existing table by adding new columns.
• DROP command is used to delete a table from the database.

Use

DDL commands are used to define, modify, and delete database objects like tables, views, and indexes.

Important Points

• CREATE command creates a new table in the database.
• ALTER command modifies an existing table by adding new columns.
• DROP command deletes a table from the database.

DML Commands

Syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

SELECT column1, column2, ...
FROM table_name
WHERE condition;

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

DELETE FROM table_name
WHERE condition;

Example

INSERT INTO students (id, name, age)
VALUES (1, 'John', 20);

INSERT INTO students (id, name, age)
VALUES (2, 'Mary', 22);

SELECT * FROM students
WHERE age > 20;

UPDATE students
SET age = 23
WHERE name = 'Mary';

DELETE FROM students
WHERE id = 1;

Output

The INSERT command will add a new row of data to a table. The SELECT command a result set based on the specified condition. The UPDATE command will modify existing data in the table. The DELETE command will remove rows from the table based on the specified condition.

Explanation

• INSERT command is used to insert a new row of data into a table.
• SELECT command is used to select data from a table based on a condition.
• UPDATE command is used to modify existing data in a table.
• DELETE command is used to remove rows from a table based on a condition.

Use

DML commands are used to manipulate data in the database.

Important Points

• INSERT command is used to insert new data into a table.
• SELECT command is used to select data from a table based on a condition.
• UPDATE command is used to modify data in a table.
• DELETE command is used to remove data from a table based on a condition.

Summary

DDL and DML commands are important components of SQL Transaction Control Language (TCL) commands. DDL commands are used to define, modify, and delete database objects like tables, views, and indexes. DML commands are used to manipulate data in the database. Understanding how to use these commands is crucial for managing and maintaining the integrity of a database.