Creating table using select statement from multiple tables

Question

I have this university task to create table using SELECT statement from multiple tables, but it's not as simple... here's basic info:

I'm using 2 tables -

CITY(city_ID, name);
PERSON(person_ID, name, surname, city_ID); 
--city_ID is FK indicating in which city person was born.

Now my task is to create new table

STATISTICS(city_ID, city_name, number_of_births);
--number_of_births is basically a count of people born in each city

Problem is that I have to use only SELECT statement to do so.

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)

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);

Ha! You can try my answer, but I don't think that's the correct syntax for Oracle. If not let me know and I'll delete it. — Dave Zych
– Dave Zych, Commented Jan 2, 2014 at 20:49
I've rewriten it to match oracle dialect and everything seems to work just fine! Thanks a lot mate! — devBem
– devBem, Commented Jan 2, 2014 at 20:55
I'm deleting my answer because it's not valid for Oracle. Add an answer with what you did and accept yours. — Dave Zych
– Dave Zych, Commented Jan 2, 2014 at 21:09

Dave Zych · Accepted Answer · 2014-01-02 20:44:06Z

1

For SQL Server you can do SELECT * INTO. Something like this:

SELECT
    *
INTO Statistics
FROM (
    SELECT 
        city.city_ID, 
        city.name as "city_name", 
        (SELECT COUNT(*) FROM person WHERE person.city_id = city.city_id) as 'number_of_births'
    FROM city
        inner join person on city.city_id = person.city_id
) t1

answered Jan 2, 2014 at 20:44

Dave Zych

22k7 gold badges53 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Maheswaran Ravisankar Over a year ago

SELECT INTO is sql server syntax.. just create table as will do, you got it!

user330315 Over a year ago

That is invalid for Oracle. I can't understand how that got accepted.

Dave Zych Over a year ago

@a_horse_with_no_name I added this answer prior to him adding the Oracle tag. Then he accepted it. I honestly don't know either.

halfer · Accepted Answer · 2018-07-13 07:18:12Z

1

(Posted on behalf of the question author).

Ok, this got really messy. Dave Zych's answer was correct when rewritten in Oracle dialect.

CREATE TABLE Statistics AS SELECT * FROM (
    SELECT DISTINCT
        city.city_ID, 
        city.name AS "City_name",
        (SELECT COUNT(*) FROM person WHERE person.city_ID = city.city_ID) AS "number_of_births"
         FROM city  INNER JOIN person ON city.city_ID = person.city_ID);

answered Jul 13, 2018 at 7:18

community wiki

halfer

Collectives™ on Stack Overflow

Creating table using select statement from multiple tables

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related