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

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

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