Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '^[AEIOUaeiou]';
Query the list of CITY names from STATION that do not start with vowels. Your result cannot contain duplicates.
SELECT DISTINCT CITY FROM STATION WHERE CITY NOT REGEXP '^[AEIOUaeiou]';
Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.
SELECT DISTINCT CITY FROM STATION WHERE CITY REGEXP '[AEIOUaeiou]$';
Query the list of CITY names from STATION that do not end with vowels. Your result cannot contain duplicates.
SELECT DISTINCT CITY FROM STATION WHERE CITY NOT REGEXP '[AEIOUaeiou]$';
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]$';
Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.
SELECT DISTINCT CITY FROM STATION WHERE CITY NOT REGEXP '^[AEIOUaeiou]' OR CITY NOT REGEXP '[AEIOUaeiou]$';
🔍 REGEXP BREAKDOWN
^ start of string
$ end of string
.* any character in between
P(R) represents a pattern drawn by Julia in R rows. The following pattern represents P(5):
* * * * *
* * * *
* * *
* *
*
Write a query to print the pattern P(20).
SET @n = 21;
SELECT REPEAT('* ', @n := @n - 1) FROM information_schema.tables LIMIT 20;
:= is assignment operator in MySQL
information_schema.tables is a system table. It contains many rows. We use it only to generate rows. The actual table data does not matter.
Write a query to print all prime numbers less than or equal to 1000. Print your result on a single line, and use the ampersand (&) character as your separator (instead of a space).
SET @n = 1;
SET @primes = '';
WHILE @n <= 1000 DO
SET @is_prime = 1;
SET @i = 2;
WHILE @i <= SQRT(@n) DO
IF @n % @i = 0 THEN
SET @is_prime = 0;
END IF;
SET @i = @i + 1;
END WHILE;
IF @is_prime = 1 AND @n > 1 THEN
SET @primes = CONCAT(@primes, @n, '&');
END IF;
SET @n = @n + 1;
END WHILE;
SELECT LEFT(@primes, LENGTH(@primes) - 1);
Hackerrank accepted solution:
SELECT GROUP_CONCAT(n SEPARATOR '&')
FROM (
SELECT a.n
FROM (
SELECT @n := @n + 1 AS n
FROM information_schema.tables t1,
information_schema.tables t2,
(SELECT @n := 1) temp
LIMIT 1000
) a
WHERE a.n > 1
AND NOT EXISTS (
SELECT 1
FROM (
SELECT @i := @i + 1 AS i
FROM information_schema.tables,
(SELECT @i := 1) temp2
LIMIT 1000
) b
WHERE b.i > 1
AND b.i <= SQRT(a.n)
AND a.n % b.i = 0
)
) primes;