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

self join in sql

                                                                                                                            edited by sukamal

create table dept(d_no varchar2(20) primary key,d_name varchar2(20),mg_no number);

insert into dept values('1','it','1');

insert into dept values('2','finance','3');

select *

from dept;

output:

D_NO	D_NAME	MG_NO
1	it	1
2	finance	3

2:code:

create table employee(e_id varchar2(20) primary key,e_name varchar2(20),d_no varchar2(20) references dept,city varchar2(20));

insert into employee values('1','ram','1','m.p');

insert into employee values('2','shyam','1','m.p');

insert into employee values('3','sita','2','u.p');

insert into employee values('4','gita','2','goa');

select *

from employee;

output:

E_ID	E_NAME	D_NO	CITY
1	ram	1	m.p
2	shyam	1	m.p
3	sita	2	u.p
4	gita	2	goa

3: Find the manager name of shyam.

Program code:

 select e_name

from employee

where e_id=(select mg_no from dept where d_no=(select d_no from employee where e_name='shyam'));

output:

E_NAME

ram

4:Find the employee city and manager city of all employee

Program code:

select e2.city,e1.city

from employee e1,dept,employee e2

where e2.d_no=dept.d_no and

dept.mg_no=e1.e_id;

output:

CITY	CITY
m.p	m.p
m.p	m.p
u.p	u.p
goa	u.p

5:Find the all employee name and their manager name.

Program code:

select e2.e_name,e1.e_name

from employee e1,dept,employee e2

where e2.d_no=dept.d_no and

dept.mg_no=e1.e_id;

output:

E_NAME		E_NAME
ram		ram
shyam		ram
sita		sita
gita		sita
6:Find all the employee  whose city is equal to their manager city program code: select e2.e_name from employee e1,dept,employee e2 where e2.d_no=dept.d_no and dept.mg_no=e1.e_id and e1.city=e2.city; output: E_NAME ram shyam sita