DBMS Languages

A DBMS provides several specialized languages for different types of database operations. These languages are collectively referred to as database languages or sublanguag

Overview

A DBMS provides several specialized languages for different types of database operations. These languages are collectively referred to as database languages or sublanguages.

Categories of DBMS Languages

DBMS Languages ├── DDL — Data Definition Language ├── DML — Data Manipulation Language ├── DCL — Data Control Language └── TCL — Transaction Control Language

1. Data Definition Language (DDL)

DDL is used to define and manage the structure of database objects such as tables, indexes, views, and schemas.

DDL commands modify the schema (structure), not the data.

Command	Purpose
CREATE	Create a new database object
ALTER	Modify an existing object
DROP	Remove a database object
TRUNCATE	Remove all data from a table (structure remains)
RENAME	Rename an object

sql exampleWoHoTech

-- CREATE
CREATE TABLE Student (
    student_id  INT         PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(100) UNIQUE,
    dept_id     INT,
    gpa         DECIMAL(3,2) CHECK (gpa BETWEEN 0.0 AND 10.0)
);

-- ALTER
ALTER TABLE Student ADD COLUMN phone VARCHAR(15);
ALTER TABLE Student DROP COLUMN phone;
ALTER TABLE Student MODIFY COLUMN name VARCHAR(200);

-- DROP
DROP TABLE Student;

-- TRUNCATE
TRUNCATE TABLE Student;

DDL processing:

DDL statements are compiled and stored in the system catalog (data dictionary)
The system catalog records table names, column names, data types, and constraints

2. Data Manipulation Language (DML)

DML is used to manipulate the data within the database — inserting, querying, updating, and deleting rows.

DML commands modify or query the instance (data), not the schema.

Command	Purpose
SELECT	Retrieve data from tables
INSERT	Add new rows to a table
UPDATE	Modify existing rows
DELETE	Remove rows from a table

sql exampleWoHoTech

-- SELECT
SELECT name, gpa FROM Student WHERE dept_id = 1 ORDER BY gpa DESC;

-- INSERT
INSERT INTO Student (student_id, name, email, dept_id, gpa)
VALUES (1001, 'Alice', 'alice@college.edu', 1, 9.2);

-- UPDATE
UPDATE Student SET gpa = 9.5 WHERE student_id = 1001;

-- DELETE
DELETE FROM Student WHERE student_id = 1001;

Procedural vs. Non-Procedural DML

Type	Description	Example
Procedural	Specifies HOW to retrieve data	Relational Algebra
Non-Procedural	Specifies WHAT data to retrieve	SQL (declarative)

3. Data Control Language (DCL)

DCL is used to control access to data in the database. It manages permissions and security.

Command	Purpose
GRANT	Give privileges to a user
REVOKE	Remove privileges from a user

sql exampleWoHoTech

-- Grant SELECT and INSERT on Student table to user 'john'
GRANT SELECT, INSERT ON Student TO 'john'@'localhost';

-- Grant all privileges on all tables
GRANT ALL PRIVILEGES ON university.* TO 'admin'@'localhost';

-- Revoke INSERT privilege
REVOKE INSERT ON Student FROM 'john'@'localhost';

-- Revoke all privileges
REVOKE ALL PRIVILEGES ON university.* FROM 'admin'@'localhost';

4. Transaction Control Language (TCL)

TCL is used to manage transactions within the database, ensuring data integrity across multiple operations.

Command	Purpose
COMMIT	Permanently save transaction changes
ROLLBACK	Undo transaction changes
SAVEPOINT	Set a named point within a transaction
SET TRANSACTION	Configure transaction properties

sql exampleWoHoTech

-- Basic transaction
BEGIN TRANSACTION;
    UPDATE Accounts SET balance = balance - 1000 WHERE acct_id = 'A001';
    UPDATE Accounts SET balance = balance + 1000 WHERE acct_id = 'A002';
COMMIT;

-- Rollback on error
BEGIN TRANSACTION;
    DELETE FROM Employee WHERE emp_id = 999;
    -- Oops, wrong delete
ROLLBACK;

-- Savepoints
BEGIN TRANSACTION;
    INSERT INTO Orders VALUES (101, 'Alice', 500);
    SAVEPOINT sp1;
    INSERT INTO Orders VALUES (102, 'Bob', 300);
    SAVEPOINT sp2;
    -- Roll back only to sp1 (undo Bob's order)
    ROLLBACK TO SAVEPOINT sp1;
COMMIT;

Summary

Language	Purpose	Key Commands
DDL	Define structure	CREATE, ALTER, DROP, TRUNCATE, RENAME
DML	Manipulate data	SELECT, INSERT, UPDATE, DELETE
DCL	Control access	GRANT, REVOKE
TCL	Manage transactions	COMMIT, ROLLBACK, SAVEPOINT

Overview

A DBMS provides several specialized languages for different types of database operations. These languages are collectively referred to as database languages or sublanguages.

Categories of DBMS Languages

DBMS Languages ├── DDL — Data Definition Language ├── DML — Data Manipulation Language ├── DCL — Data Control Language └── TCL — Transaction Control Language

1. Data Definition Language (DDL)

DDL is used to define and manage the structure of database objects such as tables, indexes, views, and schemas.

DDL commands modify the schema (structure), not the data.

Command	Purpose
CREATE	Create a new database object
ALTER	Modify an existing object
DROP	Remove a database object
TRUNCATE	Remove all data from a table (structure remains)
RENAME	Rename an object

sql exampleWoHoTech

-- CREATE
CREATE TABLE Student (
    student_id  INT         PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(100) UNIQUE,
    dept_id     INT,
    gpa         DECIMAL(3,2) CHECK (gpa BETWEEN 0.0 AND 10.0)
);

-- ALTER
ALTER TABLE Student ADD COLUMN phone VARCHAR(15);
ALTER TABLE Student DROP COLUMN phone;
ALTER TABLE Student MODIFY COLUMN name VARCHAR(200);

-- DROP
DROP TABLE Student;

-- TRUNCATE
TRUNCATE TABLE Student;

DDL processing:

DDL statements are compiled and stored in the system catalog (data dictionary)
The system catalog records table names, column names, data types, and constraints

2. Data Manipulation Language (DML)

DML is used to manipulate the data within the database — inserting, querying, updating, and deleting rows.

DML commands modify or query the instance (data), not the schema.

Command	Purpose
SELECT	Retrieve data from tables
INSERT	Add new rows to a table
UPDATE	Modify existing rows
DELETE	Remove rows from a table

sql exampleWoHoTech

-- SELECT
SELECT name, gpa FROM Student WHERE dept_id = 1 ORDER BY gpa DESC;

-- INSERT
INSERT INTO Student (student_id, name, email, dept_id, gpa)
VALUES (1001, 'Alice', 'alice@college.edu', 1, 9.2);

-- UPDATE
UPDATE Student SET gpa = 9.5 WHERE student_id = 1001;

-- DELETE
DELETE FROM Student WHERE student_id = 1001;

Procedural vs. Non-Procedural DML

Type	Description	Example
Procedural	Specifies HOW to retrieve data	Relational Algebra
Non-Procedural	Specifies WHAT data to retrieve	SQL (declarative)

3. Data Control Language (DCL)

DCL is used to control access to data in the database. It manages permissions and security.

Command	Purpose
GRANT	Give privileges to a user
REVOKE	Remove privileges from a user

sql exampleWoHoTech

-- Grant SELECT and INSERT on Student table to user 'john'
GRANT SELECT, INSERT ON Student TO 'john'@'localhost';

-- Grant all privileges on all tables
GRANT ALL PRIVILEGES ON university.* TO 'admin'@'localhost';

-- Revoke INSERT privilege
REVOKE INSERT ON Student FROM 'john'@'localhost';

-- Revoke all privileges
REVOKE ALL PRIVILEGES ON university.* FROM 'admin'@'localhost';

4. Transaction Control Language (TCL)

TCL is used to manage transactions within the database, ensuring data integrity across multiple operations.

Command	Purpose
COMMIT	Permanently save transaction changes
ROLLBACK	Undo transaction changes
SAVEPOINT	Set a named point within a transaction
SET TRANSACTION	Configure transaction properties

sql exampleWoHoTech

-- Basic transaction
BEGIN TRANSACTION;
    UPDATE Accounts SET balance = balance - 1000 WHERE acct_id = 'A001';
    UPDATE Accounts SET balance = balance + 1000 WHERE acct_id = 'A002';
COMMIT;

-- Rollback on error
BEGIN TRANSACTION;
    DELETE FROM Employee WHERE emp_id = 999;
    -- Oops, wrong delete
ROLLBACK;

-- Savepoints
BEGIN TRANSACTION;
    INSERT INTO Orders VALUES (101, 'Alice', 500);
    SAVEPOINT sp1;
    INSERT INTO Orders VALUES (102, 'Bob', 300);
    SAVEPOINT sp2;
    -- Roll back only to sp1 (undo Bob's order)
    ROLLBACK TO SAVEPOINT sp1;
COMMIT;

Summary

Language	Purpose	Key Commands
DDL	Define structure	CREATE, ALTER, DROP, TRUNCATE, RENAME
DML	Manipulate data	SELECT, INSERT, UPDATE, DELETE
DCL	Control access	GRANT, REVOKE
TCL	Manage transactions	COMMIT, ROLLBACK, SAVEPOINT

DBMS Languages

Overview

Categories of DBMS Languages

1. Data Definition Language (DDL)

2. Data Manipulation Language (DML)

Procedural vs. Non-Procedural DML

3. Data Control Language (DCL)

4. Transaction Control Language (TCL)

Summary

Continue learning this concept

DBMS Languages

Overview

Categories of DBMS Languages

1. Data Definition Language (DDL)

2. Data Manipulation Language (DML)

Procedural vs. Non-Procedural DML

3. Data Control Language (DCL)

4. Transaction Control Language (TCL)

Summary

Continue learning this concept