You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
49 lines
1.4 KiB
49 lines
1.4 KiB
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;
|