SQL ZOO

Question 1

Show the 1984 winners and subject in nobel table ordered by subject and winner name; but list chemistry and physics last.

REF

Answer 1

SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY CASE WHEN subject IN (‘Chemistry’, ‘Physics’) THEN 1 ELSE 0 END, subject, winner

Question 2

What’s a correlated or synchronized sub-query?

REF

Answer 2

Correlated sub-queries are used for row-by-row processing, where each sub-query is executed once for every row of the outer query.

SELECT continent, name, area FROM world x
WHERE area =
(SELECT MAX(area) FROM world y
WHERE y.continent=x.continent
)

Finds the largest country (by area) in each continent, show the continent, the name and the area

SELECT w1.name, w1.continent
FROM world w1
WHERE w1.population >= ALL (
SELECT w2.population * 3
FROM world w2
WHERE w1.continent = w2.continent AND w1.name != w2.name
);

Some countries have populations more than three times that of all of their neighbors (in the same continent). The above, gives the countries and continents.