Unit 3: Relational Query Languages

What is a Relational Query Language?

When we store data in a database, we need a way to talk to the database — to create, read, update, or delete data.
This communication happens through a query language.

A Relational Query Language helps us to:

• Ask questions (queries) from a relational database (like MySQL, Oracle, MS SQL Server, etc.).
• Get the required information in an organized form (like a table).

What is SQL (Structured Query Language)?

SQL stands for Structured Query Language.
It is the standard language used to manage and manipulate databases.

In simple words:

SQL is like the language you use to “talk” to your database.

You can use SQL to:

• Create a new table
• Insert data
• Update existing data
• Delete data
• Retrieve (view) data

Two Main Parts of SQL

SQL has many components, but two of the most important are:

1. DDL – Data Definition Language
2. DML – Data Manipulation Language

Let’s understand both in easy terms 

1. DDL (Data Definition Language)

DDL is used to define the structure of the database — such as tables, fields, and their data types.

It deals with how data is stored.

Think of DDL as setting up the foundation of your database.

Common DDL Commands:

Command	Full Form	Purpose	Example
CREATE	Create a new table or database	Defines structure	CREATE TABLE students (id INT, name VARCHAR(50));
ALTER	Modify an existing table	Add or remove columns	ALTER TABLE students ADD age INT;
DROP	Delete a table or database permanently	Removes structure	DROP TABLE students;
TRUNCATE	Delete all data but keep table structure	Removes data quickly	TRUNCATE TABLE students;
RENAME	Change the name of a table	Updates name	RENAME TABLE students TO learners;

Key Point

• DDL commands change the structure, not the actual data.
• Changes made by DDL are automatically saved (no need for COMMIT).

2. DML (Data Manipulation Language)

DML is used to manipulate or manage the actual data inside tables.

Think of DML as working with the data that lives inside your database tables.

Common DML Commands:

Command	Full Form	Purpose	Example
INSERT	Add new data into a table	Adds a new row	INSERT INTO students VALUES (1, 'Jay', 22);
UPDATE	Change existing data	Modify rows	UPDATE students SET age = 23 WHERE id = 1;
DELETE	Remove data from a table	Deletes rows	DELETE FROM students WHERE id = 1;
SELECT	Retrieve data from table	View information	SELECT * FROM students;

Key Point:

• DML deals with data inside tables, not the table structure.
• Changes made by DML are not permanent until you COMMIT them.

Example

Let’s say we are managing a Student Database.

Step 1: Create a Table (DDL)

CREATE TABLE Students (
    Student_ID INT,
    Name VARCHAR(50),
    Age INT
);

Step 2: Insert Data (DML)

INSERT INTO Students VALUES (1, 'Jay', 22);

Step 3: View Data (DML)

SELECT * FROM Students;

Step 4: Update Data (DML)

UPDATE Students SET Age = 23 WHERE Student_ID = 1;

Step 5: Delete Data (DML)

DELETE FROM Students WHERE Student_ID = 1;

Difference Between DDL and DML

Basis	DDL	DML
Full Form	Data Definition Language	Data Manipulation Language
Purpose	Defines database structure	Manages data inside tables
Examples	CREATE, ALTER, DROP, TRUNCATE	INSERT, UPDATE, DELETE, SELECT
Type of Operation	Structural	Data-related
COMMIT Required?	No (auto saved)	Yes (manual save)

In Short:

• DDL = “Design the table”
• DML = “Work with the data in the table”

Structured Query Language (SQL) and Relational Algebra

Creating, Altering, and Deleting Tables (DDL Commands)

CREATE TABLE

Used to create a new table in the database.

Syntax:

CREATE TABLE table_name (
   column1 datatype constraint,
   column2 datatype constraint,
   ...
);

Example:

CREATE TABLE Employees (
   EmpID INT PRIMARY KEY,
   Name VARCHAR(50),
   Salary DECIMAL(10,2),
   Department VARCHAR(30)
);

ALTER TABLE

Used to add, modify, or delete a column in an existing table.

Purpose	Syntax Example
Add a column	ALTER TABLE Employees ADD Age INT;
Modify column	ALTER TABLE Employees MODIFY Salary DECIMAL(12,2);
Delete a column	ALTER TABLE Employees DROP COLUMN Age;

DROP TABLE

Used to delete the table permanently from the database.

DROP TABLE Employees;

Data Types in SQL

Data Type	Description	Example
INT	Whole numbers	100, -20
DECIMAL(p, q)	Decimal numbers (p = total digits, q = digits after decimal)	12.50
VARCHAR(n)	Variable-length text	'Jay'
CHAR(n)	Fixed-length text	'UP'
DATE	Stores date	'2025-11-06'
BOOLEAN	True/False values	TRUE

Constraints in SQL

Constraints are rules applied to table columns to maintain data accuracy and reliability.

Constraint	Meaning	Example
PRIMARY KEY	Unique & not null	EmpID INT PRIMARY KEY
FOREIGN KEY	Links two tables	DeptID INT FOREIGN KEY REFERENCES Department(DeptID)
NOT NULL	Cannot be empty	Name VARCHAR(30) NOT NULL
UNIQUE	No duplicate values	Email VARCHAR(50) UNIQUE
CHECK	Adds a condition	CHECK (Salary > 10000)
DEFAULT	Sets a default value	Salary DECIMAL(10,2) DEFAULT 15000

Aggregate Functions

Aggregate functions are used to perform calculations on a group of values and return a single value.

Function	Description	Example
COUNT()	Counts number of rows	SELECT COUNT(*) FROM Employees;
SUM()	Adds all values	SELECT SUM(Salary) FROM Employees;
AVG()	Finds average value	SELECT AVG(Salary) FROM Employees;
MAX()	Finds highest value	SELECT MAX(Salary) FROM Employees;
MIN()	Finds lowest value	SELECT MIN(Salary) FROM Employees;

Joins in SQL

A JOIN combines data from two or more tables based on a related column.

Type	Description	Example
INNER JOIN	Returns only matching records from both tables	SELECT E.Name, D.DeptName FROM Employees E INNER JOIN Department D ON E.DeptID = D.DeptID;
LEFT JOIN	Returns all records from left table + matched from right	SELECT E.Name, D.DeptName FROM Employees E LEFT JOIN Department D ON E.DeptID = D.DeptID;
RIGHT JOIN	Returns all records from right table + matched from left	SELECT E.Name, D.DeptName FROM Employees E RIGHT JOIN Department D ON E.DeptID = D.DeptID;
FULL JOIN	Returns all records when there is a match in either table	(Some databases: FULL OUTER JOIN)

Sub-Queries

A Sub-query is a query inside another query.

Example:

SELECT Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

🟢 This shows employees earning more than the average salary.

Views

A View is a virtual table created using a SQL query.
It does not store data physically — it displays data from one or more tables.

Syntax:

CREATE VIEW HighSalary AS
SELECT Name, Salary FROM Employees WHERE Salary > 50000;

Now you can use:

SELECT * FROM HighSalary;

Transaction Control Commands (TCL)

Used to manage changes made by DML (INSERT, UPDATE, DELETE) commands.

Command	Purpose	Example
COMMIT	Saves the changes permanently	COMMIT;
ROLLBACK	Cancels the recent changes	ROLLBACK;
SAVEPOINT	Sets a point to which you can rollback	SAVEPOINT sp1;

Relational Algebra

Relational Algebra is a theoretical language for querying databases.
It uses operators to get results from relations (tables).

Operator	Symbol / Command	Description	Example
Selection (σ)	σ condition (Table)	Selects specific rows	σ Dept='HR' (Employees)
Projection (π)	π column_list (Table)	Selects specific columns	π Name, Salary (Employees)
Join (⋈)	Table1 ⋈ Table2	Combines related tables	Employees ⋈ Department
Union (∪)	Table1 ∪ Table2	Combines results (no duplicates)	SELECT Name FROM TableA UNION SELECT Name FROM TableB;
Intersection (∩)	Table1 ∩ Table2	Returns common rows	SELECT Name FROM TableA INTERSECT SELECT Name FROM TableB;
Difference (−)	Table1 − Table2	Rows in first table, not in second	SELECT Name FROM TableA MINUS SELECT Name FROM TableB;

Relational Calculus

Relational Calculus is a non-procedural language —
it focuses on what to get, not how to get it.

There are two types:

1. Tuple Relational Calculus (TRC) → works on rows
2. Domain Relational Calculus (DRC) → works on columns/attributes

Example (TRC):

{ t.Name | t ∈ Employees AND t.Salary > 50000 }

Means: Get the names of employees whose salary is greater than 50,000.

Summary Table

Topic	Key Purpose	Example
DDL	Define structure	CREATE, ALTER, DROP
DML	Manage data	INSERT, UPDATE, DELETE
Aggregate	Summarize data	SUM(), AVG()
Joins	Combine tables	INNER JOIN
Sub-query	Query inside a query	WHERE Salary > (SELECT AVG(Salary))
View	Virtual table	CREATE VIEW
TCL	Manage transactions	COMMIT, ROLLBACK
Relational Algebra	Theoretical operations	Selection, Projection, Join
Relational Calculus	Non-procedural query	TRC/DRC expressions