EXCEPT , EXISTS 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,fpreign 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 sname from sailors where exsists(select * from reserves where bid=103 and sid=sid);

SNAME

-----------

Sam

SQL>select sname from sailors where except(select * from reserves where bid=103 and sid=sid);

SNAME

-----------

John

dean

SQL>select sid from sailors left join boats on sailors.bid=boat.bid except select sid from sailors right join boats on sailors.bid=boat.bid;

SID

------

1

3

SQL>select sid from sailors except select sid from reserves;

SID

-----

1

2

3