Chapter 2:
SQL Commands
What are SQL Commands?
SQL (Structured Query Language) commands are predefined instructions used to communicate
with a database. They help users create databases, insert data, retrieve records, update
information, delete records, manage users, and control transactions.
SQL commands are categorized into five main types:
1. DDL (Data Definition Language)
2. DML (Data Manipulation Language)
3. DQL (Data Query Language)
4. DCL (Data Control Language)
5. TCL (Transaction Control Language)
1. DDL (Data Definition Language)
Definition
DDL commands are used to define and modify the structure of database objects such as
databases, tables, views, indexes, and schemas.
These commands affect the database structure rather than the data.
Characteristics
• Defines database objects
• Changes table structure
• Auto-commit commands
• Changes are permanent
• Cannot be rolled back in most database systems
Common DDL Commands
• CREATE
• ALTER
• DROP
• TRUNCATE
• RENAME
CREATE
Definition
Used to create new database objects.
Used For
• Database
• Table
• View
• Index
• Schema
ALTER
Definition
Used to modify the structure of an existing database object.
Used For
• Add columns
• Remove columns
• Modify data types
• Rename columns
• Add constraints
DROP
Definition
Deletes an entire database object permanently.
Characteristics
• Removes structure and data
• Cannot be recovered without backup
TRUNCATE
Definition
Removes all rows from a table while keeping the table structure intact.
Characteristics
• Faster than DELETE
• Resets identity/auto-increment in many databases
• Cannot delete selected rows
RENAME
Definition
Changes the name of an existing database object.
2. DML (Data Manipulation Language)
Definition
DML commands are used to manipulate the data stored inside tables.
These commands work with table records instead of the table structure.
Characteristics
• Insert data
• Update data
• Delete data
• Rollback possible before commit
• Does not modify table structure
Common DML Commands
• INSERT
• UPDATE
• DELETE
INSERT
Definition
Adds new records into a table.
UPDATE
Definition
Modifies existing records in a table.
DELETE
Definition
Removes records from a table.
Characteristics
• Deletes selected rows
• Table structure remains unchanged
• Can be rolled back before commit
3. DQL (Data Query Language)
Definition
DQL commands are used to retrieve data from one or more tables.
Characteristics
• Fetches information
• Supports filtering
• Supports sorting
• Supports grouping
• Supports joins
Common DQL Command
• SELECT
SELECT
Definition
Retrieves data from database tables.
Capabilities
• Display all records
• Display selected columns
• Filter records
• Sort records
• Group records
• Join multiple tables
4. DCL (Data Control Language)
Definition
DCL commands are used to control user permissions and security in a database.
Characteristics
• User management
• Database security
• Access control
• Privilege management
Common DCL Commands
• GRANT
• REVOKE
GRANT
Definition
Provides permissions to users on database objects.
Permissions May Include
• SELECT
• INSERT
• UPDATE
• DELETE
• CREATE
• ALTER
REVOKE
Definition
Removes previously granted permissions from users.
5. TCL (Transaction Control Language)
Definition
TCL commands are used to manage database transactions and maintain data consistency.
A transaction is a sequence of one or more SQL operations treated as a single unit of work.
Characteristics
• Maintains data integrity
• Controls transactions
• Supports rollback
• Ensures consistency
Common TCL Commands
• COMMIT
• ROLLBACK
• SAVEPOINT
COMMIT
Definition
Permanently saves all changes made during the current transaction.
Characteristics
• Makes changes permanent
• Cannot be rolled back after commit
ROLLBACK
Definition
Undoes all changes made during the current transaction before commit.
Characteristics
• Restores previous state
• Useful when errors occur
SAVEPOINT
Definition
Creates a checkpoint within a transaction.
Characteristics
• Allows partial rollback
• Multiple savepoints can exist in one transaction
SQL Command Classification Summary
Category Full Form Purpose Common Commands
Defines database CREATE, ALTER, DROP, TRUNCATE,
DDL Data Definition Language
structure RENAME
Data Manipulation
DML Manipulates table data INSERT, UPDATE, DELETE
Language
DQL Data Query Language Retrieves data SELECT
Controls user
DCL Data Control Language GRANT, REVOKE
permissions
Transaction Control
TCL Manages transactions COMMIT, ROLLBACK, SAVEPOINT
Language
Important Interview Jargons
• Database Object – A logical structure stored in a database (table, view, index, schema,
procedure, etc.).
• Schema – The logical blueprint or organization of database objects.
• Table Structure (Metadata) – The definition of columns, data types, and constraints of a
table.
• Record (Row/Tuple) – A single entry of data in a table.
• Field (Column/Attribute) – A single property or characteristic within a table.
• Transaction – A sequence of SQL operations executed as a single logical unit.
• Auto Commit – A mode where each SQL statement is committed automatically after
execution.
• Commit – Permanently saves changes made during a transaction.
• Rollback – Reverses uncommitted changes made during a transaction.
• Savepoint – A checkpoint within a transaction that allows partial rollback.
• Data Integrity – Ensuring data remains accurate, complete, and consistent throughout
its lifecycle.
• Privilege – A permission granted to a user to perform specific operations on database
objects.
• Constraint – A rule applied to table columns to enforce data validity and consistency.
• Metadata – Data that describes other data, such as table definitions and column
properties.
• Persistence – The property of data remaining stored permanently after a transaction is
committed.
• CRUD Operations – The four fundamental data operations: Create, Read, Update, and
Delete.