Учебная база данных
Учебная база данных содержит информацию о содержимом телефонной книги и журнале вызовов.
Необходимо хранить информацию о контактах: название (ФИО), возраст, пол (мужской, женский), мелодия контакта, список номеров, список групп контактов (семья, друзья, коллеги, VIP и т.п.). Для одного контакта может быть сохранено несколько номеров телефонов, но каждый номер телефона может относиться только к одному контакту. Каждый контакт может относиться к нескольким группам, к каждой группе может относиться несколько контактов. Мелодия может быть назначена контакту или группе контактов. Для номера телефона необходимо также хранить тип номера (мобильный, домашний, рабочий и т.п.). В журнале вызовов необходимо хранить тип вызова (входящий, исходящий, пропущенный), дату и время вызова, номер телефона на связи, длительность и стоимость вызова.
Рисунок 1 - Схема учебной базы данных
Определение и манипулирование данными
Создать аналог таблицы «Вызовы». Столбцы: «код вызова», «код типа вызова», «дата и время вызова», «код номера вызова», «длительность».
Добавить столбец «стоимость вызова».
Добавить первую запись (исходящий вызов, код 2, стоимость 0).
Добавить вторую запись (пропущенный вызов, код 3).
Добавить третью запись (входящий вызов, код 1, столбец «стоимость» оставить пустым).
Задать другой код номера вызова (4) и другую длительность в записи, где стоимость не была заполнена (IS NULL).
Установить стоимость каждого звонка из расчета 0,6 руб./мин.
Увеличить стоимость каждого звонка на 50%.
Установить нулевую стоимость для всех звонков, не являющихся исходящими (код не равен 2).
Удалить все пропущенные звонки (код 3).
Удалить все записи.
Удалить столбец «стоимость вызова».
Удалить созданную таблицу.
Запросы на выборку
Обязательные предложения оператора SELECT
Пример 1. Вывести коды и названия мелодий.
Пример 2. Вывести информацию о контактах (код, имя, возраст).
Пример 3. Для каждого вызова вывести фразу типа: “Стоимость звонка 1 равна 3,50р.”.
SELECT 'Стоимость звонка ', ID, ' равна ', STOIM
FROM VYZOVY
Пример 4. Вывести код, длительность и стоимость звонка в долларах (по курсу 30 руб./$).
SELECT ID, DLIT, STOIM/30 AS DOLLARS
FROM VYZOVY
Пример 5. Вывести полную информацию о группах контактов.
Пример 6. Вывести полную информацию о вызовах, а также стоимость минуты каждого вызова.
SELECT *, STOIM/DLIT*60 AS TARIF
FROM VYZOVY
Пример 7. Вывести коды (ID) мелодий, которые уже использовались (были назначены хотя бы одному контакту).
Отбор строк (предложение where)
Пример 8. Вывести имена контактов, чей возраст больше 20.
Пример 9. Вывести полную информацию о звонках, стоимость которых рассчитывалась исходя из тарифа 1.2 руб/мин.
SELECT *
FROM VYZOVY
WHERE STOIM=DLIT/60*1.2
Пример 10. Вывести названия мелодий с кодами (ID) от 5 до 15 включительно.
Пример 11. Вывести номера телефонов, принадлежащих контактам с кодами (ID) 1, 2, 4, 5.
SELECT NOMER, KONT_ID
FROM NOMERA
WHERE KONT_ID IN (1,2,4,5)
Пример 12. Вывести номера телефонов, не принадлежащих контактам с кодами 1, 2, 4, 5.
Пример 13. Вывести номера телефонов оператора TELE2 с кодом 953.
Пример 14. Вывести список групп контактов, для которых не указана мелодия.
Пример 15. Вывести список исходящих вызовов (код=2) длительностью более 50 секунд.
Пример 16. Вывести список контактов, для которых указан лишь один из параметров: пол или возраст.
SELECT *
FROM KONTAKTY
WHERE ((POL_ID IS NULL) AND (VOZRAST IS NOT NULL)) OR
((POL_ID IS NOT NULL) AND (VOZRAST IS NULL))
Сортировка результатов запроса (предложение ORDER BY)
Пример 17. Вывести список групп контактов в алфавитном порядке.
Пример 18. Вывести список входящих звонков в порядке убывания их длительности.
Пример 19. Вывести список исходящих звонков. Результаты отсортировать сначала в порядке убывания их тарифов, а затем – в хронологическом порядке.
SELECT TIP_ID, VREMYA, NOMER_ID, DLIT, STOIM, STOIM/DLIT*60
FROM VYZOVY
WHERE TIP_ID=2
ORDER BY 6 DESC, VREMYA ASC
Многотабличные запросы
Пример. Пусть имеется база данных, в которой хранится информация о домашних животных и их владельцах.
Схема данных имеет следующий вид:
Содержимое таблицы OWNERS (владельцы):
Содержимое таблицы ANIMALS (животные):
Необходимо вывести список домашних животных и имена их владельцев.
Как видно из схемы данных, в таблице ANIMALS определено поле owner_id, которое является внешним ключом: в этом поле содержится идентификационный номер владельца для каждого животного (считается, что у одного животного может быть только один владелец). Именно благодаря связи между полями owner_id и id (из таблицы OWNERS) для каждого животного можно узнать имя его владельца.
Очевидно, что для получения требуемого результата нам потребуются обе таблицы. Для начала будем считать, что нам требуется полная информация о животных и их владельцах.
Тогда первые два предложения искомого запроса можно записать так:
SELECT *
FROM ANIMALS, OWNERS
Давайте проанализируем, как будет выполняться этот запрос.
Так как в предложении FROM перечислены названия двух таблиц, то SQL выполнит декартово произведение (умножение) этих двух таблиц и сформирует одну результирующую таблицу (таблицу произведения). При этом результирующая таблица будет содержать все возможные комбинации строк первой и второй таблиц, т.е. «на всякий случай» каждому животному будет поставлен в соответствие каждый владелец. Количество строк в результирующей таблице будет равно количеству строк первой таблицы, умноженному на количество строк во второй таблице. В нашем случае результирующая таблица будет содержать 4x3=12 строк.
Так как в предложении SELECT указана звездочка, то результирующая таблица будет содержать все столбцы таблицы ANIMALS и следующие за ними все столбцы таблицы OWNERS.
Таким образом, в результате выполнения выше приведенного запроса получим следующий результат:
Очевидно, что нас не устраивают такие результаты. Получается, что каждое животное принадлежит сразу всем владельцам (и наоборот), что, конечно же, неверно (как видно из содержимого исходных таблиц). Другими словами, таблица произведения содержит много лишних, некорректных строк, в которых представлена неверная (несвязанная) информация. Справедливости ради отметим, что наряду с некорректными строками данная таблица произведения содержит также и все нужные нам, «правильные» строки со связанной информацией, как видно из следующей таблицы.
В этих «правильных» строках в связанных столбцах (согласно схеме данных это столбцы owner_id и OWNERS.id) находятся одинаковые значения, т.е. эти строки удовлетворяют следующему условию (условию равенства связанных столбцов):
Owner_id=OWNERS.id
Таким образом, из всех строк результирующей таблицы необходимо оставить только нужные, или, другими словами, отбросить некоторые ненужные.
Произведение двух таблиц, из которого удалены некоторые строки, называется соединением этих таблиц.
Из вышесказанного следует вывод, что для правильной работы многотабличного запроса необходимо получить не произведение всех исходных таблиц (что осуществляется при указании названий всех таблиц в предложении FROM), а соединение всех исходных таблиц, в котором остаются только строки со связанной корректной информацией.
Следовательно, для получения соединения всех исходных таблиц (фактически для отбрасывания из таблицы произведения некоторых некорректных строк), в многотабличном запросе обязательно присутствует предложение WHERE, в котором указывается условие (или несколько условий) равенства связанных столбцов.
В нашем случае предложение WHERE будет выглядеть так:
WHERE owner_id=OWNERS.id
Введя псевдонимы для обеих таблиц и учитывая то, что согласно заданию в качестве возвращаемых столбцов нам потребуются только столбцы vid, nickname и name, окончательный правильный вариант требуемого запроса запишем в следующем виде:
SELECT A.vid, A.nickname, O.name
FROM ANIMALS A, OWNERS О
WHERE A.owner_id=O.id;
В результате выполнения этого запроса мы получим требуемую информацию:
Пример 20. Вывести список контактов и номеров телефонов.
Пример 21. Вывести список контактов-женщин.
Пример 22. Вывести список контактов с указанием групп контактов.
Пример 23. Вывести список вызовов определенного контакта.
Пример 24. Вывести список контактов, с которыми состоялся хотя бы один телефонный
Пример 25. Вывести подробную информацию о вызовах (в хронологическом порядке) с указанием типа вызова, номера телефона, типа номера телефона, контакта и мелодии контакта.
SELECT V.ID, TV.NAZV,K.NAME1, N.NOMER,TN.NAZV,V.VREMYA,V.DLIT, V.STOIM, M.NAZV
FROM VYZOVY V, TIPY_VYZ TV, NOMERA N, TIPY_NOM TN, KONTAKTY K, MELODII M
WHERE (V.TIP_ID=TV.ID) AND (V.NOMER_ID=N.ID) AND (N.TIP_ID=TN.ID)
AND (N.KONT_ID=K.ID) AND (K.MEL_ID=M.ID)
ORDER BY V.VREMYA
Соединение таблиц в предложении FROM.
Пример 26 (20). Вывести список контактов и номеров телефонов.
Пример 27. Вывести список контактов c указанием пола.
Некорректно:
SELECT K.NAME1, P.NAZV
FROM KONTAKTY K, POL P
WHERE K.POL_ID=P.ID
ORDER BY K.NAME1
Пример 28 (22). Вывести список контактов с указанием групп контактов.
SELECT K.NAME1, G.NAZV
FROM (KONTAKTY K LEFT JOIN KONT_GR KG ON K.ID = KG.KONT_ID)
LEFT JOIN GRUPPY G ON KG.GR_ID=G.ID
ORDER BY K.NAME1, G.NAZV
Агрегатные функции
Пример 29. Вычислить суммарную стоимость всех вызовов.
Пример 30. Вычислить средний возраст для контактов.
Пример 31. Вычислить минимальную и максимальную длительность исходящих вызовов.
SELECT MIN(DLIT), MAX(DLIT)
FROM VYZOVY V, TIPY_VYZ T
WHERE (V.TIP_ID=T.ID) AND (T.NAZV='Исходящий')
Пример 32. Вывести дату и время самого первого вызова.
Пример 33. Вычислить количество контактов старше 30 лет.
SELECT COUNT(ID)
FROM KONTAKTY
WHERE VOZRAST>30
Или
SELECT COUNT(*)
FROM KONTAKTY
WHERE VOZRAST>30
Пример 34. Вычислить количество мелодий, уже назначенных для какого-либо контакта.
Не работает в ACCESS:
SELECT COUNT(DISTINCT MEL_ID)
FROM KONTAKTY
Группировка строк (предложение GROUP BY)
Пример 35а. Посчитать общую длительность всех вызовов.
SELECT SUM(DLIT)
FROM VYZOVY
Пример 35б. Посчитать общую длительность всех исходящих вызовов (TIP_ID=2).
SELECT SUM(DLIT)
FROM VYZOVY
WHERE TIP_ID=2
Пример 35в. Посчитать общую длительность вызовов каждого типа.
вариант 1 (известны коды)
SELECT TIP_ID, SUM(DLIT)
FROM VYZOVY
GROUP BY TIP_ID
вариант 2 (известны названия)
SELECT T.NAZV, SUM(DLIT)
FROM VYZOVY V, TIPY_VYZ T
WHERE V.TIP_ID=T.ID
GROUP BY T.ID, T.NAZV
Пример 36. Вывести количество номеров определенного контакта.
Пример 37. Вывести количество номеров для каждого контакта.
Пример 38. Вывести количество контактов в каждой группе.
Пример 39. Вычислить, со сколькими контактами был зафиксирован каждый тип вызова.
Неправильно (вычисляется количество вызовов, а не количество контактов):
SELECT T.NAZV, COUNT(N.KONT_ID)
FROM TIPY_VYZ T, VYZOVY V, NOMERA N
WHERE (T.ID=V.TIP_ID) AND (V.NOMER_ID=N.ID)
GROUP BY T.ID, T.NAZV
Не работает в Microsoft Access:
SELECT T.NAZV, COUNT(DISTINCT N.KONT_ID)
FROM TIPY_VYZ T, VYZOVY V, NOMERA N
WHERE (T.ID=V.TIP_ID) AND (V.NOMER_ID=N.ID)
GROUP BY T.ID, T.NAZV
Пример 40. Для каждого контакта вывести дату и время самого первого вызова каждого типа.
SELECT K.NAME1, T.NAZV, MIN(VREMYA) AS FIRST
FROM KONTAKTY K, NOMERA N, VYZOVY V, TIPY_VYZ T
WHERE (T.ID=V.TIP_ID) AND (V.NOMER_ID=N.ID) AND (N.KONT_ID=K.ID)
GROUP BY K.ID, K.NAME1, T.NAZV