SQL 퀘스트 9, 10
문 37
patients 테이블에서 생년월일이 1980년대인 환자들의 수를 계산하는 쿼리를 작성해주세요!
내 답안
SELECT COUNT(1) AS born_in_80s
FROM patients
WHERE birth_date LIKE '%198%';
예시 답안
SELECT COUNT(1) AS born_in_80s
FROM patients
WHERE birth_date BETWEEN '1980-01-01' AND '1989-12-31'
- 헷갈린 점: birth_date column을 그대로 쓸 것인지, DATE()를 사용해 쓸 것인지 고민했다.
- 개선점: DBeaver로 테스트했을 떄, 내 답안과 예시 답안이 같은 결과가 나왔다. 하지만 코드 가독성 면에서 예시 답안이 더 좋다. 80년대 출생자만 선택하겠다는 의도가 명확히 읽히기 때문이다.
문 39
모든 직원과 그들이 속한 부서의 이름을 나열하는 쿼리를 작성해주세요!
내 답안
SELECT e. name,
d. name
FROM employees e LEFT JOIN departments d ON e.department_id=d.id;
예시 답안
SELECT e. name,
d. name
FROM employees e INNER JOIN departments d ON e.department_id=d.id;
- 헷갈린 점: 이 테이블의 경우 LEFT JOIN이나 INNER JOIN이나 같은 결과가 나올 것으로 예상된다. 같은 결과인데 왜 INNER JOIN을 쓰는 걸까?
- 개선점: GPT에게 물어본 결과, 코드의 의도도 중요하다고 한다.
다음과 같이 이해했다. 문제에서 직원과 부서 이름을 나열하라고 한다. LEFT JOIN을 사용하면 직원 이름이 없거나 부서 이름이 없는 경우도 선택된다. 빈 칸은 의미 없는 내용이므로 빈 칸은 포함하지 않는 것이 의도에 부합할 것이다. 이 테이블에서는 LEFT JOIN과 INNER JOIN의 결과가 같다. 하지만 테이블이 수정될 때에도 이 의도가 반영되도록 하려면 INNER JOIN을 쓰는 것이 적합하다.
1. JOIN 비교하기
1) LEFT JOIN
- 공통 column을 기준으로, 하나의 테이블에 값이 없더라도 모두 조회되는 경우
- 예시: 고객 ID가 공통 column
2) INNER JOIN
- 공통 column을 기준으로, 두 테이블 모두에 있는 값만 조회한다. LEFT JOIN 예시의 주문번호 14는 고객에 대한 정보가 고객 정보에 없으므로 조회 시 제외된다.
- 예시
문 40
'기술팀' 부서에 속한 직원들의 이름을 나열하는 쿼리를 작성해주세요!
내 답안
SELECT e. name
FROM employees e LEFT JOIN departments d ON e.department_id=d.id
WHERE d.name = '기술팀';
예시 답안
SELECT e. name
FROM employees e INNER JOIN departments d ON e.department_id=d.id
WHERE d.name = '기술팀';
- 헷갈린 점: 39번 문제와 동일
- 개선점: INNER JOIN이 문제의 의도에 더 부합한다는 것을 이해하기
이 문제의 경우 기술팀 소속인 직원의 이름을 나열하는 것이다. 기술팀은 d.name에, 직원 이름은 e.name에 있다.
INNER JOIN으로 연결하면, 기술팀 소속인 직원만 선택된다. 부서가 없는 직원은 제외된다.
LEFT JOIN으로 연결하면,
부서가 없고 직원 이름이 있는 경우, 부서가 있고 직원 이름이 없는 경우도 테이블에 포함된다.
이 상황에서 WHERE절에서 d.name = '기술팀' 조건을 넣으면 부서가 있고 직원 이름이 없는 경우가 포함된다.
기술팀 직원 이름 나열하는 것이 목표이므로 NULL이 포함될 수 있는 LEFT JOIN 대신 INNER JOIN을 사용한다.
문 41
부서별로 직원 수를 계산하는 쿼리를 작성해주세요!
내 답안
SELECT d.name,
COUNT(1)
FROM employees e LEFT JOIN departments d ON e.department_id=d.id
GROUP BY d.name;
예시 답안
SELECT d.name,
COUNT(e.id)
FROM employees e LEFT JOIN departments d ON e.department_id=d.id
GROUP BY d.id;
- 헷갈린 점: 어떤 column으로 그룹을 묶을지 헷갈렸다. COUNT()에 어떤 column을 넣어야 할 지 헷갈렸다.
- 개선점: 코드 작성 과정시 최종 목표를 작은 목표로 나누어 작성한다.
최종 목표:
부서별 직원 수 → 부서 수는 department table의 id column
직원 수는 employees table의 id column (e의 primary key)
세부 목표 1: table 2개에 있는 정보가 다 필요하다 → JOIN
세부 목표 2:
어떤 JOIN 사용? → LEFT JOIN
직원이 없는 부서가 있다면,
INNER JOIN 시 department table의 모든 부서를 출력하지 못할 수도 있다.
모든 부서를 출력하기 위해 LEFT JOIN 사용
세부 목표 3: 부서별 → GROUP BY d.id
세부 목표 4: 직원 수 → SELECT COUNT(e.id)
- 개선점: 이 문제에서 COUNT에 e 테이블의 다른 column 대신 primary key를 쓰는 이유를 알아둔다.
primary key는 고유 식별자로 NULL 값을 가지지 않는다.
column에 NULL이 있으면 그 부분은 제외하고 세어서 잘못된 결과 나올 수도 있다.
primary key는 NULL이 없어 정확하게 셀 수 있다.
1. Relational Schemas: SQL - MySQL for Data Analytics and Business Intelligence by 365 Careers, Udemy
1) PRIMARY KEY
- a column (or a set of columns) whose value exists and is unique for every record in a table is called a primary key
- in one table, you cannot have 3 or 4 primary keys
- each table can have one and only one primary key
- primary keys are the unique identifiers of a table
- cannot contain null values
- not all tables you work with will have a primary key
2) Foreign Key
- identifies the relationships between tables, not the tables themselves
3) Unique Key
- used whenever you would like to specify that you don’t want to see duplicate data in a given field
-
| primary key | unique key | |
| NULL VALUES | no | yes |
| NUMBER OF KEYS | 1 | 0, 1, 2 |
| APPLICATION TO MULTIPLE COLUMNS |
yes | yes |
문 42
직원이 없는 부서의 이름을 찾는 쿼리를 작성해주세요!
내 답안
SELECT d.name
FROM employees e LEFT JOIN departments d ON e.department_id=d.id
WHERE e.name is NULL;
예시 답안
SELECT d.name
FROM departments d LEFT JOIN employees e ON d.id = e.department_id
WHERE e.id IS NULL;
- 헷갈린 점 1: WHERE에 e.id를 넣는 이유가 무엇일까?
e.id는 e 테이블의 pk이다. pk는 NULL 값이 없는 것이 아닌지? 없는 값을 어떻게 찾지?
- 개선점 1:
테이블 하나만 고려할 때 → e 테이블에서 e.id는 pk이므로 NULL이 없다.
JOIN 후 → 왼쪽 테이블 d를 기준으로 LEFT JOIN을 했으므로,
오른쪽 테이블인 e의 column인 e.id에 NULL이 있을 수 있다.
- 헷갈린 점 2: JOIN 후에도 NULL이 없는 column은 주 테이블(parent table)의 pk인가?
- 개선점 2:
Database Schema: 여러 table 간 연결 관계를 정의. 주 테이블의 pk와 자식 테이블의 fk가 연결된다.
LEFT JOIN: 두 테이블의 값을 동시에 불러오고 싶을 때, 기준이 되는 테이블(왼쪽)의 행 모두 불러온 뒤,
그에 맞는 오른쪽 테이블의 데이터 가져온다.
→ JOIN 시 NULL이 없는 column은 JOIN 기준이 되는 테이블의 pk이다.
JOIN 기준이 되는 테이블이 꼭 주 테이블(parent table)이어야 할 필요는 없다.
1. LEFT JOIN과 RIGHT JOIN 정리
1) LEFT JOIN
-
SELECT
FROM A
LEFT JOIN B
ON A.column1=B.column2
- 이 때 A가 왼쪽에, B가 오른쪽에 있다.
- 왼쪽 table A의 모든 행(record)을 가져온다.
왼쪽 table의 모든 행 기준으로 오른쪽 table B의 매칭되는 행을 가져온다.
오른쪽 table에 일치하는 값 없으면 NULL이 된다.
- RIGHT JOIN을 사용하지 않고, LEFT JOIN에서 table 순서만 바꿔도 된다.
2) RIGHT JOIN
-
SELECT
FROM A
RIGHT JOIN B
ON A.column1=B.column2
- 이 때 A가 왼쪽에, B가 오른쪽에 있다.
- 오른쪽 table B의 모든 행(record)을 가져온다.
오른쪽 table의 모든 행 기준으로 왼쪽 table A의 매칭되는 행을 가져온다.
왼쪽 table에 일치하는 값 없으면 NULL이 된다.
//