SQLZoo 题目参考代码

Mar 20, 2016


SQLZOO:SELECT basics

1

select population from world where name='Germany'

2

select name, gdp/population from world where area > 5000000

3

select name, population from world
    where name in ('Ireland', 'Iceland', 'Denmark')

4

SELECT name,
       area
FROM   world
WHERE  area BETWEEN 200000 AND 250000

SQLZOO: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 IN ( ‘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 (area >= 3000000 OR population > 250000000) AND NOT area >= 3000000 AND population > 250000000


## 9

SELECT name, ROUND(population / 1000000, 2), ROUND(GDP / 1000000000, 2) FROM world WHERE continent = ‘South America’


## 10

SELECT name, round(gdp / population, -3) FROM world WHERE GDP >= 1000000000000


## 11

SELECT name, CASE 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 IN ( ‘Eurasia’, ‘Turkey’ ) THEN ‘Europe/Asia’ WHEN continent = ‘Oceania’ THEN ‘Australasia’ WHEN continent = ‘Caribbean’ THEN CASE WHEN name like ‘B%’ THEN ‘North America’ ELSE ‘South America’ END else continent END FROM world ORDER BY name ASC

# 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 yr >= 2000 AND subject = ‘Peace’


## 5

SELECT yr, subject, winner FROM nobel WHERE subject = ‘Literature’ AND yr BETWEEN 1980 AND 1989


## 6

SELECT * FROM nobel WHERE winner IN (‘Theodore Roosevelt’, ‘Woodrow Wilson’, ‘Jimmy Carter’)


## 7

SELECT winner FROM nobel WHERE winner LIKE ‘John %’


## 8

SELECT * FROM nobel WHERE yr = 1984 AND subject = ‘Chemistry’ OR yr = 1980 AND subject = ‘Physics’


## 9

SELECT * FROM nobel WHERE yr = 1980 AND subject NOT IN (‘Chemistry’, ‘Medicine’)


## 10

SELECT * FROM nobel WHERE yr < 1910 AND subject = ‘Medicine’ OR yr >= 2004 AND subject = ‘Literature’


## 11

SELECT * FROM nobel WHERE winner = ‘Peter Grünberg’


## 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 ASC
```

## 14

SELECT winner, subject FROM nobel WHERE yr = 1984 ORDER BY subject IN (‘Chemistry’, ‘Physics’) ASC, 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 gdp / population > ( SELECT gdp / population FROM world WHERE name = ‘United Kingdom’ ) AND continent = ‘Europe’


## 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 BETWEEN ( SELECT population + 1 FROM world WHERE name = ‘Canada’ ) AND ( SELECT population - 1 FROM world WHERE name = ‘Poland’ )


## 5

SELECT name, CONCAT(round(100 * population / ( SELECT population FROM world WHERE name = ‘Germany’ ), 0), ‘%’) FROM world WHERE continent = ‘Europe’


## 6

SELECT name FROM world WHERE gdp > ALL (SELECT gdp FROM world WHERE gdp > 0 AND continent = ‘Europe’)


## 7

SELECT continent, name, area FROM world x WHERE area >= ALL (SELECT area FROM world y WHERE x.continent = y.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 ASC)


## 9

SELECT population FROM world y WHERE world.continent = y.continent


## 10

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)


# SUM and COUNT

## 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 (‘France’, ‘Germany’, ‘Spain’)


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


# The JOIN operation

## 2

SELECT DISTINCT id, stadium, team1, team2 FROM goal, game WHERE goal.matchid = ‘1012’ AND goal.matchid = game.id



## 3

SELECT player, teamid, stadium, mdate FROM game JOIN goal ON goal.teamid = ‘GER’ AND goal.matchid = game.id


## 4

SELECT team1, team2, player FROM game JOIN goal ON goal.player LIKE ‘Mario%’ AND goal.matchid = game.id


## 5

SELECT player, teamid, coach, gtime FROM goal JOIN eteam ON eteam.id = goal.teamid AND goal.gtime <= 10


## 6

SELECT mdate, teamname FROM game JOIN eteam ON eteam.coach = ‘Fernando Santos’ AND game.team1 = eteam.id


## 7

SELECT player FROM game JOIN goal ON stadium = ‘National Stadium, Warsaw’ AND matchid = id


## 8

SELECT DISTINCT player FROM game JOIN goal ON matchid = id AND teamid != ‘GER’ AND (team1 = ‘GER’ OR team2 = ‘GER’)


## 9

SELECT teamname, COUNT(*) FROM goal JOIN eteam ON teamid = id GROUP BY teamname ``

10

SELECT stadium, COUNT(*)
FROM goal JOIN game ON matchid = id
AND (teamid = team1
	OR teamid = team2)
GROUP BY stadium

11

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

12

SELECT matchid, mdate, COUNT(*)
FROM goal JOIN game ON matchid = id
AND teamid = 'GER'
GROUP BY matchid

More JOIN operations

7

SELECT name
FROM actor JOIN casting ON actorid = id
WHERE movieid = 11768

8

SELECT name
FROM movie JOIN casting ON movieid = movie.id JOIN actor ON actor.id = actorid
WHERE title = 'Alien'

9

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

11

SELECT title, name
FROM movie JOIN casting ON movie.id = movieid
AND ord = 1 JOIN actor ON actor.id = actorid
WHERE yr = 1962

12

SELECT yr, COUNT(1)
FROM actor JOIN casting ON id = actorid JOIN movie ON movie.id = movieid
WHERE name = 'John Travolta'
GROUP BY yr
HAVING COUNT(1) > 2

13

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

14

SELECT name
FROM movie JOIN casting ON movieid = id JOIN actor ON actor.id = actorid
WHERE ord = 1
GROUP BY name
HAVING COUNT(1) >= 30
ORDER BY name

15

这里已经没错了,但是标准答案给出的排序条件不完全,所以一直对不上。

SELECT title, COUNT(1)
FROM movie JOIN casting ON id = movieid
WHERE yr = 1978
GROUP BY movieid
ORDER BY COUNT(1) DESC

16

SELECT DISTINCT name
FROM movie JOIN casting ON id = movieid JOIN actor ON actorid = actor.id
WHERE movieid IN (SELECT movieid
		FROM movie JOIN casting ON id = movieid JOIN actor ON actorid = actor.id
		WHERE name = 'Art Garfunkel')
	AND name != 'Art Garfunkel'