CODEWITHSUNDEEP
sqloracle-database
user2609410
user2609410

Reputation: 319

Using Oracle in select query

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

Upvotes: -1

Views: 4357

Answers (5)

akshatrana22
akshatrana22

Reputation: 1

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

Upvotes: 0

Anjan Chakraborty
Anjan Chakraborty

Reputation: 1

Use ; at the end of the code.

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

Upvotes: 0

Dakshitha96
Dakshitha96

Reputation: 57

This is the right code

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

Upvotes: 1

Aditya Mhaske
Aditya Mhaske

Reputation: 11

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

Upvotes: 1

AntDC
AntDC

Reputation: 1917

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

Upvotes: 1

Related Questions