WITH city_counts AS (SELECT COUNT(CITY)          AS total_cities,
                            COUNT(DISTINCT CITY) AS distinct_cities
                     FROM STATION)
SELECT total_cities - distinct_cities AS city_difference
FROM city_counts;
-- city_counts is a CTE (Common Table Expression )
-- it is a temp table, which in this case has just one row


--         Query the list of CITY names from STATION which have vowels (i.e., a, e, i, o, and u) as both their first and last characters.
--             Your result cannot contain duplicates.
SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[aeiouAEIOU].*[aeiouAEIOU]$';

SELECT DISTINCT CITY
FROM STATION
WHERE LOWER(SUBSTR(CITY, 1, 1)) IN ('a', 'e', 'i', 'o', 'u')
  AND LOWER(SUBSTR(CITY, LENGTH(CITY), 1)) IN ('a', 'e', 'i', 'o', 'u');

-- NOT start with vowels
SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[^aeiouAEIOU]'; -- ^ae.. negates the expression


-- NOT end with vowels
SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '[^aeiouAEIOU]$'; -- ^ae.. negates the expression

-- NOT start or END with vowels
SELECT DISTINCT CITY
FROM STATION
WHERE CITY REGEXP '^[^aeiouAEIOU]'
AND CITY REGEXP '[^aeiouAEIOU]$';


-- order by last 3 characters, then by ID
SELECT NAME FROM STUDENTS
WHERE MARKS > 75
ORDER BY SUBSTR(NAME, -3), ID;


SELECT CITY, LENGTH(CITY) AS NAME_LENGTH
FROM STATION
ORDER BY LENGTH(CITY) DESC, CITY ASC
    LIMIT 1;