-1

Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is 'Africa'.

CITY.CountryCode and COUNTRY.Code are matching key columns.

The solution is

SELECT city.NAME
FROM city
    INNER JOIN country
    ON city.CountryCode = country.Code
WHERE country.continent = 'Africa';

Why was city.Name was written instead of NAME and also why was the ınner joın was executed by ON city.CountryCode = country.Code?

Why is use of ON city.POPULATION = country.POPULATION and ON city.NAME = country.NAME not working?

City:

+-------------+----------+
| Field       | Type     |
+-------------+----------+
| ID          | int(11)  |
| Name        | char(35) |
| CountryCode | char(3)  |
| District    | char(20) |
| Population  | int(11)  |

COUNTRY:

+----------------+-------------+
| Field          | Type        |
+----------------+-------------+
| Code           | char(3)     |
| Name           | char(52)    |
| Continent      | char(50)    |
| Region         | char(26)    |
| SurfaceArea    | float(10,2) |
| IndepYear      | smallint(6) |
| Population     | int(11)     |
| LifeExpectancy | float(3,1)  |
| GNP            | float(10,2) |
| GNPOld         | float(10,2) |
| LocalName      | char(45)    |
| GovernmentForm | char(45)    |
| HeadOfState    | char(60)    |
| Capital        | int(11)     |
| Code2          | char(2)     |
+----------------+-------------+
philipxy
  • 14,867
  • 6
  • 39
  • 83
Berk Turgut
  • 1
  • 1
  • 1
    It's good programming practice to _qualify_ all column names, especially when several tables are involved. – jarlh Mar 20 '23 at 11:59
  • 1
    Typically, the population of a _city_ is vastly different than the _country_. Also, city names and country names typically do not match either. – Paul T. Mar 20 '23 at 12:04
  • BTW, what happens if you just write `SELECT NAME FROM ...`? – jarlh Mar 20 '23 at 12:21
  • Please either ask re 1 bad query/function with obligatory [mre], including why you think it should return something else or are unsure at the 1st subexpression that it doesn't give what you expect or are stuck, justified by reference to authoritative documentation. The answers to your questions are, that's how the language is defined. You essentially ask us to (re)write a textbook for an unidentified method & bespoke tutorial with no details on what you misunderstand or do or don't understand. That's too broad. [research effort](https://meta.stackoverflow.com/q/261592/3404097) – philipxy Mar 20 '23 at 22:02
  • Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) – philipxy Mar 20 '23 at 22:03
  • [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) [Re relational querying.](https://stackoverflow.com/a/24425914/3404097) – philipxy Mar 21 '23 at 01:00

1 Answers1

2

This example is based on Oracle database, but it really doesn't matter; the same goes in any other database.

Sample data:

SQL> select * from city;

NAME       COU POPULATION
---------- --- ----------
Tripoli    LIB    3000000
Casablanca MOR    3500000
London     UK     9000000

SQL> select * from country;

COD NAME           CONTIN POPULATION
--- -------------- ------ ----------
LIB Libya          Africa    6700000
MOR Morocco        Africa   37000000
UK  United Kingdom Europe   67000000

SQL> select city.name
  2  from city inner join country on city.countrycode = country.code
  3  where country.continent = 'Africa';

NAME
----------
Tripoli
Casablanca

but I can not understand why "city.Name" was writen instead of "NAME"

Because both tables (city and country) have column named name. If you don't qualify it with table name, database engine won't know which one you meant to fetch (city name? Country name?) and will raise an error saying that column name is ambiguous.

SQL> select name
  2  from city inner join country on city.countrycode = country.code
  3  where country.continent = 'Africa';
select name
       *
ERROR at line 1:
ORA-00918: column ambiguously defined

and also why was the "ınner joın" was executed by written "ON city.CountryCode = country.Code".

Well, because of the note you posted: "Note: CITY.CountryCode and COUNTRY.Code are matching key columns."

If it weren't for that note, in a properly designed database you'd see foreign keys that establish referential integrity constraint, where foreign key column (that would be city.countryCode) points to primary key column (country.code in this example). ER model should reveal that information if you don't have create table scripts which also say so.

why the usages of "ON city.POPULATION = country.POPULATION" and "ON city.NAME = country.NAME" are not working ?

SQL> select city.name
  2  from city inner join country on city.population = country.population
  3  where country.continent = 'Africa';

no rows selected

SQL>

Such a query expects that the whole country population resides in one city. Are there such countries/cities? Yes; Vatican City, but that's most probably NOT what this task is about.

The same goes for your other suggested condition (about city.name = country.name).

Littlefoot
  • 131,892
  • 15
  • 35
  • 57