SQLzoo错题集

1-2.select basics&select from world

1. Exclusive OR (XOR). Show the countries that are big by area (more than 3 million) or big by population (more than 250 million) but not both. Show name, population and area.

   SELECT name, population, area FROM world WHERE area > 3000000 XOR population > 250000000;
   

   A XOR B：A和B只有一个条件为真，不可两者皆为真或两者皆为假

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

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

   取南美洲的国家、人口（百万计）、GDP（十亿计），且保留两位小数。

   SELECT name, ROUND(population/1000000,2), ROUND(gdp/1000000000,2) FROM world
   WHERE continent = 'South America';
   

10.显示国家有至少一个万亿元国内生产总值（万亿，也就是12个零）的人均国内生产总值。四舍五入这个值到最接近1000

select name,round(gdp/population/1000)*1000 from world where gdp>1000000000000;
select name,round(gdp/population，-3) from world where gdp>1000000000000;

11.The CASE statement shown is used to substitute North America for Caribbean in the third column.

Show the name - but substitute Australasia for Oceania - for countries beginning with N.

SELECT name, CASE WHEN continent='Oceania' THEN 'Australasia'
            ELSE continent END
  FROM world
 WHERE name LIKE 'N%'

CASE WHEN SCORE = ‘A’ THEN ‘优’
WHEN SCORE = ‘B’ THEN ‘良’
WHEN SCORE = ‘C’ THEN ‘中’ ELSE ‘不及格’ END
多个when then中间是不需要逗号隔开的，但是一定要end表示字段的结束，同时，then后面的值类型 和 else后边的值类型应一致，否则会报错

12.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
  #LEFT('Hello world', 4) -> 'Hell'     
  left（a,n）a可以是列名也可是文本
  

3 select from nobel

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

文本里如果有引号要打两遍

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

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

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

14.The expression subject IN (‘Chemistry’,‘Physics’) can be used as a value - it will be 0 or 1.

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'),subject,winner; 

当把subject in (‘Physics’,‘Chemistry’)放在select后面的时候，出来的结果subject in (‘Physics’,‘Chemistry’)作为一个column显示的是1或0 – 这就是说当把subject in (‘Physics’,‘Chemistry’)放在order by后面的时候，先是0，后是1，也就是先排序非物理和化学领域，再排序化学和物理领域。因此把subject in (‘Physics’,‘Chemistry’)放在ORDER BY后第一个可以保证物理和化学领域在最后

quiz:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-phC3enHv-1628662892411)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20201225140137506.png)]

3.Pick the code that shows the amount of years where no Medicine awards were given

SELECT COUNT(DISTINCT yr) 
       FROM nobel
       WHERE yr NOT IN 
               (SELECT DISTINCT yr FROM nobel WHERE subject = 'Medicine')

嵌套查询时，yr必须与嵌套SELECT语句的查询主语一致

4 select in select

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-CKDvUkn2-1628662892412)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20201227161447506.png)]

5.Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.

Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

The format should be Name, Percentage for example:

name	percentage
Albania	3%
Andorra	0%
Austria	11%
…	…

Decimal places

Percent symbol %

You can use the function CONCAT to add the percentage symbol.

select name, concat(round(population/(select population from world where name='Germany')*100,0),'%') as Percentage from world where continent ='Europe' 

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

答案1：

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

答案2：

select name from world where GDP >(select max(GDP) from world where continent ='Europe')

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

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

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

select continent, name from world X where x.name=(select y.name from world y where y.continent=x.continent order by name limit 1)---limit是MySQL语法

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

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

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

select in select quiz扩展训练

5.group by&having

注意对比分析7和8题

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

select continent , count(name) from world where population>10000000 group by continent 

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

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

quiz

4. Select the result that would be obtained from the following code:

 SELECT region, SUM(area)
   FROM bbc 
  WHERE SUM(area) > 15000000 
  GROUP BY region

答案：No result due to invalid use of the WHERE function

6.JOIN

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

SELECT player FROM goal JOIN game ON matchid=id WHERE stadium = 'National Stadium, Warsaw'

8.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 JOIN game ON id = matchid WHERE teamid <> 'GER' AND (team1='GER' OR team2='GER')

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

SELECT matchid, mdate, COUNT(gtime) FROM game JOIN goal ON matchid = id WHERE (team1 = 'POL' OR team2 = 'POL') GROUP BY matchid, mdate

不可以用having，having 子据配合group by 后面接聚合函数，在where 子句中不能使用聚组函数，但是可以用字段直接乘除。

where group by having order by

table_A join B on column_a=column_b and 这里的“and ”效用堪比where，但是where会排除不符合条件的行，and会保留并显示为。参考网页

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

SELECT id, mdate, COUNT(gtime) FROM game JOIN goal ON id = matchid WHERE (team1 = 'GER' OR team2 = 'GER') AND teamid = 'GER' GROUP BY id, mdate

13.List every match with the goals scored by each team as shown. This will use “CASE WHEN” which has not been explained in any previous exercises.

mdate	team1	score1	team2	score2
1 July 2012	ESP	4	ITA	0
10 June 2012	ESP	1	ITA	1
10 June 2012	IRL	1	CRO	3
…

Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1.

Sort your result by mdate, matchid, team1 and team2.

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

Join Quiz

7.More Join[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uL1di2NM-1628662892414)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210105141453576.png)]

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

(两种答案)

SELECT name FROM actor JOIN casting ON actor.id=actorid where movieid = (select movie.id from movie where title = 'Alien')

select name from movie as t1 
join casting as t2 on t1.id = t2.movieid and title = 'Alien'
join actor as t3 on t2.actorid = t3.id 

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

SELECT title FROM movie JOIN casting ON movie.id=movieid WHERE actorid = (SELECT actor.id FROM actor WHERE name = 'Harrison Ford')

11.Which were the busiest years for ‘John Travolta’, 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 JOIN casting ON movie.id=movieid
        JOIN actor   ON actorid=actor.id
and name='Rock Hudson'
GROUP BY yr
HAVING COUNT(title) > 2

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

Did you get “Little Miss Marker twice”?

Julie Andrews starred in the 1980 remake of Little Miss Marker and not the original(1934).

Title is not a unique field, create a table of IDs in your subquery

用名称选julie出演过的电影名筛选出来以后，存在同名电影，但是她没有出演过的。这样筛选出的电影主演也是冗余值。所以根本上应该按照电影id来规避重名问题。亦即：julie出演过的电影的id

SELECT title, name FROM movie JOIN casting ON movie.id=movieid JOIN actor ON actor.id=actorid where ord=1 AND movieid IN (SELECT movieid FROM casting JOIN actor ON actorid=actor.id WHERE name='Julie Andrews')

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

select name from actor join casting on id=actorid and ord=1 group by name having count(ord)>=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(actorid)from movie join casting on movie.id=movieid where yr=1978 group by title order by count(actorid) desc,title

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

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

select distinct actor.name
from casting t1
INNER JOIN (
    select distinct movieid
    from casting
    inner join actor
    on casting.actorid = actor.id
    where actor.name = 'Art Garfunkel'
)t2
on t1.movieid = t2.movieid
inner join actor 
on t1.actorid = actor.id
where actor.name <> 'Art Garfunkel'
;

quiz

3**.Select the statement that shows the list of actors called ‘John’ by order of number of movies in which they acted.**

SELECT name, COUNT(movieid)
  FROM casting JOIN actor ON actorid=actor.id
  WHERE name LIKE 'John %'
  GROUP BY name 
  ORDER BY 2 DESC
# ORDER BY 2 DESC表示对第二字段降序排列，第二字段指的是SELECT后的第二字段COUNT(movieid)

8.Using Null

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’

select teacher.name,
coalesce(mobile,'07986 444 2266') as NO 
from teacher;

select coalesce(a,b,c);

如果a=null,则选择b；如果b=null,则选择c；如果a!=null,则选择a；如果a b c 都为null ，则返回为null（没意义）。a\b\c可以为列名，也可以为具体值

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

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

注意对比

select count(teacher.name),count(teacher.mobile) from teacher group by name,mobile

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.

select teacher.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;

quiz

3. Select out of following the code which uses a JOIN to show a list of all the departments and number of employed teachers

   结果对比：

   --join 测试
   SELECT dept.name, COUNT(teacher.name) 
FROM teacher JOIN dept ON dept.id = teacher.dept 
   GROUP BY dept.name

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-niZ1gF0K-1628662892415)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210105165708984.png)]

--left join 测试
SELECT dept.name, COUNT(teacher.name) 
FROM teacher LEFT JOIN dept ON dept.id = teacher.dept 
GROUP BY dept.name

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qQsRNu5Z-1628662892417)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210105165725900.png)]

--right join 测试
 SELECT dept.name, COUNT(teacher.name) 
 FROM teacher RIGHT JOIN dept ON dept.id = teacher.dept 
 GROUP BY dept.name

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FsmdBX8T-1628662892418)(C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20210105165739321.png)]

9.Self Join

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.

方法1：
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
方法2：
SELECT a.company,a.num,a.stop,b.stop from route a join route b 
on a.num=b.num and a.company=b.company 
join stops c on c.id=a.stop join stops d on d.id=b.stop
where c.name= 'Craiglockhart'and d.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, c.name,d.name
  from route a 
join route b on a.company=b.company and a.num=b.num 
join stops c on c.id=a.stop 
join stops d on d.id=b.stop 
where c.name= 'Craiglockhart' and d.name='London Road'

7.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 join route b on a.num=b.num and a.company=b.company where a.stop=115 and b.stop=137

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

--方法1
select a.company, a.num from route a join route b on a.num=b.num and a.company=b.company join stops c on c.id=a.stop join stops d on d.id=b.stop where c.name='Craiglockhart' and d.name='Tollcross'
--方法2
SELECT R1.company, R1.num
  FROM route R1, route R2, stops S1, stops S2
  WHERE R1.num=R2.num AND R1.company=R2.company
    AND R1.stop=S1.id AND R2.stop=S2.id
    AND 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.

SELECT DISTINCT S2.name, R2.company, R2.num
FROM stops S1, stops S2, route R1, route R2
WHERE S1.name='Craiglockhart'
  AND S1.id=R1.stop  AND R1.company=R2.company AND R1.num=R2.num AND R2.stop=S2.id

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.

stops(id, name)
route(num, company, pos, stop)

SELECT m.num,m.company,name,n.num,n.company
FROM (SELECT a.num,a.company,b.stop FROM route a JOIN route b ON a.company=b.company AND a.num=b.num AND a.stop!=b.stop WHERE a.stop=(SELECT id FROM stops WHERE name='Craiglockhart')) AS m
JOIN (SELECT d.num,d.company,c.stop FROM route c JOIN route d ON c.company=d.company AND c.num=d.num AND c.stop!=d.stop WHERE d.stop=(SELECT id FROM stops WHERE name='Lochend')) AS n
ON  m.stop=n.stop
JOIN stops 
ON m.stop=stops.id
WHERE m.num!=n.num
ORDER BY m.num,stops.name,n.num;