Essential SQL Data Definition and Manipulation Commands

Basic SQL Statements: DDL and DML Commands

Data Definition Language (DDL) Commands

DDL commands are used to define or modify the structure of database objects. All DDL commands are auto-committed, meaning they save all changes permanently in the database immediately after execution.

The primary DDL commands are:

• CREATE
• ALTER
• TRUNCATE
• DROP
• RENAME

CREATE Command

The CREATE command is used to define a new table structure. It uniquely defines each column, specifying its name and data type. Each column definition is separated by a comma, and the SQL statement is terminated with a semicolon.

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 destroys the entire table structure and all data recorded within it.

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE Student;

TRUNCATE Command

The TRUNCATE command is used to quickly delete all rows from a table. Unlike DELETE, TRUNCATE is a DDL command and cannot be rolled back.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE Student;

RENAME Command

The RENAME command changes the name of an existing table.

Syntax:

RENAME OldTableName TO NewTableName;

Example:

RENAME Student TO Stu;

ALTER Command

The ALTER TABLE command is used to modify the structure of an existing table, such as adding, dropping, or modifying columns.

Adding New Columns

This command adds one or more new columns to the table structure.

Syntax:

ALTER TABLE table_name ADD (
    NewColumnName DataType,
    ...
);

Example:

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

Dropping a Column from the Table

This command removes a specific column from the table structure.

Syntax:

ALTER TABLE table_name DROP COLUMN column_name;

Example:

ALTER TABLE Student DROP COLUMN Age;

Modifying Existing Columns

This command modifies the data type or size of an existing column.

Syntax:

ALTER TABLE table_name MODIFY (
    column_name NewDataType(NewSize)
);

Example:

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

Data Manipulation Language (DML) Commands

DML, or Data Manipulation Language, deals with manipulating the data stored within database objects. It includes common SQL statements used to store, modify, retrieve, and delete data.

The primary DML commands include SELECT, INSERT, UPDATE, and DELETE.

INSERT Command

The INSERT command is used to add new rows (records) into a table.

Syntax:

INSERT INTO table_name VALUES (data1, data2, ...);

Example:

Consider a table Student with fields: S_id, S_Name, Age.

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

The command inserts the following record:

UPDATE Command

The UPDATE command is used to modify existing data in a table. It typically uses a WHERE clause to specify which rows should be updated.

Syntax (Single Column):

UPDATE table_name SET column_name = value WHERE condition;

Example:

UPDATE Student SET Age = 18 WHERE S_id = 101;

The resulting record:

Syntax (Multiple Columns):

UPDATE Student SET column_name1 = value1, column_name2 = value2 WHERE condition;

DELETE Command

The DELETE command is used to remove rows from a table. It can be used with a WHERE clause to delete specific rows, or without a WHERE clause to delete all rows (but unlike TRUNCATE, it is transactional and slower).

Syntax (Deleting all rows):

DELETE FROM table_name;

Syntax (Deleting specific rows):

DELETE FROM table_name WHERE condition;