title: Creating table using select statement from multiple tables
body: <p>I have this university task to create table using SELECT statement from multiple tables, but it's not as simple... here's basic info:</p>
  
  <p>I'm using 2 tables - </p>
  
  <pre><code>CITY(city_ID, name);
  PERSON(person_ID, name, surname, city_ID); 
  --city_ID is FK indicating in which city person was born.
  </code></pre>
  
  <p>Now my task is to create new table </p>
  
  <pre><code>STATISTICS(city_ID, city_name, number_of_births);
  --number_of_births is basically a count of people born in each city
  </code></pre>
  
  <p>Problem is that I have to use only SELECT statement to do so. </p>
  
  <p>I've tried something like this: (I'm well aware that this cannot possibly work but as to give you a better idea where I'm stuck)</p>
  
  <pre><code>CREATE TABLE Statistics AS 
  (SELECT city.city_ID, city.name as "city_name", number_of_births AS 
                  (SELECT COUNT(*) FROM person WHERE person.city_id = city.city_id)
   FROM city, person);
  </code></pre>