UNIT – II

RELATIONAL QUERY LENGUAGES

Relational Algebra Operations
Relational algebra in DBMS refers to a procedural query language used to query
the database. It consists of a set of relational algebra operations in DBMS, such as
selection, projection, union, and more, that can be applied to relations to retrieve or
manipulate data.
The result of these operations is typically a relation, which can be further
processed with other operations. This mathematical approach helps in understanding
the structure of queries and optimizing them for better performance in DBMS
environments.
Types of Relational Algebra in DBMS
The relational algebra operations in DBMS can be divided into several
categories based on their nature such as:
1. Unary Operations
Unary operations involve a single relation as input and produce a new relation
as output.
 Selection (σ)
The selection operation is used to retrieve rows from a relation that satisfies a
given condition.
Syntax
σ(condition)(Relation)
Projection (π)
The projection operation is used to retrieve specific columns from a relation,
effectively removing other columns.
Syntax
π(column1, column2, ...)(Relation)
Where column1, column2, ... are the columns to be retrieved from the relation.
Example: Consider a relation Employee with the following columns
Emp_Id Name Salary Dept
101 ram 55,000 Marketing
102 sitha 60,000 HR
103 shiva 45,000 Marketing
104 varun 70,000 IT
Query: Retrieve employees with a salary greater than 55,000.
σ(Salary >=55000)(Employee)
Emp_Id Name Salary Dept
101 ram 55,000 Marketing
102 sitha 60,000 HR
104 varun 70,000 IT

2. Binary Operations
Binary operations work with two relations and produce a new relation as a result.
Union (∪)
The union operation combines the results of two relations, eliminating
duplicates. It requires both relations to have the same number of attributes with
matching domains.
Syntax
Relation1 ∪ Relation2
Example
If Employees has the columns Employee_ID, Name, and Salary, and another
relation Managers also has the same columns, the union operation will combine all rows
from both relations, ensuring no duplicate rows
Relation 1: Employees
Emp_Id Name
101 arun
102 shiva
Relation 2: Department
Dept_Id Dept_Name
201 HR
202 Finance
Cartesian Product (Employees × Departments)
Emp_Id Name Dept_Id Dept_Name
101 Arun 201 HR
102 Shiva 202 Finance
Intersection (∩)
The intersection operation returns the rows that are common to both relations. It is
equivalent to performing a Union followed by a Selection on common elements.

Syntax
Relation1 ∩ Relation2
Example
If both Employees and Managers have the same structure, the operation Employees ∩
Managers will return only those employees who are also managers.
Relation1 (Employees)

Employee_ID Name Salary

101 Ram 50000

102 Sita 55000

103 Deepthi 60000

Relation2 (Managers)

Employee_ID Name Salary

103 Deepthi 60000

104 Varun 65000

Intersection (Employees ∩ Managers)

Employee_ID Name Salary

103 Deepthi 60000

3. Additional Operations
The additional operations of relational algebra in DBMS:
Rename (ρ)
The rename operation is used to rename a relation or its attributes.
Syntax
ρ(new_name, Relation)
Join Operations:
Join operations are used to combine data from two relations based on some condition.
Theta Join (⨝θ):
Combines rows from two relations based on a specified condition (θ). This condition
can involve any comparison operator (=, <, >, etc.).
Equi Join:
A specific type of theta join where the condition uses the equality operator (=).
Natural Join:
A type of equi join that automatically joins on columns with the same name in both
relations.
Other Operations:
Division (÷): Used to find tuples that are related to all tuples in another relation.
Intersection (∩): Returns rows that are present in both relations.

Relational calculus
 It is a Declarative Langauge or non-procedural query language.
 Before understanding Relational calculus in DBMS, we need to understand
Procedural Language and Declarative Langauge.
Types of Relational Calculus in DBMS

1. Tuple Relational Calculus (TRC)

2. Domain Relational Calculus (DRC)

[Link] Relational Calculus (TRC)

Tuple Relational Calculus uses a tuple variable (t) that goes to each row of the
table and checks if the predicate condition is true or false for the given row.
Depending on the given predicate condition, it returns the row or part of the row.
A tuple relational calculus query has the form
Syntax
{t | P(t)}

where t is a tuple variable and P(t) is a logical formula that describes the conditions that
the tuples in the result must satisfy. The curly braces {} are used to indicate that the
expression is a set of tuples.
P(t) may have various conditions logically combined with OR (∨), AND (∧), NOT(¬).

There are two types of tuple variables

  Free Variables: Any tuple variable without any ‘For All’ or ‘there exists’
condition is calledFreeVariable
 Bounded Variables: Any tuple variable with the ‘For All’ or ‘there exists’
condition is called Bounded Variable
Example: Retrieve the name of the students whose age is < 18
[Link] | Student(T) AND [Link]< 18

Domain Relational Calculus (DRC)

Domain Relational Calculus uses domain Variables to get the column values
required from the database based on the predicate expression or condition.
The key idea of Domain Relational Calculus is to define queries in terms of the
domains (sets of values) of the attributes, rather than the tuples or records.
Queries are formulated as expressions involving variables that range over
domains.
A Domain Relational Calculus query has the form
Syntax
{<x1,x2,x3,x4...> | P(x1,x2,x3,x4...)}

where each Xi is either a domain variable or a constant, p(x1, x2, ... ,xn ) denotes a DRC
formula composed of atoms.

Example: Retrieve the name and branch of the students whose age is < 18
{< name, branch > | ∈ Student ∧ age < 18}

SQL - Structured query language

Structured query language (SQL) is a programming language for storing and
processing information in a relational database.
A relational database stores information in tabular form, with rows and columns
representing different data attributes and the various relationships between the data
values.
Basic Structure of SQL Queries:
The basic structure of an SQL query consists of three main clauses: SELECT,
FROM, and WHERE.
The SELECT clause specifies which columns to retrieve,
The FROM clause indicates the table(s) to retrieve data from, and
The optional WHERE clause filters the rows based on a specified condition.
1. SELECT Clause:
  This clause is used to specify the columns (attributes) you want to retrieve from
the database.
 It is the first clause in an SQL query and is followed by the names of the columns
you want to see in the result set, separated by commas.

Example:Customer

SELECT CustomerName, LastName

FROM Customer;

2. FROM Clause:
 This clause indicates the table(s) from which you want to retrieve data.
 It follows the SELECT clause and specifies the table name(s).
 For example:

SELECT * FROM Customer;

3. WHERE Clause (Optional):

 This clause is used to filter the rows based on a specific condition.
 It is placed after the FROM clause and includes a condition that must be true for
a row to be included in the result set.
 For example:

SELECT CustomerName
FROM Customer
 where Age = '21';

SELECT with GROUP BY Clause

In this example, we will use SELECT statement with GROUP BY Clause to Group
rows and perform aggregation. Here, Count orders per customer.
Query:
SELECT Customer_id, COUNT(*) AS order_count
FROM Orders
GROUP BY Customer_id;

SELECT Statement with HAVING Clause

Use HAVING to filter results after grouping. Consider the following database for
fetching departments with total salary above 50,000.

Use WHERE for row-level filtering, HAVING for group-level filtering.

Query:

SELECT Department, sum(Salary) as Salary

FROM employee
GROUP BY department
HAVING SUM(Salary) >= 50000;

SET Operations in SQL

SQL supports few Set operations which can be performed on the table data. These
are used to get meaningful results from data stored in the table, under different special
conditions.

In this tutorial, we will cover 4 different types of SET operations, along with example:

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS
UNION Operation

UNION is used to combine the results of two or more SELECT statements.

However it will eliminate duplicate rows from its resultset. In case of union,
number of columns and datatype must be same in both the tables, on which UNION
operation is being applied.
 Example of UNION

The First table,

Id Name
1 Abhi
2 Adam

The Second table,

Id Name
2 Adam
4 Arun

SELECT * FROM First

UNION
SELECT * FROM Second;
Id Name
1 Abhi
2 Adam
4 Arun

UNION ALL

This operation is similar to Union. But it also shows the duplicate rows.

SELECT * FROM First

UNION ALL
SELECT * FROM Second;
Id Name
1 Abhi
2 Adam
2 Adam
4 Arun
INTERSECT

Intersect operation is used to combine two SELECT statements, but it only retuns the
records which are common from both SELECT statements. In case of Intersect the
number of columns and datatype must be same.

Ex:

SELECT * FROM First

INTERSECT
SELECT * FROM Second;
Id Name
2 Adam

MINUS

The Minus operation combines results of two SELECT statements and return only those
in the final result, which belongs to the first set of the result.

Ex:

SELECT * FROM First

MINUS
SELECT * FROM Second;

Id Name
1 abhi
Aggregate functions

In relational database management systems (RDBMS), aggregate

functions perform calculations on sets of rows and return a single value.
They are used to summarize data from multiple rows into a concise result, often
for reporting or analysis purposes.
Common examples include SUM, AVG, COUNT, MIN, and MAX.
1. Count()
The COUNT() function returns the number of rows that match a given condition or
are present in a column.
 COUNT(*): Counts all rows.
 COUNT(column_name): Counts non-NULL values in the specified column.
 COUNT(DISTINCT column_name): Counts unique non-NULL values in the
column.

Examples:
-- Total number of records in the table
SELECT COUNT(*) AS TotalRecords FROM Employee;

-- Count of non-NULL salaries

SELECT COUNT(Salary) AS NonNullSalaries FROM Employee;

-- Count of unique non-NULL salaries

SELECT COUNT(DISTINCT Salary) AS UniqueSalaries FROM Employee;

2. SUM()
The SUM() function calculates the total sum of a numeric column.

 SUM(column_name): Returns the total sum of all non-NULL values in a column.

Examples:
-- Calculate the total salary
SELECT SUM(Salary) AS TotalSalary FROM Employee;

-- Calculate the sum of unique salaries

SELECT SUM(DISTINCT Salary) AS DistinctSalarySum FROM Employee;

3. AVG()
The AVG() function calculates the average of a numeric column. It divides the sum
of the column by the number of non-NULL rows.

 AVG(column_name): Returns the average of the non-NULL values in the column.

Examples:
-- Calculate the average salary
SELECT AVG(Salary) AS AverageSalary FROM Employee;

-- Average of distinct salaries

SELECT AVG(DISTINCT Salary) AS DistinctAvgSalary FROM Employee;

4. MIN() and MAX()

The MIN() and MAX() functions return the smallest and largest values,
respectively, from a column.

 MIN(column_name): Returns the minimum value.

 MAX(column_name): Returns the maximum value.
Examples:
-- Find the highest salary
SELECT MAX(Salary) AS HighestSalary FROM Employee;

-- Find the lowest salary

SELECT MIN(Salary) AS LowestSalary FROM Employee;

Using Aggregate Functions with GROUP BY

SQL GROUP BY allows us to group rows that have the same values in specific
columns.
We can then apply aggregate functions to these groups, which helps us
summarize data for each group.
This is commonly used with the COUNT(), SUM(), AVG(), MIN(), and MAX()
functions.

Example: Total Salary by Each Employee

SELECT Name, SUM(Salary) AS TotalSalary
FROM Employee
GROUP BY Name;

Output:
Name TotalSalary

A 802

B 403

C 604

D 705
Name TotalSalary

E 606

F -

Using HAVING with Aggregate Functions

The HAVING clause is used to filter results after applying aggregate functions,
unlike WHERE, which filters rows before aggregation.
HAVING is essential when we want to filter based on the result of an aggregate
function.

Example: Find Employees with Salary Greater Than 600

SELECT Name, SUM(Salary) AS TotalSalary
FROM Employee
GROUP BY Name
HAVING SUM(Salary) > 600;
Output:
Name TotalSalary

A 802

C 604

D 705

E 606

NULL values in SQL

In SQL, some records in a table may not have values for every field, and such
fields are termed as NULL values.
These occur when data is unavailable during entry or when the attribute does not apply
to a specific record.
To handle such scenarios, SQL provides a special placeholder value
called NULL to represent unknown, unavailable, or inapplicable data.
1. Value Unknown: The value exists but is not known.
2. Value Not Available: The value exists but is intentionally withheld.
3. Attribute Not Applicable: The value is undefined for a specific record.

Principles of NULL values

 Setting a NULL value is appropriate when the actual value is unknown, or when
a value is not meaningful.
 A NULL value is not equivalent to a value of ZERO if the data type is a number
and is not equivalent to spaces if the data type is a character.
 A NULL value can be inserted into columns of any data type.
 A NULL value will evaluate NULL in any expression.

Logical Behavior
SQL uses three-valued logic (3VL): TRUE, FALSE, and UNKNOWN. Logical
expressions involving NULL return UNKNOWN.
 AND: Returns FALSE if one operand is FALSE; otherwise, returns UNKNOWN.
 OR: Returns TRUE if one operand is TRUE; otherwise, returns UNKNOWN.
 NOT: Negates the operand; UNKNOWN remains UNKNOWN.

Logical Behaviour of AND

Logical Behaviour of OR

IS NULL Operator
In this query, it retrieves the Fname and Lname of employees whose SSN is NULL.
Since SSN represents a unique identifier, rows with NULL in this column
indicate missing data. This query helps identify records that lack this essential
information.
Query:
SELECT Fname, Lname FROM Employee WHERE SSN IS NULL;
Output

IS
NULL Operator
IS NOT NULL Operator
In this query, it counts the number of employees who have a valid SSN by excluding
rows where SSN is NULL. The result provides the total number of employees with
an SSN present in the table. The COUNT(*) function ensures that all non-
NULL rows are included in the count.
Query
SELECT COUNT(*) AS Count FROM Employee WHERE SSN IS NOT NULL;
Output

Nested Queries in SQL

A nested query (also called a subquery) is a query embedded within another SQL
query.
The result of the inner query is used by the outer query to perform additional
operations. Subqueries can be used in various parts of an SQL query such as SELECT,
FROM or WHERE Clauses.
They are commonly used for performing calculations, filtering data or joining
datasets indirectly without explicitly using joins.
Nested queries are useful for filtering data based on complex conditions,
aggregating results or even joining tables without directly using SQL Joins.

 The inner query runs first, providing data for the outer query.
 The output query uses the results of the inner query for comparison or as input.
 Nested queries are particularly useful for breaking down complex problems into
smaller, manageable parts, making it easier to retrieve specific results from large
datasets.
Types of Nested Queries in SQL
There are two primary types of nested queries in SQL, Independent Nested
Queries and Correlated Nested Queries.
Each type has its own use case and benefits depending on the complexity of the
task at hand.
1. Independent Nested Queries
In an independent nested query, the execution of the inner query is independent
of the outer query.
The inner query runs first and its result is used directly by the outer query.
Operators like IN, NOT IN, ANY and ALL are commonly used with independent
nested query.
Example 1: Using IN
In this Example we will find the S_IDs of students who are enrolled in the courses
‘DSA’ or ‘DBMS’. We can break the query into two parts:
Step 1: Find the C_IDs of the courses:
This query retrieves the IDs of the courses named 'DSA' or 'DBMS' from
the COURSE table.
SELECT C_ID FROM COURSE WHERE C_NAME IN ('DSA', 'DBMS');
Output
C_ID

C1

C3

Step 2: Use the result of Step 1 to find the corresponding S_IDs:

The inner query finds the course IDs, and the outer query retrieves the student IDs
associated with those courses from the STUDENT_COURSE table
SELECT S_ID FROM STUDENT_COURSE WHERE C_ID IN (
SELECT C_ID FROM COURSE WHERE C_NAME IN ('DSA', 'DBMS')
);

Output
S_ID

S1

S2

S4

Explanation: In this example, the inner query retrieves the C_IDs of the courses
'DSA' and 'DBMS', and the outer query retrieves the student IDs (S_IDs) enrolled in
those courses.

2. Correlated Nested Queries

In correlated nested queries, the inner query depends on the outer query for its
execution. For each row processed by the outer query, the inner query is executed.
This means the inner query references columns from the outer query. The
EXISTS keyword is often used with correlated queries.
Example 2: Using EXISTS
In this Example, we will find the names of students who are enrolled in the course
with C_ID = 'C1':
SELECT S_NAME FROM STUDENT S
WHERE EXISTS (
SELECT 1 FROM STUDENT_COURSE SC
WHERE S.S_ID = SC.S_ID AND SC.C_ID = 'C1'
);
Output
S_NAME

RAM

RAMESH

Explanation:
For each student in the STUDENT table, the inner query checks if an entry exists in
the STUDENT_COURSE table with the same S_ID and the specified C_ID. If such a
record exists, the student’s name is included in the output.

Views
A view is a table whose rows are not explicitly stored, a view is a virtual table
based on the result-set of an SQL statement.
A view can contain all rows of a table or select rows from a table. A view can be
created from one or many tables which depends on the written SQL query to create a
view.
A view is generated to show the information that the end-user requests the data
according to specified needs rather than complete information of the table.

Advantages of View over database tables

 Using Views, we can join multiple tables into a single virtual table.
 Views hide data complexity.
 In the database, views take less space than tables for storing data because the
database contains only the view definition.
 Views indicate the subset of that data, which is contained in the tables of the
database.

Creating Views
Database views are created using the CREATE VIEW statement. Views can be
created from a single table, multiple tables or another view.
To create a view, a user must have the appropriate system privilege according to the
specific implementation.
Syntax in Mysql

CREATE VIEW view_name AS

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example:
CREATE VIEW Students_CSE AS
SELECT Roll_no,Name
FROM Students
WHERE Branch = 'CSE';

A view can be updated with the CREATE OR REPLACE VIEW statement.

Syntax in Mysql
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

The following SQL adds the "Mobile" column to the "Students_CSE" view:
Example:
CREATE OR REPLACE VIEW Students_CSE AS
SELECT Roll_no,Name,Mobile
FROM Students
WHERE Branch = 'CSE';

CREATE VIEW defines a view on a set of tables or views or both.

REPLACE VIEW redefines an existing view or, if the specified view does not exist,

Inserting a row in a view

We can insert a row in a View in a same way as we do in a table. We can use the
INSERT INTO statement of SQL to insert a row in a View.
Syntax in Mysql

INSERT INTO view_name(column1, column2, ...)

VALUES(value1,value2,.....);
Example:

INSERT INTO Students_CSE(Roll_no,Name,Mobile)

VALUES(521,'ram',9988776655);

Deleting a row in a view

Deleting rows from a view is also as simple as deleting rows from a table. We can
use the DELETE statement of SQL to delete rows from a view.
Syntax in Mysql

DELETE FROM view_name

WHERE condition;

Example:

DELETE FROM Students_CSE

WHERE Name="ram";

Dropping a View
In order to delete a view in a database, we can use the DROP VIEW statement.
Syntax in Mysql

DROP FROM view_name

Example:

DROP FROM Students_CSE;

Modification of database
Modifying a database involves making changes to its existing structure, schema,
or the data it contains.
These modifications are essential for adapting to evolving business needs,
improving data integrity, and optimizing performance.
Here's a breakdown of the key aspects of database modification:
1. Types of modifications
Schema Modifications: These involve changing the database's blueprint, which defines
how data is organized. This can include tasks like:

 Adding or removing tables, fields, or relationships.

 Modifying column properties or data types.
 Creating or dropping indexes.
 Altering constraints like primary keys or foreign keys.
 Example of an SQL ALTER TABLE statement (adding a column)
 ALTER TABLE Customers ADD Email VARCHAR(255);
Data Modifications: These focus on changing the data itself within the database tables.
This involves operations such as:

 Inserting new data or records into a table.

 -- Example of an SQL INSERT statement
 INSERT INTO Customers (CustomerID, CustomerName, ContactName, City,
PostalCode, Country)
 VALUES ('1', 'Alfreds Futterkiste', 'Maria Anders', 'Berlin', '12209', 'Germany');

 Updating existing records with new values.

 Example of an SQL UPDATE statement
 UPDATE Customers
 SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
 WHERE CustomerID = 1;

 Deleting records from a table.

 Example of an SQL DELETE statement
 DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
Instance Modifications: These refer to changes to the database instance itself, such as
configuration changes or resource allocation.