RELATIONAL QUERY LANGUAGES

Prepared by: Kutale T. (MSc.)

January 2025
 Introduction
 Query languages: Allow manipulation and retrieval of data from a
database.

 There are varieties of Query languages used by relational DBMS for

manipulating relations.
 Procedural: user tells the system exactly what and how to manipulate the data

 Non-procedural: user states what data is needed rather than how it is to be

retrieved.

 Two mathematical Query Languages form the basis for Relational

languages: 1) Relational Algebra

2) Relational Calculus
1/20/2025 2
 6.1. Relational Algebra
 We may describe the relational algebra as procedural language: it
can be used to tell the DBMS how to build a new relation from one
or more relations in the database.
 The basic set of operations for the relational model is known as the
relational algebra. These operations enable a user to specify basic
retrieval requests.
 The result of the retrieval is a new relation, which may have been
formed from one or more relations.
 The algebra operations thus produce new relations, which can be
further manipulated using operations of the same algebra.
1/20/2025 3
 Continued..
 Relational algebra is a theoretical language with operations that
work on one or more relations to define another relation without
changing the original relation.

 The output from one operation can become the input to another
operation (nesting is possible).

1/20/2025 4
 There are different basic operations that could be applied on
relations on a database based on the requirement.
 Selection (σ): Selects a subset of rows from a relation.
 Projection (π): Deletes unwanted columns from a relation.
 Renaming: assigning intermediate relation for a single operation
 Join ( ): Tuples joined from two relations based on a condition
 Set operations: include UNION, INTERSECTION, SET DIFFERENCE,
and CARTESIAN PRODUCT (also known as CROSS PRODUCT).
 Cross-Product (x): Allows us to combine two relations.
 Set-Difference (-): Tuples in relation1, but not in relation2.
 Union (𝖴): Tuples in relation1 or in relation2.
 Intersection (∩): Tuples in relation1 and in relation2
1/20/2025 5
 Selection (σ) operation
 It selects subset of tuples/rows in a relation that satisfy selection
condition.
 It is a unary operator (it is applied to a single relation it applied to
each tuple individually)
 The degree of the resulting relation is the same as the original relation
but the cardinality (no. of tuples) is >= to the original relation.
 The Selection operator is commutative.
 Set of conditions can be combined using Boolean operations (𝖠
(AND), ∨(OR), and ~(NOT)).
 No duplicates in result!
1/20/2025 6
 Continued..
 Schema of result identical to schema of (only) input relation.
 Result relation can be the input for another relational algebra
operation! (Operator composition.)
 It is a filter that keeps only those tuples that satisfy a qualifying
condition (those satisfying the condition are selected while others are
discarded.)
 Notation:
<Selection Condition>
<Relation Name>

1/20/2025 7
 Example: The relation contains information about employees, IT skills
they have and the school where they attend each skill.

Employee
EmpID FName LName SkillID Skill SkillType School SchoolAdd SkillLevel
12 Abebe Mekuria 2 SQL Database AAU Sidist_Kilo 5

16 Lemma Alemu 5 C++ Programming Unity Gerji 6

28 Chane Kebede 2 SQL Database AAU Sidist_Kilo 10

25 Abera Taye 6 VB6 Programming Helico Piazza 8

65 Almaz Belay 2 SQL Database Helico Piazza 9

24 Dereje Tamiru 8 Oracle Database Unity Gerji 5

51 Selam Belay 4 Prolog Programming Jimma Jimma City 8

94 Alem Kebede 3 Cisco Networking AAU Sidist_Kilo 7

18 Girma Dereje 1 IP Programming Jimma Jimma City 4

13 Yared Gizaw 7 Java Programming AAU Sidist_Kilo 6

1/20/2025 8
 Continued..
 Example: Find all Employees with skill type of Database.

< SkillType =”Database”> (Employee)

 This query will extract every tuple from a relation called Employee
with all the attributes where the SkillType attribute with a value of
“Database”.

 The resulting relation will be the following.

EmpID FName LName SkillID Skill SkillType School SchoolAdd SkillLevel
12 Abebe Mekuria 2 SQL Database AAU Sidist_Kilo 5
28 Chane Kebede 2 SQL Database AAU Sidist_Kilo 10
65 Almaz Belay 2 SQL Database Helico Piazza 9
24 Dereje Tamiru 8 Oracle Database Unity Gerji 5
1/20/2025 9
 Projection (π) operation
 It selects certain attributes while discarding the other from the base
relation.

 The PROJECT creates a vertical partitioning (one with the needed

columns (attributes) containing results of the operation and other
containing the discarded Columns).

 Deletes attributes that are not in projection list.

 Schema of result contains exactly the fields in the projection list, with
the same names that they had in the (only) input relation.

 Projection operator has to eliminate duplicates!

1/20/2025 10
 Continued..
 If the Primary Key is in the projection list, then duplication will not
occur. Duplication removal is necessary to insure that the resulting
table is also a relation.
 Notation:
π <Selected Attributes> <Relation Name>
 Example: To display Name, Skill, and Skill Level of an employee,
the query and the resulting relation will be:
π <FName, LName, Skill, Skill_Level> (Employee)
SQL Equivalent: SELECT DISTINCT Name, Department FROM Employee;
Note: DISTINCT ensures duplicate rows are removed.

1/20/2025 11
 Rename Operation
 We may want to apply several relational algebra operations one after
the other. The query could be written in two different forms:

a) Write the operations as a single relational algebra expression by

nesting the operations.

b) Apply one operation at a time and create intermediate result

relations. In the latter case, we must give names to the relations
that hold the intermediate results Rename Operation

 Example: If we want to have the Name, Skill, and Skill Level of an

employee with salary greater than 1500 and working for department 5,
we can write the expression for this query using the two alternatives:
1/20/2025 12
 Continued..
1. A single algebraic expression:

π< (
FName, LName, Skill, Skill_Level> <Skill=”SQL” SkillLevel>5>
𝖠
(Employee))
2. Using an intermediate relation by the Rename Operation:

Step1: Result <DeptNo=5 𝖠 Salary>1500>

(Employee)

Step2: Result π <FName, LName, Skill, Skill_Level

(Result1)>
 Then Result will be equivalent with the relation we get using the first
alternative.
1/20/2025 13
 The UNION, INTERSECTION, and MINUS
Operations
 These are binary operations; that is, each is applied to two sets (of tuples).

 When these operations are adapted to relational databases, the two relations
on which any of these three operations are applied must have the same type
of tuples; this condition has been called union compatibility or type
compatibility.

 Two relations R(A1, A2, ..., An) and S(B1, B2, ..., Bn) are said to be union
compatible (or type compatible) if they have the same degree n and if
dom(Ai) = dom(Bi) for 1 ≤ i ≤ n. This means that the two relations have the
same number of attributes and each corresponding pair of attributes has the
same domain.
1/20/2025 14
 UNION Operation
 The result of this operation, denoted by R U S, is a relation that
includes all tuples that are either in R or in S or in both R and S.

 Duplicate tuples are eliminated.

1/20/2025 15
 INTERSECTION and MINUS Operations
 INTERSECTION Operation

 The result of this operation, denoted by R ∩ S, is a relation that

includes all tuples that are in both R and S.

 The two operands must be "type compatible"

 Set Difference (or MINUS) Operation

 The result of this operation, denoted by R - S, is a relation that

includes all tuples that are in R but not in S.

 The two operands must be "type compatible”.

1/20/2025 16
 Continued..
Some Properties of the Set Operators

 Both union and intersection are commutative operations;

 i.e. R 𝖴 S = S 𝖴 R, and R ∩ S = S ∩ R

 Both union and intersection can be treated as n-nary operations

applicable to any number of relations as both are associative
operations;

 R 𝖴 (S 𝖴 T) = (R 𝖴 S) 𝖴 T, and (R ∩ S) ∩ T = R ∩ (S ∩ T)

 The minus operation is not commutative; that is, R - S ≠ S – R

1/20/2025 17
 Continued..
Example: The set operations UNION, INTERSECTION, and MINUS.
(a) Two union-compatible relations (b) STUDENT ∪ INSTRUCTOR

1/20/2025 18
 Continued..
(c) STUDENT ∩ INSTRUCTOR
(d) STUDENT − INSTRUCTOR
(e) INSTRUCTOR − STUDENT

1/20/2025 19
 The CARTESIAN PRODUCT (CROSS PRODUCT)
Operation
 This is also a binary set operation, but the relations on which it is
applied do not have to be union compatible.

 In its binary form, this set operation produces a new element by

combining every member (tuple) from one relation (set) with every
member (tuple) from the other relation (set).

 In general, the result of R(A1, A2, ..., An) × S(B1, B2, ..., Bm) is a
relation Q with degree n + m attributes Q(A1, A2, ..., An, B1, B2, ...,
Bm), in that order.

 The resulting relation Q has one tuple for each combination of tuples
one from R and one from S.
1/20/2025 20
 Continued..
 Hence, if R has nR tuples (denoted as |R| = nR), and S has nS tuples,
then R × S will have nR * nS tuples.

 Example: suppose that we want to retrieve a list of names of each

female employee‟s dependents. We can do this as follows:

 FEMALE_EMPS ← σSex=„F‟(EMPLOYEE)

 EMPNAMES ← πFname, Lname, Ssn(FEMALE_EMPS)

 EMP_DEPENDENTS ← EMPNAMES × DEPENDENT

 ACTUAL_DEPENDENTS ← σSsn=Essn(EMP_DEPENDENTS)

 RESULT←πFname,Lname,Dependent_name(ACTUAL_DEPENDENTS)
1/20/2025 21
 Continued..

1/20/2025 22
 Continued..

1/20/2025 23
 Continued..

1/20/2025 24
 Join ( ) operation
 It is used to combine related tuples from two relations into single
“longer” tuples.
 This operation is very important for any relational database with more
than a single relation because it allows us to process relationships
among relations.
 Tuples joined from two relations based on a condition.

 Read more about types of Join operation (SQL Join)

1/20/2025 25
 Continued..
 To illustrate JOIN, suppose that we want to retrieve the name of the
manager of each department.
 To get the manager’s name, we need to combine each department
tuple with the employee tuple whose Ssn value matches the Mgr_ssn
value in the department tuple.
 We do this by using the JOIN operation and then projecting the result
over the necessary attributes, as follows:

1/20/2025 26
 Continued..

1/20/2025 27
 6.2. Relational Calculus
 It is a non-procedural query language in which user states what data
is needed rather than how it is to be retrieved.

 A relational calculus expression creates a new relation, which is

specified in terms of variables that range over rows of the stored
database relations (in tuple calculus) or over columns of the stored
relations (in domain calculus).

 In a calculus expression, there is no order of operations to specify how

to retrieve the query result.

 A calculus expression specifies only what information the result

should
1/20/2025
contain rather than how to retrieve it. 28
 Continued..
 In Relational calculus, there is no description of how to evaluate a query

 In relational calculus, we can write a sequence of operations to specify

a retrieval request.

 When applied to relational database, the calculus is not that of

derivative and differential but in a form of first-order logic or
predicate calculus, a predicate is a truth-valued function with
arguments.

 When we substitute values for the arguments in the predicate, the

function yields an expression, called a proposition, which can be
either
1/20/2025 true or false. 29
 Continued..
 If COND is a predicate, then the set of all tuples evaluated to be true
for the predicate COND will be expressed as follows:

 {t | COND(t)}, Where: t is a tuple variable and COND (t) is a

conditional expression involving t.

 The result of such a query is the set of all tuples t that satisfy COND (t).

 The predicates can be connected using the Boolean operators:

 𝖠 (AND), ∨ (OR), ∼ (NOT)

1/20/2025 30
 Continued..
 COND(t) is a formula, and is called a Well-Formed-Formula (WFF)
if: the COND is composed of n-nary predicates (formula composed of
n single predicates) and the predicates are connected by any of the
Boolean operators.

 And each predicate is of the form A θ B and θ is one of the logical

operators{ <, ≤ , >, ≥, ≠, = }which could be evaluated to either true or
false. Where A and B are either constant or variables.

 Formulae should be unambiguous and should make sense.

1/20/2025 31
 Continued..
 Types of Relational Calculus:

 Tuple Relational Calculus (TRC): Uses tuple variables to represent rows in a

table.

 Example: Retrieve the names of employees with a salary greater than 1500:

 {𝑡.Name∣𝑡 ∈𝐸𝑚𝑝𝑙𝑜𝑦𝑒𝑒∧𝑡.Salary>1500}{[Link] ∣ t∈Employee∧[Link]>1500}

 DomainRelational Calculus (DRC): Uses domain variables to represent

column values.

 Example: Retrieve the names of employees with a salary greater than 1500:

 {Name∣(Name,Salary)∈𝐸𝑚𝑝𝑙𝑜𝑦𝑒𝑒
∧Salary>1500}{Name ∣ (Name,Salary)∈Employee∧Salary>1500}
1/20/2025 32
 Continued..
 Example: Extract all employees whose skill level is greater than or
equal to 8.

1/20/2025 33
 Continued..
 Example: To find only the EmpId, FName, LName, Skill and the
School where the skill is attended where of employees with skill level
greater than or equal to 8, the tuple based relational calculus
expression will be:

1/20/2025 34
 Quantifiers in Relation Calculus
 To tell how many instances the predicate applies to, we can use the two
quantifiers in the predicate logic.

 One relational calculus expressed using Existential Quantifier can also be

expressed using Universal Quantifier.

1) Existential quantifier ∃ (‘there exists’): is used in formulae that must

be true for at least one instance, such as:

 An employee with skill level greater than or equal to 8 will be:

 {E | Employee(E) 𝖠 (∃E)([Link] >= 8)}. This means, there

exist at least one tuple of the relation employee where the value for
the SkillLevel is greater than or equal to 8
1/20/2025 35
 Continued..
2) Universal quantifier ∀ (‘for all’): is used in statements about
every instance, such as:

 An employee with skill level greater than or equal to 8 will be:

 {E | Employee(E) 𝖠 (∀E)([Link] >= 8)}. This means, for

all tuples of relation employee where value for the SkillLevel
attribute is greater than or equal to 8.

1/20/2025 36
 Continued..
 Example: Let‟s say that we have the following Schema (set of
Relations):

 Employee(EID, FName, LName, Dept) Project(PID, PName,

Dept)

 Dept(DID, DName, DMangID) WorksOn(EID, PID)

 To find employees who work on projects controlled by department 5

the query will be:

 {E | Employee(E) 𝖠 (∀x)(Project(x) 𝖠 (∃w)(WorksOn(w) 𝖠

[Link]=5 𝖠 [Link]=[Link]))}
1/20/2025 37
 6.3. STRUCTURED QUERY LANGUAGE (SQL)

 Structured Query Language, commonly abbreviated to SQL and

pronounced as “sequel”, is not a conventional computer programming
language in the normal sense of the phrase.

 It allows users to access data in relational database management systems

(RDBMS).

 SQL is about data and results, each SQL statement returns a result,
whether that result be a query, an update to a record or the creation of a
database table.

 SQL is most often used to address a relational database, which is what

1/20/2025 38
some people refer to as a SQL database.
 Continued..
 So, in brief we can describe SQL as follows:

 SQL allows you to access a database

 SQL can execute queries against a database

 SQL can retrieve data from a database

 SQL can insert new records in a database

 SQL can delete records from a database

 SQL can update records in a database

1/20/2025 39
 SQL Commands
 There are four main groups of commands in SQL:

1. Data Definition language (DDL)

2. Data Manipulation language (DML)

3. Transaction control language (TCL)

4. Data control language (DCL).

1/20/2025 40
 Data Definition Language (DDL) in SQL

 The DDL part of SQL permits database tables to be created or deleted.

We can also define indexes (keys), specify links between tables, and
impose constraints between database tables.

 These commands will primarily be used by database administrators

during the setup and removal phases of a database project.

 The most important DDL statements in SQL are:

 CREATE TABLE - creates a new database or object (e.g. table.)

 ALTER TABLE - modify the structure of existing table.

 DROP TABLE - deletes a database table

41
 TRUNCATE: removes all the records from the table
 Continued..
1. CREATE

 CREATE TABLE: This is used to create a new relation (table)

 Syntax: CREATE TABLE <relation_name/table_name > (field_1

data_type(size),field_2 data_type(size), .. . );

 Example: SQL> CREATE TABLE Student (sno NUMBER (3),

sname CHAR (10), class CHAR (5));

1/20/2025 42
 Continued..
2. ALTER

a) ALTER TABLE ...ADD...: This is used to add some extra fields into
existing relation.

 Syntax: ALTER TABLE relation_name ADD (new field_1

data_type(size), new field_2 data_type(size),..);

 Example: SQL>ALTER TABLE std ADD (Address CHAR(10));

b) ALTER TABLE...MODIFY...: This is used to change the width as

well as data type of fields of existing relations.

 Syntax: ALTER TABLE relation_name MODIFY (field_1 newdata_type(Size),

field_2 newdata_type(Size),....field_newdata_type(Size));
1/20/2025 43
 Continued..
 Example: SQL>ALTER TABLE student MODIFY(sname
VARCHAR(10),class VARCHAR(5));

c) ALTER TABLE..DROP...: This is used to remove any field of

existing relations.

 Syntax: ALTER TABLE relation_name DROP COLUMN

(field_name);

 Example: SQL>ALTER TABLE student DROP column (sname);

1/20/2025 44
 Continued..
d) TABLE..RENAME...: This is used to change the name of fields in

existing relations.

 Syntax: ALTER TABLE relation_name RENAME COLUMN

(OLD field_name) to (NEW field_name);

 Example: SQL>ALTER TABLE student RENAME COLUMN

sname to stu_name;

1/20/2025 45
 Continued..
3. DROP TABLE: This is used to delete the structure of a relation. It
permanently deletes the records in the table.

 Syntax: DROP TABLE relation_name;

 Example: SQL>DROP TABLE std;

4. TRUNCATE: This command will remove the data permanently. But

structure will not be removed. Truncate is a DDL command & delete
is a DML command.

1/20/2025 46
 Data Manipulation Language in SQL (DML)
 The DML is used to retrieve, insert and modify database information.
These commands will be used by all database users during the routine
operation of the database.

 Some of the basic of DML commands:

1) INSERT INTO - inserts new data into a database table

2) UPDATE - updates data in a database table

3) DELETE - deletes data from a database table

4) SELECT - extracts data from a database table

1/20/2025 47
 Continued..
1) INSERT INTO: This is used to add/insert records into a relation.
These are three type of INSERT INTO queries which are as:

a) Inserting a single record

 Syntax: INSERT INTO <relation/table name>

(field_1,field_2……field_n)VALUES (data_1,data_2,........data_n);

 Example: SQL>INSERT INTO student(sno,sname,class,address)VALUES

(1,‟Ravi‟,‟[Link]‟,‟Palakol‟);

1/20/2025 48
 Continued..
a) Inserting multiple records

 Syntax: INSERT INTO relation_name field_1,field_2,.....field_n)

VALUES (&data_1,&data_2,........&data_n);

 Example: SQL>INSERT INTO student (sno, sname,

class,address) VALUES (&sno,‟&sname‟,‟&class‟,‟&address‟);

Enter value for sno: 101

Enter value for name: Ravi

Enter value for class: [Link]

1/20/2025
Enter value for adress: Palakol 49
 Continued..
2) UPDATE-SET-WHERE: This is used to update the content of a
record in a relation.

 Syntax: SQL>UPDATE relation name SET

Field_name1=data,field_name2=data, WHERE field_name=data;

 Example: SQL>UPDATE student SET sname = „kumar‟ WHERE sno=1;

1/20/2025 50
 Continued..
3) DELETE-FROM: This is used to delete all the records of a relation
but it will retain the structure of that relation.

a) DELETE-FROM: This is used to delete all the records of relation.

 Syntax: SQL>DELETE FROM relation_name;

 Example: SQL>DELETE FROM std;

b) DELETE -FROM-WHERE: This is used to delete a selected record

from a relation.

 Syntax: SQL>DELETE FROM relation_name WHERE condition;

 Example: SQL>DELETE FROM student WHERE sno = 2;

1/20/2025 51
 Continued..
4) SELECT FROM: This command extracts/retrive data from a
database table.

 To display all fields for all records.

 Syntax: SELECT * FROM relation_name;

 Example: SQL> select * from dept;

1/20/2025 52
 Continued..
 To display a set of fields for all records of relation.

 Syntax: SELECT a set of fields FROM relation_name;

 Example: SQL> select deptno, dname from dept;

1/20/2025 53
 Continued..
 SELECT - FROM -WHERE: This query is used to display a
selected set of fields for a selected set of records of a relation.

 Syntax: SELECT a set of fields FROM relation_name WHERE

condition;

 Example: SQL> select * FROM dept WHERE deptno<=20;

1/20/2025 54
 Continued..
3) Data Control Language (DCL): Database is a shared resource that
demands control of data access and usage. DCL is used to control
access to data in the database.
 DCLs are commands that will help the DBA to control the database.
 The commands include GRANT or REVOKE privileges to access the
database or particular object within the database and to store or
remove database transactions.
 GRANT: grant permissions to the user for running DML (SELECT,
INSERT, DELETE) commands on the table
 REVOKE: revoke/removes permissions to the user for running DML
(SELECT, INSERT, DELETE) command on the specified table. 55
 Continued..
4) Transactional Control Language (TCL)
 Acts as a manager for all types of transactional data and all
transactions.
 Some of the command of TCL are:

ROLE BACK: Used to cancel or Undo changes made in

the database
COMMIT: It is used to apply or save changes in the
database
SAVE POINT: It is used to save the data on the
56
temporary basis in the database.
 Relational Algebra and Calculus are theoretical tools for understanding, designing,
and optimizing relational databases.
 SQL is the practical implementation of these concepts, allowing users to interact
57
with databases efficiently.
58