sqlzoo-solutions.md

🗄️ SQLZoo Problem Solutions

---

0 SELECT basics

1. Show the population of Germany

SELECT population
FROM world
WHERE name = 'Germany';

2. Show the name and the population for 'Sweden', 'Norway' and 'Denmark'.

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

3. show the country and the area for countries with an area between 200,000 and 250,000.

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

---

1 SELECT name

1. Find the country that start with Y.

SELECT name 
FROM world
WHERE UPPER(name) LIKE 'Y%';

2. Find the countries that end with y.

SELECT name 
FROM world
WHERE UPPER(name) LIKE '%Y';

3. Find the countries that contain the letter x.

SELECT name
FROM world
WHERE UPPER(name) LIKE '%X%';

4. Find the countries that end with land.

SELECT name 
FROM world
WHERE UPPER(name) LIKE '%LAND';

5. Find the countries that start with C and end with ia.

SELECT name 
FROM world
WHERE UPPER(name) LIKE 'C%IA'
ORDER BY 1; 

6. Find the country that has oo in the name.

SELECT name
FROM world
WHERE UPPER(name) LIKE '%OO%';

7. Find the countries that have three or more a in the name.

SELECT name 
FROM world
WHERE UPPER(name) LIKE '%A%A%A%';

8. Find the countries that have "t" as the second character.

SELECT name
FROM world
WHERE UPPER(name) LIKE '_T%'
ORDER BY name;

9. Find the countries that have two "o" characters separated by two others.

SELECT name
FROM world
WHERE UPPER(name) LIKE '%O__O%';

10. Find the countries that have exactly four characters.

SELECT name
FROM world
WHERE UPPER(name) LIKE '____';

11. Find the country where the name is the capital city.

SELECT name
FROM world
WHERE name = capital;

12. Find the country where the capital is the country plus "City".

SELECT name
FROM world
WHERE UPPER(capital) = Concat(UPPER(name), ' CITY');

13. Find the capital and the name where the capital includes the name of the country.

SELECT capital, name 
FROM world 
WHERE UPPER(capital) LIKE UPPER(CONCAT('%', name, '%'));

14. Find the capital and the name where the capital is an extension of name of the country.

SELECT capital, name 
FROM world 
WHERE UPPER(capital) LIKE UPPER(CONCAT(name, '_%'));

15. Show the name and the extension where the capital is a proper (non-empty) extension of name of the country.

SELECT name, REPLACE(capital, name, '') AS extension
FROM world 
WHERE UPPER(capital) LIKE UPPER(CONCAT(name, '_%'));

---

2 SELECT from World

1. Observe the result of running this SQL command to show the name, continent and population of all countries.

SELECT name, continent, population
FROM world;

2. Show the name for the countries that have a population of at least 200 million.

SELECT name 
FROM world
WHERE population > 200000000;

3. Give the name and the per capita GDP for countries with a population of at least 200 million.

SELECT name, (gdp/population) AS gdp_per_capita
FROM world
WHERE population > 200000000;

4. Show the name and population in millions for the countries of South America.

SELECT name, population/1000000 AS population_in_millions
FROM world
WHERE continent = 'South America';

5. Show the name and population for France, Germany, and Italy.

SELECT name, population
FROM world
WHERE name IN ('France', 'Germany', 'Italy');

6. Show the countries which have a name that includes the word United.

SELECT name
FROM world
WHERE UPPER(name) LIKE '%UNITED%';

7. Show the countries that are big by area or population.

SELECT name, population, area
FROM world
WHERE area > 3000000 
OR population > 250000000; 

8. Show the countries that are big by area or population, but not both.

SELECT name, population, area
FROM world
WHERE 
area > 3000000 AND population < 250000000
OR
area < 3000000 AND population > 250000000
; 

9. Show the name, population, and GDP for South American countries, rounded appropriately.

SELECT name, 
ROUND(population/1000000,2) AS population_in_millions,
ROUND(gdp/1000000000,2) AS gdp_in_billions
FROM world
WHERE continent = 'South America';

10. Show per-capita GDP for trillion dollar countries, rounded to the nearest 1000.

SELECT name, 
ROUND(gdp/population,-3) AS gdp_per_capita_in_thousands
FROM world
WHERE gdp > 1000000000000;

11. Show the name and capital where the name and capital have the same number of characters.

SELECT name, capital
FROM world
WHERE LENGTH(name) = LENGTH(capital);

12. Show the name and capital where the first letters match but the name and capital are different.

SELECT name, capital
FROM world
WHERE name <> capital 
AND LEFT(name, 1) = LEFT(capital, 1);

13. Find countries that contain all vowels and no spaces in their name.

SELECT name
FROM world
WHERE UPPER(name) LIKE '%A%'
AND UPPER(name) LIKE '%E%'
AND UPPER(name) LIKE '%I%'
AND UPPER(name) LIKE '%O%'
AND UPPER(name) LIKE '%U%'
AND UPPER(name) NOT LIKE '% %';

---

3 SELECT from Nobel

1. Show Nobel prizes for 1950.

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

2. Show the winner of the 1962 Literature prize.

SELECT winner
FROM nobel
WHERE yr = 1962
AND UPPER(subject) = 'LITERATURE';

3. Show the year and subject for Albert Einstein.

SELECT yr, subject
FROM nobel
WHERE winner = 'Albert Einstein';

4. Show Peace prize winners since 2000.

SELECT winner
FROM nobel
WHERE UPPER(subject) = 'PEACE' 
AND yr >= 2000;

5. Show Literature prize winners from 1980 to 1989.

SELECT yr, subject,winner
FROM nobel
WHERE UPPER(subject) = 'LITERATURE' 
AND yr BETWEEN 1980 AND 1989;

6. Show prize details for selected US presidents.

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

7. Show winners with first name John.

SELECT winner
FROM nobel
WHERE UPPER(winner) LIKE ('JOHN%');

8. Show Physics winners from 1980 plus CHemistry winners from 1984.

SELECT yr, subject, winner
FROM nobel
WHERE 
(UPPER(subject) = 'PHYSICS' AND yr = 1980)
OR
(UPPER(subject) = 'CHEMISTRY' AND yr = 1984);

9. Show winners from 1980 excluding Chemistry and Medicine.

SELECT yr, subject, winner
FROM nobel
WHERE yr = 1980
AND subject NOT IN ('Chemistry','Medicine');

10. Show early Medicine winners and recent Literature winners.

SELECT yr, subject, winner
FROM nobel
WHERE
(UPPER(subject) = 'MEDICINE' AND yr < 1910)
OR
(UPPER(subject) = 'LITERATURE' AND yr >= 2004);

11. Find the winner with special characters in the name.

SELECT *
FROM nobel
WHERE UPPER(winner) = 'PETER GRÜNBERG';

12. Find the winner with an apostrophe in the name.

SELECT *
FROM nobel
WHERE UPPER(winner) = "EUGENE O'NEILL";

13. Show winners whose name starts with Sir.

SELECT winner, yr, subject
FROM nobel
WHERE UPPER(winner) LIKE 'SIR%'
ORDER BY yr DESC, winner;

14. Show 1984 winners ordered by subject (But physics and chemistry should be last) and winner. Rare Tricky Concept

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

---

4 SELECT within SELECT

1. List countries with population greater than Russia.

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

2. Show countries in Europe with per-capita GDP greater than the United Kingdom.

SELECT name 
FROM world
WHERE continent = 'Europe'
AND gdp/population > 
(
SELECT gdp/population 
FROM world
WHERE name='United Kingdom'
);

3. List countries in the same continents as Argentina or Australia.

SELECT name, continent
FROM world
WHERE continent IN
(
SELECT continent
FROM world
WHERE name IN ('Argentina', 'Australia')
)
ORDER BY name;

4. Show countries with population greater than Canada but less than 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. Show European countries with population as a percentage of Germany.

SELECT name, CONCAT(ROUND(population*100/(SELECT population FROM world WHERE name = 'GERMANY'),0),'%') AS percentage
FROM world
WHERE continent = 'Europe'
ORDER BY name

6. Show countries with GDP greater than every country in Europe.

SELECT name
FROM world
WHERE gdp > 
(
SELECT MAX(gdp)
FROM world
WHERE continent = 'Europe'
)
AND gdp is NOT NULL;

7. Find the largest country by area in each continent.

WITH cte AS
(
SELECT continent, name, area, RANK() OVER(PARTITION BY continent ORDER BY area DESC) AS area_rnk
FROM world
)

SELECT continent, name, area
FROM cte
WHERE area_rnk = 1
order by name;

8. List the first country alphabetically in each continent.

WITH cte AS
(
SELECT continent, name, RANK() OVER(PARTITION BY continent ORDER BY name) AS name_rnk
FROM world
)

SELECT continent, name
FROM cte
WHERE name_rnk = 1
order by continent;

9. Find countries from continent where every country has population less than or equal to 25 million.

SELECT name, continent, population
FROM world
WHERE continent IN
(
SELECT continent
FROM world
GROUP BY continent
HAVING MAX(population) <= 25000000
);

10. Find countries with population more than three times that of any other country in the same continent.

WITH cte AS
(
SELECT continent, name, population, RANK() OVER(PARTITION BY continent ORDER BY population DESC) AS p_rnk
FROM world
),

cte1 AS
(
SELECT *
FROM cte
WHERE p_rnk = 1
),

cte2 AS
(
SELECT *
FROM cte
WHERE p_rnk = 2
)

SELECT a.name, a.continent
FROM cte1 a JOIN cte2 b ON a.continent = b.continent
WHERE a.population > 3 * b.population
ORDER BY name;

---

5 SUM and COUNT

1. Show the total population of the world.

SELECT SUM(population)
FROM world;

2. List all continents only once.

SELECT continent
FROM world
GROUP BY 1;

3. Show the total GDP of Africa.

SELECT SUMpopulation(gdp)
FROM world
WHERE continent = 'Africa';

4. Count the countries with area of at least 1,000,000.

SELECT COUNT(name)
FROM world
WHERE area >= 1000000;

5. Show the total population of Estonia, Latvia, and Lithuania.

SELECT SUM(population)
FROM world
WHERE name IN ('Estonia', 'Latvia', 'Lithuania');

6. For each continent, show the continent and number of countries.

SELECT continent, COUNT(name)
FROM world
GROUP BY 1;

7. For each continent, show the continent and number of countries with population of at least 10 million.

SELECT continent, COUNT(name)
FROM world
WHERE population >= 10000000
GROUP BY 1;

8. Show continents with total population of at least 100 million.

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

---

6 JOIN

1. Show match id and player name for goals scored by Germany.

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

2. Show id, stadium, team1, and team2 for a given game.

SELECT id,stadium,team1,team2
FROM game
WHERE id = 1012;

3. Show player, team id, stadium, and date for German goals.

SELECT t2.player, t2.teamid, t1.stadium, t1.mdate
FROM game t1 JOIN goal t2 ON t1.id = t2.matchid
WHERE t2.teamid = 'GER';

4. Show team1, team2, and player for goals by a specific player.

SELECT t1.team1, t1.team2, t2.player
FROM game t1 JOIN goal t2 ON t1.id = t2.matchid
WHERE UPPER(t2.player) LIKE ('%MARIO%');

5. Show player, team id, coach, and goal time for goals scored in the first 10 minutes.

SELECT g.player, g.teamid, e.coach, g.gtime
FROM goal g JOIN eteam e ON g.teamid = e.id
WHERE g.gtime <= 10;

6. Show match dates and team names where a specific coach was involved.

SELECT g.mdate, e.teamname
FROM game g JOIN eteam e ON g.team1 = e.id
WHERE coach = 'Fernando Santos';

7. Show players who scored in a specific stadium.

SELECT t2.player
FROM game t1 JOIN goal t2 ON t1.id = t2.matchid
WHERE t1.stadium = 'National Stadium, Warsaw';

8. Show players who scored against Germany.

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

9. Show team name and total goals scored by each team.

SELECT e.teamname, count(g.teamid) AS numberofgoals
FROM goal g
JOIN eteam e ON g.teamid = e.id
GROUP BY 1;

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

SELECT t1.stadium, count(t2.teamid) AS numberofgoals
FROM game t1 JOIN goal t2 ON t1.id = t2.matchid
GROUP BY 1;

11. Show match id, date, and number of goals for matches involving a specific team.

SELECT t2.matchid, t1.mdate, count(t2.teamid) AS numberofgoals
FROM game t1 JOIN goal t2 ON t1.id = t2.matchid
WHERE (t1.team1 = 'POL' OR t1.team2 = 'POL')
GROUP BY 1,2;

12. Show match id, date, and number of goals scored by Germany.

SELECT t2.matchid, t1.mdate, count(t2.teamid) AS numberofgoals
FROM game t1 JOIN goal t2 ON t1.id = t2.matchid
WHERE t2.teamid = 'GER'
GROUP BY 1,2;

13. Show every match with team names and score.

SELECT t1.mdate, 
t1.team1, SUM(CASE WHEN t2.teamid = t1.team1 THEN 1 ELSE 0 END) AS score1,
t1.team2, SUM(CASE WHEN t2.teamid = t1.team2 THEN 1 ELSE 0 END) AS score2
FROM game t1 LEFT JOIN goal t2 ON t1.id = t2.matchid
WHERE (t1.team1 = 'ENG' OR t1.team2 = 'ENG')
GROUP BY 1,2,4
ORDER BY t1.mdate, t1.id, t1.team1, t1.team2;

---

7 More JOIN operations

1. List the films released in 1962.

SELECT id, title
FROM movie
WHERE yr=1962 AND budget > 2000000;

2. Show the year when Citizen Kane was released.

SELECT yr
FROM movie
WHERE UPPER(title) = 'CITIZEN KANE';

3. List all Star Trek movies, including id, title, and year.

SELECT id, title, yr
FROM movie
WHERE UPPER(title) LIKE 'STAR TREK%'
ORDER BY yr;

4. Find the id number for actor Glenn Close.

SELECT id
FROM actor
WHERE name = 'Glenn Close';

5. Find the id number for the film Casablanca.

SELECT id
FROM movie
WHERE title = 'Casablanca' AND yr = 1942;

6. Show the cast list for Casablanca.

SELECT a.name
FROM actor a 
JOIN casting c ON a.id = c.actorid
JOIN movie m ON c.movieid = m.id
WHERE m.title = 'Casablanca' AND m.yr = 1942;

7. Show the cast list for Alien.

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

8. List the films in which Harrison Ford appeared.

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

9. List films where Harrison Ford appeared but was not the starring actor.

SELECT m.title
FROM actor a 
JOIN casting c ON a.id = c.actorid
JOIN movie m ON c.movieid = m.id
WHERE a.name = 'Harrison Ford'
AND c.ord != 1;

10. Lead actors in 1962 movies.

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

11. Busy years for Rocky Hudson

SELECT m.yr, COUNT(m.title) AS numberofmovies
FROM actor a 
JOIN casting c ON a.id = c.actorid
JOIN movie m ON c.movieid = m.id
WHERE a.name = 'Rock Hudson'
GROUP BY 1
HAVING numberofmovies > 2;

12. List lead actors in Julie Andrews films.

SELECT m.title, a.name 
FROM actor a 
JOIN casting c ON a.id = c.actorid
JOIN movie m ON c.movieid = m.id
WHERE c.ord = 1
AND m.id IN 
(
SELECT m1.id 
FROM actor a1 
JOIN casting c1 ON a1.id = c1.actorid
JOIN movie m1 ON c1.movieid = m1.id
WHERE a1.name = 'Julie Andrews'
);

13. Find actors with at least 15 starring roles.

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

14. List films released in 1978 ordered by cast size.

SELECT m.title, COUNT(a.id) AS numberofactors
FROM actor a 
JOIN casting c ON a.id = c.actorid
JOIN movie m ON c.movieid = m.id
WHERE m.yr = 1978
GROUP BY 1
ORDER BY COUNT(a.id) DESC, m.title;

15. List people who worked with Art Garfunkel.

SELECT a.name 
FROM actor a 
JOIN casting c ON a.id = c.actorid
JOIN movie m ON c.movieid = m.id
WHERE m.id IN 
(
SELECT m1.id 
FROM actor a1 
JOIN casting c1 ON a1.id = c1.actorid
JOIN movie m1 ON c1.movieid = m1.id
WHERE a1.name = 'Art Garfunkel'
)
AND a.name NOT IN ('Art Garfunkel');

---

8 Using Null

1. List teachers who have NULL for their department.

SELECT t.name
FROM teacher t 
LEFT JOIN dept d ON t.dept = d.id
WHERE d.name IS NULL;

2. Use INNER JOIN to show teacher and department details.

SELECT t.name, d.name
FROM teacher t
INNER JOIN dept d ON t.dept = d.id;

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

SELECT t.name, d.name
FROM teacher t
LEFT JOIN dept d ON t.dept = d.id;

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

SELECT t.name, d.name
FROM teacher t
RIGHT JOIN dept d ON t.dept = d.id;

5. Use COALESCE to show mobile number or default value.

SELECT name, COALESCE(mobile, '07986 444 2266')
FROM teacher;

6. Use the COALESCE function and a LEFT JOIN to print the teacher name and department name.

SELECT t.name, COALESCE(d.name, 'None') AS dept_name
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(t.name) AS numberofteachers, COUNT(t.mobile) AS mobilephones
FROM teacher t;

8. Count the number of teachers by department.

SELECT d.name, COUNT(t.name) AS numberofteachers
FROM teacher t
RIGHT JOIN dept d ON t.dept = d.id
GROUP BY 1;

9. Use CASE to classify teachers by department.

SELECT t.name, 
CASE 
WHEN d.id <3 THEN 'Sci' 
ELSE 'Art' 
END AS dept
FROM teacher t
LEFT JOIN dept d ON t.dept = d.id;

10. Use CASE to classify teachers into custom groups.

SELECT t.name, 
CASE 
WHEN d.id <3 THEN 'Sci' 
WHEN d.id = 3 THEN 'Art' 
ELSE 'None' 
END AS dept
FROM teacher t
LEFT JOIN dept d ON t.dept = d.id;

---

8+ Numeric Examples

1. Show the the percentage who STRONGLY AGREE

SELECT A_STRONGLY_AGREE
FROM nss
WHERE question = 'Q01'
AND institution = 'Edinburgh Napier University'
AND subject = '(8) Computer Science';

2. Calculate how many agree or strongly agree

SELECT institution, subject
FROM nss
WHERE score >= 100
AND question = 'Q15';

3. Unhappy Computer Students

SELECT institution, score
FROM nss
WHERE question='Q15'
AND subject='(8) Computer Science'
AND score < 50;

4. More Computing or Creative Students?

SELECT subject, SUM(response) AS totalnumberofresponses
FROM nss
WHERE question = 'Q22'
AND subject IN ('(8) Computer Science', '(H) Creative Arts and Design')
GROUP BY 1;

5. Strongly Agree Numbers

SELECT subject, SUM((response * A_STRONGLY_AGREE) / 100) AS totalnumberofresponses
FROM nss
WHERE question = 'Q22'
AND subject IN ('(8) Computer Science', '(H) Creative Arts and Design')
GROUP BY 1;

6. Strongly Agree, Percentage

SELECT subject, ROUND(SUM(A_STRONGLY_AGREE*response)/sum(response),0) AS stronglyagreeperc
FROM nss
WHERE question = 'Q22'
AND subject IN ('(8) Computer Science', '(H) Creative Arts and Design')
GROUP BY 1;

7. Scores for Institutions in Manchester

SELECT institution, ROUND(SUM(score*response)/sum(response),0) AS score
FROM nss
WHERE question = 'Q22'
AND institution LIKE ('%Manchester%')
GROUP BY 1
ORDER BY institution;

8. Number of Computing Students in Manchester

SELECT institution, SUM(sample) AS sample,
SUM(CASE WHEN subject = '(8) Computer Science' THEN sample ELSE 0 END) AS compstudents
FROM nss
WHERE question = 'Q01'
AND institution LIKE ('%Manchester%')
GROUP BY 1;

---

9- Window function

1. Show basic election results for a selected constituency and year.

2. Show party and votes for a selected constituency.

3. Use RANK to rank candidates within a constituency.

4. Show ranking results for a selected constituency.

5. Show winning candidates for selected constituencies.

6. Count seats won by each party.

---

9+ COVID 19

1. Show COVID data for a selected country.

2. Show confirmed cases for a selected country and date range.

3. Show daily new cases using window functions.

4. Show weekly changes using window functions.

5. Show percentage increases in confirmed cases.

6. Find peak daily increases by country.

7. Compare countries using confirmed cases and deaths.

8. Rank countries by selected COVID metric.

---

9 Self join

1. Find the stop id for Craiglockhart.

2. Find the stops used by selected services.

3. Show routes involving selected stops.

4. Find services connecting two selected stops.

5. Show services from one stop to another.

6. Find routes from Craiglockhart to London Road.

7. Show routes involving two named stops.

8. Find two-bus routes from one stop to another.

9. Show transfer points for two-bus journeys.

10. Show complete two-bus journey details.