Execute Set operations on various Relations. • UNION • UNION ALL • INTERSECT • MINUS.

 

EXERCISE : 8

AIM : Execute Set operations on various Relations.
• UNION                              • UNION ALL               • INTERSECT
• MINUS.

Description :

Set operations in sql:

UNION :

Let R and S are two union compatible relations then, union operation returns the tuples that are present in R  or s or both.

·         Two relational instances are said to be union compatible if the following conditions are hold.

1)      They have the same number of columns.

2)      Corresponding columns taken in order from left to right have same data type.

1.      Find the names of sailors who have reserved red or green boat.

Query

select s.sname from sailor1 s,reserve1 r,boat1 b where s.sid = r.sid and r.bid = b.bid and b.color = 'red'

UNION

 select s.sname from sailor1 s,reserve1 r,boat1 b where s.sid = r.sid and r.bid = b.bid and b.color = 'green';

Output

 

2.      Find all sid’s of sailors who have rating of 10 or reserved boat no.104.

Query

 select s.sid from sailor1 s where s.rating=10

UNION

 select r.sid from reserve1 r where r.bid = 104;

Output

UNION ALL :

The UNION ALL command combines the result set of two or more SELECT statements (allows duplicate values).
Syntax :

SELECT column_name(s) FROM table1
UNION ALL:
SELECT column_name(s) FROM table2;

Example :

INTERSECT :

Let R and S are two union compatible relations then, intersect operation returns the tuples that are common in both the relations.

1.      Find the names of sailors who have reserved red and green boat.

Query

select s.sname from sailor1 s,reserve1 r,boat1 b where s.sid = r.sid and r.bid = b.bid and b.color = 'red'

INTERSECT

 select s .sname from sailor1 s,reserve1 r,boat1 b where s.sid = r.sid and r.bid = b.bid and b.color = 'green';

Output

MINUS :

Let R and S are two union compatible relations then, intersect operation returns the tuples that are present in R but not in S.

1.      Find the sid’s of sailors who have reserved red but not green boat.

Query

select r.sid from boat1 b,reserve1 r where r.bid = b.bid and b.color = 'red' MINUS  select r.sid from boat1 b,reserve1 r where r.bid = b.bid and b.color ='green';

Output

 

 

VIVA QUESTION:

1. List various set Operations

2. Differentiate UNION and UNION ALL