Basic Select

Revising the Select Query I

Revising the Select Query I | HackerRankHackerRank

select * from CITY
where POPULATION > 100000
and COUNTRYCODE = 'USA';

Revising the Select Query II

Revising the Select Query II | HackerRankHackerRank

select NAME from CITY
where POPULATION > 120000
and COUNTRYCODE = 'USA';

Select All

Select All | HackerRankHackerRank

select * from CITY;

Select By ID

Select By ID | HackerRankHackerRank

select * from CITY
where ID = 1661;

Japanese Cities' Attributes

Japanese Cities' Attributes | HackerRankHackerRank

select * from CITY 
where COUNTRYCODE = 'JPN';

Japanese Cities' Names

Japanese Cities' Names | HackerRankHackerRank

select NAME from CITY
where COUNTRYCODE = 'JPN';

Weather Observation Station 1

Weather Observation Station 1 | HackerRankHackerRank

select CITY, STATE from STATION;

Weather Observation Station 3

Weather Observation Station 3 | HackerRankHackerRank

select distinct CITY from STATION
where mod(ID, 2) = 0;

Weather Observation Station 4

Weather Observation Station 4 | HackerRankHackerRank

select count(CITY) - count(distinct CITY) from STATION;

Weather Observation Station 5

Weather Observation Station 5 | HackerRankHackerRank

select CITY, length(CITY) from(
    select CITY from STATION 
    order by length(CITY), CITY asc) 
where rownum = 1; 

select CITY, length(CITY) from(
    select CITY from STATION 
    order by length(CITY) desc) 
where rownum = 1;

Weather Observation Station 6

Weather Observation Station 6 | HackerRankHackerRank

select distinct CITY from STATION
where lower(substr(CITY,1,1)) in ('a','e','i','o','u');

Weather Observation Station 7

Weather Observation Station 7 | HackerRankHackerRank

select distinct CITY from STATION
where lower(substr(CITY,-1,1)) in ('a','e','i','o','u');

Weather Observation Station 8

Weather Observation Station 8 | HackerRankHackerRank

select distinct CITY from STATION 
where lower(substr(CITY,1,1)) in ('a','e','i','o','u')
and lower(substr(CITY,-1,1)) in ('a','e','i','o','u');

Weather Observation Station 9

Weather Observation Station 9 | HackerRankHackerRank

select distinct CITY from STATION 
where lower(substr(CITY,1,1)) not in ('a','e','i','o','u');

Weather Observation Station 10

Weather Observation Station 10 | HackerRankHackerRank

select distinct CITY from STATION
where lower(substr(CITY,-1,1)) not in ('a','e','i','o','u');

Weather Observation Station 11

Weather Observation Station 11 | HackerRankHackerRank

select distinct CITY from STATION
where lower(substr(CITY,1,1)) not in ('a','e','i','o','u')
or lower(substr(CITY,-1,1)) not in ('a','e','i','o','u');

Weather Observation Station 12

Weather Observation Station 12 | HackerRankHackerRank

select distinct CITY from STATION
where lower(substr(CITY,1,1)) not in ('a','e','i','o','u')
and lower(substr(CITY,-1,1)) not in ('a','e','i','o','u');

Higher Than 75 Marks

Higher Than 75 Marks | HackerRankHackerRank

select Name from STUDENTS
where Marks > 75
order by substr(Name,-3,3), ID asc;

Employee Names

Employee Names | HackerRankHackerRank

select name from Employee
order by name asc;

Employee Salaries

Employee Salaries | HackerRankHackerRank

select name from Employee
where salary > 2000
and months < 10
order by employee_id asc;