A couple of sample queries for Mondial (database)

queries.sql

-- Which German cities ('D') are stored in the database?
SELECT *
FROM city
WHERE country = 'D';

-- Which German cities ('D') have more than 100,000 inhabitants?
SELECT *
FROM city
WHERE country = 'D' AND population > 100000;

-- What is the overall population of the EU countries?
-- (27 countries with their population)
SELECT SUM(population)
FROM country, ismember
WHERE country.code = ismember.country 
AND ismember.organization = 'EU'
AND ismember.type = 'member';

-- Copilot (attempt; syntax ok, semantically not that good)
SELECT country, SUM(population)
FROM city
WHERE country 
IN ('D', 'F', 'I', 'E', 'UK', 'S', 'P', 'B', 'A', 'NL', 'PL', 'RO', 'GR', 'H', 'CZ', 'SLO', 'HR', 'BG', 'DK', 'FIN', 'IRL', 'LT', 'LV', 'EST', 'SK', 'SLO', 'CY', 'LUX', 'MT')
GROUP BY country;

--
SELECT name, population, ismember.type
FROM country, ismember
WHERE country.code = ismember.country 
AND ismember.organization = 'EU'
AND ismember.type = 'member';

-- Which organization has an abbreviation that starts with 'E'?
Select *
From organization
Where abbreviation like 'E%'    
Order by abbreviation asc;

-- Which purpose serves the ismember table?
Select *
From ismember;

-- Which countries are a member of 'EU'? 
-- Please provide country code and name.
Select country.code, country.name
From ismember, country
Where ismember.country = country.code
and ismember.organization = 'EU' 
and ismember.type = 'member';