text: USE ships;
  
  -- 1
  SELECT COUNT(c.CLASS) AS 'classes count' FROM CLASSES AS c
  GROUP BY c.TYPE
  HAVING c.TYPE= 'bb';
  
  -- 2
  SELECT c.CLASS, AVG(c.NUMGUNS) AS 'average guns count' FROM CLASSES AS c
  WHERE c.TYPE= 'bb'
  GROUP BY c.CLASS;
  
  -- 3
  SELECT AVG(C.NUMGUNS) AS 'average guns count' FROM CLASSES AS c
  GROUP BY c.TYPE
  HAVING c.TYPE= 'bb';
  
  -- 4
  SELECT c.CLASS, MIN(s.LAUNCHED) AS 'first year', MAX(s.LAUNCHED) AS 'last year' FROM CLASSES AS c
  JOIN SHIPS AS s
  ON s.CLASS = c.CLASS
  GROUP BY C.CLASS;
  
  -- 5
  SELECT s.CLASS, COUNT(s.NAME) AS 'sunken ships count' FROM SHIPS AS s
  JOIN OUTCOMES AS o
  ON o.SHIP = s.NAME
  WHERE o.RESULT = 'sunk'
  GROUP BY s.CLASS;
  
  -- 6
  SELECT s.CLASS, COUNT(s.NAME) AS 'sunken ships count' FROM SHIPS AS s
  JOIN OUTCOMES AS o
  ON o.SHIP = s.NAME
  WHERE o.RESULT = 'sunk' AND (SELECT COUNT(s1.NAME) FROM SHIPS AS s1 
  							 WHERE s1.CLASS = s.CLASS) > 2
  GROUP BY s.CLASS;
  
  -- 7
  SELECT c.COUNTRY, AVG(c.BORE) AS 'average bore' FROM CLASSES AS c
  JOIN SHIPS AS s
  ON s.CLASS = c.CLASS
  GROUP BY c.COUNTRY;