뒤돌아보기

본 글은 책 "칼퇴족 김대리는 알고 나만 모르는 SQL 기초 편"의 정리 내용입니다.

열 이름으로 그룹화

CLASS_SCORE 테이블

CREATE TABLE CLASS_SCORE
(
NAME CHAR(20) NOT NULL,
ENG INT NOT NULL,
MATH INT NOT NULL,
GENDER CHAR(20) NOT NULL,
PRIMARY KEY('NAME')
);

INSERT INTO CLASS_SCORE
VALUES
('김필호', 28, 49, '남자'),
('이치훈', 68, 98, '남자'),
('김영숙', 87, 87, '여자'),
('성은영', 65, 78, '여자');

1) AVG 함수를 이용해서 영어점수(ENG)와 수학점수(MATH)의 평균을 구해 테이블을 요약하는 경우

SELECT AVG(ENG) AS ENG_AVERAGE,
       AVG(MATH) AS MATH_AVERAGE
FROM CLASS_SCORE;

2) 성별에 따라 영어점수와 수학점수의 평균을 구해 분석하고자 하는 경우

SELECT GENDER,
       AVG(ENG) AS ENG_AVERAGE,
       AVG(MATH) AS MATH_AVERAGE
FROM CLASS_SCORE
GROUP BY GENDER;

• GROUP BY 를 사용하여 성별(Gender)로 평균값을 계산 및 출력

예제 

2013년 12월 고객별 보유상품 테이블: PPC_20131

• 열 설명 : 고객아이디, 고객등급, 카드상품보유여부, 대출상품, 보험상품, 수신상품, 펀드상품, 연 수익 

CREATE TABLE PPC_201312
(
CUST_ID INT NOT NULL,
SEG CHAR(20) NOT NULL,
CARD_FLG INT NOT NULL,
LOAN_FLG INT NOT NULL,
INSURANCE_FLG INT NOT NULL,
CTB_FLG INT NOT NULL,
FUND_FLG INT NOT NULL,
ANNL_REV INT NOT NULL,
PRIMARY KEY('CUST_ID')
);

INSERT INTO PPC_201312
VALUES
(54615, 'SILVER', 1, 1, 1, 1, 1, 1000),
(46780, 'GOLD',   0, 0, 1, 1, 0, 20000),
(23748, 'GOLD',   1, 1, 0, 1, 1, 30000),
(56432, 'DIAMOND',1, 0, 1, 1, 1, 100000),
(89647, 'SILVER', 0, 0, 1, 1, 0, 3000),
(52333, 'SILVER', 1, 1, 0, 1, 0, 2500),
(89669, 'GOLD',   1, 0, 1, 1, 0, 60000),
(21004, 'SILVER', 0, 0, 1, 1, 0, 1000),
(17890, 'DIAMOND',1, 1, 0, 1, 1, 300000);

Q1. 고객등급별 인당 평균 연 수익

SELECT SEG, AVG(ANNL_REV) 
FROM PPC_201312
GROUP BY SEG;

Q2. 고객등급별 고객 수와 해당 등급별 카드 보유여부(CARD_FLG) 확인

SELECT SEG, COUNT(*) AS CNT,
       SUM(CARD_FLG)
FROM PPC_201312
GROUP BY SEG;

Q3. 카드상품과 대출상품 보유 간의 관계

SELECT CARD_FLG, LOAN_FLG, COUNT(*) AS CNT
FROM PPC_201312
GROUP BY CARD_FLG, LOAN_FLG;

참고

• 원하는 열을 GROUP BY 절에 포함시킬 수 있음
  • 열이 한개씩 증가할수록 차원(Dimension)이 한개씩 증가하고 좀 더 세부적으로 데이터 분석 가능 (위 Q3. 참고)
• GROUP BY 절은 WHERE 조건 절 뒤, ORDER BY 절 앞에 위치

SELECT SEG, COUNT(*), AS CNT, SUM(CARD_FLG) AS CARD_FLG
FROM PPC_201312
GROUP BY SEG
ORDER BY SEG; 

본 글은 책 "칼퇴족 김대리는 알고 나만 모르는 SQL 기초 편"의 정리 내용입니다.

• 집단(Population)에 대해 요약할 때는 대표성을 갖는 수치가 필요
• EX) 합계, 평균, 오차(최대값과 최소값 간의 거리, 표준편차, 분산)

SUM 함수 

MAX 함수 

MIN 함수 

STD 함수

• SQLite에서는 지원하지 않는 함수
• MySQL에서는 STD함수와 STDDEV 모두 같은 기능으로 사용 가능

AVG 함수

• 위의 예는 NULL값을 제외한 3개의 행에 대한 평균값을 계산한 것
• NULL값을 0으로 치환하기 위해서는?? -> COALESCE 함수 사용

SELECT AVG(COALESCE(수학점수, 0)) AS 수학점수_평균 FROM 점수테이블;

예제 (테이블: STUD_SCORE)

CREATE TABLE STUD_SCORE
(
STUDENT_ID INT NOT NULL,
MATH_SCORE INT NOT NULL,
ENG_SCORE INT NULL,
PHIL_SCORE INT NULL,
MUSIC_SCORE INT NULL,
PRIMARY KEY('STUDENT_ID')
);

INSERT INTO STUD_SCORE
VALUES 
('0123511', 89, 78, 45, 90),
('0255475', 88, 90, NULL, 87),
('9921100', 87, NULL, NULL, 98),
('9732453', 69, 98, 78, 78),
('0578981', 59, 90, 89, NULL),
('0768789', 94, 80, 87, 99),
('9824579', 90, 90, 78, 87),
('0565789', 58, 64, 72, NULL);

Q1. 수학 점수의 총 합계

SELECT SUM(MATH_SCORE) AS MATH_TOTAL FROM STUD_SCORE;

Q2. 음악 점수의 평균 (NULL값 행 제외)

SELECT AVG(MUSIC_SCORE) AS MUSIC_AVG FROM STUD_SCORE;

Q3. 음악 점수의 평균 (NULL값 행 포함)

SELECT AVG(COALESCE(MUSIC_SCORE, 0)) AS MUSIC_AVG FROM STUD_SCORE;

본 글은 책 "칼퇴족 김대리는 알고 나만 모르는 SQL 기초 편"의 정리 내용입니다.

COUNT 함수를 이용하면 행의 수를 셀 수 있다. 이 때, NULL 값의 포함여부/ 중복 포함 여부에 따라 함수 사용이 달라질 수 있다.

1. NULL값, 중복 값들 포함 모든 행의 수를 계산할 경우: COUNT(*)

2. NULL값은 제외, 중복 값은 포함하여 행의 수를 계산할 경우: COUNT(열이름)

3. NULL값, 중복값 모두 제외하여 행의 수를 계산할 경우: COUNT(DISTINCT 열이름)

예제 (테이블: STUD_SCORE)

CREATE TABLE STUD_SCORE
(
STUDENT_ID INT NOT NULL,
MATH_SCORE INT NOT NULL,
ENG_SCORE INT NULL,
PHIL_SCORE INT NULL,
MUSIC_SCORE INT NULL,
PRIMARY KEY('STUDENT_ID')
);

INSERT INTO STUD_SCORE
VALUES 
('0123511', 89, 78, 45, 90),
('0255475', 88, 90, NULL, 87),
('9921100', 87, NULL, NULL, 98),
('9732453', 69, 98, 78, 78),
('0578981', 59, 90, 89, NULL),
('0768789', 94, 80, 87, 99),
('9824579', 90, 90, 78, 87),
('0565789', 58, 64, 72, NULL);

Q1. NULL 값을 포함한 행의 개수 

SELECT COUNT(*) AS CNT FROM STUD_SCORE;

Q2. NULL 값을 제외한 음악 점수 보유자를 세는 쿼리문 

SELECT COUNT(MUSIC_SCORE) AS MUSIC_CNT FROM STUD_SCORE;

 Q3. 테이블을 사용하여 NULL값 및 중복된 값 제외한 영어 점수 보유자를 세는 쿼리문

SELECT COUNT(DISTINCT ENG_SCORE) AS ENG_CNT FROM STUD_SCORE;

본 글은 책 "칼퇴족 김대리는 알고 나만 모르는 SQL 기초 편"의 정리 내용입니다.

조건문을 적용하면 아래 표와 같이 수학점수에 조건을 적용하여 새로운 열을 생성할 수 있다.

CASE WHEN 

SELECT 열이름1, 
CASE WHEN [조건1] THEN[결과값1]
     WHEN [조건2] THEN[결과값2]
     ELSE [결과값3] END AS 새로운 열 이름 

• 조건1을 만족하면 결과값1, 조건2를 만족하면 결과값2, 나머지는 결과값3으로 보여주고 결과값은 [새로운 열 이름]으로 나타냄

예제 (직원 연봉 테이블: STAFF_SAL)

CREATE TABLE STAFF_SAL
(
ID INT NOT NULL,
JOB CHAR(20) NOT NULL,
CURRENT_SAL INT NOT NULL,
ENG_SCORE INT NOT NULL,
PRIMARY KEY ('ID')
);

INSERT INTO STAFF_SAL
VALUES
(2148, 'OFFICER', 40000, 90),
(5780, 'CLERK', 32000, 98),
(6870, 'MANAGER', 100000, 81),
(4565, 'CLERK', 30000, 79),
(9687, 'CLERK', 33000, 66),
(7337, 'MANAGER', 100000, 95),
(1321, 'OFFICER', 43000, 80),
(9895, 'CLERK', 30000, 50);

Q1. 직업(JOB)이 CLERK는 7%, OFFICER는 5%, MANAGER는 3%로 연봉을 인상하기로 했다. STAFF_SAL 테이블을 사용하여 각 직원별 인상 연봉을 예상해보자

SELECT ID, JOB, CURRENT_SAL,
	CASE WHEN JOB = 'CLERK' THEN CURRENT_SAL*1.07
		 WHEN JOB = 'OFFICER' THEN CURRENT_SAL*1.05
		 WHEN JOB = 'MANAGER' THEN CURRENT_SAL*1.03
		 ELSE CURRENT_SAL
		 END AS NEXT_SAL
 FROM STAFF_SAL;

Q2. 다음 표와 같이 연봉 인상이 직급, 영어 점수와 연관될 경우 테이블을 이용하여 각 직원별 인상 연봉을 예상해보자.

SELECT ID, JOB, CURRENT_SAL,
	CASE WHEN JOB = 'CLERK' AND ENG_SCORE >= 80 
	     THEN CURRENT_SAL * 1.07 
	     WHEN JOB = 'CLERK' AND ENG_SCORE < 80 
		 THEN CURRENT_SAL * 1.06
		 WHEN JOB = 'OFFICER' AND ENG_SCORE >= 80 
		 THEN CURRENT_SAL * 1.05
		 WHEN JOB = 'OFFICER' AND ENG_SCORE < 80 
		 THEN CURRENT_SAL * 1.04
		 WHEN JOB = 'MANAGER' AND ENG_SCORE >= 80 
		 THEN CURRENT_SAL * 1.03
		 WHEN JOB = 'MANAGER' AND ENG_SCORE < 80 
		 THEN CURRENT_SAL * 1.02
		 ELSE CURRENT_SAL
		 END AS NEXT_SAL
FROM STAFF_SAL;

본 글은 책 "칼퇴족 김대리는 알고 나만 모르는 SQL 기초 편"의 정리 내용입니다.

숫자 함수

숫자 함수 목록

예제

제품판매 테이블: PROD_SALES

• Primary Key는 PROD_ID

CREATE TABLE PROD_SALES
(
PROD_ID INT NOT NULL,
TOTAL_SALES FLOAT NOT NULL,
SALES_NUM INT NOT NULL,
ECON_INCOME INT NOT NULL,
PRIMARY KEY('PROD_ID')
);

INSERT INTO PROD_SALES
VALUES
(65478, 7883.64, 564, 301),
(56870, 2156.12, 412, -241),
(13540, 5701.74, 230, 62),
(89744, 224.33, 96, 12),
(23787, 5703.5, 170, 70),
(56706, 744.57, 21, -120),
(96384, 570.55, 54, 57),
(33187, 977.98, 90, 91);

Q1. 소수점 둘째 자리에서 반올림한 제품 판매금액(TOTAL_SALES)을 계산한 후 별칭을 SALES_REV로 설정하는 쿼리문

SELECT PROD_ID, TOTAL_SALES, ROUND(TOTAL_SALES, 1) 
AS SALES_REV
FROM PROD_SALES;

Q2. 제품 판매금액을 제품 판매수량(SALES_NUM)으로 나눈 나머지를 구한 후 별칭을 SALES_BALANCE로 설정 

SELECT MOD(TOTAL_SALES, SALES_NUM) AS SALES_BALANCE
FROM PROD_SALES;

• SQlite에서는 MOD 함수가 지원 안됨

본 글은 책 "칼퇴족 김대리는 알고 나만 모르는 SQL 기초 편"의 정리 내용입니다.

문자 함수

• 함수란 변수를 특정한 형태로 변환시키는 것

문자 함수 목록

예제

벤더 테이블: VENDOR_INFO

• Primary Key는 ID

CREATE TABLE VENDOR_INFO
(
ID INTEGER NOT NULL,
NAME CHAR(20) NOT NULL,
COUNTRY CHAR(20) NOT NULL,
PRIMARY KEY('ID')
);

INSERT INTO VENDOR_INFO
VALUES
(1, 'Sue', 'Germany'),
(2, 'David', 'Switzerland'),
(3, 'Sam', 'France'),
(4, 'Jihoon', 'Brazil'),
(5, 'Sunwoo', 'France'),
(6, 'Berney', 'Italy'),
(7, 'Sandy', 'Germany'),
(8, 'Young', 'Korea')
;

Q1. 위 테이블 사용하여 이름을 소문자로 변환하고 NAME_SMALL이라는 별칭을 설정하는 쿼리문

SELECT ID, NAME, LOWER(NAME) AS NAME_SMALL 
FROM VENDOR_INFO;

Q2. 이름을 대문자로 변환하고 NAME_CAP이라는 별칭 설정

SELECT ID, NAME, UPPER(NAME) AS NAME_CAP
FROM VENDOR_INFO;

Q3. 이름의 자릿수를 세고 NAME_CNT라는 별칭 설정

SELECT ID, NAME, LENGTH(NAME) AS NAME_CNT
FROM VENDOR_INFO;

Q4. 이름의 두번째에서 네번째 위치한 글자를 나타내고 NAME_STR 별칭 설정

# SUBSTR(열이름, 시작위치, 자리수)
SELECT ID, NAME, substr(NAME,2,3) as NAME_CNT FROM VENDOR_INFO;

본 글은 책 "칼퇴족 김대리는 알고 나만 모르는 SQL 기초 편"의 정리 내용입니다.

결합 연산자 '||' 는 2개 이상의 열을 결합하여 하나의 열에 표현하고 싶을 때 사용된다.

문자 삽입하기

SELECT 열이름1 || '삽입하고 싶은 문자열' || 열이름2
FROM 테이블명;

예제 (고객정보 테이블 : CUSTOMERS)

• Primary Key는 ID

CREATE TABLE CUSTOMERS
(ID INT NOT NULL,
NAME CHAR(20) NOT NULL,
CITY CHAR(20) NOT NULL,
COUNTRY CHAR(20) NOT NULL,
PRIMARY KEY('ID')
);

INSERT INTO CUSTOMERS
VALUES
(1, 'SUE', 'BERLIN', 'GERMANY'),
(2, 'DAVID', 'BERN', 'SWITZERLAND'),
(3, 'SAM', 'NANTES', 'FRANCE'),
(4, 'KIM', 'RESENDE', 'BRAZIL'),
(5, 'LEE', 'VERSAILLES', 'FRANCE'),
(6, 'BERNEY', 'BERGAMO', 'ITALY'),
(7, 'SANDY', 'BERLIN', 'GERMANY'),
(8, 'YOUNG', 'SEOUL', 'KOREA');

Q1. 위 테이블을 사용하여 '도시(나라)' 로 표현되도록 필드를 결합하는 쿼리문

1. 먼저 2개 이상의 열 결합하기(결합 연산자 '||')에서 배운대로 ‘도시’와 ‘나라’ 열을 결합

SELECT CITY || COUNTRY FROM CUSTOMERS;

2. 단순히 결합만 하면 가독성이 떨어지기 때문에 괄호를 추가하면,

SELECT CITY || '(' || COUNTRY|| ')' FROM CUSTOMERS;

3. 마지막으로 ADDR 별칭 추가

SELECT CITY || '(' || COUNTRY|| ')' AS ADDR FROM CUSTOMERS;

본 글은 책 "칼퇴족 김대리는 알고 나만 모르는 SQL 기초 편"의 정리 내용입니다.

결합 연산자 '||' 는 2개 이상의 열을 결합하여 하나의 열에 표현하고 싶을 때 사용된다.

|| 연산자

SELECT 열이름1 || 열이름2 FROM 테이블명;
혹은
SELECT 열이름1 || 열이름2 AS 새로운 열이름 FROM 테이블명;

예제 (고객정보 테이블 : CUSTOMERS)

• Primary Key는 ID

CREATE TABLE CUSTOMERS
(ID INT NOT NULL,
NAME CHAR(20) NOT NULL,
CITY CHAR(20) NOT NULL,
COUNTRY CHAR(20) NOT NULL,
PRIMARY KEY('ID')
);

INSERT INTO CUSTOMERS
VALUES
(1, 'SUE', 'BERLIN', 'GERMANY'),
(2, 'DAVID', 'BERN', 'SWITZERLAND'),
(3, 'SAM', 'NANTES', 'FRANCE'),
(4, 'KIM', 'RESENDE', 'BRAZIL'),
(5, 'LEE', 'VERSAILLES', 'FRANCE'),
(6, 'BERNEY', 'BERGAMO', 'ITALY'),
(7, 'SANDY', 'BERLIN', 'GERMANY'),
(8, 'YOUNG', 'SEOUL', 'KOREA');

문제

Q1. 위 테이블에서 ID와 NAME 열을 하나로 묶어서 출력하는 쿼리문

SELECT ID, NAME,
ID || NAME AS ID_NAME
FROM CUSTOMERS;

• 결합연산자 ‘||’ 또는 ‘+’가 사용되는데 DBMS마다 지원되는 기호가 다르지만 보통 ‘||’를 많이 사용

본 글은 책 "칼퇴족 김대리는 알고 나만 모르는 SQL 기초 편"의 정리 내용입니다.

특정 열에 특정 키워드가 포함된 레코드를 찾을 때 → % 사용

LIKE 연산자

# 뒤에 나오는 문자들 모를 때
SELECT 열이름 FROM 테이블 WHERE 열이름 LIKE '찾고싶은문자%';
 
# 앞에 나오는 문자들 모를 때
SELECT 열이름 FROM 테이블 WHERE 열이름 LIKE '%찾고싶은문자';

# 앞뒤 나오는 문자들 모를 때
SELECT 열이름 FROM 테이블 WHERE 열이름 LIKE '%찾고싶은문자%';

# 뒤에 나오는 문자 모를 때
SELECT 열이름 FROM 테이블 WHERE 열이름 LIKE '찾고싶은문자_';

• % 는 자주 사용하는 와일드카드로 모든 문자를 의미한다고 생각하면 됨
• 와일드카드란?
  • 문자열에서 특정한 문자 또는 문자들을 대체하기 위해 사용하는 문자를 의미
    • % : 숫자 0 또는 문자들을 대체하기 위해 사용됨
    • _ : 한 개의 단어를 대체하기 위해 사용됨

예시( 고객정보 테이블 : CUSTOMERS)

• Primary Key는 ID

CREATE TABLE CUSTOMERS
(ID INT NOT NULL,
NAME CHAR(20) NOT NULL,
CITY CHAR(20) NOT NULL,
COUNTRY CHAR(20) NOT NULL,
PRIMARY KEY('ID')
);

INSERT INTO CUSTOMERS
VALUES
(1, 'SUE', 'BERLIN', 'GERMANY'),
(2, 'DAVID', 'BERN', 'SWITZERLAND'),
(3, 'SAM', 'NANTES', 'FRANCE'),
(4, 'KIM', 'RESENDE', 'BRAZIL'),
(5, 'LEE', 'VERSAILLES', 'FRANCE'),
(6, 'BERNEY', 'BERGAMO', 'ITALY'),
(7, 'SANDY', 'BERLIN', 'GERMANY'),
(8, 'YOUNG', 'SEOUL', 'KOREA');

문제

Q1. 위 테이블에서 BER로 시작하는 도시에 거주하는 고객들을 찾아서 출력하는 쿼리문

SELECT *
FROM CUSTOMERS 
WHERE CITY LIKE 'BER%';

Q2. 'NY'로 끝나는 나라에 거주하는 고객

SELECT *
FROM CUSTOMERS 
WHERE COUNTRY LIKE '%NY';

Q3. ES를 포함한 도시에 거주하는 고객

SELECT *
FROM CUSTOMERS 
WHERE CITY LIKE '%ES%';

본 글은 책 "칼퇴족 김대리는 알고 나만 모르는 SQL 기초 편"의 정리 내용입니다.

논리 연산자 IN 은 여러 개의 OR 연산자를 하나로 묶은 것과 같음

IN 연산자

SELECT 열이름 FROM 테이블명 WHERE 비교할 열 이름 IN (조건1, 조건2);

• IN 연산자는 여러 조건들 중 하나만 만족해도 출력 → 여러 개의 OR 연산자를 하나로 묶은 것과 같음

예제 (테이블명 : BRNCH_INFO)

• 각 열에 대한 설명
  : 영업점 번호, 영업점 이름, 영업점 신선일, 영업점 폐쇄일, 영업점 인원, 경영평가

• Primary Key는 BRNCH_NO

CREATE TABLE BRNCH_INFO
(
BRNCH_NO INT NOT NULL,
BRNCH_NM CHAR(20) NOT NULL,
OPEN_DT date NOT NULL,
CLOSE_DT date NULL,
BRNCH_NUM INT NOT NULL,
BRNCH_PERF CHAR(20) NOT NULL
);

INSERT INTO BRNCH_INFO
VALUES
(789, '시청', '1990-03-12', NULL, 13, 'A'),
(640, '여의도', '2005-08-07', NULL, 8, 'B'),
(368, '대치', '2005-08-01', '2013-07-09', 13, 'C'),
(248, '창동', '2000-11-30', NULL, 12, 'A'),
(547, '종각', '1993-02-21', NULL, 11, 'C'),
(780, '명동', '1999-09-22', NULL, 8, 'A'),
(987, '역삼', '2008-08-30', NULL, 7, 'B'),
(456, '대학로', '2010-12-01', NULL, 8, 'B'),
(650, '신촌', '2002-05-17', NULL, 10, 'C');

문제

Q1. 위 테이블에서 직원 수가 10명 또는 8명이면서 경영평가가 'A' 또는 'B'인 영업점 번호, 영업점 이름, 영업점 인원, 경영평가를 출력하는 쿼리문

SELECT BRNCH_NO, BRNCH_NM, BRNCH_NUM, BRNCH_PERF
FROM BRNCH_INFO
WHERE BRNCH_NUM IN (8, 10)
 AND BRNCH_PERF IN ('A', 'B');

NOT IN 연산자

SELECT 열이름 FROM 테이블명 WHERE 비교할 열 이름 NOT IN (조건1, 조건2);

• NOT IN 연산자는 뒤에 있는 조건을 부정할 때 사용
• IN 연산자 내부의 조건들은 OR 인 반면, NOT IN 연산자 내부의 조건들은 AND

문제

Q2. 아직 폐쇄되지 않았거나 2000년도 이전에 신설한 영업점 중 경영평가가 ‘A’가 아닌 영업점 번호, 영업점 이름, 신설일, 폐쇄일, 경영평가를 출력

SELECT BRNCH_NO, BRNCH_NM, OPEN_DT, CLOSE_DT, BRNCH_PERF
FROM BRNCH_INFO
WHERE CLOSE_DT IS NOT NULL OR OPEN_DT < '2000-01-01')
      AND BRNCH_PERF NOT IN ('A');