Пояснения к примеру базы данных в среде Access

На этой странице даются пояснения к запросам из примера базы данных, который можно взять здесь.

База данных содержит вседения о людях и о поездках, которые эти люди совершали в разные города. Данные хранятся в четырёх таблиц: Person, City, Trip, Person_Trip.

Таблица Person содержит личные данные людей:

• код человека,
• имя,
• дату рождения,
• пол,
• родной город человека (записывается в виде кода города в таблице City).

Таблица City содержит сведения о городах:

• код города,
• название города.

Таблица Trip содержит данные о поездках:

• код поездки,
• город, в который совершена поедка (записывается в виде кода города в таблице City),
• дата начала поездки,
• дата окончания поездки,
• вид поездки (в запросах это поле не используется).

Таблица Person_Trip содержит сведения об участии людей в поездках:

• человек, участвовавший в поездке (записывается в виде кода человека в таблице Person),
• поездка, в которой участвовал человек (записывается в виде кода поездки в таблице Trip).

Схема данных

Все запросы перенумерованы и снабжены коротким описанием, снимком окна конструктора и кодом SQL. Имя каждого запроса начинается с буквы q, после которой идёт двузначный номер запроса, а после номера — название запроса, отделённое от номера символом подчёркивания.

Результатом выполнения запроса является результирующая таблица, которая не хранится постоянно в базе данных, в отличие от основных таблиц.

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

Запрос q01_NameBirth
Пример выборки двух полей из таблицы Person

Код Access-SQL.

SELECT Person.Name, Person.BirthDate FROM Person;

Запрос q02_Name_jo
Пример выборки из таблицы Person c условием отбора.
Отбираются записи, в которых в поле имени имеются обе буквы j и о.
Звездочка в условии отбора заменяет любую последовательность букв.
Обратите внимание на союз And.

Код Access-SQL.

SELECT Person.*, Person.Name FROM Person WHERE (((Person.Name) Like "*j*" And (Person.Name) Like "*o*"));

Запрос q03_Name_oa
Пример выборки из таблицы Person c условием отбора.
Отбираются записи, в которых в поле имени имеется одна из букв: j или о.
Обратите внимание на союз Or.

Код Access-SQL.

SELECT Person.*, Person.Name FROM Person WHERE (((Person.Name) Like "*a*" Or (Person.Name) Like "*y*"));

Запрос q04_NameOrBirth
Пример выборки из таблицы Person c условием отбора.
Отбираются записи, в которых имя начинается на букву m.
Ещё отбираются записи, с датой рождения больше 01.01.84.
Обратите внимание: условия отбора для разных полей записаны в режиме конструктора в разных строчках. Это равносильно союзу Or.

Код Access-SQL.

SELECT Person.ID, Person.Name, Person.BirthDate, Person.Sex FROM Person WHERE (((Person.Name) Like "m*")) OR (((Person.BirthDate)>#1/1/1984#));

Запрос q05_NameAndBirth
Пример выборки из таблицы Person c условием отбора.
Отбираются записи, в которых имя оканчивается на букву y, и при этом дата рождения меньше 01.01.83.
Обратите внимание: условия отбора для разных полей записаны в режиме конструктора в одной строчке. Это равносильно союзу And.

Код Access-SQL.

SELECT Person.ID, Person.Name, Person.BirthDate, Person.Sex FROM Person WHERE (((Person.Name) Like "*y") AND ((Person.BirthDate)<#1/1/1983#));

Запрос q06_getForName
Пример выборки из таблицы Person c параметром.
Звездочка в строке Поле означает отображение всех полей в результирующей таблице.
Отсутствие флажка в строке Вывод на экран означает, что это поле используется лишь для отбора записей, но не для отображения в результирующей таблице.
Квадратные скобки в условии отбора означают, что значение условия отбора будет введено во время выполнения запроса.
Отбираются записи, в которых имя соответствует вводимому значению.

Код Access-SQL.

SELECT Person.* FROM Person WHERE (((Person.Name) Like []));

Запрос q07_PersonNameGroup
Пример выборки из таблицы Person c использованием групповой операции.
Групповая операция объединяет несколько записей в одну группу.
В одной группе оказываются записи, у которых совпадают значения полей, по которым проводится группировка.
В этом примере группировка проводится по одному полю Name. Таким образом, в одной группе окажутся записи с одинаковыми именами.

Код Access-SQL.

SELECT Person.Name FROM Person GROUP BY Person.Name;

Запрос q08_PersonCount
Пример использования групповой операции Count для таблицы Person.
Операция Count подсчитывает количество записей в группе.
В данном примере подсчитывается число всех записей в таблице.
Поскольку нет группируемых полей, то в одной группе оказываются все записи.
Обратите внимание, в строке Поле перед обозначением поля ID стоит res с двоеточием.
Это для того, чтобы назвать именем res поле в результирующей таблице.
Если мы не задаём имя поля в результирующей таблице, то это имя назначается по умолчанию.
В обычном случае имя поля в результирующей таблице совпадает с соответствующим полем исходной таблицы, а при групповой операции впереди приписывается имя групповой операции.
Например, в данном случае поле бы называлось Count-ID.

Код Access-SQL.

SELECT Count(Person.ID) AS res FROM Person;

Запрос q08e_PersonCount
Пример использования групповой операции Count для таблицы Person.
Данный запрос делает то же, что и предыдущий, но групповая операция записана с использованием выражения.
Интересно сравнить SQL-коды этих двух запросов.

Код Access-SQL.

SELECT Count(*) AS field FROM Person;

Запрос q09_PersonNameCount
Пример использования групповой операции Count для таблицы Person.
В данном примере подсчитывается число повторений имён в таблице.
Группировка происходит по полю Name, как и в запросе q07, но теперь применяется ещё и операция Count, подсчитывающая число записей в каждой группе.
Таким образом, в результирующей таблице отображаются числа повторений каждого имени.

Код Access-SQL.

SELECT Person.Name, Count(Person.Name) AS Count_Name FROM Person GROUP BY Person.Name;

Запрос q10_CountMenAndWomen
Пример использования групповой операции Count для таблицы Person.
В данном примере подсчитывается число мужчин и женщин в таблице.
Запрос почти такой же, как предыдущий, но группировка происходит по полю Sex.
Поле Sex может иметь только два значения, поэтому записи будут объединены в две группы: в одной группе — мужчины, в другой — женщины.

Код Access-SQL.

SELECT Count(Person.ID) AS [Count-ID], Person.Sex FROM Person GROUP BY Person.Sex;

Запрос q11_Age
Пример использования функций даты-времени.
В данном примере вычисляется возраст каждого человека.
Приблизительный возраст вычисляется в поле diff как разность между текущим моментом времени и датой рождения человека.
Текущий момент выдаёт функция Date(), не имеющая входных параметров внутри круглых скобок.
Разность дат выдаёт функция DateDiff(), которая имеет три входных параметра внутри круглых скобок:
единица измерения результата, начальная дата, конечная дата.
В нашем примере вызов этой функции записан так: DateDiff("yyyy";[Person.BirthDate];Date())
Параметр "yyyy" означает, что разность дат нужно измерять в годах,
параметр [Person.BirthDate] возьмёт начальную дату из поля BirthDate таблицы Person,
параметр Date() вызовет функцию, выдающую текущий момент времени, которая и будет взята как конечная дата при вычислении разности.

Из-за того, что функция DateDiff() выдает разность в годах без учёта дня, приблизительный возраст человека может оказаться на один год больше, если в нынешнем году день рождения этого человека ещё не был.
Другими словами, приблизительный возраст вычисляется просто как разность текущего года и года рождения.
Для вычисления полных лет человека вводятся два поля M и D.
В поле М записывается разность между номером текущего месяца в году и номером месяца даты рождения, а в поле D записывается разность между текущим числом (номером сегодняшнего дня в месяце) и числом даты рождения.
Затем вводится поле Х, которое может принимать три значения — -1, 0 и +1. Значение -1 будет в том случае, если день рождения будет после текущей даты.
Значение +1 будет в том случае, если день рождения в этом году уже был.
Значение 0 будет в том случае, если день рождения как раз сегодня.
Эти значения даёт функция Sgn, в зависимости от знака выражения 100*[M]+[D]. Коэффициент 100 нужет для того, чтобы даже если М имеет положительное значение 1, а D имеет отрицательное значение -30, результат всё равно был бы положительный. То есть, разность месяцев всегда весомее разности дней. И только если разность месяцев равна нулю, значение поля Х определяется разностью дней.
Новое поле Y определяет, нужно ли вычесть из приблизительного возраста единицу или оставить прежнее значение. Прежнее значение остаётся, если поле Х имеет значение +1 или 0, означающие, что день рождения в этом году уже был, или пришёлся на сегодня. При этих значениях Х поле Y принимает значение 0.
Возраст уменьшается на 1, если поле Х имеет значение -1. При таком значении Х поле Y принимает значение 1.
Наконец в поле age уточняется приблизительный возраст, а именно, из него вычитается 1, если день рождения человека в этом году ещё только будет.

Код Access-SQL.

SELECT Person.ID, Person.Name, Person.BirthDate, DateDiff("yyyy",[Person.BirthDate],Date()) AS diff, Month(Date())-Month([BirthDate]) AS M, Day(Date())-Day([BirthDate]) AS D, Sgn(100*[M]+[D]) AS X, [X]*([X]-1)/2 AS Y, [diff]-[X] AS age FROM Person;

Запрос q11a_Age
Пример использования функций даты-времени.
Данный пример отличается от предыдущего лишь тем, что отбираются не все записи таблицы, а лишь те, вычисленный возраст в которых удовлетворяет условию отбора.

Код Access-SQL.

SELECT Person.ID, Person.Name, Person.BirthDate, DateDiff("yyyy",[Person.BirthDate],Date()) AS diff, Date() AS [date] FROM Person WHERE (( (DateDiff("yyyy",[Person.BirthDate],Date()))>25 And (DateDiff("yyyy",[Person.BirthDate],Date()))<29 ));

Запрос q11b_Age
Пример использования функций даты-времени.
Запрос делает то же самое, что и предыдущий запрос, однако условие отбора записано без использования знаков <, > и союза And. Вместо этого используется предлог Between.

Код Access-SQL.

SELECT Person.ID, Person.Name, Person.BirthDate, DateDiff("yyyy",[Person.BirthDate],Date()) AS diff, Date() AS [date] FROM Person WHERE (((DateDiff("yyyy",[Person.BirthDate],Date())) Between 26 And 28));

Запрос q12_Avg_Age
Пример использования функций даты-времени и групповой операции Avg.
Операция Avg подсчитывает среднее значение поля внутри каждой группы.
В данном примере подсчитывается средний возраст людей из таблицы Person.
Поскольку нет группируемых полей, то в одной группе оказываются все записи.
В результирующей таблице первое поле age содержит средний возраст, вычисленный как среднее значение возраста всех людей;
второе поле avg_date — среднюю дату рождения, отображаемую во внутреннем формате.
Для перевода даты из внутреннего формата в стандартный используется функция CDate(), и это отображается в четвёртом поле dt.
Третье поле age1 содержит средний возраст, вычисленный как разность текущего момента времени и средней даты рождения.

Код Access-SQL.

SELECT Avg(DateDiff("yyyy",[Person.BirthDate],Date())) AS age, Avg(Person.BirthDate) AS avg_date, DateDiff("yyyy",[avg_date],Date()) AS age1, CVDate([avg_date]) AS dt FROM Person;

Запрос q12s_Age
Пример использования функций даты-времени.
Запрос вычисляет средний приблизительный возраст для мужчин и для женщин.

Код Access-SQL.

SELECT Avg(DateDiff("yyyy",[Person.BirthDate],Date())) AS age, CDate(Avg([BirthDate])) AS avgBirthDate, Person.Sex FROM Person GROUP BY Person.Sex;

Запрос q13_sum_age
Пример использования функций даты-времени и групповых операций Avg, Sum, Max, Min.
Операция Sum подсчитывает среднее значение поля внутри каждой группы.
Операция Max находит наибольшее значение поля внутри каждой группы.
Операция Min находит наименьшее значение поля внутри каждой группы.
В этом примере, как и в предыдущем, подсчитывается средний возраст людей в группе, кроме этого вычисляется наибольшее, наименьшее и суммарное значение возраста в группе, и подсчитывается количество записей в каждой группе.
Группировка происходит по полю NativeCity, и в группы собираются записи, имеющие одинаковые значения в этом поле. Таким образом, данный запрос вычисляет возрастную статистику по каждому городу.
В результирующей таблице в первом поле отображается код города,
во втором — число записей в группе,
в остальных полях — статистические данные по группе.

Код Access-SQL.

SELECT Person.NativeCity, Count(Person.NativeCity) AS [Count-NativeCity], Sum(DateDiff("yyyy",[BirthDate],Date())) AS sum_age, Avg(DateDiff("yyyy",[BirthDate],Date())) AS avg_age, Max(DateDiff("yyyy",[BirthDate],Date())) AS max_age, Min(DateDiff("yyyy",[BirthDate],Date())) AS min_age FROM Person GROUP BY Person.NativeCity;

Запрос q13c_sum_age
Пример использования в запросе двух таблиц,
а также функций даты-времени и групповых операций Avg, Sum, Max, Min.
Отличие этого запроса от предыдущего лишь в том, что в результирующей таблице добавлено поле с именем города. Поскольку в таблице Person нет сведений о имени города, к запрос делается по двум таблицам Person и City, и имя города берётся из второй таблицы. Таблицы связаны по полям, хранящим код города в каждой таблице.
Связь между таблицами позволяет запросу находить в таблице City запись о городе, код которого указан в таблице Person.
Создание групп по городам, показано ещё в более простом запросе q21.

Код Access-SQL.

SELECT Person.NativeCity, Count(Person.NativeCity) AS [Count-NativeCity], Sum(DateDiff("yyyy",[BirthDate],Date())) AS sum_age, Avg(DateDiff("yyyy",[BirthDate],Date())) AS avg_age, Max(DateDiff("yyyy",[BirthDate],Date())) AS max_age, Min(DateDiff("yyyy",[BirthDate],Date())) AS min_age, City.Name FROM City INNER JOIN Person ON City.ID = Person.NativeCity GROUP BY Person.Natte.[Min-BirthDate];

Запрос q14_MinBirthDate
Находится дата рождения самого старшего человека из таблицы Person при помощи групповой операции Min.

Код Access-SQL.

SELECT Min(Person.BirthDate) AS [Min-BirthDate] FROM Person;

Запрос q15_OldestPerson
Пример использования в запросе другого запроса.
Находятся данные о самом старшем человеке из таблицы Person, используя предыдущий запрос.
По дате рождения из результирующей таблицы запроса q14 находится соответствующая запись в таблице Person.
Нужно заметить, что наименьшую дату рождения могут имеють несколько человек, в этом случае будут найдены записи в таблице Person обо всех этих людях. Обратите внимание на связь между результирующей таблицей запроса q14 и таблицей Person.

Код Access-SQL.

SELECT Person.Name, Person.ID, Person.BirthDate, Person.Sex FROM Person INNER JOIN q14_MinBirthDate ON Person.BirthDate = q14_MinBirthDate.[Min-BirthDate];

Запрос q16_PersonMaxAge
К предыдущему запросу добавлено вычисление возраста самого старшего человека.

Код Access-SQL.

SELECT Person.Name, DateDiff("yyyy",[Person.BirthDate],Date()) AS age, Person.BirthDate FROM q14_MinBirthDate INNER JOIN Person ON q14_MinBirthDate.[Min-BirthDate] = Person.BirthDate;

Запрос q17_MaxBirthDate
Находится дата рождения самого младшего человека из таблицы Person при помощи групповой операции Max.

Код Access-SQL.

SELECT Max(Person.BirthDate) AS [Max-BirthDate] FROM Person;

Запрос q18_YoungestPersonAgeCity
Пример использования нескольких таблиц и запросов.
Находятся данные о самом младшем человеке из таблицы Person.
В отличие от запроса q16 возраст человека берется из запроса q11, что освобождает от необходимости вводить в режиме конструктора сложное выражение вычисления возраста.
Кроме того, находится имя родного города этого человека при помощи добавления в запрос таблицы City, как это было сделано в запросе q13c.

Код Access-SQL.

SELECT Person.*, q11_Age.diff, City.Name FROM City INNER JOIN (q17_MaxBirthDate INNER JOIN (Person INNER JOIN q11_Age ON Person.ID = q11_Age.ID) ON q17_MaxBirthDate.[Max-BirthDate] = Person.BirthDate) ON City.ID = Person.NativeCity;

Запрос q19_PersonMaxAgeSQL
Пример работы на языке SQL.
Данный запрос делает то же, что и запрос q16, но без использования запроса q14.
Вычисление даты рождения старшего человека происходит в подзапросе данного запроса, что невозможно сделать без использования языка SQL.

Код Access-SQL.

SELECT Person.Name, Person.BirthDate, DateDiff("yyyy",[BirthDate],Date()) AS age_now FROM Person WHERE (((Person.BirthDate) In (SELECT min(BirthDate) from Person )));

Запрос q20_NameCity
Пример выборки из двух таблиц.
Выводятся код, имя и название родного города каждого человека.

Код Access-SQL.

SELECT Person.ID, Person.Name, City.Name FROM City INNER JOIN Person ON City.ID = Person.NativeCity ORDER BY Person.ID;

Запрос q20a_NameCityNotMoscow
В отличие от предыдущего запроса, в данном запросе добавлено условие отбора, которое не включает в результирующую таблицу записи о людях, родным городом которых является Москва.

Код Access-SQL.

SELECT City.Name, Person.Name FROM City INNER JOIN Person ON City.ID = Person.NativeCity WHERE (((City.Name)<>'Moscow'));

Запрос q21_CountNativeCity
Вычисляется для какого числа людей каждый город является родным.
Это вычисление, в частности, уже было показано в запросах q13 и q13c.

Код Access-SQL.

SELECT Person.NativeCity, City.Name AS City, Count(City.Name) AS Count_N FROM City INNER JOIN Person ON City.ID = Person.NativeCity GROUP BY Person.NativeCity, City.Name;

Запрос q21w_CityWomenCount
Вычисляется для какого числа женщин каждый город является родным.
Отличается от предыдущего запроса по сути только условием на пол.

Код Access-SQL.

SELECT Count(Person.ID) AS [Count-ID], Person.NativeCity, City.Name FROM City INNER JOIN Person ON City.ID = Person.NativeCity WHERE (((Person.Sex)="F")) GROUP BY Person.NativeCity, City.Name;

Запрос q22_WhoFromTheSameCity
Пример на использование в запросе одной таблицы дважды.
Запрос с параметром. При выполнении запроса нужно ввести код человека в таблице Person.
В результирующей таблице будут отображены сведения о людях, для которых родным городом является тот же город, что и для человека, код которого был введён.
Таблица Person в запросе участвует дважды: под своим именем и под именем Person_1.
Из Person выбирается сам человек, а из Person_1 выбираются его земляки.
Обратите внимание на связь между Person и Person_1. Связь по полю NativeCity выбирает такие записи из Person_1, у которых значение поля NativeCity совпадает со значением этого поля выбранной записи из Person.
Таблица City используется для нахождения имени родного города.

Код Access-SQL.

SELECT Person_1.ID, Person_1.Name, City.Name, Person.Name FROM City INNER JOIN ( Person INNER JOIN Person AS Person_1 ON Person.NativeCity = Person_1.NativeCity ) ON City.ID = Person.NativeCity WHERE (((Person_1.ID)<>[Person].[ID]) AND ((Person.ID) Like ["Input Person ID"]));

Запрос q23_PersonSoleNativeCity
Нахождение людей из таблицы Person, у которых в таблице Person нет земляков.
То есть, нужно найти такие города, которые являются родными только для одного человека, а потом по этим городам искать людей, для которых эти города являются родными.
Используется запрос q21, который вычисляет для какого числа людей, каждый город является родным. При помощи условия отбора выбираются только те города из q21, для которых это число равно одному.
Связь q21 и Person по полю NativeCity выбирает те записи из Person, для которых отобранные города являются родными.

Код Access-SQL.

SELECT Person.Name, q21_CountNativeCity.Count_N, q21_CountNativeCity.City FROM Person INNER JOIN q21_CountNativeCity ON Person.NativeCity = q21_CountNativeCity.NativeCity WHERE (((q21_CountNativeCity.Count_N)=1));

Запрос q24_VisitCity
Пример на выборку из нескольких таблиц с условием отбора.
Запрос выдаёт сведения о том, какие города и когда посещал человек, код которого в таблице Person равен единице.

Код Access-SQL.

SELECT Person.Name, City.Name, Trip.StartDate, Trip.FinishDate FROM (City INNER JOIN Trip ON City.ID = Trip.City) INNER JOIN (Person INNER JOIN Person_Trip ON Person.ID = Person_Trip.PersonID) ON Trip.ID = Person_Trip.TripID WHERE (((Person.ID)=1));

Запрос q25_WhoVisitedNativeCity
Пример на выборку из нескольких таблиц.
Запрос выдает сведения о том, кто и когда посещал родные города.
Обратите внимание на связи между таблицами в этом запросе и в предыдущем.
В этом запросе присутствует связь между таблицами Person и City, указывающая на родной город.
В предыдущем запросе такой связи нет.

Код Access-SQL.

SELECT City.Name, Person.Name, Trip.StartDate, Trip.FinishDate FROM ( ( City INNER JOIN Person ON City.ID = Person.NativeCity ) INNER JOIN Trip ON City.ID = Trip.City ) INNER JOIN Person_Trip ON (Trip.ID = Person_Trip.TripID) AND (Person.ID = Person_Trip.PersonID);

Запрос q26_PersonTripCount
Пример на групповую операцию.
Вычисляется число всех поездок каждого человека.
Группировка по полям, относящимся к человеку.
Поездки каждого человека собираются в отдельную группу.

Код Access-SQL.

SELECT Person.ID, Person.Name, Count(Person_Trip.TripID) AS [Count-TripID] FROM Person INNER JOIN Person_Trip ON Person.ID = Person_Trip.PersonID GROUP BY Person.ID, Person.Name;

Запрос q27_TripCityCount
Пример на групповую операцию.
Вычисляется число всех поездок в каждый город.
Группировка по полям, относящимся к городу.
Поездки в каждый город собираются в отдельную группу.

Код Access-SQL.

SELECT Count(Trip.City) AS [Count-City], City.Name FROM City INNER JOIN Trip ON City.ID = Trip.City GROUP BY City.Name, Trip.City;

Запрос q28_CityTripCount
Пример на групповую операцию.
Вычисляется число поездок каждого человека в каждый город
Группировка по полям, относящимся к человеку и по полям, относящимся к городу.
Поездки каждого человека в каждый город собираются в отдельную группу.

Код Access-SQL.

SELECT Person.ID AS Person_ID, Person.Name, City.Name, Count(City.Name) AS [Count-Name] FROM (City INNER JOIN Trip ON City.ID = Trip.City) INNER JOIN (Person INNER JOIN Person_Trip ON Person.ID = Person_Trip.PersonID) ON Trip.ID = Person_Trip.TripID

Запрос q28n_VisitNativeCityCount
Пример на групповую операцию.
Вычисляется число поездок каждого человека в родной город.
Запрос похож на предыдущий, но добавлена одна связь, которая отбирает
поездки только в родные города.

Код Access-SQL.

SELECT Person.Name, Count(City.ID) AS trips, City.Name FROM ( ( City INNER JOIN Person ON City.ID = Person.NativeCity) INNER JOIN Trip ON City.ID = Trip.City ) INNER JOIN Person_Trip ON (Trip.ID = Person_Trip.TripID) AND (Person.ID = Person_Trip.PersonID) GROUP BY Person.Name, City.Name, Person.ID, City.ID;

Запрос q28x_Cross_CityTripCount
Пример на групповую операцию.
Данный запрос делает то же самое, что и q28 - вычисляет число поездок каждого человека в каждый город, однако результат выдает в виде перекрёстной таблицы.

Код Access-SQL.

TRANSFORM Count(City.Name) AS [Count-Name] SELECT Person.Name FROM ( City INNER JOIN Trip ON City.ID = Trip.City ) INNER JOIN ( Person INNER JOIN Person_Trip ON Person.ID = Person_Trip.PersonID ) ON Trip.ID = Person_Trip.TripID GROUP BY Person.Name, Person.ID PIVOT City.Name;

Запрос q29_TripCountAfter
Пример на групповую операцию с условием отбора.
Вычисляется число поездок, завершившихся после 01.01.2005.

Код Access-SQL.

SELECT Count(Trip.FinishDate) AS [Count-FinishDate] FROM Trip WHERE (((Trip.FinishDate)>#1/1/2005#));

Запрос q30_TripsWithMen
Пример на выборку из нескольких таблиц с группировкой и условием отбора.
Запрос выдаёт все поездки, в которых принимал участие хотя бы один мужчина.
Группировка по полям, относящимся к поездке.
Участие людей в каждой поездке собираются в отдельную группу.

Код Access-SQL.

SELECT Person_Trip.TripID, Trip.StartDate, Trip.FinishDate FROM Trip INNER JOIN (Person INNER JOIN Person_Trip ON Person.ID = Person_Trip.PersonID) ON Trip.ID = Person_Trip.TripID WHERE (((Person.Sex)="M")) GROUP BY Person_Trip.TripID, Trip.StartDate, Trip.FinishDate;

Запрос q30с_TripsWithMen
К предыдущему запросу добавлено поле кода человека из таблицы Person, и применена групповая операция Count, что позволяет вычислить число мужчин в каждой поездке.

Код Access-SQL.

SELECT Person_Trip.TripID, Trip.StartDate, Trip.FinishDate, Count(Person.ID) AS [Count-ID] FROM Trip INNER JOIN (Person INNER JOIN Person_Trip ON Person.ID = Person_Trip.PersonID) ON Trip.ID = Person_Trip.TripID WHERE (((Person.Sex)="M")) GROUP BY Person_Trip.TripID, Trip.StartDate, Trip.FinishDate;

Запрос q31_TripPersonCount
Пример на групповую операцию.
Вычисляется число людей в каждой поездке.
Группировка по полям, относящимся к поездке.
Участие людей в каждой поездке собираются в отдельную группу.

Код Access-SQL.

SELECT Count(Person.ID) AS [Count-ID], Person_Trip.TripID FROM Person INNER JOIN Person_Trip ON Person.ID = Person_Trip.PersonID GROUP BY Person_Trip.TripID;

Запрос q31a_TripPersonCount
Запрос делает то же, что и предыдущий, но в запрос добавлено больше таблиц, а в результирующую таблицу больше полей.
Добавлять поля в запрос с групповой операцией нужно осторожно, чтобы не создать лишних групп.
В нашем случае добавляются только поля, относящиеся к поездке, поэтому группировка всё равно происходит только по поездкам, как и в предыдущем запросе.
(Присутствующее в группировке имя города новых групп не создаст, поскольку у каждой поедки свой код города, однозначно определяющий имя города.)

Код Access-SQL.

SELECT Count(Person.Name) AS [Count-Name], Trip.StartDate, Trip.FinishDate, City.Name, Trip.ID FROM (City INNER JOIN Trip ON City.ID = Trip.City) INNER JOIN (Person INNER JOIN Person_Trip ON Person.ID = Person_Trip.PersonID) ON Trip.ID = Person_Trip.TripID GROUP BY Trip.StartDate, Trip.FinishDate, City.Name, Trip.ID;

Запрос q32m_TripMen
Пример на выборку из нескольких таблиц с условием отбора.
Выдает коды всех поездок, в которых участвовал каждый мужчина.

Код Access-SQL.

SELECT Person.ID AS PersonID, Person.Name, Trip.ID AS TripID, Person.Sex FROM Trip INNER JOIN (Person INNER JOIN Person_Trip ON Person.ID = Person_Trip.PersonID) ON Trip.ID = Person_Trip.TripID WHERE (((Person.Sex)="m"));

Запрос q32w_TripWomen
Пример на выборку из нескольких таблиц с условием отбора.
Отличается от предыдущего условием отбора пола.

Код Access-SQL.

SELECT Person.ID AS PersonID, Person.Name, Trip.ID AS TripID, Person.Sex FROM Trip INNER JOIN (Person INNER JOIN Person_Trip ON Person.ID = Person_Trip.PersonID) ON Trip.ID = Person_Trip.TripID WHERE (((Person.Sex)="f"));

Запрос q33m_TripMenCount
Использует запрос q32m для нахождения числа мужчин в каждой поездке.

Код Access-SQL.

SELECT Count(q32m_TripMen.PersonID) AS [Count-PersonID], q32m_TripMen.TripID FROM q32m_TripMen GROUP BY q32m_TripMen.TripID;

Запрос q33w_TripWomenCount
Использует запрос q32w для нахождения числа женщин в каждой поездке.

Код Access-SQL.

SELECT Count(q32w_TripWomen.PersonID) AS CountPerson, q32w_TripWomen.TripID FROM q32w_TripWomen GROUP BY q32w_TripWomen.TripID;

Запрос q34a_TripWomenOnly
Пример использования двойных связей. Запрос находит поездки, в которых не принимал участие ни один мужчина.
Для нахождения поездок используются два запроса:
q33w, вычисляющий число женщин в каждой поездке и q31, вычисляющий число всех людей в каждой поездке. Из результирующих таблиц двух запросов отбираются только такие поездки, в которых число всех участвовавших людей совпадает с числом участвовавших женщин.
Связка по полям с кодами поездок отбирает из каждой результирующей таблицы записи относящиеся к одним и тем же поездкам.
Связка по полям с числом участников, отбирает из этих записей только такие, в которых число участников совпадает в обеих результирующих таблицах.

Код Access-SQL.

SELECT q33w_TripWomenCount.TripID, q33w_TripWomenCount.CountPerson FROM q33w_TripWomenCount INNER JOIN q31_TripPersonCount ON (q33w_TripWomenCount.CountPerson = q31_TripPersonCount.[Count-ID]) AND (q33w_TripWomenCount.TripID = q31_TripPersonCount.TripID);

Запрос q34b_TripWomenOnly
Данный запрос отличается от предыдущего лишь тем, что в него добавлены две таблицы для того, чтобы получить имя города, в который совершались эти поездки.

Код Access-SQL.

SELECT City.Name, Trip.ID FROM City INNER JOIN ( ( q33w_TripWomenCount INNER JOIN q31_TripPersonCount ON (q33w_TripWomenCount.CountPerson = q31_TripPersonCount.[Count-ID]) AND (q33w_TripWomenCount.TripID = q31_TripPersonCount.TripID) ) INNER JOIN Trip ON q31_TripPersonCount.TripID = Trip.ID ) ON City.ID = Trip.City;

Запрос q34m_TripMenOnly
Данный запрос отличается от предыдущего тем, что в него добавлены ещё две таблицы для того, чтобы получить свеедния о людях, участвовавших в поездках. Ещё одно отличие в том, что отбираются поездки без участия женщин, а не мужчин.

Код Access-SQL.

SELECT q33m_TripMenCount.TripID, q33m_TripMenCount.[Count-PersonID], City.Name, Person.Name, Person.ID AS PersonID FROM ( City INNER JOIN ( ( q33m_TripMenCount INNER JOIN q31_TripPersonCount ON (q33m_TripMenCount.[Count-PersonID] = q31_TripPersonCount.[Count-ID]) AND (q33m_TripMenCount.TripID = q31_TripPersonCount.TripID) ) INNER JOIN Trip ON q31_TripPersonCount.TripID = Trip.ID ) ON City.ID = Trip.City ) INNER JOIN ( Person INNER JOIN Person_Trip ON Person.ID = Person_Trip.PersonID ) ON Trip.ID = Person_Trip.TripID ORDER BY q33m_TripMenCount.TripID, Person.ID;

Запрос q35_TripSinglePersonSex
Пример на групповую операцию с условием отбора.
Группировка по полям, относящимся к поездке и по полю, обозначающему пол человека.
Для каждой поездки участвовавшие в ней мужчины и женщины собираются в отдельные группы.
То есть, каждая группа состоит из людей одного пола, участвовавших в одной поездке.
Групповая операция Count подсчитывает число людей в каждой из этих групп.
Условие отбора, ограничивающее число людей в группе одним человеком, отбирает такие группы, в которых либо один мужчина, либо одна женщина.
Таким образом можно отобрать поездки, в которых представительство одного из полов ограничено одним человеком, при этом число участников противоположного пола может быть любым.

Код Access-SQL.

SELECT Count(Person.ID) AS [Count-ID], Person.Sex, Trip.ID AS TripID, City.Name FROM ( City INNER JOIN Trip ON City.ID = Trip.City ) INNER JOIN ( Person INNER JOIN Person_Trip ON Person.ID = Person_Trip.PersonID ) ON Trip.ID = Person_Trip.TripID GROUP BY Person.Sex, Trip.ID, City.Name HAVING (((Count(Person.ID))=1) AND ((Person.Sex)="m")) OR (((Count(Person.ID))=1) AND ((Person.Sex)="f"));

Запрос q36_TripMan&Woman_1
Этот запрос находит поездки в которых участвовало ровно два человека — мужчина и женщина.
Для нахождения таких поездок в данном запросе используется запрос q35, причём используется дважды.
На записи из первой результирующей таблицы запроса q35 накладывается условие отбора, отбирающее поездки, в которых участвовала одна женщина, а из второй результирующей таблицы запроса q35 при помощи подобного условия отбираются поездки, в которых участвовал один мужчина. Связка двух результирующих таблиц по полю кода поездки TripID, сопоставит отобранные записи из первой и второй таблицы и выберет те, в которых участвовал и один мужчина, и одна женщина.
Поскольку кроме мужчин и женщин других участников нет, то очевидно в этих поездках участвовало только два человека.

Код Access-SQL.

SELECT q35_TripSinglePersonSex.Sex, q35_TripSinglePersonSex_1.Sex, q35_TripSinglePersonSex_1.TripID, q35_TripSinglePersonSex.Name FROM q35_TripSinglePersonSex INNER JOIN q35_TripSinglePersonSex AS q35_TripSinglePersonSex_1 ON q35_TripSinglePersonSex.TripID = q35_TripSinglePersonSex_1.TripID WHERE ( ((q35_TripSinglePersonSex.Sex)="m") AND ((q35_TripSinglePersonSex_1.Sex)="f") );

Запрос q37_TripMan&Woman_2
Этот запрос делает то же, что и предыдущий, но другим способом.
Из результирующей таблицы запроса q33m по условию отбора выбираются поездки, в которых участвовал один мужчина.
Из результирующей таблицы запроса q33w по условию отбора выбираются поездки, в которых участвовала одна женщина.
Связка двух таблиц по полю кода поездки TripID, сопоставит отобранные записи из первой и второй таблицы и выберет те, в которых участвовал и один мужчина, и одна женщина.

Код Access-SQL.

SELECT q33m_TripMenCount.[Count-PersonID], q33w_TripWomenCount.CountPerson, q33w_TripWomenCount.TripID FROM q33m_TripMenCount INNER JOIN q33w_TripWomenCount ON q33m_TripMenCount.TripID = q33w_TripWomenCount.TripID WHERE (((q33m_TripMenCount.[Count-PersonID])=1) AND ((q33w_TripWomenCount.CountPerson)=1));

Запрос q38_TripMan&Woman_3
Этот запрос делает то же, что и два предыдущих, но другим способом.
Здесь используются три запроса q32m, q32w, q31.
Первые два запроса связаны с третьим по полю кода поездки TripID.
Из запроса q31, подсчитывающего общее число участников в каждой поездке, отбираются записи о поездках, в которых число участников равно двум.
При этом, хотя бы одна запись об этой поездке должна быть как в результирующей таблице запроса q32m, так и в результирующей таблице запроса q32w, поскольку в противном случае не будет выполнено условие хотя бы одной из связей запроса q31 c запросами q32m и q32w.
Таблицы Trip и City добавлены в запрос для того, чтобы получить имя города, в которые эти поездки совершались.

Код Access-SQL.

SELECT q32m_TripMen.Name, q32w_TripWomen.Name, City.Name, Trip.StartDate, Trip.FinishDate, q31_TripPersonCount.TripID, q31_TripPersonCount.[Count-ID] FROM City INNER JOIN ( q32m_TripMen INNER JOIN ( q32w_TripWomen INNER JOIN ( q31_TripPersonCount INNER JOIN Trip ON q31_TripPersonCount.TripID = Trip.ID ) ON q32w_TripWomen.TripID = q31_TripPersonCount.TripID ) ON q32m_TripMen.TripID = q31_TripPersonCount.TripID ) ON City.ID = Trip.City WHERE (((q31_TripPersonCount.[Count-ID])=2));

Запрос q39_ContemporaryTrips
Находит пересекающиеся во времени поездки.
Условие наложено на начало второй поездки, которое должно быть между началом и концом первой поездки.
Второе условие необходимо для того, чтобы находить поездки, начавшиеся одновременно. Здесь необходимо наложить ещё условие и на TripID, чтобы избежать повторений.

Код Access-SQL.

SELECT Trip.ID AS t0, Trip.StartDate, Trip.FinishDate, Trip_1.ID AS t1, Trip_1.StartDate, Trip_1.FinishDate FROM Trip, Trip AS Trip_1 WHERE ( ((Trip_1.ID)<>[Trip].[ID]) AND ( (Trip_1.StartDate)>[Trip].[StartDate] And (Trip_1.StartDate)<[trip].[FinishDate] ) ) OR ( ((Trip_1.ID)>[Trip].[ID]) AND ( (Trip_1.StartDate)=[Trip].[StartDate] And (Trip_1.StartDate)<[trip].[FinishDate] ) ) ORDER BY Trip.ID, Trip_1.ID;

Запрос q39n_ContemporaryTrips
Как и предыдущий запрос находит пересекающиеся во времени поездки.
Отличается более простым условием:
вторая поездка должна начаться не позднее окончания первой и завершиться не раньше, чем начнётся первая.
Условие на TripID не допускает повторений при выводе полностью совпавших по времени поездок.

Код Access-SQL.

SELECT Trip.ID, Trip.StartDate, Trip.Type, Trip_1.ID, Trip_1.StartDate, Trip_1.FinishDate, Trip_1.Type FROM Trip, Trip AS Trip_1 WHERE ( ((Trip_1.ID)>[Trip].[ID]) AND ((Trip_1.StartDate)<=[Trip].[FinishDate]) AND ((Trip_1.FinishDate)>=[Trip].[StartDate]) ) ORDER BY Trip.ID, Trip_1.ID;

Запрос q40_ImpossibleTripping
Проверяет, нет ли данных о том, что кто-то участвовал одновременно в двух разных поездках.
Для проверки использует предыдущий запрос.

Код Access-SQL.

SELECT Person.ID, Person.Name, q39_ContemporaryTrips.t0, q39_ContemporaryTrips.t1 FROM ( q39_ContemporaryTrips INNER JOIN Person_Trip ON q39_ContemporaryTrips.t0 = Person_Trip.TripID ) INNER JOIN ( Person_Trip AS Person_Trip_1 INNER JOIN Person ON Person_Trip_1.PersonID = Person.ID ) ON (Person_Trip.PersonID = PersonID) AND (q39_ContemporaryTrips.t1 = Person_Trip_1.TripID)

Запрос q41_CityWithoutMen
Находит города, из которых приехали только женщины.
Количество приехавших женщин из таких городов совпадает с количеством всех приехавших оттуда людей.

Код Access-SQL.

SELECT q21_CountNativeCity.NativeCity, q21_CountNativeCity.City FROM q21_CountNativeCity INNER JOIN q21w_CityWomenCount ON (q21_CountNativeCity.Count_N = q21w_CityWomenCount.[Count-ID]) AND (q21_CountNativeCity.NativeCity = q21w_CityWomenCount.NativeCity);

Запрос q42n_CityWithoutMen
Находит все города, не выбраные предыдущим запросом.
То есть те, из которых приехал хотя бы один мужчина, либо вообще никто не приехал.
В этом запросе не обойтись без кода на языке SQL, который определяет условие выбора города.

Код Access-SQL.

SELECT City.ID, City.Name FROM City WHERE (((City.ID) Not In (SELECT NativeCity FROM q41_CityWithoutMen)));

Запрос q43_person_2_ city
Находит все города, из которых приехало ровно два человека.
Подсчитывает число людей из каждого города и отбирает удовлетворяющие условию.

Код Access-SQL.

SELECT Count(Person.ID) AS [Count-ID], Person.NativeCity FROM Person GROUP BY Person.NativeCity HAVING (((Count(Person.ID))=2));

Запрос q44_mf_1_city
Находит все города, из которых приехал ровно один человек хотя бы одного из полов.
То есть, такие города, из которых приехал
* или один мужчина и любое число женщин,
* или одна женщина и любое число мужчин,
* или один мужчина и одна женщина.

Код Access-SQL.

SELECT Count(Person.ID) AS [Count-ID], Person.Sex, Person.NativeCity FROM Person GROUP BY Person.Sex, Person.NativeCity HAVING (((Count(Person.ID))=1));

Запрос q45_mf11
Находит все города, из которых приехали ровно два человека: один мужчина и одна женщина, а также находит этих людей.
Использует два предыдущих запроса для нахождения городов из которых приехало ровно два человека, таких, что представителя каждого пола ровно один.


Обратите внимание на связь между полями Sex. Отсутствие этой связи вызовет повторение записей в итоговой таблице.

Код Access-SQL.

SELECT q44_mf_1_city.[Count-ID], Person.Name, Person.Sex, City.Name, [q43_person_2_ city].NativeCity FROM City INNER JOIN (([q43_person_2_ city] INNER JOIN q44_mf_1_city ON [q43_person_2_ city].NativeCity = q44_mf_1_city.NativeCity ) INNER JOIN Person ON (q44_mf_1_city.Sex = Person.Sex) AND (q44_mf_1_city.NativeCity = Person.NativeCity) ) ON City.ID = Person.NativeCity;

Запрос q46_Trip_NativeCity
Для каждой поездки находит все города, представители которых в этой поездке участвовали.

Код Access-SQL.

SELECT Person_Trip.TripID, Person.NativeCity FROM Person INNER JOIN Person_Trip ON Person.ID = Person_Trip.PersonID GROUP BY Person_Trip.TripID, Person.NativeCity;

Запрос q47_Trip_NativeCityCount
Находит поездки, в которых участвовали представители только одного города.
Используя предыдущий запрос подсчитывает количество городов, представители которых принимали участие в каждой поездке, и отбирает поездки удовлетворяющие условию.

Код Access-SQL.

SELECT q46_Trip_NativeCity.TripID, Count(q46_Trip_NativeCity.NativeCity) AS [Count-NativeCity1] FROM q46_Trip_NativeCity GROUP BY q46_Trip_NativeCity.TripID HAVING (((Count(q46_Trip_NativeCity.NativeCity))=1));

Запрос q48_Trip_PersonCount2
Находит поездки с участием двух и более человек.
Подсчитывает количество людей в каждой поездке и отбирает удовлетворяющие условию.

Код Access-SQL.

SELECT Person_Trip.TripID, Count(Person_Trip.PersonID) AS [Count-PersonID] FROM Person_Trip GROUP BY Person_Trip.TripID HAVING (((Count(Person_Trip.PersonID))>1));

Запрос q49_Trip_NativeCity_Person
Находит людей, участвовавших в таких поездках, в которых были представители только одного города.
Использует два предыдущих запроса для нахождения поездок с участием представителей только одного города. При этом не выбираются поездки, в которых участвовал только один человек.

Код Access-SQL.

SELECT Person.Name, Person_Trip.PersonID, City.Name AS nativeCityName, City_1.Name AS visitedCityName, Trip.ID AS TripID FROM ( Trip INNER JOIN City AS City_1 ON Trip.City = City_1.ID ) INNER JOIN ( ( City INNER JOIN Person ON City.ID = Person.NativeCity ) INNER JOIN ( Person_Trip INNER JOIN ( q47_Trip_NativeCityCount INNER JOIN q48_Trip_PersonCount2 ON q47_Trip_NativeCityCount.TripID = q48_Trip_PersonCount2.TripID ) ON Person_Trip.TripID = q47_Trip_NativeCityCount.TripID ) ON Person.ID = Person_Trip.PersonID ) ON Trip.ID = Person_Trip.TripID;

Запрос q50_TripTogetherTwoPersons
Строит отношение человек-человек отражающее их участие в общей поездке. То есть, находит: кто с кем в какой поездке участвовал.

Код Access-SQL.

SELECT Person.ID, Person_Trip.TripID, Person_1.ID, Person.Name, Person_1.Name, City.Name, Trip.StartDate, Trip.FinishDate FROM ( City INNER JOIN Trip ON City.ID = Trip.City ) INNER JOIN ( Person INNER JOIN ( Person_Trip INNER JOIN ( Person_Trip AS Person_Trip_1 INNER JOIN Person AS Person_1 ON Person_Trip_1.PersonID = Person_1.ID ) ON Person_Trip.TripID = Person_Trip_1.TripID ) ON Person.ID = Person_Trip.PersonID ) ON Trip.ID = Person_Trip.TripID WHERE (((Person_1.ID)>[Person].[id])) ORDER BY Person.ID, Person_1.ID;

Запрос q51_TripTogetherCount
Подсчитывает сколько раз два человека оказывались в общей поездке.
Использует предыдущий запрос. Для каждой пары, найденной в предыдущем запросе подсчитывает количество поездок, в которых оба этих человека вместе принимали участие.

Код Access-SQL.

SELECT q50_TripTogetherTwoPersons.Person.Name AS P1_Name, q50_TripTogetherTwoPersons.Person_1.Name AS P2_Name, Count(q50_TripTogetherTwoPersons.TripID) AS countTrip, q50_TripTogetherTwoPersons.Person.ID AS P1_ID, q50_TripTogetherTwoPersons.Person_1.ID AS P2_ID FROM q50_TripTogetherTwoPersons GROUP BY q50_TripTogetherTwoPersons.Person.Name, q50_TripTogetherTwoPersons.Person_1.Name, q50_TripTogetherTwoPersons.Person.ID, q50_TripTogetherTwoPersons.Person_1.ID;

Запрос q52_TripTogetherManAndWoman
Этот запрос, так же как и запрос q50, ищет для каждого человека всех людей, принимавших с ним участие в общей поездке.
Разница заключается в том, что в энастоящем запросе один человек является мужчиной, а другой — женщиной.

Код Access-SQL.

SELECT Person.ID, Person_Trip.TripID, Person_1.ID, Person.Name, Person_1.Name, City.Name, Trip.StartDate, Trip.FinishDate FROM ( City INNER JOIN Trip ON City.ID = Trip.City ) INNER JOIN ( Person INNER JOIN ( Person_Trip INNER JOIN ( Person_Trip AS Person_Trip_1 INNER JOIN Person AS Person_1 ON Person_Trip_1.PersonID = Person_1.ID ) ON Person_Trip.TripID = Person_Trip_1.TripID ) ON Person.ID = Person_Trip.PersonID ) ON Trip.ID = Person_Trip.TripID WHERE (((Person.Sex)="M") AND ((Person_1.Sex)="F")) ORDER BY Person.ID, Person_Trip.TripID;

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

Код Access-SQL.

SELECT q52_TripTogetherManAndWoman.Person.ID AS P1_ID, q52_TripTogetherManAndWoman.Person_1.ID AS P2_ID, q52_TripTogetherManAndWoman.Person.Name AS P1_Name, q52_TripTogetherManAndWoman.Person_1.Name AS P2_Name, Count(q52_TripTogetherManAndWoman.TripID) AS CountTrip FROM q52_TripTogetherManAndWoman GROUP BY q52_TripTogetherManAndWoman.Person.ID, q52_TripTogetherManAndWoman.Person_1.ID, q52_TripTogetherManAndWoman.Person.Name, q52_TripTogetherManAndWoman.Person_1.Name;