공부블로그

SQL 쿼리 작성: 점수 기준 학생 이름 조회 및 정렬

문제 설명

우리는 학교 데이터베이스의 STUDENTS 테이블을 사용하여 75점 이상의 점수를 받은 학생들의 이름을 조회하고 싶습니다. 또한, 조회된 결과를 각 이름의 마지막 세 글자를 기준으로 정렬하며, 같은 세 글자로 끝나는 이름이 여러 개 있을 경우에는 학생의 ID 순으로 오름차순 정렬하려고 합니다.

테이블 구조

STUDENTS 테이블은 다음과 같은 구조를 가지고 있습니다:

ColumnType

SQL 쿼리

SELECT Name
FROM STUDENTS
WHERE Marks > 75
ORDER BY RIGHT(Name, 3), ID;

쿼리 설명

1. SELECT 문: Name 열에서 데이터를 선택합니다. 이는 출력될 데이터가 학생들의 이름임을 의미합니다.
2. FROM 절: STUDENTS 테이블에서 데이터를 가져옵니다.
3. WHERE 절: Marks 열의 값이 75를 초과하는 행만 필터링합니다. 이 조건은 우리가 관심 있는 학생들, 즉 75점 이상을 받은 학생들만을 대상으로 합니다.
4. ORDER BY 절: 먼저 RIGHT(Name, 3) 함수를 사용하여 각 이름의 마지막 세 글자를 기준으로 결과를 정렬합니다. 동일한 세 글자로 끝나는 이름이 여러 개인 경우, ID 열을 기준으로 추가적인 오름차순 정렬을 수행하여 결과의 순서를 결정합니다.

결론

이 SQL 쿼리는 데이터베이스에서 특정 조건에 맞는 데이터를 효율적으로 필터링하고 정렬하는 간단하지만 강력한 예제를 보여줍니다. 특히, RIGHT() 함수와 같은 문자열 함수를 사용하여 복잡한 정렬 조건을 쉽게 처리할 수 있음을 보여줍니다. 이러한 기술은 다양한 데이터 처리 상황에서 유용하게 사용될 수 있습니다.

도수분포표와 히스토그램: 데이터의 특징을 돋보이게 하는 도구

• 데이터 자체(단순한 숫자의 나열)을 뚫어져라 처다본다고 해서 어떤 정보를 속시원하게 알수가 없다.
• ex) 80명의 키를 정리한 데이터를 보고 어떠한 정보를 살펴볼수 있는지 알아보자
  • 데이터를 본다면 모두 같지 않고 제각각의 수치로 나타난다는 점을 알수 있다.
  • 이렇게 데이터가 다양한 수치로 나타나는 것을 전문적인 용어로 분포한다(discribute)라고 표현한다.

학생 80명의 키(cm)

151   154   160   160   163   156   158   156   154   160
154   162   156   162   157   162   162   169   150   162
154   152   161   160   160   153   155   163   160   159
164   158   150   155   157   161   168   162   153   154
158   151   155   155   165   165   154   148   169   158
146   166   161   143   156   156   149   162   159   164
162   167   159   153   146   156   160   151   151   157
151   156   166   159   157   156   159   156   156   161

• 분포가 생기는 이유
  • 수치들이 결정된 이면에 어떤 불확실성(uncertainty)이 움직이고 있기 때문이다.
  • 불확실성의 구조가 제각각인 키의 수치를 발생시킨다고 생각하는 것이다.
  • 불확실이라는 말로 표현 해도 여기에는 고유한 특징이나 반복되는 것이 있는데 이를 분포의 특성이라고 한다.
  • 분포의 특성과 반복되는것을 이끌어 내기 위한 것이 통계이다.
• 통계에서 사용되는 방법 '축약'
  • 데이터로 나열되어 있는 많은 숫자를 어떤 기준으로 정리정돈해서 의미 있는 정보만을 추출하는 것으로 2가지 방법을 사용한다.
    1. 그래프로 만들어서 그 특징을 파악할 수 있도록 해야한다.
    2. 숫자 하나로 특징을 대표하도록 한다.
  • 축약을 대표하는 숫자를 통계량이라고 한다.

히스토그램(막대그래프) 만들기

• 히스토그램을 만들기 위해서는 도수분표표를 만들어야 한다.

도수분표표 만들기

1. 데이터 중에서 수치가 가장 큰 것(최대값)과 가장 작은 것 (최소값)을 찾는다.
2. 최대값부터 최소값까지 포함되도록 하여 구간을 자르기 좋은 범위(작은 구간)들로 자른다. 이렇게 작은 범위를
   '계급'(class)라고 한다.
3. 계급을 대표하는 수치를 정한다. 기본적으로 아무 값이나 대표로 정해도 되지만, 일반적으로 가장 가운데 값을 선택하는 경우가 많다. 이값을 '계급값'(class value)라고 한다.
4. 각 계급에 들어가 있는 데이터의 총 개수를 센다 이것을 '도수(frequency)'라고 한다.
5. 각 계급의 도수가 전체에서 차지하는 비율을 계산한다. 이것을 상대도수(relative frequency)라고 한다. 상대도수는 합하면 1이 된다.
6. 어느 계급까지의 도수를 모두 합한다. 이것을 누적도수(cumulative frequency)라고 한다. 최종 누적도수는 데이터의 전체 개수와 같다.\

도수분포표의 특징

• 도수분표표를 만들면 잃어버리는 정보가 있다.
  • 데이터에 나타나 있던 수치들 자체이다.
  • 예를 들어 제 4계급인 150 ~ 160가지의 범위를 보면 30개의 데이터가 있다는걸 알 수 있지만 세부적인 수치는 알수가 없다. 
    • 도수분표표를 만드는 과정에서 축약을 하는 과정으로 인해 발생한 일이다.
• 도수분표표를 통해 얻을 수 있는 정보가 있다.
  • 도수를 통해 다음과 같은 데이터의 특징을 발견할 수 있다.
    • 데이터는 균등하게 분포하지 않고 어느 한 곳에 (156 ~ 160) 계급에 집중되어 있다.
    • 집중되어 있는 곳을 기점으로 삼으면 작은편에 속하는지 큰편에 속하는지 추이를 알 수 있다. 즉 데이터 분포에 어느 한곳을 축으로 좌우 대칭성이 있다는 것이다.
  • 여성들의 키가 정해지게 된 구조 뒤에는 어떠한 불확실성이 있지만 여기에는 고유한 특징이 있다.
    1. 어떠한 수치든지 가능한 것은 아니다.
    2. 어느 한 키(158cm) 주변에 집중되어 있다.
    3. 158cm 기점으로 해서 큰편과 작은 편은 '수치가 별로 보이지 않는다는점이 서로 비슷하다.
  • 이러한 특징은 데이터 자체를 그냥 보았을 때 몰랐던 정보이다.
• 데이터의 축약은 세부적인 수치를 희생시키지만 이 희생으로 데이터의 분포와 그 이면에 있는 특징들이 돋보이게 된다.
  • 즉 데이터의 축약은 요점을 정리하는 작업

 예제의 히스토그램

출처: 세상에서 가장 쉬운 통계학 입문

SQL 쿼리 튜토리얼: 특정 패턴을 만족하는 도시 이름 조회하기

데이터베이스에서 정보를 추출하는 것은 데이터 과학 및 소프트웨어 개발에서 매우 중요한 기술입니다. 오늘은 특정 조건을 만족하는 데이터를 조회하는 방법에 대해 알아보겠습니다.

1. 테이블 구조

먼저, 우리가 작업할 데이터베이스 테이블인 STATION의 구조부터 살펴보겠습니다. 이 테이블은 다음과 같은 필드를 포함하고 있습니다:

FieldType

이 테이블에서 CITY 필드는 도시 이름을 저장하며, 문자형 데이터로 최대 21자까지 입력 가능합니다.

2. 쿼리 목표

우리의 목표는 모음으로 시작하거나 모음으로 끝나지 않는 도시 이름을 조회하는 것입니다. 결과는 중복 없이 제공되어야 합니다.

3. SQL 쿼리

다음 SQL 쿼리는 위의 조건을 만족하는 도시 이름을 STATION 테이블에서 조회합니다

SELECT DISTINCT CITY
FROM STATION
WHERE CITY NOT LIKE 'A%' AND CITY NOT LIKE 'E%' AND CITY NOT LIKE 'I%' AND CITY NOT LIKE 'O%' AND CITY NOT LIKE 'U%'
AND CITY NOT LIKE '%a' AND CITY NOT LIKE '%e' AND CITY NOT LIKE '%i' AND CITY NOT LIKE '%o' AND CITY NOT LIKE '%u';

4. 결론

이 튜토리얼을 통해 SQL의 강력한 패턴 매칭 기능과 중복 제거 기능을 사용하여 원하는 데이터를 정확하게 추출할 수 있는 방법을 배웠습니다. 데이터베이스에서 정보를 효과적으로 처리하고 관리하는 능력은 다양한 프로젝트와 직무에서 큰 이점을 제공합니다.

SQL 쿼리 문제 해결: 도시 이름 필터링

문제 설명

데이터베이스의 'STATION' 테이블에서 'CITY' 컬럼을 조회하는 작업에서, 특정 조건에 맞는 도시 이름을 추출해야 하는 문제를 다룹니다. 우리가 해결해야 할 문제는 도시 이름이 모음으로 시작하지 않거나 모음으로 끝나지 않는 도시를 찾는 것입니다. 또한, 결과에 중복된 도시 이름이 없어야 합니다.

테이블 구조

'STATION' 테이블의 구조는 다음과 같습니다:

FieldType

조건

• 도시 이름은 모음(A, E, I, O, U)으로 시작하거나 끝나면 안 됩니다.
• 결과는 중복 없이 유일한 도시 이름만 포함해야 합니다.

SQL 쿼리

이 문제를 해결하기 위한 SQL 쿼리는 다음과 같습니다:

SELECT DISTINCT CITY
FROM STATION
WHERE CITY NOT LIKE 'A%' AND CITY NOT LIKE 'E%' AND CITY NOT LIKE 'I%' AND CITY NOT LIKE 'O%' AND CITY NOT LIKE 'U%'
OR CITY NOT LIKE '%a' AND CITY NOT LIKE '%e' AND CITY NOT LIKE '%i' AND CITY NOT LIKE '%o' AND CITY NOT LIKE '%u';

쿼리 설명

이 쿼리는 STATION 테이블에서 CITY 필드를 검색합니다. WHERE 절은 도시 이름이 모음으로 시작하지 않거나 모음으로 끝나지 않는 조건을 검사합니다. 'AND'와 'OR' 연산자의 적절한 사용으로, 어느 한 조건이라도 만족하는 도시 이름을 선택합니다. DISTINCT 키워드는 중복을 제거하여 각 도시 이름이 한 번씩만 나타나도록 합니다.

결과

이 쿼리 실행 결과, 도시 이름이 예를 들어 "Boston"이나 "Phoenix"와 같이 모음으로 시작하거나 끝나지 않는 도시들이 추출됩니다. 도시 이름이 "Anaheim"이나 "Irvine"과 같이 모음으로 시작하는 경우는 제외됩니다.

SQL 문제 해결하기: 모음으로 끝나지 않는 도시 이름 찾기

테이블 구조

다음은 'STATION' 테이블의 구조를 나타내는 표입니다. 우리의 쿼리에서는 'CITY' 필드에 주목할 것입니다.

FieldType

문제 정의

이 SQL 문제에서는 'STATION' 테이블의 데이터 중에서 도시 이름(CITY)이 모음(a, e, i, o, u)으로 끝나지 않는 도시들을 찾아야 합니다. 또한, 결과에 도시 이름이 중복되지 않도록 해야 합니다.

SQL 쿼리 작성

1. 중복 제거: DISTINCT 키워드를 사용하여 도시 이름이 중복되지 않게 합니다.
2. 조건 설정: NOT LIKE 연산자를 사용하여 도시 이름이 특정 모음으로 끝나지 않도록 조건을 설정합니다.

SQL 쿼리

SELECT DISTINCT CITY
FROM STATION
WHERE CITY NOT LIKE '%a' AND CITY NOT LIKE '%e' AND CITY NOT LIKE '%i' AND CITY NOT LIKE '%o' AND CITY NOT LIKE '%u';

쿼리 설명

• SELECT DISTINCT CITY: 'CITY' 필드에서 중복되지 않는 값을 선택합니다.
• FROM STATION: 'STATION' 테이블에서 데이터를 조회합니다.
• WHERE 조건: 도시 이름이 'a', 'e', 'i', 'o', 'u'로 끝나지 않는 도시만 선택하도록 조건을 설정합니다. 각 모음에 대해 NOT LIKE '%<모음>'을 사용하여 해당 조건을 충족합니다.

실행 결과

이 쿼리를 실행하면 모음으로 끝나지 않는 도시 이름을 중복 없이 조회할 수 있습니다. 실제 데이터베이스의 내용에 따라 결과는 다르게 나타날 수 있지만, 이 쿼리는 주어진 조건을 충족하는 모든 도시 이름을 정확히 필터링할 것입니다.

이와 같은 방식으로 SQL 쿼리를 작성하고 이해하는 방법을 익히면 다양한 데이터베이스 문제를 효과적으로 해결할 수 있습니다  감사합니다.