В базе данных две таблицы Person и City, показанные на рисунке ниже. Поскольку база даных учебная, то количество полей в таблицах минимально. Рисунок является снимком окна Связи, открывающегося из главного меню базы опцией Сервис.
Таблица Person содержит сведения о некоторых людях, таблица City — о некоторых городах.
Эти таблицы связаны отношением "родной город", то есть с каждым человеком сопоставлен город, который человек считает родным. В поле nativeCity таблицы Person хранится номер города — значение поля id в таблице City.
Начальные примеры построения запросов приведены в виде снимков окон дизайнера запросов и в виде текстов на языке SQL. Дизайнер запросов не позволяет строить сложные запросы, поэтому приходится собирать сложный запрос из нескольких простых.
Пример запроса на вычисление по формулам и использование стандартных функций. Запрос строится из двух последовательных запросов.
Первый запрос birthYearDay_1 вычисляет год, месяц и число даты рождения, а также сегодняшние год, месяц и число.
Второй запрос age_1 по найденным значениям полей предыдущего запроса вычисляет сколько полных лет исполнилось человеку.
SELECT "id", "birthDate", YEAR( "birthDate" ) AS "birthYear", DAYOFYEAR( "birthDate" ) AS "birthDayOfYear", YEAR( NOW( ) ) AS "nowYear", DAYOFYEAR( NOW( ) ) AS "nowDayOfYear", DAY( "birthDate" ) AS "birthDay", DAY( NOW( ) ) AS "nowDay", MONTH( "birthDate" ) AS "birthMonth", MONTH( NOW( ) ) AS "nowMonth" FROM "Person"
В итоговой таблице запроса всего два поля: ключевой номер человека в таблице Person и число полных лет, которое вычисляется по формуле. Формула достаточно сложная потому, что нужно определить, был уже в этом году у человека день рождения, или ещё будет. От этого зависит количество полных лет. Для вычисления используется функция sign, принимающая отрицательное единичное значение, если день рождения уже был в этом году, а положительное — если ещё будет. При этом разность месяцев должна иметь преимущество перед разностью чисел в месяце. Умножение разности месяцев на сто обеспечивает перевес разности даже в один месяц перед наибольшим значением разности чисел. Кроме этого, нужно учесть, что при совпадении месяца и числа, когда день рождения как раз сегодня, нужно считать количество полных лет так же, как при уже прошедшем дне рождения. Строится квадратичная интерполяционная функция по трём точкам, при этом аргументами являются значения функции sign — минус один, нуль и плюс один, а соответствующими значениями — нуль, нуль и плюс один. Последнее значение (+1) должно быть в том случае, если из разности номера текущего года и года рождения нужно вычесть единицу, то есть, когда день рождения в этом году ещё только будет. Для возведения в степень используется функция power, у которой первый аргумент — число, возводимое в степень, а второй — степень, в которую это число возводится.
SELECT "id", "nowYear" - "birthYear" - ( 0.5 * POWER( SIGN( "birthDay" - "nowDay" + ( "birthMonth" - "nowMonth" ) * 100 ) + 0.5, 2 ) - 0.125 ) AS "age" FROM "birthYearDay_1" AS "birthYearDay_1"
Вообще говоря, формулу, вычисляющую возраст в поле age можно упростить,
если вместо числа и месяца использовать день года. В этом случае формула будет выглядеть так:
"nowYear" - "birthYear" - ( 0.5 * POWER( SIGN( "birthDayOfYear" - "nowDayOfYear") + 0.5, 2 ) - 0.125 ).
Пример простейшего запроса по двум таблицам. В итоговую таблицу попадает имя человека и имя города, который для него является родным. Связь между таблицами определяет выбор из двух таблиц таких записей, у которых связанные поля имеют одинаковые значения.
SELECT "City"."name", "Person"."name" FROM "Person", "City" WHERE "Person"."nativeCity" = "City"."id"
Сначала строим запрос minBirthDate, который находит минимальную дату рождения.
Затем, используя построенный запрос minBirthDate и таблицу Person, строим запрос oldestPerson1, который находит человека, дата рождения которого совпадает с минимальной.
SELECT MIN( "birthDate" ) AS "minDate" FROM "Person"
SELECT "Person"."id", "Person"."name", "Person"."birthDate" FROM "minBirthDate", "Person" WHERE "minBirthDate"."minDate" = "Person"."birthDate"
Целью данного запроса является нахождение города, для которого число людей из таблицы Person, считающих его родным, является наибольшим среди всех городов из таблицы City.
Сначала строим запрос countPersonNativeCity, который для каждого города находит число людей, считающих этот город родным.
Затем, используя построенный запрос countPersonNativeCity, строим запрос maxCountPersonNativeCity, который находит наибольшее число из множества чисел, найденных запросом countPersonNativeCity.
Наконец, в новом запросе maxNativeCity, сопоставляя наибольшее число, найденное запросом maxCountPersonNativeCity, с записями итоговой таблицы запроса countPersonNativeCity, выбирается такая запись, в которой значение поля с числом людей, считающих город родным, совпадает с наибольшим числом из этого множества.
Для каждого города вычисляет число людей, считающих его родным. Использует таблицы Person и City. Вторая таблица нужна лишь для того, чтобы отобразить в итоговой таблице запроса имя города.
Группирующие поля nativeCity — номер города, и name — имя города, группируют записи, относящиеся к одному городу. На каком поле действует групповая функция Количество, вообще говоря, не важно — она считает количество записей в группе.
SELECT COUNT( "Person"."id" ) AS "number", "Person"."nativeCity", "City"."name" FROM "Person", "City" WHERE "Person"."nativeCity" = "City"."id" GROUP BY "Person"."nativeCity", "City"."name"
Выбирает наибольшее значение поля number среди всех записей итоговой таблицы запроса countPersonNativeCity. Поскольку не выбраны группирующие поля, создаётся единственная группа, в которую попадают все записи.
SELECT MAX( "number" ) AS "maxNumber" FROM "countPersonNativeCity"
Находит такую запись итоговой таблицы запроса сountPersonNativeCity, в которой значение поля number совпадает со значением поля maxNumber единственной записи итоговой таблицы запроса maxCountPersonNativeCity. Связь между таблицами определяет выбор из двух таблиц таких записей, у которых связанные поля имеют одинаковые значения.
SELECT "countPersonNativeCity"."nativeCity", "countPersonNativeCity"."name", "countPersonNativeCity"."number" FROM "maxCountPersonNativeCity", "countPersonNativeCity" WHERE "maxCountPersonNativeCity"."maxNumber" = "countPersonNativeCity"."number"
Запрос должен найти такие города в таблице City, которые являются родными только для двух человек из таблицы Person, при этом эти люди должны быть разных полов. То есть, город должен быть родным для одного мужчины и одной женщины одновременно.
Сначала строим запрос countSexNativeCity, который для каждого города находит число мужчин и женщин, считающих этот город родным.
Затем, используя построенный запрос countSexNativeCity, строим запрос countSexNativeCityEqual, который находит такие города, которые являются родными для одинакового числа мужчин и женщин.
Наконец, в новом запросе countSexNativeCityEqual, отбираются такие города, найденные запросом countSexNativeCityEqual, у которых число мужчин и женщин, считающих его родными, равно 1.
Вообще говоря, можно было бы обойтись и двумя запросами, но для большей наглядности задача решается тремя запросами.
Для каждого города вычисляет отдельно число мужчин и женщин, считающих его родным. Использует таблицу Person
Группирующие поля nativeCity — номер города, и sex — пол человека, группируют записи, относящиеся к одному городу и одному полу. Таким образом, в каждую группу попадают люди одного пола, считающие родным один и тот же город. На каком поле действует групповая функция Количество, вообще говоря, не важно — она считает количество записей в группе. В данном случае, была выбрана звёздочка, обозначающая любое поле.
SELECT "Person"."nativeCity", COUNT( "Person".* ) AS "cnt", "Person"."sex" FROM "Person" AS "Person" GROUP BY "nativeCity", "sex"
Используя запрос countSexNativeCity, находит города, являющиеся родными для одинакового числа мужчин и женщин. При этом запрос countSexNativeCity включён дважды. Второе включение идёт под именем countSexNativeCity1. Кроме того, включена таблица City для того, чтобы отобразить в итоговой таблице запроса имя города.
Из первой таблицы отбираются записи, относящиеся к мужчинам за счёт добавления условия отбора 'M' на поле sex. Из второй таблицы отбираются записи, относящиеся к женщинам за счёт добавления условия отбора 'F' на поле sex. Связанные поля nativeCity и cnt из этих записей отберут такие, у которых номера городов совпадают и количество людей одного пола, считающих его родными одинаково.
SELECT "countSexNativeCity"."sex" AS "male", "countSexNativeCity_1"."sex" AS "female", "countSexNativeCity_1"."nativeCity", "countSexNativeCity_1"."cnt", "City"."name" FROM "countSexNativeCity" AS "countSexNativeCity_1", "countSexNativeCity" AS "countSexNativeCity", "City" AS "City" WHERE "countSexNativeCity_1"."nativeCity" = "countSexNativeCity"."nativeCity" AND "countSexNativeCity_1"."cnt" = "countSexNativeCity"."cnt" AND "City"."id" = "countSexNativeCity_1"."nativeCity" AND "countSexNativeCity"."sex" = 'M' AND "countSexNativeCity_1"."sex" = 'F'
Из записей, таблицы, созданной запросом maxCountSexNativeCityEqual выбирает такие записи, у которых число мужчин и женщин, считающих его родными равно 1.
Вообще говоря, условие на поле cnt можно было наложить ещё в предыдущем запросе maxCountSexNativeCityEqual. Тогда уже в том запросе был бы получен нужный результат.
SELECT "nativeCity", "cnt" FROM "countSexNativeCityEqual" AS "countSexNativeCityEqual" WHERE "cnt" = 1
Запрос находит города, которые ни для кого не являются родными. То есть, ищет такие города, для которых значения поля nativeCity в таблице City ни разу не совпадают со значениями поля nativeCity в таблице Person.
Запросы такого типа, когда нужно найти дополнение для некоторого подмножества, строятся на основе двух запросов. Сначала строим запрос, выбирающий записи, относящиеся к этому подмножеству, а потом, другой запрос, выбирает из всего множества записи, не относящиеся к выбранному подмножеству.
Визуальный интерфейс не позволяет построить такой сложный запрос в два запроса. Поэтому приходится строить запрос в редакторе SQL. Впрочем, после построения запроса результат может быть отображён в режиме дизайна.
Условием отбора является отсутствие значения поля id в итоговой таблице подзапроса, написанного на языке SQL и целиком помещённого в строчку "Критерий".
SELECT "id", "name" FROM "City" AS "City" WHERE NOT "id" IN ( SELECT "nativeCity" FROM "Person" )