-- What is the most guns that any class of ships has?
select max(c.numguns)
from classes c;

-- What classes have the most guns, and how many guns do they have?
select c.class, c.numguns
from classes c
where c.numguns = (select max(c2.numguns) from classes c2);

select c.class, c.numguns
from classes c
where c.numguns >=ALL (select c2.numguns from classes c2);

select c.class, c.numguns
from classes c
where not (c.numguns <ANY (select c2.numguns from classes c2));

select c.class, c.numguns
from classes c
where not exists (select * from classes c2 where c2.numguns > c.numguns);

-- How many classes are there?
select count(*)
from classes c;

-- How many classes does each country have?
select c.country, count(*)
from classes c
group by c.country;

-- For each country, which classes have the most guns and how many?
select c.country, c.class, c.numguns
from classes c
where c.numguns = (select max(c2.numguns) from classes c2
                    where c2.country = c.country);
                    
-- How many ships does each country have?
select c.country, count(s.launched) as ship_count
from classes c NATURAL LEFT OUTER JOIN ships s
group by c.country
order by ship_count;

-- How many classes are represented by actual ships we know about?
select count(distinct s.class)
from ships s;

--delete from ships where name = 'California';

-- What classes have exactly one ship?
select s.class
from ships s
group by s.class
having count(*) = 1;

-- Which classes have more than 3 post-1921 ships, and how many?
select s.class, count(*)
from ships s
where s.launched > 1921
group by s.class
having count(*) > 3;

-- update ships set launched = launched + 1900 ;

--insert into ships(name, class, launched) values('Olin', 'Science Building', 1991);

--insert into ships(name, class, launched)
select 'New ' || c.class, c.class, 2013
from classes c;