Pavol Pidanič

Hackerrank – Weather Observation Station 10

Problem Statement A description of the problem can be found on Hackerrank. Solution As Weather Observation Station 7, only the condition is opposite. I created solution in: SQL All solutions are also available on my GitHub. SQL

select distinct city
  from station
where 
  substr(lower(city), length(city), 1) not in ('a', 'e', 'i', 'o', 'u')
  order by city;

select distinct city

from station

where

substr(lower(city), length(city), 1) not in ('a', 'e', 'i', 'o', 'u')

order by city;

Hackerrank – Weather Observation Station 11

Problem Statement A description of the problem can be found on Hackerrank. Solution As Weather Observation Station 8, only the condition is opposite. I created solution in: SQL All solutions are also available on my GitHub. SQL

select distinct city
  from station
where 
  substr(lower(city), 1, 1) not in ('a', 'e', 'i', 'o', 'u')
     or
  substr(lower(city), length(city), 1) not in ('a', 'e', 'i', 'o', 'u')
  order by city;

select distinct city

from station

where

substr(lower(city), 1, 1) not in ('a', 'e', 'i', 'o', 'u')

substr(lower(city), length(city), 1) not in ('a', 'e', 'i', 'o', 'u')

order by city;

Hackerrank – Weather Observation Station 12

Problem Statement A description of the problem can be found on Hackerrank. Solution Only difference between Weather Observation Station 11 is ‘AND’. I created solution in: SQL All solutions are also available on my GitHub. SQL

select distinct city
  from station
where 
  substr(lower(city), 1, 1) not in ('a', 'e', 'i', 'o', 'u')
     and
  substr(lower(city), length(city), 1) not in ('a', 'e', 'i', 'o', 'u')
  order by city;

select distinct city

from station

where

substr(lower(city), 1, 1) not in ('a', 'e', 'i', 'o', 'u')

and

substr(lower(city), length(city), 1) not in ('a', 'e', 'i', 'o', 'u')

order by city;

Hackerrank – Higher Than 75 Marks

Problem Statement A description of the problem can be found on Hackerrank. Solution I created solution in: SQL All solutions are also available on my GitHub. SQL

select name from 
(
    select name, substr(name, length(name)-2) as sub_name 
    from Students
    where marks > 75 order by sub_name, id asc
);

select name from

(

select name, substr(name, length(name)-2) as sub_name

from Students

where marks > 75 order by sub_name, id asc

);

Hackerrank – Japanese Cities’ Detail

Problem Statement A description of the problem can be found on Hackerrank. Solution Select all columns and filter if CoutryCode equals ‘JPN’. I created solution in: SQL All solutions are also available on my GitHub. SQL

select * from City where CountryCode = 'JPN';

1	select * from City where CountryCode = 'JPN';

Hackerrank – Japanese Cities’ Name

Problem Statement A description of the problem can be found on Hackerrank. Solution Select only Names and filter by CountryCode equals to ‘JPN’. I created solution in: SQL All solutions are also available on my GitHub. SQL

select Name from City where CountryCode = 'JPN';

1	select Name from City where CountryCode = 'JPN';

Hackerrank – Revising the Select Query – 2

Problem Statement A description of the problem can be found on Hackerrank. Solution The difference between Revising the Select Query – 1 is that you select only name column and filter by same conditions, only the population number is higher. I created solution in: SQL All solutions are also available on my GitHub. SQL

Select name from City
where CountryCode='USA' and population > 120000;

1 2	Select name from City where CountryCode='USA' and population > 120000;

Hackerrank – Revising the Select Query – 1

Problem Statement A description of the problem can be found on Hackerrank. Solution Select all columns and filter by CountryCode and Population. I created solution in: SQL All solutions are also available on my GitHub. SQL

Select name from City
where CountryCode='USA' and population > 120000;

1 2	Select name from City where CountryCode='USA' and population > 120000;

Hackerrank – Select All

Problem Statement A description of the problem can be found on Hackerrank. Solution Simple. Just select all rows and columns. I created solution in: SQL All solutions are also available on my GitHub. SQL

select * from City;

1	select * from City;

Hackerrank – Select by ID

Problem Statement A description of the problem can be found on Hackerrank. Solution Select all rows and filter by value if ID column equals to 1661. I created solution in: SQL All solutions are also available on my GitHub. SQL

select * from City where ID = 1661;

1	select * from City where ID = 1661;