$30 off During Our Annual Pro Sale. View Details »

実演で理解するビジネスパーソンのためのSQL入門

hikarut
September 01, 2023

 実演で理解するビジネスパーソンのためのSQL入門

2023年8月31日実施のSQL勉強会#2

hikarut

September 01, 2023
Tweet

More Decks by hikarut

Other Decks in Business

Transcript

  1. ೖ໳

    42-ษڧձ
    ࣮ԋͰཧղ͢Δ
    ϏδωεύʔιϯͷͨΊͷ
    42-

    View Slide

  2. © )JLBSV5BLBIBTIJ
    ߴڮޫ
    )JLBSV5BLBIBTIJ
    ೥ʹ৽ଔͰϠϑʔגࣜձࣾʹೖࣾ͠ɺ8FCΤϯδχΞͱͯ͠αʔϏεͷ։ൃɺӡ༻ɺا
    ըɺఏҊɺͳͲ෯޿͘αʔϏεʹߩݙɻ೥͔Β͸גࣜձࣾΠʔϒοΫΠχγΞςΟϒδϟύ
    ϯͰσʔλαΠΤϯεάϧʔϓΛ্ཱͪ͛෦௕ͱͯࣾ͠಺ͷσʔλ׆༻Λଅਐɻ೥͔Β֎ࢿ
    ܥίϯαϧςΟϯάاۀʹೖࣾ͠ɺ42-Λ࢖ͬͨσʔλ෼ੳͳͲσʔλΛ׆༻ͨ͠ϚʔέςΟϯ
    άࢧԉΛ࣮ࢪɻ
    ࣗݾ঺հ
    1SP
    fi
    MF
    ෳۀͱͯ͠#-&"(6&ͷΫϥϒνʔϜͰɺϚʔέςΟϯάετϥς
    δετͱͯ͠σʔλ෼ੳͷαϙʔτΛߦͬͨΓɺͦͷଞෳ਺ࣾͰ
    42-Λ׆༻ͨ͠σʔλ෼ੳͷαϙʔτܦݧ͋Γɻʰσʔλ෼ੳྗΛ
    ߴΊΔϏδωεύʔιϯͷͨΊͷ42-ೖ໳ʱͷஶऀɻ
    MJHIU

    View Slide

  3. wࠓ42-ΛֶͿ΂͖ͭͷཧ༝
    ΞδΣϯμ
    "HFOEB
    wຊॻͷັྗ
    wσʔλ෼ੳͰ࢖͏42-ͷͭͷಛ௃
    w42-Λ࢖ͬͨσʔλ෼ੳͷσϞ

    View Slide

  4. wࠓ42-ΛֶͿ΂͖ͭͷཧ༝
    ΞδΣϯμ
    "HFOEB
    wຊॻͷັྗ
    wσʔλ෼ੳͰ࢖͏42-ͷͭͷಛ௃
    w42-Λ࢖ͬͨσʔλ෼ੳͷσϞ

    View Slide

  5. © )JLBSV5BLBIBTIJ

    42-ͱ͸ʰ4USVDUVSFE2VFSZ-BOHVBHFʱͷུͰʰΤε
    ΩϡʔΤϧʱͱಡΉ

    42-͸
    デ
    ʔλ
    ベ
    ʔε্ͷςʔ
    ブ
    ϧʹ֨ೲ͞Εͨ
    デ
    ʔλΛऔ
    ಘ͢Δͱ͖ʹ࢖͏ݴޠ

    Ϗδωεύʔιϯ͕࢖͏42-ͱΤϯδχΞ͕࢖͏42-͸ҧ͏

    σʔλ෼ੳͰ࢖͏42-ͷͭͷಛ௃
    8IBU42-

    View Slide

  6. © )JLBSV5BLBIBTIJ
    σʔλ෼ੳͰ࢖͏42-ͷಛ௃
    8IBU42-
    VTFS@JE HFOEFS CJSUI
    " உੑ
    " ঁੑ
    ɾɾɾ ɾɾɾ ɾɾɾ
    QSPEVDU@JE QSPEVDU@OBNF QSJDF
    # ͓͍͍͠ਫ
    # γϟʔϓϖϯ
    ɾɾɾ ɾɾɾ ɾɾɾ
    42-
    VTFS@JE HFOEFS CJSUI
    " உੑ
    ࣮ߦ݁Ռ
    4&-&$5'30.VTFST
    8)&3&VTFS@JEb"`
    σʔλϕʔε ςʔϒϧ
    42-ͱ͸ʰ4USVDUVSFE2VFSZ-BOHVBHFʱͷུͰʰΤεΩϡʔΤϧʱͱಡΉ
    42-͸
    デ
    ʔλ
    ベ
    ʔε্ͷςʔ
    ブ
    ϧʹ֨ೲ͞Εͨ
    デ
    ʔλΛऔಘ͢Δͱ͖ʹ࢖͏ݴޠ

    View Slide

  7. © )JLBSV5BLBIBTIJ
    σʔλ෼ੳͰ࢖͏42-ͷಛ௃
    8IBU42-
    Ϗδωεύʔιϯ͕࢖͏42-ͱΤϯδχΞ͕࢖͏42-͸ҧ͏
    wσʔλ෼ੳͰ͸جຊతʹ͸σʔλͷऔಘʢ4&-&$5ʣΛ
    ֮͑Ε͹Α͍
    w42-ͷॲཧ଎౓ʹ͍ͭͯҙࣝ͢Δඞཁੑ͕௿͍
    wجຊతͳߏจΛ֮͑Ε͹ͣͬͱ࢖͍ଓ͚Δ͜ͱ͕Ͱ͖Δ
    wΞϓϦέʔγϣϯ։ൃʹඞཁͳ42-ʢ4&-&$5ɹ
    */4&35%&-&5&61%"5&$3&"5&ʣΛ֮͑Δ
    ඞཁ͕͋Δ
    wϛϦඵ୯ҐͰॲཧ଎౓Λҙࣝ͢Δඞཁ͕͋ΔʢͦͷͨΊ
    ΠϯσοΫεͳͲͷσʔλϕʔεͷ஌ࣝ΋ඞཁʹͳΔʣ
    w৽͍͠42-ͷߏจΛ֮͑ͯΑΓޮ཰తͳ42-Λॻ͘ඞཁ
    ͕͋Δ
    Ϗδωεύʔιϯ͕࢖͏42- ΤϯδχΞ͕࢖͏42-

    View Slide

  8. wࠓ42-ΛֶͿ΂͖ͭͷཧ༝
    ΞδΣϯμ
    "HFOEB
    wຊॻͷັྗ
    wσʔλ෼ੳͰ࢖͏42-ͷͭͷಛ௃
    w42-Λ࢖ͬͨσʔλ෼ੳͷσϞ

    View Slide

  9. © )JLBSV5BLBIBTIJ
    ᶃσʔλ෼ੳʹ͓͚Δ
    42-͸ίεύ͕Α͍
    ᶄ࣮຿Ͱ෯޿͘࢖͑Δ
    ᶅֻ͚ࢉͷεΩϧͱͯ͠
    ໾ཱͭ
    σʔλ෼ੳͰֶͿ42-͸ֶशίετ
    ͕௿͘ɺҰ౓ֶ΂͹ීวతʹ׆Ͱ͖
    ΔεΩϧ
    42-͸৬छΛ໰Θͣ͋ΒΏΔ৬छͰ
    σʔλ෼ੳͰ׆༻Ͱ͖ΔͨΊ൚༻ੑ
    ͕ߴ͍εΩϧ
    σʔλநग़ΛΤϯδχΞʹཔΒͣɺ
    42-Λ࢖͏͜ͱͰࣗ෼ͷཉ͍͠σʔ
    λΛૉૣ͘ूܭ͢Δ͜ͱ͕Ͱ͖Δ
    ࠓ42-ΛֶͿ΂͖ͭͷཧ༝
    8IZ42-

    View Slide

  10. © )JLBSV5BLBIBTIJ
    ᶃσʔλ෼ੳͷ42-͸ίεύ͕Α͍
    ׆

    ͷ

    ͕
    ڱ
    ͍
    ׆

    ͷ

    ͕
    ޿
    ͍
    ֶशίετ͕௿͍
    ֶशίετ͕ߴ͍
    42-
    Ұൠతͳϓϩάϥϛϯ
    άݴޠʹൺ΂ֶͯशί
    ετ͕௿͘ɺҰ౓ֶश
    ͢Ε͹௕ظؒ࢖͑Δε
    Ωϧͱͯ͠໾ཱͭ
    शಘͰ͖Ε͹σʔλ෼
    ੳ΍ιϑτ΢ΣΞ։ൃ
    ͳͲ෯޿͘࢖͑ΔϝϦ
    οτ͸͋Δֶ͕शίε
    τ͕ඇৗʹߴ͍
    Ұൠతͳ
    ϓϩάϥϛϯάݴޠ
    ୭Ͱ΋ѻ͍΍͘͢σʔ
    λ෼ੳͰ΋׆༻Ͱ͖Δ
    ͕ɺѻ͑Δσʔλͷྔ
    ʹݶք͕͋Γෳࡶͳ෼
    ੳ͕͠ʹ͍͘
    ΤΫηϧ
    ࠓ42-ΛֶͿ΂͖ͭͷཧ༝

    View Slide

  11. © )JLBSV5BLBIBTIJ
    ᶄ࣮຿Ͱ෯޿͘࢖͑Δ
    ࠓ42-ΛֶͿ΂͖ͭͷཧ༝
    େاۀͳͲͰΑ͋͘ΔҰൠతͳσʔλ෼ੳ؀ڥ
    42-
    42-
    ෼ੳ݁Ռ
    σʔλ
    42-Λ࢖͏͜ͱͰͦͷ࣌ʑʹԠͯ͡ඞཁͳσʔλ
    Λૉૣ͘ूܭ͢Δ͜ͱ͕Ͱ͖Δ
    w ΤϯδχΞʹґཔͤͣʹࣗ෼͕ཉ͍͠σʔλΛૉૣ͘ूܭ
    w 1%$"Λߴ଎Ͱճ͢͜ͱ͕Ͱ͖Δ
    جװγεςϜ %8)ʢσʔλ΢ΣΞϋ΢εʣ #*πʔϧ

    View Slide

  12. © )JLBSV5BLBIBTIJ
    ᶄ࣮຿Ͱ෯޿͘࢖͑Δ
    ࠓ42-ΛֶͿ΂͖ͭͷཧ༝
    খن໛ɾݸਓͰߦ͏σʔλ෼ੳ؀ڥ
    42-
    ϑΝΠϧΛ
    μ΢ϯϩʔυ
    ϑΝΠϧ͔Βࣗ
    ෼ͷ1$ʹσʔλ
    Λొ࿥
    ෼ੳ݁Ռ
    w 1ZUIPOͳͲͷϓϩάϥϛϯάݴޠͰσʔλΛѻ͏ʹ͸೉қ౓͕ߴ͍
    w ΤΫηϧΛ࢖͏ͱѻ͑Δσʔλͷྔ͕ݶΒΕΔσʔλͷॲཧʹ͕͔͔࣌ؒΔ
    σʔλ෼ੳ؀ڥ͕੔͍ͬͯͳͯ͘΋ɺݩσʔλΛࣗ෼ͷ1$ʹೖΕΔ
    ͜ͱͰखݩͰ42-Λ࢖ͬͯσʔλ෼ੳ͕Ͱ͖Δ
    جװγεςϜ ݩσʔλʢDTWϑΝΠϧͳͲʣ ࣗ෼ͷ1$

    View Slide

  13. © )JLBSV5BLBIBTIJ
    ᶅֻ͚ࢉͷεΩϧͱͯ͠໾ཱͭ
    ࠓ42-ΛֶͿ΂͖ͭͷཧ༝
    42-
    ϚʔέςΟϯά σʔλΞφϦετ
    ܦӦاը Ӧۀ
    w &$αΠτͷߦಈ෼ੳ
    w ސ٬ͷߪങ෼ੳ
    w ޿ࠂ΍ΩϟϯϖʔϯͷޮՌݕূ
    w ೥౓ผച্
    w ࢛൒ظ͝ͱͷച্
    w ސ٬ηάϝϯτ෼ੳ
    w %8)͔ΒσʔλΛநग़
    w #*πʔϧ͔Βσʔλ઀ଓ
    w σʔλͷલॲཧ
    w ചΕے঎඼ͷ෼ੳ
    w ੒໿݅਺෼ੳ
    w ஍Ҭผ෼ੳ
    ༷ʑͳ৬छͰ࣮຿ͱ
    ͯ͠׆༻͠΍͍͢
    42-͸୭Ͱ΋࣮຿Ͱ࢖͍΍͍͢εΩϧͳͷͰͲΜͳ৬छͰ΋෯޿͘׆༻Ͱ͖Δ

    View Slide

  14. wࠓ42-ΛֶͿ΂͖ͭͷཧ༝
    ΞδΣϯμ
    "HFOEB
    wຊॻͷັྗ
    wσʔλ෼ੳͰ࢖͏42-ͷͭͷಛ௃
    w42-Λ࢖ͬͨσʔλ෼ੳͷσϞ

    View Slide

  15. <13>σʔλ෼ੳྗΛߴΊΔϏδωεύʔιϯͷͨΊͷ42-ೖ໳
    ઈࢍൃചத
    IUUQTXXXBNB[PODPKQEQ

    View Slide

  16. © )JLBSV5BLBIBTIJ
    w42-Λ͜Ε͔Βֶ΅͏ͱࢥ͍ͬͯΔਓ
    w࢓ࣄͰ42-Λ৮Γ࢝Ί͚ͨͲΠϚΠνΑ͘Θ͔Βͳ͍ਓ
    w&YDFMΛ࢖ͬͯσʔλूܭ͍ͯ͠Δਓ
    w΋ͬͱޮ཰తͳ؀ڥͰσʔλ෼ੳ͍ͨ͠Ϗδωεύʔιϯ
    ຊॻͷର৅ऀ
    ʰσʔλ෼ੳྗΛߴΊΔϏδωεύʔιϯͷͨΊͷ42-ೖ໳ʱͷັྗ

    View Slide

  17. © )JLBSV5BLBIBTIJ
    w42-͕શ͘Θ͔Βͳ͍ਓ͕ಡΜͰ΋σʔλ෼ੳͰ࢖͏جຊత
    ͳ42-͕͜ͷ࡭Ͱֶ΂Δ
    wΤϯδχΞͰ͸ͳ͘σʔλ෼ੳΛߦ͏Ϗδωεύʔιϯ޲͚
    wϏδωεॻͱͯ͠ಡΈ΍͍͢ϖʔδʹऩ·Δ಺༰
    wϋϯζΦϯܗࣜͰ࣮ફΛҙࣝͨ͠42-ֶ͕΂Δ
    ຊॻͷಛ௃
    ʰσʔλ෼ੳྗΛߴΊΔϏδωεύʔιϯͷͨΊͷ42-ೖ໳ʱͷັྗ

    View Slide

  18. © )JLBSV5BLBIBTIJ
    ຊॻͷಛ௃ɹଞͷຊͱͷൺֱ
    ʰσʔλ෼ੳྗΛߴΊΔϏδωεύʔιϯͷͨΊͷ42-ೖ໳ʱͷັྗ
    ϋϯζΦϯܗࣜͰޮ཰Α͘σʔλ෼ੳͰ࢖͏42-Λֶ΂Δͷ͕ຊॻͷಛ௃
    ຊॻ Ұൠతͳ42-ೖ໳ॻ σʔλ෼ੳ༻ͷ42-ຊ
    ର৅ऀ Ϗδωεύʔιϯ ΤϯδχΞ Ϗδωεύʔιϯ
    ຊͷ಺༰ σʔλ෼ੳ ΞϓϦέʔγϣϯ։ൃ σʔλ෼ੳ
    ຊͷಛ௃
    ϋϯζΦϯܗࣜͰ࣮຿ͷσʔλ෼
    ੳͰΑ͘࢖͏ߏจʹಛԽ
    ໢ཏతʹ42-ͷߏจΛઆ໌
    ϋϯζΦϯܗࣜʹͳ͍ͬͯͳ͍
    ໢ཏతʹ42-ͷߏจΛઆ໌
    ϖʔδ਺ ʙ ʙ

    View Slide

  19. © )JLBSV5BLBIBTIJ
    ຊॻͷಛ௃ɹϋϯζΦϯͰಡΈਐΊΔ
    ʰσʔλ෼ੳྗΛߴΊΔϏδωεύʔιϯͷͨΊͷ42-ೖ໳ʱͷັྗ
    w ࣗ෼ͷ1$Ͱ࣮ࡍʹ42-Λ࣮ߦͯ݁͠
    ՌΛ֬ೝ͠ͳ͕ΒಡΈਐΊΔ
    w ͲΜͳ42-Λ࣮ߦ͢ΔͱͲΜͳ݁Ռ͕
    ฦͬͯ͘Δͷ͔Λ֬ೝ͠ͳ͕ΒಡΈਐ
    ΊΔ͜ͱͰσʔλ෼ੳΛ࣮຿Ͱ࢖͍΍
    ͍͢εΩϧͱͯ͠਎ʹ͚ͭΔ͜ͱ͕Ͱ
    ͖Δ

    View Slide

  20. © )JLBSV5BLBIBTIJ
    ຊॻͷߏ੒
    ʰσʔλ෼ੳྗΛߴΊΔϏδωεύʔιϯͷͨΊͷ42-ೖ໳ʱͷັྗ
    ͳͥ42-͕ඞཁ͔ʁ
    42-ͱ͸Կ͔ʁ
    42-࣮ߦ؀ڥͷߏங
    ϋϯζΦϯͷ४උ
    42-ͷߏจ
    ཧղ
    w σʔλ෼ੳ͕ॏཁͳཧ༝
    w σʔλ෼ੳʹ͓͚Δ42-ͱ͸
    w 42-ͷجຊతͳ֓೦
    ୈষ ୈষ ୈʙষ
    w 42-JUFΛ࢖ͬͨσʔλ෼ੳ؀ڥ
    w 42-࣮ߦͷͨΊͷ؀ڥߏங
    w 42-࣮ߦπʔϧͷ࢖͍ํ
    4&-&$5-*.*503%&3#:ू
    ໿ؔ਺(3061#:%*45*/$5
    8)&3&)"7*/(+0*/6/*0/
    $"4&αϒΫΤϦ8*5)

    View Slide

  21. ຊॻͷັྗ
    4VNNBSZ
    42-Λֶͼ͍ͨͱࢥͬͨਓ͕࠷ॳʹखʹऔΔ࡭
    σʔλ෼ੳͰ࢖͏

    View Slide

  22. wࠓ42-ΛֶͿ΂͖ͭͷཧ༝
    ΞδΣϯμ
    "HFOEB
    wຊॻͷັྗ
    wσʔλ෼ੳͰ࢖͏42-ͷͭͷಛ௃
    w42-Λ࢖ͬͨσʔλ෼ੳͷσϞ

    View Slide

  23. © )JLBSV5BLBIBTIJ
    ຊॻͰѻ͏42-࣮ߦ؀ڥͷσϞ
    42-Λ࢖ͬͨσʔλ෼ੳͷσϞᶃ
    w42-JUFʢ%##SPXTFSGPS42-JUFʣΛ࢖ͬͨ42-ͷ࣮ߦ
    w&$αΠτͷߪങσʔλ෼ੳྫ
    VTFS@JE HFOEFS CJSUI
    " உੑ
    " ঁੑ
    ɾɾɾ ɾɾɾ ɾɾɾ
    QSPEVDU@JE QSPEVDU@OBNF QSJDF
    # ͓͍͍͠ਫ
    # γϟʔϓϖϯ
    ɾɾɾ ɾɾɾ ɾɾɾ
    PSEFS@JE VTFS@JE PSEFS@EBUF
    $ "
    $ "
    ɾɾɾ ɾɾɾ ɾɾɾ
    σʔλϕʔε ςʔϒϧ
    ސ
    ٬






    ߪ



    ຊॻͰѻ͍ͬͯΔ
    Սۭͷ&$αΠτͷσʔλ

    View Slide

  24. © )JLBSV5BLBIBTIJ
    ΦʔϓϯσʔλΛ׆༻ͨ͠σʔλ෼ੳ
    42-Λ࢖ͬͨσʔλ෼ੳͷσϞᶄ
    wΦʔϓϯσʔλΛ࢖ͬͨσʔλ
    ෼ੳͷσϞ
    wຊॻΛಡΜͰ42-Λཧղ͢Ε͹
    Φʔϓϯσʔλͷ෼ੳ΋Ͱ͖Δ
    ˞ްੜ࿑ಇল͕ग़͍ͯ͠Δ৽ܕίϩφ
    ΢Οϧεͷ೔ผཅੑऀਪҠ
    DTWϑΝΠϧͷμ΢ϯϩʔυ
    IUUQTXXXNIMXHPKQTUGDPWJEPQFOEBUBIUNM

    View Slide

  25. © )JLBSV5BLBIBTIJ
    w౦ژ౎ΦʔϓϯσʔλΧλϩάαΠτϗʔϜϖʔδ

    IUUQTQPSUBMEBUBNFUSPUPLZPMHKQ
    wτοϓcF(PWσʔλϙʔλϧ

    IUUQTEBUBFHPWHPKQJOGPKB
    ͦͷଞͷΦʔϓϯσʔλྫ
    42-Λ࢖ͬͨσʔλ෼ੳͷσϞ

    View Slide

  26. &/%

    View Slide