title: Query to find for each CEO, the number of store types, and the max item number among its cities
body: <pre><code>Store_Type  CEO_Name       *Table Store*        
  Store1      CEO1            
  Store2      CEO1            
  Store3      CEO2            
  Store4      CEO2            
  Store5      CEO2            
  Store6      CEO3            
  
  
  
  City        Store_Type  Items          *Table City*
  NY          Store1      8000000     
  DC          Store1      600000      
  Philly      Store1      1500000     
  Ottawa      Store2      800000      
  Tortonto    Store2      2500000     
  Berlin      Store4      3500000     
  Hamburg     Store4      2000000     
  Bonn        Store4      300000      
  Paris       Store5      2000000     
  Lyon        Store5      700000      
  Bamako      Store6      2000000     
  Timbuktu    Store6      50000       
  Mopti       Store6      100000      
  </code></pre>
  
  <p>From inspecting the tables, the result should be similar to this: The output is for each CEO, it list's the number of store types for that CEO and the max item number among its cities</p>
  
  <pre><code>CE01        2             8000000
  CEO2        2 (3?)        3500000
  CE03        1             2000000
  </code></pre>
  
  <p>I have tried multiple ways to do this and failed. I know that I can use COUNT(Store_Type) for counting how many store types each CEO has, but since there are duplicates, it's getting difficult to make this 3 column table so to speak. Then MAX(items) will be used but it has to be the max for the CEO as a whole.  </p>
  
  <p>I tried breaking it up but I kept getting group errors continuously. Here are some examples I managed to write down but I did try multiple brute forces in the command line once I thought I had an "epiphany" and changed tactics so I don't have those saved. Those mainly encompassed lots of tries with joins but I still couldn't achieve what I wanted to do. Hence, why I am here now asking a simple SQL question.</p>
  
  <pre><code>    select distinct B.CEO_Name
      from Store B, City C
      where B.Store_Type=C.Store_Type;
      -----
      select distinct B.CEO_Name
      from Store B, City C
      where B.Store_Type=C.Store_Type;
      INTERSECT 
      select COUNT(distinct Store_Type)
      from City;
      -----
      select distinct C.Store_Type, D.CEO, COUNT(distinct C.Store_Type)
      from City C, Store D
      where C.Store_Type=D.Store_Type;
      ------
      select D.CEO, COUNT(C.Store_Type)
      from Store D, City C
      where D.Store_Type=C.Store_Type;
      ------
      select D.CEO, COUNT(C.Store_Type)
      from Store D full outer join City C
      on (D.Store_Type=C.Store_Type);
  </code></pre>