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

select continent,name from world a
where a.name <= all(select b.name from world b where a.continent=b.continent)
order by name


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 a.name,a.continent,a.population from world a
where 25000000>= all(select b.population from world b where a.continent=b.continent )


SUM and COUNT

1. Show the total population of the world.

SELECT sum(population) FROM world


2. List all the continents - just once each.

select distinct continent from world


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

select sum(population) from world
where name in(France,Germany,Spain)


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


The JOIN operation

1.Modify it to 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.The code below shows the player (from the goal) and stadium name (from the game table) for every goal scored.

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

SELECT b.player, b.teamid, a.stadium, a.mdate FROM game a JOIN goal b ON (a.id=b.matchid)
WHERE b.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 the dates of the matches and the name of the team in which Fernando Santos was the team1 coach.

select b.mdate,a.teamname from eteam a join game b on b.team1=a.id
where a.coach=Fernando Santos


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

select player from ((eteam a join game b on a.id=b.team1) join goal c on b.id=c.matchid )
where b.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 matchid = id
WHERE (teamid = team1 AND team2 = GER) OR (teamid = team2 AND team1 = GER)


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

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


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

select stadium,count(player) 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(team1) FROM game JOIN goal ON matchid = id
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(player) FROM game JOIN goal ON matchid = id
WHERE teamid = GER
group by matchid,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.

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


More JOIN operations

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 actress Glenn Close have?

select title from movie where id in(11768,11955,21191)


5.What is the id of the film Casablanca

SELECT id FROM actor WHERE name = Glenn Close;


6. What is the id of the film Casablanca

select id from movie where title=Casablanca


7.Obtain the cast list for Casablanca. Use movieid=11768, this is the value that you obtained in the previous question.

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


8.Obtain the cast list for the film Alien

select name from (movie m join casting c on m.id=c.movieid) join actor a on a.id=c.actorid
WHERE title = Alien


9.List the films in which Harrison Ford has appeared

select title from (movie m join casting c on m.id=c.movieid) join actor a on a.id=c.actorid
where name=Harrison Ford


10.List the films where Harrison Ford has appeared - but not in the starring role.

select title from (movie m join casting c on m.id=c.movieid) join actor a on a.id=c.actorid
where name=Harrison Ford and ord <>1


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

select title,name from (movie m join casting c on m.id=c.movieid) join actor a on a.id=c.actorid
where yr=1962 and ord=1


13. 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 a.id=c.actorid
where c.movieid in(select movieid from casting join actor on id=actorid where name =Julie Andrews)
and ord=1


14.Obtain a list, in alphabetical order, of actors whove had at least 30 starring roles.

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


16. List all the people who have worked with Art Garfunkel.

select name from actor a join casting c on a.id=c.actorid
where movieid in
(select movieid from casting join actor on id= actorid where name = Art Garfunkel)
and name<>Art Garfunkel


Using Null

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

select name from teacher where dept is null


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 teachers are listed.

SELECT teacher.name, dept.name FROM teacher right JOIN dept ON (teacher.dept=dept.id)


5.Use COALESCE to print the mobile number. Use the number 07986 444 2266 there is no number given. Show teacher name and mobile number or 07986 444 2266

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


6.Use the COALESCE function and a LEFT JOIN to print the 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 teacher t right join dept d on t.dept=d.id group by d.name


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

select name,(case when dept in(1,2) then Scielse Art end) from teacher


10. Use CASE to show the name of each teacher followed by Sci if the the teacher is in dept 1 or 2 show Art if the dept is 3 and None otherwise.

SELECT
teacher.name,
(CASE WHEN teacher.dept IN (1, 2) THEN Sci WHEN teacher.dept = 3 THEN Art ELSE None END)
FROM teacher;


推荐阅读：