#### Table of Contents

Languages for describing queries on a relational database –

**Structured Query Language**

Structured Query Language (SQL) is a query language that allows performing some operations on data/query such as create, edit, modify/change, delete, etc.,

**Relational Algebra**

Relational Algebra is a theoretical language used for converting SQL queries into relational algebra form by using operations & operators defined under relational algebra.

In other words, relational algebra is a theoretical language with operators applied to one or two relations to produce another.

Points to remember:

- A procedural language
- Not implemented in native forms in DBMS
- Basis for other HL DMLs

**Types of Relational Algebra Operation**

Relational Operations are divided into three groups:

**Unary Relational Operations**

- SELECT
- PROJECT
- RENAME

**Relational Algebra Operations from Set Theory**

- UNION
- INTERSECTION
- DIFFERENCE
- CARTESIAN PRODUCT

**Binary Relational Operations**

- JOIN
- DIVISION

**Explanations of:**

**SELECT**

** **The SELECT command is applied to the single table and takes queries from rows that meet conditions copying them into a new table.

**Syntax:**

` ````
```SELECT Column name
FROM Table name
WHERE Condition

**Symbolic form:**

` ````
```σ Age=20 (Student)

σ : σ is the symbolic representation of command SELECT.

**Explanation:**

Table name: Student

Column name: Age

Condition: 20

Select the column named Age from table Student where condition = 20.

**PROJECT**

The PROJECT command is applied to the single table and takes a query from columns, extracts the value from the table in vertical form, eliminates duplicate values, and places them into a new table.

**Syntax:**

` ````
```PROJECT tablename OVER (column name,....., column name)

**Symbolic form:**

` ````
```π Name (Student)

**Combining SELECT and PROJECT**

` ````
```SELECT Student Where name = 'John'
PROJECT Name OVER(lastname, firstname)
π last name, firstname ( name = 'John'(Student))

**JOIN**

The JOIN operation is a combination of the SELECT and PRODUCT and returns possible projection operations.

The JOIN of two relations, say A and B, operates as follows:

- First form the product of A times B.
- Then make a selection to eliminate some tuples (criteria for the selection are specified as part of the join)

- Then (optionally) remove some attributes by means of projection.

**NATURAL JOIN**

NATURAL JOIN is an equijoin in which the repeated column is eliminated.

- This is the most common form of the join operation and is usually what is meant by JOIN

**Syntax:**

` ````
```tableName1 JOIN tableName2 [GIVING newTableName]

**Operators**

Operators are the same as mathematical operators and are always used in difficult relation algebra conditions.

- <, <=,
- >, >=,
- =, ,
- AND,
- OR,
- NOT

**Set Operations on Relations**

- CARTESIAN PRODUCT
- UNION
- INTERSECTION
- DIFFERENCE

**Relation Operations **

- Cartesian Product
- The cartesian product of two relations is the concatenation of every tuple of one relation with every tuple of second relations.
- The Cartesian product of relation A (having m tuples) and relation B (having n tuples) has m times n tuples.
- The Cartesian product is denoted A X B or A TIMES B.

Though converting a high-level language (SQL) to low-level relational algebra is challenging, we can define the concept logically and with a few examples. To overcome ambiguities, the relation symbols in a SQL statement are assigned a specific name through the alias (SQL aliases are used to give a table or a column in a table a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of the query.) mechanism of SQL.

SQL statements, where a relation symbol occurs multiple times, for example,

SELECT * FROM R,

R is rewritten into a SQL statement of the form

SELECT * FROM R, R R1

Here every occurrence is given a distinct (alias) name. Let us study two occurrences.

1. Select-from-where statements without sub-queries

Consider a general SELECT-FROM-WHERE statement of the form

` ````
```SELECT Select-list
FROM R1, . . . , R2 T1, . . .
WHERE (Where-condition)

Since, here query does not use sub queries in where-condition then it can be translated into the relational algebra as follows:

` ````
```π Select-list σWhere-condition(R1 X ……..X ρT1(R2) _ _ _ _ )

**Note:** Here an alias R2 T1 in the FROM-clause corresponds to a renaming ρT1(R2).

If there is no WHERE clause then there is no need to include the selection σ in the expression.

For omitting the projection (π) we obtain the translation of the following special case:

` ````
```SELECT *
FROM R1, . . . , R2 T1, . . .
WHERE Where-condition

E.g.: SQL SELECT-FROM-WHERE statement is

` ````
```SELECT Cinema_Name
FROM Actors_In, Cinema_Actor
WHERE Actor_Name = name AND date of birth = 1990

Translating relational algebra like

` ````
```πCinema_NameσActor_Name=name (Actors_In X Cinema_Actor):
^ date of birth =1990

Example with Sub queries: The SQL query is

` ````
```SELECT LastName, FirstName
FROM EMPLOYEE
WHERE Salary > (SELECT MAX (Salary)
FROM EMPLOYEE
WHERE IdNo = 2);

It can be split into the following sub-queries like

` ````
```SELECT LastName, FirstName
FROM EMPLOYEE
WHERE Salary > 10000

Respective R.A expression:

` ````
```πLastName, FirstName(σ Salary>10000(EMPLOYEE))

` ````
```SELECT MAX (Salary)
FROM EMPLOYEE
WHERE IdNo = 2

Respective R.A expression:

` ````
```MAX Salary (σIdNo = 2 (EMPLOYEE))

**Translating Joins**

` ````
```(SELECT * FROM R R1) JOIN (SELECT * FROM R R1) ON R1.A = R2.B
ρR1(R) R1.A= R2.B R2(R)

**Group and Having**

` ````
```SELECT Select-list
FROM From-list
WHERE Where-condition
GROUP BY Group-list
HAVING Having-condition

` ````
```πA1;:::;An; Select-listσ Having-condition ϒ A1;:::;An; Group-list;Agg-list(E):

E.g.: **SELECT** name, **SUM**(length)

` ````
```FROM Cinema Exce, Cinema
WHERE cert = SeniorProducer
GROUP BY name
HAVING MIN(year) <1960

**Relational Algebra**

` ````
```π name;SUM(length) σMIN(year)<1960ϒname;MIN(year);SUM(length)
σ cert=SeniorProducer(CinemaExecx Cinema)