[HackerRank] SQL Basic (easy)(1)

1.Revising the select query 2

https://www.hackerrank.com/challenges/revising-the-select-query-2/problem?isFullScreen=true

Revising the Select Query II | HackerRank

Query the NAME field for all American cities in the CITY table with populations larger than 120000. The CountryCode for America is USA.

The CITY table is described as follows

SELECT 
    NAME
FROM CITY
WHERE COUNTRYCODE = 'USA'
    AND POPULATION > 120000;

 

2. SELECT All

https://www.hackerrank.com/domains/sql?filters%5Bskills%5D%5B%5D=SQL%20%28Basic%29&filters%5Bdifficulty%5D%5B%5D=easy

Solve SQL Code Challenges

SELECT 
	*
FROM 
	CITY;

 

3.  Select By ID

https://www.hackerrank.com/challenges/select-by-id/problem?isFullScreen=true

Select By ID | HackerRank

Query all columns for a city in CITY with the ID 1661.

The CITY table is described as follows:

뜻: CITY 테이블에서 특정 도시에 대한 모든 컬럼을 조회하라

조건: ID가 1661인

SELECT 
    *
FROM 
    CITY
WHERE 
    ID = 1661;

 

4. Japanese Cities' Attributes

https://www.hackerrank.com/challenges/japanese-cities-attributes/problem?isFullScreen=true

Japanese Cities' Attributes | HackerRank

Query all attributes of every Japanese city in the CITY table. The COUNTRYCODE for Japan is JPN.

The CITY table is described as follows:

attributes(속성) = 컬럼

SELECT
    *
FROM 
    CITY
WHERE COUNTRYCODE = 'JPN';

 

5. Japanese Cities'Names

https://www.hackerrank.com/challenges/japanese-cities-name/problem?isFullScreen=true

Japanese Cities' Names | HackerRank

Query the names of all the Japanese cities in the CITY table. The COUNTRYCODE for Japan is JPN.
The CITY table is described as follows:

SELECT 
    NAME
FROM
    CITY
WHERE COUNTRYCODE = 'JPN';

 

6. Weather Observation Station 1

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

Weather Observation Station 1 | HackerRank

Query a list of CITY and STATE from the STATION table.
The STATION table is described as follows:

Query a list of :해당 컬럼의 모든 값을 쭉 나열해서 보여줘라

SELECT 
    CITY,
    STATE
FROM
    STATION;

 

7.Weather Observation Station 2

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

Weather Observation Station 2 | HackerRank

Query the following two values from the STATION table:

1. The sum of all values in LAT_N rounded to a scale of  2 decimal places.
2. The sum of all values in LONG_W rounded to a scale of  2 decimal places.

SELECT 
    ROUND(SUM(LAT_N),2)  AS lat,
    ROUND(SUM(LONG_W), 2) AS lon
FROM STATION

POINT!

rounded to a scale of 2 decimal places

: 소수점 이하 둘째자리까지 반올림해서 표시하라

• scale: 소수점 이하 자리 수
• decimal places : 소수자리

8. Weather Observation Station 3

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

Weather Observation Station 3 | HackerRank

Query a list of CITY names from STATION for cities that have an even ID number. Print the results in any order, but exclude duplicates from the answer.
The STATION table is described as follows:

SELECT 
    DISTINCT CITY AS CITY
FROM 
    STATION
WHERE MOD(ID, 2) = 0;

문제 해석

• Query a list of CITY names → CITY 컬럼 값(도시 이름)만 가져와라
• from STATION → STATION 테이블에서
• for cities that have an even ID number → ID 값이 짝수인 도시만
  • ( odd number : 홀수)
• Print the results in any order → 출력 순서는 상관없음
  • ascending order : 오름차순
  • descending order : 내림차순
• but exclude duplicates from the answer → 중복은 제거해야 함 → DISTINCT 사용

주의할 점

ID값이 짝수인 도시만이라는 조건에서 처음에는 ID % 2 = 0 이라고 했었는데 오답이 나왔습니다.

이 문제가 Orakle로 채첨이 되는 것으로 추측되어 mod()를 사용했더니 정답 처리가 되더라구요    

 

9. Weather Observation Station 4

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

Weather Observation Station 4 | HackerRank

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:

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

문제 해석

• total number of CITY entries → CITY 컬럼의 전체 개수 (COUNT(CITY))
• number of distinct CITY entries → CITY 컬럼의 서로 다른 값 개수 (COUNT(DISTINCT CITY))
• Find the difference → 두 개의 차이를 구하라 (빼기 연산)

entries:행에 들어있는 값 하나하나

 

10. Weather Observation Station 6

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

Weather Observation Station 6 | HackerRank

Query the list of CITY names starting with vowels (i.e., a, e, i, o, or u) from STATION. Your result cannot contain duplicates.

Input Format

The STATION table is described as follows:

SELECT 
	DISTINCT CITY 
FROM 
	STATION
WHERE UPPER(SUBSTR(CITY, 1, 1)) IN ('A','E','I','O','U');

 

문제 해석

• Query the list of CITY names
  → CITY 컬럼의 값(도시 이름) 목록을 가져와라.
• starting with vowels (i.e., a, e, i, o, or u)
  → 첫 글자가 모음(a, e, i, o, u)인 도시만.
• from STATION
  → STATION 테이블에서.
• Your result cannot contain duplicates
  → 결과에 중복이 있으면 안 된다 → DISTINCT 사용.

11. Weather Observation Station 7

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

Weather Observation Station 7 | HackerRank

Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION. Your result cannot contain duplicates.

Input Format

The STATION table is described as follows:

SELECT 
    DISTINCT CITY
FROM 
    STATION
WHERE LOWER(SUBSTR(CITY, length(RTRIM(CITY)), 1)) IN ('a','e','i','o','u');

바로 전 문제가 첫 글자가 모음으로 시작하는  도시를 추출하는 거였죠

이번 문제는 끝글자가 모음으로 끝나는 도시를 추출하는거라서  시작위치를 -1로 설정해 줬는데요

SELECT 
	DISTINCT CITY 
FROM 
	STATION
WHERE LOWER(SUBSTR(CITY, -1, 1)) IN ('A','E','I','O','U');

 

실행이 안됩니다. ㅎㅎ 

point 1) 오라클의 특성

MySQL / SQLite → SUBSTR() 시작 위치에 -n을 쓰면 뒤에서부터 n번째 글자부터 추출 가능

Oracle / 일부 DB → 음수 인덱스가 없거나 동작이 달라서, 반드시 정확한 위치를 계산해야 함

• LENGTH() 함수로 전체 길이 구하기
• 뒤에서 부터  n번째 글자 

SUBSTR(CITY, LENGTH(CITY), 1)          -- 마지막 글자
SUBSTR(CITY, LENGTH(CITY)-2, 3)        -- 마지막 3글자

 

point 2) 공백 이슈

SUBSTR로 풀 때 공백을 주의해야합니다!

지금 처럼 맨 마지막 글자를 추출하려고 할때 공백이 있다면 실행이 안됩니다.

그래서 RTRIM()을 써서 문자열 오른쪽 끝의 불필요한 공백을 제서 후 SUBSTR()을 적용해야합니다.

 

공백 제거 함수

RTRIM() → 오른쪽 끝 공백 제거
LTRIM() → 왼쪽 시작 공백 제거
TRIM() → 양쪽 공백 제거