
ãSQL zooè§£çâ¢ã1 SELECT from WORLD Tutorial
ã¯ããã«
ããã«ã¡ã¯ãã³ããªã§ãã
ããŠã次ã¯ãSELECT from WORLDãã§ãã
ååã®èšäºã§ã¯ã0 SELECT basics QUIZãã解説ããŠããã®ã§ããŸã ã®æ¹ã¯ãã¡ããããé¡ãããŸãïŒ
ããŠãããããå§ããŸãã
ãSELECT from WORLDããéããŠãã ããã
ãã®äžã«ããªã³ã¯ã貌ã£ãŠãããŸãã
SELECT from WORLD Tutorial - SQLZoo
解説ã§ã¯ä»¥äžã®æ§æã§è¡ããŸãã
å顿ïŒè±èªïŒ
å顿ïŒåèš³ïŒ
ãã€ã³ã解説
æ£è§£
ä»å䜿çšããSQLã³ãã³ã
ä»åã¯ä»¥äžã®ããã°ã©ã ãæ°åºãããã¯éèŠã§ãã
LIKEïŒname LIKE 'Al%' ã®ããã«ãç¹å®ã®æååãå«ãã»å§ãŸãã»çµããããŒã¿ãæœåºããŸãã(6çª)
length()ïŒæåæ°ãã«ãŠã³ãããããã°ã©ã ã§ãã
XORïŒæä»çè«çåã䜿ããŸãã(8çª)
ROUND(æ°å€ãæ¡æ°)ïŒæå®ã®æ¡æ°ã§åæšäºå ¥ããŸã(9,10çª)
LENGTH(ã«ã©ã å)ïŒæååã®æåæ°ãæ°ããŸã(11çª)
LEFT(åå,æ°å)ïŒååã®æååã®å·Šãããæåç®ãæœåºããŸã(12çª)
<>ïŒäžèŽããªã(â )ã(12çª)
NOT LIKE '%X%'ïŒXãå«ãŸãªãããããæŽ»çšããŠãã¹ããŒã¹ãå«ãŸãªããæœåºããŸãã(13çª)
1 Introduction
åèš³ïŒããã°ã©ã ãå®è¡ããŠããã¹ãŠã®åœã®åç§°ã倧éžã人å£ã衚瀺ããçµæã確èªããŠãã ãã
æåãªã®ã§ãæžããŠããããã°ã©ã ã®åäœç¢ºèªã§ãã
submit SQLãæŒããŠã仿žãããŠããããã°ã©ã ãå®è¡ããå Žåã®çµæã確èªããŸãã
whereæã§æ¡ä»¶ãæå®ããŠããªãã®ã§æå®ããåã®å€å šéšã衚瀺ãããŸãã
ãæ£è§£ã
SELECT name, continent, population FROM world2 Large Countries(倧ããåœ)
åèš³ïŒäººå£2å人以äžã®åœã®ãåœåã衚瀺ããŠãã ãã
whereã§æ¡ä»¶ãæå®ããŸãã
ä»åã¯äººå£(population)ã2å以äžã§ãã
ãåçäŸã
SELECT name FROM world
WHERE population >= 2000000003 Per capita GDP(1人åœããGDP)
åèš³ïŒäººå£2å人以äžã®åœã®ãåœåãš1人åœããGDPã衚瀺ããŠãã ãã
â¡ã«è¿œå ããŠãæ°ãã«ã1人åœããGDPãã衚瀺ããå¿ èŠããããŸãã
1人åœããGDPã¯GDP÷人å£ã§ãã®ã§ãgdp/populationãšè¡šããŸããããšã®è¡šã«1人åœããGDPãšããåã¯ãããŸããããSQLã§ã¯ãã®ããã«èšç®ã§æ°ããåãäœãããšãã§ããŸãâ
ãåçäŸã
select name,gdp/population from world
WHERE population >= 2000000004 South America In millions(åã¢ã¡ãªã«ã®äººå£)
åèš³ïŒåã¢ã¡ãªã«å€§éžã®ååœã«ã€ããŠãåœåãšäººå£ïŒåäœïŒçŸäžïŒã衚瀺ããŠãã ããã
â¢ã§èšç®ã§æ°ããåãäœãããšãã§ããããšãåŠã³ãŸããã
ä»åã¯äººå£ãçŸäžäººåäœã§è¡šç€ºãããã®ã§ã人å£Ã·1000000(population/1000000 )ããã°OKã§ããwhereã§ã¯å€§éžå(åã¢ã¡ãªã«)ãæå®ããŸãã倧éžåã¯æååãªã®ã§ã·ã³ã°ã«ã¯ãªãŒããŒã·ã§ã³( ' )ã§å²ãããšãå¿ããªãããã«ããŸããã
ãåçäŸã
select name,population/1000000 from world
where continent in ('South America')5 France, Germany, Italy(ãã©ã³ã¹ããã€ããã€ã¿ãªã¢)
åèš³ïŒä»¥äžã®è¡šã«ããã°ã©ã ãå®è¡ããçµæãéžæããŠãã ãã
ããããã«ããŠã¯ãŸããšããå Žåãin ()ã䜿ã£ãŠè¡šãããšãã§ããŸãã
ãåçäŸã
select name,population from world
where name in ('France','Germany','Italy')6 United
åèš³ïŒåœåã«ããŠãã€ãããããå«ãåœãæœåºããŠãã ãã
Unitedãâå«ãâãªã®ã§ã%%ã§å²ã¿ãŸã
ãåçäŸã
select name from world
where name like '%United%'7 Two ways to be bigã倧ããåœã
åèš³ïŒã倧ããåœãã«ãªãã«ã¯ãâ é¢ç©ã3çŸäžä»¥äžãããã¯â¡äººå£ã2åïŒåäžäººä»¥äžã§ããã°ããã§ããã倧ããåœãã®åœåã人å£ãé¢ç©ã衚瀺ããŠãã ãã
2ã€ã®æ¡ä»¶ã or ã§ç¹ããã°OKã§ãã
ãåçäŸã
select name,population,area from world
where (area >= 3000000) or (population >= 250000000)SELECT name, population/area
 FROM world
 WHERE name IN ('China', 'Nigeria', 'France', 'Australia')8 One or the other (but not both)
åèš³ïŒæä»çè«çåïŒXORïŒãé¢ç©ã倧ããïŒ300äžè¶ ïŒãã人å£ãå€ãïŒ2å5000äžè¶ ïŒãã®ããããäžæ¹ã«ã®ã¿è©²åœããåœã®åœåã人å£ãé¢ç©ã衚瀺ããŠãã ããã
XOR(æä»çè«çå)ãšã¯ã2ã€ã®æ¡ä»¶ã®ã©ã¡ããã«ã ãåœãŠã¯ãŸãå Žåãšããæå³ã§ããäž¡æ¹ãšãã«åœãŠã¯ãŸããšãã¯é€å€ããå¿ èŠããããŸãã
SQLã§ã¯ãã®ãŸãŸxorã䜿ããã®ã§ã7çªã®åé¡ã® or ã®éšåã xor ã«å€æŽããã°OKã§ãã
ãåçäŸã
select name,population, area from world
where (area >= 3000000) xor (population >= 250000000)9 Rounding(åæšäºå ¥)
åèš³ïŒåã¢ã¡ãªã«å€§éžã®åœåã人å£(åäœïŒçŸäžäºº)ãGDP(åäœïŒ10åãã«)ã衚瀺ããŠãã ãããROUND颿°ã䜿çšããŠå°æ°ç¬¬2äœãŸã§è¡šç€ºããŠãã ããã
ROUND颿°ã䜿ããŸããExcelã§äœ¿ã£ãããšãããæ¹ã¯äœãšãªãã€ã¡ãŒãžããããããããŸããããåæšäºå ¥ãã§ããŸãã
ROUND(æ°å€ãæ¡æ°)
第ïŒåŒæ°ïŒåæšäºå ¥ãããæ°å€
第2åŒæ°ïŒå°æ°ç¹ä»¥äžäœæ¡ãŸã§æ®ãã
ROUND() as 'XX'ïŒROUND颿°ã®çµæåã®ååãXXã衚瀺ããã
ãåçäŸã
SELECT name
,ROUND(population / 1000000, 2) AS 'population in millions'
,ROUND(gdp / 1000000000, 2) AS 'gdp in billions'
FROM world
WHERE continent = 'South America'10 Trillion dollar economies(1å ãã«å)
åèš³ïŒGDPã1å 以äžã®åœã«ã€ããŠãåœã®åœåãš1人åœããGDPã衚瀺ããŠãã ããã1人åœããGDPã¯åã®äœã§åæšäºå ¥ããŠãã ãã
ROUND颿°ã®å°ãå¿çšçãªäœ¿ãæ¹ã§ããåã®äœã§åæšäºå ¥ããã®ã§-3ã§OKã§ãã
ãåçäŸã
select name,round((gdp/population),-3) as 'per-capita GDP'
from world
where gdp >= 100000000000011 Name and capital have the same length(åœåãšéŠéœãåãæåæ°)
åèš³ïŒåœåãšéŠéœã®æåæ°ãåãåœã®ãåœåãšéŠéœã衚瀺ããŠãã ããã
æåæ°ã¯LENGTH颿°ã䜿ããŸãã
LENGTH(ã«ã©ã å)ïŒæååã®æåæ°ãæ°ãã
ãåçäŸã
SELECT name,capital
 FROM world
WHERE LENGTH(name)=LENGTH(capital)12 Matching name and capitalã倧ããåœã
åèš³ïŒåœåãšéŠéœã®é æåãåãåœã®ãåœåãšéŠéœåã衚瀺ããŠãã ããããã ããåœåãšéŠéœãåãåèªã§ããå Žåã¯é€å€ããŠãã ããã
LEFT颿°ã§ãå·Šãããæåç®ãéžæã§ããŸã
LEFT(åå,æ°å)ïŒååã®æååã®å·Šãããæåç®ãæœåº
<>ïŒäžèŽããªã(â )ã
ãåçäŸã
SELECT name,capital
FROM world
where
LEFT(name,1) = left(capital,1)
 and
name <> capital13 All the vowels(ãã¹ãŠã®æ¯é³)
åèš³ïŒaiueoãã¹ãŠãå«ã¿ããã€ãåœåã«ã¹ããŒã¹ãå«ãŸãªãåœã®ãåœåã衚瀺ããŠãã ããã
è€æ°ã®æ¡ä»¶ã䜿ããšã㯠AND ã§ã€ãªããŸãã
NOT LIKE '%X%'ïŒXãå«ãŸãªãããããæŽ»çšããŠãã¹ããŒã¹ãå«ãŸãªããã§ããŸãã
ãåçäŸã
SELECT name FROM world
WHERE
name LIKE '%a%'
AND name LIKE '%i%'
AND name LIKE '%u%'
AND name LIKE '%e%'
AND name LIKE '%o%'
AND name NOT LIKE '% %'ãããã«
ã©ãã ã£ãã§ããããïŒ
å°ããã€é£ãããªã£ãŠããŸãããã
å°ããã€ãåãããªããŠãããŸãæ°ã«ããé²ããŠãããŸãããïŒ
質åç®±
ããããªããšããããã£ããæ¹åç¹ããã£ããããããªãã§ãæããŠãã ããïŒ
ãããããé¡ãããããŸãã