Database Language
A Database Management System (DBMS) provides various languages and interfaces to interact with the database for defining structures, manipulating data, controlling access, and managing transactions.
These database languages are used to read, insert, update, and delete data, as well as to define and control the database schema and its security.
Types of Database Language
Database languages are generally classified into four main types:
1. Data Definition Language (DDL)
DDL is used to define and modify the structure of database objects such as tables, schemas, indexes, views, and constraints.
It is used by database administrators or developers to define the skeleton of the database.
Key DDL Commands
Command | Description |
---|---|
CREATE | Creates new objects in the database (e.g., tables, views, schemas). |
ALTER | Modifies the structure of an existing object (e.g., add a column to a table). |
DROP | Deletes objects from the database permanently. |
TRUNCATE | Removes all records from a table without logging individual row deletions. |
RENAME | Renames a database object. |
COMMENT | Adds descriptive comments on database objects (stored in the data dictionary). |
DDL commands affect the schema and are usually auto-committed (i.e., changes are permanent immediately).
2. Data Manipulation Language (DML)
DML is used to perform operations on the data stored in database tables.
It allows users to retrieve, insert, modify, and delete the actual data.
Key DML Commands
Command | Description |
---|---|
SELECT | Retrieves data from one or more tables. |
INSERT | Adds new rows of data into a table. |
UPDATE | Modifies existing data within a table. |
DELETE | Removes existing rows from a table. |
MERGE | Performs UPSERT — inserts or updates data based on a condition. |
CALL | Executes a stored procedure or subprogram (PL/SQL or Java). |
EXPLAIN PLAN | Displays the execution plan for a SQL query, used for optimization. |
LOCK TABLE | Controls concurrency by explicitly locking a table during a transaction. |
DML operations are not auto-committed and usually require TCL (Transaction Control Language) to finalize or undo the changes.
3. Data Control Language (DCL)
DCL is used to control access and permissions on database objects.
It defines security levels and determines who can do what in the database system.
Key DCL Commands
Command | Description |
---|---|
GRANT | Gives specific privileges to users or roles (e.g., SELECT, INSERT, UPDATE). |
REVOKE | Removes previously granted privileges from users or roles. |
DCL commands help implement authorization and access control.
Some systems (e.g., Oracle) auto-commit DCL commands, so they cannot be rolled back.
4. Transaction Control Language (TCL)
TCL is used to manage transactions in the database.
It ensures data consistency and integrity during multiple DML operations by grouping them into logical units of work (transactions).
Key TCL Commands
Command | Description |
---|---|
COMMIT | Saves all changes made during the current transaction permanently. |
ROLLBACK | Undoes changes made in the current transaction since the last COMMIT . |
SAVEPOINT | Sets a point within a transaction to which you can roll back later. |
SET TRANSACTION | Configures properties for the current transaction. |
TCL is crucial for maintaining ACID properties (Atomicity, Consistency, Isolation, Durability) in database transactions.
Summary: Database Language Types
Language | Purpose | Example Commands |
---|---|---|
DDL | Define and modify database schema | CREATE , ALTER , DROP , TRUNCATE |
DML | Manipulate and access data | SELECT , INSERT , UPDATE , DELETE , MERGE |
DCL | Control access and permissions | GRANT , REVOKE |
TCL | Manage transactions | COMMIT , ROLLBACK , SAVEPOINT , SET TRANSACTION |
Note:
Most SQL-compliant DBMSs like Oracle, MySQL, SQL Server, and PostgreSQL support all four language types.
DML commands require TCL to explicitly confirm or undo changes.
DDL and DCL commands are often auto-committed, making their changes immediate and irreversible.
SQL - Structured Query Language
SQL uses the terms table, row and column for the formal relational model terms relation, tuple, and attribute, respectively.
SQL Data Definition and Data Types
An SQL Schema is identified by a schema name, and includes an authorization identifier to indicate the user or account who owns the schema, as well as descriptors for each element in the schema.
Schema elements include tables, constraints, views, domains and other constructs (such as authorization grants) that determine the schema.
The main SQL command for data definition is the CREATE statement, which can be used to create schemas, tables(relations), and domains (as well as other constructs such as views, assertions, and triggers).
A Schema is created via the CREATE SCHEMA statement, which can include all the schema elements definition.
Example: To create a schema for airline-related tables and give the authorization ID anita access to all of the objects that use the schema, use the following syntax:
CREATE SCHEMA FLIGHTS AUTHORIZATION anita;
Parts of SQL Language
Data-Definition Language: The SQL DDL provides commands for defining relation schemas, deleting relations, and modifying relation schemas.
Data-Manipulation Language: The SQL DML provides the ability to query information from the database and to insert tuples into, delete tuples from, and modify tuples in the database.
Integrity: The SQL DDL includes commands for specifying integrity constraints that the data stored in the database must satisfy. Updates that violate integrity constraints are disallowed.
View Definition: The SQL DDL includes commands for defining views.
Transaction Control: SQL includes commands for specifying the beginning and end of transactions.
Embedded SQL and Dynamic SQL: Embedded and dynamic SQL define how SQL statements can be embedded within general-purpose programming languages, such as C, C++ and Java.
Authorization: The SQL DDL includes commands for specifying access rights to relations and views.
Attribute Data Types in SQL
In SQL, data types define the kind of values that can be stored in each column of a table. They help ensure data consistency, integrity, and efficient storage.
1. Numeric Data Types
a. Integer Types
INT
/INTEGER
: Standard 4-byte signed integer.SMALLINT
: 2-byte smaller integer, used for values with a limited range.BIGINT
: 8-byte large integer, used for very large numbers.
b. Fixed-Point Types
NUMERIC(p, s)
or DECIMAL(p, s)
:
Used for precise decimal numbers (e.g., currency).
p
(precision): total number of digits.s
(scale): number of digits after the decimal point.Example:
DECIMAL(7,2)
stores numbers like12345.67
.
c. Floating-Point Types
FLOAT(n)
: Approximate numeric, with at leastn
digits of precision.REAL
: Typically a 4-byte floating-point.DOUBLE PRECISION
: Higher precision (usually 8 bytes).
2. Character String Data Types
a. Fixed-Length Strings
CHAR(n)
orCHARACTER(n)
:Always stores exactly
n
characters.Padded with spaces on the right if value is shorter than
n
.
b. Variable-Length Strings
VARCHAR(n)
orCHARACTER VARYING(n)
:Stores up to
n
characters.Uses only as much space as needed (no padding).
c. String Literals & Operations
Literals: Enclosed in single quotes (
'...'
).Case-sensitive:
'abc'
≠'ABC'
.Comparison: Lexicographical (alphabetical).
Concatenation:
Standard SQL:
'abc' || 'def'
→'abcdef'
MySQL:
CONCAT('abc', 'def')
→'abcdef'
3. Large Text and Binary Data Types
a. CLOB (Character Large Object)
Stores large amounts of text.
Specified using units:
CLOB(20M)
→ up to 20 megabytes of text.MySQL Equivalents:
TEXT
: Up to 64KBMEDIUMTEXT
: Up to 16MBLONGTEXT
: Up to 4GB
b. BLOB (Binary Large Object)
Stores large binary data (e.g., images, audio, videos).
Size can be specified similarly (e.g.,
BLOB(100M)
).MySQL BLOB types:
BLOB
: Up to 64KBMEDIUMBLOB
: Up to 16MBLONGBLOB
: Up to 4GB
4. Bit-String Data Types
BIT(n)
: Fixed-length bit string of n
bits.
- Example:
BIT(5)
stores binary like10101
.
BIT VARYING(n)
: Variable-length bit string up to n
bits.
- Note: Not supported in MySQL.
Bit String Literals
Bit strings are enclosed in single quotes ('...') and preceded by B.
- Use syntax:
B'10101'
5. Boolean Data Type
Standard SQL: Values:
TRUE
,FALSE
,UNKNOWN
(due toNULL
)MySQL: No native
BOOLEAN
type; useTINYINT(1)
0
=FALSE
,1
=TRUE
NULL
=UNKNOWN
Example Table & Query:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
is_active TINYINT(1),
salary DECIMAL(10, 2)
);
INSERT INTO employees (name, is_active, salary) VALUES
('Alice', 1, 5000),
('Bob', 0, 6000),
('Charlie', NULL, 7000);
-- Check 3-valued logic
SELECT name, is_active,
is_active IS TRUE AS is_true_check,
is_active IS FALSE AS is_false_check,
is_active IS NULL AS is_unknown_check
FROM employees;
6. Date and Time Types
a. DATE
Format:
YYYY-MM-DD
Example:
'2025-07-05'
Range:
1000-01-01
to9999-12-31
b. TIMESTAMP(fsp)
Format:
YYYY-MM-DD hh:mm:ss[.fractional_seconds]
fsp
: Fractional seconds precision (0–6)Stores both date and time.
MySQL TIMESTAMP stores internally as seconds since Unix epoch (
1970-01-01 00:00:00 UTC
)