The SELECT operation is used for selecting/fetching a subset of the tuples according to a given selection condition. Sigma(σ) Symbol is used to denote it. It is used as an expression to choose tuples which meet the selection condition. Select operator selects tuples that satisfy a given predicate. σp(r)
σ is the predicate
r stands for relation which is the name of the table
p is prepositional logic
Syntax :

σ Condition/Predicate(Relation/Table name)

Example :

σ ecity="Vadodara" (employee) 

select * from employee where ecity='Vadodara';

The above statement will return the data of employee whose city name is Vadodara.

The projection removes all the attributes of the input relation except those which are mentioned in the projection list. The projection method defines a relation that contains a vertical subset of Relation. This helps to extract the values of specified attributes to remove duplicate values. (pi) symbol is used to select attributes from a relation. This operator helps to keep specific columns from a relation and removes the other columns. Syntax :

∏ column_name1, column_name2, ...., column_nameN(table_name)

Example :

∏ ename, ecity (employee)   

select ename,ecity from employee;

The above statement will return the data of student name and city name from the employee relation.

UNION is denoted by ∪ symbol. It includes all tuples which are in the given relations ie A or in B. It also removes duplicate tuples. So, set A UNION set B will be represented as: The result <- A ∪ B For a union operation to be valid, the following conditions must be met
A and B must be the same number of attributes.
Attribute domains need to be compatible(field type wise).
Duplicate tuples should be automatically removed.
Example :

SELECT * FROM employee UNION ALL SELECT * FROM supplier  

INTERSECTION is denoted by ∩ symbol. It includes only common tuples which are in the given relations ie A and in B. It also removes duplicate tuples. So, set A INTERSECTION set B will be represented as: The result <- A ∩ B For a intersection operation to be valid, the following conditions must be met
A and B must be the same number of attributes.
Attribute domains need to be compatible(field type wise).
Duplicate tuples should be automatically removed.
Example :

SELECT * FROM employee INTERSECT SELECT * FROM supplier  

DIFFERENCE is denoted by − symbol. It includes all the tupes tuples which are available in A but not in B. It also removes duplicate tuples. So, set A DIFFERENCE set B will be represented as: The result <- A − B For a intersection operation to be valid, the following conditions must be met
A and B must be the same number of attributes.
Attribute domains need to be compatible(field type wise).
Duplicate tuples should be automatically removed.

SELECT * FROM employee MINUS SELECT * FROM supplier  

Cartesian Product is an operation used to merge columns from two relations.It is also known as Cross Join operation.

σ column 2 = (A X B)

The above code will return all rows from relation A and B whose column 2.

SELECT * FROM employee CROSS JOIN supplier

The above statement returns all the rows of employee as well as of supplier. The Total no. of rows is = 8 ie 4 records of employee table X 2 records of supplier table.Alternatively, you can write above statement as :

SELECT * FROM employee,supplier

The output of relational algebra operations are also relations but without any name. The rename operation enables us to rename the output relation.

ρ x (E)

Where x is the name and E is the expression

RENAME employee TO employee1;

The above statment of oracle will use a copy of the sample table employee to employee1.

There are mainly two types of joins available in DBMS:
   1) Inner Joins: Theta, Equi,Self
   2) Outer Join: Left, Right, Full
Inner Joins : INNER JOIN fetches rows from both the given tables which satisfy the given condition. It is the most widely used operation and can be considered as a default join-type.An Inner join or equijoin is a comparator-based join type which uses equality comparisons in the join-predicates. Inner Join further divided into three subtypes :

1) Theta Join : Theta Join enables to merge two tables based on the condition represented by theta. Theta joins work for all comparison operators. It is denoted by θ symbol . The general case of JOIN operation is called a Theta join.
Syntax :

A ⋈θ B

Example :

SELECT * FROM employee JOIN supplier on scity < ecity

2) Equi Join : Equi Join enables to merge two tables on equivalence condition represented by theta.
Syntax :
Example :

SELECT * FROM employee a JOIN supplier b on a.ecity=b.scity

3) Self Join : Self Join enables to apply the join operation on single table.
Syntax :
Example :

SELECT * FROM employee a JOIN employee b on a.ecity=b.ecity where a.eno<>b.eno

Outer Joins : An OUTER JOIN doesn't require that each record of the two joined tables to have a common record. In this type of join, the table retains each record even if no other common record exists.Outer Join further divided into three subtypes :

1) Left Outer Join : The left outer join returns a records of the given tables with the matched data from the both the tables and then the remaining all the rows of the left side table.
Example :

SELECT * FROM employee a LEFT OUTER JOIN supplier b ON a.ecity=b.scity

2) Right Outer Join : The right outer join returns a records of the given tables with the matched data from the both the tables and then the remaining all the rows of the right side table.
Example :

SELECT * FROM employee a RIGHT OUTER JOIN supplier b ON a.ecity=b.scity

3) Full Outer Join : The full outer join returns a records of the given tables with the matched data from the both the tables and then the remaining all the rows of the both the side.
Example :

SELECT * FROM employee a FULL OUTER JOIN supplier b ON a.ecity=b.scity

Division operator A÷B can be applied if and only if:All Attributes of B is proper subset of Attributes of A.The relation returned by division operator will have attributes = (All attributes of A – All Attributes of B).The relation returned by division operator will return those tuples from relation A which are associated to every B’s tuple.

Refer the above tables and Write down the Relational Operations and/or PL/SQL Statements for the following :
1) List out the data of all the Boys whose city name is Surat.
2) List out all the students who is studying in FYBCA and beloging from the Ahmedabad City.
3) Write down PL/SQL Statement for : Student ⋈ Class ⋈ City.
4) Write down PL/SQL Statement for : (Student U Class) ∩ (Student U City).
5) Write down PL/SQL Statement for : PStudentName,Gender(RCityName=”Vadodara”(Student ⋈ City)).