Experiment No.
:-8
Aim :- To understand and execute Relational Algebra and SQL operators commands in
MySQL.
Relational Algebra in DBMS
1.1 Introduction to Relational Algebra
Relational Algebra is a procedural query language used to query the database in a systematic
way. It operates on relations (tables) and produces results as new relations.
Some common relational algebra operations include:
1. Selection (σ): Select rows based on a condition.
2. Projection (π): Select specific columns.
3. Union (∪): Combine two relations with the same schema.
4. Set Difference (-): Return rows in one relation but not in another.
5. Cartesian Product (×): Combine each row of one relation with each row of another.
6. Join (⨝): Combine related rows from two tables.
7. Rename (ρ): Rename a relation or its attributes.
1.2 Basic Table Creation
Let's assume the following two tables in a database:
• Employee (ID, Name, Salary, Department)
• Department (Dept_ID, Dept_Name)
1.3 Relational Algebra Operations Examples
Selection (σ): Select rows based on a condition.
Syntax:
σ(condition)(Relation)
Example: Select employees from the Employee table who earn more than $50,000.
σ(Salary > 50000)(Employee)
Adarsh
Putul Pal (2204310100074)
kumari Roll no:- 2204310130001
SQL code:
SELECT * FROM Employee WHERE Salary > 50000;
Projection (π): Select specific columns from a table.
Syntax:
π(attribute1, attribute2, ...)(Relation)
Example: Project only the Name and Salary of employees from the Employee table.
π(Name, Salary)(Employee)
SQL code
SELECT Name, Salary FROM Employee;
Union ( ): Combine two relations with the same schema. Syntax:
Relation1 Relation2
Example: Suppose we have another table ContractEmployee with similar columns as Employee.
To combine both relations:
Employee ContractEmployee
Adarsh
Putul Pal (2204310100074)
kumari Roll no:- 2204310130001
SQL code
SELECT * FROM Employee UNION SELECT * FROM ContractEmployee;
Set Difference (-): Return rows that are in the first relation but not in the second. Syntax:
Relation1 - Relation2
Example: Find employees who are not in the Department table (i.e., employees without
departments).
Employee - Department
SQL code
SELECT * FROM Employee
WHERE Department NOT IN (SELECT Dept_Name FROM Department);
Cartesian Product (×): Combine each row of one relation with every row of another
relation. Syntax:
Relation1 × Relation2
Example: Get all combinations of employees and departments.
Employee × Department
Adarsh
Putul Pal (2204310100074)
kumari Roll no:- 2204310130001
SQL code
SELECT * FROM Employee, Department;
Join (⨝): Combine related rows from two tables based on a common column. Syntax:
Relation1 ⨝ Relation2
Example: Get the details of employees and their respective departments by joining the Employee
and Department tables on the Department field.
Employee ⨝ Department
SQL code
SELECT E.Name, E.Salary, D.Dept_Name
FROM Employee E
JOIN Department D ON E.Department = D.Dept_Name;
Rename (ρ): Rename a relation or attribute.
Syntax:
ρ(new_name)(Relation)
Adarsh
Putul Pal (2204310100074)
kumari Roll no:- 2204310130001
Example: Rename the Employee table to EmpDetails.
ρ(EmpDetails)(Employee) SQL
code.
Renaming in SQL is not directly possible, but it can be done using aliases in queries
SELECT * FROM Employee AS EmpDetails;
SQL Operators 1. Arithmetic Operators
Definition: Arithmetic operators are used to perform mathematical operations like addition,
subtraction, multiplication, division, and modulus on numeric data.
Questions:
1. Addition (+):
Question: Write the SQL query to increase the salary of every employee by 1000.
SELECT Salary + 1000 AS NewSalary FROM Employee;
2. Subtraction (-):
Question: Write the SQL query to reduce the salary of every employee by 500.
SELECT Salary - 500 AS NewSalary FROM Employee;
Adarsh
Putul Pal (2204310100074)
kumari Roll no:- 2204310130001
3. Multiplication (*):
Question: Write the SQL query to double the salary of every employee.
SELECT Salary * 2 AS NewSalary FROM Employee;
4. Division (/):
SELECT Salary / 2 AS HalfSalary FROM Employee;
5. Modulus (%):
Question: Write the SQL query to find the remainder when each employee's salary is
SELECT Salary % 2 AS SalaryRemainder FROM Employee;
Adarsh
Putul Pal (2204310100074)
kumari Roll no:- 2204310130001
2. Comparison Operators
Definition: Comparison operators are used to compare two values and return a boolean result
(True/False).
Questions:
1. Equalto(=):
Question: Write the SQL query to find all employees whose salary is exactly 50000.
SELECT * FROM Employee WHERE Salary = 50000;
2. NotEqualto(<>or!=):
Question: Write the SQL query to find all employees whose salary is not equal to 50000.
SELECT * FROM Employee WHERE Salary <> 50000;
3. GreaterThan(>):
Question: Write the SQL query to find all employees whose salary is greater than 50000.
SELECT * FROM Employee WHERE Salary > 50000;
Adarsh
Putul Pal (2204310100074)
kumari Roll no:- 2204310130001
4. LessThan(<):
Question: Write the SQL query to find all employees whose salary is less than 50000.
SELECT * FROM Employee WHERE Salary < 50000;
5. GreaterThanorEqualto(>=):
Question: Write the SQL query to find all employees whose salary is greater than or
equal to 50000.
SELECT * FROM Employee WHERE Salary >= 50000;
6. LessThanorEqualto(<=):
Question: Write the SQL query to find all employees whose salary is less than or equal to
50000.
SELECT * FROM Employee WHERE Salary <= 50000;
3. Logical Operators
Definition: Logical operators are used to combine multiple conditions in SQL queries. They
include AND, OR, and NOT.
Questions:
1. AND:
Adarsh
Putul Pal (2204310100074)
kumari Roll no:- 2204310130001
Question: Write the SQL query to find all employees in the HR department with a salary
greater than 50000.
SELECT * FROM Employee WHERE Department = 'HR' AND Salary > 50000;
2. OR:
Question: Write the SQL query to find all employees who are either in the HR
department or have a salary greater than 50000.
SELECT * FROM Employee WHERE Department = 'HR' OR Salary > 50000;
3. NOT:
Question: Write the SQL query to find all employees who are not in the HR department.
SELECT * FROM Employee WHERE NOT Department = 'HR';
Questions:
4. UNION:
Question: Write the SQL query to combine the Employee and ContractEmployee tables
to get a list of all employees.
SELECT EmpID, Name, Salary FROM Employee
UNION
SELECT EmpID, Name, Salary FROM ContractEmployee;
Adarsh
Putul Pal (2204310100074)
kumari Roll no:- 2204310130001
5. EXCEPT:
Question: Write the SQL query to find employees who are not listed in the Department
table.
SELECT * FROM Employee
WHERE Department NOT IN (SELECT Dept_Name FROM Department);
6. LIKE:
Question: Write the SQL query to find all employees whose name starts with the letter 'J'.
SELECT * FROM Employee WHERE Name LIKE 'J%';
7. IN:
Question: Write the SQL query to find all employees working in either the HR or
Finance department.
SELECT * FROM Employee WHERE Department IN ('HR', 'Finance');
8. BETWEEN:
Adarsh
Putul Pal (2204310100074)
kumari Roll no:- 2204310130001
Question: Write the SQL query to find all employees with a salary between 50000 and
75000.
SELECT * FROM Employee WHERE Salary BETWEEN 50000 AND 75000;
9. ISNULL:
Question: Write the SQL query to find all employees who do not have a department
assigned (i.e., where the department is NULL).
SELECT * FROM Employee WHERE Department IS NULL;
Adarsh
Putul Pal (2204310100074)
kumari Roll no:- 2204310130001
