SQLZOO练习（持更ing）

1）SELECT from WORLD Tutorial

12.Matching name and capital
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

13.All the vowels
Find the country that has all the vowels (a e i o u) and no spaces in its name.
• You can use the phrase name NOT LIKE ‘%a%’ to exclude characters from
your results.
• The query shown misses countries like Bahamas and Belarus because they contain at least one ‘a’.

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

NOTE：AND连接的每一段语句都有"name like"

2）SELECT from Nobel Tutorial

7.John
Show the winners with first name John

SELECT winner
FROM nobel
WHERE winner like 'John %'

SELECT winner
FROM nobel
WHERE LEFT(winner,4)='John'

8.Chemistry and Physics from different years
Show the year, subject, and name of Physics winners for 1980 together with the Chemistry winners for 1984.

SELECT *
FROM nobel
WHERE (subject='Physics' AND yr=1980) OR (subject= 'Chemistry' AND yr=1984)

NOTE：用括号括起某实体所有特征，表示优先级；两个实例间用OR连接；

13.Knights in order
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

NOTE：排序命令，默认升序

14.Chemistry and Physics last
Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.
• The expression subject IN (‘Chemistry’,‘Physics’) can be used as a value - it will be 0 or 1.

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

NOTE：排序的写法

3）SELECT within SELECT Tutorial

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

SELECT name,concat(ROUND(population/(SELECT population FROM world WHERE name='Germany')*100,0),'%')
FROM world
WHERE continent='Europe'

NOTE：百分号用引号括起；语句中查找Germany的人口数，不能直接用已知数据，以表中查询为准

6.Bigger than every country in Europe
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)

NOTE：子查询”GDP>0”作用，以防有国家GDP为空值，造成无法比较。

We can refer to values in the outer SELECT within the inner SELECT. We can name the tables so that we can tell the difference between the inner and outer versions.
关联子查询：将表通过命名为外部表和内部表，在内部表中使用外部表中的值。

7.Largest in each continent
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 >= ALL
    (SELECT area FROM world y
        WHERE y.continent=x.continent
          AND area>0)

NOTE：需要在同一个区域做比较

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

SELECT continent, name
FROM world as x
WHERE name in (SELECT min(name) FROM world as y
               WHERE y.continent=x.continent)

select continent, min(name)
FROM world
GROUP BY continent

9.Difficult Questions That Utilize Techniques Not Covered In Prior Sections
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
WHERE continent NOT IN (SELECT continent FROM world 
                        WHERE population>25000000)

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

SELECT name,continent
FROM world x
WHERE x.population/3 > (SELECT max(population) 
                        FROM world y 
                        WHERE x.continent=y.continent AND x.population<>y.population)

4）SUM and COUNT

7.Counting big countries in each continent
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

NOTE：先选择过滤（WHERE），再分组聚合（GROUP BY）

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

SELECT continent
FROM world
GROUP BY continent 
HAVING SUM(population) >100000000

NOTE：先分组聚合（GROUP BY），再选择过滤（HAVING）

• 聚合前选择条件，用WHERE；聚合后选择条件，用HAVING

5）The nobel table can be used to practice more SUM and COUNT functions.

5. For each subject show the first year that the prize was awarded.

SELECT subject,min(yr)
FROM nobel
GROUP BY subject

NOTE：最小年放在SELECT语句中

9. Show the years in which three prizes were given for Physics.

SELECT yr
FROM nobel
WHERE subject='Physics'
GROUP BY yr
HAVING COUNT(*)=3

11. Show winners who have won more than one subject.

SELECT winner
FROM nobel
GROUP BY winner
HAVING COUNT(DISTINCT subject)>=2

NOTE：使用distinct

12.GROUP BY yr, subject
Show the year and subject where 3 prizes were given. Show only years 2000 onwards.

select yr,subject
from nobel
where yr>=2000
group by yr,subject
having count(winner)=3

NOTE：GROUP BY语句内属性与SELECT语句中属性相同

6）The JOIN operation

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

• Select goals scored only by non-German players in matches where GER was the id of either team1 or team2.
• You can use teamid!=‘GER’ to prevent listing German players.
• You can use DISTINCT to stop players being listed twice.

SELECT DISTINCT player
  FROM game JOIN goal ON matchid = id 
    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 id,mdate,count(gtime)
#SELECT id,mdate,count(*)
FROM game JOIN goal ON matchid = id 
WHERE team1 LIKE 'POL' OR team2 LIKE 'POL'
GROUP BY id, mdate

13. List every match with the goals scored by each team as shown. Sort your result by mdate, matchid, team1 and team2.

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

7）More JOIN operations

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

SELECT DISTINCT movie.title, actor.name FROM 
   movie join casting on movie.id=casting.movieid
         join actor on casting.actorid=actor.id
WHERE movieid IN (SELECT movieid FROM 
                 casting join actor on actorid=id
                 WHERE actor.name='Julie Andrews')
AND ord=1

13.Actors with 30 leading roles
Obtain a list, in alphabetical order, of actors who’ve had at least 30 starring roles.

SELECT actor.name
FROM casting JOIN actor ON casting.actorid=actor.id
GROUP BY actor.name
HAVING SUM(CASE ord WHEN 1 THEN 1 ELSE 0 END)>=30
ORDER BY name

NOTE：
CASE WHEN condition1 THEN value1
WHEN condition2 THEN value2
ELSE def_value
END

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

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

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

8） Using Null

9. Using CASE.
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.id=1 or dept.id =2 then 'Sci'
     else 'Art'
     end
from teacher left join dept on teacher.dept=dept.id

NOTE：case语句放在select语句中

9）Self join

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

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'

Why it needs four tables？

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

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;

NOTE：存疑