sqlzoo刷题记录

话不多说了，今天我要把SQL zoo一顿暴刷！Today is the day! 搞他

附上链接：Welcome to SQL Zoo

一、SELECT from WORLD Tutorial

table内容：

in的使用

select name, population
from world
where name in ('Sweden','Norway','Denmark')

between的用法

select name,area
from world
where area between 200000 and 250000

* like的用法

主要是 % 和 _ 的使用

* 逻辑运算符

下面列出了 SQL 中可用的逻辑运算符

运算符	描述
ALL	ALL 运算符用于将一个值同另一个值集中所有的值进行比较。
AND	AND 运算符使得在 WHERE 子句中可以同时存在多个条件。
BETWEEN	给定最小值和最大值，BETWEEN 运算符可以用于搜索区间内的值。
EXISTS	EXISTS 运算符用于在表中搜索符合特定条件的行。
IN	IN 运算符用于将某个值同指定的一列字面值相比较。
LIKE	LIKE运算符用于使用通配符对某个值和与其相似的值做出比较。
NOT	NOT 操作符反转它所作用的操作符的意义。例如，NOT EXISTS、NOT BETWEEN、NOT IN 等。这是一个求反运算符。
OR	OR 运算符用于在 SQL 语句中连接多个条件。
IS NULL	NULL Operator 用于将某个值同 NULL 作比较。
UNIQUE	UNIQUE 运算符检查指定表的所有行，以确定没有重复。

Exclusive OR（XOR）

查找只满足两个条件中的某一个的情况，剔除同时满足和同时不满足的数据

select name,population,area
from world
where (area>3000000 or population>250000000)
  and (not(area>3000000 and population>250000000))

round函数

select name,round(population/1000000,2),round(gdp/1000000000,2)
from world
where continent ='South America'

第二个参数是保留几位小数，例如 -2 是指精确到100，2 指精确到0.01

left函数

第一个参数是字段，第二个参数是位数

<>表示"不等于"

字符包含问题，like

---

二、SELECT from Nobel Tutorial

table内容：

单引号转义

order by 后边插入语句

---

三、SELECT within SELECT

基于world表：

cast(xxx as int)  --类型转换函数
concat(str1,str2)  --字符串拼接函数
round(number,3)  --保留有效数字函数

 ">=ALL(......)" 的使用，注意括号内不能有空值
 所以6题中用 "gdp>0" 约束

选出分组中最大值的经典题

-- 查询每个洲中，面积最大国家的信息
-- 相关子查询：依靠表别名来标识同一表的两种不同用途，一种用于外部查询，另一种用于子查询。

-- 解法一
select continent,name,area
from world x
where area>=all(select area from world y 
  where x.continent=y.continent and y.area>0
)

-- 解法二
select continent,name,area
from world x
where area=all(select max(area) from world y 
  where x.continent=y.continent and y.area>0
)

-- 不知道为什么，显示limit语法错误
select continent,name
from world x
where name=(select y.name from world y
  where x.continent=y.continent
  order by y.name limit 1
)

-- 重点题 
-- 每个国家人口都小于25000000的大洲，其中包含的所有国家
select name,continent,population from world x
where 25000000>=all(select population from world y
                    where y.continent=x.continent)

select name,continent from world x
where (population/3)>all(select population from world y
                       where y.continent=x.continent and y.name<>x.name)
-- 最后比较时候，记得剔除自己

---

四、Sum and Count

表world内容：

1. Show the total population of the world.

   select sum(population) from world
   

   • sum 求和函数

2. List all the continents - just once each.

   select distinct(continent) from world
   

   • distinct 去重函数

3. Give the total GDP of Africa

   select sum(gdp) from world
   where continent='Africa'
   

4. How many countries have an area of at least 1000000

   select count(name) from world
   where area>=1000000
   

5. What is the total population of (‘Estonia’, ‘Latvia’, ‘Lithuania’)

   select sum(population) from world
   where name in ('Estonia', 'Latvia', 'Lithuania')
   

6. For each continent show the continent and number of countries.

   select continent,count(name) from world
   group by continent
   

7. For each continent show the continent and number of countries with populations of at least 10 million.

   select x.continent,count(x.continent)
   from (select * from world
         where population>10000000) x
   group by x.continent
   

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

   select continent
   from(select continent,sum(population) as totalpl from world
        group by continent) x
   where x.totalpl>100000000
   

五、Join

game表的主键是id表示比赛的场次，mdate是比赛日期，stadium是比赛场地，team1和team2是比赛双方的名字，是eteam表的外键

goal表主键matchid是比赛场次，和game表的id是相同意思，teamid，player和gtime分别表示哪个球队的哪位球员在比赛开始后多少分钟进球得分

eteam表id是球队编码，和game表中的team1，team2是一个意思，teamname和coach分别表示球队名称和教练

1. show the matchid and player name for all goals scored by Germany. To identify German players, check for: teamid = 'GER'

   select matchid,player from goal
   where teamid='GER'
   

2. Show id, stadium, team1, team2 for just game 1012

   sekect * from game
   where id=1012
   

3. Modify it to show the player, teamid, stadium and mdate for every German goal.

   select player,teamid,stadium,mdate
   from goal left join game on id=matchid
   where teamid='GER'
   

4. Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'

   select team1,team2,player
   from goal left join game on matchid=id
   where player like 'Mario%'
   

5. Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10

   select player,teamid,coach,gtime
   from goal left join eteam on teamid=id
   where gtime<=10
   

6. 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 left join eteam on team1=eteam.id
   where coach='Fernando Santos'
   -- 名字重复时，用表名加“.”区分
   

7. List the player for every goal scored in a game where the stadium was ‘National Stadium, Warsaw’

   select player
   from goal left join game on matchid=id
   where stadium='National Stadium, Warsaw'
   

8. show the name of all players who scored a goal against Germany.

   select distinct player
   from goal left join game on matchid=id
   where teamid!='GER' and (team1='GER' or team2='GER')
   -- 注意去重
   

9. Show teamname and the total number of goals scored.

   select teamname,count(teamname)
   from goal left join eteam on teamid=id
   group by teamname
   -- 如果是group by teamid 会报错：
   -- teamname 既不在聚合函数中，也不在group by语句中
   

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

    select stadium, count(stadium)
    from goal left join game on id=matchid
    group by stadium
    -- 与9题一样
    

11. For every match involving ‘POL’, show the matchid, date and the number of goals scored.

    select id,mdate,count(1)
    from goal left join game on id=matchid
    where team1='POL' or team2='POL'
    group by id,mdate
    -- group by 多列分组，用逗号间隔开
    

12. For every match where ‘GER’ scored, show matchid, match date and the number of goals scored by 'GER’

    select matchid,mdate,count(1)
    from goal left join game on id=matchid
    where teamid='GER'
    group by matchid,mdate
    -- 这里group by要写两个，不然mdate会报错
    

13. 得到如图所示的结果

    

    select mdate,team1,     
        SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) score1,     
        team2,     
        SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) score2     
    from game LEFT JOIN goal ON matchid = id
    group by mdate,matchid,team1,team2
    -- 非常重要的 case when 与 sum 合用的方法，配合 order by 使用 
    

    13题知识，学习参考：https://blog.csdn.net/WuLex/article/details/115055979?utm_medium=distribute.pc_relevant.none-task-blog-baidujs_title-0&spm=1001.2101.3001.4242

六、More Join

表结构如图所示

movie表：id是电影的编号，title是电影名称，yr是放映日期，score是评分，votes是投票人数，director是导演人数（？？director没看懂）

actor表：id是演员编号，name是演员姓名

casting表：movieid与movie表中id对应，actorid与actor表中的id对应，ord表示该演员在电影中是几号演员，如1表示主角

1. List the films where the yr is 1962 [Show id, title]

   select id,title
   from movie
   where yr=1962
   

2. Give year of ‘Citizen Kane’.

   select yr
   from movie
   where title='Citizen Kane'
   

3. List all of the Star Trek movies, include the id, title and yr (all of these movies include the words Star Trek in the title). Order results by year.

   select id,title,yr
   from movie
   where title like '%Star Trek%'
   

4. What id number does the actor ‘Glenn Close’ have?

   select id from actor
   where name = 'Glenn Close'
   

5. What is the id of the film ‘Casablanca’

   select id from movie
   where title = 'Casablanca'
   

6. Obtain the cast list for ‘Casablanca’.

   what is a cast list?

   The cast list is the names of the actors who were in the movie.

   select distinct(name)
   from actor a left join casting c
   on a.id=c.actorId
   where c.movieId = (select id from movie where title= 'Casablanca')
   

7. Obtain the cast list for the film ‘Alien’

   select distinct(name)
   from actor a left join casting c
   on a.id=c.actorId
   where c.movieId = (select id from movie where title= 'Alien')
   

8. List the films in which ‘Harrison Ford’ has appeared

   select distinct(title)
   from movie m left join casting c
   on m.id=c.movieId
   where c.actorId=(select id from actor where name = 'Harrison Ford')
   

9. List the films where ‘Harrison Ford’ has appeared - but not in the starring role.

   select distinct(title)
   from movie m left join casting c
   on m.id=c.movieId
   where c.ord<>1
       and c.actorId = (select id from actor where name = 'Harrison Ford')
   

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

    select title,name
    from casting c
    left join movie m on m.id = c.movieId
    right join actor a on a.id = c.actorId  -- 这里是三个表的连接，第一个是主表
    where ord = 1 and yr = 1962
    
    -- 一个电影可能有多个主演
    

11. Which were the busiest years for ‘Rock Hudson’, show the year and the number of movies he made each year for any year in which he made more than 2 movies.

    select yr,count(title)
    from movie m
    join casting c on m.id = c.movieId
    join actor a on c.actorId = a.id
    where a.name = 'Rock Hudson'
    group by m.yr
    having count(m.title)>2
    

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

    select title,name
    from movie m
    join casting c on m.id=c.movieId
    join actor a on c.actorId=a.id
    where m.id in (select movieId
                   from casting
                   where actorId=(select id from actor where name='Julie Andrews'))
    and ord=1
    

13. Obtain a list, in alphabetical order, of actors who’ve had at least 15 starring roles.

    select name
    from actor a
    join casting c on a.id=c.actorId
    where ord=1
    group by name
    having count(name)>=15
    order by name
    

14. List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

    select title,count(title)
    from movie m
    join casting c on m.id=c.movieId
    where yr=1978
    group by title
    order by count(title) desc,title
    

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

    select distinct(name)
    from actor a
    join casting c on a.id=c.actorId
    where movieId in (select movieId
                      from casting
                      where actorId=(select id from actor where name='Art Garfunkel'))
    and name<>'Art Garfunkel'
    

七、Using NULL

表结构如图所示

teacher表中的dept字段与dept表中的id字段对应

1. List the teachers who have NULL for their department.

   select name
   from teacher
   where dept is NULL
   

2. Note the INNER JOIN misses the teachers with no department and the departments with no teacher.

   select t.name,d.name
   from teacher t
   join dept d on t.dept=d.id
   

3. Use a different JOIN so that all teachers are listed.

   select t.name,d.name
   from teacher t
   left join dept d on t.dept=d.id
   

4. Use a different JOIN so that all departments are listed.

   select t.name,d.name
   from teacher t
   right join dept d on t.dept=d.id
   

5. 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’

   -- coalesce函数，返回第一个不为NULL的值
   select name,coalesce(mobile,'07986 444 2266')
   from teacher
   

6. Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string ‘None’ where there is no department.

   select t.name,coalesce(d.name,'None')
   from teacher t
   left join dept d on t.dept=d.id
   

7. Use COUNT to show the number of teachers and the number of mobile phones.

   select count(name),count(mobile)
   from teacher
   

8. 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 d.name,count(t.name)
   from dept d
   left join teacher t on d.id=t.dept
   group by d.name
   

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

   -- 题目意思：如果是在1或2学院，第二列是Sci，否则第二列是Art
   
   select name,case when dept in (1,2) then 'Sci' else 'Art' end
   from teacher
   

10. 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 name,
    case when dept in (1,2) then 'Sci' when dept=3 then 'Art' else 'None' end
    from teacher
    

八、Self Join

表结构如图所示

• stops表

• route表

  

route表：num是公交车号（注意部分有字母，是字符串格式），company是公交公司，num和company两者组成复合主键，pos表示停站的顺序，stop表示站点，与 stops表中的id对应

1. How many stops are in the database.

   select count(id)
   from stops
   

2. Find the id value for the stop ‘Craiglockhart’

   select id
   from stops
   where name='Craiglockhart'
   

3. Give the id and the name for the stops on the ‘4’ ‘LRT’ service.

   -- 查找的是公交车号为4，公司是LRT的车经过的站点id和名字
   
   select id,name from stops
   where id in(select stop
   from route
   where num='4' and company='LRT'
   )
   

4. The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes.

   -- 对于这两个站点（149）和（53），看看那一辆车同时经过这一个站点
   -- 注意：车辆是复合主键（num,company）
   
   SELECT company, num, COUNT(*)
   FROM route WHERE stop=149 OR stop=53
   GROUP BY company, num
   having count(*)>1  -- 这是加的那一句
   

5. Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road.

   /*
   这是初始给的代码，意思是可以得到所有的从53号出发可以到达的站点（b.stop）
   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
   */
   
   
   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=(select id from stops where name='London Road')
   
   
   

6. 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. If you are tired of these places try ‘Fairmilehead’ against ‘Tollcross’

   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'
   
   -- 跟上一个题没什么变化，只是后边加了两个stops表的连接，这样可以直接显示站点的名字
   

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

   -- 这里存疑，关于下图这样的内容怎么去重,distinct放在哪
   
   select distinct(t1.company),t1.num
   from route t1
   join route t2 on t1.num=t2.num and t1.company=t2.company
   where t1.stop=115 and t2.stop=137
   

   

8. Give a list of the services which connect the stops ‘Craiglockhart’ and ‘Tollcross’

   select t1.company,t1.num
   from route t1
   join route t2 on t1.num=t2.num and t1.company=t2.company
   join stops s1 on t1.stop=s1.id
   join stops s2 on t2.stop=s2.id
   where s1.name='Craiglockhart' and s2.name='Tollcross'
   

9. 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.

   -- 从 'Craiglockhart' 出发可以到达的站点，包括自己，公司是LRT
   
   select s2.name,t1.company,t1.num
   from route t1
   join route t2 on t1.num=t2.num and t1.company=t2.company
   join stops s1 on t1.stop=s1.id
   join stops s2 on t2.stop=s2.id
   where s1.name='Craiglockhart' and t1.company='LRT'
   

10. 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.

    Hint提示

    Self-join twice to find buses that visit Craiglockhart and Lochend, then join those on matching stops.

    -- 根据提示，首先做两个表，第一个是从Craiglockhart出发的，第二个是到达Lochend的
    -- 然后根据中转车站名相同，将两个表连接，再输出想要的结果，（车1，换乘车站name，车2）
    
    select b1.num,b1.company,b1.name,b2.num,b2.company
    from (select t1.num as num,t1.company as company,s2.name as name
          from route t1
          join route t2 on t1.num=t2.num and t1.company=t2.company
          join stops s1 on t1.stop=s1.id
          join stops s2 on t2.stop=s2.id
          where s1.name='Craiglockhart'
         ) b1
    join (select t1.num as num,t1.company as company,s1.name as name
          from route t1
          join route t2 on t1.num=t2.num and t1.company=t2.company
          join stops s1 on t1.stop=s1.id
          join stops s2 on t2.stop=s2.id
          where s2.name='Lochend'
         ) b2 on b1.name=b2.name
    

到最后还是用了好几天才做完，看着简单，其实需要耗费时间去琢磨。这只是第一遍，记得多回来巩固，一遍是不够的。