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

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

29fc9602e0ed9cca544d677266dadd68?s=47 hikarut
December 29, 2021

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

29fc9602e0ed9cca544d677266dadd68?s=128

hikarut

December 29, 2021
Tweet

Transcript

  1.  σʔλ෼ੳͷͨΊͷ42-ษڧձ ʙॳڃฤϋϯζΦϯʙ

  2.  w 42-ษڧձͷᶃର৅ऀᶄΰʔϧᶅਐΊํ w ͳͥ42-ΛֶͿͷ͔ʁ w 42-ͱ͸ʁ w 42-࣮ߦ؀ڥߏங w

    42-ߨ࠲ "HFOEB
  3.  w 42-ษڧձͷᶃର৅ऀᶄΰʔϧᶅਐΊํ w ͳͥ42-ΛֶͿͷ͔ʁ w 42-ͱ͸ʁ w 42-࣮ߦ؀ڥߏங w

    42-ߨ࠲ "HFOEB
  4.  ᶃ42-ษڧձͷର৅ऀ w42-Λ͜Ε͔Βֶͼ͍ͨਓ w࢓ࣄͰ42-Λ৮Γ࢝Ί͚ͨͲΠϚΠνΑ͘Θ͔Βͳ͍ਓ wσʔλूܭ΍σʔλ෼ੳΛΤΫηϧ࢖ͬͯ΍ͬͯΔਓ ˞ࠓճ͸ʮσʔλ෼ੳͷͨΊͷʯ42-ษڧձͰ͢ ˞ΞϓϦέʔγϣϯ։ൃͳͲͰ࢖͏42-͸૝ఆ͍ͯ͠ͳ͍ͷͰσʔλऔ ಘ 4&-&$5จ ͕த৺ʹͳ͍ͬͯ·͢

  5.  ᶄ42-ษڧձͷΰʔϧ w42-Λ࣮຿Ͱ࢖͑Δ༷ʹͳΔ w42-Λ࢖ͬͯཉ͍͠σʔλΛࣗ༝ʹऔΕΔ༷ʹͳΔࣄ w ௐ΂͔͚ͯΔ༷ʹ͢Δ w ଞͷਓ͕ॻ͍ͨ42- ࣗ෼͕աڈʹॻ͍ͨ42- Λࢀߟʹ42-ͷಡΈղ

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

    Ͱ42-Λ࣮ߦͯ݁͠Ռ͕Ͳ͏ͳΔ͔֬ೝ͠·͠ΐ͏ w ·ͣ͸جຊతͳ42-ͷઆ໌͠ɺͦͷޙࣗ෼Ͱ42-Λॻ͍͍ͯ͘ελΠϧ w ໰୊ͷղ౴ྫ΋Ұॹʹࡌ͍ͤͯ·͕͢ɺղ౴ΛΈΔલʹ·ͣ͸ࣗ෼Ͱߟ͑ ͯΈ·͠ΐ͏
  7.  w 42-ษڧձͷᶃର৅ऀᶄΰʔϧᶅਐΊํ w ͳͥ42-ΛֶͿͷ͔ʁ w 42-ͱ͸ʁ w 42-࣮ߦ؀ڥߏங w

    42-ߨ࠲ "HFOEB
  8. ͳͥ42-ΛֶͿͷ͔ʁ  w σʔλ׆༻͕Ϗδωε੒௕ʹ͓͍ͯඞਢͰ͋Δ w 42-͕࢖͑ΔσʔλΛࣗ༝ʹѻ͑Δ w σʔλ෼ੳʹ͓͍ͯ42-ͷ׆༻ͷػձ͕૿͍͑ͯΔ w #JH2VFSZ

    5SFBTVSF%BUB SFEBTI 3FETIJGUͳͲ w ΤΫηϧͰѻ͑Δσʔλʹݶք͕͋Δ w ΤΫηϧͷݶք͸ສߦ͘Β͍ ΤΫηϧͰ෼ੳ͢Δͱॏ͍ 42-͸σʔλ෼ੳͷ൚༻తͳεΩϧͰ͋Γɺ 42-͕࢖͑ΔͱେྔͷσʔλΛߴ଎ʹ෼ੳͰ͖Δ
  9.  w 42-ษڧձͷᶃର৅ऀᶄΰʔϧᶅਐΊํ w ͳͥ42-ΛֶͿͷ͔ʁ w 42-ͱ͸ʁ w 42-࣮ߦ؀ڥߏங w

    42-ߨ࠲ "HFOEB
  10.  w42-ͱ͸4USVDUVSFE2VFSZ-BOHVBHFͷུ wσʔλϕʔε্ͷςʔϒϧʹ֨ೲ͞ΕͨσʔλΛऔಘ͢Δ࣌ ʹ࢖͏ݴޠ 42-ͱ͸ʁ

  11.  wσʔλϕʔεͱ͸σʔλΛ֨ೲ͢Δେ͖ͳശ wςʔϒϧͱ͸σʔλϕʔε্ʹଘࡏ͢Δσʔλͷ͔ͨ·Γ wσʔλϕʔεʹ͋Δςʔϒϧ͔Β৘ใΛऔಘ͢Δํ๏͕42- Ϣʔβʔ αʔόʔ σʔλϕʔε %# 42- VTFS@JE

    TFY CJSUI " உੑ  " ঁੑ  " உੑ  ɾɾɾ ɾɾɾ ɾɾɾ ςʔϒϧ QSPEVDU@JE QSPEVDU@OBNF QSJDF # ͓͍͍͠ਫ  # γϟʔϓϖϯ  # ࿹࣌ܭ  ɾɾɾ ɾɾɾ ɾɾɾ 8&#αΠτ 42-ͱ͸ʁ
  12.  w σʔλϕʔεʹ͸.Z42-ɺ0SBDMFɺ1PTUHSF42-ɺ42-JUFɺ5FSBEBUBɺ )BEPPQ )%'4 ͳͲ༷ʑͳछྨ͕͋Δ w ͦΕͧΕͷ%#ʹରͯ͠σʔλΛऔಘ͢ΔͨΊͷํ๏͕42- 1SFTUP )JWFͳͲ΋͋Δ

    42-ͷछྨ
  13.  w 42-ษڧձͷᶃର৅ऀᶄΰʔϧᶅਐΊํ w ͳͥ42-ΛֶͿͷ͔ʁ w 42-ͱ͸ʁ w 42-࣮ߦ؀ڥߏங w

    42-ߨ࠲ "HFOEB
  14.  wࠓճͷߨٛͰ͸ʮ42-JUFʯΛ࢖͍·͢ w42-JUFͷಛ௃ wܰͯ͘଎͍ wαʔόʔ͕ͳͯ͘΋%#ΛߏஙͰ͖Δ ΞϓϦέʔγϣϯͱ ͯ͠૊ΈࠐΊΔ  wࣗ෼ͷ1$ʹ%#Λཱͯͯɺ42-Λ࣮ߦ͢Δ؀ڥ͕࡞ΕΔ w42-JUFͷ࣮ߦ؀ڥͱͯ͠ʮ%##SPXTFSGPS42-JUFʯΛ

    ࢖͍·͢ 42-࣮ߦ؀ڥߏங
  15.  IUUQTTRMJUFCSPXTFSPSHEM IUUQTXXXLLBOFLPKQEBUBTRMJUFECCSPXTFSIUNM IUUQTRJJUBDPNIJFTJFBJUFNTGDGDEF ʮ%##SPXTFSGPS42-JUFʯͷΠϯετʔϧ ࢀߟ 8JOEPXT .BD

  16.  ࠓճͷߨٛͰ͸Սۭͷ&$αΠτͷߪങσʔλΛࢀߟʹσʔλநग़Λߦ͍·͢ ΧϥϜ໊ ҙຯ ܕ උߟ 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ςʔϒϧ
  17.  ʮ%##SPXTFSGPS42-JUFʯΛىಈͯ͠σʔλϕʔεΛ࡞੒ᶃ

  18.  ʮ%##SPXTFSGPS42-JUFʯΛىಈͯ͠σʔλϕʔεΛ࡞੒ᶄ ˞σʔλϕʔε໊͸ԿͰ΋0,Ͱ͢

  19.  ʮ%##SPXTFSGPS42-JUFʯΛىಈͯ͠σʔλϕʔεΛ࡞੒ᶅ ˞ςʔϒϧ࡞੒͸Ωϟϯηϧ

  20.  DTWϑΝΠϧ͔ΒҎԼͭͷςʔϒϧ৘ใΛΞοϓϩʔυᶃ VTFSTςʔϒϧ QSPEVDUTςʔϒϧ PSEFS@EBUBςʔϒϧ

  21.  VTFSTςʔϒϧ QSPEVDUTςʔϒϧ PSEFS@EBUBςʔϒϧ DTWϑΝΠϧ͔ΒҎԼͭͷςʔϒϧ৘ใΛΞοϓϩʔυᶄ w VTFSTDTW w QSPEVDUTDTW w

    PSEFS@EBUBDTW ͷͭͷDTWϑΝΠϧΛΠϯϙʔτͯ͠ ςʔϒϧΛ࡞੒
  22.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

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

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

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

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

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

  28.  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ςʔϒϧ
  29.  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#ͱ͸จࣈྻ΍਺ࣈͰ͸ͳ͍೚ҙͷσʔλ ΛอଘͰ͖Δσʔλܕ ը૾ɺಈըɺ࣮ߦϑΝ ΠϧͳͲ
  30.  4&-&$5จɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ SELECT * FROM products

  31.  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
  32.  4&-&$5จͷ"4ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅ SELECT product_id AS '঎඼ID', name AS '঎඼໊' FROM

    products SELECT product_id AS id, name AS na FROM products
  33.  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-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅɹղઆ
  34.  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ςʔϒϧ
  35.  4&-&$5จɹղ౴ྫᶃ ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ ͔Βnameͱcategory1ͷσʔλΛऔಘ ͍ͯͩ͘͠͞ɻ·ͨname͸ʮ঎඼໊ʯɺcategory1͸ʮΧςΰϦ1ʯͱผ໊ Λ͚ͭͯग़ྗ͍ͯͩ͘͠͞ɻ SELECT name AS

    '঎඼໊', category1 AS 'ΧςΰϦ1' FROM products
  36.  4&-&$5จɹղ౴ྫᶃɹղઆ ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ ͔Βnameͱcategory1ͷσʔλΛऔಘ ͍ͯͩ͘͠͞ɻ·ͨname͸ʮ঎඼໊ʯɺcategory1͸ʮΧςΰϦ1ʯͱผ໊ Λ͚ͭͯग़ྗ͍ͯͩ͘͠͞ɻ SELECT name AS

    '঎඼໊', category1 AS 'ΧςΰϦ1' FROM products w ඞཁͳΧϥϜΛ4&-&$5จͰࢦఆ w "4Λ࢖ͬͯผ໊Ͱग़ྗ
  37.  4&-&$5จͷΠϝʔδ QSPEVDU@JE OBNF QSJDF DBUFHPSZ # ͓͍͍͠ਫ  ৯඼

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

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  39.  ू߹ؔ਺ɾूܭؔ਺ wσʔλͷ݅਺ɺ߹ܭɺฏۉͳͲΛूܭ͢Δࡍʹ࢖͏ؔ਺ wෳ਺͋ΔσʔλΛ୅ද஋ʹ·ͱΊΔํ๏ 46. ૯࿨ΛٻΊΔɻ/6--ͷ৔߹͸ूܭର৅֎ ."9 ࠷େ஋ΛٻΊΔ .*/ ࠷খ஋ΛٻΊΔ

    "7( ฏۉ஋ΛٻΊΔɻ/6--ͷ৔߹͸ूܭର৅֎ $06/5 ૯਺ΛٻΊΔɻ/6--ͷ৔߹͸ूܭର৅֎
  40.  ू߹ؔ਺ɾूܭؔ਺ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ SELECT COUNT(order_id) FROM order_data

  41.  ू߹ؔ਺ɾूܭؔ਺ɹ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ςʔϒϧ
  42.  ू߹ؔ਺ɾूܭؔ਺ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ SELECT COUNT(order_id) AS count_1, COUNT(*) AS count_2, COUNT(1)

    AS count_3 FROM order_data
  43.  ू߹ؔ਺ɾूܭؔ਺ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ SELECT COUNT(order_id) AS count_1, COUNT(*) AS count_2, COUNT(1)

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

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

    AVG(birth) AS avg_birth FROM users
  46.  ू߹ؔ਺ɾूܭؔ਺ɹղ౴ྫᶃɹղઆ usersςʔϒϧ͔Β஀ੜ೔ͷ࠷େ஋ɺ࠷খ஋ɺฏۉ஋Λग़ྗ͍ͯͩ͘͠͞ SELECT MAX(birth) AS max_birth, MIN(birth) AS min_birth,

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

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

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

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  50.  (3061#: ɾෳ਺ͷσʔλΛಛఆͷ੾ΓޱͰ·ͱΊΔ࣌ʹ࢖͏ߏจ ɾෳ਺ͷσʔλΛͭʹ·ͱΊΔࣄ͔Βू߹ؔ਺ $06/5 46.ͳͲ ͱҰॹʹ࢖͏͜ͱ͕ଟ͍

  51.  (3061#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ SELECT COUNT(*) FROM products SELECT category1, COUNT(*) FROM

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

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

    category1, category2
  54.  (3061#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ SELECT category1, category2, COUNT(*) FROM products GROUP BY

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

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

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

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

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  60.  %*45*/$5 wॏෳΛഉআ͍ͨ࣌͠ʹ࢖͏ wॏෳΛഉআͯ͠ෳ਺ϨίʔυΛϨίʔυʹ͢Δ

  61.  %*45*/$5ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ SELECT category1 FROM products SELECT DISTINCT category1 FROM

    products ΧςΰϦΛग़ྗ ॏෳഉআͯ͠ΧςΰϦΛग़ྗ
  62.  %*45*/$5ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ SELECT category1 FROM products SELECT DISTINCT category1 FROM

    products ΧςΰϦΛग़ྗ ॏෳഉআͯ͠ΧςΰϦΛग़ྗ 4&-&$5จͷதͰॏෳഉআ͍߲ͨ͠໨ͷલʹ%*45*/$5Λهड़͢Δ
  63.  %*45*/$5ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ SELECT COUNT(*), COUNT(category1), COUNT(DISTINCT category1) FROM products

  64.  %*45*/$5ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ SELECT COUNT(*), COUNT(category1), COUNT(DISTINCT category1) FROM products ू߹ؔ਺ͷதͰॏෳഉআ͍ͨ࣌͠ʹ

    Α͘࢖ΘΕΔ
  65.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

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

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

    WHERE order_date = '2020-01-01' શϨίʔυ਺ΛΧ΢ϯτ ৚݅ʹ֘౰͢ΔϨίʔυ਺ΛΧ΢ϯτ
  68.  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&Ͱࢦఆͨ͠৚݅ͷϨίʔυΛग़ྗͰ͖Δ
  69.  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'
  70.  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' ೥݄೔ΑΓ΋খ͍͞೔෇ͷϨίʔ υΛऔಘ ೥݄೔Ҏ֎ͷϨίʔυΛऔಘ
  71.  8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅ SELECT COUNT(*) FROM order_data WHERE order_date IS NULL

    SELECT COUNT(*) FROM order_data WHERE order_date IS NOT NULL
  72.  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--Ͱͳ͍ σʔλ͕ۭͰ ͳ͍ ϨίʔυΛऔಘ
  73.  8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆ SELECT COUNT(*) FROM order_data WHERE order_date >= '2020-01-01'

    AND order_date <= '2020-01-02'
  74.  8)&3&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆɹղઆ SELECT COUNT(*) FROM order_data WHERE order_date >= '2020-01-01'

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

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

    WHERE price <= 1000
  77.  8)&3&ɹղ౴ྫᶃɹղઆ ঎඼ςʔϒϧ QSPEVDUTςʔϒϧ ͔ΒՁ͕֨1000ԁҎԼͷ঎඼਺Λग़ྗ͠ ͍ͯͩ͘͞ SELECT COUNT(*) FROM products

    WHERE price <= 1000 ঎඼਺ͷΧ΢ϯτʹ͸ू߹ؔ਺ͷ ʮ$06/5ʯΛ࢖͏ ʮԁҎԼʯͷ৚݅ʹ8)&3&Ͱ ʮʯΛ࢖͏
  78.  8)&3&ɹิ଍ ຊ౰ʹσʔλ͕͋ͬͯΔ͔֬ೝ͢Δ৔߹ʹ$06/5Λ࢖ΘͣʹσʔλΛશ ͯநग़ͯ֬͠ೝ͢Δࣄ͕͋Γ·͢ ࣮ࡍͷσʔλ෼ੳͰ΋Α͘࢖͍·͢ SELECT * FROM products WHERE

    price <= 1000 σʔλͷ֬ೝͰʮ ʯΛ࢖ͬͯूܭલͷ σʔλΛ֬ೝ͢Δ
  79.  8)&3&ͷΠϝʔδ 8)&3&͸৚݅ʹҰகͨ͠ϨίʔυΛநग़͢ΔΠϝʔδ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF $ " #

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

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  81.  8)&3&Λ࢖ͬͨ৚݅ɹ#&58&&/ ɾ8)&3&ͷதͰ࢖͑Δ৚݅ࢦఆͷ΍Γํ ɾ೔෇΍਺ࣈͷಛఆͷൣғΛநग़͢Δ৔߹ʹ࢖͏

  82.  #&58&&/ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ SELECT * FROM products WHERE price <= 1000

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

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

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  85.  8)&3&Λ࢖ͬͨ৚݅ɹ-*,& w 8)&3&ͷதͰ࢖͑Δ৚݅ࢦఆͷ΍Γํ w จࣈྻΛݕࡧ͢Δ࣌ʹ࢖͏ w ݕࡧͷࡍʹ͸ϫΠϧυΧʔυจࣈΛ࢖ͬͯจࣈྻϚο νΛߦ͏ 

    จࣈҎ্ͷ೚ҙͷจࣈྻ @ ೚ҙͷจࣈ -*,&Ͱ࢖͑ΔϫΠϧυΧʔυจࣈ ΞϯμʔείΞ
  86.  -*,&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ SELECT * FROM products WHERE name LIKE '%ηοτ'

  87.  -*,&ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ SELECT * FROM products WHERE name LIKE '%ηοτ'

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

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

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  90.  8)&3&Λ࢖ͬͨ৚݅ɹ*/ w 8)&3&ͷதͰ࢖͑Δ৚݅ࢦఆͷ΍Γํ w ෳ਺ͷࢦఆͨ͠஋ʹ֘౰͢Δ͔Λ൑ผ͢Δ࣌ʹ࢖͏

  91.  */ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ SELECT DISTINCT category1 FROM products SELECT * FROM

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

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

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

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

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

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  97.  -*.*5 w ݁Ռͷग़ྗ݅਺Λࢦఆ͢Δ৚݅

  98.  -*.*5ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ SELECT * FROM products SELECT * FROM products

    LIMIT 10 શ݅औಘ ͚݅ͩऔಘ
  99.  -*.*5ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ SELECT * FROM products SELECT * FROM products

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

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  101.  03%&3#: w ݁ՌΛಛఆͷ৚݅Ͱฒ΂ସ͑Δ

  102.  03%&3#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ SELECT * FROM products SELECT * FROM products

    ORDER BY price શ݅औಘ ஋ஈ͕͍҆ॱʹฒͼସ͑
  103.  03%&3#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ SELECT * FROM products SELECT * FROM products

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

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

    category1 ASC
  106.  03%&3#:ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ SELECT * FROM products ORDER BY price DESC,

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

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  108.  +0*/ w ͭҎ্ͷςʔϒϧΛ݁߹ͤ͞Δ࣌ʹ࢖͏ w ݁߹ͤ͞ΔͨΊʹ͸ԿΛΩʔʹͯ݁͠߹ͤ͞Δ͔ࢦఆ͢Δඞཁ͕͋Δ w ݁߹ʹ͸େ͖͘෼͚ͯछྨ͋Δ ˞ݫີʹ͸छྨҎ্͋Δ ᶃ಺෦݁߹

    *//&3+0*/ ֎෦݁߹ ᶄࠨ֎෦݁߹ -&'5 065&3+0*/ ᶅӈ֎෦݁߹ 3*()5 065&3+0*/ ݁߹
  109.  ᶃ಺෦݁߹ *//&3+0*/ ᶄࠨ֎෦݁߹ -&'5 065&3+0*/ ᶅӈ֎෦݁߹ 3*()5 065&3+0*/ ྆ํͷςʔϒϧͷڞ௨෦෼ͷ

    ΈΛ݁߹ͯ͠औಘ͢Δ ࠨଆͷςʔϒϧΛશͯऔಘ͠ ͯ݁߹͢Δ ӈଆͷςʔϒϧΛશͯऔಘ͠ ͯ݁߹͢Δ छྨͷ+0*/ɹ֓ཁ
  110.  +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ͳͲͷ࿈൪Ͱผ໊Λ͚ͭΔ৔߹΋͋Δ͕ɺՄಡੑ্͕ ͕ΔΑ͏ʹ͚ͭͨํ͕ྑ͍ ݁߹৚݅
  111.  +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
  112.  +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
  113.  +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*/ʯͱলུͰ͖Δ
  114.  +0*/ ಺෦݁߹ ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄ SELECT COUNT(*) FROM order_data o INNER

    JOIN users u ON o.user_id = u.user_id
  115.  +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ͭ ͷςʔϒϧʹ྆ํೖ͍ͬͯΔσʔλΛऔಘ͍ͯ͠Δ͔Β
  116.  +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ςʔϒϧ
  117.  +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
  118.  +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 ಺ ෦ ݁ ߹ ֎ ෦ ݁ ߹
  119.  +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*/ ͱ֮͑ͯྑ͍
  120.  +0*/ ֎෦݁߹ ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆ SELECT COUNT(*) FROM order_data o LEFT

    JOIN users u ON o.user_id = u.user_id
  121.  +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ͷϨίʔυ਺ͱಉ݅͡਺ʹͳ͍ͬͯΔ
  122.  +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ςʔϒϧ ˓
  123.  +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 ֎ ෦ ݁ ߹ ಺ ෦ ݁ ߹
  124.  +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--ͱͯ͠ ݁߹͞ΕΔ
  125.  +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 ͷ৔ ߹͸ͲͪΒͷςʔϒϧʹ ΋ڞ௨͢ΔσʔλΛऔಘ ͢ΔͷͰ݁Ռͷ݅਺͸ม ΘΒͳ͍
  126.  +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 ͷ৔ ߹͸ͲͪΒͷςʔϒϧΛ ϕʔεʹ͢Δ͔Ͱ݅਺͕ มΘΔ ৔߹͕͋Δ
  127.  ͳͥ֎෦݁߹ͷ৔߹͸+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 ߪೖ৘ใͷग़ྗ ߪೖ͍ͯ͠ͳ͍Ϣʔβʔ৘ ใ͸Ͱͳ͍ Ϣʔβʔ৘ใͷग़ྗ Ϣʔβʔ৘ใ͕ͳ͍ߪೖ σʔλ͸ग़ͳ͍ߪೖ৘ใ ͷͳ͍Ϣʔβʔ৘ใ͕ग़Δ
  128.  ֎෦݁߹ͷࡍͷ஫ҙ఺ w ֎෦݁߹ͷ৔߹͸+0*/ͷॱ൪Ͱ݁Ռ͕มΘΔՄೳੑ͕͋ΔͷͰ஫ҙ͕ ඞཁ w ·ͣ͸ԿͷσʔλΛऔΓ͍͔ͨ֬ೝ͠ɺͦͷςʔϒϧΛઌʹऔಘ͢Δ w ߪೖσʔλ͕ཉ͚͠Ε͹ߪೖσʔλΛFROMͰઌʹࢦఆɺϢʔβʔσʔ λ͕ཉ͚͠Ε͹ϢʔβʔσʔλΛFROMͰઌʹࢦఆɺJOINʹ͸ͦΕҎ

    ֎ͷσʔλΛ෇ਵͤ͞Δ w ֎෦݁߹-&'5+0*/ͱ֮͑ͯྑ͍ 3*()5+0*/͸΄΅࢖Θͳ͍
  129.  +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ςʔϒϧ
  130.  +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'
  131.  +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 Λ঎඼*%ΛΩʔʹ݁߹ ೔෇Λ৚݅ࢦఆ ू߹ؔ਺Λ࢖ͬͯച্ͱ݅਺Λऔಘ
  132.  +0*/ɹղ౴ྫᶃɹิ଍ w ࠓճ͸-&'5+0*/ ֎෦݁߹ Ͱ΋*//&3+0*/ ಺෦݁߹ Ͱ΋݁Ռ͸ಉ͡Ͱ͢ w PSEFS@EBUBʹೖ͍ͬͯΔPSEFS@QSPEVDU@JE΋QSPEVDUTʹೖ͍ͬͯΔQSPEVDU@JE΋Ͳ

    ͪΒ΋ಉ͡΋ͷ͕ೖ͍ͬͯΔͷͰ Ͳ͔ͬͪʹ͔͠ೖͬͯͳ͍*%͕ͳ͍ͷͰ ݁Ռ͸ಉ͡ w ྫ͑͹ɺorder_dataʹ͸͋ͬͯɺproductsʹͳ͍঎඼ID͕ଘࡏͨ࣌͠(ͨ·ͨ·঎඼؅ཧΛ ͍ͯ͠ͳ͍σʔλ͕͋ͬͨ࣌)ɺINNER JOINͩͱ঎඼؅ཧ͞Ε͍ͯͳ͍σʔλ͕͸͔͡Εͯ ૯ച্΋ͦͷ෼ݮͬͯ͠·͏ͷͰ஫ҙ͕ඞཁ w ͜Ε΋঎඼؅ཧ͞Ε͍ͯͳ͍͔Βച্ͱͯ͠΋Χ΢ϯτ͠ͳ͍΄͏͕ྑ͍ͷ͔ɺച্ͱͯ͠ Χ΢ϯτͨ͠΄͏͕ྑ͍ͷ͔͸࣌ͱ৔߹ʹΑΔͷͰҰ֓ʹͲ͕ͬͪྑ͍ͱ͸ݴ͑ͳ͍
  133.  +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ςʔϒϧ
  134.  +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
  135.  +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.ͨ݁͠Ռ΋هೖͰ͖Δ
  136.  +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-- ɾɾɾ ɾɾɾ ɾɾɾ
  137.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  138.  )"7*/( w ಛఆͷ৚݅Λ͚ͭͯσʔλΛग़ྗ͍ͨ࣌͠ʹ࢖͏ߏจ w 8)&3&ͱಉ͡Α͏ͳ࢖͍ํΛ͢Δ w 8)&3&ͱͷҧ͍͸࣮ߦ͞ΕΔॱ൪ͷҧ͍

  139.  )"7*/(ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ ෮श SELECT category1, COUNT(*) FROM products GROUP BY

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

    5 GROUP BY category1 ݅਺͕݅Ҏ্ͷDBUFHPSZΛऔಘ SELECT category1, COUNT(*) FROM products GROUP BY category1 DBUFHPSZ͝ͱͷ঎඼݅਺Λऔಘ
  141.  )"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͝ͱͷ঎඼݅਺Λऔಘ
  142.  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 º
  143. SELECT category1, COUNT(*) FROM products GROUP BY category1 HAVING COUNT(*)

    >= 5  )"7*/(ɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅ '30. +0*/ 8)&3& (3061#: )"7*/( 4&-&$5 03%&3#: -*.*5 ᶃ ᶄ ᶅ ᶆ ᶇ ᶈ ᶉ ᶊ
  144.  )"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
  145.  )"7*/( w 42-ʹ͸࣮ߦ͞ΕΔॱ൪͕͋Δ w (3061#:ͨ݁͠Ռʹରͯ͠৚݅Λ௥Ճ͍ͨ͠ͱ͖ w ˠ)"7*/( w ͦΕҎ֎Ͱ৚݅Λࢦఆ͍ͨ͠ͱ͖(௨ৗΑ͘࢖͏৚݅ࢦఆ

     w ˠ8)&3&
  146.  )"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ςʔϒϧ
  147.  )"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
  148.  )"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*/
  149.  )"7*/(ͷΠϝʔδ )"7*/(͸৚݅ʹҰகͨ͠ϨίʔυΛநग़͢ΔΠϝʔδ 8)&3&ͱಉ͡Πϝʔδ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF $ "

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

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

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

    ϔϏʔ ϛυϧ ϥΠτ ೥ؒߪೖֹ͕ۚ=  Ҏ্ ೥ؒߪೖֹ͕ۚ= ʙ=  Ҏ্ ೥ؒߪೖֹ͕ۚ= ҎԼ
  153.  $"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 ·ͣ͸Ϣʔβʔ͝ͱͷߪೖֹۚΛग़͢
  154.  $"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*/ Ϣʔβʔ*%͕ۭͷσʔλ͸আ֎
  155. 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-Λ࣮ߦͯ͠ΈΑ͏ᶄ
  156. 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&ࣜΛ࢖ͬͯߪೖֹۚ͝ͱʹϑϥά͚ͭ
  157.  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-Λ࣮ߦͯ͠ΈΑ͏ᶄɹղઆ
  158.  $"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 ͸ෆཁ
  159.  $"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 º ˞৚݅ͷॱ൪Λม͑ΔͱϔϏʔ͕֘౰ͳ͠ʹͳͬͯ͠·͏
  160.  $"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
  161.  $"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
  162.  $"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 ʹͯ͠ɺ ύλʔϯʹ෼ྨ
  163.  $"4&ͷΠϝʔδ $"4&͸৽͘͠ྻΛ௥Ճ͢Δͱ͖ʹ࢖͏Πϝʔδ ˞4&-&$5ͷதͰ$"4&ࣜΛ࢖͏৔߹ PSEFS@JE VTFS@JE PSEFS@QSPEVDU@JE PSEFS@EBUF $ "

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

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  165.  αϒΫΤϦͱ͸ w 42-ͷதʹ͞Βʹ42-Λॻ͘͜ͱ͕Ͱ͖Δػೳ

  166.  αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ ෮श Ϣʔβʔͷੑผ͕ʮஉੑʯ͚ͩͷ஫จ*%Λऔಘ͍ͨ͠ SELECT o.order_id, u.sex FROM order_data o

    LEFT JOIN users u ON o.user_id = u.user_id WHERE u.sex = 'உੑ'
  167.  Ϣʔβʔͷੑผ͕ʮஉੑʯ͚ͩͷ஫จ*%Λऔಘ͍ͨ͠ 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-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃ ෮श ɹղઆ
  168.  Ϣʔβʔͷੑผ͕ʮஉੑʯ͚ͩͷ஫จ*%Λऔಘ͍ͨ͠ SELECT order_id FROM order_data WHERE user_id IN (SELECT

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

    DISTINCT user_id FROM users WHERE sex = 'உੑ') 8)&3&ͷ৚݅ʹ4&-&$5จΛ࢖͏ࣄ͕Ͱ͖Δ ˞+0*/Λ࢖͏ͱݩͷςʔϒϧͷϨίʔυ਺͕૿ݮ͢ΔՄೳੑ͕͋ΔͷͰɺݩͷςʔϒϧͷϨίʔυ਺Λ୲อͭͭ͠৚݅ࢦఆ ͍ͨ͠৔߹ʹ͸ศར αϒΫΤϦɹ42-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶄɹղઆ
  170.  ߪೖֹۚͰϔϏʔɺϛυϧɺϥΠτΛ෼͚ͨͱ͖ʹɺϔϏʔɺϛυϧɺϥΠ τͷϢʔβʔ͕ͦΕͧΕԿਓ͍Δͷ͔ूܭ͍ͨ͠ 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-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶅ ෮श
  171.  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-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆ
  172.  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-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆɹղઆ
  173.  ͜ͷͭͷ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-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶆɹղઆ
  174.  αϒΫΤϦͱ͸ w 42-ͷதʹ͞Βʹ42-Λॻ͘͜ͱ͕Ͱ͖Δػೳ w '30.ͷத΍8)&3&ͷ৚݅ͱͯ͠࢖͏ࣄ͕ଟ͍

  175.  αϒΫΤϦɹ໰୊ᶃ ҎԼ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&ࣜΛ࢖ͬͯ).-ʹ෼ྨ
  176.  αϒΫΤϦɹղ౴ྫᶃ ҎԼ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
  177.  αϒΫΤϦɹղ౴ྫᶃɹղઆ ҎԼ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Ͱ໊લ Λ͚ͭΔඞཁ͕͋Γ·͢
  178.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  179.  8*5)۟ͱ͸ w 42-্ͰҰ࣍ςʔϒϧΛ࡞੒Ͱ͖Δॻ͖ํ w '30.ͷதͰαϒΫΤϦΛ࢖͏৔߹ʹ8*5)۟Λ࢖͏ͱՄ ಡੑ্͕͕Δ

  180.  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-͕ݟͮΒ͍
  181.  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 ϔϏʔɺϛυϧɺϥΠτͷϢʔβʔ਺ूܭ
  182.  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-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
  183.  8*5)۟ͷߏจ WITH ςʔϒϧ໊1 AS( ), ςʔϒϧ໊2 AS( ) SELECT

    * FROM ςʔϒϧ໊1 w ΧϯϚ۠੾ΓͰෳ਺ͷҰ࣍ςʔϒϧ Λ࡞Δࣄ͕Ͱ͖Δ w 42-্ͰҰ࣍ςʔϒϧΛ࡞Δࣄ͕Ͱ ͖Δ ࣮ࡍʹςʔϒϧ͕࡞੒͞ΕΔ Θ͚Ͱ͸ͳ͍ͷͰ42-ͷதͰ͔͠࢖ ͑ͳ͍  w 8*5)۟Λ࢖͏ͱෳࡶͳΫΤϦ΋Մ ಡੑΛߴΊΔࣄ͕Ͱ͖Δ αϒΫΤϦΛ࢖͏৔߹ʹ͸8*5)۟Λ࢖ͬͨํ͕Մಡੑ͕ߴ· ΔͷͰੵۃతʹ8*5)۟Λ࢖͏͜ͱΛ͓קΊ͠·͢
  184.  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)۟Λ࢖ͬͯॻ͖௚͠
  185.  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)
  186.  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)۟Λ࢖ͬͨํ͕Մಡੑ͕ߴ· Δ ྫ͑͹உੑͰɺࡀҎ্Ͱɺ࡟আ͞Εͯͳ͍*%ͳͲ৚͕݅૿͑ͨ৔߹
  187.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

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

    ʯΛ݁߹͢Δ΍ΓํͰɺ 6/*0/ 6/*0/"-- ʹΑΔ݁߹͸ςʔϒϧͷʮߦ ԣ ʯΛ݁߹͢Δ ΍Γํ +0*/ 6/*0/ 6/*0/"--
  189.  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
  190.  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
  191.  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-Λ࣮ߦͯ͠Έ·͠ΐ͏ᶃɹղઆ
  192.  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 $ " # $ " # $ " # $ " # ɾɾɾ ɾɾɾ ɾɾɾ
  193.  42-ߨ࠲ 4&-&$5 ू߹ؔ਺ (3061#: %*45*/$5 8)&3& #&58&&/ -*,& */

    -*.*5 03%&3#: +0*/ )"7*/( $"4& αϒΫΤϦ 8*5) 6/*0/ 6/*0/"--  ͦͷଞ5JQT
  194.  5JQTᶃ 42-Λॻ࣌͘͸ՄಡੑΛߴΊΔͨΊʹΠϯσϯτʹؾΛ͚ͭ·͠ΐ͏ SELECT product_id, name FROM products SELECT product_id,

    name FROM products ˓ ˚
  195.  5JQTᶄ ίϝϯτΛ༗ޮʹ࢖͍·͠ΐ͏ -- ঎඼σʔλͷऔಘ SELECT product_id, name FROM products

    /* ঎඼σʔλͷऔಘ (঎඼IDͱ໊લ) */ SELECT product_id, name FROM products ʮʯΛ࢖ͬͯߦίϝϯτΞ΢τ͕Ͱ͖·͢ ʮ  ʯΛ࢖͏ͱෳ਺ߦίϝϯτΞ΢τ͕Ͱ͖·͢ ෳࡶͳ42-ʹίϝϯτΛ͚ͭͯՄಡੑΛ͋͛ͨΓɺτϥΠΞϯυΤϥʔͰ Ұ࣌తʹίϝϯτΞ΢τͨ͠Γ͢ΔͱศརͰ͢
  196.  5JQTᶅ 42-ͷʮ༧໿ޠʯ͸େจࣈͰॻ͘श׳͕͋Γ·͢ ࣮ࡍʹࣗ෼Ͱॻ࣌͘͸͋·Γҙࣝ͠ͳͯ͘΋ྑ͍ͱࢥ͍·͢ɻ ˞༧໿ޠ͸"4ͳͲͷผ໊Ͱ࢖͑ͳ͍ͷͰ஫ҙ͕ඞཁͰ͢ SELECT product_id, name FROM products

    select product_id, name from products ˓ ˓ SELECT product_id, name as select FROM products º
  197.  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 จࣈྻΛ੾ΓऔΔ ɾ ɾ ɾ ˞ࠓճ͸৭ʑͳؔ਺΋͋Δͱ͍͏ཧղ͚ͩͰ͖Ε͹ྑ͍Ͱ͢
  198.  &/%