SQL Review in Sqlzoo

Only record the code which I can’t write it down immediately

Such as I search the function via Baidu/Google
Some code is recorded last year, I will note the code with checkbox which I can’t remember well this year

目录

1. Day 1

2. Day 2

3. Record some questions

4. Day 2 night

5. Day 3

6. Day 4

7. Day 5 - inner join/ outer join/left join/self join

8. 其他

Day 1

SQL语句模糊查询的方法是使用操作符LIKE，LIKE操作符用于在WHERE子句中搜索列中的指定模式，另外还需要使用%作为通配符

- 查询含有Keyword的内容
SELECT * FROM TableName WHERE FieldName LIKE '%Keyword%';
- 查询Keyword开头的内容
SELECT * FROM TableName WHERE FieldName LIKE 'Keyword%';
- 查询Keyword结尾的内容
SELECT * FROM TableName WHERE FieldName LIKE '%Keyword';

Day 2

保留两位小数

Show the name and population in millions and the GDP in billions for the countries of the continent ‘South America’. Use the ROUND function to show the values to two decimal places.

For South America show population in millions and GDP in billions both to 2 decimal places.

Divide by 1000000 (6 zeros) for millions. Divide by 1000000000 (9 zeros) for billions.

select name, round(population/1000000,2) as 'population(millions)', 
round(GDP/1000000000,2) as 'GDP(billions)'
from world
where continent='South America'

取整到千位

Show the name and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.

Show per-capita GDP for the trillion dollar countries to the nearest $1000.

-3 往前推三位就是保留千位

select name,round(GDP/population, -3) as 'per-capita GDP(nearst 1000)'
from world
where GDP >=1000000000000

The capital of Sweden is Stockholm. Both words start with the letter ‘S’.

Show the name and the capital where the first letters of each match. Don’t include countries where the name and the capital are the same word.

• You can use the function LEFT to isolate the first character.
• You can use <> as the NOT EQUALS operator.

SELECT name, capital
FROM world
where left(name,1)=left(capital,1) and (name <> capital)

没有空格

name not like '% %'

• Equatorial Guinea and Dominican Republic have all of the vowels
  (a e i o u) in the name. They don’t count because they have more
  than one word in the name.

Find the country that has all the vowels and no spaces in its name.

SELECT name
FROM world
WHERE name LIKE '%a%' AND name LIKE '%e%' AND name LIKE '%i%' AND name LIKE '%o%' AND name LIKE '%u%' 
AND name NOT LIKE '% %'
AND name NOT LIKE '%a%a%' AND name NOT LIKE '%e%e%' AND name NOT LIKE '%i%i%' 
AND name NOT LIKE '%o%o%'  AND name NOT LIKE '%u%u%'

Find all details of the prize won by EUGENE O’NEILL

'单引号继续转义

select * from Nobel
where winner = 'EUGENE O''NEILL'

List the winners, year and subject where the winner starts with
Sir. Show the the most recent first, then by name order.

order by 条件 asc/desc，条件

select winner,yr,subject 
from nobel 
where winner like 'sir%' order by yr desc,winner;

DESC 从大到小
ASC 从小到大

Show the 1984 winners and subject ordered by subject and winner
name; but list Chemistry and Physics last.

SELECT winner, subject
  FROM nobel
 WHERE yr=1984
 ORDER BY subject IN ('Physics','Chemistry') asc, subject,winner

I don’t know why it’s incorrect.

Record some quetions

其实有点好奇为什么第一个是错误啊的，我纠结第一个和第二个很久

这个第二个选项太绕了，如果按照subject条件来写，我觉得是

select yr from Nobel
where subject not in ('Chemistry','Physics')

这个也很绕，不选二的原因是因为先AND两个条件的话与问题中是"或"的描述不符合

Day 2 night

Show the countries in Europe with a per capita GDP greater than ‘United Kingdom’.

select name from world 
where continent = 'Europe' and (GDP/population > (select GDP/population from world where name = 'United Kingdom'))

List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

select name, continent from world
where continent = (select continent from world where name ='Argentina') 
or continent = (select continent from world where name ='Australia') 
order by name

或者
select name, continent from world
where continent in (
  select continent from world where name in ('Argentina','Australia'))
order by name

in：in适合用于子查询表数据比父查询表数据多的情况。把父查询表和子查询表作hash连接。

or：or适合用于子查询表数据比父查询表数据少的情况。对父查询表作loop循环，每次loop循环再对子查询表进行查询

在没有索引的情况下，随着or后面的数据量越多，or的执行效率会有明显的下降；in没有这种情况

concat(某一个东西，‘另一个东西，可能是单位’)

floor向下区取整

神奇的一道题，为什么不加concat都是对的，整数数字一致，加了concat后面多了很多小数位

Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

select name 
from world
where GDP > all (select GDP 
                 from world 
                 where continent='Europe' and GDP>0)

注意为零的项

Find the largest country (by area) in each continent, show the
continent, the name and the area:

select continent, name , area 
from world x
where area = (select max(area) 
              from world y
              where x.continent=y.continent );

子查询条件（where后）的连接与所求分类目标相关，比如这里是找各洲最大面积的国家，因此关联的是continent；不同职位也同理

List each continent and the name of the country that comes first alphabetically.

字符串也可以比大小，依据是字母顺序，A~Z对应的是从小到大，所以选择名字是这个洲全部国家中最小的

select continent, name
from world a
where name <= all(select name from world b where a.continent=b.continent)
或者
select continent,min(name)
from world
group by continent

Find the continents where all countries have a population <=
25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

select name,continent,population from world x
where 25000000 >= all (select population from world y where x.continent=y.continent)

Some countries have populations more than three times that of all of their neighbours (in the same continent). Give the countries and continents.

select name,continent from world x
where population/3 > all(select population from world y where x.continent=y.continent and x.name <> y.name)

NULL 不能用 ‘<>’ '='取做比较

Day 3

主要复习SUM, Count, MAX, DISTINCT and ORDER BY.

select distinct XXX from table;

List the continents that have a total population of at least 100 million.

select continent from world
group by continent having sum(population) >= 100000000

SELECT *|字段列表 [as 别名] FROM 表名 [WHERE 子句] [GROUP BY 子句][HAVING 子句][ORDER BY 子句][LIMIT 子句]

where 后面不能跟聚合函数

mysql中，当我们用到聚合函数，如sum，count后，又需要筛选条件时，having就派上用场了，因为WHERE是在聚合前筛选记录的，having子句用于筛选分组之后的各种数据，通常与“group by”联合使用

Select the list of core SQL aggregate functions(sql中的聚集函数)

• AVG(), COUNT(), MAX(), MIN(), SUM()

主要复习Join

Left join: table 1 left join table 2 on 条件
return： table 1 所有信息记录及字段 + table 2与table 1共同信息记录字段
A + A 交 B

如果想要得到不重合部分，加上where table 2.目标 is null （即table 1有，table 2 没有）
is not null就是重合部分

inner join: 就是两张表的交集

select展示列，where展示行

List the dates of the matches and the name of the team in which ‘Fernando Santos’ was the team1 coach.

select  mdate, teamname from game as ga
left join eteam as et on (team1=et.id or team2 = et.id)
where et.coach = 'Fernando Santos' and et.id = team1
或者
select  mdate, teamname from game as ga
left join eteam as et on team1=et.id 
where et.coach = 'Fernando Santos' 

The example query shows all goals scored in the Germany-Greece quarterfinal.
Instead show the name of all players who scored a goal against Germany.

select distinct player 
from goal a left join game b on a.matchid= b.id
where (team1='GER'and teamid<>'GER') or(team2='GER'and teamid<>'GER');

teamid代表进球队伍
Show teamname and the total number of goals scored.

select teamname, count(player)
from eteam et 
left join goal go on et.id = go.teamid
group by teamname

Show the stadium and the number of goals scored in each stadium.

select stadium, count(player)
from game ga
left join goal go on ga.id=go.matchid
group by stadium

忘记了matchid的存在。。。
For every match involving ‘POL’, show the matchid, date and the number of goals scored.

select matchid,mdate,count(player)
from goal go
left join game ga on go.matchid = ga.id
where ga.team1='POL' or ga.team2='POL'
group by matchid,mdate

每一场关于POL需要按照比赛编号和时间分类集合
涉及的话就是team1，team2都要考虑

join，其实就是“inner join”，为了简写才写成join，两个是表示一个的，内连接，表示以两个表的交集为主，查出来是两个表有交集的部分，其余没有关联就不额外显示出来

Day 4

Obtain the cast list for ‘Casablanca’.
The cast list is the names of the actors who were in the movie.
Use movieid=11768, (or whatever value you got from the previous question)

select name from actor ac
left join casting ca on ac.id=ca.actorid
where ca.movieid = (select id from movie 
where title = 'Casablanca')

这里11768感觉有点误导了，我发现使用11768做不出来，没有查询结果，私下写的Casablanca对应的id是27，可能数据库有变动

List the films together with the leading star for all 1962 films.

select title, name as 'leading star' from movie mo
left join casting cast on mo.id=cast.movieid
left join actor act on cast.actorid = act.id
where yr = 1962 and ord = 1

查了一下多表查询的方法，发现就是直接join就好了

仅记录方法

List the film title and the leading actor for all of the films ‘Julie Andrews’ played in.

select title, name from movie mo
left join casting cast on mo.id=cast.movieid and cast.ord=1
left join actor act on cast.actorid=act.id
where mo.id in (select movieid from casting where actorid in (select id from actor where name = 'Julie Andrews'))

但是我自己的答案是这个，但是少一个答案就是title中为1的

select title, name from movie mo
left join casting cast on mo.id=cast.movieid
left join actor act on cast.actorid=act.id
where cast.ord=1 and act.name='Julie Andrews'

可以看到包含Julie的确实有title=10的，但是加上ord=1 却不是Julie

顺序： select、where、group by、having、order

List all the people who have worked with ‘Art Garfunkel’.

select name from casting 
join actor on(actor.id=actorid) join movie on(movie.id=movieid) 
where movieid in
 (select movieid 
from casting where actorid in 
(select id from actor where name ='Art Garfunkel'))
and name !='Art Garfunkel'

思路：先把三个表连起来，找它对应的movieid获得包含Art的所有人名字，最后输出不包含Art的名字
先获得有Art的movie有哪些（可能不止一部，所以用in），然后找到在有他的movie中输出除他外的名字

上图Order by 2的意思是按照count(movieid)排序，select有几个元素，对应了index
order by 1 = order by name

Day 5 (join)

inner join只得到两张表都有值的交集部分

补充：
left outer join = left join : 就是把左边表的数据全部取出来，而右边表的数据有相等的，显示出来，如果没有，显示NULL
right outer join = right join: 就是把右边表的数据全部取出来，而左边表的数据有相等的，显示出来，如果没有，显示NULL
join = inner join

Use a different JOIN so that all departments are listed.
我理解的是right join 但是这里似乎目标答案不太一样

• COALESCE
  COALESCE 接受任意数量的参数并返回第一个不为空的值。

COALESCE(x,y,z) = x if x is not NULL
COALESCE(x,y,z) = y if x is NULL and y is not NULL
COALESCE(x,y,z) = z if x and y are NULL but z is not NULL
COALESCE(x,y,z) = NULL if x and y and z are all NULL

以下三题都写出来了，仅做函数使用方法的记录
所以可以用coalesce来填补空值
Use COALESCE to print the mobile number. Use the number ‘07986 444 2266’ if there is no number given. Show teacher name and mobile number or ‘07986 444 2266’

select teacher.name, coalesce(teacher.mobile,'07986 444 2266') from teacher
left join dept on dept.id = teacher.dept

Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.

select dept.name, count(te.name) from dept
left join teacher te on te.dept = dept.id
group by dept.name 

Use CASE to show the name of each teacher followed by ‘Sci’ if the teacher is in dept 1 or 2, show ‘Art’ if the teacher’s dept is 3 and ‘None’ otherwise.

select te.name, (Case when te.dept = 1 or te.dept = 2 then 'Sci'
when te.dept = 3 then 'Art'
else 'None' END)
from teacher te
left join dept on te.dept = dept.id

格式说明
case 列名
when 条件值1 then 选项1
when 条件值2 then 选项2…
else 默认值 end

eg:

select
　　　　case 　　job_level
　　　　when ‘1’ then ‘1111’
　　　　when　 ‘2’ then ‘1111’
　　　　when　 ‘3’ then ‘1111’
　　　　else ‘eee’ end
　　　　from dbo.employee

第二种 格式 :Case搜索函数

case
　　　　when 列名= 条件值1 then 选项1
　　　　when 列名=条件值2 then 选项2…
　　　　else 默认值 end

eg:

case
　　　　when job_level = ‘1’ then e_wage1.97
　　　　when job_level = ‘2’ then e_wage1.07
　　　　when job_level = ‘3’ then e_wage1.06
　　　　else e_wage1.05
　　　　end

这里先连接的是teacher，但是需要获得的是dept所有的name，因此是right join

主要是理解’4’和‘LRT’分别代表什么，在route中，
num 是指几路公交车，varchar
company是服务公司
pos是int, 停靠站点顺序
stop是int， 关联着stops的id

self join
The query shown is similar to the previous one, however by joining two copies of the stops table we can refer to stops by name rather than by number. Change the query so that the services between ‘Craiglockhart’ and ‘London Road’ are shown.

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) #a 连接 stop
  JOIN stops stopb ON (b.stop=stopb.id) # b 连接 stop
WHERE (stopa.name='Craiglockhart' and stopb.name='London Road')

Give a list of all the services which connect stops 115 and 137 (‘Haymarket’ and ‘Leith’)

select distinct a.company, a.num
from route a left join route b on a.company = b.company and a.num = b.num
left join stops stopa on a.stop=stopa.id
left join stops stopb on b.stop=stopb.id
where (stopa.id = 115 and stopb.id = 137) or (stopa.id = 137 and stopb.id = 115)

Give a distinct list of the stops which may be reached from ‘Craiglockhart’ by taking one bus, including ‘Craiglockhart’ itself, offered by the LRT company. Include the company and bus no. of the relevant services.

select distinct stopb.name, a.company, a.num from route a
left join route b on a.company = b.company and a.num = b.num
left join stops stopa on stopa.id = a.stop
left join stops stopb on stopb.id = b.stop
where stopa.name='Craiglockhart'

这里最开始没写出来的原因是不知道stop.name用哪一个，然后条件也在思考需不需要stopa或stopb都等于目标
后面想通了，只需要找目标对面的那个就行

Find the routes involving two buses that can go from Craiglockhart to Lochend.Show the bus no. and company for the first bus, the name of the stop for the transfer, and the bus no. and company for the second bus.
自己写了几个答案不对
我本来思考的是两个内连的话，跟上面一题一样设置stopa.name='Craiglockhart ', stopc.name=‘Lochend’,输出 stopb.name，但是没有结果，所以希望有大佬可以给我答疑
我搜了一下没有找到正确的答案
Stakeoverflow上的答案

SELECT DISTINCT one.num as FirstBus, one.company as FirstComp, one.name as Transfer, two.num as SecBus, two.company as SecComp
FROM
    (select distinct a.num, a.company, yy.name
     from route a join route b on (a.company=b.company and a.num=b.num) 
                  join stops xx on (xx.id=a.stop) 
                  join stops yy on (yy.id=b.stop)
     where xx.name='Craiglockhart' and yy.name<>'Lochend'
     ) AS one
inner JOIN
    (select distinct c.num, d.company, mm.name
     from route c join route d on (c.company=d.company and c.num=d.num) 
                  join stops mm on (mm.id=c.stop) 
                  join stops nn on (nn.id=d.stop)
     where mm.name <> 'Craiglockhart' and nn.name='Lochend'
     ) AS two
ON (two.name=one.name)

构思非常巧妙：

1. find the num and company for the ones who have ‘Craiglockhart’ but not ‘Lochend’; 找起点是Craiglockhart的所有（不包括Lochend为终点）
2. find the num and company for the ones who have ‘Lochend’ but not ‘Craiglockhart’; 找终点是Lochend不包括起点是Craiglockhart
3. find the shared stops from the two above-- using the table “stops” as the link; 重叠部分就是起点为Craiglockhart，终点为Lochend中间的那些站，因此做一个inner join ,
4. display as required.

其他