Revising the Select Query I

https://www.hackerrank.com/challenges/revising-the-select-query/problem

SELECT *
FROM CITY
WHERE COUNTRYCODE = "USA" AND POPULATION > 100000

Revising the Select Query II

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

Select All

SELECT * 
FROM CITY

Select By ID

SELECT *
FROM CITY
WHERE ID = 1661

Japanese Cities' Attributes

SELECT *
FROM CITY
WHERE COUNTRYCODE = "JPN"

Japanese Cities' Names

SELECT NAME
FROM CITY
WHERE COUNTRYCODE = "JPN"

Weather Observation Station 1

SELECT CITY, STATE
FROM STATION

Weather Observation Station 3

SELECT DISTINCT(CITY)
FROM STATION
WHERE ID % 2 = 0

Weather Observation Station 4

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

Weather Observation Station 5

select city, length(city)
from station
order by length(city), city
limit 1;

select city, length(city)
from station
order by length(city) DESC
limit 1;

order by에는 정렬 기준을 넣으면 된다

Weather Observation Station 6

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

정규식을 이용한 풀이다.

참고 사이트: https://velog.io/@gillog/MySQL-REGEXPRegular-Expression%EC%A0%95%EA%B7%9C-%ED%91%9C%ED%98%84%EC%8B%9D

Weather Observation Station 7

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

Weather Observation Station 8

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

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

처음에 이렇게 했다가 틀렸다. 이유를 discussion을 보고 알게 되었다.

^  			// string's beginning
[aeiou]   		// a SINGLE vowel
$  			// end of string

^			// start of string
[aeiou]			// a single vowel
.			// any characted...
*			// ...repeated any number of times
[aeiou]			// another vowel
$			// end of string

Weather Observation Station 9

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

잘 풀었다.

Weather Observation Station 10

SELECT DISTINCT(CITY)
FROM station
where city regexp '[^aeiou]$'

Weather Observation Station 11

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

Weather Observation Station 12

SELECT distinct(city)
from station
where city regexp '^[^aeiou]' and city regexp '[^aeiou]$'

Higher Than 75 Marks

SELECT NAME
FROM STUDENTS
WHERE marks > 75
ORDER BY SUBSTRING(NAME, -3) , ID

substring 사용하기

Employee Names

select name
from employee
order by name

Employee Salaries

select name
from employee
where salary > 2000 and months < 10
order by employee_id