DB学習3週目

3問(私が出した問題を除く)
テーブルはOTです.
[3週間の問題2]
各営業担当者(マネージャを除く)の受注数は100を超え、収益が発生した受注総数から最小値(小数点を除く)で最大値を取得します.
すべての従業員と顧客の携帯電話番号の最後の人は、以前に求めた価格の車の数字に少なくとも1人(1-5桁)の人が含まれていることを印刷してください.
--出力フィールド:ID、FIRST NAME、LAST NAME、PHONE、EMAIL
EX)65432の差があれば、携帯電話の最後の位置が6、5、4、3、2の人をプリントアウトすることができます.
72名CS/EM 54名
各営業担当者(マネージャを除く)の受注数は100を超え、수익を発行した受注の合計から最小価格で나머지값(小数点を除く)を取得します.

SELECT  FLOOR(MOD(MAX(A.RSUM),MIN(A.RSUM))) AS RESULT
FROM (SELECT O.SALESMAN_ID,SUM(OI.QUANTITY * OI.UNIT_PRICE) AS RSUM,
		RANK() OVER(ORDER BY SUM(OI.QUANTITY * OI.UNIT_PRICE) DESC) AS RNK
      FROM ORDERS O INNER JOIN ORDER_ITEMS OI
			    ON O.ORDER_ID = OI.ORDER_ID
			   AND O.STATUS = 'Shipped'
			   AND OI.QUANTITY >= 100
		    INNER JOIN EMPLOYEES E
			    ON O.SALESMAN_ID = E.EMPLOYEE_ID
      GROUP BY O.SALESMAN_ID)A

営業マン、受注ステータス、総収益値が必要で、ORDERSとORDER ITEMSテーブルをチェックインし、条件の下でステータス「Shipped」で、数量は100個以上です.
従業員ごとに収益を取る必要があるため、Group byにSALESMANを加入した.
収益順位は順位関数を利用した.そしてサブクエリ後にMAXとMINを用いて残り値を求める.
出力結果

すべての従業員と顧客の携帯電話番号の最後の人は、以前に求めた価格の車の数字に少なくとも1人(1-5桁)の人が含まれていることを印刷してください.
--出力フィールド:ID、FIRST NAME、LAST NAME、PHONE、EMAIL

SELECT *
FROM   (SELECT ('(EM)' ||FIRST_NAME || LAST_NAME),PHONE,SUBSTR(PHONE,-1,1) AS EN, EMAIL FROM EMPLOYEES
        UNION 
        SELECT ('(CS)' ||FIRST_NAME || LAST_NAME),PHONE,SUBSTR(PHONE,-1,1) AS EN, EMAIL FROM CONTACTS ) NA INNER  JOIN (SELECT  FLOOR(MOD(MAX(A.RSUM),MIN(A.RSUM))) AS RESULT
                                                                                                                        FROM (SELECT O.SALESMAN_ID,SUM(OI.QUANTITY * OI.UNIT_PRICE) AS RSUM,
                                                                                                                                RANK() OVER(ORDER BY SUM(OI.QUANTITY * OI.UNIT_PRICE) DESC) AS RNK
                                                                                                                              FROM ORDERS O INNER JOIN ORDER_ITEMS OI
                                                                                                                                                    ON O.ORDER_ID = OI.ORDER_ID
                                                                                                                                                   AND O.STATUS = 'Shipped'
                                                                                                                                                   AND OI.QUANTITY >= 100
                                                                                                                                            INNER JOIN EMPLOYEES E
                                                                                                                                                    ON O.SALESMAN_ID = E.EMPLOYEE_ID
                                                                                                                              GROUP BY O.SALESMAN_ID)A) R
                                                                                                                  ON 1=1
                                                                                                                AND  NA.EN IN( SUBSTR(R.RESULT,0,1),SUBSTR(R.RESULT,1,1),SUBSTR(R.RESULT,2,1))

                                                                                 
;

職員も顧客も出力するので、2つの表にユニオン関数で貼り付け、含まれる数字のためにSUBSTR(クリッピング関数)で解いた.
正解

[3週間の問題3]
3500～4500に限定された会社を探しています/サイト名の共通部分を除いて、会社名の文字長(スペースを除く)とサイト名の文字長を比較します.
会社名の文字長はサイト名の文字長より1.3倍大きく、1.5倍、1.7倍未満で、すべての会社の文字長に比べて6位と300位以下にランクされています
出力フィールド:名前、Webサイト名、クレジットポイント、ランキング
EX)https://abcdefg.com->abcdefg(共通部分を除く)
3500～4500に限定された会社を探しています/サイト名の公共部門を除いて、会社名の文字長(スペースを除く)とサイト名の文字長

SELECT C.NAME,C.WEBSITE,C.CREDIT_LIMIT,REPLACE(C.NAME,' ',''),LENGTH(REPLACE(C.NAME,' ','')) AS CNAME, 
         SUBSTR(SUBSTR(WEBSITE,INSTR(WEBSITE,'.',1)+1),0,INSTR(SUBSTR(WEBSITE,INSTR(WEBSITE,'.',1)+1),'.')-1) AS WNAME
FROM CUSTOMERS C 
WHERE C.CREDIT_LIMIT BETWEEN 3500 AND 4500

限度3500と4500はwhere条件下で処理され,ウェブサイトの共通部分を排除するためにsubst(クリップ関数),insr(位置戻り数)を用いた.
会社名の長さのためにスペースを置換して消去し、lengthで長さを求める
出力結果

会社名の文字長はサイト名の文字長より1.3倍大きく、1.5倍、1.7倍未満で、すべての会社の文字長に比べて6位と300位以下にランクされています

SELECT R3.*
FROM   (SELECT CU.NAME,CU.WEBSITE, CASE WHEN R2.RLIMIT IS NULL
                                            THEN CU.CREDIT_LIMIT
                                            ELSE R2.RLIMIT
                                            END AS LIMIT,
                  RANK() OVER (ORDER BY CASE WHEN R2.RLIMIT IS NULL
                                            THEN CU.CREDIT_LIMIT
                                            ELSE R2.RLIMIT
                                            END DESC) AS RNK
            FROM CUSTOMERS CU LEFT OUTER JOIN (SELECT R.*, CASE WHEN LENGTH(CNAME) > LENGTH(WNAME)
                                                           THEN R.CREDIT_LIMIT * 1.3
                                                           WHEN LENGTH(CNAME) = LENGTH(WNAME)
                                                           THEN R.CREDIT_LIMIT * 1.5
                                                           ELSE R.CREDIT_LIMIT * 1.7
                                                           END AS RLIMIT
                                          FROM (SELECT  NAME,WEBSITE,CREDIT_LIMIT,REPLACE(NAME,' ','') AS CNAME,
                                                    SUBSTR(SUBSTR(WEBSITE,INSTR(WEBSITE,'.',1)+1),0,INSTR(SUBSTR(WEBSITE,INSTR(WEBSITE,'.',1)+1),'.',1)-1) AS WNAME
                                                FROM CUSTOMERS 
                                                WHERE CREDIT_LIMIT BETWEEN 3500 AND 4500) R) R2
                                    ON CU.NAME = R2.NAME ) R3
WHERE R3.RNK  <=6 OR R3.RNK >= 300

case文を用いて限度額計算を行い,最後の条件はwhereで処理した.
正解

質問です.
17年下半期、顧客が注文した注文の総数(少数の第1位から四捨五入).
上位5位のお客様は信用限度額を30%引き上げ、他のお客様は30%引き下げ、信用限度額を基準にランキングを行います.
出力フィールド:CUSTOMER ID、NAME、SUM(注文金額合計)、CREDIT LIMIT、RLMIT(信用限度変動値)、RNK
17年下半期のお客様の注文金額の合計(少数の第1位から四捨五入)

SELECT CU.NAME,SUM(OI.QUANTITY*OI.UNIT_PRICE) ,ROUND(SUM(OI.QUANTITY*OI.UNIT_PRICE)) AS OSUM,
        RANK() OVER (ORDER BY ROUND(SUM(OI.UNIT_PRICE),1) DESC) AS RNK
FROM  CUSTOMERS CU INNER JOIN ORDERS O
                           ON CU.CUSTOMER_ID = O.CUSTOMER_ID
                   INNER JOIN ORDER_ITEMS OI
                           ON O.ORDER_ID = OI.ORDER_ID
                          AND O.STATUS NOT IN 'Canceled'
                          AND TO_CHAR(O.ORDER_DATE, 'YY') = 17
                          AND TO_CHAR(O.ORDER_DATE, 'MM') > 6
GROUP BY CU.NAME

17年と下半期にTo CHAR関数を使用したが、注文するだけなのでNot inにキャンセル.しかもお客様別に分類されているのでgroupbyCU.NAMEした
しゅつりょく

上位5位のお客様はクレジットを30%引き上げ、他のお客様はクレジットを30%引き下げます

SELECT CU2.CUSTOMER_ID,CU2.NAME,S.OSUM,CU2.CREDIT_LIMIT,S.RNK,
        CASE WHEN S.RNK < 6
             THEN CU2.CREDIT_LIMIT *1.3
             ELSE CU2.CREDIT_LIMIT *0.7
             END AS RLIMIT
FROM CUSTOMERS CU2 INNER JOIN (SELECT CU.NAME,SUM(OI.QUANTITY*OI.UNIT_PRICE) ,ROUND(SUM(OI.QUANTITY*OI.UNIT_PRICE)) AS OSUM,
                                        RANK() OVER (ORDER BY ROUND(SUM(OI.UNIT_PRICE),1) DESC) AS RNK
                               FROM  CUSTOMERS CU INNER JOIN ORDERS O
                                                           ON CU.CUSTOMER_ID = O.CUSTOMER_ID
                                                   INNER JOIN ORDER_ITEMS OI
                                                           ON O.ORDER_ID = OI.ORDER_ID
                                                          AND O.STATUS NOT IN 'Canceled'
                                                          AND TO_CHAR(O.ORDER_DATE, 'YY') = 17
                                                          AND TO_CHAR(O.ORDER_DATE, 'MM') > 6
                                GROUP BY CU.NAME) S
                                                           ON CU2.NAME = S.NAME

条件に基づいてcase文を用いて計算を行った.
しゅつりょく

信用限度額を基準にランキングを求めてください.

SELECT R.*,RANK() OVER(ORDER BY R.RLIMIT DESC ) AS RNKA
FROM (SELECT CU2.CUSTOMER_ID,CU2.NAME,S.OSUM,CU2.CREDIT_LIMIT,S.RNK,
        CASE WHEN S.RNK < 6
             THEN CU2.CREDIT_LIMIT *1.3
             ELSE CU2.CREDIT_LIMIT *0.7
             END AS RLIMIT
     FROM CUSTOMERS CU2 INNER JOIN (SELECT CU2.CUSTOMER_ID,CU2.NAME,S.OSUM,CU2.CREDIT_LIMIT,S.RNK,
        CASE WHEN S.RNK < 6
             THEN CU2.CREDIT_LIMIT *1.3
             ELSE CU2.CREDIT_LIMIT *0.7
             END AS RLIMIT
FROM CUSTOMERS CU2 INNER JOIN (SELECT CU.NAME,SUM(OI.QUANTITY*OI.UNIT_PRICE) ,ROUND(SUM(OI.QUANTITY*OI.UNIT_PRICE)) AS OSUM,
                                        RANK() OVER (ORDER BY ROUND(SUM(OI.UNIT_PRICE),1) DESC) AS RNK
                               FROM  CUSTOMERS CU INNER JOIN ORDERS O
                                                           ON CU.CUSTOMER_ID = O.CUSTOMER_ID
                                                   INNER JOIN ORDER_ITEMS OI
                                                           ON O.ORDER_ID = OI.ORDER_ID
                                                          AND O.STATUS NOT IN 'Canceled'
                                                          AND TO_CHAR(O.ORDER_DATE, 'YY') = 17
                                                          AND TO_CHAR(O.ORDER_DATE, 'MM') > 6
                                GROUP BY CU.NAME) S
                                                           ON CU2.NAME = S.NAME) S
                           ON CU2.NAME = S.NAME) R
;

正解

3週目も無事終了!!
今週は前よりいろいろな関数で解いた問題が多かったので楽しかったです.