SQL Commands: A Comprehensive Guide to DDL, DML, DCL, and TCL

Posted on May 7, 2024 in Computers

Basic SQL Statements

DDL Commands

DDL (Data Definition Language) commands manage the structure of database objects. They are auto-committed, meaning changes are saved permanently. Common DDL commands include:

  • CREATE: Defines tables and their columns.
  • ALTER: Modifies existing tables.
  • DROP: Deletes tables and their data.
  • TRUNCATE: Removes all data from a table.
  • RENAME: Changes the name of a table.

CREATE Command

The CREATE command defines a table’s structure, specifying column names and data types.

Syntax:

CREATE TABLE table_name (column_name1 datatype1, column_name2 datatype2, ..., column_namen datatypen);

Example:

CREATE TABLE student (reg_no int, name char(20), address varchar2(10), dob date);

DROP Command

The DROP command permanently deletes a table and its data.

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE student;

TRUNCATE Command

The TRUNCATE command removes all data from a table but retains the table structure.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE Student;

RENAME Command

The RENAME command changes the name of a table.

Syntax:

RENAME table_name TO new_table_name;

Example:

RENAME Student TO Stu;

ALTER Command

The ALTER command modifies the structure of an existing table. It can add, drop, or modify columns.

Adding a New Column:

ALTER TABLE table_name ADD (new_column_name datatype);

Example:

ALTER TABLE Student ADD (Age number(2), Marks number(3));

Dropping a Column:

ALTER TABLE table_name DROP COLUMN column_name;

Example:

ALTER TABLE Student DROP COLUMN Age;

Modifying a Column:

ALTER TABLE table_name MODIFY (column_name new_datatype);

Example:

ALTER TABLE Student MODIFY (Name Varchar2(40));

DML Commands

DML (Data Manipulation Language) commands manage data within tables. Common DML commands include:

  • INSERT: Adds new rows to a table.
  • UPDATE: Modifies existing rows in a table.
  • DELETE: Removes rows from a table.
  • SELECT: Retrieves data from a table.

INSERT Command

The INSERT command adds a new row to a table.

Syntax:

INSERT INTO table_name VALUES (value1, value2, ...);

Example:

INSERT INTO Student VALUES (101, 'Adam', 15);

UPDATE Command

The UPDATE command modifies existing rows in a table based on a condition.

Syntax:

UPDATE table_name SET column_name = value WHERE condition;

Example:

UPDATE Student SET age = 18 WHERE s_id = 101;

DELETE Command

The DELETE command removes rows from a table based on a condition.

Syntax:

DELETE FROM table_name WHERE condition;

Example:

DELETE FROM Student WHERE s_id = 103;

SELECT Command

The SELECT command retrieves data from a table.

Syntax:

SELECT column1, column2, ... FROM table_name;

Example:

SELECT S_name FROM Student;

To select all columns, use the asterisk (*):

SELECT * FROM Student;

DCL Commands

DCL (Data Control Language) commands manage user access and permissions in a database. Common DCL commands include:

  • GRANT: Gives users access privileges to database objects.
  • REVOKE: Removes access privileges from users.

GRANT Command

The GRANT command assigns privileges to users.

Examples:

GRANT CREATE ANY TABLE TO username;

GRANT DROP ANY TABLE TO username;

REVOKE Command

The REVOKE command removes privileges from users.

Example:

REVOKE CREATE TABLE FROM username;

TCL Commands

TCL (Transaction Control Language) commands manage transactions, which are groups of SQL statements treated as a single unit. Common TCL commands include:

  • COMMIT: Saves changes made within a transaction permanently.
  • ROLLBACK: Reverts changes made within a transaction.
  • SAVEPOINT: Creates a point within a transaction to which you can roll back.

COMMIT Command

The COMMIT command saves all changes made since the last COMMIT or ROLLBACK.

Syntax:

COMMIT;

ROLLBACK Command

The ROLLBACK command undoes changes made since the last COMMIT or to a specified SAVEPOINT.

Syntax:

ROLLBACK TO savepoint_name;

SAVEPOINT Command

The SAVEPOINT command creates a named point within a transaction to which you can roll back.

Syntax:

SAVEPOINT savepoint_name;

Examples of TCL Commands

Consider a table named ‘class’ with columns ‘ID’ and ‘NAME’.

Initial Data:

ID | NAME
---|-----
1 | Abhi
2 | Adam
4 | Alex

Insert a new row and commit:

INSERT INTO class VALUES (5, 'Rahul');
COMMIT;

Update the new row and create a SAVEPOINT:

UPDATE class SET name = 'Abhijit' WHERE id = 5;
SAVEPOINT A;

Insert more rows and create SAVEPOINTs:

INSERT INTO class VALUES (6, 'Chris');
SAVEPOINT B;
INSERT INTO class VALUES (7, 'Bravo');
SAVEPOINT C;

View the current state of the table:

SELECT * FROM class;

Output:

ID | NAME
---|-------
1 | Abhi
2 | Adam
4 | Alex
5 | Abhijit
6 | Chris
7 | Bravo

Rollback to SAVEPOINT B:

ROLLBACK TO B;

View the table after rollback:

SELECT * FROM class;

Output:

ID | NAME
---|-------
1 | Abhi
2 | Adam
4 | Alex
5 | Abhijit
6 | Chris

Rollback to SAVEPOINT A:

ROLLBACK TO A;

View the table after rollback:

SELECT * FROM class;

Output:

ID | NAME
---|-------
1 | Abhi
2 | Adam
4 | Alex
5 | Abhijit

This demonstrates how TCL commands allow you to manage transactions and control the state of your database.