-- Nested Queries

-- Find the names of sailors who have reserved boat 103
select s.sname
from sailors s
where s.sid in (select r.sid
				from reserves r
                where r.bid = 103);

select s.sid, s.sname
from sailors s
where s.sid not in (22, 63, 64);

-- Find the names of sailors who have reserved a red boat
select s.sname
from sailors s
where s.sid in (
	select r.sid
    from reserves r 
    where r.bid in (
		select b.bid
        from boats b
        where b.color = 'red'
    )
);

-- Find the names of sailors who have not reserved a red boat
select s.sname
from sailors s
where s.sid not in (
	select r.sid
    from reserves r 
    where r.bid in (
		select b.bid
        from boats b
        where b.color = 'red'
    )
);

-- Correlated Nested Queries
-- Find the names of sailors who have reserved boat number 103
select s.sname
from sailors s
where exists (
	select *
    from reserves r 
    where r.bid = 103
			and r.sid = s.sid
);

-- Set-Comparison Operators
-- Find sailors whose rating is better than some sailor called Horatio
select s.sid
from sailors s
where s.rating > ANY (
	select s2.rating
    from sailors s2
    where s2.sname = 'Horatio'
);

-- Find sailors whose rating is better than every sailor called Horatio
select s.sid
from sailors s
where s.rating > ALL (
	select s2.rating
    from sailors s2
    where s2.sname = 'Horatio'
);

-- Find the sailors with the highest rating
select s.sid
from sailors s
where s.rating >= ALL (
	select s2.rating
    from sailors s2
);

-- More Nested Queries
-- Find the names of sailors who have reserved both a red and a green boat
select s.sname
from sailors s, reserves r, boats b
where s.sid = r.sid and r.bid = b.bid and b.color = 'red'
		and s.sid in (
			select s2.sid
            from sailors s2, boats b2, reserves r2
            where s2.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 boats b, reserves r
    where r.bid = b.bid and b.color = 'red')
    intersect
    (select r2.sid
    from boats b2, reserves r2
    where r2.bid = b2.bid and b2.color = 'green')
);

-- Find the names of sailors who have reserved all boats
select s.sname
from sailors s
where not exists (
	(select b.bid
    from boats b)
    except
    (select r.bid
    from reserves r
    where r.sid = s.sid)
);

select s.sname
from sailors s
where not exists (
	select b.bid
    from boats b
    where not exists (
		select r.bid
        from reserves r
        where r.bid = b.bid
				and r.sid = s.sid
    )
);

-- Aggregate Functions
-- COUNT, SUM, AVG, MAX, MIN
-- 
-- Find the average age of all sailors
select avg(s.age)
from sailors s;

-- Find the average age of sailors with a rating 10
select avg(s.age)
from sailors s
where s.rating = 10;

-- Find the name and age of the oldest sailor
select s.sname, max(s.age)
from sailors s; -- illegal

select s.sname, s.age
from sailors s
where s.age = (
	select max(s2.age)
    from sailors s2
);

-- Count the number of sailors
select count(*)
from sailors s;

-- Count the number of different sailor names
select count(distinct s.sname)
from sailors s;

-- Find the names of sailors who are older than the oldest sailor with a rating of 10
select s.sname
from sailors s
where s.age > (
	select max(s2.age)
    from sailors s2
    where s2.rating = 10
);

select s.sname
from sailors s
where s.age > all (
	select s2.age
    from sailors s2
    where s2.rating = 10
);