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

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-ͱ͸4USVDUVSFE
    2VFSZ
    -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ͷ࣮ߦ؀ڥͱͯ͠ʮ%#
    #SPXTFS
    GPS
    42-JUFʯΛ ࢖͍·͢ 42-࣮ߦ؀ڥߏங
15.  IUUQTTRMJUFCSPXTFSPSHEM IUUQTXXXLLBOFLPKQEBUBTRMJUFECCSPXTFSIUNM IUUQTRJJUBDPNIJFTJFBJUFNTGDGDEF ʮ%#
    #SPXTFS
    GPS
    42-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.  ʮ%#
    #SPXTFS
    GPS
    42-JUFʯΛىಈͯ͠σʔλϕʔεΛ࡞੒ᶃ
18.  ʮ%#
    #SPXTFS
    GPS
    42-JUFʯΛىಈͯ͠σʔλϕʔεΛ࡞੒ᶄ ˞σʔλϕʔε໊͸ԿͰ΋0,Ͱ͢
19.  ʮ%#
    #SPXTFS
    GPS
    42-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 -&'5
     065&3
     +0*/͸֎෦݁߹ͷͭ
     w 065&3͸লུͯ͠-&'5
     +0*/ͱॻ͘ࣄ͕Ͱ͖Δ
     w -&'5
     
     '30.ͷ௚ޙʹॻ͍ͨςʔϒϧɺ3*()5
     
     +0*/ͷޙʹॻ͍ ͨςʔϒϧ -&'5 3*()5 ˞42-JUFͷ৔߹͸3*()5
     065&3
     +0*/͸αϙʔτ͞Ε͍ͯͳ͍ͷͰΤϥʔʹͳΔ
     ˞࣮ࡍ3*()5
     065&3
     +0*/Λ࢖͏έʔε͸΄ͱΜͲͳ͍ͷͰɺ֎෦݁߹-&'5
     065&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 ಺෦݁߹ JOOFS
     KPJO ͷ৔ ߹͸ͲͪΒͷςʔϒϧʹ ΋ڞ௨͢ΔσʔλΛऔಘ ͢ΔͷͰ݁Ռͷ݅਺͸ม ΘΒͳ͍
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 ֎෦݁߹ MFGU
     KPJO ͷ৔ ߹͸ͲͪΒͷςʔϒϧΛ ϕʔεʹ͢Δ͔Ͱ݅਺͕ มΘΔ ৔߹͕͋Δ
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.  &/%