SQL Data Types, Commands, Relational Algebra & Calculus

Posted on Dec 31, 2025 in Computers

SQL Data Types and Their Purpose

SQL data types specify the type of data that can be stored in a database table. Choosing the correct data type improves storage efficiency, performance, and data integrity.

Numeric Data Types

These types are used to store numeric values.

  • INT / INTEGER – stores whole numbers.
  • SMALLINT / BIGINT – stores small or large integers.
  • DECIMAL(p, s) / NUMERIC – stores fixed-point (exact) numbers.
  • FLOAT / REAL – stores floating-point numbers (approximate).

Character (String) Data Types

These types are used to store text.

  • CHAR(n) – fixed-length character string.
  • VARCHAR(n) – variable-length character string.
  • TEXT – stores large text data.

Date and Time Data Types

Used to store date and time values.

  • DATE – stores a date (YYYY-MM-DD).
  • TIME – stores a time value.
  • DATETIME / TIMESTAMP – stores date and time together.

Boolean Data Type

BOOLEAN – stores TRUE or FALSE values.

SQL Components and Command Categories

SQL commands are grouped by functionality. These components help manage database structure, data, access, and queries.

1. DDL (Data Definition Language)

DDL defines and modifies the structure of database objects such as tables, views, and indexes.

Common DDL commands:

  • CREATE – creates a database, table, or other object.
  • ALTER – modifies an existing object (for example, table structure).
  • DROP – deletes an object such as a table or database.
  • TRUNCATE – removes all records from a table (structure remains).

Example:

CREATE TABLE Student (
  RollNo INT,
  Name VARCHAR(50)
);

2. DML (Data Manipulation Language)

DML is used to insert, update, and delete data in database tables.

Common DML commands:

  • INSERT – adds new records.
  • UPDATE – modifies existing records.
  • DELETE – removes records.

Example: INSERT INTO Student VALUES (1, 'Ravi');

3. DQL (Data Query Language)

DQL is used to retrieve data from the database.

Primary DQL command:

  • SELECT – fetches required data from tables.

Example: SELECT * FROM Student;

4. DCL (Data Control Language)

DCL controls access and permissions in the database.

Common DCL commands:

  • GRANT – gives user permissions.
  • REVOKE – removes user permissions.

Example: GRANT SELECT ON Student TO User1;

Comparing DDL, DML, DQL and DCL

High-level differences:

  • DDL – defines structure; affects schema; commands: CREATE, ALTER.
  • DML – manipulates data; affects data; commands: INSERT, UPDATE.
  • DQL – retrieves data; generally read-only; command: SELECT.
  • DCL – controls access and security; commands: GRANT, REVOKE.

Meaning, Purpose, and Need of SQL

Meaning of SQL: SQL stands for Structured Query Language. It is the standard language used to create, store, retrieve, update, and manage data in relational databases. SQL was developed by IBM in the early 1970s and later standardized by ANSI and ISO. It enables communication between users/applications and the database management system (DBMS). Major relational DBMSs such as MySQL, Oracle, SQL Server, and PostgreSQL use SQL.

Purpose of SQL

The main purpose of SQL is to provide an easy and efficient way to interact with databases. SQL allows users to perform operations on data without needing to know internal database storage details.

  • Data retrieval – use SELECT to fetch required data from one or more tables.
  • Data insertion – use INSERT to add new records.
  • Data update – use UPDATE to modify existing data.
  • Data deletion – use DELETE to remove unwanted records.
  • Database creation and modification – use CREATE, ALTER, DROP to manage databases and tables.

Why SQL is Needed

SQL is necessary to manage databases effectively and efficiently. Key reasons include:

  • User-friendly syntax – SQL uses English-like commands that are easy to learn and use.
  • Standard language – supported by most relational DBMSs, making commands portable across systems.
  • Efficient data handling – SQL can handle large volumes of data quickly and efficiently.
  • Data security – features like GRANT and REVOKE control access to data.
  • Data integrity – enforces constraints such as PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK.
  • Multi-user support – supports concurrent access by multiple users.

Advantages of SQL

  • Easy to understand and use.
  • Supports complex queries.
  • Provides high data security.
  • Reduces data redundancy.
  • Works with large databases.

Relational Algebra: Theory and Operations

Relational algebra is a procedural query language used in DBMSs. It specifies how to retrieve data by defining a sequence of operations. It operates on relations (tables) and produces a relation as output. Relational algebra forms a theoretical foundation for SQL and is used internally by DBMSs to process queries.

Basic Operations of Relational Algebra

  • Projection (π) – selects columns.
  • Selection (σ) – selects rows based on conditions.
  • Union (∪) – combines tuples from two relations.
  • Set Difference (−) – removes tuples found in another relation.
  • Cartesian Product (×) – combines every tuple of one relation with every tuple of another.
  • Rename (ρ) – renames a relation or attributes.
  • Join (⨝) – combines related tuples from tables.

Example: To find students from the CS department: σ Dept = 'CS' (STUDENT)

Relational Calculus: Concepts and Types

Relational calculus is a non-procedural (declarative) query language. It specifies what data is required, not how to retrieve it; the DBMS decides the best execution plan. Relational calculus is based on mathematical logic and predicate calculus.

Types of Relational Calculus

There are two main types:

  • Tuple Relational Calculus (TRC) – uses tuple variables to represent tuples (rows) of a relation. General form: { t | P(t) }, where t is a tuple variable and P(t) is a predicate (condition).
  • Domain Relational Calculus (DRC) – uses domain (attribute) variables that represent values from attribute domains instead of whole tuples. General form: { <x1, x2, …, xn> | P(x1, x2, …, xn) }.

Examples:

  • TRC: To find names of students from the CS department: { t.Name | t ∈ STUDENT ∧ t.Dept = 'CS' }.
  • DRC: To find names of students from the CS department: { <Name> | ∃ RollNo, Dept (STUDENT(RollNo, Name, Dept) ∧ Dept = 'CS') }.

Features of TRC and DRC

  • TRC – uses tuple variables; conditions use logical operators (AND, OR, NOT); result is a set of tuples; conceptually easy to understand.
  • DRC – uses domain/attribute variables; conditions are based on attribute values; also produces a set of tuples; more value-oriented and sometimes more detailed.

Key Differences: Relational Algebra vs Relational Calculus

Comparison summary:

  • Relational Algebra – procedural language; tells how to get data; uses operators; based on algebra; used internally by DBMS; output is a relation.
  • Relational Calculus – non-procedural language; tells what data is required; uses logical expressions; based on predicate logic; used for theoretical query formulation; output is a set of tuples.

Advantages and Limitations

Relational Algebra:

  • Clear execution steps.
  • Efficient query optimization by the DBMS.
  • Less user-friendly for writing queries directly.

Relational Calculus:

  • Simple and flexible declarative expressions.
  • Easy to write queries conceptually.
  • Execution method is hidden (DBMS determines strategy).