What is Relational Algebra?


Relational algebra consists of a certain set of rules or operations that are widely
used to manipulate and query data from a relational database. It can be facilitated
by utilizing SQL language and helps users interact with database tables based on
querying data from the database more efficiently and effectively.
Relational Algebra incorporates a collection of operations, such as filtering data
or combining data, that help us organize and manipulate data more efficiently.
This ” algebra ” is the foundation for most database queries, and it enables us to
extract the required information from the databases by using SQL query
language.
Fundamental Operators
Relational algebra consists of various operators that help us fetch and manipulate
data from relational tables in the database to perform certain operations on
relational data.
These are the basic/fundamental operators used in Relational Algebra
1. Selection(σ)
2. Projection(π)
3. Union(U)
4. Set Difference(-)
5. Set Intersection( )
6. Rename(ρ)
7. Cartesian Product(X)
8.
1. Selection(σ) : Selection Operation is basically used to filter out rows from a
given table based on certain given condition. It basically allows you to retrieve
only those rows that match the condition as per condition passed during SQL
Query.
It is used to select required tuples of the relations.
Illustration : If we want to get the details of all the work in the Department
“IT”, we would use the selection operator to filter out these based on the given
condition.
Example:
A B C

1 2 4
A B C

2 2 3

3 2 3

4 3 4

Output Table :
For the above relation, σ(c>3)R will select the tuples which have c more than 3.

A B C

1 2 4

4 3 4

Note: The selection operator only selects the required tuples but does not display them.
For display, the data projection operator is used.

2. Projection(π) : While Selection operation works on rows , similarly projection

operation of relational algebra works on columns. It basically allows you to pick
specific columns from a given relational table based on the given condition and
ignoring all the other remaining columns.
Illustration : If we are interested in the specific columns from the relational tables, we
would prefer to use the Projection Operator.
It is used to project required column data from a relation.
Example: Consider Table 1. Suppose we want columns B and C from Relation R.
π(B,C)R will show following columns.

B C
B C

2 4

2 3

3 4

Note: By Default, projection removes duplicate data.

3. Union(U) : Union Operator is basically used to combine the results of two queries
into a single result. The only condition is that both queries must return same number of
columns with same data types.
Illustration : If in case we want a list of all the employee from two different
department . Then in that case we should use union operation to merge both the list
from two different department.
Union operation in relational algebra is the same as union operation in set theory.
Example: FRENCH

Student_Name Roll_Number

Ram 01

Mohan 02

Vivek 13

Geeta 17

GERMAN
Student_Name Roll_Number

Vivek 13

Geeta 17

Shyam 21

Rohan 25

Consider the following table of Students having different optional subjects in their
course.
π(Student_Name)FRENCH U π(Student_Name)GERMAN

Student_Name

Ram

Mohan

Vivek

Geeta

Shyam
Student_Name

Rohan

Note: The only constraint in the union of two relations is that both relations must have
the same set of Attributes.

4. Set Difference(-) : Set difference basically provides the rows that are present in one
table , but not in another tables.
Illustration : If you have two lists of employees, one from the IT Department and one
from the Marketing department & you want to know which employees are in IT but not
in Marketing , the set difference operator would used.
Set Difference in relational algebra is the same set difference operation as in set
theory.
Example: From the above table of FRENCH and GERMAN, Set Difference is used as
follows
π(Student_Name)FRENCH - π(Student_Name)GERMAN

Student_Name

Ram

Mohan

Note: The only constraint in the Set Difference between two relations is that both
relations must have the same set of Attributes.

5. Set Intersection( ) : Set Intersection basically allows to fetches only those rows of
data that are common between two sets of relational tables.
Illustration : If we want to find the number of employees who work in both IT and
Marketing Department , then in that case we use Intersection Operator.
Set Intersection in relational algebra is the same set intersection operation in set theory.
Example: From the above table of FRENCH and GERMAN, the Set Intersection is
used as follows:
π(Student_Name)FRENCH π(Student_Name)GERMAN
Student_Name

Vivek

Geeta

Note: The only constraint in the Set Difference between two relations is that both
relations must have the same set of Attributes.
6. Rename(ρ) : Rename operator basically allows you to give a temporary name to a
specific relational table or to its columns. It is very useful when we want to avoid
ambiguity, especially in complex Queries.
Rename is a unary operation used for renaming attributes of a relation.
ρ(a/b)R will rename the attribute 'b' of the relation by 'a'.

7. Cross Product(X) : Cartesian product Operator combines every row of one table
with every row of another table , producing all the possible combination. It’s mostly
used as a precursor to more complex operation like joins.
Cross-product between two relations. Let’s say A and B, so the cross product between
A X B will result in all the attributes of A followed by each attribute of B. Each record
of A will pair with every record of B.
Example:
A

Name Age Sex

Ram 14 M

Sona 15 F

Kim 20 M

B
ID Course

1 DS

2 DBMS

A X B

Name Age Sex ID Course

Ram 14 M 1 DS

Ram 14 M 2 DBMS

Sona 15 F 1 DS

Sona 15 F 2 DBMS

Kim 20 1 DS

M

Kim 20 2 DBMS

M
 Relational Calculus
There is an alternate way of formulating queries known as Relational Calculus.
Relational calculus is a non-procedural query language. In the non-procedural
query language, the user is concerned with the details of how to obtain the end
results. The relational calculus tells what to do but never explains how to do. Most
commercial relational languages are based on aspects of relational calculus
including SQL-QBE and QUEL

Types of Relational calculus:

1. Tuple Relational Calculus (TRC)

It is a non-procedural query language which is based on finding a number of tuple
variables also known as range variable for which predicate holds true. It describes
the desired information without giving a specific procedure for obtaining that
information. The tuple relational calculus is specified to select the tuples in a
relation. In TRC, filtering variable uses the tuples of a relation. The result of the
relation can have one or more tuples.

Notation:

A Query in the tuple relational calculus is expressed as following notation

1. {T | P (T)} or {T | Condition (T)}

Where
T is the resulting tuples

P(T) is the condition used to fetch T.

For example:

{ [Link] | Author(T) AND [Link] = 'database' }

Output: This query selects the tuples from the AUTHOR relation. It returns
a tuple with 'name' from Author who has written an article on 'database'.

TRC (tuple relation calculus) can be quantified. In TRC, we can use

Existential (! ) and Universal Quantifiers (∀).

For example:

{ R| ! T # Authors([Link]='database' AND [Link]=[Link])}

Output: This query will yield the same result as the previous one.

2. Domain Relational Calculus (DRC)

The second form of relation is known as Domain relational calculus. In
domain relational calculus, filtering variable uses the domain of attributes.
Domain relational calculus uses the same operators as tuple calculus. It
uses logical connectives ∃ (and), %(or) and (not). It uses Existential (! )
and Universal Quantifiers (∀) to bind the variable. The QBE or Query by
example is a query language related to domain relational calculus.

Notation:

{ a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}

Where

a1, a2 are attributes

P stands for formula built by inner attributes

For example:

{< article, page, subject > | # javatpoint ∃ subject = 'database'}

Output: This query will yield the article, page, and subject from the
relational javatpoint, where the subject is a database.
Difference Between Relational Algebra and
Relational Calculus
Basis of Relational
Comparison Algebra Relational Calculus

Relational Calculus is a
It is a Procedural
Language Type Declarative (non-procedural)
language.
language.

Relational Calculus means

Relational Algebra means
Procedure what result we have to
how to obtain the result.
obtain.

In Relational Algebra, the

order is specified in which In Relational Calculus, the
Order
the operations have to be order is not specified.
performed.

Relational Algebra is Relation Calculus can be

Domain independent of the domain-dependent because
domain. of domain relational calculus.

Relational Calculus is not

Relational Algebra is
Programming nearer to programming
nearer to a programming
language language but to natural
language.
language.
Basis of Relational
Comparison Algebra Relational Calculus

The SQL includes only SQL is based to a greater extent

Inclusion in SQL some features from the on the tuple relational
relational algebra. calculus.

Relational Algebra is one

of the languages in which For a database language to be
queries can be expressed relationally complete, the query
Relationally written in it must be expressible
but the queries should
completeness
also be expressed in in relational
relational calculus to be calculus.
relationally complete.

The evaluation of the

The order of operations does
query relies on the order
Query not matter in relational
specification in which the
Evaluation calculus for the evaluation of
operations must be
queries.
performed.

For accessing the

database, relational For accessing the database,
algebra provides a relational calculus provides a
Database solution in terms of what solution in terms as simple
access is required and how to as what is required and lets
get that information by the system find the solution
following a step-by-step for that.
description.
Basis of Relational
Comparison Algebra Relational Calculus

The completeness of a
language is measured in the
The expressiveness of manner that it is least as
any given language is powerful as calculus. That
Expressiveness judged using relational implies relation defined using
algebra operations as a some expression of the
standard. calculus is also definable by
some other expression, the
language is in question.
Sql constraints
SQL constraints are essential elements in relational database design that ensure
the integrity, accuracy, and reliability of the data stored in a database. By enforcing
specific rules on table columns, SQL constraints help maintain data consistency,
preventing invalid data entries and optimizing query performance.
In this article, we will explain the most common SQL constraints in detail, providing
clear examples and explaining how to implement them effectively.
What Are SQL Constraints?
SQL constraints are rules applied to columns or tables in a relational database to
limit the type of data that can be inserted, updated, or deleted. These rules ensure the
data is valid, consistent, and adheres to the business logic or database requirements.
Constraints can be enforced during table creation or later using the ALTER
TABLE statement. They play a vital role in maintaining the quality and integrity of
your database.

Types of SQL Constraints

SQL provides several types of constraints to manage different aspects of data integrity.
These constraints are essential for ensuring that data meets the requirements
of accuracy, consistency, and validity. Let’s go through each of them with detailed
explanations and examples.

1. NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot contain NULL values. This is
particularly important for columns where a value is essential for identifying records or
performing calculations. If a column is defined as NOT NULL, every row must
include a value for that column.
Example:
CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
ADDRESS varchar(20)
);
Explanation: In the above example, both the ID and NAME columns are defined with
the NOT NULL constraint, meaning every student must have an ID and NAME value.

2. UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column are distinct across all rows
in a table. Unlike the PRIMARY KEY, which requires uniqueness and does not allow
NULLs, the UNIQUE constraint allows NULL values but still enforces uniqueness for
non-NULL entries.
Example:
CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20)
);
Explanation: Here, the ID column must have unique values, ensuring that no two
students can share the same ID. We can have more than one UNIQUE constraint in a
table.

3. PRIMARY KEY Constraint

A PRIMARY KEY constraint is a combination of the NOT
NULL and UNIQUE constraints. It uniquely identifies each row in a table. A table can
only have one PRIMARY KEY, and it cannot accept NULL values. This is typically
used for the column that will serve as the identifier of records.
Example:
CREATE TABLE Student
(
ID int(6) NOT NULL UNIQUE,
NAME varchar(10),
ADDRESS varchar(20),
PRIMARY KEY(ID)
);
Explanation: In this case, the ID column is set as the primary key, ensuring that each
student’s ID is unique and cannot be NULL.

4. FOREIGN KEY Constraint

A FOREIGN KEY constraint links a column in one table to the primary key in
another table. This relationship helps maintain referential integrity by ensuring that
the value in the foreign key column matches a valid record in the referenced table.
Orders Table:

O_ID ORDER_NO C_ID

1 2253 3

2 3325 3
O_ID ORDER_NO C_ID

3 4521 2

4 8532 1

Customers Table:

C_ID NAME ADDRESS

1 RAMESH DELHI

2 SURESH NOIDA

3 DHARMESH GURGAON

As we can see clearly that the field C_ID in Orders table is the primary key in
Customers table, i.e. it uniquely identifies each row in the Customers table. Therefore,
it is a Foreign Key in Orders table.
Example:
CREATE TABLE Orders
(
O_ID int NOT NULL,
ORDER_NO int NOT NULL,
C_ID int,
PRIMARY KEY (O_ID),
FOREIGN KEY (C_ID) REFERENCES Customers(C_ID)
)
Explanation: In this example, the C_ID column in the Orders table is a foreign key
that references the C_ID column in the Customers table. This ensures that only valid
customer IDs can be inserted into the Orders table.

5. CHECK Constraint
The CHECK constraint allows us to specify a condition that data must satisfy before it
is inserted into the table. This can be used to enforce rules, such as ensuring that a
column’s value meets certain criteria (e.g., age must be greater than 18)
Example:
CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int NOT NULL CHECK (AGE >= 18)
);
Explanation: In the above table, the CHECK constraint ensures that only students
aged 18 or older can be inserted into the table.

6. DEFAULT Constraint
The DEFAULT constraint provides a default value for a column when no value is
specified during insertion. This is useful for ensuring that certain columns always have
a meaningful value, even if the user does not provide one
Example:
CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int DEFAULT 18
);
Unit - 3
SQL: QUERIES, CONSTRAINTS, TRIGGERS: form of basic SQL query, UNION,
INTERSECT, and EXCEPT, Nested Queries, aggregation operators, NULL values, complex
integrity constraints in SQL, triggers and active databases.

Schema Refinement: Problems caused by redundancy, decompositions, problems related to

decomposition, reasoning about functional dependencies, FIRST, SECOND, THIRD normal
forms, BCNF, lossless join decomposition, multivalued dependencies, FOURTH normal form,
FIFTH normal form.
*************************************************************************
Form of basic SQL query

The basic form of an SQL query, specifically when retrieving data, is composed of a combination
of clauses. The most elementary form of an SQL query for data retrieval can be represented as

Syntax
SELECT [DISTINCT] column1, column2, ...
FROM tablename
WHERE condition;
Let's break it down:

1. SELECT Clause: This is where you specify the columns you want to retrieve. Use an
asterisk (*) to retrieve all columns.
2. FROM Clause: This specifies from which table or tables you want to retrieve the data.
3. WHERE Clause (optional): This allows you to filter the results based on a condition.
4. DISTINCT Clause (optional): is an optional keyword indicating that the answer should
not contain duplicates. Normally if we write the SQL without DISTINCT operator then it
does not eliminate the duplicates.

Here are the primary components of SQL queries:

 SELECT: Retrieves data from one or more tables.

  FROM: Specifies the table from which you're retrieving the data.
 WHERE: Filters the results based on a condition.
 GROUP BY: Groups rows that have the same values in specified columns.
 HAVING: Filters the result of a GROUP BY.
 ORDER BY: Sorts the results in ascending or descending order.
 JOIN: Combines rows from two or more tables based on related columns.

To provide a more holistic view, here are a few more SQL examples, keeping them as basic as
possible:

1. Retrieve all columns from a table:

Syntax
SELECT * FROM tablename;

2. Retrieve specific columns from a table:

Syntax
SELECT column1, column2 FROM tablename;

3. Retrieve data with a condition:

Syntax
SELECT column1, column2 FROM tablename WHERE column1 = 'value';

4. Sort retrieved data:

Syntax
SELECT column1, column2 FROM tablename ORDER BY column1 ASC;

Regular expressions in the SELECT Command

SQL provides support for pattern matching through the LIKE operator, along with the use of the
wild-card symbols.

Regular expressions: is a sequence of characters that define a search pattern, mainly for use in
pattern matching with strings, or string matching.

Examples:
Finds Names that start or ends with "a“
Finds names that start with "a" and are at least 3 characters in length.
LIKE: The LIKE operator is used in a 'WHERE' clause to search for a specified pattern in a
column

wild-card: There are two primary wildcards used in conjunction with the `LIKE` operator
percent sign (%) Represents zero, one, or multiple characters
underscore sign(_) Represents a single character

Here's a breakdown of how you can use these wildcards with the `LIKE` operator:

Using `%` Wildcard

1. Find values that start with a specific pattern:

Syntax
SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern%';
For example, to find all customers whose names start with "Ma":

Example
SELECT FirstName
FROM Customers
WHERE FirstName LIKE 'Ma%';
2. Find values that end with a specific pattern:
Syntax
SELECT column_name
FROM table_name
WHERE column_name LIKE '%pattern';

For instance, to find all products that end with "ing":

Example
SELECT ProductName
FROM Products
WHERE ProductName LIKE '%ing';
3. Find values that have a specific pattern anywhere:
Syntax
SELECT column_name
FROM table_name
WHERE column_name LIKE '%pattern%';

Example, to find all books that have the word "life" anywhere in the title:
Example
SELECT BookTitle
FROM Books
WHERE BookTitle LIKE '%life%';
Using `_` Wildcard
1. Find values of a specific length where you only know some characters:
Syntax
SELECT column_name
FROM table_name
WHERE column_name LIKE 'p_ttern';
For instance, if we're looking for a five-letter word where you know the first letter is "h" and the
third letter is "l", you could use:
Example
SELECT Word
 FROM Words
WHERE Word LIKE 'h_l__';
Combining `%` and `_`
We can use both wildcards in the same pattern. For example, to find any value that starts with
"A", followed by two characters, and then "o":
Example
SELECT column_name
FROM table_name
WHERE column_name LIKE 'A__o%';
Keep in mind that the actual symbols used for wildcards might vary depending on the database
system. For example, in SQL Server, the wildcard for a single character is `[?]` instead of `_`.
Always refer to the specific documentation of the database you're working with.
***************************************************************************

SQL Set Operation

The SQL Set operation is used to combine the two or more SQL SELECT statements.

Types of Set Operation

1. Union

2. Union All

3. Intersect

4. Minus/Except

1. Union

 The SQL Union operation is used to combine the result of two or more SQL SELECT
queries.
 In the union operation, all the number of data type and columns must be the same in both
the tables on which the UNION operation is being applied.
 The union operation eliminates the duplicate rows from its result set.
Syntax

SELECT column_name FROM table1

UNION

SELECT column_name FROM table2;

Example:

The First table

ID NAME

1 Jack

2 Harry

3 Jackson

The Second table

ID NAME

3 Jackson

4 Stephan

5 David

Union SQL query will be:

SELECT * FROM First UNION SELECT * FROM Second;

The result set table will look like:

ID NAME

1 Jack
 Harry
3 Jackson

44Stephan

5 David

2. Union All

Union All operation is equal to the Union operation. It returns the set without removing duplication
and sorting the data.

Syntax:

SELECT column_name FROM table1

UNION ALL

SELECT column_name FROM table2;

Example: Using the above First and Second table.

Union All query will be like:

SELECT * FROM First

UNION ALL

SELECT * FROM Second;

The result set table will look like:

ID NAME

1 Jack

Harry
3 Jackson
 33Jackson

4 Stephan

David

3. Intersect

 It is used to combine two SELECT statements. The Intersect operation returns the
common rows from both the SELECT statements.

 In the Intersect operation, the number of data types and columns must be the same.
 It has no duplicates and it arranges the data in ascending order by default.

Syntax

SELECT column_name FROM table1

INTERSECT

SELECT column_name FROM table2;

Example:

Using the above First and Second table.

Intersect query will be:

SELECT * FROM First

INTERSECT

SELECT * FROM Second;

The resultset table will look like:

ID NAME

3 Jackson
4. Minus/Except

 It combines the result of two SELECT statements. Except operator is used to display the
rows which are present in the first query but absent in the second query.

 It has no duplicates and data arranged in ascending order by default.

Syntax:

SELECT column_name FROM table1

Except

SELECT column_name FROM table2;

Example

Using the above First and Second table.

Except query will be:

SELECT * FROM First

Except

SELECT * FROM Second;

The result set table will look like:

ID NAME

1 Jack

Harry
******************************************************************************

Nested Query in SQL

A nested query in SQL contains a query inside another query. The outer query will use the result
of the inner query. For instance, a nested query can have two SELECT statements, one on the inner
query and the other on the outer query.

Types of Nested Queries in SQL

Nested queries in SQL can be classified into two different types:

 Independent Nested Queries

 Co-related Nested Queries

Independent Nested Queries

In independent nested queries, the execution order is from the innermost query to the outer query.
An outer query won't be executed until its inner query completes its execution. The outer query
uses the result of the inner query. Operators such as IN, NOT IN, ALL, and ANY are used to write
independent nested queries.

 The IN operator checks if a column value in the outer query's result is present in the inner
query's result. The final result will have rows that satisfy the IN condition.
 The NOT IN operator checks if a column value in the outer query's result is not present in
the inner query's result. The final result will have rows that satisfy the NOT IN condition.
 The ALL operator compares a value of the outer query's result with all the values of the
inner query's result and returns the row if it matches all the values.
 The ANY operator compares a value of the outer query's result with all the inner query's
result values and returns the row if there is a match with any value.
Co-related Nested Queries

In co-related nested queries, the inner query uses the values from the outer query to execute the
inner query for every row processed by the outer query. The co-related nested queries run slowly
because the inner query is executed for every row of the outer query's result.

How to Write a Nested Query in SQL?

We can write a nested query in SQL by nesting a SELECT statement within another SELECT
statement. The outer SELECT statement uses the result of the inner SELECT statement for
processing.

The general syntax of nested queries will be:

SELECT column_name [, column_name ]

FROM table1 [, table2]

WHERE column_name OPERATOR

(SELECT column_name [, column_name] FROM table1 [, table2] [WHERE]);

The SELECT query inside the brackets () is the inner query, and the SELECT query outside the
brackets is the outer query. The outer query uses the result of the inner query.

Examples of Nested Query in SQL

We will use the Employees and Awards table below to understand independent and co-related
nested queries. We will be using Oracle SQL syntax in our queries.
Let's create the Employees and Awards tables:

CREATE TABLE employee (id NUMBER PRIMARY KEY, name VARCHAR2(100) NOT
NULL, salary NUMBER NOT NULL, role VARCHAR2(100) NOT NULL );

CREATE TABLE awards (id NUMBER PRIMARY KEY, employee_id NUMBER NOT NULL,
award_date DATE NOT NULL);

Let's add data to the tables created above:

INSERT INTO employees VALUES (1, 'Augustine Hammond', 10000, 'Developer');

INSERT INTO employees VALUES (2, 'Perice Mundford', 10000, 'Manager');

INSERT INTO employees VALUES (3, 'Cassy Delafoy', 30000, 'Developer');

INSERT INTO employees VALUES (4, 'Garwood Saffen', 40000, 'Manager');

INSERT INTO employees VALUES (5, 'Faydra Beaves', 50000, 'Developer');

INSERT INTO awards VALUES(1, 1, TO_DATE('2022-04-01', 'YYYY-MM-DD'));

INSERT INTO awards VALUES(2, 3, TO_DATE('2022-05-01', 'YYYY-MM-DD'));

Employees

id name salary Role

1 Augustine Hammond 10000 Developer

2 Perice Mundford 10000 Manager

3 Cassy Delafoy 30000 Developer

 4 Garwood Saffen 40000 Manager

5 Faydra Beaves 50000 Developer

Awards

id employee_id award_date

11 11 2022-04-01

22 33 2022-05-01

Independent Nested Queries

Example 1: IN

Select all employees who won an award.

SELECT id, name FROM employees

WHERE id IN (SELECT employee_id FROM awards);

Output

id name
2 Perice Mundford

4 Garwood Saffen

5 Faydra Beaves
Example 2: NOT IN

Select all employees who never won an award.

SELECT id, name FROM employees

WHERE id NOT IN (SELECT employee_id) FROM awards);

Output
id name

1 Augustine Hammond

3 Cassy Delafoy

Example 3: ALL

Select all Developers who earn more than all the Managers

SELECT * FROM employees

WHERE role = 'Developer'

AND salary > ALL (SELECT salary FROM employees WHERE role = 'Manager');

Output

id name salary role

55 Faydra Beaves 50000 Developer

Explanation

The developer with id 5 earns (50000) more than all the managers: 2 (10000) and 4 (40000)

Example 4: ANY

Select all Developers who earn more than any Manager

SELECT * FROM employees

WHERE role = 'Developer'

AND salary > ANY (SELECT salary FROM employees WHERE role = 'Manager');
Output

id Name salary role

Faydra Beaves 50000 Developer

3 Cassy Delafoy 30000 Developer

Explanation

The developers with id 3 and 5 earn more than any manager:

 The developer with id 3 earns (30000) more than the manager with id 2 (10000)
 The developer with id 5 earns (50000) more than the managers with id 2 (10000) and 4
(40000)

Co-related Nested Queries

Select all employees whose salary is above the average salary of employees in their role.

SELECT * FROM employees emp1

WHERE salary > (SELECT AVG(salary) FROM employees emp2 WHERE [Link] =
[Link]);

Output

id Name salary role

4 Garwood Saffen 40000 Manager

5 Faydra Beaves 50000 Developer

Explanation

The manager with id 4 earns more than the average salary of all managers (25000), and the
developer with id 5 earns more than the average salary of all developers (30000). The inner query
is executed for all rows fetched by the outer query. The inner query uses the role value ([Link])
of every outer query's row ([Link] = [Link]).

We can find the average salary of managers and developers using the below query:

SELECT role, AVG(salary)

FROM employees GROUP BY role;

role avg(salary)

Developer 30000

Manager 25000

************************************************************************

SQL Aggregate Functions

 SQL aggregation function is used to perform the calculations on multiple rows of a single
column of a table. It returns a single value.

 It is also used to summarize the data.

Types of SQL Aggregation Function

1. COUNT FUNCTION

 The COUNT function is used to Count the number of rows in a database table. It can
work on both numeric and non-numeric data types.
 COUNT function uses the COUNT(*) that returns the count of all the rows in a specified
table. COUNT(*) considers duplicate and Null.

Syntax

1. COUNT(*)
2. or
3. COUNT([ALL|DISTINCT] expression)

Sample table:

PRODUCT_MAST

PRODUCT COMPANY QTY RATE COST

Item1 Com1 2 10 20

Item2 Com2 3 25 75

Item3 Com1 2 30 60

Item4 Com3 5 10 50

Item5 Com2 2 20 40

Item6 Cpm1 3 25 75

Item7 Com1 5 30 150

Item8 Com1 3 10 30

Item9 Com2 2 25 50

Item10 Com3 4 30 120

Example: COUNT()

SELECT COUNT(*)

FROM PRODUCT_MAST;

Output:

10

Example: COUNT with WHERE

SELECT COUNT(*)

FROM PRODUCT_MAST;

WHERE RATE>=20;

Output:

7

Example: COUNT() with DISTINCT

SELECT COUNT(DISTINCT COMPANY)

FROM PRODUCT_MAST;
Output:

3

Example: COUNT() with GROUP BY

SELECT COMPANY, COUNT(*)

FROM PRODUCT_MAST

GROUP BY COMPANY;

Output:

Com1 5

Com2 3

Com3 2

Example: COUNT() with HAVING

SELECT COMPANY, COUNT(*)

FROM PRODUCT_MAST

GROUP BY COMPANY

HAVING COUNT(*)>2;

Output:

Com1 5

Com2 3

2. SUM Function
Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

Syntax

SUM()

or

SUM([ALL|DISTINCT] expression)

Example: SUM()

SELECT SUM(COST)

FROM PRODUCT_MAST;

Output:

670

Example: SUM() with WHERE

SELECT SUM(COST)

FROM PRODUCT_MAST

WHERE QTY>3;

Output:

320

Example: SUM() with GROUP BY

SELECT SUM(COST)

FROM PRODUCT_MAST
 WHERE QTY>3

GROUP BY COMPANY;

Output:

Com1 150

Com2 170

Example: SUM() with HAVING

SELECT COMPANY, SUM(COST)

FROM PRODUCT_MAST

GROUP BY COMPANY

HAVING SUM(COST)>=170;

Output:

Com1 335

Com3 170

3. AVG function

The AVG function is used to calculate the average value of the numeric type. AVG function returns
the average of all non-Null values.

Syntax

AVG()

or
 AVG([ALL|DISTINCT] expression)

Example:

SELECT AVG(COST)

FROM PRODUCT_MAST;

Output:

67.00

4. MAX Function

MAX function is used to find the maximum value of a certain column. This function determines
the largest value of all selected values of a column.

Syntax

MAX()

or

MAX([ALL|DISTINCT] expression)

Example:

SELECT MAX(RATE)

FROM PRODUCT_MAST;

30

5. MIN Function

MIN function is used to find the minimum value of a certain column. This function determines the
smallest value of all selected values of a column.
Syntax

MIN()

or

MIN([ALL|DISTINCT] expression)

Example:

SELECT MIN(RATE)

FROM PRODUCT_MAST;

Output:

10
****************************************************************************
NULL values in SQL
In SQL there may be some records in a table that do not have values or data for every field and
those fields are termed as a NULL value.

NULL values could be possible because at the time of data entry information is not available. So
SQL supports a special value known as NULL which is used to represent the values of attributes
that may be unknown or not apply to a tuple. SQL places a NULL value in the field in the absence
of a user-defined value.

So, NULL values are those values in which there is no data value in the particular field in the
table.

Importance of NULL Value

 It is important to understand that a NULL value differs from a zero value.
 A NULL value is used to represent a missing value, but it usually has one of three
different interpretations:
 The value unknown (value exists but is not known)
 Value not available (exists but is purposely withheld)
1. Functions in SQL

A function in SQL is a stored program that can accept inputs, perform operations, and
return a value. It is similar to a procedure, but a function must return a value, and it can
be used within queries like any other expression.

Key Points:

 Functions can be invoked from queries.

 Functions return a single value.
 They can take input parameters.
 They are typically used for computations, transformations, and data retrieval.

Syntax

CREATE FUNCTION function_name (@parameter DATATYPE)

RETURNS RETURN_DATATYPE
AS
BEGIN
-- SQL Statements
RETURN value;
END;

Example of Function

CREATE FUNCTION GetTotalStudents()

RETURNS INT
AS
BEGIN
DECLARE @Total INT;
SELECT @Total = COUNT(*) FROM Students;
RETURN @Total;
END;

Usage a function

SELECT [Link]();

2. Procedures in SQL

A procedure (also called a stored procedure) is a set of SQL statements that can be
executed as a unit. Procedures can take parameters, perform operations like insert,
update, delete, and select, and return multiple results (but not directly a single value like
functions).

Key Points:

 Procedures do not necessarily return a value, but they may return multiple result
sets.
 They can perform multiple operations.

Syntax:

CREATE PROCEDURE procedure_name (parameters)

AS
BEGIN
-- SQL statements
END;

Example of Procedure
CREATE PROCEDURE GetAllStudents
AS
BEGIN
SELECT * FROM Students;
END;

Executing a Procedure::

EXEC GetAllStudents;

SQL | Triggers
Trigger is a statement that a system executes automatically when there is any
modification to the database. In a trigger, we first specify when the trigger is to
be executed and then the action to be performed when the trigger executes.
Triggers are used to specify certain integrity constraints and referential
constraints that cannot be specified using the constraint mechanism of SQL.
Example –
Suppose, we are adding a tuple to the ‘Donors’ table that is some person has
donated blood. So, we can design a trigger that will automatically add the value
of donated blood to the ‘Blood_record’ table.
Types of Triggers –
We can define 6 types of triggers for each table:
1. AFTER INSERT activated after data is inserted into the table.

2. AFTER UPDATE: activated after data in the table is modified.

3. AFTER DELETE: activated after data is deleted/removed from the table.

4. BEFORE INSERT: activated before data is inserted into the table.

5. BEFORE UPDATE: activated before data in the table is modified.

6. BEFORE DELETE: activated before data is deleted/removed from the table.

Examples showing implementation of Triggers:

1. Write a trigger to ensure that no employee of age less than 25 can be
inserted in the database.
delimiter $$
CREATE TRIGGER Check_age BEFORE INSERT ON employee
FOR EACH ROW
BEGIN
IF [Link] < 25 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'ERROR:
AGE MUST BE ATLEAST 25 YEARS!';
END IF;
END; $$
delimiter;
Explanation: Whenever we want to insert any tuple to table ’employee’, then
before inserting this tuple to the table, trigger named ‘Check_age’ will be
executed. This trigger will check the age attribute. If it is greater than 25 then this
tuple will be inserted into the table otherwise an error message will be printed
stating “ERROR: AGE