HackerRank SQL Challenges

Revising The Select Query I

SELECT
  id
, name
, countrycode
, district
, population
FROM
  city
WHERE
  population >= 100000
AND
  countrycode = 'USA';

We can replace all column names with the star *, but it is more readable if all column names are explicitly stated in the query.

Also, we could use LIKE in place of = for this case: …​ AND countrycode LIKE 'USA'.

Revising the Select Query II

SELECT
  name
FROM
  city
WHERE
  population >= 120000
    AND countrycode = 'USA';

Select All

SELECT
  id
, name
, countrycode
, district
, population
FROM
  city;

Again, writing the column names explicitly makes it more self-documenting than simply using *.

Select By ID

SELECT
  id
, name
, countrycode
, district
, population
FROM
  city
WHERE
  id = 1661;

Japanese Cities' Attributes

SELECT
  id
, name
, countrycode
, district
, population
FROM
  city
WHERE
  countrycode = 'JPN';

Weather Observation Station 1

SELECT
    city
  , state
FROM station;

Weather Observation Station 2

SELECT
  ROUND(SUM(lat_n), 2) AS lat
, ROUND(SUM(long_w), 2) AS lon
FROM station;

We can SUM any numeric column, e.g.:

SELECT SUM(id) FROM users;

And also ROUND(value, num_decimal_places), e.g.:

psql session
mydb=# SELECT ROUND(3.141592653589793, 5);
  round
---------
 3.14159

mydb=# SELECT ROUND(3.14159, 2) AS PI;
  pi
------
 3.14

So we basically round the result of the sum and rename the output columns as lat and lon.

Weather Observation Station 5

(
  SELECT
      city
    , LENGTH(city) AS len
  FROM station
  ORDER BY len DESC, city ASC
  LIMIT 1
) UNION ALL (
  SELECT
      city
    , LENGTH(city) AS len
  FROM station
  ORDER BY len ASC, city ASC
  LIMIT 1
);

We basically have two queries. One that selects the city with lengthier name (ORDER BY len DESC), and another that selects the city with the shortest name (ORDER BY len ASC).

We also sort by city name in ascending order so if multiple cities have the same max or min length, we choose the first one alphabetically.

For each query, we limit by 1.

Finally, the UNION ALL produces the final tabular structure with the result of the two individual queries.

Weather Station Observation 7

Solution 1 using ORs

Simply using a lot of OR's to match city names ending in vowels.

SELECT DISTINCT city
FROM station
WHERE city LIKE '%a'
OR city LIKE '%e'
OR city LIKE '%i'
OR city LIKE '%o'
OR city LIKE '%u';

Case sensitivity depends on a few things like collation and/or other configurations. This solution worked on HackerRank so we are fine with it.

Solution 2 using REGEXP_LIKE

Very standard regex syntax. Using “or” (|) with grouping and matching the “end of string” with $.

SELECT DISTINCT city
FROM station
WHERE REGEXP_LIKE(city, '(a|e|i|o|u)$')