Overview

SQLZOO is a site where you can learn SQL for free through hands-on practice.

I tried the SELECT in SELECT and JOIN sections of SQL Zoo and noted down things I didn't know.

SELECT in SELECT

6. Bigger than every country in Europe

The ALL clause

Using the keyword ALL, you can apply comparison operators >=, >, <, <= to all elements of a list.

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

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

This was difficult...

Find continents where all countries have a population of 25000000 or less, and display the name, continent, and population of countries in those continents.

SELECT
 name,continent,population
FROM
 world A
WHERE
 25000000>=ALL(SELECT population
               FROM world B
               WHERE B.continent=A.continent);

JOIN

11. Display the matchid, date, and number of goals for all matches in which Poland (POL) participated.

For all columns other than the ones grouped by GROUP BY, you must use aggregate functions. Otherwise, an error occurs.

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

13.

I didn't understand this one, so I'll try again next time....