Login

Please fill in your details to login.





the world database

A database for the world
Welcome to The World Database on The Computing Café! This page features an interactive SQLite editor where you can practise your database skills directly in your browser without needing to download external software. As shown in the console, the 
world.db
database is already loaded and ready for you to explore.

The Database Structure (Schema)


The World Database contains seven tables. Below is the structure for each table so you can understand the fields and relationships before querying:

Continent Table: Stores the names of the six continents used in the data model. 
ContinentCode
TEXT UNIQUE (
Primary Key
) 
ContinentName
TEXT

Language Table: Contains common languages together with their ISO 639-2B language codes. 
LanguageCode
TEXT UNIQUE (
Primary Key
) 
LanguageName
TEXT

Region Table: Holds the names of the regions into which the continents are split. 
RegionCode
TEXT UNIQUE (
Primary Key
) 
RegionName
TEXT 
ContinentCode
TEXT (
Foreign Key
linking to the Continent table)

Country Table: Identifies countries by their unique ISO 1366-1 Alpha-3 country codes. 
CountryCode
TEXT UNIQUE (
Primary Key
) 
CountryName
TEXT 
RegionCode
TEXT (
Foreign Key
linking to the Region table)

City Table: Holds the names and populations of 4514 major cities in the World with populations exceeding 100,000, or 90,000 for Europe excluding Russia. 
CityID
INTEGER UNIQUE AUTOINCREMENT (
Primary Key
) 
CityName
TEXT 
CountryCode
TEXT (
Foreign Key
linking to the Country table) 
Population
INTEGER 
Capital
INTEGER (Boolean field) 
Largest
INTEGER (Boolean field)

Spoken Table: Lists 469 different records identifying particular languages spoken in particular countries. 
CountryCode
TEXT (
Foreign Key
, Part of 
Primary Key
) 
LanguageCode
TEXT (
Foreign Key
, Part of 
Primary Key
) 
Official
INTEGER (Identifies if the language is official)

Visited Table: A log of holiday destinations with ratings. 
VisitedID
INTEGER UNIQUE AUTOINCREMENT (
Primary Key
) 
CityID
INTEGER (
Foreign Key
linking to the City table) 
YearVisited
TEXT 
Rating
INTEGER (A score from 0 to 10\)

Querying Challenges


Type your SQL commands into the editor on the left side of the interface and click the play button to see the output. The number of expected rows for each query is provided so you can check if you are correct.

Easy Challenges (Single Table)

For some of these questions, you may need to use the 
LIKE
or the 
LIMIT
operators.

List out the names of all the continents (6 rows).
List out the names of continents in reverse alphabetical order (6 rows).
List out the names of all the capital cities (239 rows).
List out the names of all the capital cities which are also the largest cities in the country (181 rows).
List out the names of all the capital cities which are not the largest cities in the country (58 rows).
State the name and population for Berlin (1 row).
List out the names of all the regions which have the word 'Asia' in their name (6 rows).
List out the name and population for all cities beginning with a letter 'D' and ending with the letter 's' (15 rows).
List out the names of all Languages with a space in their name (10 rows).
List out the names of the top 3 cities and their populations for those cities with populations greater than 8,000,000. This list should be given in decreasing order of population (3 rows).

Medium Challenges (Multiple Tables)

For these examples, you can use the 
INNER JOIN
function or manually join the tables on the primary key and foreign key pairs.

List out the names of all the cities in Pakistan (51 rows).
List out the names of the Regions in Latin America (3 rows).
List out the year visited and city name for all cities that I have visited and given a rating of more than 5 out of 10 in ascending order of year visited (20 rows).
Tell me the name of, and the year I visited, my favourite city in the 'United States' (1 row).
List out the names of all the countries where the official language is Arabic (17 rows).

Hard Challenges (Optional)

List out the names of the regions where Spanish is spoken (5 rows). Hint: Use the 
DISTINCT
keyword.
How many countries did I visit between 1980 and 1990 (inclusive) where 'English' was spoken (1 row). Hint: Use the 
COUNT()
function and the 
DISTINCT
keyword.
List out the total populations of Germany, France and Belgium in decreasing order of total population (3 rows). Hint: Use the 
SUM()
function, the 
AS
alias function, the 
IN
operator and the 
GROUP BY
clause.

Data Manipulation Challenges


Practise altering data in the database. At this level, you only need to concern yourself with inserting, deleting, and updating data. To prove your changes were successful, run a 
SELECT
query before you execute the manipulation query and then again afterwards.

INSERT INTO Queries

Insert a new city called 'Machala' in Ecuador (ECU) with a population of 241300\. It is neither the largest nor the capital city of Ecuador.
Insert another language into the Languages table called 'Klingon' with a language code of 'kli'.

DELETE FROM Queries

Klingon isn't really a language. Delete it from the languages table.
2010 was a bad year for me. Delete details of all the cities that I visited in 2010\.

UPDATE Queries

Change the population of Houma in China (CHN) from 122717 to 225123.
Correct the spelling mistake in the spelling of 'Swedish' in the language table. Its language code is 
swe
.

By completing these challenges, you have built a strong foundation in one of the most critical skills in modern computing - Structured Query Language (
SQL
). You have learnt how to navigate complex relational schemas, extract precise information using multi-table joins, and confidently manipulate live data through insertion, deletion, and updating.

These practical database management skills are highly sought after across the tech sector. Whether you are aiming to become a Data Analyst uncovering trends in user behaviour, a Software Developer building the backend of a new web application, or a Database Administrator securing millions of confidential records, mastering 
SQL
is an essential stepping stone for your future career in technology. Keep experimenting with the editor, and well done on completing The World Database module!
Last modified: September 30th, 2026
The Computing Café works best in landscape mode.
Rotate your device.
Dismiss Warning