


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


/* The rest of these queries illustrate various behaviors in the presense
   of NULLs.  */

insert into sailors values (99, 'Frank', NULL, 23)



select * from sailors



select * 
from sailors 
where rating < 5



select * 
from sailors 
where rating < 5 or rating IS NULL



select * 
from sailors 
where (rating = 5) or not (rating = 5)



select count (rating) from sailors



select s.rating, min (s.age)
from sailors s
group by s.rating



insert into sailors values (98, 'Fred', NULL, 25)



select s.rating, min (s.age)
from sailors s
group by s.rating
order by s.rating desc



select s.rating, min (s.age) as minage
from sailors s
group by s.rating
order by minage



insert into reserves values (99, 104, '10-JAN-99')



/* reservations for sailors not known to be rated 10 */
select * 
from reserves R
where R.sid NOT IN (select S.sid 
                    from sailors S
                    where S.rating = 10)



/* reservations for sailors not known to be rated 10 */
select * 
from reserves R
where NOT EXISTS (select * 
                  from sailors S
                  where S.sid = R.sid and S.rating = 10)



/* reservations for sailors known not to be rated 10 */
select * 
from reserves R
where R.sid IN (select S.sid 
                    from sailors S
                    where S.rating <> 10)


