SQL 심화_3

YJ·2023년 4월 21일

▷ 오늘 학습 계획: SQL 강의(심화6~8)

📖 05_Aggregate Functions(집계함수)

여러 칼럼 혹은 테이블 전체 칼럼으로부터 하나의 결과값을 반환하는 함수

1) COUNT

SELECT COUNT(column)
FROM tablename
WHERE condition;

police_station 테이블에서 데이터는 모두 몇 개?
SELECT COUNT(*) FROM police_station;
crime_status 테이블에서 경찰서는 총 몇군데?
SELECT COUNT(DISTINCT police_station) FROM crime_status;
crime_type 은 총 몇 가지?
SELECT COUNT(DISTINCT crime_type) FROM crime_status;

2) SUM

SELECT SUM(column)
FROM tablename
WHERE condition;

범죄 총 발생건수는?

SELECT SUM(case_number) FROM crime_status WHERE status_type="발생";

살인의 총 발생건수는?

SELECT SUM(case_number) FROM crime_status 
WHERE status_type="발생" AND crime_type LIKE '살인';

중부 경찰서에서 검거된 총 범죄 건수

SELECT SUM(case_number) FROM crime_status 
WHERE status_type="검거" AND police_station = '중부';

3) AVG

SELECT AVG(column)
FROM tablename
WHERE condition;

평균 폭력 검거 건수는?

SELECT AVG(case_number)
FROM crime_status
WHERE crime_type like '폭력' and status_type like '검거';

중부경찰서 범죄 평균 발생 건수

SELECT AVG(case_number)
FROM crime_status
WHERE police_station like '중부' and status_type like '발생';

4) MIN

SELECT MIN(column)
FROM tablename
WHERE condition;

강도 발생 건수가 가장 적은 경우 몇 건?

SELECT MIN(case_number)
FROM crime_status
WHERE crime_type like '강도' and status_type like '발생';

중부경찰서에서 가장 낮은 검거 건수는?

SELECT MIN(case_number)
FROM crime_status
WHERE police_station like '중부' and status_type like '검거';

5) MAX

SELECT MAX(column)
FROM tablename
WHERE condition;

살인이 가장 많이 검거된 건수는?

SELECT MAX(case_number)
FROM crime_status
WHERE crime_type like '살인' and status_type like '검거';

강남 경찰서에서 가장 많이 발생한 범죄 건수는?

SELECT MAX(case_number)
FROM crime_status
WHERE police_station like '강남' and status_type like '발생';

6) FIRST, LAST

FIRST: 첫번째 결과값을 리턴
LAST: 마지막 결과값을 리턴

7) GROUP BY

그룹화하여 데이터를 조회

SELECT column1, column2
FROM tablename
WHERE condition
GROUP BY column1, column2
ORDER BY column1, column2

crime_status 에서 경찰서별로 그룹화 하여 경찰서 이름을 조회
SELECT police_station
FROM crime_status
GROUP BY police_station
ORDER BY police_station
LIMIT 5;
DISTINCT를 사용한 경찰서 종류 검색(ORDER BY 를 사용할 수 없음)
SELECT DISTINCT police_station FROM crime_status LIMIT 5;
경찰서 별로 총 발생 범죄 건수를 검색
SELECT police_station, sum(case_number) 발생건수
FROM crime_status
WHERE status_type like '발생'
GROUP BY police_station
ORDER BY 발생건수 DESC
LIMIT 5;
경찰서 별로 평균 범죄 검거 건수를 검색
SELECT police_station, avg(case_number) 평균검거건수
FROM crime_status
WHERE status_type like '검거'
GROUP BY police_station
ORDER BY 평균검거건수 DESC
LIMIT 5;
경찰서 별 평균 범죄 발생건수와 평균 범죄 검거 건수를 검색
SELECT police_station, status_type, avg(case_number)
FROM crime_status
GROUP BY police_station, status_type
LIMIT 5;

8) HAVING

조건에 집계함수가 포함되는 경우 WHERE 대신 HAVING 사용

SELECT column1, column2
FROM tablename
WHERE condition
GROUP BY column1, column2
HAVING condition (Aggregate functions)
ORDER BY column1, column2

경찰서 별로 발생한 범죄 건수의 합이 4000 건보다 보다 큰 경우를 검색
SELECT police_station, sum(case_number) count
FROM crime_status
WHERE status_type like '발생'
GROUP BY police_station
HAVING count > 4000;
경찰서 별로 발생한 폭력과 절도의 범죄 건수 평균이 2000 이상인 경우
SELECT police_station, avg(case_number) 
FROM crime_status
WHERE (crime_type like '폭력' or crime_type like '절도') 
AND status_type like '발생'
GROUP BY police_station
HAVING avg(case_number) >= 2000;
경찰서 별로 가장 많이 검거한 범죄 건수를 가장 적은 건수 순으로 5개 검색
SELECT police_station, AVG(case_number) FROM crime_status
WHERE (crime_type like '폭력' or crime_type like '절도')
AND status_type like '발생'
GROUP BY police_station
HAVING AVG(case_number) > 2000;
경찰서 별로 가장 적게 검거한 건수 중 4건보다 큰 경우를 건수가 큰 순으로 정렬하여 검색
SELECT police_station, MIN(case_number) FROM crime_status
WHERE status_type LIKE '검거' 
GROUP BY police_station 
HAVING MIN(case_number) > 4 
ORDER BY MIN(case_number) DESC;

📖 06_Scalar Functions

입력값을 기준으로 단일 값을 반환하는 함수

1) UCASE

영문을 대문자로 변환하는 함수
SELECT UCASE(string);

2) LCASE

영문을 소문자로 변환하는 함수
SELECT LCASE(string);

3) MID

문자열 부분을 반환하는 함수
SELECT MID(string, start_position, length);

4) LENGTH

문자열의 길이를 반환하는 함수
SELECT LENGTH(string);
문자가 없는 경우 길이 0
SELECT LENGTH('');
공백일 경우 길이 1
SELECT LENGTH(' ');
NULL의 경우 길이가 없음(NULL)
SELECT LENGTH(NULL);

5) ROUND

지정한 자리에서 숫자를 반올림하는 함수
(0이 소수점 첫째 자리)
SELECT ROUND(number, decimals_place);
반올림할 위치를 지정하지 않을 경우 소수점 자리(0)에서 반올림

6) NOW

현재 날짜 및 시간을 반환하는 함수
SELECT NOW(number, decimals_place);

7) FORMAT

숫자를 천단위 콤마가 있는 형식으로 반환하는 함수
SELECT FORMAT(number, decimals_place);

▷ 내일 학습 계획: SQL 강의(심화9~10)