SQLZOO刷题笔记

最近复习sql语句，发现很多不会了，然后在网上找到了一个很好联系SQL语句的网站 - sqlzoo.net，在上面可以很好地联系SQL的各种语句（主要是SELECT），两天刷完一遍后把答案记录下来了，感觉很多题不止一种实现方法，有些可以使用join连接或者使用select嵌套查询，感觉能出来答案就可以，有些题目还是比较困难的，有错误的话可以在评论区纠正一些

题目里面有中文选项，但是显示的是繁体中文，不太好辨认，最后直接用英文做了，所以题干也是英文，查找的时候可以对照一下题号

SELECT basics

SELECT basics - SQLZoo

1. Introducing the world table of countries

SELECT population FROM world
  WHERE name = 'Germany'

2. Scandinavia

SELECT name, population FROM world
  WHERE name IN ('Sweden', 'Norway', 'Denmark');

3. Just the right size

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

SELECT from WORLD Tutorial

SELECT from WORLD Tutorial - SQLZoo

1. Introduction

SELECT name, continent, population FROM world

2. Large Countries

SELECT name FROM world
WHERE population >= 200000000

3. Per capita GDP

SELECT name, GDP/population
FROM world
WHERE population >= 200000000

4. South America In millions

select name, population/ 1000000
from world
where continent = 'South America'

5. France, Germany, Italy

select name, population
from world
where name in ('France', 'Germany', 'Italy')

6. United

select name
from world
where name like '%United%'

7. Two ways to be big

select name, population, area
from world
where area > 3000000 or population > 250000000

8. One or the other (but not both)

select name, population, area
from world
where area > 3000000 Xor population > 250000000

9. Rounding

select name, Round(population/1000000, 2), ROUND(GDP/1000000000, 2)
from world
where continent = 'South America'

10. Trillion dollar economies

select name, Round(GDP/population, -3)
from world
where GDP >= 1000000000000

11. Name and capital have the same length

select name, capital
from world
where length(name) = length(capital)

12. Matching name and capital

select name, capital
from world
where left(name, 1) = left(capital, 1) and name <> capital

13. All the vowels

select name
from world
where name not like '% %' 
and lower(name) like '%a%'
and lower(name) like '%e%'
and lower(name) like '%i%'
and lower(name) like '%o%'
and lower(name) like '%u%'

SELECT from Nobel Tutorial

SELECT from Nobel Tutorial - SQLZoo

1. 1962 Literature

SELECT yr, subject, winner
  FROM nobel
 WHERE yr = 1950

2. 1962 Literature

SELECT winner
  FROM nobel
 WHERE yr = 1962
   AND subject = 'literature'

3. Albert Einstein

select yr, subject
from nobel
where winner = 'Albert Einstein'

4. Recent Peace Prizes

select winner 
from nobel
where yr >= 2000 and subject = 'peace'

5. Literature in the 1980’s

select *
from nobel
where subject = 'literature' and yr >= 1980 and yr <= 1989

6. Only Presidents

SELECT * FROM nobel
 WHERE winner in ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter', 'Barack Obama')

7. John

select winner
from nobel
where winner like 'John%'

8. Chemistry and Physics from different years

select *
from nobel
where (yr = 1980 and subject = 'physics') or (yr = 1984 and subject = 'chemistry')

9. Exclude Chemists and Medics

select *
from nobel
where yr = '1980' and subject not in ('chemistry', 'medicine')

10. Early Medicine, Late Literature

select *
from nobel
where (subject = 'medicine' and yr < 1910) or (subject = 'literature' and yr >= 2004)

11. Umlaut

select *
from nobel
where winner = 'PETER GRÜNBERG'

12. Apostrophe

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

13. Knights of the realm

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

14. Chemistry and Physics last

SELECT winner, subject
  FROM nobel
 WHERE yr=1984
 ORDER BY  subject IN ('physics','chemistry') , subject,winner

SELECT within SELECT Tutorial

SELECT within SELECT Tutorial - SQLZoo

1. Bigger than Russia

SELECT name FROM world
  WHERE population >
     (SELECT population FROM world
      WHERE name='Russia')

2. Richer than UK

Select name
from world
where continent = 'europe' 
and gdp/population > (select gdp/population from world where name = 'United Kingdom')

3. Neighbours of Argentina and Australia

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

4. Between Canada and Poland

select name, population
from world
where population > (select population from world where name = 'United Kingdom') and population < (select population from world where name = 'Germany')

5. Percentages of Germany

select name, concat(Round(100*population/(select population from world where name = 'germany'), 0), '%')
from world
where continent = 'europe'

6. Bigger than every country in Europe

select name 
from world
where gdp > ALL(select gdp from world where continent = 'europe' and gdp is not null)

7. Largest in each continent

SELECT continent, name, area FROM world x
  WHERE area>= ALL
    (SELECT area FROM world y
        WHERE y.continent=x.continent
          AND area>0)

8. First country of each continent (alphabetically)

select continent, name
from world t1
where name <= ALL(select name from world t2 where t1.continent=t2.continent )

9. Difficult Questions That Utilize Techniques Not Covered In Prior Sections

select name, continent, population
from world
where continent not in (select distinct continent from world where population > 25000000)

10. Three time bigger

select name, continent
from world t1
where population > ALL(select 3*population from world t2 where t1.continent = t2.continent and t1.name <> t2.name)

SUM and COUNT

SUM and COUNT - SQLZoo

1. Total world population

SELECT SUM(population)
FROM world

2. List of continents

select distinct(continent)
from world

3. GDP of Africa

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

4. Count the big countries

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

5. Baltic states population

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

6. Counting the countries of each continent

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

7. Counting big countries in each continent

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

8. Counting big continents

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

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

The nobel table can be used to practice more SUM and COUNT functions. - SQLZoo

1. Show the total number of prizes awarded.

SELECT COUNT(winner) FROM nobel

2. List each subject - just once

select distinct(subject)
from nobel

3. Show the total number of prizes awarded for Physics.

select count(winner)
from nobel
where subject = 'Physics'

4. For each subject show the subject and the number of prizes.

select subject, count(winner)
from nobel
group by subject

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

select subject, min(yr)
from nobel
group by subject

6. For each subject show the number of prizes awarded in the year 2000.

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

7. Show the number of different winners for each subject. Be aware that Frederick Sanger has won the chemistry prize twice - he should only be counted once.

select subject, count(distinct(winner))
from nobel
group by subject

8. For each subject show how many years have had prizes awarded.

select subject, count(distinct(yr))
from nobel
group by subject

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

select yr
from nobel
where subject = 'physics'
group by yr
having count(winner) = 3

10. Show winners who have won more than once.

select winner
from nobel
group by winner
having count(yr) > 1

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

select winner
from nobel
group by winner
having count(distinct(subject)) > 1

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

The JOIN operation

The JOIN operation - SQLZoo

1. show the matchid and player name for all goals scored by Germany

SELECT matchid, player 
FROM goal 
WHERE teamid = 'GER'

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

SELECT id,stadium,team1,team2
FROM game
where id = 1012

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

SELECT player, teamid,stadium, mdate
FROM game JOIN goal 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 game join goal on (id=matchid)
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 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 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 join game on id = matchid
where stadium = 'National Stadium, Warsaw'

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

select distinct(player)
from goal join game on (id = matchid)
where (team1 = 'GER' or team2 = 'GER') and teamid != 'GER'

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

SELECT teamname, count(*)
FROM eteam JOIN goal ON id=teamid
group by teamname

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

select stadium, count(*)
from game join goal on id = matchid
group by stadium

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

select matchid, mdate, count(*)
from game join goal on id = matchid
where team1 = 'POL' or team2 = 'POL'
group by matchid, mdate

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

select matchid, mdate, count(*)
from goal join game on id = matchid
where teamid = 'GER'
group by matchid, mdate

13. **List every match with the goals scored by each team as shown.

一直没通过，但是感觉和答案一样

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 JOIN goal ON matchid = id
group by mdate, team1, team2
order by mdate, matchid, team1, team2

Old JOIN Tutorial

Old JOIN Tutorial - SQLZoo

1. Show the athelete (who) and the country name for medal winners in 2000.

SELECT who, country.name
  FROM ttms JOIN country
         ON (ttms.country=country.id)
 WHERE games = 2000

2. Show the who and the color of the medal for the medal winners from ‘Sweden’.

select who, color
from ttms join country on ttms.country = country.id
where country.name = 'Sweden'

3. Show the years in which ‘China’ won a ‘gold’ medal.

select games
from ttms join country on ttms.country = country.id
where color = 'gold' and country.name = 'China'

4. Show who won medals in the ‘Barcelona’ games.

SELECT who
  FROM ttws JOIN games
            ON (ttws.games=games.yr)
  WHERE city = 'Barcelona'

5. show which city ‘Jing Chen’ won medals. Show the city and the medal color.

select city, color
from ttws JOIN games ON games = yr
where who = 'Jing Chen'

6. Show who won the gold medal and the city.

select who, city
from ttws JOIN games ON games = yr
where color = 'gold'

7. Show the games and color of the medal won by the team that includes ‘Yan Sen’.

select games, color
from ttmd join team on team = id
where name = 'Yan Sen'

8. Show the ‘gold’ medal winners in 2004.

select name
from team join ttmd on team = id
where color = 'gold' and games = 2004

9. Show the name of each medal winner country ‘FRA’.

select name 
from ttmd join team on team = id
where country = 'FRA'

More JOIN operations

More JOIN operations - SQLZoo

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

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

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

select name
from actor
where id in (select actorid from casting where movieid = 11768)

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

select name
from actor
where id in (select actorid from casting where movieid = (select id from movie where title = 'Alien'))

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

select title
from movie
where id in (select movieid from casting join actor on actorid = id where name = 'Harrison Ford')

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

select title
from movie
where id in (select movieid from casting join actor on actorid = id where name = 'Harrison Ford' and ord <> 1)

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

select title, name
from movie, actor, casting
where yr = 1962 and movie.id = casting.movieid and actor.id = casting.actorid and ord = 1

11. Which were the busiest years for ‘Rock Hudson’

SELECT yr,COUNT(title) FROM
  movie JOIN casting ON movie.id=movieid
        JOIN actor   ON actorid=actor.id
WHERE 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.

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

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

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

Using Null

Using Null - SQLZoo

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 teacher.name, dept.name
 FROM teacher INNER JOIN dept
           ON (teacher.dept=dept.id)

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

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

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

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

5. Show teacher name and mobile number or ‘07986 444 2266’

select name, coalesce(mobile, '07986 444 2266')
from teacher

6. print the teacher name and department name.

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

7. show the number of teachers and the number of mobile phones.

select count(*) 'count(teacher)', 
       sum(case when mobile is not null then 1 else 0 end) 'count(mobilephobe)'
from teacher

8. show each department and the number of staff.

select dept.name, 
       sum(case when teacher.name is not null then 1 else 0 end) teacherNum
from teacher right join dept on teacher.dept = dept.id
group by dept.name

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

select name, case when dept = 1 or dept =2 then 'Sci' else 'Art' end
from teacher

10. 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 = 1 or dept =2 then 'Sci' when dept = 3 then 'Art' else 'None' end
from teacher

Self join

Self join - SQLZoo

1. How many stops are in the database.

select count(*)
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.

select id, name 
from route join stops on id = stop
where num = 4 and company = 'LRT'

4. Add a HAVING clause to restrict the output to these two routes.

SELECT company, num, COUNT(*)
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num
having count(*) = 2

5. shows the services from Craiglockhart to London Road.

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. show the services between ‘Craiglockhart’ and ‘London Road’

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'

7. Give a list of all the services which connect stops 115 and 137

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

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

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

9. Give a distinct list of the stops which may be reached from ‘Craiglockhart’ by taking one bus,

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