create table sailors1 (sid tinyint primary key, sname char(9), rating tinyint,
	age tinyint)

create table sailors (sid tinyint primary key, sname char(9), rating tinyint,
	age tinyint)

create table boats (bid tinyint primary key, bname char(10), color char(8))

create table reserves (sid tinyint not null references sailors(sid), bid tinyint not 
	null references boats (bid), day smalldatetime not null)

alter table reserves add primary key (sid, bid, day)

create table reserves1 (sid tinyint not null references sailors1(sid), bid tinyint not 
	null references boats (bid), day smalldatetime not null)

alter table reserves1 add primary key (sid, bid, day)

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')

insert into sailors1 values (22, 'dustin', 7, 45)
insert into sailors1 values (31, 'lubber', 8, 55)
insert into sailors1 values (58, 'rusty', 10, 35)

insert into sailors values (28, 'Horatio', 9, 35)
insert into sailors values (31, 'lubber', 8, 55)
insert into sailors values (58, 'rusty', 10, 35)
insert into sailors values (44, 'Horatio', 5, 35)
insert into sailors values (56, 'Bob', 10, 42)
insert into sailors values (64, 'BOB', 3, 19)
insert into sailors values (67, 'Bob', 8, 43)
insert into sailors values (47, 'George', 10, 35)

insert into reserves values (47, 101, '10-OCT-1996')
insert into reserves values (58, 103, '11-NOV-1996')
insert into reserves values (58, 104, '11-OCT-1999')
insert into reserves values (44, 104, '10-DEC-1996')
insert into reserves values (44, 101, '10-NOV-1998')

insert into reserves1 values (22, 101, '10-OCT-1996')
insert into reserves1 values (58, 103, '11-NOV-1996')

select * from sailors1, reserves1

select * from sailors1 S, reserves1 R
where S.sid=R.sid and R.bid = 103

select s.sname 
from sailors1 S, reserves1 R
where S.sid=R.sid and R.bid = 103

select s.sname 
from sailors S, reserves R
where S.sid=R.sid and bid = 103

select sname 
from sailors, reserves
where sailors.sid=reserves.sid and bid = 103

select s.sid 
from sailors S, reserves R
where S.sid = R.sid

select S.age, age1=S.age-5, 2*S.age AS age2
from sailors S
where S.sname LIKE 'B_%B'

select S.sid 
from sailors S, boats B, reserves R
where S.sid = R.sid and R.bid = B.bid and (B.color = 'red' or B.color = 'green')

select S.sid 
from sailors S, boats B, reserves R
where S.sid = R.sid and R.bid = B.bid and B.color = 'red'
	UNION
select S.sid 
from sailors S, boats B, reserves R
where S.sid = R.sid and R.bid = B.bid and B.color = 'green'

select S.sid 
from sailors S, boats B1, reserves R1, boats B2, reserves R2
where S.sid = R1.sid and R1.bid = B1.bid and B1.color = 'red'
	and S.sid = R2.sid and R2.bid = B2.bid and B2.color = 'green'

select S.sname
from sailors S
where S.sid IN (select R.sid 
		from reserves R
		where R.bid = 103)

select S.sname
from sailors S
where EXISTS (select * 
		from reserves R
		where R.bid = 103 and S.sid = R.sid)

select *
from sailors S
where S.rating > SOME (select S2.rating 
			from sailors S2
			where S2.sname = 'Horatio')

select count (*)
from sailors S

select avg (S.age)
from sailors S
where S.rating = 10
 
select count (distinct S.rating)
from sailors S
where S.sname = 'Bob'

select S.sname
from sailors S
where S.rating = (select max (S2.rating)
			from sailors S2)

select avg (distinct S.age)
from sailors S
where S.rating = 10

select S.sname, S.age
from sailors S
where S.age = (select max (S2.age)
		from sailors S2)

select S.sname, S.age
from sailors S
where (select max (S2.age)
	from sailors S2) = S.age

select S.rating, min (S.age)
from sailors S
where S.age >= 18
group by S.rating
having count (*) > 1

select B.bid, count (*) as rcount
from sailors S, boats B, reserves R
where S.sid = R.sid and R.bid = B.bid and B.color = 'red'
group by B.bid

select sname, age
from sailors
order by sname

select sname, age
from sailors
order by sname, age