[MySQL] Query Basic

애초에 SQL공부를 열심히 한 적이 없었지만, 최근 ORM에 익숙해지면서 SQL사용이 점점더 힘들어지게 되었다.

해당 포스트는 블로그 포스팅 식으로 진행될 것이 아니라, 쿼리 공부를 통해 지속적으로 업데이트 될 포스트이다.

문제 1.

CITY테이블에서 총 인구소가 100000을 초과하는 American city들을 모두 구하라. America의 CountryCode는 USA이다.

select * from CITY where population>100000 and countrycode='USA';

문제 2.

STATION의 ID값이 짝수인 CITY 이름을 모두 구하라. 중복은 제거되어야한다.

select distinct city from station where id%2=0;
/*OR*/
select city from station where id%2=0 group by city;

distinct나 group by를 사용해서 해결한다.¹

문제 3.

STATION테이블에 있는 모든 CITY의 갯수를 $N$ 이라고 가정하고, and 중복된 이름들을 제거했을 때의 갯수는 $N'$ 이라고 가정하자; $N - N'$ 의 값을 구하라. 좀 더 자세히 설명하자면, 전체 CITY의 갯수와 중복값을 제거한 CITY의 갯수를 구하라.

select count(city) - count(distinct city) from station;

문제 4.

STATION 테이블에서 CITY의 이름이 가장 긴 값과 가장 짧은 두 개를 구하라, 각각의 길이 역시 구해야한다. (즉, 철자의 갯수). 가장 작거나 큰 길이를 갖는 이름들이 여러개가 있다면, 알파벳 순서상 오름차순에 있는 이름을 선택해라.

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

union은 두 query의 결과를 합칠 때 이용한다. order by의 뒤에 오는 인자를 통해 정렬 순서를 결정한다. 다수의 인자일 경우 모두 반영한다.

문제 5.

STATION 테이블에서 CITY의 이름이 모음 (i.e., a, e, i, o, or u)으로 시작하는 값을 구하라. 중복된 값이 있어선 안된다.

select distinct city from station where city like "a%" or city like "e%" or city like "i%" or city like "o%" or city like "u%";

문제 8.(문제 5~7 까지는 거의 비슷한 문제라서 제외한다.)

STATION 테이블에서 CITY 이름의 양 끝에 모음(a,e,i,o,u)로 시작되는 값들을 구하라. 중복된 값이 있어선 안된다.

select distinct city from station where left(city, 1) in ("a", "e", "i", "o", "u") and right(city, 1) in ("a", "e", "i", "o", "u")

문제 9.

STATION 테이블에서 CITY 이름의 양 끝에 모음(a,e,i,o,u)로 시작되지 않는 값들을 구하라. 중복된 값이 있어선 안된다.

select distinct city from station where left(city, 1) not in ("a", "e", "i", "o", "u")

문제 11.

STATION 테이블에서 모음으로 시작하거나 혹은 모음으로 끝나는 CITY 값들을 구하라. 중복된 값이 있어선 안된다.

select distinct city from station where left(city, 1) not in ("a","e","i","o","u") or right(city, 1) not in ("a", "e", "i","o","u")

문제 75점보다 높다.

STUDENTS 테이블에서 75점보다 높은 학생들의 이름을 출력하라. 이름의 뒷자리에서부터 3자리의 철차를 이용해 오름차순으로 정렬하고, 해당 조건에서도 우선순위를 결정지을 수 없다면 ID값으로 오름차순 정렬하라

select Name from STUDENTS where Marks>75 order by right(Name, 3), ID

문제 직원 월급.

Employee 테이블에서 10개월 미만의 근무자 중, 월급이 $2000 보다 많은 직원들의 이름을 출력하라. employee_id 으로 오름차순 계산하라

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

문제. 일본 인구

일본 도시 인구의 총합을 구하라.

select sum(POPULATION) from CITY where COUNTRYCODE='JPN';

문제. 인구차

인구가 가장 많은 도시와 적은 도시의 인구수 차를 구하라

select max(POPULATION) - min(POPULATION) from CITY;

문제. 큰 실수를 저지르다

Samantha는 전 직원들의 평균 월급을 계산하는 업무를 진행하고 있었다. 계산을 다 마치고 나서야 그녀의 키보드에서 0 키가 고장났다는 사실을 깨닳았다. 실제 평균 급료와 잘못 계산된 급료의 차이를 구하라.

select CEIL((SUM(salary)/count(salary)) - (sum(replace(salary,'0',''))/count(salary))) from EMPLOYEES;

CEIL 함수는 매개변수로 받은 숫자와 같거나 큰 수 중에서 가장 작은 수를 반환한다.²

문제. 최고 수익자

EMPLOYEE 테이블에서 $months*salary$ 를 합쳐서 직원들의 수익을 계산하였다. 최고 수익자의 수익금과 그와 같은 수익을 올린 직원이 총 몇 명인지 구하라.

select max(months*salary), count(*) from employee where (months*salary) = (select max(months*salary) from employee)

문제. 기후 관측소2

LAT_N, LONG_W 각각의 총합을 구하고 소수 둘째 짜리 까지 표현하라

select round(sum(LAT_N),2), round(sum(LONG_W),2) from STATION;

문제. 기후 관측소13

LAT_N의 크기가 $38.7880$ 보다 크고 $137.2345$ 보다 작은 기상청의 LAT_N 값의 총합을 구하고 수소 넷째 자리 까지 표기하라

select round(sum(LAT_N),4) from STATION where LAT_N > 38.788 and LAT_N < 137.2345

문제. 기후 관측소14

LAT_N의 크기가 $137.2345$ 보다 작은 값 중에서 가장 큰 값을 구하고 소수 넷자 짜리 까지 표기하라.

select round(sum(LAT_N), 4) from STATION where LAT_N < 137.2345

문제. 기후 관측소15

LAT_N의 크기가 $137.2345$ 보다 작은 값 중에서 가장 큰 값의 LONG_W를 소수 넷째 자리 까지 표기하라

select round(sum(LONG_W),4) from STATION where LAT_N = (select max(LAT_N) from STATION where LAT_N < 137.2345)

문제. 기후 관측소16

LAT_N의 크기가 $38.7780$ 보다 큰 값 중에서 가장 작은 값을 소수 넷째 자리 까지 표기하라

select round(min(LAT_N),4) from STATION where LAT_N > 38.7780

문제. 기후 관측소17

LAT_N 크기가 $38.7780$ 보다 큰 값 중에서 가장 작은 값을 갖는 레코드의 LONG_W 값을 구하라

select round(LONG_W,4) from STATION where LAT_N = (select min(LAT_N) from station where LAT_N>38.7780)

문제. 기후 관측소18

LAT_N, LONG_W 의 값이 가장 작은 좌표 $P_1$ 과 LAT_N, LONG_W 의 값이 가장 큰 좌표 $P_2$ 사이의 $Manhattan Distance$³ 를 구하라

select round(abs(min(LAT_N)-max(LAT_N)) + abs(min(LONG_W) - max(LONG_W)),4) from STATION