1. SQL


例+問題を解くことで,sqlがどのような感じなのかを見つけようとする.
例を解く前に、複数のテーブルの関係を視覚的に整理してみると役に立つかもしれませんが、想像以上に時間がかかり、良い方法を探すべきだと思います.
実际には教育用pptにも関系図がありますが、いつもこんなものがあるとは保证できませんので、自分でやりましょう…!
+整理の過程がどうであれ、まず整理しておくと、確かに問題を解くときに便利です

  • の合計顧客数は何人ですか?122
  • select count(distinct customerNumber) from customers;
  • lastnameがBrownの顧客数は?3
  • select count(contactLastName) from customers 
    where contactLastName = 'Brown';
  • 携帯電話番号4で始まるお客様の数は?5
  • select count(phone) from customers where phone like '4%';
    #count(customerNumber)을 쓰는 게 더 적절해보임
  • お客様が住んでいる国のリストは?
  • select distinct country from customers;
  • 販売担当者がそれぞれ担当する顧客数は?
  • select salesRepEmployeeNumber as 직원,
    count(customerNumber) as 담당고객수 from customers
    where salesRepEmployeeNumber is not null
    group by salesRepEmployeeNumber;
  • 7名以上の販売者リスト
  • select salesRepEmployeeNumber,count(customerNumber) from customers
    where salesRepEmployeeNumber is not null
    group by salesRepEmployeeNumber
    having count(customerNumber) >= 7;
  • 毎月の顧客数はいくらですか?
  • select extract(year from orderDate), extract(month from orderDate), count(customerNumber) from orders
    group by 1, 2;
    #근데 같은 고객이 한 달에 여러 번 구매를 할 수도 있지 않나??
    그냥 월별 구매 수를 세는 건가?
    만약 구매를 한 실제 고객수를 세는 거면 count(distinct customerNumber)라고 해야 함
  • 毎月の注文を担当するoffice数量
  • select extract(year from orderDate), extract(month from orderDate), count(officeCode) from orders
    join customers on customers.customerNumber = orders.customerNumber
    join employees on employees.employeeNumber = customers.salesRepEmployeeNumber
    group by 1,2;
    #left join과 결과는 같음.. 그 차이는??
  • 22003年度オーダーの月次オーダー数量と平均オーダー日から出荷日(オーダーから出荷まで)
  • select extract(year from orderDate), extract(month from orderDate), 
    count(orderNumber), avg(datediff(shippedDate,orderDate)) from orders
    where extract(year from orderDate) = 2003
    group by 1,2;
  • 国/地域のお客様の注文数と平均注文日から出荷日
  • まで
    select country, count(orderNumber), avg(datediff(shippedDate,orderDate))
    from orders
    left join customers on orders.customerNumber = customers.customerNumber
    group by country;
  • 米国年、月次受注数量および平均受注日から出荷日までの
  • select extract(year from orderDate), extract(month from orderDate),
    count(orderNumber), avg(datediff(shippedDate, orderDate))
    from orders
    group by 1,2;
  • 月販売
  • select extract(year from orderDate), extract(month from orderDate), sum(amount)
    from orders
    left join payments on orders.customerNumber = payments.customerNumber
    group by 1,2;
    
    #사실 payments 테이블의 paymentDate를 써도 된다....
    
    #만약 같은 주문 건에 대해서
    #orderDate와 paymentDate가 다르면??
    #매출이 회계장부상 기록되는 revenue를 말하는 것일 듯...
    #그럼 paymentDate가 아니라 orderDate를 쓰는게 맞지 않나??
    
    select orders.customerNumber, orderNumber, orderDate, paymentDate from orders
    left join payments on orders.customerNumber = payments.customerNumber
    where orderDate != paymentDate;
    
    #이걸로 확인해 보면, 같은 customer의 동일한 order여도 여러 번 나눠서 payment를 실행한 경우가 있다. 
    #따라서, orderDate를 월 매출 산정의 기준으로 쓰는 것이 더 타당해 보임.
    #근데 이건 내 뇌피셜이라는 점... 
    
    #그런데 저 변수들에 대해 명확한 설명이 공식 제공되지 않아서 해석하기 나름인 듯!! 이렇게 해도 맞고 paymentDate기준으로 봐도 맞다 !!!! !!
    
    #근데.... 분할결제를 1년 단위로도 하나.......?
    다시 생각해보니 paymentDate가 좀더 적절해 보임
  • アメリカ月収
  • select extract(year from paymentDate), 
    extract(month from paymentDate), 
    sum(amount) from payments
    left join customers on payments.customerNumber = customers.customerNumber
    where country = 'USA'
    group by 1,2
    order by 1,2;
    #group by를 숫자로 할 수 있는게 정말 편한 것 같다
  • 国/地域月収
  • select country, extract(year from paymentDate),
    extract(month from paymentDate),
    sum(amount) from payments
    left join customers 
    on payments.customerNumber = customers.customerNumber
    group by 1,2,3
    order by 1,2,3;
  • 累計支払金額が100000を超える顧客情報(氏名、国/地域、電話)*
  • select customerName, country, phone from customers
    where customerNumber in
    (select customerNumber from payments
    group by 1 
    having sum(amount>=100000));
    #이건 서브쿼리문에 sum(amount)를 추가하는 실수를 했는데,
    서브쿼리 안에 컬럼을 두 개 넣는다는건
    '이 순서쌍을 찾는다'는 의미인 듯
  • 毎月の注文数量と販売
  • select extract(year from orderDate),
    extract(month from orderDate),
    count(extract(month from orderDate)),
    sum(amount)
    from orders
    left join payments
    on orders.customerNumber = payments.customerNumber
    group by 1,2;
    라고 했는데 예제 답과 너무 다름.. 어디가 잘못된 거지??
    이건 나중에..

    課題

  • 国/地域オフィスおよび従業員数
  • #처음 짠 코드
    select country, count(offices.officeCode), count(employeeNumber) from offices
    left join employees 
    on offices.officeCode = employees.officeCode
    group by 1;
    
    #결과가 이상해서 국가, officeCode, 직원번호 조회해보니 역시나 이상했음
    select country, employeeNumber, offices.officeCode from offices left join employees
    on offices.officeCode = employees.officeCode;
    #수정: officeCode 중복 고려해서 distinct 넣어줌
    select country, count(distinct offices.officeCode), count(employeeNumber) from offices
    left join employees 
    on offices.officeCode = employees.officeCode
    group by 1
    order by 1;
    
  • CustomerFirstName Rで始まる顧客リスト
  • #걍 공부용으로 customerNumber까지 포함되게 출력되도록 했다.. 그런데 자꾸 null이 살아있어서 난감했다. 
    where절에서 and조건을 사용해 not null인 값만 표시되도록 했는데도 왜 null이 안 없어지지???????? 노 이해..
    
    SELECT customerNumber in (select customerNumber from customers 
    where customerNumber is not Null), customerName, contactFirstName 
    FROM customers
    where contactFirstName like 'R%';
    
    이렇게 하니까 내가 원하는 customerNumber 정보가 표시가 안 됐다
    
    SELECT customerNumber, customerName, contactFirstName 
    FROM (select * from customers
    where customerNumber is not null) customers
    where contactFirstName like 'R%'
    order by 1;
    
    드디어 성공 ㅠ 근데 해놓고 나니 정말 별 거 아니었다 ㅋㅋ
    정말 이해가 안 되는게 왜 is not null조건은
    where절 안에 다중조건으로 들어가질 않는 거지??
    
  • 受注ステータスが「Canceled」または「OnHold」の米国顧客の受注数
  • On Hold인 것만 살펴봤다
    select * from orders
    where status = 'On Hold';
    
    근데 여기도 Null 있다... 범인은
    이번에도 식별코드(Number) 컬럼이었다... 킹받악
    
    select orders.customerNumber, orderNumber, status, country from orders
    left join customers
    on orders.customerNumber = customers.customerNumber
    where country = 'USA'
    and status in ('Cancelled', 'On Hold');
    왜 이번에는 null이 안 뜨지? 하여튼 조건에 맞는 고객 리스트를 뽑긴 함
    이제 주문 건수를 세는 코드로 바꿔 보자
    
    select count(orders.customerNumber) from orders
    left join customers
    on orders.customerNumber = customers.customerNumber
    where country = 'USA'
    and status in ('Cancelled', 'On Hold');
    
  • 最大顧客のオフィスコード
  • を担当
    select officeCode, count(distinct customerNumber) from employees
    left join customers
    on employees.employeeNumber = customers.salesRepEmployeeNumber
    group by 1
    order by 2 DESC;
    
    select officeCode, count(distinct customerNumber) from customers
    left join employees
    on employees.employeeNumber = customers.salesRepEmployeeNumber
    group by 1
    order by 2 DESC;
    null값 빼면 결과는 어쨌든 같긴 같다... 뭔 차이지??
    
    
    select officeCode, customerNumber from customers
    left join employees
    on employees.employeeNumber = customers.salesRepEmployeeNumber;
    
    select officeCode, customerNumber from employees
    left join customers
    on employees.employeeNumber = customers.salesRepEmployeeNumber;
    이렇게 하면 officeCode가 null인건 아예 안 뜸
    
    select officeCode from employees
    where officeCode is null;
    
    이걸 실행시켜 보면 조회되는 값이 나오지 않는데,
    도대체 아까는 왜 officeCode가 null로 나왔지??????
    
    select salesRepEmployeeNumber, employeeNumber from customers
    left join employees
    on employees.employeeNumber = customers.salesRepEmployeeNumber;
    이유를 찾았다!
    고객의 담당직원이 null값일 줄은 몰랐지;;
    허무한 결말.. table을 제대로 살펴보지 않고 검색만 대충 하니까
    이렇게 먼 길을 돌게 되는 것 같다
  • 2004年11月の支払い金額が最大の顧客情報(name、国/地域、電話?)
  • 이번에는 전체 테이블부터 살펴보고 시작..
    
    select payments.customerNumber, customerName, country, phone, paymentDate, amount from payments
    left join customers
    on payments.customerNumber = customers.customerNumber;
    
    null값은 없다
    
    
    select customerName, country, phone from customers
    left join payments
    on payments.customerNumber = customers.customerNumber
    where amount in
    (select max(결제금액) 
    from (select sum(amount) as 결제금액 from payments
    where extract(year from paymentDate)=2004
    and extract(month from paymentDate) = 11
    group by customerNumber) payment);
    
    이게 ㄹㅇ 힘들었던게,
    그냥 결제금액 순서로 쭉 나열해서 사람 눈으로 제일 첫번째 자료를 찾는 방식이 아니라
    제일 많이 결제한 고객만 조회하려고 하니까
    서브쿼리가 들어가야 해서 굉장히 헷갈렸다,,,,
    라고 생각했는데 sql에도 그런 기능이 있었다
    아무래도 계속 앉아서 달렸더니 뇌가 굳은 듯??
    그냥 from절 다음에 limit N 해주면 됐다 ㅎㅎ;;;;;;
    내 40분 ㅠ
    
    검토:
    select payments.customerNumber, customerName, country, phone, 
    paymentDate, sum(amount) from payments
    left join customers
    on payments.customerNumber = customers.customerNumber
    where extract(year from paymentDate)=2004
    and extract(month from paymentDate) = 11
    group by customerNumber
    order by 6 DESC;
    
    여기에 맨 끝에 LIMIT 1 붙여주니까 
    앞에서 힘들게 짠 쿼리와 같은 결과가 나온다 ㅎㅎㅎㅎㅎㅎㅎ....
  • 72005年1月OrderDateとShippeddDateの間の最大値と最小値
  • select max(배송기간), min(배송기간) from 
    (select dateDiff(shippedDate,orderDate) as 배송기간 from orders
    where extract(year from orderDate) = 2005
    and extract(month from orderDate) = 1) orders;
    
    서브쿼리로 찾는 것도 힘들게 해봤으니까 괜히 여기도 적용해 보기,,,ㅎㅎㅎ
    아 그런데 이 문제는 limit로는 풀기 힘들 듯?? 헛고생한건 아닌가
    
  • 2004年1年間の支払い金額が最大の顧客従業員情報
  • select * from (select * from employees where employeeNumber is not null) employees
    where employeeNumber = (select salesRepEmployeeNumber
     from (select payments.customerNumber, sum(amount), 
    salesRepEmployeeNumber
    from payments
    left join customers
    on payments.customerNumber = customers.customerNumber
    where extract(year from paymentDate) = 2004
    group by 1
    order by 2 DESC
    limit 1) employees);
    
    어려웠다 ㅠㅠ 우선은
    제일 많이 결제한 고객을 골라내는 데 그치지 않고
    그 고객의 담당 직원 정보를 얻어내야 하는 게 낯설었다
    거의 이것 때문에 제일 오래 걸린듯
    그리고 그 다음 단계에서 서브쿼리문으로 필터링 하려고 했는데
    한 컬럼만 별칭 지정하니까 에러 나더라?? 거참.. 공평해야 한다 이건가
    그래서 아싸리 다 지정 안하고도 해보고 지정하고도 해봤는데 둘 다 정상작동
    마지막으로 null값 지워주기,, 이거 정말 귀찮았다
    
    
    select employeeNumber, lastName, firstName,
    extension, email, officeCode, reportsTo, jobtitle, customers.customerNumber,
    paymentDate, sum(amount) from employees
    left join customers
    on employees.employeeNumber = customers.salesRepEmployeeNumber
    left join payments
    on customers.customerNumber = payments.customerNumber
    group by 9
    order by 11 desc
    limit 1;
    이렇게도 할 수 있었다!
    일단 필요한 정보를 담은 컬럼들을 싹 join 해준 다음에
    그 테이블에서 정렬을 통해 원하는 값을 얻어내는 방식인데
    위의 방식은 깔끔하게 직원정보만 나오는 반면
    이렇게 하면 고객 정보까지 다 나와서 조금 지저분스
    그렇지만 코드 작성할 때 쉽게 접근할 수 있어서 훨씬 편리했다
    
    最後に言いたいことがあったら、
    なぜfromの後ろにサブクエリを書くときに一般的なテーブル名を付けなければならないのか理解できません.
    fromセクションにサブクエリがある場合は、インラインビューと呼ばれます.
    必ず別名を付けます
    たとえば
    from(サブクエリ)as~~
    しかし、私のコードでもグーグルの結果でもasは頻繁に省略されています.
    上記のコードにasを追加して結果を再実行
    回転がよかった...
    道理でコラムに别名を贴る时、いつも别名の间违いが现れます...
    繰り返してないのに…?そう思って別名を外しました.
    やっぱりまた探してみればよかった.
    その他の参考資料
    [リンクテキスト](ソース:https://inpa.tistory.com/entry/MYSQL-📚-サブクエリ-クリア[👨‍💻 Dev Scroll])