The goal of my query is to return the country name and its head of state if it's headofstate has a name starting with A, and the capital of the country has greater than 100,000 people utilizing a nested query.
Here is my query:
SELECT country.name as country,
(SELECT country.headofstate
from country
where country.headofstate like 'A%')
from country, city
where city.population > 100000;
I've tried reversing it, placing it in the where clause etc. I don't get nested queries. I'm just getting errors back, like "subquery returns more than one row" and such. If someone could help me out with how to order it, and explain why it needs to be a certain way, that'd be great.
If it has to be "nested", this would be one way to get your job done:
SELECT o.name AS country, o.headofstate
FROM country o
WHERE o.headofstate like 'A%'
AND ( -- guaranteed to return a single value
SELECT i.population
FROM city i
WHERE i.id = o.capital
) > 100000;
A "nested query" is an odd requirement for the task. A JOIN is simpler and more efficient:
SELECT o.name AS country, o.headofstate
FROM country o
JOIN city i ON i.id = o.capital
WHERE o.headofstate like 'A%'
AND i.population > 100000;