06 Sep 2022

Snippets

Fetch

Fetch names of cities that have a population between 500K and 5M:

SELECT name
FROM city
WHERE population BETWEEN 500000 AND 5000000;

Fetch names of cities that don't miss a rating value:

SELECT name
FROM city
WHERE rating IS NOT NULL;

Fetch names of cities that are in countries with IDs 1, 4, 7, or 8:

SELECT name
FROM city
WHERE country_id IN (1, 4, 7, 8);

Fetch names of cities that start with a 'P' or end with an 's':

SELECT name
FROM city
WHERE name LIKE 'P%'
OR name LIKE '%s';

Fetch names of cities that start with any letter followed by 'ublin' (like Dublin in Ireland or Lublin in Poland):

SELECT name
FROM city
WHERE name LIKE '_ublin';

SELECT co.name, ci.name
FROM city AS ci
JOIN country AS co
ON ci.country_id = co.id;