INTERSECT IN SQL

SQL>create table sailors(sid int,sname varchar2(20),rating int,age int,primary  key(sid));

Table created.

SOL> insert into sailors values(‘&sid’,’&sname’,’&rating’,’&age’);

Enter value for sid:1

Enter value for sname:john

Enter value for rating:7

Enter value for age:22

1 row created.

SQL>select * from sailors;

SID    SNAME    RATING   AGE

----    ----------    ---------     -----

1       john          7                22

2       dean         8                33

3       sam          5                32

SQL>create table boats(bid int,bname varchar2(20),color  varchar2(20),primary  key(bid));

Table created.

SOL> insert into boats values(‘&bid’,’&bname’,’&color’,’&age’);

Enter value for bid:102

Enter value for sname:casino

Enter value for color::red

1 row created.

SQL>select * from boats;

BID    BNAME   COLOR

----    ----------    ---------

102       casino         red               

102       titanic         blue               

103       santro         green              

SQL>create table reserves(sid int,bid int,date varchar2(20),primary  key(sid,bid,date) foreign key (sid) references sailors,foreign key (bid) references boats);

Table created.

SOL> insert into boats reserves(‘&sid’,‘&bid’,’&date’);

Enter value for sid:1

Enter value for bid:102

Enter value for date:9/9/91

1 row created.

SQL>select * from reserves;

SID    BID           DATE

----    ----------    ---------

1        102          9/9/91              

2        102          8/8/81               

3        103          6/6/91              

SQL>select s.sid from sailors s,boats b,reserves r where s.ssid=r.sid and b.color=’red’ INTERSECT select s.sid from sailors s,boats b,reserves r where r.bid=b.bid and b.color=’blue’

SID 

---- 

1    

2