HackerRank 해커랭크 SQL(Basic) 뽀개기 (1) + LENGTH()

sql 코테 대비..

전에 공부하면서 풀었던 문제 제외

 

1. https://www.hackerrank.com/challenges/weather-observation-station-9/problem?isFullScreen=true

Weather Observation Station 9 | HackerRank

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

Input Format

The STATION table is described as follows:

A:

SELECT DISTINCT city
FROM station
WHERE city NOT LIKE 'a%'
AND city NOT LIKE 'e%'
AND city NOT LIKE 'i%'
AND city NOT LIKE 'o%'
AND city NOT LIKE 'u%'

 

 

2. https://www.hackerrank.com/challenges/weather-observation-station-4/problem?isFullScreen=true

Weather Observation Station 4 | HackerRank

Q: Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.
The STATION table is described as follows:

A:

SELECT COUNT(city) - COUNT(DISTINCT(city))
FROM station

 

 

문자열 길이 출력하는 함수 LENGTH(), CHAR_LENGTH()

• LENGTH()

SELECT LENGTH(칼럼명1), LENGTH(칼럼명2)
FROM 테이블명

-- LENGTH 함수는 띄어쓰기도 포함한다.
-- 한글의 경우 BYTE길이를 가져오기 때문에 값이 '서진'이여도 2가 아닌 5를 출력한다.

 

• CHAR_LENGTH()

-- BYTE수를 계산하지 않고 단순히 문자가 몇 개인지를 가져오고 싶을 때,
SELECT CHAR_LENGTH(칼럼명1), CHAR_LENGTH(칼럼명2)
FROM 테이블명
-- 얘도 마찬가지로 공백은 포함한다.

 

3. https://www.hackerrank.com/challenges/weather-observation-station-5/problem?isFullScreen=true

Weather Observation Station 5 | HackerRank

Q: 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:

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

Sample Output

ABC 3
PQRS 4

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

 

A:

SELECT city, LENGTH(city)
-- sample output에 도시 이름과 길이를 함께 출력하라고 되어있음.
FROM station
ORDER BY LENGTH(city) ASC, city ASC
-- 길이로도, 알파벳순으로도 제일 짧은거
LIMIT 1;

-- 쿼리를 2개로 분리해서 작성해도 된다고 함.

SELECT city, LENGTH(city)
FROM station
ORDER BY LENGTH(city) DESC, city DESC
LIMIT 1;

 

 

4. https://www.hackerrank.com/challenges/weather-observation-station-10/problem?isFullScreen=true

Weather Observation Station 10 | HackerRank

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

Input Format

The STATION table is described as follows:

A:

SELECT DISTINCT city
FROM station
WHERE city NOT LIKE '%a'
AND city NOT LIKE '%e'
AND city NOT LIKE '%i'
AND city NOT LIKE '%o'
AND city NOT LIKE '%u'

-- NOT LIKE일 때 AND