SQL World database
In deze les ga je oefenen met queries.
Hiervoor gebruik je de database World
SQL World database
In deze les ga je oefenen met queries.
Hiervoor gebruik je de database World
Hier zie je een overzicht van de tabellen in de database World.
Wat zijn de primary keys in de tabellen city, country en countrylanguage?
city.ID, country.Code en countrylanguage.Language
city.Name, Country.Name en city.ID
city.countrycode, country.Name en countrylanguage.CountryCode
country.Language, city.Code en countrylanguage.District
Welke kolommen in de tabellen city en countrylanguage bevatten dezelfde informatie als de kolom country.Code?
Name en Language
Er zijn geen kolommen met dezelfde informatie.
City.ID en countrylanguage.CountryCode
city.CountryCode en countrylanguage.CountryCode
Vraag 1: Toon alles van alle landen
SHOW ALL FROM country;
SELECT country.* FROM world;
SELECT * FROM country;
SELECT * FROM countries;
Vraag 2:
Toon alle steden en hun inwonersaantallen van België
SELECT Name, Population FROM city WHERE CountryCode = 'BEL';
SELECT city.Name, country.Population FROM city JOIN country ON CountryCode = Code WHERE Name = 'Belgium';
SELECT Name, Population FROM city WHERE Name = 'Belgium';
SELECT * FROM city WHERE Country = 'Belgium';
3: Toon alle talen die gesproken worden in Nederland
SELECT Language FROM countrylanguage WHERE Name = 'Netherlands';
SELECT Language FROM countrylanguage JOIN country ON countrylanguage.CountryCode = country.Code WHERE country.Name = 'Netherlands';
select countrylanguage.Language from countrylanguage where countrylanguage.countrycode="NLD";
SELECT Language FROM country WHERE Name = 'Netherlands';
4: Toon de namen en continenten van de landen die meer dan 100 miljoen inwoners hebben
SELECT Name, Continent FROM country HAVING Population > 100000000;
SELECT Name, Continent FROM country WHERE Population >= 100000000;
SELECT Name, Continent FROM country WHERE Population > 100000000;
SELECT Name, Continent FROM country FILTER (Population > 100000000);
5: Toon de gemiddelde levensverwachting van Europa
SELECT AVERAGE(LifeExpectancy) FROM country WHERE Continent = 'Europe';
SELECT AVG(LifeExpectancy) FROM country WHERE Continent = 'Europe';
SELECT MEAN(LifeExpectancy) FROM country WHERE Continent = 'Europe';
SELECT SUM(LifeExpectancy) / COUNT(*) FROM country WHERE Continent = 'Europe';
6: Geef alle informatie terug van het land waarvoor de code ALB is
SELECT Code = 'ALB' FROM country;
SELECT * FROM country WHERE Code = 'ALB';
SELECT * FROM country WHERE Name = 'ALB';
FIND * FROM country WHERE Code = 'ALB';
7: Toon alles van alle steden
SHOW city;
SELECT * FROM city;
SELECT city.* FROM database;
GET * FROM city;
8: Toon de namen van alle landen die een stad met meer dan 10 miljoen inwoners hebben
SELECT country.Name FROM country JOIN city ON country.Code = city.CountryCode WHERE city.Population > 10000000;
SELECT Name FROM country WHERE Population > 10000000 JOIN city;
SELECT DISTINCT country.Name FROM country JOIN city ON country.Code = city.CountryCode WHERE city.Population > 10000000;
SELECT DISTINCT Name FROM country WHERE city.Population > 10000000;
9: Toon alle namen van landen in Afrika
SELECT Name FROM country WHERE Continent LIKE 'Africa';
SELECT Name FROM country WHERE Continent = 'Africa';
SELECT Continent FROM country WHERE Name = 'Africa';
SELECT * FROM country WHERE Continent = Africa;
10: Toon van alle plaatsen die beginnen met een Z de inwoners van de stad én die van het land (gescheiden door een pipe-symbool |) en noem dit "Aantallen"
SELECT city.Population + ' | ' + country.Population AS Aantallen FROM city JOIN country ON city.CountryCode = country.Code WHERE city.Name = 'Z%';
SELECT CONCAT(city.Population, country.Population) AS Aantallen FROM city JOIN country ON city.CountryCode = country.Code WHERE city.Name LIKE 'Z%';
SELECT city.Population, country.Population AS Aantallen FROM city JOIN country ON city.CountryCode = country.Code WHERE city.Name LIKE 'Z%';
SELECT CONCAT(city.Population, ' | ', country.Population) AS Aantallen FROM city JOIN country ON city.CountryCode = country.Code WHERE city.Name LIKE 'Z%';