Dbms lab 4

an anonymous user · June 11, 2021 · SQL
create table sailors(sid integer,sname char(8) ,rating integer,age real); 
create table boats(bid integer, bname char(8) ,color char(6) );
create table reserves(sid integer, bid integer);
insert into sailors values(22,'dustin',7,45);
insert into sailors values(29,'brutus',1,33);
insert into sailors values(31,'lubber',8,55.5);
insert into sailors values(32,'andy',8,25.5);
insert into sailors values(58,'rusty',10,35);
insert into sailors values(64,'horatio',7,35);
insert into sailors values(71,'zobra',10,16);
insert into sailors values(74,'horatio',9,40);
insert into sailors values(85,'art',3,25.5);
insert into sailors values(95,'bob',3,63.5);
select *from sailors;
insert into boats values(101,'interlake','blue');
insert into boats values(102,'interlake','red');
insert into boats values(103,'clipper','green') ;
insert into boats
values(104,'marine','red');
select * from boats;
insert into reserves values (22,101);
insert into reserves values (22,102);
insert into reserves values (22,103);
insert into reserves values (22,104);
insert into reserves values (31,102);
insert into reserves values (31,103);
insert into reserves values (31,104);
insert into reserves values (64,101);
insert into reserves values (64,102);
insert into reserves values (74,103);
select *from reserves;
    select count(*)from sailors;
    select count(bid)from reserves;
    select count(bid)from boats;
    select max( age) from sailors;
    select min( age) from sailors;
    select sum( rating) from sailors;
    select rating from sailors;
    select avg(rating) from sailors;
    select sname,age from sailors;
    select *from sailors where rating>7;
    select s.sname from sailors s,reserves r where s.sid=r.sid and r.bid=103;
    select r.sid from boats b,reserves r where r.bid=b.bid and b.color='red';
    select s.sname from sailors s,boats b,reserves r where s.sid=r.sid and r.bid=b.bid and b.color='red';
    select b.color from sailors s, reserves r,boats b where s.sid=r.sid and r.bid=b.bid and s.sname='lubber';
    select distinct s.sname from sailors s,reserves r where s.sid=r.sid;
    select distinct s.sname from sailors s,reserves r, boats b where s.sid=r.sid and r.bid=b.bid and(b.color='red'or b.color='green');
    select distinct s.sname from sailors s,reserves r, boats b where s.sid=r.sid and r.bid=b.bid and(b.color='red'and b.color='green');
    select sailors.sid,reserves.bid from sailors left join reserves on sailors.sid=reserves.sid;  
    select sailors.sid,reserves.bid from sailors right join reserves on sailors.sid=reserves.sid; 
    select sailors.sid,boats.bid from reserves right join boats on reserves.bid=boats.bid; 
    
    select distinct s.sid,r.sid ,s.sname from sailors s,reserves r where s.sid=r.sid;
    select *from sailors;
    select  s.sid ,r.sid ,s.sname from sailors s,reserves r where s.sid(+)=r.sid;
    select distinct s.sname from sailors s,reserves r, boats b where s.sid=r.sid and r.bid=b.bid and b.color='red'
    union
    select distinct s1.sname from sailors s1,reserves r1, boats b1 where s1.sid=r1.sid and r1.bid=b1.bid and  b1.color='green';
    select distinct s.sname ,b.color from sailors s,reserves r, boats b where s.sid=r.sid and r.bid=b.bid and b.color='red'
    union
    select distinct s1.sname ,b1.color from sailors s1,reserves r1, boats b1 where s1.sid=r1.sid and r1.bid=b1.bid and  b1.color='green';
    select sid from sailors
    union
    select sid from reserves;
   select distinct s.sid from reserves r,sailors s,boats b where s.sid=r.sid and r.bid=b.bid and b.color='red';
   select distinct s.sid from reserves r,sailors s,boats b where s.sid=r.sid and r.bid=b.bid and b.color='red';
   select distinct s.sid from reserves r,sailors s,boats b where s.sid=r.sid and r.bid=b.bid and b.color='red'
   EXCEPT
   select distinct s.sid from reserves r,sailors s,boats b where s.sid=r.sid and r.bid=b.bid and b.color='green';
   select distinct s.sid from reserves r,sailors s,boats b where s.sid=r.sid and r.bid=b.bid and b.color='green'
   EXCEPT
   select distinct s.sid from reserves r,sailors s,boats b where s.sid=r.sid and r.bid=b.bid and b.color='red';
    select sid from sailors
    INTERSECT
    select sid from reserves;
    select s.sid  from reserves r,sailors s,boats b where s.sid=r.sid and r.bid=b.bid and b.color='red'
    INTERSECT
    select s1.sid from reserves r1,sailors s1,boats b1 where s1.sid=r1.sid and r1.bid=b1.bid and b1.color='green';
select * from reserves;
select sname from sailors;
select count (sid),bid from reserves group by bid;
select count (sid),bid from reserves group by bid having count(sid)>2;
select s.rating,avg(s.age) from sailors s group by s.rating having count(*)>1;
select b.bid,count(*) from boats b,reserves r where r.bid=b.bid and b.color='red' group by b.bid;
select rating ,min(age) from sailors where age>=18 group by rating having count(*)>1;

Comments

Please sign up or log in to contribute to the discussion.