SELECT whithin SELECT/ru

В этом уроке рассматривается, как использовать оператор SELECT внутри других операторов SELECT для выполнения более сложных запросов.

name	continent	area	population	gdp
Afghanistan	Asia	652230	25500100	20343000000
Albania	Europe	28748	2831741	12960000000
Algeria	Africa	2381741	37100000	188681000000
Andorra	Europe	468	78115	3712000000
Angola	Africa	1246700	20609294	100990000000
...

Using nested SELECT

Summary

Больше России

Выведите название каждой страны, население которой больше населения России.

world(name, continent, area, population, gdp)

где

• name — название страны;
• continent — континент;
• area — площадь;
• population — население;
• gdp — ВВП.

SELECT name FROM world
  WHERE population >
     (SELECT population FROM world
      WHERE name='Romania')

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

Богаче Великобритании

Выведите страны Европы, у которых ВВП на душу населения выше, чем у 'United Kingdom'.

ВВП на душу населения рассчитывается как gdp/population

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

Соседи Аргентины и Австралии

Выведите название и континент стран, расположенных на континентах, к которым относятся либо Argentina, либо Australia. Отсортируйте по названию страны.

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

Между Канадой и Польшей

Какая страна имеет население больше, чем у United Kingdom, но меньше, чем у Germany? Выведите название и население.

SELECT name,population FROM world
WHERE population BETWEEN
(SELECT population+1 FROM world WHERE name='United Kingdom')
AND
(SELECT population-1 FROM world WHERE name='Germany')

Проценты от населения Германии

Германия (населением примерно 80 миллионов человек) имеет самое большое население среди стран Европы. Австрия (населением 8,5 миллиона человек) имеет 11% от населения Германии.

Выведите название и население каждой страны Европы. Покажите население как процент от населения Германии.

Формат должен быть Название, Процент, например:

name	percentage
Albania	3%
Andorra	0%
Austria	11%
...	...

Вы можете использовать функцию ROUND, чтобы убрать знаки после запятой.

Вы можете использовать функцию CONCAT, чтобы добавить символ процента.

SELECT name, CONCAT(CAST(ROUND(100*population/(SELECT population FROM world WHERE name = 'Germany'),0) as int), '%')
FROM world
WHERE continent = 'Europe'

Чтобы получить более полное представление о важных возможностях SQL, перейдите к следующему уроку, посвящённому агрегатным функциям.

Чтобы получить чрезмерно подробное представление об одной незначительной особенности языка, продолжайте чтение.

Мы можем использовать слово ALL, чтобы разрешить операторам >=, >, < или <= работать со списком значений. Например, с помощью следующего запроса можно найти самую большую страну в мире по населению:

SELECT name
  FROM world
 WHERE population >= ALL(SELECT population
                           FROM world
                          WHERE population>0)

В подзапросе необходимо использовать условие population>0, так как у некоторых стран значение population равно null.

Больше, чем любая страна Европы

Какие страны имеют ВВП больше, чем у любой страны Европы? Выведите только name. (У некоторых стран значение gdp может быть NULL.)

SELECT name FROM world 
  WHERE gdp > ALL
   (SELECT gdp FROM world
    WHERE continent = 'Europe' 
      AND gdp IS NOT NULL)

Мы можем обращаться к значениям из внешнего SELECT внутри внутреннего SELECT, а также давать таблицам имена, чтобы различать внутренние и внешние версии таблиц.

Самая большая страна на каждом континенте

Найдите самую большую страну (по площади) на каждом континенте. Выведите continent, name и area.

SELECT continent, name, population FROM world x
  WHERE population >= ALL
    (SELECT population FROM world y
        WHERE y.continent=x.continent
          AND population>0)

SELECT continent, name, area 
 FROM world x
  WHERE area >= ALL
    (SELECT area FROM world y
        WHERE y.continent=x.continent
         and area > 0 )

Приведённый выше пример называется коррелированным или синхронизированным подзапросом.

Коррелированный подзапрос работает как вложенный цикл: подзапрос получает доступ только к строкам, связанным с одной записью внешнего запроса за раз. Этот метод использует псевдонимы таблиц для обозначения двух разных обращений к одной и той же таблице: одно во внешнем запросе, другое — в подзапросе.

Один из способов интерпретировать строку в условии WHERE, которая связывает две таблицы: «... где связанные значения совпадают».

В приведённом примере можно сказать: «выберите данные о стране из таблицы world, где площадь больше или равна площади всех стран, у которых совпадает континент».

Первая страна на каждом континенте (по алфавиту)

Выведите каждый континент и название страны, которая идёт первой по алфавиту.

SELECT continent,name FROM world x
  WHERE x.name <= ALL (
    SELECT name FROM world y
     WHERE x.continent=y.continent)

Сложные вопросы, использующие методы, не рассмотренные в предыдущих разделах

Найдите континенты, где все страны имеют население <= 25000000. Затем найдите названия стран, относящихся к этим континентам. Выведите name, continent и 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)

В три раза больше

Некоторые страны имеют население более чем в три раза больше, чем у всех их соседей (на том же континенте). Выведите названия стран и континенты.

SELECT name, continent FROM world x WHERE
 population > ALL
 (SELECT population*3 FROM world y
 WHERE y.continent = x.continent
 AND y.name != x.name)

Nested SELECT Quiz