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';

Join

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

Domain not in Email

AND email NOT LIKE '%' || domain || '%';

is used to exclude rows where the email field contains the domain field as a substring.

Explanation:
1. email: Refers to the email address of a person (e.g., user@example.com).
2. domain: Refers to a domain name (e.g., example.com).
3. NOT LIKE: Ensures that the condition excludes rows where email contains the domain.
4. ||: The string concatenation operator in SQLite.
• '% || domain || '%': Constructs a pattern where the domain can appear anywhere in the email (e.g., %example.com%).