Online Appendix C — SQL 필수

선행 조건

• SQL의 초기 역사 읽기, (Chamberlin 2012)
  • SQL 개발에 대한 흥미로운 개요.

핵심 개념 및 기술

• 다양한 R 패키지를 사용하거나 RStudio 내에서 SQL을 작성할 수 있지만, SQL에 대한 산업 수요로 인해 적어도 처음에는 R과 독립적으로 배우는 것이 좋습니다.
• SQLite는 SQL의 한 종류이며, SQL용 IDE로 DB Browser for SQLite를 사용할 수 있습니다.

주요 패키지 및 함수

• BETWEEN
• DESC
• DISTINCT
• FROM
• GROUP BY
• LEFT JOIN
• LIKE
• LIMIT
• OR
• ORDER BY
• SELECT
• UPDATE
• WHERE

C.1 서론

구조화된 쿼리 언어(Structured Query Language)의 약자인 SQL(“시퀄” 또는 “에스큐엘”이라 읽음)은 관계형 데이터베이스를 관리하고 조작하기 위한 핵심 언어입니다. 관계형 데이터베이스란 하나 이상의 테이블이 서로 유기적으로 연결된 상태를 말하며, 각 테이블은 우리가 이미 익숙한 행과 열의 구조를 가집니다. 데이터베이스 안에 여러 테이블이 존재할 때, 이들을 서로 연결해 주는 기준 열(key)이 반드시 존재해야 합니다. SQL은 우리가 앞서 배운 프로그래밍 언어보다는, 마크업 언어인 HTML이나 CSS를 다루는 것과 다소 유사한 느낌을 줍니다. 한 가지 흥미로운 관습은 예약어(명령어)를 대문자로 작성한다는 점입니다. 또한 줄 바꿈이나 공백은 코드 실행에 영향을 주지 않지만, 모든 SQL 문장은 반드시 세미콜론(;)으로 끝맺어야 합니다.

SQL은 1970년대 IBM에서 처음 개발된 이후, 오늘날까지 데이터를 다루는 가장 강력하고 인기 있는 도구로 자리 잡았습니다. 사용 환경에 따라 다양한 “SQL 방언(Dialects)”이 존재하지만, 그 뿌리는 같습니다. 이 장에서는 가볍고 빠르며 Mac에 기본 설치되어 있는 오픈 소스 데이터베이스인 SQLite를 중심으로 설명합니다. Windows 사용자라면 공식 홈페이지에서 간편하게 설치할 수 있습니다.

고급 사용자는 SQL만으로도 복잡한 분석 업무를 완결할 수 있지만, 초보자라 하더라도 기본적인 SQL 문법만 익히면 다룰 수 있는 데이터의 폭이 획기적으로 넓어집니다. 특히 기업의 방대한 데이터가 SQL 기반 서버에 저장되어 있는 경우가 많기 때문에, 데이터를 직접 쿼리하여 가져올 수 있는 능력은 업무 효율성 측면에서 커다란 강점이 됩니다.

최근에는 R의 DBI (R Special Interest Group on Databases (R-SIG-DB), Wickham, and Müller 2022) 패키지 등을 활용해 RStudio 안에서 직접 SQL을 작성하는 것도 가능해졌습니다. 하지만 R에 대한 숙련도와는 별개로, 노동 시장에서의 SQL 수요는 독립적으로 매우 높기 때문에 RStudio 외부에서 SQL을 다루는 실무 지식을 갖추는 것이 커리어 관점에서 더 유리할 수 있습니다. 사실 우리가 이 책에서 배운 dplyr의 동사들과 SQL 명령어들은 개념적으로 매우 닮아 있습니다. R 환경을 벗어나고 싶지 않다면 dbplyr (Wickham, Girlich, and Ruiz 2022) 패키지를 사용해 보세요. dplyr 함수를 사용하면 이를 자동으로 SQL 문으로 변환해 줍니다. 이미 mutate(), filter(), left_join() 등에 익숙하다면 SQL의 핵심 명령어도 금방 배울 수 있을 것입니다. 다만 SQL은 명령어의 작성 순서와 실제 실행 순서가 다를 수 있다는 점에 유의해야 합니다.

C.2 시작하기

SQL을 시작하려면 무료 오픈 소스인 DB Browser for SQLite (DB4S)를 다운로드하여 엽니다(Figure C.1).

Figure C.1: DB Browser for SQLite 열기

여기에서 “AustralianPoliticians.db”를 다운로드한 다음 “데이터베이스 열기”로 열고 데이터베이스를 다운로드한 위치로 이동합니다.

이제 다룰 세 가지 주요 SQL 명령은 SELECT, FROM, WHERE입니다. SELECT를 사용하면 데이터의 특정 열을 지정할 수 있으며, SELECT를 select()와 유사하게 고려할 수 있습니다. select()로 데이터셋을 지정해야 하고 파이프 연산자를 사용하여 그렇게 했던 것과 마찬가지로, FROM으로 데이터셋을 지정합니다. 예를 들어, “SQL 실행”을 열고 다음을 입력한 다음 “실행”을 클릭할 수 있습니다.

SELECT
    surname   
FROM
    politicians;

결과는 성(surname) 열을 얻는 것입니다. 쉼표로 구분하여 여러 열을 선택하거나, 별표를 사용하여 모든 열을 선택할 수 있지만, 데이터셋이 우리가 모르는 사이에 변경될 경우 결과가 달라질 수 있으므로 이는 모범 사례가 아닙니다.

SELECT
    uniqueID,
    surname   
FROM
    politicians;

SELECT
    *
FROM
    politicians;

마지막으로, 반복되는 행이 있다면 DISTINCT를 사용하여 고유한 행만 볼 수 있습니다. 이는 distinct()와 유사합니다.

SELECT
    DISTINCT surname   
FROM
    politicians;

지금까지 SELECT와 FROM을 사용했습니다. 일반적으로 사용되는 세 번째 명령은 WHERE이며, 이는 filter()와 유사하게 특정 행에 초점을 맞출 수 있도록 합니다.

SELECT
    uniqueID,
    surname,
    firstName   
FROM
    politicians     
WHERE
    firstName = "Myles";

“=”, “!=”, “>”, “<”, “>=”, “<=”와 같은 모든 일반적인 논리 연산자는 WHERE와 함께 사용할 수 있습니다. AND와 OR를 사용하여 조건을 결합할 수 있습니다.

SELECT
    uniqueID,
    surname,
    firstName   
FROM
    politicians     
WHERE
    firstName = "Myles" 
    OR firstName = "Ruth";

많은 결과를 반환하는 쿼리가 있다면 LIMIT를 사용하여 결과 수를 제한할 수 있습니다.

SELECT
    uniqueID,
    surname,
    firstName        
FROM
    politicians          
WHERE
    firstName = "Robert"       LIMIT 5;

그리고 ORDER를 사용하여 결과의 순서를 지정할 수 있습니다.

SELECT
    uniqueID,
    surname,
    firstName 
FROM
    politicians 
WHERE
    firstName = "Robert" 
ORDER BY
    surname DESC;

기준에 매우 가까운 행을 확인하십시오:

SELECT
    uniqueID,
    surname,
    firstName      
FROM
    politicians      
WHERE
    firstName LIKE "Ma__";

위의 “_“는 모든 문자와 일치하는 와일드카드입니다. 이는”Mary”와 “Mark”를 포함하는 결과를 제공합니다. LIKE는 대소문자를 구분하지 않습니다: “Ma“와”ma” 모두 동일한 결과를 반환합니다.

“NULL” 또는 “NOT NULL”을 사용하여 누락된 데이터에 초점을 맞출 수 있습니다.

SELECT
    uniqueID,
    surname,
    firstName,
    comment
FROM
    politicians      
WHERE
    comment     IS NULL;

숫자, 날짜, 텍스트 필드에 순서가 적용되어 BETWEEN을 모든 필드에 사용할 수 있습니다. 숫자뿐만 아니라. 예를 들어, X와 Z 사이의 문자로 시작하는 모든 성을 찾을 수 있습니다 (Z는 포함하지 않음).

SELECT
    uniqueID,
    surname,
    firstName
FROM
    politicians      
WHERE
    surname     BETWEEN "X" AND "Z";

숫자 변수와 함께 WHERE를 사용하면 BETWEEN이 포함적입니다. 문자와 함께 사용한 예시와는 다릅니다.

SELECT
    uniqueID,
    surname,
    firstName,
    birthYear
FROM
    politicians      
WHERE
    birthYear     BETWEEN 1980 AND 1990;

요청한 데이터셋 관측치를 제공하는 것 외에도 데이터셋을 수정할 수 있습니다. 예를 들어, UPDATE와 SET을 사용하여 값을 편집할 수 있습니다.

UPDATE
    politicians   
SET
    displayName = "John Gilbert Alexander"
WHERE
    uniqueID = "Alexander1951";

CASE와 ELSE를 사용하여 if-else 논리를 통합할 수 있습니다. 예를 들어, “Josh Frydenberg”의 경우 “Yes”, “Kevin Rudd”의 경우 “No”, 다른 모든 경우 “Unsure”인 “wasTreasurer”라는 열을 추가할 수 있습니다.

SELECT
    uniqueID,
    surname,
    firstName,
    birthYear,
    CASE                  
        WHEN uniqueID = "Frydenberg1971" THEN "Yes"                  
        WHEN surname = "Rudd" THEN "No"                  
        ELSE "Unsure"            
    END AS "wasTreasurer"        
FROM
    politicians;

summarize() 대신 COUNT, SUM, MAX, MIN, AVG, ROUND와 같은 명령을 사용하여 요약 통계를 생성할 수 있습니다. COUNT는 열 이름을 전달하여 해당 열에 대해 비어 있지 않은 행의 수를 계산하며, MIN 등도 유사하게 작동합니다.

SELECT
    COUNT(uniqueID)   
FROM
    politicians;

SELECT
    MIN(birthYear)   
FROM
    politicians;

R의 group_by와 유사하게 GROUP BY를 사용하여 데이터셋의 다른 그룹을 기반으로 결과를 얻을 수 있습니다.

SELECT
    COUNT(uniqueID)   
FROM
    politicians     
GROUP BY
    gender;

마지막으로 LEFT JOIN을 사용하여 두 테이블을 결합할 수 있습니다. 점 표기법을 사용하여 일치하는 열을 지정하는 데 주의해야 합니다.

SELECT
    politicians.uniqueID,
    politicians.firstName,
    politicians.surname,
    party.partySimplifiedName   
FROM
    politicians 
LEFT JOIN
    party     
        ON politicians.uniqueID = party.uniqueID;

SQL은 본 도서의 주된 주제는 아니기에 여기서는 필수적인 명령어들만 간략히 살펴보았습니다. 하지만 실무에서 데이터 과학자로 성장하기 위해서는 SQL에 익숙해지는 과정이 반드시 필요합니다. “SQL 없이는 데이터 과학의 여정을 멀리 지속하기 어렵다” (Robinson and Nolis 2020, 8)는 말처럼, 거의 모든 데이터 과학 관련 채용 인터뷰에서는 여러분의 SQL 능력을 검증하려 할 것입니다 (Robinson and Nolis 2020, 110).

C.3 연습 문제

연습

무료 w3school SQL 퀴즈에서 최소 70%를 획득한 화면을 제출하십시오. 튜토리얼을 살펴보는 것도 좋지만, 이 장의 SQL 내용(dplyr 경험과 결합)만으로도 70%를 얻기에 충분합니다. 스크린샷에는 시간과 날짜가 포함되어야 합니다. 즉, 브라우저뿐만 아니라 전체 화면을 스크린샷하십시오.

퀴즈

1. SQL은 주로 무엇에 사용됩니까?
   1. 웹 페이지 스타일 지정
   2. 관계형 데이터베이스 관리 및 쿼리
   3. 머신러닝 모델 생성
   4. 그래픽 인터페이스 설계
2. SQL에서 테이블에서 특정 열을 검색하는 데 사용되는 명령은 무엇입니까?
   1. JOIN
   2. WHERE
   3. SELECT
   4. FROM
3. SQL에서 데이터를 쿼리할 테이블을 지정하는 데 사용되는 절은 무엇입니까?
   1. WHERE
   2. FROM
   3. GROUP BY
   4. SELECT
4. SQL 명령 WHERE는 무엇을 합니까?
   1. 두 테이블을 조인합니다.
   2. 데이터를 정렬합니다.
   3. 동일한 데이터를 가진 레코드를 그룹화합니다.
   4. 지정된 조건에 따라 레코드를 필터링합니다.
5. employees라는 테이블에서 모든 열을 선택하려면 어떻게 해야 합니까?
   1. SELECT # FROM employees;
   2. SELECT * FROM employees;
   3. SELECT all FROM employees;
   4. SELECT columns FROM employees;
6. 결과 집합에서 중복 행을 제거하는 데 사용되는 SQL 키워드는 무엇입니까?
   1. UNIQUE
   2. DISTINCT
   3. REMOVE
   4. DELETE
7. SQL 쿼리에서 LIMIT 절의 목적은 무엇입니까?
   1. 열의 최대값을 설정합니다.
   2. 반환되는 행의 수를 제한합니다.
   3. 표시되는 열의 수를 제한합니다.
   4. 액세스 제어를 적용합니다.
8. 쿼리 결과 집합을 정렬하는 데 사용되는 SQL 절은 무엇입니까?
   1. SORT
   2. ORDER BY
   3. SORT BY
   4. ORDER
9. SQL에서 LIKE 연산자와 함께 사용될 때 와일드카드 문자 _는 무엇을 나타냅니까?
   1. 모든 숫자
   2. 공백 문자
   3. 모든 단일 문자
   4. 0개 이상의 문자
10. firstName이 ’Ma’로 시작하고 그 뒤에 두 문자가 오는 레코드를 어떻게 선택합니까?
    1. WHERE firstName LIKE ’Ma*’;
    2. WHERE firstName LIKE ’Ma__’;
    3. WHERE firstName LIKE ‘Ma??’;
    4. WHERE firstName LIKE ‘Ma%’;
11. 데이터베이스 테이블에서 데이터를 업데이트하는 데 사용되는 SQL 문은 무엇입니까?
    1. SET
    2. CHANGE
    3. UPDATE
    4. MODIFY
12. SQL 쿼리에서 NULL 값을 확인하려면 어떻게 해야 합니까?
    1. WHERE column LIKE NULL
    2. WHERE column EQUALS NULL
    3. WHERE column = NULL
    4. WHERE column IS NULL
13. SQL에서 BETWEEN 연산자는 무엇을 합니까?
    1. 주어진 범위 내의 값을 선택합니다.
    2. 결과 집합을 정렬합니다.
    3. 값이 NULL인지 확인합니다.
    4. 여러 조건을 결합합니다.
14. SQL에서 각 그룹에 집계 함수를 적용할 수 있도록 속성을 공유하는 행을 그룹화하는 데 사용되는 SQL 키워드는 무엇입니까?
    1. GROUP BY
    2. HAVING
    3. DISTINCT
    4. ORDER BY
15. SQL에서 LEFT JOIN의 목적은 무엇입니까?
    1. 두 테이블의 모든 행을 결합합니다.
    2. 오른쪽 테이블의 모든 레코드와 왼쪽 테이블의 일치하는 레코드를 반환합니다.
    3. 왼쪽 테이블의 모든 레코드와 오른쪽 테이블의 일치하는 레코드를 반환합니다.
    4. 두 테이블에서 일치하는 값을 가진 행을 반환합니다.
16. SQL 쿼리에서 SELECT *를 사용하는 것이 모범 사례가 아닌 이유는 무엇입니까?
    1. 열을 지정하는 것보다 느립니다.
    2. 모든 SQL 종류에서 지원되지 않습니다.
    3. 데이터를 반환하지 않습니다.
    4. 데이터베이스 스키마가 변경되면 예상치 못한 결과가 발생할 수 있습니다.

과제

여기에서 SQL 데이터셋을 가져오십시오.

SQL(R 또는 Python 아님)을 사용하여 이 관찰 데이터를 사용하여 몇 가지 결과를 도출하십시오. Quarto를 사용하여 짧은 논문을 작성하십시오 (그래프를 만드는 데 R/Python을 사용해도 좋지만, 데이터 준비/조작에는 사용하지 마십시오. 이는 별도의 스크립트에서 SQL로 수행되어야 합니다). 토론에서 다음 각각에 대한 하위 섹션을 하나씩 포함하십시오: 1) 상관 관계 대 인과 관계; 2) 누락된 데이터; 3) 편향의 원인.

일반적인 기대치를 충족하는 GitHub 저장소 링크(그룹당 하나의 저장소)를 제출하십시오.

관련된 루브릭 구성 요소는 다음과 같습니다: “R/Python 인용”, “데이터 인용”, “수업 논문”, “LLM 사용 문서화”, “제목”, “저자, 날짜, 저장소”, “초록”, “서론”, “데이터”, “측정”, “결과”, “토론”, “산문”, “교차 참조”, “캡션”, “그래프/표/기타”, “참고 문헌”, “커밋”, “스케치”, “시뮬레이션”, “테스트”, “재현 가능한 워크플로우”.

Chamberlin, Donald. 2012. “Early History of SQL.” IEEE Annals of the History of Computing 34 (4): 78–82. https://doi.org/10.1109/mahc.2012.61.

R Special Interest Group on Databases (R-SIG-DB), Hadley Wickham, and Kirill Müller. 2022. DBI: R Database Interface. https://CRAN.R-project.org/package=DBI.

Robinson, Emily, and Jacqueline Nolis. 2020. Build a Career in Data Science. Shelter Island: Manning Publications. https://livebook.manning.com/book/build-a-career-in-data-science.

Wickham, Hadley, Maximilian Girlich, and Edgar Ruiz. 2022. dbplyr: A “dplyr” Back End for Databases. https://CRAN.R-project.org/package=dbplyr.