Common Database Operations
Database operations can be broadly categorized into two main groups: Data
Definition Operations and Data Manipulation Operations. Let’s break them
down:
1. Data Definition Operations
These operations define and modify the structure of the database. They include:
a. CREATE
The CREATE operation is used to define a new database object, such as a
table, view, index, or database itself.
SQL Example:
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
Department VARCHAR(50)
);
b. ALTER
The ALTER command modifies an existing database object, such as adding,
deleting, or modifying columns in a table.
SQL Example:
ALTER TABLE Employees ADD COLUMN Salary DECIMAL(10, 2);
c. DROP
The DROP operation removes a database object, such as a table, index, or
view, and permanently deletes its data.
SQL Example:
, DROP TABLE Employees;
d. TRUNCATE
The TRUNCATE operation removes all rows from a table but does not
remove the table structure itself. It is faster than DELETE as it does not log
individual row deletions.
SQL Example:
TRUNCATE TABLE Employees;
2. Data Manipulation Operations
These operations deal with the actual data inside the database and include:
a. SELECT
The SELECT statement is used to retrieve data from one or more tables.
SQL Example:
SELECT Name, Age FROM Employees WHERE Department = 'HR';
b. INSERT
The INSERT operation adds new records (rows) into a table.
SQL Example:
INSERT INTO Employees (ID, Name, Age, Department)
VALUES (1, 'Alice', 30, 'HR');
c. UPDATE
The UPDATE operation modifies existing records in a table.
SQL Example:
UPDATE Employees
SET Age = 31
WHERE ID = 1;
Database operations can be broadly categorized into two main groups: Data
Definition Operations and Data Manipulation Operations. Let’s break them
down:
1. Data Definition Operations
These operations define and modify the structure of the database. They include:
a. CREATE
The CREATE operation is used to define a new database object, such as a
table, view, index, or database itself.
SQL Example:
CREATE TABLE Employees (
ID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT,
Department VARCHAR(50)
);
b. ALTER
The ALTER command modifies an existing database object, such as adding,
deleting, or modifying columns in a table.
SQL Example:
ALTER TABLE Employees ADD COLUMN Salary DECIMAL(10, 2);
c. DROP
The DROP operation removes a database object, such as a table, index, or
view, and permanently deletes its data.
SQL Example:
, DROP TABLE Employees;
d. TRUNCATE
The TRUNCATE operation removes all rows from a table but does not
remove the table structure itself. It is faster than DELETE as it does not log
individual row deletions.
SQL Example:
TRUNCATE TABLE Employees;
2. Data Manipulation Operations
These operations deal with the actual data inside the database and include:
a. SELECT
The SELECT statement is used to retrieve data from one or more tables.
SQL Example:
SELECT Name, Age FROM Employees WHERE Department = 'HR';
b. INSERT
The INSERT operation adds new records (rows) into a table.
SQL Example:
INSERT INTO Employees (ID, Name, Age, Department)
VALUES (1, 'Alice', 30, 'HR');
c. UPDATE
The UPDATE operation modifies existing records in a table.
SQL Example:
UPDATE Employees
SET Age = 31
WHERE ID = 1;
