sqlzoo_answer

SELECT from WORLD Tutorial

-- 1
SELECT name, continent, population FROM world;

--2
SELECT name FROM world
WHERE population>200000000;

--3
SELECT name, GDP/population
FROM world
WHERE population>200000000;

--4
SELECT name, population/1000000
FROM world
WHERE continent='South America';

--5
SELECT name, population
FROM world
WHERE name IN ('France', 'Germany', 'Italy');

--6
SELECT name
FROM world
WHERE name LIKE '%United%';

--7
SELECT name,population,area
FROM world
WHERE area>3000000 or population>250000000;

--8
SELECT name,population,area
FROM world
WHERE (name NOT IN ('USA', 'India', 'China')) 
  AND ((area>3000000 AND population<=250000000) 
  OR (area<=3000000 AND population>250000000));
  
 --9 小数位
SELECT name, ROUND(population/1000000, 2), ROUND(GDP/1000000000,2)
FROM world
WHERE continent='South America';

--10 四舍五入最近的1000（先除以1000，取整，再乘以1000）
SELECT name,ROUND(GDP/population/1000,0)*1000
FROM world
WHERE GDP>=1000000000000;

--11 当州名为Caribbean和Oceania时进行替换
SELECT name,
       CASE WHEN continent='Caribbean' THEN 'North America'
            WHEN continent='Oceania' THEN 'Australasia'
            ELSE continent END
FROM world
WHERE name LIKE 'N%'

--12
SELECT name,
  CASE WHEN continent IN ('Europe','Asia') THEN 'Eurasia'
    WHEN continent IN ('North America','South America','Caribbean') THEN 'America'
    ELSE continent END
FROM world
WHERE name LIKE 'A%' or name LIKE 'B%';

--13
SELECT name,continent,
  CASE WHEN continent='Oceania' THEN 'Australasia'
    WHEN continent IN ('Eurasia','Turkey') THEN 'Europe/Asia'
    WHEN continent='Caribbean' AND name LIKE 'B%' THEN 'North America'
    WHEN continent='Caribbean' AND name NOT LIKE 'B%' THEN 'South America'
    ELSE continent END
FROM world;

SELECT from Nobel Tutorial

--1
SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950;

--2
SELECT winner
FROM nobel
WHERE yr = 1962
AND subject = 'Literature';
 
--3
SELECT yr,subject
FROM nobel
WHERE winner='Albert Einstein'

--4
SELECT winner
FROM nobel
WHERE subject='Peace' AND yr>=2000;

--5
SELECT *
FROM nobel
WHERE subject='Literature' AND yr BETWEEN 1980 AND 1989;

--6
SELECT * FROM nobel
WHERE winner IN ('Theodore Roosevelt','Woodrow Wilson','Jimmy Carter','Barack Obama');

--7
SELECT winner
FROM nobel
WHERE winner LIKE 'John%';

--8
SELECT yr, subject, winner
FROM nobel
WHERE (subject='Physics' AND yr='1980')
  OR (subject='Chemistry' AND yr='1984'); 
  
--9
SELECT yr, subject, winner
FROM nobel
WHERE yr=1980 AND subject NOT IN ('Chemistry','Medicine');

--10
SELECT yr,subject,winner
FROM nobel
WHERE (subject='Medicine' AND yr<1910) OR (subject='Literature' AND yr>=2004);

--11 非ASCII码字符
SELECT *
FROM nobel
WHERE winner='PETER GRÜNBERG'; -- 直接复制来的名字

或者
--TODO

--12 用两个单引号代替一个单引号
SELECT *
FROM nobel
WHERE winner='EUGENE O''NEILL';
 
--13 降序
SELECT winner,yr,subject
FROM nobel
WHERE winner LIKE 'Sir%'
ORDER BY yr DESC, winner

--14 这题核对了网站底部的讲解视频，结果是正确的，但网站不反馈正确
SELECT winner, subject
FROM nobel
WHERE yr=1984
  ORDER BY 
    CASE WHEN subject IN ('Chemistry','Physics') THEN 1 ELSE 0 END,subject,winner

SELECT within SELECT Tutorial

--1
SELECT name FROM world
  WHERE population >
    (SELECT population FROM world
     WHERE name='Russia')
     
--2
SELECT name
FROM world
WHERE continent='Europe' 
  AND gdp/population > 
    (SELECT gdp/population FROM world
     WHERE name='United Kingdom');
     
--3
SELECT name,continent
FROM world
WHERE continent IN 
  (SELECT continent FROM world
   WHERE name IN ('Argentina','Australia'))
ORDER BY name;

--4
SELECT name,population
FROM world
WHERE population >
  (SELECT population FROM world WHERE name='Canada')
   AND population <
  (SELECT population FROM world WHERE name='Poland'); 
  
--5 避免浮点型ROUND截取后自动填充，应用CAST将其改为 int 类型再 CONCAT
SELECT name,
  CONCAT(CAST (ROUND(population*100/
    (SELECT population FROM world WHERE name='Germany'),0)AS int),'%')
FROM world
WHERE continent='Europe';

--6 注意GDP大于所有欧洲国家（显然就不包括欧洲国家了）
SELECT name
FROM world 
WHERE continent<>'Europe' AND gdp>=ALL(SELECT gdp
                											FROM world
 											                WHERE continent='Europe' AND gdp>0);
                                      
--7 联结表与子查询的结合
SELECT continent, name, area FROM world x
  WHERE area>= ALL
    (SELECT area FROM world y
        WHERE y.continent=x.continent
          AND area>0)
          
--8 联结表与子查询
SELECT continent,name
FROM world x
WHERE name <= ALL 
  (SELECT name FROM world y
     WHERE x.continent = y.continent)
ORDER BY name;

--9
SELECT name,continent,population 
FROM world x
WHERE 25000000>= ALL(
  SELECT population FROM world y
    WHERE x.continent=y.continent);

--10
SELECT name,continent
FROM world x
WHERE population > ALL(
  SELECT 3*population FROM world y
    WHERE x.continent=y.continent AND x.population<>y.population);

Nested SELECT Quiz

--1
SELECT region, name, population FROM bbc x WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population>0);

--2
SELECT name,region,population FROM bbc x WHERE 50000 < ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0);
 
 --3
SELECT name, region FROM bbc x
WHERE population < ALL (SELECT population/3 FROM bbc y WHERE y.region = x.region AND y.name != x.name);

--5
SELECT name FROM bbc
WHERE gdp > (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa')

--6
SELECT name FROM bbc
 WHERE population < (SELECT population FROM bbc WHERE name='Russia')
   AND population > (SELECT population FROM bbc WHERE name='Denmark')

SUM and COUNT

--1
SELECT SUM(population)
FROM world;

--2
SELECT DISTINCT continent
FROM world;

--3
SELECT SUM(GDP)
FROM world
WHERE continent='Africa';

--4
SELECT COUNT(*)
FROM world
WHERE area>=1000000;

--5
SELECT SUM(population)
FROM world
WHERE name IN ('Estonia', 'Latvia', 'Lithuania');

--6
SELECT continent,COUNT(name)
FROM world
GROUP BY continent;

--7
SELECT continent,COUNT(name)
FROM world
WHERE population>= 10000000
GROUP BY continent;

--8
SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population)>=100000000;

SUM and COUNT QUIZ

--1
SELECT SUM(population) FROM bbc WHERE region = 'Europe'

--2
SELECT COUNT(name) FROM bbc WHERE population < 150000

--3
AVG(), COUNT(), MAX(), MIN(), SUM()

--4
No result due to invalid use of the WHERE function

--5
SELECT AVG(population) FROM bbc WHERE name IN ('Poland', 'Germany', 'Denmark')

--6
SELECT region, SUM(population)/SUM(area) AS density FROM bbc GROUP BY region

--7
SELECT name, population/area AS density FROM bbc WHERE population = (SELECT MAX(population) FROM bbc)

The JOIN operation

JOIN 的说明
1
2
SELECT *
FROM game JOIN goal ON (id=matchid)
- 语句 FROM 表示合并 game 和 goal 两个表的数据。
- ON 表示如何找出 game 中每一列应该配对 goal 中的哪一列（game 的 id 需要配对 goal 的matchid），就是ON (game.id=goal.matchid)
GROUP BY 的说明 （参考 MySQL 必知必会 P84）

第11题

--1
SELECT matchid,player
FROM goal 
WHERE teamid='GER';

--2
SELECT id,stadium,team1,team2
FROM game
WHERE id=1012;

--3
SELECT player,teamid,stadium,mdate
FROM game JOIN goal ON (id=matchid)
WHERE teamid='GER';

--4
SELECT team1,team2,player
FROM game JOIN goal ON (id=matchid)
WHERE player LIKE 'Mario%';

--5
SELECT player, teamid, coach, gtime
FROM goal JOIN eteam ON (teamid=id)
WHERE gtime<=10;

--6
SELECT mdate,teamname
FROM game JOIN eteam ON (team1=eteam.id)
WHERE coach='Fernando Santos'
GROUP BY mdate, teamname;

--7
SELECT player
FROM game JOIN goal ON (id=matchid)
WHERE stadium = 'National Stadium, Warsaw';

--8 注意 player 是不重复的
SELECT DISTINCT player
FROM game JOIN goal ON (id=matchid) 
WHERE (team1='GER' OR team2='GER') AND teamid<>'GER';

--9
SELECT teamname, COUNT(player)
FROM eteam JOIN goal ON (id=teamid)
GROUP BY teamname;

--10
SELECT stadium,COUNT(teamid)
FROM game JOIN goal ON id=matchid
GROUP BY stadium;

--11 这里要以 matchid,mdate 为分组，少一个都不行
SELECT matchid, mdate, COUNT(*)
FROM game JOIN goal ON matchid = id 
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY matchid,mdate;

--12
SELECT matchid, mdate, COUNT(gtime)
FROM goal JOIN game ON matchid=id
WHERE teamid='GER'
GROUP BY matchid,mdate;

--13 CASE WHEN THEN ELSE END
SELECT mdate,team1,
  SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) AS score1,
  team2,
  SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) AS score2
FROM game LEFT JOIN goal ON matchid = id
GROUP BY mdate,matchid,team1,team2;

第 13 题，如果不用 SUM 统计 CASE 总和的化，得到的列中行值不是 0 就是 1；另外，需要 LEFT JOIN，因为仅仅 JOIN 的话，如果有某行全为 0（没有进球），则该行就不会显示了。LEFT JOIN 无论如何都会显示，不进球就全给 0 值（或NULL）

JOIN Quiz

--1
game  JOIN goal ON (id=matchid)

--2
 matchid, teamid, player, gtime, id, teamname, coach
 
 --3
 SELECT player, teamid, COUNT(*)
  FROM game JOIN goal ON matchid = id
 WHERE (team1 = "GRE" OR team2 = "GRE")
   AND teamid != 'GRE'
 GROUP BY player, teamid

--5
SELECT DISTINCT player, teamid 
FROM game JOIN goal ON matchid = id 
WHERE stadium = 'National Stadium, Warsaw' 
  AND (team1 = 'POL' OR team2 = 'POL')
  AND teamid != 'POL'
  
--6
SELECT DISTINCT player, teamid, gtime
  FROM game JOIN goal ON matchid = id
 WHERE stadium = 'Stadion Miejski (Wroclaw)'
   AND (( teamid = team2 AND team1 != 'ITA') OR ( teamid = team1 AND team2 != 'ITA'))

Using Null

第四题，JOIN 前后换序，结果是不一样的参考
- LEFT JOIN 先匹配左表
- RIGHT JOIN 先匹配右表

第五题，COALESCE 说明

COALESCE(x,y,z) 返回括号内参数的第一个非空值

--1
SELECT name
FROM teacher
WHERE dept is NULL;

--2
SELECT teacher.name, dept.name
 FROM teacher JOIN dept
           ON (teacher.dept=dept.id)
           
--3
SELECT teacher.name, dept.name
FROM teacher LEFT JOIN dept 
  ON (teacher.dept=dept.id);

--4 
SELECT teacher.name,dept.name
FROM dept LEFT JOIN teacher 
  ON (teacher.dept=dept.id);
或者
SELECT teacher.name,dept.name
FROM teacher RIGHT JOIN dept 
  ON (teacher.dept=dept.id);
  
--5 COALESCE
SELECT name,COALESCE(mobile,'07986 444 2266')
FROM teacher;

--6
SELECT teacher.name,COALESCE(dept.name,'None')
FROM teacher LEFT JOIN dept
  ON teacher.dept=dept.id;
  
--7
SELECT COUNT(name), COUNT(mobile)
FROM teacher;

--8
SELECT dept.name, COUNT(teacher.name)
FROM teacher RIGHT JOIN dept ON teacher.dept=dept.id
GROUP BY dept.name;

--9
SELECT name,CASE WHEN dept IN (1,2) THEN 'Sci'
  ELSE 'Art' END
FROM teacher;

--10
SELECT name,
  CASE WHEN dept IN (1,2) THEN 'Sci'
    WHEN dept=3 THEN 'Art'
    ELSE 'None' END
FROM teacher;

Using Null Quiz

--1
SELECT teacher.name, dept.name FROM teacher LEFT OUTER JOIN dept ON (teacher.dept = dept.id)

--2
SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE teacher.name = 'Cutflower'

--3
SELECT dept.name, COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON dept.id = teacher.dept GROUP BY dept.name

--4
display 0 in result column for all teachers without department

--5
'four' for Throd

Self join

MySQL 必知必会 P107

自联结：相当于一张表当两张用参考

帮助理解的 Leetcode 题 181. 超过经理收入的员工

# Write your MySQL query statement below
SELECT a.Name AS Employee
FROM Employee a JOIN Employee b ON a.ManagerId=b.Id
WHERE a.Salary > b.Salary

需回顾 6-10

--1
SELECT COUNT(*)
FROM stops;

--2
SELECT id
FROM stops
WHERE name='Craiglockhart';

--3
SELECT id,name
FROM stops JOIN route ON id=stop
WHERE num='4' AND company='LRT';

--4
SELECT company, num, COUNT(*)
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num
HAVING COUNT(*)=2;

--5
SELECT a.company, a.num, a.stop, b.stop
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
WHERE a.stop=53 AND b.stop=149;

--6
SELECT a.company, a.num, stopa.name, stopb.name
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name='Craiglockhart' AND stopb.name='London Road';

--7
SELECT DISTINCT a.company,a.num
FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num)
WHERE a.stop=115 AND b.stop=137;

--8 修改 6 的代码
SELECT a.company, a.num
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON a.stop=stopa.id
  JOIN stops stopb ON b.stop=stopb.id
WHERE stopa.name='Craiglockhart'and stopb.name ='Tollcross';

--9
select d.name,a.company,a.num
from route a join route b
on a.num =b.num and a.company = b.company
join stops c on (a.stop = c.id)
join stops d on (b.stop = d.id)
where c.name ='Craiglockhart';

--10
select distinct x.num,x.company,name,y.num,y.company
from(select a.num,a.company,b.stop
from route a
join route b
on a.num = b.num and a.company = b.company
and a.stop != b.stop
where a.stop = (select id from stops where name ='Craiglockhart')) as x
join (select c.num,c.company,c.stop
from route c
join route d 
on c.num = d.num and c.company = d.company
and c.stop != d.stop
where d.stop =(select id from stops where name = 'Lochend'))as y
on x.stop = y.stop
join stops on x.stop = stops.id
order by x.num,stops.name,y.num