wbsu 2016 dbms practical question

                                                                                                                                 edited by- joy

create table boat(bid varchar2(10) primary key,bname varchar2(10),color varchar2(10));

create table sailor(sid varchar2(10) primary key,sname varchar2(10),rating number);

create table reserve(sid varchar2(10) references sailor, bid varchar2(10) references boat, day varchar2(10));

insert into boats values('001','titanic','red');

insert into boats values('002','gulliver','green');

insert into boats values('003','crusoe','red');

insert into boats values('004','shanok','green');

insert into boats values('005','paradise','yellow');

insert into sailors values('A01','varshan','6');

insert into sailors values('A02','ashutosh','7');

insert into sailors values('A03','anindo','5');

insert into sailors values('A04','akash','9');

insert into sailors values('A05','ashutosh','8');

insert into reserves values('A01','001','sunday');

insert into reserves values('A01','003','wednesday');

insert into reserves values('A01','002','monday');

insert into reserves values('A01','004','thurshday');

insert into reserves values('A02','001','monday');

insert into reserves values('A03','001','tueday');

insert into reserves values('A03','003','friday');

insert into reserves values('A04','004','saturday');

insert into reserves values('A04','001','wednesday');

insert into reserves values('A05','001','sunday');       

select * from boats

B_ID	B_NAME	COLOR
001	titanic	red
002	gulliver	green
003	crusoe	red
004	shanok	green
005	paradise	yellow

select * from sailors

SID	SNAME	RATING
A01	varshan	6
A02	ashutosh	7
A03	anindo	5
A04	akash	9
A05	ashutosh	8

select * from reserves

SID	BID	DAY
A01	001	sunday
A01	003	wednesday
A01	002	monday
A01	004	thurshday
A02	001	monday
A03	001	tuesday
A03	003	friday
A04	004	saturday
A04	001	wednesday
A05	001	monday

1.find the name of sailors who have reserved red boats but not green boats?

select sname from sailors,boats,reserves where sailors.sid=reserves.sid and

 boats.bid=reserves.bid and

color='red'

minus

select sname

from sailors,boats,reserves

where sailors.sid=reserves.sid and

 boats.bid=reserves.bid and

color='green';

SNAME

anindo

ashutosh

2.find  the name of boats which are reserved by all sailors?
(division using minus and not exists)

select bname from   boats  where not exists           

                     ((select sid  from sailors )minus(select sid from reserves where  boats.bid=reserves.bid));

BNAME

titanic