 |
Выборка из нескольких таблиц.
|
|
|
|
Фильтрация декартового произведения. Определите атрибуты двух и более объектов, связанных следующими отношениями:
- (не) этот продавец (не) обслуживает (не) этого покупателя
- (не) этот продавец (не) обслуживает (не) этот заказ
- (не) этот покупатель (не) сделал (не) этот заказ
- этот заказ - на (не) этот товар
- (не) этот покупатель (не) сделал (не этот) заказ (не) этому продавцу
- (не этот) покупатель (не) сделал (не этот) заказ (не этому) продавцу на (не) этот товар
- эти продавец и покупатель - однофамильцы
- эти продавец и покупатель живут в одном городе
- этот продавец старше (младше, ровесник) этого покупателя
Рефлексивные связи. Определите атрибуты двух и более объектов, связанных следующими отношениями
- эти покупатели - однофамильцы
- эти покупатели живут в одном городе
- этот покупатель старше (младше, ровесник) другого покупателя
- эти продавцы - однофамильцы
- эти продавцы живут в одном городе
- этот продавцы старше (младше, ровесник) другого продавца
- этот заказ - на более крупную сумму, чем другой
- этот заказ сделан раньше, чем другой
- эти заказы обслуживает один продавец
- эти заказы - одного покупателя
- эти заказы - на один товар
- это товар - дороже (дешевле, стоит столько же), чем другой
- эти товары (не) произведены на одном предприятии
Декодирование.
Определите атрибуты объектов по свойствам других (одного или нескольких) связанных с ними объектов.
Варианты задания
Определите фамилии продавцов,
- обслуживавших дорогие заказы
- обслуживавших кредитоспособных покупателей
Определите цену и наименования товаров
- для дорогих заказов
- для заказов, обсуживавшихся высокооплачиваемыми продавцами
|
|
|
- для заказов, обсуживавших молодых покупателей
Группировка и групповые вычисления.
Определите значения групповых функций для заданного определения группы
Подсчитайте
- средний, максимальный, минимальный возраст (дата рождения, сумма кредита) покупателя и количество покупателей
- по всем покупателям
- по всем категориям покупателям, перечисленных выше
- для каждого города
Подсчитайте
- средний, максимальный, минимальный процент комиссионных (возраст, дата рождения) продавца и количество продавцов
- по всем продавцам
- по всем категориям продавцов, перечисленных выше
- для каждого города
Подчитайте
- среднюю, максимальную, минимальную, совокупную сумму заказов и количество заказов
- по всем заказам
- по всем категориям заказов, перечисленных выше
- данного покупателя
- данного продавца
- для каждого покупателя
- для каждого продавца
- для каждой пары покупатель-продавец
-
Подчитайте
- минимальную, максимальную, среднюю цену товара
- минимальное, максимальное, среднее, совокупное количество единиц товара, хранящихся на складе
- количество единиц товара, по каждому типу товара
- по всем товарам
- по всем категориям товаров, перечисленным выше
Фильтрация групп. Подсчитайте значения групповых функций, выводя лишь значения, попадающие в заданный интервал.
Вариант задания. Определить количество товаров, которых мало на складе (т.е. это количество меньше некоторого заданного числа - скажем, 100)
Предикаты, использующие выборку. Вложенные запросы
Сравнение с выборкой. Определите атрибуты объектов, для которых значение некоторого поля больше (меньше, равно) среднего значения этого (или другого) поля этой (или другой) таблицы по группе.
Варианты задания.
Выведите атрибуты сравнительно молодых покупателей - т.е. покупателей, возраст которых меньше среднего значения по всем покупателям.
|
|
|
Выведите атрибуты сравнительно дорогих товаров, по каждому типу товаров.
Отношение принадлежности. Определите атрибуты объектов таких, что связанные с ними объекты принадлежат данной категории.
Варианты задания.
Определите атрибуты покупателей, обслуживающихся казанскими продавцами.
Определите атрибуты товаров, проданных за последний месяц молодыми продавцами.
Определите атрибуты продавцов, продавших хотя бы одно наименование из товаров, проданных данным продавцем.
Кванторные предикаты. Определите атрибуты объектов, для которых категория связанных с ними объектов (не)пуста.
Определите атрибуты продавцов, (не) обслуживающих хотя бы одного казанского покупателя.
Определите атрибуты продавцов, (не) продавших за последний месяц дорогие товары.
(Связанные запросы).
(Внешние) объединения. Определите совпадающие атрибуты объектов из разных таблиц.
Вариант задания. Определите фамилии и возраст покупателей и продавцов, проживающих в данном городе (например, Казани).
МОДИФИКАЦИЯ ТАБЛИЦ
Удалите из таблиц заданные категории объектов.
Вариант задания.
Удалите все сведения о товарах, не пользовавшихся спросом в последнее время (скажем, год).
Удалите сведения о продавцах, не осуществляших никаких продаж за последнее время.
Удалите сведения о покупателях, не делавших покупок за последнее время.
Измените значения атрибутов объектов заданной категории.
Тема 3. Задачи-многоходовки. Представления и транзакции.
В контексте заданной предметной области, любой человек характеризуется следующими данными.
n NAMe ФАМИЛИЯ (30 символов)
n ID ПАСПОРТ - № паспорта, 10 символов (очевидно, уникальный для каждого человека)
n MUM МАТЬ - № паспорта матери (NULL, если нет сведений)
n DAD ОТЕЦ - № паспорта отца (NULL, если нет сведений)
n СУПРУГ - № паспорта жены/мужа (NULL, если нет сведений)
n BIRTDAY ДАТА - дата рождения (тип дата)
n INCOME ДОХОД - ежемесячный доход (зарплата, стипендия и т.п.), вещественное 99.999.999,99
n CITY ГОРОД - город проживания (30 символов)
n TAX НАЛОГ - сумма подоходного налога, в % (целое)
|
|
|
Кроме того, в информацию о студентах дополнительно включаются сведения
n INSTITUTE ВУЗ - сокращенное название ВУЗа, 10 символов - например, ‘КГУ’, ‘КГМУ’ и т.п.
n SEX ПОЛ - один из символов ‘М’,’Ж’
БД включает таблицы СТУДЕНТЫ, ОТЦЫ и МАТЕРИ, включающую информацию о студентах и их родителях, соответственно.
ПРИМЕЧАНИЕ. Как всегда, считаем, что допустимо применение имен на кириллице.
- Выдать список студентов, с указанием фамилий и дохода родителей, упорядоченный по возрасту студентов. Студентов, чьи доходы ниже 100 руб., в список не включать.
- Выдать упорядоченный по фамилиям список живущих в Казани, Москве и Самаре родителей, с указанием - мать это или отец, чей доход не превышает 300 руб.
- Выдать все пары студентов-однофамильцев из КГУ, с указанием фамилии и, для обоих, паспортных данных. Дублирующиеся пары не включать.
- Выбрать всю информацию об отцах, чьи доходы выше среднего по городу, в котором они живут.
- Выдать список живущих вне Казани матерей, имеющих не менее - двух сыновей-студентов, учащихся в Казани.
- Выбрать всю информацию об студентах, чьи доходы выше среднего для казанских студентов.
"Многоходовки" – т.е. задачи, решение которых не реализуется легко единственной командой SQL проще решать, используя представления или курсоры, например:
- Удалить информацию о студентах ВУЗА 'АГУ' а также их родителях - в случае, если у тех нет других детей-студентов (вариант посложнее - включить в число родителей студентов их бабушек и дедушек)
Вариант решения:
- запомнить студентов нужного ВУЗа и ссылки на их отцов,
- запомнить отцов, у которых все дети (если вообще есть таковые) учатся в нужном ВУЗе
- удалить информацию об отцах, входящих в первый и второй список
- … сделать то же с мамами
- удалить информацию о студентах нужного ВУЗа
1. Create View Dad1 as Select Distinct Dad from Students where Institute=’АГУ’
2. Create View as Dad2 as Select Id from Dad where NOT Exists (Select Id from Students where Dad=Dad.Id and Institute<>’АГУ’)
|
|
|
