면접을 위한 SQL 기본 및 중급 질문-MySQL 튜토리얼-php.cn

본론으로 들어가겠습니다.

인터뷰 질문을 준비하고 SQL 쿼리를 연습하기 위해 Awesome SQL Interview GitHub 저장소를 만들었습니다. SQL 쿼리를 기본(L0), 중급(L1), 고급(L2)의 세 가지 섹션으로 나누었습니다. 기본 구간에 대한 솔루션입니다.

연습할 L1(중급) SQL 쿼리입니다. 더 나은 연습을 위해 먼저 L0을 참조하세요.

참고: 이 예는 MySQL에서 테스트되었습니다. MS-SQL 또는 Oracle과 같은 다른 데이터베이스에서는 구문이 다를 수 있습니다.

L1: 중간 SQL

JOIN, GROUP BY, HAVING 및 복잡한 WHERE 조건을 사용하여 여러 테이블 작업과 관련된 쿼리
하위 쿼리, 집계 함수, 사례문 소개

질문:

'미국'과 '프랑스' 고객의 고객 이름과 도시를 검색하는 쿼리를 작성하세요.
'샌프란시스코' 사무실에서 근무하는 모든 직원의 EmployeeNumber, LastName, OfficeCode를 어떻게 가져오나요?
주문 및 고객 테이블을 사용하여 각 고객의 총 주문 수를 찾는 쿼리를 작성하세요.
10회 이상 주문한 제품의 productName, amountInStock, buyPrice를 어떻게 검색하나요?
customerNumber가 103인 고객이 주문한 주문에 대해 orderNumber, status 및 customerName을 가져오는 쿼리를 작성합니다.
orderdetails 테이블에서 각 주문의 총 판매액(수량주문 * 가격각)을 찾는 쿼리를 작성하세요.
주문 세부정보 테이블에서 각 주문 번호에 대해 주문한 평균 수량을 어떻게 알 수 있나요?
orderdetails 테이블에서 총 수익(수량주문 * 가격각)이 가장 높은 productLine을 나열하는 쿼리를 작성합니다.
직원 테이블과 사무실 테이블을 조인하여 직원 번호, 이름, 성, 직원이 근무하는 사무실 이름을 표시하는 쿼리를 작성하세요.
주문한 적이 없는 고객은 어떻게 찾나요?
customerName과 각 고객(아무 주문도 하지 않은 고객 포함)의 총 주문 수를 검색하는 쿼리를 작성합니다.
주문한 제품 수량이 50개를 초과하는 모든 주문에 대해 productName 및 QuantityOrdered를 찾는 쿼리를 작성합니다.
주문한 고객에게 영업 담당자로 지정된 직원의 EmployeeNumber, firstName 및 orderNumber를 검색합니다.
buyPrice를 기준으로 제품 테이블에 있는 제품의 평균 가격을 계산하는 쿼리를 작성하세요.
제품 테이블에서 가장 비싼 상위 3개 제품을 어떻게 가져오나요?
상태가 '배송됨'인 모든 주문의 customerName, orderNumber 및 orderDate를 검색하는 쿼리를 작성하세요.
각 제품군별로 판매된 총 제품 수를 어떻게 표시하나요?
employmentNumber = 1143인 직원에게 직접 보고하는 직원을 찾는 쿼리를 작성하세요.
상태별로 그룹화된 주문 테이블의 총 주문 수를 계산하는 쿼리를 작성하세요.
직원의 이름을 관리자 이름과 함께 나열하세요.

잘못된 점에 대해서도 언급하겠습니다. 무엇을 해야 하는지 아는 것도 중요하지만, 무엇을 하지 말아야 하는지, 어디서 실수를 하는지 아는 것도 매우 중요합니다. 다시 본론으로 들어가죠...

필요한 설명이 포함된 솔루션

'미국'과 '프랑스' 고객의 고객 이름과 도시를 검색하는 쿼리입니다.
- 또는 -> 조건이 많으면 쿼리가 조건을 하나씩 확인하기 때문에 약간 느려집니다.
- 에 -> 특히 긴 목록의 경우 데이터베이스 엔진에 의해 내부적으로 약간 최적화되었습니다.
- 2~3가지 조건이면 둘 다 괜찮습니다. 가독성과 확장성을 위해서는 특히 더 큰 값 목록을 처리할 때 IN이 더 좋습니다.
- IS는 문자열 비교가 아닌 IS NULL 또는 IS NOT NULL과 같은 조건을 확인하는 데 사용됩니다.
'샌프란시스코' 사무실에서 근무하는 모든 직원의 EmployeeNumber, LastName 및 officeCode를 가져옵니다.
주문 테이블과 고객 테이블을 이용하여 고객별 총 주문 건수를 구하는 쿼리입니다.
- 쿼리에서 집계 함수를 사용할 때는 항상 GROUP BY 절에 집계되지 않은 열을 포함하세요.
- 이를 통해 SQL은 행을 그룹화하는 방법을 알고 추가 열을 선택할 때 모호함을 피할 수 있습니다.
- 이 예에서는 customerNumber와 customerName이 COUNT(*)와 함께 선택되므로 둘 다 GROUP BY 절에 있어야 합니다.
? 황금률:
SELECT 목록의 모든 열은 다음 중 하나를 충족해야 합니다.
GROUP BY 절에 있거나
COUNT(), SUM() 등과 같은 집계 함수를 사용하세요.
10회 이상 주문한 제품의 productName, QuantumInStock, buyPrice를 검색하시겠습니까?
- 이 쿼리는 중소 규모 데이터베이스에 효율적이며, 대규모 데이터베이스의 경우 인덱스를 사용할 수 있고 HAVING 절에만 의존하는 대신 WHERE 절을 사용하여 스캔되는 데이터를 줄일 수 있습니다.
customerNumber가 103인 고객이 주문한 주문에 대해 orderNumber, status 및 customerName을 가져옵니다.

설명:
- 사용된 테이블:
  - 주문: 주문 번호 및 상태가 포함됩니다.
  - 고객: customerName을 포함합니다.
- 내부 조인:
  - customerNumber 열(공통 키)을 사용하여 주문과 고객 테이블을 결합합니다.
- WHERE 절:
  - customerNumber = 103인 레코드만 포함하도록 데이터를 필터링합니다.
- 선택한 열:
  - o.orderNumber: 주문 번호입니다.
  - o.status: 주문 상태
  - c.customerName: 주문하는 고객의 이름
주문 세부정보 테이블에서 각 주문의 총 판매액(수량주문 * 가격개당)을 확인하세요.
주문 세부정보 테이블에서 각 주문 번호에 대해 주문한 평균 수량을 확인하세요.
- 설명:
- 주문번호:
  - orderNumber를 기준으로 행을 그룹화합니다.
- AVG(주문 수량):
  - 동일한 orderNumber에 속하는 모든 행에 대해 Ordered 평균 수량을 계산합니다.
- 그룹 기준:
  - 각 주문 번호에 대해 개별적으로 평균이 계산되도록 합니다.
주문 세부정보 테이블에서 총 수익(수량주문 * 가격각)이 가장 높은 productLine을 나열하는 쿼리입니다.
- 설명:
- 제품 라인:
  - 제품을 '오토바이', '비행기' 등 다양한 라인으로 분류합니다.
- SUM(od.QuantityOrdered * od.priceEach):
  - 각 제품군의 총 수익을 계산합니다.
- 내부 조인:
  - productCode의 제품 및 주문 세부정보 테이블을 조인하여 제품 라인을 주문 세부정보와 연결합니다.
- p.productLine별 그룹:
  - 각 제품 라인별로 결과를 그룹화합니다.
- totalRevenue DESC 기준 주문:
  - 그룹화된 결과를 수익 내림차순으로 정렬하므로 가장 높은 수익이 먼저 표시됩니다.
- 한도 1:
  - 수익이 가장 높은 productLine으로만 결과를 제한합니다.
직원 테이블과 사무실 테이블을 조인하여 직원 번호, 이름, 성, 직원이 근무하는 사무실 이름을 표시하는 쿼리입니다.
- CONCAT(열, '구분자', 열, '구분자', 열)
- CONCAT_WS('구분자', 열)
한 번도 주문한 적이 없는 고객 찾기

설명:
- LEFT JOIN: 주문 테이블에 일치하는 행이 있는지 여부에 관계없이 고객 테이블에서 모든 고객을 검색합니다.
- o.orderNumber IS NULL: 해당 주문이 없는 고객을 식별합니다(예: 주문 테이블에 일치하는 항목이 없기 때문에 orderNumber는 NULL입니다).
- 열:
  - customerNumber: 고객의 고유 식별자입니다.
  - customerName: 고객의 이름입니다.
customerName과 각 고객이 주문한 총 주문 수(주문하지 않은 고객 포함)를 검색하는 쿼리입니다.
주문한 제품 수량이 50개를 초과하는 모든 주문에 대해 productName 및 QuantityOrdered를 찾습니다.
주문한 고객에게 영업 담당자로 지정된 직원의 EmployeeNumber, firstName 및 orderNumber를 검색합니다.

설명:
- 직원으로부터 전자:
  - 직원 세부 정보, 특히 EmployeeNumber 및 FirstName을 원하기 때문에 직원 테이블(별칭 e)부터 시작합니다.
- 고객 가입 c ON e.employeeNumber = c.salesRepEmployeeNumber:
  - 직원의 EmployeeNumber와 고객의 salesRepEmployeeNumber에 대한 고객 테이블(별칭 c)을 조인합니다. 이를 통해 직원(영업 담당자)과 고객 간의 관계가 형성됩니다. 이제 각 고객에게 어떤 직원이 배정되어 있는지 확인할 수 있습니다.
- JOIN 주문 o ON c.customerNumber = o.customerNumber:
  - customNumber를 사용하여 주문 테이블(별칭 o)을 고객 테이블과 조인합니다. 이를 통해 각 고객의 주문 내역을 알 수 있습니다.
- SELECT e.employeeNumber, e.firstName, o.orderNumber:
  - 마지막으로 직원 테이블(영업 담당자)에서 EmployeeNumber 및 firstName을 선택하고 주문한 각 고객에 대한 주문 테이블에서 orderNumber를 선택합니다.
buyPrice를 기준으로 제품 테이블에 있는 제품의 평균 가격을 계산하는 쿼리입니다.
제품 테이블에서 가장 비싼 상위 3개 제품을 가져오시겠습니까?
'배송됨' 상태인 모든 주문의 customerName, orderNumber 및 orderDate를 검색합니다.
각 productLine에 대해 판매된 총 제품 수 표시
직원번호 = 1143인 직원에게 직속으로 보고하는 직원을 찾습니다.
주문 테이블의 총 주문 수를 상태별로 그룹화하여 계산하는 쿼리입니다.
직원의 이름과 관리자 이름을 나열하세요.