HackerRank:: SQL Practice 01

02.

[solved]
SELECT NAME FROM CITY
WHERE POPULATION > 120000 AND COUNTRYCODE = "usa"

03.SELECT ALL

SELECT * FROM CITY;

04.SELECT BY ID

SELECT * FROM CITY 
WHERE ID = 1661;

05. Japanese Cities' Attributes

SELECT * FROM CITY
WHERE COUNTRYCODE = 'JPN';

06.Weather Observation Station 3

#방법1
SELECT DISTINCT CITY FROM STATION
WHERE MOD(ID,2)=0 ORDER BY CITY ASC;

#방법2
SELECT DISTINCT CITY FROM STATION
WHERE ID % 2 = 0 ORDER BY CITY ASC;

07.Weather Observation Station 4

SELECT COUNT(CITY)-COUNT(DISTINCT CITY) FROM STATION;

08.Weather Observation Station 5

Query the two cities in STATION with the shortest and longest CITY names, as well as their respective lengths (i.e.: number of characters in the name). If there is more than one smallest or largest city, choose the one that comes first when ordered alphabetically.
The STATION table is described as follows:

Station.jpg

where LAT_N is the northern latitude and LONG_W is the western longitude.

Sample Input

For example, CITY has four entries: DEF, ABC, PQRS and WXY.

Sample Output

ABC 3
PQRS 4
Explanation

When ordered alphabetically, the CITY names are listed as ABC, DEF, PQRS, and WXY, with lengths and . The longest name is PQRS, but there are options for shortest named city. Choose ABC, because it comes first alphabetically.

Note
You can write two separate queries to get the desired output. It need not be a single query.

(SELECT CITY,length(CITY) FROM STATION
ORDER BY length(CITY) ASC, CITY
LIMIT 1)
UNION
(SELECT CITY,length(CITY) FROM STATION
ORDER BY length(CITY) DESC, CITY
LIMIT 1)

09.Weather Observation Station 6

Problem

My Answer

SELECT DISTINCT CITY FROM STATION
WHERE CITY LIKE 'i%' OR 
CITY LIKE 'a%' OR 
CITY LIKE 'e%' OR 
CITY LIKE 'o%' OR 
CITY LIKE 'u%'; 

Note
: 정규 표현식으로도 문제 풀이가능

select distinct city from station where city regexp '^[aeiou]'

10.Weather Observation Station 7

SELECT DISTINCT CITY 
FROM STATION 
WHERE CITY REGEXP '[aeiou]$'

11.Weather Observation Station 9

: 모음을 제외한 CITY 찾기

SELECT DISTINCT CITY FROM STATION
WHERE CITY REGEXP "^[^aeiou]"

12.Weather Observation Station 10

SELECT DISTINCT CITY FROM STATION
WHERE CITY REGEXP '[^aeiou]$'

13.Weather Observation Station 11

SELECT DISTINCT CITY FROM STATION
WHERE CITY REGEXP '^[^aeiou]'
OR CITY REGEXP '[^aeiou]$'

14.Weather Observation Station 12

Problem
Query the list of CITY names from STATION that do not start with vowels and do not end with vowels. Your result cannot contain duplicates.

Input Format

The STATION table is described as follows:

Answer

SELECT DISTINCT CITY FROM STATION
WHERE CITY NOT REGEXP '^[aeiou]'
AND CITY NOT REGEXP '[aeiou]$';

[참고]

Reference

https://junyoung-developer.tistory.com/34
: 정규표현식 정리표