Upgrade to Pro — share decks privately, control downloads, hide ads and more …

データ分析のためのSQL勉強会 〜初級編 ハンズオン〜

hikarut
December 29, 2021

データ分析のためのSQL勉強会 〜初級編 ハンズオン〜

hikarut

December 29, 2021
Tweet

More Decks by hikarut

Other Decks in Technology

Transcript

  1.  ᶅ42-ษڧձͷਐΊํ w ϋϯζΦϯܗࣜͰ࣮ࢪ w ͨͩ࿩Λฉ͚ͩ͘Ͱ͸ͳ͘ඞͣࣗ෼Ͱ42-Λॻ͘ࣄ w ͦͷͨΊʹࣗ෼ͷ1$Ͱ42-Λ࣮ߦͰ͖Δ؀ڥΛ༻ҙ͢Δ w ࢿྉͷதͰʮ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ʯͱॻ͔Ε͍ͯΔ෦෼͸ࣗ෼

    Ͱ42-Λ࣮ߦͯ݁͠Ռ͕Ͳ͏ͳΔ͔֬ೝ͠·͠ΐ͏ w ·ͣ͸جຊతͳ42-ͷઆ໌͠ɺͦͷޙࣗ෼Ͱ42-Λॻ͍͍ͯ͘ελΠϧ w ໰୊ͷղ౴ྫ΋Ұॹʹࡌ͍ͤͯ·͕͢ɺղ౴ΛΈΔલʹ·ͣ͸ࣗ෼Ͱߟ͑ ͯΈ·͠ΐ͏
  2. ͳͥ42-ΛֶͿͷ͔ʁ  w σʔλ׆༻͕Ϗδωε੒௕ʹ͓͍ͯඞਢͰ͋Δ w 42-͕࢖͑ΔσʔλΛࣗ༝ʹѻ͑Δ w σʔλ෼ੳʹ͓͍ͯ42-ͷ׆༻ͷػձ͕૿͍͑ͯΔ w #JH2VFSZ

    5SFBTVSF%BUB SFEBTI 3FETIJGUͳͲ w ΤΫηϧͰѻ͑Δσʔλʹݶք͕͋Δ w ΤΫηϧͷݶք͸ສߦ͘Β͍ ΤΫηϧͰ෼ੳ͢Δͱॏ͍ 42-͸σʔλ෼ੳͷ൚༻తͳεΩϧͰ͋Γɺ 42-͕࢖͑ΔͱେྔͷσʔλΛߴ଎ʹ෼ੳͰ͖Δ
  3.  wσʔλϕʔεͱ͸σʔλΛ֨ೲ͢Δେ͖ͳശ wςʔϒϧͱ͸σʔλϕʔε্ʹଘࡏ͢Δσʔλͷ͔ͨ·Γ wσʔλϕʔεʹ͋Δςʔϒϧ͔Β৘ใΛऔಘ͢Δํ๏͕42- Ϣʔβʔ αʔόʔ σʔλϕʔε %# 42- VTFS@JE

    TFY CJSUI " உੑ  " ঁੑ  " உੑ  ɾɾɾ ɾɾɾ ɾɾɾ ςʔϒϧ QSPEVDU@JE QSPEVDU@OBNF QSJDF # ͓͍͍͠ਫ  # γϟʔϓϖϯ  # ࿹࣌ܭ  ɾɾɾ ɾɾɾ ɾɾɾ 8&#αΠτ 42-ͱ͸ʁ
  4.  ࠓճͷߨٛͰ͸Սۭͷ&$αΠτͷߪങσʔλΛࢀߟʹσʔλநग़Λߦ͍·͢ ΧϥϜ໊ ҙຯ ܕ උߟ VTFS@JE Ϣʔβʔ*% 5&95 TFY

    ੑผ 5&95 ঁੑஉੑ CJSUI ஀ੜ೔ */5&(&3 ஀ੜ೥͕਺ࣈͰೖΓ·͢ JT@EFMFUFE ࡟আϑϥά */5&(&3 ɿ௨ৗϢʔβʔ ɿ࡟আϢʔβʔ ΧϥϜ໊ ҙຯ ܕ උߟ QSPEVDU@JE ঎඼*% 5&95 OBNF ঎඼໊ 5&95 QSJDF ֹۚ */5&(&3 DBUFHPSZ ΧςΰϦ 5&95 DBUFHPSZ ΧςΰϦ 5&95 ΧϥϜ໊ ҙຯ ܕ උߟ PSEFS@JE ஫จ*% 5&95 VTFS@JE Ϣʔβʔ*% 5&95 PSEFS@QSPEVDU@JE ঎඼*% 5&95 PSEFS@EBUF ஫จ೔࣌ */5&(&3 JT@EJTDPVOUFE ׂҾϑϥά */5&(&3 ɿׂҾͳ͠ ɿׂҾ͋Γ JT@DBODFMFE Ωϟϯηϧϑϥά */5&(&3 ɿΩϟϯηϧͳ͠ ɿΩϟϯηϧ͋Γ VTFSTςʔϒϧ QSPEVDUTςʔϒϧ PSEFS@EBUBςʔϒϧ
  5.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  6.  w ͲͷߦͷσʔλΛऔΔͷ͔ w ͲͷྻͷσʔλΛऔΔͷ͔ w Ͳ͏σʔλΛ·ͱΊΔͷ͔ w Ͳ͏σʔλΛ૊Έ߹ΘͤΔͷ͔ طଘͷςʔϒϧ

    42-ͷجຊతͳߟ͑ํ ৽͍͠ςʔϒϧ OBNF DPVOU QSJDF ͓͍͍͠ਫ    γϟʔϓϖϯ    ɾɾɾ ɾɾɾ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE $ " # $ " # ɾɾɾ ɾɾɾ ɾɾɾ QSPEVDU@JE OBNF QSJDF DBUFHPSZ # ͓͍͍͠ਫ  ৯඼ # γϟʔϓϖϯ  จ๪۩ ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ ߦ ྻ ͔Β Λ࡞੒͢Δ࡞ۀ Λߟ͑ͯ
  7.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  8.  جຊతͳ42-ߏจ SELECT ΧϥϜ໊ FROM ςʔϒϧ໊ WHERE ৚݅ GROUP BY

    άϧʔϓ৚݅ΧϥϜ ORDER BY ฒͼॱࢦఆΧϥϜ جຊߏจ SELECT category1, COUNT(*) FROM products WHERE category1 = '৯඼' GROUP BY category1 ORDER BY category1 ۩ମྫ
  9.  جຊతͳ42-ߏจ جຊߏจ SELECT category1, COUNT(*) FROM products WHERE category1

    = '৯඼' GROUP BY category1 ORDER BY category1 ˞ࡉ͔͍෦෼͸ޙ΄Ͳৄࡉʹઆ໌͢ΔͷͰ·ͣ͸4&-&$5 ෦෼ͷΈ஫໨ 4&-&$5 '30. (3061#: 03%&3#: 8)&3& ग़ྗ͢ΔσʔλΛࢦఆ Ͳͷςʔϒϧ͔ΒσʔλΛऔ ಘ͢Δ͔ σʔλΛநग़͢Δ৚݅ ूܭͷࡍͷάϧʔϓ৚݅ ग़ྗ݁Ռͷฒͼॱ
  10.  4&-&$5จɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ SELECT product_id, name FROM products ςʔϒϧʹͲ͏Ώ͏σʔλ͕ೖ͍ͬͯΔ͔֬ೝͯ͠ɺཉ͍͠σʔλ ΧϥϜ Λࢦఆͯ͠42-Λ࣮ߦ

    ΧϥϜ໊ ҙຯ ܕ උߟ QSPEVDU@JE ঎඼*% 5&95 OBNF ঎඼໊ 5&95 QSJDF ֹۚ */5&(&3 DBUFHPSZ ΧςΰϦ 5&95 DBUFHPSZ ΧςΰϦ 5&95 DBUFHPSZ ΧςΰϦ 5&95 QSPEVDUTςʔϒϧ
  11.  42-JUFͷσʔλͷܕ 42-JUFͰ͸ͭͷܕ͕ଘࡏ͠·͢ ΧϥϜ໊ ҙຯ ܕ උߟ QSPEVDU@JE ঎඼*% 5&95

    OBNF ঎඼໊ 5&95 QSJDF ֹۚ */5&(&3 DBUFHPSZ ΧςΰϦ 5&95 DBUFHPSZ ΧςΰϦ 5&95 DBUFHPSZ ΧςΰϦ 5&95 QSPEVDUTςʔϒϧ /6-- */5&(&3 5&95 #-0# 3&"- /6--஋ ੔਺ খ਺఺ '-0"5 %06#-& จࣈྻ $)"3 7"3$)"3 #JOBSZ-BSHF 0CKFDU ˞ͦͷଞͷ%#Ͱ͸ʮ%"5&ʯ΍ʮ5*.&45".1ʯͳ Ͳ೔෇Λ֨ೲ͢Δܕ͕࢖ΘΕΔͷ͕ҰൠతͰ͕͢ɺ 42-JUFʹ͸ଘࡏ͠·ͤΜ #-0#ͱ͸จࣈྻ΍਺ࣈͰ͸ͳ͍೚ҙͷσʔλ ΛอଘͰ͖Δσʔλܕ ը૾ɺಈըɺ࣮ߦϑΝ ΠϧͳͲ
  12.  4&-&$5จɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ SELECT * FROM products w શͯͷσʔλΛநग़͢Δ৔߹͸ʮ ʯ ΞελϦεΫ

    Λ࢖͍·͢ w ͲΜͳσʔλ͕ೖͬͯΔ͔֬ೝ͢Δ࣌ʹศར w ग़ྗ͢Δ߲໨͕ଟ͘ͳΔ෼42-͕ॏ͘ͳΓෛՙ͕͔͔Γ΍͍͢ͷͰ஫ҙ w ʮ ʯΛ࢖ͬͯσʔλΛ֬ೝ͢Δ৔߹͸-*.*5ͱҰॹʹ࢖͏ͷ͕͓קΊ ޙड़ ΧϥϜ໊ ҙຯ ܕ උߟ QSPEVDU@JE ঎඼*% 5&95 OBNF ঎඼໊ 5&95 QSJDF ֹۚ */5&(&3 DBUFHPSZ ΧςΰϦ 5&95 DBUFHPSZ ΧςΰϦ 5&95 DBUFHPSZ ΧςΰϦ 5&95
  13.  SELECT product_id AS '঎඼ID', name AS '঎඼໊' FROM products

    w "4ͷޙʹผ໊͕෇͚ΒΕΔ w ೔ຊޠͰ΋ྑ͍͠ΞϧϑΝϕοτͰ΋Α͍ w σʔλͷղऍ͕͠΍͍͢Α͏ʹ42-࣮ߦऀ͕޷͖ʹ໊લΛ͚ͭΒΕΔ ʮ"4ʯΛ࢖͏ࣄͰ4&-&$5݁Ռʹผ໊Λ͚ͭΔࣄ͕Ͱ͖Δ SELECT product_id AS id, name AS na FROM products 4&-&$5จͷ"4ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅɹղઆ
  14.  4&-&$5จɹ໰୊ᶃ ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ ͔Βnameͱcategory1ͷσʔλΛऔಘ ͍ͯͩ͘͠͞ɻ·ͨname͸ʮ঎඼໊ʯɺcategory1͸ʮΧςΰϦ1ʯͱผ໊ Λ͚ͭͯग़ྗ͍ͯͩ͘͠͞ɻ ΧϥϜ໊ ҙຯ ܕ

    උߟ QSPEVDU@JE ঎඼*% 5&95 OBNF ঎඼໊ 5&95 QSJDF ֹۚ */5&(&3 DBUFHPSZ ΧςΰϦ 5&95 DBUFHPSZ ΧςΰϦ 5&95 DBUFHPSZ ΧςΰϦ 5&95 QSPEVDUTςʔϒϧ
  15.  4&-&$5จͷΠϝʔδ QSPEVDU@JE OBNF QSJDF DBUFHPSZ # ͓͍͍͠ਫ  ৯඼

    # γϟʔϓϖϯ  จ๪۩ # ࿹࣌ܭ  ϑΝογϣϯ ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ 4&-&$5จ͸ςʔϒϧ͔ΒྻΛࢦఆͯ͠σʔλΛऔಘ͢ΔΠϝʔδ QSPEVDU@JE OBNF # ͓͍͍͠ਫ # γϟʔϓϖϯ # ࿹࣌ܭ ɾɾɾ ɾɾɾ
  16.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  17.  ू߹ؔ਺ɾूܭؔ਺ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ SELECT COUNT(order_id) FROM order_data w ʮ$06/5ʯ͸Ϩίʔυ਺ σʔλͷ૯݅਺σʔλͷߦ਺ ΛΧ΢ϯτ͢Δ

    w ͷதʹ͸ Ҿ਺ʹ͸ ूܭ͍ͨ͠ΧϥϜ ྻ Λࢦఆ͠·͢ ΧϥϜ໊ ҙຯ ܕ උߟ PSEFS@JE ஫จ*% 5&95 VTFS@JE Ϣʔβʔ*% 5&95 PSEFS@QSPEVDU@JE ঎඼*% 5&95 PSEFS@EBUF ஫จ೔࣌ */5&(&3 JT@EJTDPVOUFE ׂҾϑϥά */5&(&3 ɿׂҾͳ͠ ɿׂҾ͋Γ JT@DBODFMFE Ωϟϯηϧϑϥά */5&(&3 ɿΩϟϯηϧͳ͠ ɿΩϟϯηϧ͋Γ PSEFS@EBUBςʔϒϧ
  18.  ू߹ؔ਺ɾूܭؔ਺ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ SELECT COUNT(order_id) AS count_1, COUNT(*) AS count_2, COUNT(1)

    AS count_3 FROM order_data ߦ਺ΛΧ΢ϯτ͍ͨ͠৔߹͸ͲͷྻΛࢦఆ ͯ͠΋ಉ͡ͳͷͰʮ ʯ΍ʮʯͱೖΕΔ৔ ߹͕ଟ͍Ͱ͢ "4Λ͚ͭͯू߹ؔ਺ͷ݁Ռʹ໊લΛ͚ͭΔ ͜ͱ΋Ͱ͖·͢ɻ ू߹ؔ਺͚ͩ࢖͏ͱ݁Ռͷղऍ͕ͮ͠Β͍ ͷͰɺ"4Λ࢖໊ͬͯલΛ͚ͭΔࣄ͕ଟ͍Ͱ ͢
  19.  ू߹ؔ਺ɾूܭؔ਺ɹ໰୊ᶃ usersςʔϒϧ͔Β஀ੜ೔ͷ࠷େ஋ɺ࠷খ஋ɺฏۉ஋Λग़ྗ͍ͯͩ͘͠͞ ΧϥϜ໊ ҙຯ ܕ උߟ VTFS@JE Ϣʔβʔ*% 5&95

    TFY ੑผ 5&95 ঁੑஉੑ CJSUI ஀ੜ೔ */5&(&3 ஀ੜ೥͕਺ࣈͰೖΓ·͢ JT@EFMFUFE ࡟আϑϥά */5&(&3 ɿ௨ৗϢʔβʔ ɿ࡟আϢʔβʔ VTFSTςʔϒϧ
  20.  ू߹ؔ਺ɾूܭؔ਺ɹղ౴ྫᶃɹղઆ usersςʔϒϧ͔Β஀ੜ೔ͷ࠷େ஋ɺ࠷খ஋ɺฏۉ஋Λग़ྗ͍ͯͩ͘͠͞ SELECT MAX(birth) AS max_birth, MIN(birth) AS min_birth,

    AVG(birth) AS avg_birth FROM users w ࠷େ஋͸."9ɺ࠷খ஋͸.*/ɺฏ ۉ஋͸"7(Λ࢖ͬͯग़ྗ w "4Λ࢖ͬͯผ໊Ͱग़ྗ ೚ҙ
  21.  ू߹ؔ਺ɾूܭؔ਺ɹิ଍ ू߹ؔ਺ͷ݁Ռ΍೔෇΍਺ࣈ͸଍͠ࢉҾ͖ࢉͳͲͷܭࢉ͕Ͱ͖Δ SELECT MAX(birth) AS max_birth, MIN(birth) AS min_birth,

    MAX(birth) - MIN(birth) AS diff FROM users ஀ੜ೔ͷ࠷େ஋ͱ࠷খ஋ΛҾ͖ࢉ͕ Ͱ͖Δ ˠ࠷େͷ೥ྸ͕ࠩΘ͔Δ
  22.  ू߹ؔ਺ɾूܭؔ਺ͷΠϝʔδ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF JT@EJTDPVOUFE JT@DBODFMFE $ "

    #    $ " #    $ " #    ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ ू߹ؔ਺͸ςʔϒϧ͔ΒߦΛ·ͱΊͯͭͷϨίʔυʹ͢ΔΠϝʔδ DPVOU@ DPVOU@ DPVOU@   
  23.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  24.  (3061#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ SELECT COUNT(*) FROM products SELECT category1, COUNT(*) FROM

    products GROUP BY category1 ςʔϒϧશମͷ݅਺Λग़ྗ͢Δ৔߹ ΧςΰϦ͝ͱͷ݅਺Λग़ྗ͢Δ৔߹
  25.  (3061#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ SELECT COUNT(*) FROM products SELECT category1, COUNT(*) FROM

    products GROUP BY category1 ςʔϒϧશମͷ݅਺Λग़ྗ͢Δ৔߹ ΧςΰϦ͝ͱͷ݅਺Λग़ྗ͢Δ৔߹ (3061#:Ͱࢦఆͨ͠ΧϥϜΛ4&-&$5۟Ͱ΋ࢦఆ͢Δ ˞4&-&$5۟Ͱࢦఆ͠ͳͯ͘΋ߏจͱͯ͠͸੒Γཱ͕ͭղऍ͕͠ʹ͘͘ͳΔ
  26.  (3061#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ SELECT category1, category2, COUNT(*) FROM products GROUP BY

    category1, category2 (3061#:͸ΧϯϚ۠੾ΓͰෳ਺ͷΧ ϥϜΛࢦఆ͢Δ͜ͱ΋Ͱ͖·͢ DBUFHPSZͱDBUFHPSZͰ·ͱΊͨͱ͖ ͷ݅਺Λग़ྗ͢Δ
  27.  (3061#:ɹ໰୊ᶃ ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ ͔ΒΧςΰϦ1͝ͱͷฏۉՁ֨Λऔಘ͠ ͍ͯͩ͘͞ ΧϥϜ໊ ҙຯ ܕ උߟ

    QSPEVDU@JE ঎඼*% 5&95 OBNF ঎඼໊ 5&95 QSJDF ֹۚ */5&(&3 DBUFHPSZ ΧςΰϦ 5&95 DBUFHPSZ ΧςΰϦ 5&95 DBUFHPSZ ΧςΰϦ 5&95 QSPEVDUTςʔϒϧ
  28.  (3061#:ɹղ౴ྫᶃ ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ ͔ΒΧςΰϦ1͝ͱͷฏۉՁ֨Λऔಘ͠ ͍ͯͩ͘͞ SELECT category1, AVG(price) FROM

    products GROUP BY category1 SELECT category1, AVG(price), COUNT(*) FROM products GROUP BY category1
  29.  (3061#:ɹղ౴ྫᶃɹղઆ ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ ͔ΒΧςΰϦ1͝ͱͷฏۉՁ֨Λऔಘ͠ ͍ͯͩ͘͞ SELECT category1, AVG(price) FROM

    products GROUP BY category1 SELECT category1, AVG(price), COUNT(*) FROM products GROUP BY category1 w ฏۉʹ͸"7(Λ࢖͍·͢ w ू߹ؔ਺͸(3061#:ͱ߹ΘͤͯͭҎ্ग़ྗ͢Δࣄ΋Ͱ͖·͢
  30.  (3061#:ͷΠϝʔδ (3061#:͸ࢦఆͨ͠ྻ͝ͱʹςʔϒϧ͔Βෳ਺͋ΔߦΛ·ͱΊͯ ͭͷϨίʔυʹ͢ΔΠϝʔδ DBUFHPSZ DPVOU@ ϑΝογϣϯ  ৯඼ 

    ೔༻඼  QSPEVDU@JE OBNF QSJDF DBUFHPSZ DBUFHPSZ # ͓͍͍͠ਫ  ৯඼ ໺ࡊ # γϟʔϓϖϯ  ೔༻඼ จ๪۩ # ࿹࣌ܭ  ϑΝογϣϯ ΞΫηαϦʔ # τʔτόοΫ  ϑΝογϣϯ Χόϯ ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ
  31.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  32.  %*45*/$5ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ SELECT category1 FROM products SELECT DISTINCT category1 FROM

    products ΧςΰϦΛग़ྗ ॏෳഉআͯ͠ΧςΰϦΛग़ྗ 4&-&$5จͷதͰॏෳഉআ͍߲ͨ͠໨ͷલʹ%*45*/$5Λهड़͢Δ
  33.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  34.  8)&3& wಛఆͷ৚݅Λ͚ͭͯσʔλΛग़ྗ͍ͨ࣌͠ʹ࢖͏ߏจ  ౳͍͠  େ͖͍  খ͍͞ 

    Ҏ্  ҎԼ  ౳͘͠ͳ͍ "/% ͭҎ্ͷ৚݅Λ݁߹ͯ͠ɺ྆ํͷ৚͕݅Ұக͢Δ 03 ͭҎ্ͷ৚݅Λ݁߹ͯ͠ɺ͍ͣΕ͔ͷ৚݅ʹҰக͢Δ /05 ৚݅ͷ൓ର #&58&&/""/%# "ͱ#ͷؒʹ֘౰͢Δ */ ର৅ͷΧϥϜ͕Ұཡͷ͍ͣΕ͔ʹҰக͢Δ -*,& ର৅ͷΧϥϜ͕৚݅ͷจࣈྻʹϚον͢Δ
  35.  8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ SELECT COUNT(*) FROM order_data SELECT COUNT(*) FROM order_data

    WHERE order_date = '2020-01-01' શϨίʔυ਺ΛΧ΢ϯτ ৚݅ʹ֘౰͢ΔϨίʔυ਺ΛΧ΢ϯτ
  36.  8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ SELECT COUNT(*) FROM order_data SELECT COUNT(*) FROM order_data

    WHERE order_date = '2020-01-01' શϨίʔυ਺ΛΧ΢ϯτ ৚݅ʹ֘౰͢ΔϨίʔυ਺ΛΧ΢ϯτ w '30.ͷޙʹ8)&3&Ͱ৚݅Λࢦఆ w 8)&3&Ͱࢦఆͨ͠৚݅ͷϨίʔυΛग़ྗͰ͖Δ
  37.  8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ SELECT COUNT(*) FROM order_data WHERE order_date <= '2020-01-05'

    SELECT COUNT(*) FROM order_data WHERE order_date <> '2020-01-01' ೥݄೔ΑΓ΋খ͍͞೔෇ͷϨίʔ υΛऔಘ ೥݄೔Ҏ֎ͷϨίʔυΛऔಘ
  38.  8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅ SELECT COUNT(*) FROM order_data WHERE order_date IS NULL

    SELECT COUNT(*) FROM order_data WHERE order_date IS NOT NULL
  39.  8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅɹղઆ SELECT COUNT(*) FROM order_data WHERE order_date IS NULL

    SELECT COUNT(*) FROM order_data WHERE order_date IS NOT NULL PSEFS@EBUB͕/6-- σʔλ͕ۭ Ϩίʔ υΛऔಘ PSEFS@EBUB͕/6--Ͱͳ͍ σʔλ͕ۭͰ ͳ͍ ϨίʔυΛऔಘ
  40.  8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆɹղઆ SELECT COUNT(*) FROM order_data WHERE order_date >= '2020-01-01'

    AND order_date <= '2020-01-02' ೥݄೔ΑΓେ͖͘ɺ ೥݄೔ΑΓখ͍͞೔෇ͷ ϨίʔυΛऔಘ ෳ਺ͷ৚݅Λ͚ͭΔ৔߹ɺͭ໨Ҏ߱ͷ৚݅͸ʮ"/%ʯͰܨ͛Δ
  41.  8)&3&ɹ໰୊ᶃ ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ ͔ΒՁ͕֨1000ԁҎԼͷ঎඼਺Λग़ྗ͠ ͍ͯͩ͘͞ ΧϥϜ໊ ҙຯ ܕ උߟ

    QSPEVDU@JE ঎඼*% 5&95 OBNF ঎඼໊ 5&95 QSJDF ֹۚ */5&(&3 DBUFHPSZ ΧςΰϦ 5&95 DBUFHPSZ ΧςΰϦ 5&95 DBUFHPSZ ΧςΰϦ 5&95 QSPEVDUTςʔϒϧ
  42.  8)&3&ɹղ౴ྫᶃɹղઆ ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ ͔ΒՁ͕֨1000ԁҎԼͷ঎඼਺Λग़ྗ͠ ͍ͯͩ͘͞ SELECT COUNT(*) FROM products

    WHERE price <= 1000 ঎඼਺ͷΧ΢ϯτʹ͸ू߹ؔ਺ͷ ʮ$06/5ʯΛ࢖͏ ʮԁҎԼʯͷ৚݅ʹ8)&3&Ͱ ʮʯΛ࢖͏
  43.  8)&3&ͷΠϝʔδ 8)&3&͸৚݅ʹҰகͨ͠ϨίʔυΛநग़͢ΔΠϝʔδ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF $ " #

     $ " #  $ " #  ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF $ " #  $ " # 
  44.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  45.  #&58&&/ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ SELECT * FROM products WHERE price <= 1000

    AND price >= 500 SELECT * FROM products WHERE price BETWEEN 500 AND 1000 "/%Ͱ৚݅Λͭͳ͛ͨ৔߹ #&58&&/Λ࢖ͬͯൣғࢦఆΛͨ͠৔߹
  46.  #&58&&/ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ SELECT * FROM products WHERE price <= 1000

    AND price >= 500 SELECT * FROM products WHERE price BETWEEN 500 AND 1000 "/%Ͱ৚݅Λͭͳ͛ͨ৔߹ #&58&&/Λ࢖ͬͯൣғࢦఆΛͨ͠৔߹ ͲͪΒ΋݁Ռ͸ಉ͡ͳͷͰͲͬͪΛ࢖ͬͯ΋ྑ͍Ͱ͕͢ɺ ൣғࢦఆͷ৔߹͸#&58&&/Λ࢖ͬͨํ͕௚ײతʹ෼͔Γ΍͍͢
  47.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  48.  -*,&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ SELECT * FROM products WHERE name LIKE '%ηοτ'

    ʮ8)&3&ྻ໊-*,&bݕࡧ৚݅`ʯͱͯ͠࢖͏ ໊લ͕ʮʓʓηοτʯʹͳ͍ͬͯΔσʔλ Λऔಘ(ʓʓ͸Կจࣈೖͬͯ΋ྑ͍)
  49.  -*,&ͷύλʔϯϚονྫ WHERE name LIKE '%ηοτ' ࣮ફͰ͸ʮʯΛ࢖͏ࣄ͕ଟ͍ͷͰʮʯͷ࢖͍ํ͚֮ͩ͑Ε͹0, WHERE name LIKE

    'ηοτ%' WHERE name LIKE '%ηοτ%' WHERE name LIKE '_ηοτ' ʮʓʓηοτʯ ʮʓʓηοτʓʓʯ ʮʓηοτʯ ʮηοτʓʓʯ ˞ʓ͸จࣈ ˞ʓ͸ԿจࣈͰ΋0, ˞ʓ͸ԿจࣈͰ΋0, ˞ʓ͸ԿจࣈͰ΋0,
  50.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  51.  */ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ SELECT DISTINCT category1 FROM products SELECT * FROM

    products WHERE category1 = '৯඼' ΧςΰϦͷೖ͍ͬͯΔ஋Λ֬ೝ ΧςΰϦΛࢦఆͯ͠ϨίʔυΛऔಘ ΧςΰϦ৚݅Λࢦఆͯ͠ςʔϒϧ͔ΒϨίʔυΛऔಘ͢Δ৔߹
  52.  */ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ SELECT * FROM products WHERE category1 = '৯඼'

    OR category1 = '೔༻඼' ෳ਺ͷΧςΰϦ৚݅Λࢦఆͯ͠ςʔϒϧ͔ΒϨίʔυΛऔಘ͢Δ৔߹
  53.  */ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ SELECT * FROM products WHERE category1 = '৯඼'

    OR category1 = '೔༻඼' ʮ03ʯ͸ANDͷ൓ରͰɺ௚લͷ৚͕݅௚ ޙͷ৚݅ͷ͍ͣΕ͔ʹ֘౰͢ΔσʔλΛந ग़͢Δ ෳ਺ͷΧςΰϦ৚݅Λࢦఆͯ͠ςʔϒϧ͔ΒϨίʔυΛऔಘ͢Δ৔߹
  54.  */ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅ SELECT * FROM products WHERE category1 IN ('৯඼',

    '೔༻඼') ෳ਺ͷΧςΰϦ৚݅Λࢦఆͯ͠ςʔϒϧ͔ΒϨίʔυΛऔಘ͢Δ৔߹
  55.  */ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅɹղઆ SELECT * FROM products WHERE category1 IN ('৯඼',

    '೔༻඼') ʮ*/ʯΛ࢖ͬͯෳ਺৚ ݅ʹϚον͢Δ͔Λ ߦͰهड़Ͱ͖Δ ෳ਺ͷΧςΰϦ৚݅Λࢦఆͯ͠ςʔϒϧ͔ΒϨίʔυΛऔಘ͢Δ৔߹ 03Λ࢖ͬͨ৚݅ͱ݁Ռ͸ಉ͡ͳͷͰͲͬͪΛ࢖ͬͯ΋ྑ͍Ͱ͕͢ɺ ෳ਺ͷ஋Λࢦఆ͢Δ৔߹͸*/Λ࢖ͬͨํ͕௚ײతʹ෼͔Γ΍͍͢
  56.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  57.  -*.*5ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ SELECT * FROM products SELECT * FROM products

    LIMIT 10 શ݅औಘ ͚݅ͩऔಘ w -*.*5Λ࢖͏ͱ42-ͷ࣮ߦ଎౓͕଎͘ͳΔ શ݅औಘͰ͸ͳ݅͘਺Λߜͬͯ औಘ͕ՄೳͳͷͰ  w Ͳ͏Ώ͏σʔλ͕ೖ͍ͬͯΔͷ͔֬ೝ͍ͨ࣌͠ʹΑ͘࢖͏(શ݅औಘͩͱ SQLͷෛՙ͕͔͔Δ৔߹͕͋ΔͷͰ)
  58.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  59.  03%&3#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ SELECT * FROM products SELECT * FROM products

    ORDER BY price ASC શ݅औಘ ஋ஈ͕͍҆ॱʹฒͼସ͑ ͲͷΧϥϜΛԿॱʹฒ΂ସ͑Δͷ ͔Λࢦఆ
  60.  03%&3#:ɹฒͼॱͷղઆ ORDER BY price ORDER BY price ASC ORDER

    BY price DESC ঢॱ খ͍͞ॱ Ͱฒͼସ͑ ߱ॱ େ͖͍ॱ Ͱฒͼସ͑ ঢॱ খ͍͞ॱ Ͱฒͼସ͑
  61.  03%&3#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ SELECT * FROM products ORDER BY price DESC,

    category1 ASC ฒͼସ͑৚݅͸ෳ਺ࢦఆͰ ͖Δ w ୈιʔτج४͕஋ஈͷ߱ॱ w ୈιʔτج४͕ΧςΰϦͷঢॱ
  62.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  63.  ᶃ಺෦݁߹ *//&3+0*/ ᶄࠨ֎෦݁߹ -&'5 065&3+0*/ ᶅӈ֎෦݁߹ 3*()5 065&3+0*/ ྆ํͷςʔϒϧͷڞ௨෦෼ͷ

    ΈΛ݁߹ͯ͠औಘ͢Δ ࠨଆͷςʔϒϧΛશͯऔಘ͠ ͯ݁߹͢Δ ӈଆͷςʔϒϧΛશͯऔಘ͠ ͯ݁߹͢Δ छྨͷ+0*/ɹ֓ཁ
  64.  +0*/ͷجຊߏจ FROM ςʔϒϧ1 AS ςʔϒϧ1ผ໊ JOIN ςʔϒϧ2 AS ςʔϒϧ2ผ໊

    ON ςʔϒϧ1ผ໊.Ωʔ = ςʔϒϧ2ผ໊.Ωʔ FROM order_data AS o INNER JOIN users AS u ON o.user_id = u.user_id جຊߏจ ۩ମྫ w ݁߹ͷࡍͷςʔϒϧ໊Λผ໊Ͱॻ͘͜ͱ͕Ͱ͖Δɻ4&-&$5ͷ"4ͱಉ͡ "4͸লུՄೳ  w ςʔϒϧ໊ͷ಄จࣈʹͨ͠ΓɺA,B΍1,2ͳͲͷ࿈൪Ͱผ໊Λ͚ͭΔ৔߹΋͋Δ͕ɺՄಡੑ্͕ ͕ΔΑ͏ʹ͚ͭͨํ͕ྑ͍ ݁߹৚݅
  65.  +0*/ ಺෦݁߹ ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ SELECT o.order_id, o.user_id, u.sex, u.birth FROM

    order_data o INNER JOIN users u ON o.user_id = u.user_id LIMIT 10
  66.  +0*/ ಺෦݁߹ ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ SELECT o.order_id, o.user_id, u.sex, u.birth FROM

    order_data o INNER JOIN users u ON o.user_id = u.user_id LIMIT 10 w PSEFS@EBUBͱVTFSTͷ৘ใ͕ͭͷςʔϒϧʹͳͬͯग़ྗͰ͖Δ w ςʔϒϧ໊Λผ໊Ͱॻ͘͜ͱ͕Ͱ͖Δʮ"4ʯ͸লུՄೳ w 4&-&$5ͰྻΛࢦఆ͢Δࡍ͸Ͳͷςʔϒϧͷྻͳͷ͔Λ໌ࣔ͢Δඞཁ͕͋Δ PVTFS@JE
  67.  +0*/ ಺෦݁߹ ɹઆ໌ SELECT COUNT(*) FROM order_data o INNER

    JOIN users u ON o.user_id = u.user_id SELECT COUNT(*) FROM order_data o JOIN users u ON o.user_id = u.user_id ʮ*//&3+0*/ʯ͸ ʮ+0*/ʯͱলུͰ͖Δ
  68.  +0*/ ಺෦݁߹ ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ SELECT COUNT(*) FROM order_data o INNER

    JOIN users u ON o.user_id = u.user_id w +0*/ͨ͠ޙͷςʔϒϧͷϨίʔυ਺Λ֬ೝ w ݩʑ͋ͬͨPSEFS@EBUBͷϨίʔυ਺ΑΓগͳ͘ͳ͍ͬͯΔ w ͜Ε͸಺෦݁߹ʹΑͬͯuser_idΛΩʔʹͨ࣌͠ʹorder_dataͱusersͷ2ͭ ͷςʔϒϧʹ྆ํೖ͍ͬͯΔσʔλΛऔಘ͍ͯ͠Δ͔Β
  69.  +0*/ ಺෦݁߹ ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ SELECT COUNT(*) FROM order_data o INNER

    JOIN users u ON o.user_id = u.user_id w PSEFS@EBUBͷதʹೖ͍ͬͯΔVTFS@JEΛ֬ೝ͢Δͱʮ/6--ʯʹͳ͍ͬͯ ΔϨίʔυ͕͋Δ ϩάΠϯͤͣʹߪೖͨ͠৔߹ΛΠϝʔδ  w ʮNULLʯ͸usersςʔϒϧʹ৘ใ͕ೖ͍ͬͯͳ͍ͷͰɺͦͷ෼ͷσʔλ͕ ݁߹͞Εͣʹ݁Ռͱͯ͠*//&3+0*/ͨ݁͠ՌͷϨίʔυ਺΋ݮ͍ͬͯΔ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE $ " # $ " # $ /6-- # ɾɾɾ ɾɾɾ ɾɾɾ VTFS@JE TFY CJSUI " உੑ  " ঁੑ  " உੑ  ɾɾɾ ɾɾɾ ɾɾɾ º ˓ ˓ VTFSTςʔϒϧ PSEFS@EBUBςʔϒϧ
  70.  +0*/ ֎෦݁߹ ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅ SELECT * FROM order_data o LEFT

    OUTER JOIN users u ON o.user_id = u.user_id LIMIT 10 SELECT * FROM order_data o INNER JOIN users u ON o.user_id = u.user_id LIMIT 10
  71.  +0*/ ֎෦݁߹ ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅɹղઆ SELECT * FROM order_data o LEFT

    OUTER JOIN users u ON o.user_id = u.user_id LIMIT 10 SELECT * FROM order_data o INNER JOIN users u ON o.user_id = u.user_id LIMIT 10 ಺ ෦ ݁ ߹ ֎ ෦ ݁ ߹
  72.  +0*/ ֎෦݁߹ ɹઆ໌ SELECT * FROM order_data o LEFT

    OUTER JOIN users u ON o.user_id = u.user_id LIMIT 10 w جຊߏจ͸಺෦݁߹ *//&3+0*/ ͱಉ͡ w -&'5065&3+0*/͸֎෦݁߹ͷͭ w 065&3͸লུͯ͠-&'5+0*/ͱॻ͘ࣄ͕Ͱ͖Δ w -&'5'30.ͷ௚ޙʹॻ͍ͨςʔϒϧɺ3*()5+0*/ͷޙʹॻ͍ ͨςʔϒϧ -&'5 3*()5 ˞42-JUFͷ৔߹͸3*()5065&3+0*/͸αϙʔτ͞Ε͍ͯͳ͍ͷͰΤϥʔʹͳΔ ˞࣮ࡍ3*()5065&3+0*/Λ࢖͏έʔε͸΄ͱΜͲͳ͍ͷͰɺ֎෦݁߹-&'5065&3+0*/ -&'5+0*/ ͱ֮͑ͯྑ͍
  73.  +0*/ ֎෦݁߹ ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆɹղઆ SELECT COUNT(*) FROM order_data o LEFT

    JOIN users u ON o.user_id = u.user_id w -&'5+0*/ͨ͠ޙͷςʔϒϧͷϨίʔυ਺Λ֬ೝ w ݩʑ͋ͬͨPSEFS@EBUBͷϨίʔυ਺ͱಉ݅͡਺ʹͳ͍ͬͯΔ
  74.  +0*/ ֎෦݁߹ ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆɹղઆ SELECT COUNT(*) FROM order_data o LEFT

    JOIN users u ON o.user_id = u.user_id ʮNULLʯ͸usersςʔϒϧʹ৘ใ͕ೖ͍ͬͯͳ͍͕ɺݩͱͳΔPSEFS@EBUBʹ ͸ೖ͍ͬͯΔͷͰɺVTFSTςʔϒϧͷ৘ใ͕શͯ/6--ͷঢ়ଶͰ݁߹͞ΕΔ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE $ " # $ " # $ /6-- # ɾɾɾ ɾɾɾ ɾɾɾ VTFS@JE TFY CJSUI " உੑ  " ঁੑ  /6-- /6-- /6-- ɾɾɾ ɾɾɾ ɾɾɾ ˓ ˓ VTFSTςʔϒϧ PSEFS@EBUBςʔϒϧ ˓
  75.  +0*/ͷ಺෦݁߹ͱ֎෦݁߹ͷҧ͍Λ֬ೝɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶇɹ SELECT * FROM order_data o INNER JOIN users

    u ON o.user_id = u.user_id WHERE o.user_id IS NULL SELECT * FROM order_data o LEFT JOIN users u ON o.user_id = u.user_id WHERE o.user_id IS NULL ֎ ෦ ݁ ߹ ಺ ෦ ݁ ߹
  76.  +0*/ͷ಺෦݁߹ͱ֎෦݁߹ͷҧ͍Λ֬ೝɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶇɹղઆɹ SELECT * FROM order_data o INNER JOIN users

    u ON o.user_id = u.user_id WHERE o.user_id IS NULL SELECT * FROM order_data o LEFT JOIN users u ON o.user_id = u.user_id WHERE o.user_id IS NULL ֎ ෦ ݁ ߹ ಺ ෦ ݁ ߹ VTFSTςʔϒϧͷ VTFS@JEʹ/6--ͷ৘ใ ͕ͳ͍ͷͰ݁߹͞Εͳ͍ VTFSTςʔϒϧͷ VTFS@JEʹ/6--ͷ৘ใ ͕ͳ͍ͷ͕/6--ͱͯ͠ ݁߹͞ΕΔ
  77.  +0*/ͷॱ൪Λม͑ΔͱͲ͏ͳΔ͔ ಺෦݁߹ͷ৔߹ SELECT COUNT(*) FROM order_data o INNER JOIN

    users u ON o.user_id = u.user_id SELECT COUNT(*) FROM users u INNER JOIN order_data o ON o.user_id = u.user_id ಺෦݁߹ JOOFSKPJO ͷ৔ ߹͸ͲͪΒͷςʔϒϧʹ ΋ڞ௨͢ΔσʔλΛऔಘ ͢ΔͷͰ݁Ռͷ݅਺͸ม ΘΒͳ͍
  78.  +0*/ͷॱ൪Λม͑ΔͱͲ͏ͳΔ͔ ֎෦݁߹ͷ৔߹ SELECT COUNT(*) FROM order_data o LEFT JOIN

    users u ON o.user_id = u.user_id SELECT COUNT(*) FROM users u LEFT JOIN order_data o ON o.user_id = u.user_id ֎෦݁߹ MFGUKPJO ͷ৔ ߹͸ͲͪΒͷςʔϒϧΛ ϕʔεʹ͢Δ͔Ͱ݅਺͕ มΘΔ ৔߹͕͋Δ
  79.  ͳͥ֎෦݁߹ͷ৔߹͸+0*/ͷॱ൪Λม͑Δͱ݁Ռ͕มΘΔͷ͔ SELECT COUNT(*) FROM order_data o LEFT JOIN users

    u ON o.user_id = u.user_id SELECT COUNT(*) FROM users u LEFT JOIN order_data o ON o.user_id = u.user_id ߪೖ৘ใͷग़ྗ ߪೖ͍ͯ͠ͳ͍Ϣʔβʔ৘ ใ͸Ͱͳ͍ Ϣʔβʔ৘ใͷग़ྗ Ϣʔβʔ৘ใ͕ͳ͍ߪೖ σʔλ͸ग़ͳ͍ߪೖ৘ใ ͷͳ͍Ϣʔβʔ৘ใ͕ग़Δ
  80.  +0*/ɹ໰୊ᶃ ҎԼςʔϒϧΛࢀߟʹʮ2020೥1݄1೔ʯͷߪೖσʔλͷ૯ച্ͱ݅਺Λग़ ͍ͯͩ͘͠͞ ΧϥϜ໊ ҙຯ ܕ QSPEVDU@JE ঎඼*% 5&95

    OBNF ঎඼໊ 5&95 QSJDF ֹۚ */5&(&3 DBUFHPSZ ΧςΰϦ 5&95 DBUFHPSZ ΧςΰϦ 5&95 ΧϥϜ໊ ҙຯ ܕ PSEFS@JE ஫จ*% 5&95 VTFS@JE Ϣʔβʔ*% 5&95 PSEFS@QSPEVDU@JE ঎඼*% 5&95 PSEFS@EBUF ஫จ೔࣌ */5&(&3 JT@EJTDPVOUFE ׂҾϑϥά */5&(&3 JT@DBODFMFE Ωϟϯηϧϑϥά */5&(&3 QSPEVDUTςʔϒϧ PSEFS@EBUBςʔϒϧ
  81.  +0*/ɹղ౴ྫᶃɹղઆ ҎԼςʔϒϧΛࢀߟʹʮ2020೥1݄1೔ʯͷߪೖσʔλͷߪೖֹۚͱ݅਺Λ ग़͍ͯͩ͘͠͞ SELECT COUNT(*), SUM(p.price) FROM order_data o

    LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.order_date = '2020-01-01' QSPEVDUTͱPSEFS@EBUF Λ঎඼*%ΛΩʔʹ݁߹ ೔෇Λ৚݅ࢦఆ ू߹ؔ਺Λ࢖ͬͯച্ͱ݅਺Λऔಘ
  82.  +0*/ɹղ౴ྫᶃɹิ଍ w ࠓճ͸-&'5+0*/ ֎෦݁߹ Ͱ΋*//&3+0*/ ಺෦݁߹ Ͱ΋݁Ռ͸ಉ͡Ͱ͢ w PSEFS@EBUBʹೖ͍ͬͯΔPSEFS@QSPEVDU@JE΋QSPEVDUTʹೖ͍ͬͯΔQSPEVDU@JE΋Ͳ

    ͪΒ΋ಉ͡΋ͷ͕ೖ͍ͬͯΔͷͰ Ͳ͔ͬͪʹ͔͠ೖͬͯͳ͍*%͕ͳ͍ͷͰ ݁Ռ͸ಉ͡ w ྫ͑͹ɺorder_dataʹ͸͋ͬͯɺproductsʹͳ͍঎඼ID͕ଘࡏͨ࣌͠(ͨ·ͨ·঎඼؅ཧΛ ͍ͯ͠ͳ͍σʔλ͕͋ͬͨ࣌)ɺINNER JOINͩͱ঎඼؅ཧ͞Ε͍ͯͳ͍σʔλ͕͸͔͡Εͯ ૯ച্΋ͦͷ෼ݮͬͯ͠·͏ͷͰ஫ҙ͕ඞཁ w ͜Ε΋঎඼؅ཧ͞Ε͍ͯͳ͍͔Βച্ͱͯ͠΋Χ΢ϯτ͠ͳ͍΄͏͕ྑ͍ͷ͔ɺച্ͱͯ͠ Χ΢ϯτͨ͠΄͏͕ྑ͍ͷ͔͸࣌ͱ৔߹ʹΑΔͷͰҰ֓ʹͲ͕ͬͪྑ͍ͱ͸ݴ͑ͳ͍
  83.  +0*/ɹ໰୊ᶄ ͪΐͬͱ೉͍͠ ҎԼςʔϒϧΛࢀߟʹuser_id͝ͱͷߪೖ݅਺ͱߪೖֹۚΛग़ͯͩ͘͠͞ ͍ɻ·ͨ݁Ռ͸ߪೖֹ͕ۚଟ͍ॱʹιʔτͯ͠ग़ྗ͍ͤͯͩ͘͞͞ ΧϥϜ໊ ҙຯ ܕ QSPEVDU@JE ঎඼*%

    5&95 OBNF ঎඼໊ 5&95 QSJDF ֹۚ */5&(&3 DBUFHPSZ ΧςΰϦ 5&95 DBUFHPSZ ΧςΰϦ 5&95 ΧϥϜ໊ ҙຯ ܕ PSEFS@JE ஫จ*% 5&95 VTFS@JE Ϣʔβʔ*% 5&95 PSEFS@QSPEVDU@JE ঎඼*% 5&95 PSEFS@EBUF ஫จ೔࣌ */5&(&3 JT@EJTDPVOUFE ׂҾϑϥά */5&(&3 JT@DBODFMFE Ωϟϯηϧϑϥά */5&(&3 QSPEVDUTςʔϒϧ PSEFS@EBUBςʔϒϧ
  84.  +0*/ɹղ౴ྫᶄɹղઆ ҎԼςʔϒϧΛࢀߟʹuser_id͝ͱͷߪೖ݅਺ͱߪೖֹۚΛग़ͯͩ͘͠͞ ͍ɻ·ͨ݁Ռ͸ߪೖֹ͕ۚଟ͍ॱʹιʔτͯ͠ग़ྗ͍ͤͯͩ͘͞͞ SELECT o.user_id, COUNT(*), SUM(p.price) AS total_price

    FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id GROUP BY o.user_id ORDER BY total_price DESC (3061#:ͰϢʔβʔ͝ͱʹά ϧʔϐϯά 03%&3#:Ͱߪೖֹ͕ۚଟ͍ॱ ʹιʔτ ू߹ؔ਺Λ࢖ͬͯϢʔβʔ͝ͱʹֹۚͱ݅਺Λऔಘ ˞03%&3#:ʹ͸$06/5΍46.ͨ݁͠Ռ΋هೖͰ͖Δ
  85.  +0*/ͷΠϝʔδ +0*/͸݁߹ͷΩʔΛܾΊͯςʔϒϧΛ݁߹͢Δ ྻΛ૿΍͢ Πϝʔδ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF $

    " #  $ " #  $ " #  ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF VTFS@JE TFY CJSUI $ " #  " உੑ  $ " #  " ঁੑ  $ " #  /6-- /6-- /6-- VTFS@JE TFY CJSUI " உੑ  " ঁੑ  /6-- /6-- /6-- ɾɾɾ ɾɾɾ ɾɾɾ
  86.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  87.  )"7*/(ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ ෮श SELECT category1, COUNT(*) FROM products GROUP BY

    category1 DBUFHPSZ͝ͱͷ঎඼݅਺Λऔಘ ݅਺͕݅Ҏ্ͷΧςΰϦΛந ग़͍ͨ͠৔߹͸ʁ
  88.  )"7*/(ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ SELECT category1, COUNT(*) FROM products WHERE COUNT(*) >=

    5 GROUP BY category1 ݅਺͕݅Ҏ্ͷDBUFHPSZΛऔಘ SELECT category1, COUNT(*) FROM products GROUP BY category1 DBUFHPSZ͝ͱͷ঎඼݅਺Λऔಘ
  89.  )"7*/(ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ SELECT category1, COUNT(*) FROM products WHERE COUNT(*) >=

    5 GROUP BY category1 ݅਺͕݅Ҏ্ͷDBUFHPSZΛऔಘ w 8)&3&Ͱू߹ؔ਺ͷ৚݅Λࢦఆ͢ΔͱΤϥʔʹͳΔ w 42-ʹ͸࣮ߦ͞ΕΔॱ൪͕͋Δ º SELECT category1, COUNT(*) FROM products GROUP BY category1 DBUFHPSZ͝ͱͷ঎඼݅਺Λऔಘ
  90.  42-ͷ࣮ߦ͞ΕΔॱ൪ '30. +0*/ 8)&3& (3061#: )"7*/( 4&-&$5 03%&3#: -*.*5

    ᶃ ᶄ ᶅ ᶆ ᶇ ᶈ ᶉ ᶊ ɾ8)&3&۟Ͱ(3061#:ͨ݁͠ՌΛऔಘ͠Α͏ͱ͍ͯ͠Δ͕ɺ (3061#:ΑΓઌʹ8)&3&ͷ৚͕࣮݅ߦ͞Εͯ͠·͏ ɾͭ·Γ(3061#:ͷ݁ՌΛ8)&3&Ͱࢦఆ͢Δ͜ͱ͸Ͱ͖ͳ͍ ɾGROUP BYͷޙʹ࣮ߦ͞ΕΔHAVINGΛ࢖͏͜ͱͰ࣮ݱՄೳ SELECT category1, COUNT(*) FROM products WHERE COUNT(*) >= 5 GROUP BY category1 º
  91. SELECT category1, COUNT(*) FROM products GROUP BY category1 HAVING COUNT(*)

    >= 5  )"7*/(ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅ '30. +0*/ 8)&3& (3061#: )"7*/( 4&-&$5 03%&3#: -*.*5 ᶃ ᶄ ᶅ ᶆ ᶇ ᶈ ᶉ ᶊ
  92.  )"7*/(ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅɹղઆ '30. +0*/ 8)&3& (3061#: )"7*/( 4&-&$5 03%&3#: -*.*5

    ᶃ ᶄ ᶅ ᶆ ᶇ ᶈ ᶉ ᶊ w 8)&3&$06/5 Ͱ͸ͳ͘)"7*/($06/5 ʹ ͢Δ w )"7*/(͸(3061#:ͷޙʹ࣮ߦ͞ΕΔͷͰ(3061#:ͷޙʹ ॻ͘ ˞8)&3&͸(3061#:ͷલʹॻ͘  w 03%&3#:ʹ$06/5 ͕࢖͑Δͷ΋42-ͷ࣮ߦॱ൪తʹ໰୊ͳ ͍͔Β SELECT category1, COUNT(*) FROM products GROUP BY category1 HAVING COUNT(*) >= 5
  93.  )"7*/(ɹ໰୊ᶃ ҎԼςʔϒϧΛࢀߟʹ೔͝ͱͷച্͕  ԁҎ্ͷσʔλΛநग़͠ ͍ͯͩ͘͞ ΧϥϜ໊ ҙຯ ܕ QSPEVDU@JE

    ঎඼*% 5&95 OBNF ঎඼໊ 5&95 QSJDF ֹۚ */5&(&3 DBUFHPSZ ΧςΰϦ 5&95 DBUFHPSZ ΧςΰϦ 5&95 ΧϥϜ໊ ҙຯ ܕ PSEFS@JE ஫จ*% 5&95 VTFS@JE Ϣʔβʔ*% 5&95 PSEFS@QSPEVDU@JE ঎඼*% 5&95 PSEFS@EBUF ஫จ೔࣌ */5&(&3 JT@EJTDPVOUFE ׂҾϑϥά */5&(&3 JT@DBODFMFE Ωϟϯηϧϑϥά */5&(&3 QSPEVDUTςʔϒϧ PSEFS@EBUBςʔϒϧ
  94.  )"7*/(ɹղ౴ྫᶃ ҎԼςʔϒϧΛࢀߟʹ೔͝ͱͷച্͕  ԁҎ্ͷσʔλΛநग़͠ ͍ͯͩ͘͞ SELECT o.order_date, COUNT(*), SUM(p.price)

    FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id GROUP BY o.order_date HAVING SUM(p.price) > 3000000
  95.  )"7*/(ɹղ౴ྫᶃɹղઆ ҎԼςʔϒϧΛࢀߟʹ೔͝ͱͷച্͕  ԁҎ্ͷσʔλΛநग़͠ ͍ͯͩ͘͞ SELECT o.order_date, COUNT(*), SUM(p.price)

    FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id GROUP BY o.order_date HAVING SUM(p.price) > 3000000 ू߹ؔ਺Λ࢖ͬͯ೔͝ͱʹֹۚͱ݅਺Λऔಘ )"7*/(Ͱച্ͷ৚݅Λࢦఆ ঎඼৘ใͱߪೖ৘ใΛ+0*/
  96.  )"7*/(ͷΠϝʔδ )"7*/(͸৚݅ʹҰகͨ͠ϨίʔυΛநग़͢ΔΠϝʔδ 8)&3&ͱಉ͡Πϝʔδ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF $ "

    #  $ " #  $ " #  ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF $ " #  $ " # 
  97.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  98.  $"4& w SQLͰ৚݅෼ذΛॻ͘͜ͱ͕Ͱ͖Δߏจ w ʓʓͩͬͨΒ˚˚ɺ××ͩͬͨΒ□□(ϓϩάϥϛϯάͰ͍͏if จΈ͍ͨͳ΋ͷ) CASE WHEN ৚݅1

    THEN ݁Ռ1 WHEN ৚݅2 THEN ݁Ռ2 (ELSE ্هҎ֎ͷ৚݅ʹ֘౰ͨ͠৔߹ͷ݁Ռ) END $"4&ࣜͷߏจ
  99.  $"4&ࣜͷ۩ମྫ ߪೖֹۚͰϔϏʔɺϛυϧɺϥΠτͷଐੑʹ෼ྨ͍ͨ͠ ߪೖֹۚ = =  =  

    ϔϏʔ ϛυϧ ϥΠτ ೥ؒߪೖֹ͕ۚ=  Ҏ্ ೥ؒߪೖֹ͕ۚ= ʙ=  Ҏ্ ೥ؒߪೖֹ͕ۚ= ҎԼ
  100.  $"4&ࣜͷ۩ମྫɹ42-Λ࣮ߦͯ͠ΈΑ͏ᶃ ෮श SELECT o.user_id, COUNT(*) AS total_count, SUM(p.price) AS

    total_price FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id IS NOT NULL GROUP BY o.user_id ·ͣ͸Ϣʔβʔ͝ͱͷߪೖֹۚΛग़͢
  101.  $"4&ࣜͷ۩ମྫɹ42-Λ࣮ߦͯ͠ΈΑ͏ᶃ ෮श ɹղઆ SELECT o.user_id, COUNT(*) AS total_count, SUM(p.price)

    AS total_price FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id IS NOT NULL GROUP BY o.user_id ·ͣ͸Ϣʔβʔ͝ͱͷߪೖֹۚΛग़͢ ू߹ؔ਺Λ࢖ͬͯϢʔβʔ͝ͱʹֹۚͱ݅਺Λऔಘ ঎඼৘ใͱߪೖ৘ใΛ+0*/ Ϣʔβʔ*%͕ۭͷσʔλ͸আ֎
  102. SELECT o.user_id, COUNT(*) AS total_count, SUM(p.price) AS total_price, CASE WHEN

    SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN SUM(p.price) < 1000000 AND SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id <> 'NULL' GROUP BY o.user_id  $"4&ࣜͷ۩ମྫɹ42-Λ࣮ߦͯ͠ΈΑ͏ᶄ
  103. SELECT o.user_id, COUNT(*) AS total_count, SUM(p.price) AS total_price, CASE WHEN

    SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN SUM(p.price) < 1000000 AND SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id <> 'NULL' GROUP BY o.user_id  $"4&ࣜͷ۩ମྫɹ42-Λ࣮ߦͯ͠ΈΑ͏ᶄɹղઆ $"4&ࣜΛ࢖ͬͯߪೖֹۚ͝ͱʹϑϥά͚ͭ
  104.  SELECT o.user_id, COUNT(*) AS total_count, SUM(p.price) AS total_price, CASE

    WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN SUM(p.price) < 1000000 AND SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id <> 'NULL' GROUP BY o.user_id ߪೖֹۚ 46. QQSJDF ͕  Ҏ্ͳΒϔϏʔ 8)&/5)&/Ͱ৚݅Λෳ਺هड़Մೳ ৚݅ʹ֘౰͠ͳ͍৔߹͸ʮͦͷଞʯ ϑϥά෇͚ͨ͠΋ͷΛ"4Ͱผ໊Λ͚ͭΔ $"4&ࣜͷ۩ମྫɹ42-Λ࣮ߦͯ͠ΈΑ͏ᶄɹղઆ
  105.  $"4&ࣜͷ۩ମྫɹղઆ CASE WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN

    SUM(p.price) < 1000000 AND SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml ෳ਺৚݅Λॻ͘৔߹ɺ৚݅͸ॻ͍ͨॱ͔Β࣮ߦ͞Εͯɺ৚݅ʹҰகͨ͠ ৔߹͸ͦͷޙͷ৚݅͸εΩοϓ͞ΕΔ CASE WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml ্͔Βॱ൪ʹධՁ͞ΕΔͷͰ 46. QQSJDF ͸ෆཁ
  106.  $"4&ࣜͷ஫ҙ఺ w $"4&ࣜͷ৚݅͸্͔Βॱ൪ʹධՁ͞ΕΔͷ w ৚݅ͷॱ൪Λม͑Δͱҙਤ͠ͳ͍݁ՌʹͳΔՄೳੑ͕͋ΔͷͰ஫ҙ w ΏΔ͍৚݅Λઌʹॻ͍ͯ͠·͏ͱޙ͔Β͖͍ͭ৚݅ʹ֘౰͠ͳ͘ͳͬ ͯ͠·͏ͷͰ஫ҙ CASE

    WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml º ˞৚݅ͷॱ൪Λม͑ΔͱϔϏʔ͕֘౰ͳ͠ʹͳͬͯ͠·͏
  107.  $"4&ɹ໰୊ᶃ ҎԼ42-ΛࢀߟʹϢʔβʔ͝ͱͷߪೖ݅਺͕݅Ҏ্ΛϩΠϠϧɺ ݅Ҏ্݅ະຬΛϔϏʔɺ݅Ҏ্݅ະຬΛϛυϧɺ݅ະຬ ΛϥΠτɺͦΕҎ֎Λͦͷଞͱ෼ྨ͍ͯͩ͘͠͞ SELECT o.user_id, COUNT(*) AS total_count,

    SUM(p.price) AS total_price, CASE WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN SUM(p.price) < 1000000 AND SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id <> 'NULL' GROUP BY o.user_id
  108.  $"4&ɹղ౴ྫᶃ ҎԼ42-ΛࢀߟʹϢʔβʔ͝ͱͷߪೖ݅਺͕݅Ҏ্ΛϩΠϠϧɺ ݅Ҏ্݅ະຬΛϔϏʔɺ݅Ҏ্݅ະຬΛϛυϧɺ݅ະຬ ΛϥΠτɺͦΕҎ֎Λͦͷଞͱ෼ྨ͍ͯͩ͘͠͞ SELECT o.user_id, COUNT(*) AS total_count,

    SUM(p.price) AS total_price, CASE WHEN COUNT(*) >= 500 THEN 'ϩΠϠϧ' WHEN COUNT(*) >= 300 THEN 'ϔϏʔ' WHEN COUNT(*) >= 100 THEN 'ϛυϧ' WHEN COUNT(*) < 100 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id <> 'NULL' GROUP BY o.user_id
  109.  $"4&ɹղ౴ྫᶃɹղઆ ҎԼ42-ΛࢀߟʹϢʔβʔ͝ͱͷߪೖ݅਺͕݅Ҏ্ΛϩΠϠϧɺ ݅Ҏ্݅ະຬΛϔϏʔɺ݅Ҏ্݅ະຬΛϛυϧɺ݅ະຬ ΛϥΠτɺͦΕҎ֎Λͦͷଞͱ෼ྨ͍ͯͩ͘͠͞ SELECT o.user_id, COUNT(*) AS total_count,

    SUM(p.price) AS total_price, CASE WHEN COUNT(*) >= 500 THEN 'ϩΠϠϧ' WHEN COUNT(*) >= 300 THEN 'ϔϏʔ' WHEN COUNT(*) >= 100 THEN 'ϛυϧ' WHEN COUNT(*) < 100 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id <> 'NULL' GROUP BY o.user_id ৚݅Λߪೖ݅਺ $06/5 ʹͯ͠ɺ ύλʔϯʹ෼ྨ
  110.  $"4&ͷΠϝʔδ $"4&͸৽͘͠ྻΛ௥Ճ͢Δͱ͖ʹ࢖͏Πϝʔδ ˞4&-&$5ͷதͰ$"4&ࣜΛ࢖͏৔߹ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF $ "

    #  $ " #  $ " #  ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF INM $ " #  ϔϏʔ $ " #  ϛυϧ $ " #  ϥΠτ ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ ɾɾɾ
  111.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  112.  Ϣʔβʔͷੑผ͕ʮஉੑʯ͚ͩͷ஫จ*%Λऔಘ͍ͨ͠ SELECT o.order_id, u.sex FROM order_data o LEFT JOIN

    users u ON o.user_id = u.user_id WHERE u.sex = 'உੑ' +0*/Λ࢖ͬͯVTFSTͱPSEFS@EBUBΛ݁߹ͤͯ͞ɺVTFSTͷੑผΛ8)&3& ͷ৚݅Ͱࢦఆ αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ ෮श ɹղઆ
  113.  Ϣʔβʔͷੑผ͕ʮஉੑʯ͚ͩͷ஫จ*%Λऔಘ͍ͨ͠ SELECT order_id FROM order_data WHERE user_id IN (SELECT

    DISTINCT user_id FROM users WHERE sex = 'உੑ') αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ
  114.  Ϣʔβʔͷੑผ͕ʮஉੑʯ͚ͩͷ஫จ*%Λऔಘ͍ͨ͠ SELECT order_id FROM order_data WHERE user_id IN (SELECT

    DISTINCT user_id FROM users WHERE sex = 'உੑ') 8)&3&ͷ৚݅ʹ4&-&$5จΛ࢖͏ࣄ͕Ͱ͖Δ ˞+0*/Λ࢖͏ͱݩͷςʔϒϧͷϨίʔυ਺͕૿ݮ͢ΔՄೳੑ͕͋ΔͷͰɺݩͷςʔϒϧͷϨίʔυ਺Λ୲อͭͭ͠৚݅ࢦఆ ͍ͨ͠৔߹ʹ͸ศར αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ
  115.  ߪೖֹۚͰϔϏʔɺϛυϧɺϥΠτΛ෼͚ͨͱ͖ʹɺϔϏʔɺϛυϧɺϥΠ τͷϢʔβʔ͕ͦΕͧΕԿਓ͍Δͷ͔ूܭ͍ͨ͠ SELECT o.user_id, COUNT(*) AS total_count, SUM(p.price) AS

    total_price, CASE WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id <> 'NULL' GROUP BY o.user_id $"4&ࣜΛ࢖ͬͯϔ ϏʔɺϛυϧɺϥΠ τͷϑϥά෇͚ ͜ͷ݁Ռʹରͯ͠͞ Βʹ42-Λॻ͘ࣄ͕ Ͱ͖Δ αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅ ෮श
  116.  SELECT hml, COUNT(user_id) FROM ( SELECT o.user_id, COUNT(*) AS

    total_count, SUM(p.price) AS total_price, CASE WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id <> 'NULL' GROUP BY o.user_id ) GROUP BY hml ߪೖֹۚͰϔϏʔɺϛυϧɺϥΠτΛ෼͚ͨͱ͖ʹɺϔϏʔɺϛυϧɺϥΠ τͷϢʔβʔ͕ͦΕͧΕԿਓ͍Δͷ͔ूܭ͍ͨ͠ αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆ
  117.  SELECT hml, COUNT(user_id) FROM ( SELECT o.user_id, COUNT(*) AS

    total_count, SUM(p.price) AS total_price, CASE WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id <> 'NULL' GROUP BY o.user_id ) GROUP BY hml 4&-&$5จͷ݁Ռ Λ'30.Ͱࢦఆ͠ ͯɺ4&-&$5ͨ͠ ݁Ռʹରͯ͠42- Λ͔͚Δ ߪೖֹۚͰϔϏʔɺϛυϧɺϥΠτΛ෼͚ͨͱ͖ʹɺϔϏʔɺϛυϧɺϥΠ τͷϢʔβʔ͕ͦΕͧΕԿਓ͍Δͷ͔ूܭ͍ͨ͠ αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆɹղઆ
  118.  ͜ͷͭͷ42-Λ߹ମͤͨ͞Πϝʔδ SELECT o.user_id, COUNT(*) AS total_count, SUM(p.price) AS total_price,

    CASE WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id <> 'NULL' GROUP BY o.user_id $"4&ࣜΛ࢖ͬͯϑϥά෇͚ + SELECT hml, COUNT(user_id) FROM αϒΫΤϦ GROUP BY hml αϒΫΤϦΛ࢖ͬͯूܭ αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆɹղઆ
  119.  αϒΫΤϦɹ໰୊ᶃ ҎԼSQLΛࢀߟʹߪೖ݅਺ͰHML෼ྨͨ݁͠ՌͰɺHML͝ͱͷϢʔβʔ ਺ɺߪೖ݅਺ɺߪೖֹۚΛग़͍ͯͩ͘͠͞ SELECT o.user_id, COUNT(*) AS total_count, SUM(p.price)

    AS total_price, CASE WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id <> 'NULL' GROUP BY o.user_id $"4&ࣜΛ࢖ͬͯ).-ʹ෼ྨ
  120.  αϒΫΤϦɹղ౴ྫᶃ ҎԼSQLΛࢀߟʹߪೖ݅਺ͰHML෼ྨͨ݁͠ՌͰɺHML͝ͱͷϢʔβʔ ਺ɺߪೖ݅਺ɺߪೖֹۚΛग़͍ͯͩ͘͠͞ SELECT hml, COUNT(user_id), SUM(total_count), SUM(total_price) FROM

    ( SELECT o.user_id, COUNT(*) AS total_count, SUM(p.price) AS total_price, CASE WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id <> 'NULL' GROUP BY o.user_id ) GROUP BY hml
  121.  αϒΫΤϦɹղ౴ྫᶃɹղઆ ҎԼSQLΛࢀߟʹߪೖ݅਺ͰHML෼ྨͨ݁͠ՌͰɺHML͝ͱͷϢʔβʔ ਺ɺߪೖ݅਺ɺߪೖֹۚΛग़͍ͯͩ͘͠͞ SELECT hml, COUNT(user_id), SUM(total_count), SUM(total_price) FROM

    ( SELECT o.user_id, COUNT(*) AS total_count, SUM(p.price) AS total_price, CASE WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id <> 'NULL' GROUP BY o.user_id ) GROUP BY hml UPUBM@DPVOU ߪೖ݅਺ ͱ UPUBM@QSJDF ߪೖֹۚ Λ46.Ͱ߹ܭ ※FROMͷதͰαϒΫΤϦΛ࢖͏৔߹ ͸SELECTͨ݁͠Ռʹରͯ͠ASͰ໊લ Λ͚ͭΔඞཁ͕͋Γ·͢
  122.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  123.  8*5)۟ͷ׆༻ྫ SELECT hml, COUNT(user_id) FROM ( SELECT o.user_id, COUNT(*)

    AS total_count, SUM(p.price) AS total_price, CASE WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id <> 'NULL' GROUP BY o.user_id ) GROUP BY hml ϔϏʔɺϛυϧɺϥΠτͷϢʔβʔ਺ूܭ ؒҧ͍Ͱ͸ͳ͍͚ Ͳ42-͕ݟͮΒ͍
  124.  8*5)۟ͷ׆༻ྫɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ WITH hml_data AS( SELECT o.user_id, COUNT(*) AS total_count,

    SUM(p.price) AS total_price, CASE WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id <> 'NULL' GROUP BY o.user_id ) SELECT hml, COUNT(user_id) FROM hml_data GROUP BY hml ϔϏʔɺϛυϧɺϥΠτͷϢʔβʔ਺ूܭ
  125.  WITH hml_data AS( SELECT o.user_id, COUNT(*) AS total_count, SUM(p.price)

    AS total_price, CASE WHEN SUM(p.price) >= 1000000 THEN 'ϔϏʔ' WHEN SUM(p.price) >= 200000 THEN 'ϛυϧ' WHEN SUM(p.price) < 200000 THEN 'ϥΠτ' ELSE 'ͦͷଞ' END AS hml FROM order_data o LEFT JOIN products p ON o.order_product_id = p.product_id WHERE o.user_id <> 'NULL' GROUP BY o.user_id ) SELECT hml, COUNT(user_id) FROM hml_data GROUP BY hml ϔϏʔɺϛυϧɺϥΠτͷϢʔβʔ਺ूܭ 8*5)۟Λ࢖ͬͯҰ࣍ ςʔϒϧΛ࡞੒ ࡞੒ͨ͠ςʔϒϧΛ 42-ͷதͰ࢖͑Δ 8*5)۟ͷ׆༻ྫɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
  126.  8*5)۟ͷߏจ WITH ςʔϒϧ໊1 AS( ), ςʔϒϧ໊2 AS( ) SELECT

    * FROM ςʔϒϧ໊1 w ΧϯϚ۠੾ΓͰෳ਺ͷҰ࣍ςʔϒϧ Λ࡞Δࣄ͕Ͱ͖Δ w 42-্ͰҰ࣍ςʔϒϧΛ࡞Δࣄ͕Ͱ ͖Δ ࣮ࡍʹςʔϒϧ͕࡞੒͞ΕΔ Θ͚Ͱ͸ͳ͍ͷͰ42-ͷதͰ͔͠࢖ ͑ͳ͍  w 8*5)۟Λ࢖͏ͱෳࡶͳΫΤϦ΋Մ ಡੑΛߴΊΔࣄ͕Ͱ͖Δ αϒΫΤϦΛ࢖͏৔߹ʹ͸8*5)۟Λ࢖ͬͨํ͕Մಡੑ͕ߴ· ΔͷͰੵۃతʹ8*5)۟Λ࢖͏͜ͱΛ͓קΊ͠·͢
  127.  8*5)۟ɹ໰୊ᶃ ҎԼSQLΛ8*5)۟Λ࢖ͬͯॻ͖௚ͯ͠Έ͍ͯͩ͘͞ ϦϑΝΫλϦϯά SELECT order_id FROM order_data WHERE user_id

    IN (SELECT DISTINCT user_id FROM users WHERE sex = 'உੑ') ͜͜Λ8*5)۟Λ࢖ͬͯॻ͖௚͠
  128.  8*5)۟ɹղ౴ྫᶃ ҎԼSQLΛ8*5)۟Λ࢖ͬͯॻ͖௚ͯ͠Έ͍ͯͩ͘͞ ϦϑΝΫλϦϯά WITH user_man AS( SELECT DISTINCT user_id

    FROM users WHERE sex = 'உੑ' ) SELECT order_id FROM order_data WHERE user_id IN (SELECT user_id FROM user_man)
  129.  8*5)۟ɹղ౴ྫᶃɹղઆ ҎԼSQLΛ8*5)۟Λ࢖ͬͯॻ͖௚ͯ͠Έ͍ͯͩ͘͞ ϦϑΝΫλϦϯά WITH user_man AS( SELECT DISTINCT user_id

    FROM users WHERE sex = 'உੑ' ) SELECT order_id FROM order_data WHERE user_id IN (SELECT user_id FROM user_man) 8*5)۟Λ࢖ͬͯҰ࣍ςʔϒϧΛ࡞੒ 8*5)۟ͷςʔϒϧΛ ৚݅Ͱࢦఆ ˞͜Ε͘Β͍ͩͱ8*5)۟Λ࢖ͬͯ΋͋·ΓมԽͳ͍Ͱ͕͢ɺ৚͕݅ෳࡶʹͳͬͯ͘Δͱ8*5)۟Λ࢖ͬͨํ͕Մಡੑ͕ߴ· Δ ྫ͑͹உੑͰɺࡀҎ্Ͱɺ࡟আ͞Εͯͳ͍*%ͳͲ৚͕݅૿͑ͨ৔߹
  130.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  131.  6/*0/ 6/*0/"-- w ෳ਺ͷςʔϒϧΛ݁߹͢Δͱ͖ʹ࢖͏هड़ w σʔλΛॎ͔࣋ͪΒԣ࣋ͪʹมߋ͍ͨ͠ͱ͖ʹ࢖͑Δ w +0*/ʹΑΔ݁߹͸ςʔϒϧͷʮྻ ॎ

    ʯΛ݁߹͢Δ΍ΓํͰɺ 6/*0/ 6/*0/"-- ʹΑΔ݁߹͸ςʔϒϧͷʮߦ ԣ ʯΛ݁߹͢Δ ΍Γํ +0*/ 6/*0/ 6/*0/"--
  132.  6/*0/ 6/*0/"-- ͷ׆༻ྫɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ SELECT MAX(birth) AS max_birth, MIN(birth) AS

    min_birth FROM users SELECT 'max_birth' as data_key, MAX(birth) AS data_value FROM users UNION ALL SELECT 'max_birth' as data_key, MIN(birth) AS data_value FROM users
  133.  6/*0/ 6/*0/"-- ͷ׆༻ྫɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ SELECT MAX(birth) AS max_birth, MIN(birth) AS

    min_birth FROM users SELECT 'max_birth' as data_key, MAX(birth) AS data_value FROM users UNION ALL SELECT 'max_birth' as data_key, MIN(birth) AS data_value FROM users σʔλΛॎʹ࣋ͭ৔߹ NBY@CJSUI NJO@CJSUI NBY@CJSUI NJO@CJSUI σʔλΛԣʹ࣋ͭ৔߹ 9999 9999 9999 9999
  134.  SELECT 'max_birth' as data_key, MAX(birth) AS data_value FROM users

    UNION ALL SELECT 'max_birth' as data_key, MIN(birth) AS data_value FROM users σʔλΛԣʹ࣋ͭ৔߹ w 6/*0/ 6/*0/"-- Ͱ݁߹ͤ͞ Δςʔϒϧ͸ΧϥϜ͕શͯҰகͯ͠ ͍Δඞཁ͕͋Δ ࠨͷྫͰ͸ EBUB@LFZͱEBUB@WBMVF͕ͲͪΒ ʹ΋ଘࡏ͢Δඞཁ͕͋Δ 6/*0/ 6/*0/"-- ॏෳߦΛഉআͯ݁͠߹ ॏෳߦΛഉআͤͣ݁߹ 6/*0/ 6/*0/"-- ͷ׆༻ྫɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
  135.  6/*0/ 6/*0/"-- ͷΠϝʔδ 6/*0/ 6/*0/"-- ͸৽͘͠ߦΛ௥Ճ͢Δ࢖͏Πϝʔδ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE

    $ " # $ " # ɾɾɾ ɾɾɾ ɾɾɾ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE $ " # $ " # ɾɾɾ ɾɾɾ ɾɾɾ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE $ " # $ " # $ " # $ " # ɾɾɾ ɾɾɾ ɾɾɾ
  136.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  137.  5JQTᶄ ίϝϯτΛ༗ޮʹ࢖͍·͠ΐ͏ -- ঎඼σʔλͷऔಘ SELECT product_id, name FROM products

    /* ঎඼σʔλͷऔಘ (঎඼IDͱ໊લ) */ SELECT product_id, name FROM products ʮʯΛ࢖ͬͯߦίϝϯτΞ΢τ͕Ͱ͖·͢ ʮ  ʯΛ࢖͏ͱෳ਺ߦίϝϯτΞ΢τ͕Ͱ͖·͢ ෳࡶͳ42-ʹίϝϯτΛ͚ͭͯՄಡੑΛ͋͛ͨΓɺτϥΠΞϯυΤϥʔͰ Ұ࣌తʹίϝϯτΞ΢τͨ͠Γ͢ΔͱศརͰ͢
  138.  5JQTᶆ 42-ͷू߹ؔ਺Ҏ֎ʹ΋࢖͑Δؔ਺͸͍ͬͺ͍͋Γ·͢ %#ͷछྨʹΑͬͯ΋࢖͑Δؔ਺͸ҟͳΔͷͰɺશ֮ͯ͑Δඞཁ͸ͳ͘౎౓ ௐ΂ͯ࢖͑Ε͹ྑ͍ͱࢥ͍·͢ SELECT birth, cast(birth as TEXT)

    as birth_text, date(), substr(date(), 1, 4) as year, substr(date(), 6, 2) as month FROM users DBTU ܕΛม׵͢Δ EBUF ݱࡏ೔෇Λऔಘ͢Δ TVCTUS จࣈྻΛ੾ΓऔΔ ɾ ɾ ɾ ˞ࠓճ͸৭ʑͳؔ਺΋͋Δͱ͍͏ཧղ͚ͩͰ͖Ε͹ྑ͍Ͱ͢