Using Oracle in select query

Question

Question: Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is 'Asia'.

City table contains fields: CountryCode, Population Country table contains fields: Code, Continent (CITY.CountryCode and COUNTRY.Code are matching key columns.)

I tried the following query: (I know this can be solved using Inner join)

Select sum(city.population) from city
where city.countrycode in (Select code from Country where continent = 'Asia')

Hacker rank gives following error: ERROR at line 3: ORA-00933: SQL command not properly ended

Your syntax look right, so what's the question - how to do it with an inner join? — D Stanley
– D Stanley, Commented Jun 8, 2016 at 13:17
Hackerrank gives following error: ERROR at line 3: ORA-00933: SQL command not properly ended. Also, @jarlh when do we use in and when do we use exist operation? — user2609410
– user2609410, Commented Jun 8, 2016 at 13:22
Maybe Hacker Rank requires an ending semicolon? if this query were run in SQL developer it looks like it would work. — xQbert
– xQbert, Commented Jun 8, 2016 at 13:23
Yes, ; worked. This is stupid. I have an interview with Amazon data engineer role and have a sql test. Syntax for mysql/ oracle (ex. AS alias does not work in hackerrank portal) does not seem consistent on different forums. Any suggestion? — user2609410
– user2609410, Commented Jun 8, 2016 at 13:27

Joel Coehoorn · Accepted Answer · 2025-08-16 17:27:17Z

2

This is the right code

Select sum(city.population) 
from city 
inner join country on country.code = city.countrycode
where country.continent= 'Asia';

edited Aug 16 at 17:27

Joel Coehoorn

419k114 gold badges582 silver badges820 bronze badges

answered May 12, 2021 at 11:30

Dakshitha96

677 bronze badges

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

Comments

AntDC · Accepted Answer · 2016-06-08 13:23:43Z

1

Do you need a semi colon?

Something along these lines......

Per city..........

SELECT City.Name, SUM(City.Population)
FROM City INNER JOIN Country ON Country.Code = City.CountryCode
WHERE
  Country.Continent = 'ASIA'
GROUP BY 
  City.Name;

Per Country & City

SELECT Country.Name, City.Name, SUM(City.Population)
FROM City INNER JOIN Country ON Country.Code = City.CountryCode
WHERE
  Country.Continent = 'ASIA'
GROUP BY 
  Country.Name, City.Name;

Just the Total for ASIA

SELECT SUM(City.Population)
FROM City INNER JOIN Country ON Country.Code = City.CountryCode
WHERE
  Country.Continent = 'ASIA';

edited Jun 8, 2016 at 13:23

answered Jun 8, 2016 at 13:21

AntDC

1,93715 silver badges24 bronze badges

4 Comments

user2609410 Over a year ago

I know this. My question is what is wrong with the query I have written?

user2609410 Over a year ago

Also, what works faster: using the inner join or the query I have written? And why?

AntDC Over a year ago

That would depend on the amount of data in the tables. Stick with the INNER JOIN, it will scale better as your tables grow. Your original query will have to scan through the country table to find the continent....Imagine if you have a lot of countries in there.

user5683823 Over a year ago

From what I've read: it used to be that the "in" syntax would result in slower execution. Since Oracle 10 (I think) the wise folks at Oracle figured out that the "in" syntax is equivalent to a join and the optimizer now does that for you. The performance should be the same. Which you could check for yourself by experimentation.

Joel Coehoorn · Accepted Answer · 2025-08-16 17:27:55Z

1

What we are doing here is querying the city table and finding all matches in the country table by using the identifying field in both those tables:

country.code in your country table

city.countrycode in your city table

Whenever country.code = city.countrycode and country.continent= 'Asia', you will be returned the sum of that population.

Select sum(city.population) 
from city 
inner join country on country.code = city.countrycode
where country.continent= 'Asia';

I also recommend you select the city the population count belongs to:

Select city.name, sum(city.population) 
from city 
inner join country on country.code = city.countrycode
where country.continent= 'Asia';

edited Aug 16 at 17:27

Joel Coehoorn

419k114 gold badges582 silver badges820 bronze badges

answered Oct 6, 2020 at 15:15

Aditya Mhaske

111 bronze badge

1 Comment

Joel Coehoorn Aug 16 at 17:28

The 2nd sample won't work, because it mixes agreggate with individual results without a group by clause.

Fedor · Accepted Answer · 2023-01-10 15:53:10Z

0

Use ; at the end of the code.

Select sum(city.population) from city
where city.countrycode in (Select code from Country where continent = 'Asia');

edited Jan 10, 2023 at 15:53

Fedor

24.8k45 gold badges60 silver badges190 bronze badges

answered Jan 9, 2023 at 9:27

Anjan Chakraborty

1

Comments

Suhas Bachhav · Accepted Answer · 2023-03-30 10:02:31Z

0

SELECT SUM(City.POPULATION) FROM City JOIN Country ON city.countrycode=country.code WHERE country.continent='Asia'

edited Mar 30, 2023 at 10:02

Suhas Bachhav

4231 gold badge8 silver badges30 bronze badges

answered Dec 21, 2022 at 12:07

akshatrana22

11 bronze badge

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Jeremy Caney · Accepted Answer · 2025-08-16 19:02:01Z

-1

select sum(c.population) from city c,country cn
where cn.continent='Asia' and c.countrycode=cn.code;

edited Aug 16 at 19:02

Jeremy Caney

7,798115 gold badges58 silver badges87 bronze badges

answered Aug 16 at 17:07

Sameer Barik

1

2 Comments

Luuk Aug 16 at 17:49

Please do not post unformatted code, without explaining why this is better than the other answers here....

Jeremy Caney Aug 16 at 19:10

To clarify @Luuk’s feedback: Even if it’s formatted—as it is now, per my edit—you should still offer an explanation of what your code does and, since there are established answers, why you prefer your approach.

Collectives™ on Stack Overflow

Using Oracle in select query

6 Answers 6

Comments

4 Comments

1 Comment

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

4 Comments

1 Comment

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related