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.

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.

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.

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).

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.

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

• 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: The Structured Query Language

SQL (Structured Query Language) is the standard language for managing and manipulating relational databases. It allows users to create, retrieve, update, and delete data.

In SQL, the terminology is slightly different from the formal relational model:

• Table is used for relation.

• Row is used for tuple.

• Column is used for attribute.

The Anatomy of SQL: Core Components

SQL is divided into several sub-languages, each responsible for a different set of tasks.

• Data Definition Language (DDL): Used to define and manage the database structure. These commands create, alter, or drop database objects.

• Data Manipulation Language (DML): Used to manage the data within the database objects. These are the most frequently used commands.

• Data Control Language (DCL): Used to control access and permissions to the database.

• Transaction Control Language (TCL): Used to manage transactions to ensure data integrity.

• Other Components: SQL also includes elements for defining views (virtual tables), handling embedded/dynamic SQL in programming languages, and specifying integrity constraints.

SQL Data Definition

The primary DDL command is CREATE, which is used to build the framework of a database, from the overall schema to individual tables.

Creating a Table

The CREATE TABLE command defines a new table, specifying its columns and their respective data types.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    hire_date DATE,
    salary DECIMAL(10, 2),
    is_active BOOLEAN
    -- In MySQL, BOOLEAN is often implemented as TINYINT(1)
);

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.

Numeric Types

Used for storing numerical data.

Integer Types

For whole numbers.

• INT or INTEGER: The standard choice for whole numbers. Typically a 4-byte integer.

• SMALLINT: A smaller integer type, useful when the range of values is limited. Saves storage space (2 bytes).

• BIGINT: For extremely large whole numbers (8 bytes).

Fixed-Point Types

For exact decimal values, essential for financial calculations where precision is non-negotiable.

• DECIMAL(p, d) or NUMERIC(p, d): Stores numbers with user-defined precision. The parameter p is the total number of digits (precision), and d is the number of digits to the right of the decimal point (scale). For example, DECIMAL(7, 2) can store a number like 12345.67.

Floating-Point Types

For approximate decimal values, suitable for scientific measurements where absolute precision isn't as critical.

• FLOAT(n): A floating-point number with a precision of at least n digits.

• REAL and DOUBLE PRECISION: Represent single-precision and double-precision floating-point numbers, respectively.

Character String Types

Used for storing text.

• CHAR(n): A fixed-length string. If a stored string is shorter than n, it is padded with spaces. Best for data with a consistent length.

• VARCHAR(n): A variable-length string with a maximum length of n. It only uses storage for the characters entered. This is the most common choice for text data.

String Literals and Operations

• String values (literals) are enclosed in single quotes: 'Hello, World!'.

• String comparisons are typically case-sensitive.

• Concatenation combines strings:

  • Standard SQL: || (e.g., 'First' || ' ' || 'Last')

  • MySQL: CONCAT() function (e.g., CONCAT('First', ' ', 'Last'))

Large Object (LOB) Types

For storing very large amounts of data.

• CLOB (Character Large Object): Stores large blocks of text. MySQL equivalents include TEXT, MEDIUMTEXT, and LONGTEXT.

• BLOB (Binary Large Object): Stores large binary data, such as images or files. MySQL equivalents include BLOB, MEDIUMBLOB, and LONGBLOB.

Date and Time Types

• DATE: Stores a date (year, month, and day). The standard format is 'YYYY-MM-DD'.

• TIME: Stores the time of day (hours, minutes, and seconds). The standard format is 'HH:MM:SS'. Can include WITH TIME ZONE.

• TIMESTAMP: Includes both date and time fields, often with fractional seconds. The format is 'YYYY-MM-DD HH:MM:SS.FFFFFF'. Can include WITH TIME ZONE.

• INTERVAL: Represents a duration of time (e.g., '2 days'). It is used for date and time arithmetic.

Bit-String Types

For storing strings of bits (0s and 1s).

• BIT(n): A fixed-length bit string of n bits.

• BIT VARYING(n): A variable-length bit string up to n bits.

• Literals: Bit strings are specified using B followed by the string in single quotes (e.g., B'10110').

Boolean Type

A data type that can hold one of three values: TRUE, FALSE, or UNKNOWN. The UNKNOWN value arises from SQL's three-valued logic, which accounts for the presence of NULL values in comparisons.

• Standard SQL: Natively supports the BOOLEAN type.

• MySQL: Does not have a native BOOLEAN type. The common practice is to use TINYINT(1), where 0 represents FALSE, 1 represents TRUE, and NULL represents UNKNOWN.

Example: Three-Valued Logic in Practice

The following query demonstrates how TRUE, FALSE, and UNKNOWN (NULL) are handled.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    is_active TINYINT(1),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (id, name, is_active, salary) VALUES
    (1, 'Alice', 1, 5000),
    (2, 'Bob', 0, 6000),
    (3, 'Charlie', NULL, 7000);

-- Check 3-valued logic results
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;