These are my solutions for SQLZOO Tutorial.
HAPPY LEARNING!
- SELECT basics
- SELECT from WORLD
- SELECT from NOBEL
- SELECT in SELECT
- SUM and COUNT
- JOIN
- More JOIN
- Using NULL
- Self JOIN
Introducing the world table of countries.
1.Modify it to show the population of Germany
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;
- 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 as "per capita GDP"
FROM world
WHERE population >= 200000000;
4.South America In millions
SELECT name,population/1000000 as "Population"
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) as "Population",
round(gdp/1000000000,2) as "GDP"
FROM world
WHERE continent = 'South America';
10.Trillion dollar economies
SELECT name,round(gdp/population,-3) as "Per capita gdp"
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 LIKE '%a%'
AND name LIKE '%e%'
AND name LIKE '%i%'
AND name LIKE '%o%'
AND name LIKE '%u%'
AND name NOT LIKE '% %';
1.Winners from 1950
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 subject = 'Peace'
AND yr >= 2000;
5.Literature in the 1980's (Between is genrally inclusive,but not always!!!)
select yr,subject,winner
FROM nobel
WHERE subject = 'Literature'
AND yr between 1980 and 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 (Subject = 'Physics' and yr = 1980)
OR (Subject = 'Chemistry' and yr = 1984);
9.Exclude Chemists and Medics
SELECT * from nobel
FROM nobel
WHERE (subject = 'Medicine' and yr < 1910)
OR (subject = 'Literature' and yr >= 2004);
10.Early Medicine, Late Literature
SELECT *
FROM nobel
WHERE yr < 1910 AND subject = 'Medicine'
OR yr >= 2004 AND subject = 'Literature';
11.Umlaut
SELECT *
FROM nobel
WHERE winner like 'PETER GR%';
12.Apostrophe
SELECT *
FROM nobel
WHERE winner LIKE 'EUGENE O%';
12.Apostrophe (Alternative!)
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;
14.Chemistry and Physics last (Reffer to video {quite a catch!!} )
SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY subject in ('Physics','Chemistry'),subject,winner;
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 IN ('Argentina','Australia'));
ORDER BY name
4.Between Canada and Poland
SELECT name,population
FROM world
WHERE population > (SELECT population
FROM world
WHERE name = 'Canada')
AND population < (SELECT population
FROM world
WHERE name = 'Poland');
5.Percentages of Germany
SELECT name,
CONCAT(ROUND(population/(SELECT population
FROM world
WHERE name = 'Germany')*100,0),'%')
FROM world
WHERE continent = 'Europe';
6.Bigger than every country in Europe
SELECT name
FROM world
WHERE gdp > (SELECT max(gdp) FROM world
WHERE gdp > 0 AND continent = 'Europe');
6.Bigger than every country in Europe(Suggested Alternative!)
SELECT name
FROM world
WHERE gdp > ALL(SELECT gdp FROM world
WHERE gdp > 0 AND continent = 'Europe');
7.Largest in each continent
--This is an example of a self join!
SELECT continent, name, area
FROM world x
WHERE area >= ALL(SELECT area
FROM world y
WHERE y.continent=x.continent
AND area>0);
7.Largest in each continent (Alternative!)
SELECT continent, name, area
FROM world
WHERE area in (SELECT max(area)
FROM world
group by continent);
8.First country of each continent (alphabetically)
SELECT continent, name FROM world x
WHERE name <= ALL(SELECT name
FROM world y
WHERE y.continent=x.continent);
8.First country of each continent (alphabetically)[Alternative]
select continent,name from world
where name in (select min(name) from world group by continent );
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
AND y.population>0);
9.ALternative!!
SELECT name,continent,population
FROM world
WHERE continent in (SELECT continent
FROM world
group by continent
having max(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 population >= ALL(SELECT population*3
FROM world y
WHERE x.continent = y.continent
AND x.name != y.name);
1.Total world population
SELECT SUM(population) as "Total Population"
FROM world;
2.List all the continents - just once each.
SELECT DISTINCT continent
FROM world;
2.List all the continents - just once each.(Alternative)
SELECT continent
FROM world
group by continent;
3.GDP of Africa
SELECT SUM(gdp) as "GDP of Africa"
FROM world
WHERE continent = 'Africa';
4.Count the big countries
SELECT COUNT(name) as "Number of Countries"
FROM world
WHERE area >= 1000000;
5.Baltic states population
SELECT SUM(population) as "Total Population"
FROM world
WHERE name IN ('Estonia', 'Latvia', 'Lithuania');
6.Counting the countries of each continent
SELECT continent, COUNT(name) "No. of countries"
FROM world
GROUP BY continent;
7.Counting big countries in each continent
SELECT continent, COUNT(name) "Big countries" 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.
1.Show the total number of prizes awarded.
SELECT count(winner) "Total Prizes"
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) "No. of prizes"
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) "No. of Prizes"
FROM nobel
WHERE yr = 2000
group by subject;
7.Show the number of different winners for each subject.
SELECT subject,count(distinct winner) "Diff Winners"
FROM nobel
group by subject;
8.For each subject show how many years have had prizes awarded.
SELECT subject,count(distinct yr) "Years"
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(winner) > 1;
11.Show winners who have won more than once.
SELECT winner
FROM nobel
group by winner
having count( distinct subject) > 1;
--Dont forget the distinct!!
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;
JOIN and UEFA EURO 2012
1.Modify it to show the matchid and player name for all goals scored by Germany. To identify German players, check for: teamid = 'GER'
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.Modify it to 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';
3.The above query can be written across diffrent servers as follows:
SELECT go.player,go.teamid,gm.stadium,gm.mdate
FROM game gm
JOIN goal go ON (gm.id=go.matchid)
WHERE go.teamid = 'GER';
4.Show the team1, team2 and player for every goal scored by a player called Mario. Use 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';
--Notice that (team1=eteam.id) was used to join,as both 'game' and 'eteam' had columns named 'id';
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.Show the name of all players who scored a goal against Germany.
SELECT player
FROM game JOIN goal ON matchid = id
WHERE (team1='GER' OR team2='GER')
AND TEAMID != 'GER';
9.Show teamname and the total number of goals scored.
SELECT teamname,count(teamid) "goals"
FROM goal
JOIN eteam on (teamid = id)
group by teamname;
10.Show the stadium and the number of goals scored in each stadium.
SELECT stadium,count(gtime) "goals"
FROM goal
JOIN game on (matchid = id)
group by stadium;
11.For every match involving 'POL', show the matchid, date and the number of goals scored.
SELECT matchid,mdate,count(gtime) "goals"
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(teamid)
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.
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 matchid, mdate,team1,team2
order by mdate, matchid, team1 and team2;
--Don't forget the 'LEFT JOIN'!!This helps you select all the data in the table 1(here game),corresponding to which there may or may not be data in table 2(here goal)
1.Show the athelete (who) and the country name for medal winners in 2000.
SELECT who,name
FROM ttms
JOIN country on (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 (country = id)
WHERE name = 'Sweden';
3.Show the years in which 'China' won a 'gold' medal.
SELECT games
FROM ttms
JOIN country on (country = id)
WHERE name = 'China'
AND color = 'gold';
Women's Singles Table Tennis Olympics Database.
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 (ttws.games = games.yr)
WHERE who = 'Jing Chen';
6.Show who won the gold medal and the city.
SELECT who,city
FROM ttws
JOIN games ON (ttws.games = games.yr)
WHERE color = 'gold';
Table Tennis Mens Doubles.
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 (ttmd.team = team.id)
WHERE name = 'Yan Sen';
8.Show the 'gold' medal winners in 2004.
SELECT name
FROM ttmd
JOIN team on (ttmd.team = 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 ttmd.team = team.id
WHERE country = 'FRA';
1.List the films where the yr is 1962 (Show id, title)
SELECT id, title
FROM movie
WHERE yr=1962;
2.When was Citizen Kane released?
SELECT yr
FROM movie
WHERE title = 'Citizen Kane';
3.Star Trek movies
SELECT id,title,yr
FROM movie
WHERE title LIKE '%Star Trek%'
order by yr;
4.id for actor Glenn Close
SELECT id
FROM actor
WHERE name = 'Glenn Close';
5.id for Casablanca
SELECT id
FROM movie
WHERE title = 'Casablanca';
6.Cast list for Casablanca
SELECT name
FROM actor
JOIN casting on casting.actorid = actor.id
JOIN movie on movie.id = casting.movieid
WHERE movie.title = 'Casablanca' ;
--You could use 'movie.id = 11768' and get the above results!!
7.Alien cast list
SELECT name
FROM actor
JOIN casting on casting.actorid = actor.id
JOIN movie on movie.id = casting.movieid
WHERE title = 'Alien';
8.Harrison Ford movies
SELECT title
FROM movie
JOIN casting on casting.movieid = movie.id
JOIN actor on actor.id = casting.actorid
WHERE actor.name = 'Harrison Ford';
9.Harrison Ford as a supporting actor
SELECT title
FROM movie
JOIN casting on casting.movieid = movie.id
JOIN actor on actor.id = casting.actorid
WHERE casting.ord != 1
AND actor.name = 'Harrison Ford' ;
--You could use 'casting.ord > 1' and get the same result!
10.Lead actors in 1962 movies
SELECT title,actor.name
FROM movie
JOIN casting on casting.movieid = movie.id
JOIN actor on casting.actorid = actor.id
WHERE yr = 1962
AND casting.ord = 1;
11.Busy years for Rock Hudson
SELECT yr,COUNT(title) "No. of movies"
FROM movie
JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE actor.name='Rock Hudson'
GROUP BY yr
HAVING COUNT(title) > 2;
12.Lead actor in Julie Andrews movies
SELECT title,name
FROM movie
JOIN casting on (casting.movieid = movie.id)
JOIN actor on (actor.id = casting.actorid)
WHERE movie.id IN (SELECT movieid
FROM casting
WHERE actorid IN (SELECT id
FROM actor
WHERE name = 'Julie Andrews'))
AND casting.ord = 1;
13.Actors with 15 leading roles
SELECT actor.name
FROM casting
JOIN actor on (actor.id = casting.actorid)
WHERE ord = 1
group by actor.name
having count(ord) >=15
order by actor.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(ord) "total casting"
FROM movie
JOIN casting on casting.movieid = movie.id
WHERE yr = 1978
group by title
order by count(ord) desc, title;
15.List all the people who have worked with 'Art Garfunkel'.
SELECT actor.name
FROM actor
JOIN casting on casting.actorid = actor.id
JOIN movie on movie.id = casting.movieid
WHERE movie.id IN (SELECT movieid
FROM casting
WHERE actorid IN (SELECT id
FROM actor
WHERE name ='Art Garfunkel' ))
AND actor.name !='Art Garfunkel';
--the catch is the And statement in the last line!!
1.NULL, INNER JOIN, LEFT JOIN, RIGHT JOIN
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.Using the COALESCE function
SELECT name,COALESCE(mobile, '07986 444 2266') as "mobile"
FROM teacher;
6.Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string 'None' where there is no department.
SELECT teacher.name,COALESCE(dept.name,'None') as "dept"
FROM teacher
LEFT JOIN dept on (teacher.dept=dept.id);
7.Use COUNT to show the number of teachers and the number of mobile phones.
SELECT COUNT(name) "no. of teachers",count(mobile) "no.of 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 dept.name,count(teacher.name)
FROM teacher
RIGHT JOIN dept on (teacher.dept=dept.id)
group by dept.name;
9.Using CASE
SELECT teacher.name,
case WHEN dept = 1 or dept = 2 THEN 'Sci'
ELSE 'Art'
END as dept
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 = 1 or dept = 2 THEN 'Sci'
WHEN dept = 3 THEN 'Art'
ELSE 'None'
END as dept
FROM teacher;
Edinburgh Buses
1.How many stops are in the database.
SELECT DISTINCT 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 stops.id,stops.name
FROM route
LEFT JOIN stops on (stops.id = route.stop)
WHERE num = 4
order by company;
4.Routes and stops
SELECT company, num, COUNT(*) "no of stops"
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num
having COUNT(*) = 2;
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');
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';
SELECT a.company, a.num
FROM route a, route b
WHERE a.num = b.num AND (a.stop = 115 AND b.stop = 137)
group by a.company, a.num;
SELECT a.company,b.num
FROM route a ,route b
WHERE a.num = b.num and (a.stop = (SELECT id
FROM stops
WHERE name='Craiglockhart')
AND b.stop = (SELECT id
FROM stops
WHERE name='Tollcross'))
group by a.company,b.num;
SELECT name,a.company,a.num
FROM route a
JOIN route b on (a.num = b.num AND b.company = a.company)
JOIN stops on a.stop = stops.id
WHERE b.stop = 53;
SELECT a.num, a.company, stopb.name, c.num, c.company
FROM route a
JOIN route b ON (a.company = b.company AND a.num = b.num)
JOIN (route c JOIN route d ON (c.company = d.company AND c.num = d.num))
JOIN stops stopa ON a.stop = stopa.id
JOIN stops stopb ON b.stop = stopb.id
JOIN stops stopc ON c.stop = stopc.id
JOIN stops stopd ON d.stop = stopd.id
WHERE stopa.name = 'Craiglockhart'
AND stopd.name = 'Sighthill'
AND stopb.name = stopc.name
ORDER BY LENGTH(a.num), b.num, stopb.name, LENGTH(c.num), d.num;