written 6.1 years ago by | modified 6.1 years ago by |
(i)Select:
• The select operator is used to select the rows from a table which satisfy particular selection condition given in selection operation.
• Select operator selects a set of tuples that satisfy a selection condition.
• Output of query is exactly same as input schema of table.
• This is unary relational operator having only one input table.
Syntax:
σ<attribute_name><comparison_operator><constant_value>(<input_table_name>)
Where,
Attribute_name: Name of the column in table
Comparison_operator: =,<,<=,>,>=,<>
Example:
Eid | EName | Age |
---|---|---|
1 | Nidhi | 34 |
2 | Ajit | 24 |
3 | Amit | 28 |
Select all the employees having age below 25 years
σage<25(Employee)
Eid | EName | Age |
---|---|---|
2 | Ajit | 24 |
ii) Project
• Project operator is used foe selecting some of many columns in table to display in result set.
• Few rows and columns can be selected as per requirement.
• This unary relational operator having only one input table.
Syntax:
π<column_list>(<input_table_name>)
Example:
Find Employees salary from Employee table and Salary
πEid,Salary,Age(Employee)
Eid | Salary | Age |
---|---|---|
1 | 100000 | 34 |
2 | 12000 | 24 |
3 | 24000 | 28 |
ii) Natural Join
• A Natural join returns all rows by matching values in comman columns having same name and data type of columns should be presentin both sides
Example:
Find all the Employees and their department name
Employee Table
Eid | EName | Did |
---|---|---|
1 | Amit | 10 |
2 | Nidhi | 30 |
3 | Ajit | 50 |
Department Table
Did | DName |
---|---|
10 | IPF |
30 | HR |
70 | TIS |
πeid,dname(Employee ⋈ Department)
Eid | DName |
---|---|
1 | IPF |
2 | HR |
iv) Set Intersection
• Set Intersection operator finds out all rows that are common in both result of Query 1 and in the result of Query 2.
Syntax: (Query Expression 1) ⋂ (Query Expression 2)
R
A | 1 |
---|---|
B | 2 |
C | 3 |
D | 4 |
E | 5 |
S
A | 1 |
---|---|
B | 2 |
C | 3 |
D | 4 |
R ⋂S
A | 1 |
---|---|
B | 2 |
C | 3 |
D | 4 |
Examples:
All Employees in IT Department under Mumbai University
IT_Employee
Eid | EName | Age |
---|---|---|
11 | Suhas | 24 |
12 | Jayendra | 24 |
13 | Sachin | 25 |
14 | Mahesh | 23 |
All Employess in ABC College
Eid | EName | Age |
---|---|---|
11 | Suhas | 24 |
12 | Jayendra | 24 |
23 | Geeta | 25 |
24 | Amruta | 23 |
35 | Sangeeta | 21 |
Intersection of the above is employees in IT Department of ABC College
Eid | EName | Age |
---|---|---|
11 | Suhas | 24 |
12 | Jayendra | 24 |