안녕하세요~!~~! 커피러브입니다.
오늘은 학교 데이터 엔지니어링 수업 과제로 classicmodels DB 데이터 분석을 진행한 걸 정리해보려고 해요~~
저는 리눅스 터미널이 익숙해서 거기서 진행했지만, workbench를 사용해도 똑같습니다~~
classicmodels DB는 MySQL에서 연습용으로 제공하는 Sample Database로, https://www.mysqltutorial.org/getting-started-with-mysql/mysql-sample-database/해당 공식 사이트에서 다운로드 가능합니다~~!~!
1) [조건+LIKE+NULL+정렬+집계함수+그룹] 검색 작업 수행
1. where + like
만약, 우리가 customers 테이블에서 'Inc.'로 끝나는, 즉 법인회사인 고객들의 이름과 전화번호를 찾고 싶다고 하면, 다음과 같이 sql문을 짤 수 있습니다.
select customerName, phone from customers where customerName like '%Inc.';
위처럼 이름과 전화번호가 출력된 걸 볼 수 있습니다.
2. where + null + 집계함수 + 그룹
만약, 우리가 orders 테이블에서, 아직 발송을 안 한 주문들을 대상으로 그 개수를 그룹별로 세서, status와 개수를 같이 출력하고 싶다고 한다면, 다음과 같이 sql문을 짤 수 있습니다.
SELECT status, COUNT(*) AS totalOrders
FROM orders
WHERE shippedDate IS NULL
GROUP BY status;
그 결과는 위와 같이 나와, 지금 각 상태에 대해 발송이 안 된 주문들이 몇 개나 있는지 알 수 있습니다(물론 그냥 shippedDate is NULL인 것만 센 것이기 때문에, 발송을 아직 안한 건지 아니면 위처럼 Cancelled된 건지는 사용자가 판단해야 할 겁니다).
참고로, NULL 라인을 제외하면 위처럼 모든 행에 대해서 나오게 됩니다.
3. 정렬
만약, 우리가 offices 테이블에서 각 오피스들의 country, state, city를 출력하려고 하는데, 저 순서대로 오름차순으로 출력하고 싶다면, 다음과 같이 sql문을 짤 수 있습니다.
SELECT officeCode, country, state, city
FROM offices
ORDER BY country ASC, state ASC, city ASC;
이러면 우리가 지정해 둔 정렬 순서대로, 같은 요소의 경우 다음 순위의 정렬 순서로 정렬되는 걸 볼 수 있습니다. (단, 이 예시의 경우는 country가 같은 행끼리 state까지 같은 경우는 없었습니다.)
2) [Inner+Left+Right+Full] 조인 작업 수행
1. Inner Join
만약 우리가 각 product에 대해 살펴볼 건데, 여기서 각 product가 속하는 productLine에 대한 정보 또한 보고 싶다면, products 테이블과 productLines 테이블을 inner join해줘야 합니다.
아래 예시의 경우는 각 product에 대해 볼 건데, 그 product가 속하는 productline에 대한 textDescription을 출력합니다(너무 길어서 텍스트는 30까지로 끊었습니다.)
첫 번째 살펴볼 inner join은 두 테이블에서 해당 요소가 같을 경우만 검색하여 줍니다.
SELECT p.productCode, p.productName, pl.productLine, left(pl.textDescription, 30)
FROM products p
INNER JOIN productlines pl ON p.productLine = pl.productLine;
그럼, 위와 같이 출력되게 됩니다.
2. Left Join
만약 우리가 테이블 간의 무결성 확인을 위해, 주문은 있지만 주문 상세 정보는 없는 행이 있는지 확인하고 싶다면, 다음과 같이 sql문을 짤 수 있습니다.
SELECT o.orderNumber
FROM orders o
LEFT JOIN orderdetails od ON o.orderNumber = od.orderNumber
WHERE od.orderNumber IS NULL;
보면 orders를 left로 하여 left join을 하고 있기에, orderdetails에는 없지만 orders에는 있는 행 또한 전부 다 포함하여 검색하고 있는데, 그 중에서 orderdetails에는 존재하지 않는 행이 있는지 여부를 검색하고 있습니다.
실행 결과, Empty Set이 나와 두 테이블 간에는 적어도 orders에는 있는 행이 orderdetails에는 없는 경우는 없다는 걸 알 수 있습니다.
3. Right Join
이 때, 위에 Left Join으로 실행한 결과와 반대로, orders에는 없지만 orderdetails에는 있는 행 또한 존재하는지 확인해보려면, 다음과 같이 sql문을 짤 수 있습니다.
SELECT od.orderNumber
FROM orders o
RIGHT JOIN orderdetails od ON o.orderNumber = od.orderNumber
WHERE o.orderNumber IS NULL;
보면 아까와 달리 이번엔 Right 위치에 orderdetails가 존재해, orderdetails를 기준으로 잡고 검색을 수행하게 됩니다.
그리고 해당 실행 결과 또한 Empty set이 나온 것을 토대로, 결국 orders 테이블과 orderdetails 두 테이블에는 한 쪽에 있는 것이 다른 쪽에 없는 무결성 문제가 없는 것으로 일단은 결론지어볼 수 있습니다.
4. Full Join
위처럼 left join과 right join을 따로 따로 수행할 수도 있지만, 하나의 sql문으로 수행할 수도 있는데 그걸 full (outer) join이라 부릅니다.
즉, 지정한 요소들이 같지 않다(매칭이 되지 않는다)고 하더라도, 양쪽의 것들을 모두 검색하여 주기에, 결국 같은 조건 하에 수행한 left join과 right join 결과의 '합집합'이라고 볼 수 있을 것 같습니다.
다만 mysql에서는 'full join'과 같은 특정 키워드가 존재하는 것은 아니라, 두 join의 union으로 full join을 구현해야 합니다. 이를 구현한 sql문은 아래와 같습니다.
SELECT e.employeeNumber, e.firstName, e.lastName, o.officeCode, o.city
FROM employees e
LEFT JOIN offices o ON e.officeCode = o.officeCode
UNION
SELECT e.employeeNumber, e.firstName, e.lastName, o.officeCode, o.city
FROM employees e
RIGHT JOIN offices o ON e.officeCode = o.officeCode;
(union은 자동으로 중복을 제거하기에 중복을 포함해 보고 싶으면 union all)을 써야 합니다.
수행 결과, 만약 직원과 사무실 매칭이 안 되는 것이 있다고 하더라도, 그것들까지 포함해서 출력하게 됩니다.