metoda_labs_DBO_26_09_2013

41

1.Напишите запрос для вывода названия, автора и цену книг из таблицы Книги.

2.Напишите запрос для вывода всех уникальных имен авторов книг в текущем порядке из таблицы Книги.

3.Напишите запрос для вывода всех заказов из таблицы Заказы со значениями суммы предоплаты выше 1,000 рублей.

4.Напишите запрос для вывода всех заказов из таблицы Заказы, для которых сумма предоплаты составляет не менее 30% от стоимости книги.

5.Напишите запрос к таблице Заказы для вывода всех оплаченных заказов, соответствующих заказчику с именем «Иванов И.».

6.Напишите запрос для вывода всех строк из таблицы Заказчики, относящихся к заказчикам, проживающим в г.Бресте.

7.Напишите запрос для вывода кодов всех книг, их цен и кодов поставщиков для книг, поставленных из Москвы.

8.Что может быть выведено в результате следующего запроса ?

SELECT * FROM “Книги”

WHERE (“Цена” < 10000 OR NOT (“Автор” IS NULL AND “Остаток” > 50))

9. Как можно иначе переписать запрос:

SELECT *

FROM ”Заказы”

WHERE (“Дата” > ‘01/01/01’ OR “Дата” < ‘01/03/01)

10.Напишите запрос для выборки всех заказчиков, чьи имена начинаются с буквы, попадающей в диапазон от A до М.

11.Напишите запрос для выборки всех книг, в названии которых есть словосочетание «база данных» или «базы данных» или «баз данных».

12.Напишите запрос для вывода всех поставщиков, данные о которых содержат NULL-значения.

13.Напишите запрос для подсчета суммы всех заказов на 31 января

2001 года.

14.Напишите запрос для подсчета числа различных не NULL-значений полей Адрес и Телефон в таблице Поставщики.

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

16.Напишите запрос для выборки заказчиков в алфавитном порядке, чьи имена начинаются с буквы М.

17.Напишите запрос для подсчета общей суммы заказов за каждый день с 01.02.01 по 01.04.01 .

18.Напишите запрос к таблице Заказы для определения средней суммы предоплаты за каждый месяц.

19.Напишите запрос для вывода общей стоимости оплаченных заказов на каждый день в нисходящем порядке.

42

20.Напишите запрос для формирования рейтинга авторов в виде упорядоченного по убыванию списка авторов и количества их книг, проданных за некоторый период.

2.1.3 Использование SELECT для соединения двух и более таблиц

Декартово произведение таблиц

Соединения таблиц это подмножества их декартова произведения. Так как декартово произведение n таблиц это таблица, содержащая все возможные строки r, такие, что r является сцеплением какой-либо строки из первой таблицы, строки из второй таблицы, ... ,строки из n-й таблицы (с помощью SELECT можно получить любое подмножество реляционной таблицы), то осталось лишь выяснить, можно ли с помощью SELECT получить декартово произведение. Для получения декартова произведения нескольких таблиц надо указать во фразе FROM перечень перемножаемых таблиц, а во фразе SELECT – все их столбцы:

SELECT ”Заказы”.*, “Заказчики”.*

FROM “Заказы”, “Заказчики”

В результате SQL выдаст комбинацию каждой строки из таблицы Заказы со всеми строками таблицы Заказчики, т.е. количество строк в запросе равно произведению количества строк обеих таблицы. Большинство этих строк не имеют никакого смысла.

Эквисоединение таблиц

Если из декартова произведения убрать ненужные строки и столбцы, то можно получить вполне осмысленный результат, соответствующий любому из соединений. Например, если к предыдущему запросу добавить предложение WHERE:

SELECT	“Заказы”.*, “Заказчики”.*
FROM	“Заказы”, “Заказчики”
WHERE	“Заказы”.”Код_заказчика” = “Заказчики”.”Код_заказчика”

то получим таблицу эквисоединение двух таблиц, в которой останутся только строки с одинаковыми значениями в столбцах Код_заказчика, но таких столбцов будет два и они будут дублировать друг друга.

Естественное соединение таблиц

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

SELECT	“Код_заказа”, “Код_книги”, “ Дата”, “Оплачен”, “Заказчики”.*
FROM	“Заказы”, “Заказчики”
WHERE	“Заказы”.”Код_заказчика” = “Заказчики”.”Код_заказчика”

Композиция таблиц

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

	43
SELECT	“Код_заказа”, “Код_книги”, “Дата”, “Оплачен”, “Имя”, “Адрес”,
	“Телефон”
FROM	“Заказы”, “Заказчики”
WHERE	“Заказы”.”Код_заказчика” = “Заказчики”.”Код_заказчика”

При формировании соединения создается рабочая таблица, к которой применимы все операции, рассмотренные ранее: отбор нужных строк соединения (WHERE), упорядочение получаемого результата (ORDER BY) и агрегатирование данных (SQL-функции и GROUP BY). Например, следующий запрос позволит произвести выборку всех оплаченных заказов за период времени между двумя датами, упорядоченных по Код_книги:

SELECT	“Код_заказа”, “Код_книги”, “Дата”, “Оплачен”, “Код_заказчика”
FROM	“Заказы”, “Заказчики”
WHERE	(“Заказы”.”Код_заказчика” = “Заказчики”.”Код_заказчика”)
	AND (“Дата” BETWEEN ‘03/01/2001’ AND ‘04/30/2001’)
	AND (“Оплачен” LIKE ‘%да%’)

ORDER BY “Код_книги”

Соединение таблицы со своей копией

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

FROM “Заказчики” AS A, “Заказчики” AS “B”

или просто

FROM “Заказчики” A, “Заказчики” B

то будут сформированы две копии таблицы Заказчики с именами A, B. Тогда, чтобы узнать, есть ли в базе данных два разных заказчика с одинаковыми телефонами, надо ввести запрос:

SELECT	A.*, B.*
FROM	“Заказчики” A, “Заказчики” B
WHERE	A.”Телефон” = B.”Телефон”
	AND A.”Код_заказчика” != B.”Код_заказчика”

2.1.4 Вложенные подзапросы

Вложенный подзапрос это подзапрос, заключенный в круглые скобки и вложенный в предложение WHERE (HAVING) инструкции SELECT или других инструкций, использующих предложение WHERE. Вложенный подзапрос может содержать в своем предложении WHERE (HAVING) другой вложенный подзапрос и т.д. Назначение вложенных подзапросов состоит в том, чтобы при отборе строк таблицы,

44

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

Существуют простые и коррелированные вложенные подзапросы.

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

Коррелированные вложенные подзапросы обрабатываются системой в обратном порядке. Сначала выбирается первая строка рабочей таблицы, сформированной основным запросом, и из нее выбираются значения тех столбцов, которые используются во вложенном подзапросе (вложенных подзапросах). Если эти значения удовлетворяют условиям вложенного подзапроса, то выбранная строка включается в результат. Затем выбирается вторая строка и т.д., пока в результат не будут включены все строки, удовлетворяющие вложенному подзапросу (последовательности вложенных подзапросов).

Подзапросы включаются в предложение WHERE (HAVING) с помощью ключевых слов IN, EXISTS или одного из условий сравнения ( = | <> | < | <= | > | >= ):

подзапросы, не возвращающие ни одного или возвращающие несколько значений, начинаются с IN или операторов сравнения и содержат ключевые слова ANY или ALL;

подзапросы, возвращающие единственное значение, начинаются с оператора сравнения;

подзапросы, представляющие собой тест на существование или присутствие данных, начинаются с EXISTS.

Простые вложенные подзапросы

Простые вложенные подзапросы используются для представления множества значений, исследование которых должно осуществляться в какомлибо предикате IN, например:

SELECT	*
FROM	“Поставщики”
WHERE	“Код_поставщика” IN
	(SELECT DISTINCT “Код_поставщика”
	FROM “Книги”
	WHERE“Автор” LIKE ‘%Дейт%’ )

Система выполняет прежде всего вложенный подзапрос. Этот подзапрос выдает множество уникальных кодов поставщиков, которые поставили книги указанного автора. Затем будет выполнен внешний запрос, который выведет данные об этих поставщиках.

Тот же результат можно получить с помощью соединения:

SELECT	”Поставщики”.*
FROM	“Книги”, “Поставщики”
WHERE	“Книги”.”Код_поставщика” = “Поставщики”.“Код_поставщика”
	AND “Автор” LIKE ‘%Дейт%’

45

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

В подобных запросах можно использовать не только оператор IN, но и операторы сравнения (<>, <=, <, >= или >), однако, если вложенный подзапрос возвращает более одного значения и не используется оператор IN, будет возникать ошибка.

Коррелированные вложенные подзапросы

Рассмотрим запрос:

SELECT	*
FROM	“Заказы”
WHERE	‘Некто’ IN
	(SELECT ”Имя”
	FROM “Заказчики”
	WHERE “Код_заказчика” = “Заказы”.”Код_заказчика”);

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

1. Система проверяет первую строку таблицы Заказы. Предположим, что в этой строке Код_заказчика имеет значение 1. Тогда значение Заказы.Код_заказчика в данный момент также примет значение 1 и система cможет обработать внутренний запрос

(SELECT Имя

FROM Заказчики

WHERE Код_заказчика = 1)

получив в результате некоторое подмножество значений поля Имя. Выборка значений для этого заказчика будет проведена тогда и только тогда, когда значение ‘Некто’ будет принадлежать этому множеству. На этом завершается обработка для заказчика с кодом 1.

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

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

Следующий пример демонстрирует использование одной и той же таблицы во внешнем подзапросе и коррелированном вложенном подзапросе:

SELECT	"Код_книги", "Автор", "Название", "Издательство"
FROM	"Книги" A
WHERE	"Название" IN
	(SELECT DISTINCT "Название"

46

FROM "Книги" B

WHERE A."Издательство" != B."Издательство" )

ORDER BY "Автор"

Вэтом запросе поочередно для каждой строки таблицы Книги выбирается значение поля Издательство и для этого значения выполняется вложенный подзапрос, результатом которого является список уникальных названий книг, изданных другими издательствами. Текущая строка таблицы Книги включается в результат запроса, если значение в поле Название входит в список, сформированный вложенным запросом. Т.о., будут выданы данные о книгах, изданных более чем в одном издательстве, и результирующий список будет отсортирован по полю Автор.

Запросы, использующие EXISTS в предложении WHERE

Вязыке SQL подзапросы, выполняющие проверку на существование, представляются выражением

WHERE [NOT] EXISTS (подзапрос).

Такое выражение считается истинным только тогда, когда подзапрос возвращает непустое множество, т.е. когда существует какая-либо запись в таблице, указанной в предложении FROM подзапроса, которая удовлетворяет условию WHERE подзапроса (практически этот подзапрос всегда будет коррелированным множеством).

Рассмотрим пример запроса на выдачу названия поставщиков, поставляющих книги некоторого издательства, скажем BHV:

SELECT "Название"

FROM "Поставщики" A WHERE EXISTS (

SELECT *

FROM "Книги"

WHERE "Код_поставщика" = A."Код_поставщика" AND "Издательство" = 'BHV')

Система последовательно выбирает строки таблицы Поставщики, выделяет из них значения столбца Название, а затем проверяет, существует ли в таблице Книги хотя бы одна строка со значением ‘BHV’ в столбце Издательство и значением в столбце Код_поставщика, равным значению того же столбца, выбранному из таблицы Поставщики. Если условие выполняется, то полученное значение столбца Название включается в результат.

Подзапросы c ANY и ALL

ANY (SOME) и ALL отличаются от EXISTS тем, что используются совместно с реляционными операторами. В этом отношении они подобны оператору IN, однако, в отличие от IN, они могут использоваться только с подзапросами.

Оператор ANY приводит к тому, что текущая строка внешнего запроса включается в результат, если любое (ANY) из значений, выведенных подзапросом, совпадает со значением в текущей строке внешнего запроса. Следующий запрос

SELECT *

	47
FROM	”Заказчики”
WHERE	“Код_заказчика” = ANY (SELECT “Код_заказчика” FROM “Заказы”)
равнозначен запросу
SELECT	*
FROM	Заказчики
WHERE	Код_заказчика IN (SELECT Код_заказчика FROM Заказы);

Отличие заключается в том, что оператор ANY может использовать другие операторы сравнения, а не только оператор «=» как в случае IN. Например, если в предыдущем запросе в предложении WHERE заменить оператор «=» на оператор «<=», то получим информацию о заказчике с минимальным значением в поле Код_заказчика, поскольку это – единственное значение, для которого условие основного запроса выполняется.

Оператор ALL аналогичен оператору ANY по взаимодействию с внешним запросом, но для того, чтобы текущее значение во внешнем запросе было выбрано, оно должно удовлетворять условию в предикате внешнего запроса для каждого значения, выбранного подзапросом.

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

Любой запрос, который может быть сформулирован с ANY или с ALL, может быть также сформулирован с EXISTS, хотя обратное в общем случае неверно. Строго говоря, вариант с EXISTS не абсолютно идентичен вариантам с ANY или с ALL из-за различия в том, как обрабатываются NULL-значения. Всякий раз, когда допустимый подзапрос возвращает пустое множество, предикат с ALL автоматически верен, а с ANY – автоматически неверен.

Использование WITH

Конструкция WITH позволяет оформить результаты подзапроса в виде временной таблицы, которая создается на время выполнения запроса. Т.о., WITH может использоваться для структурирования сложных запросов с многоуровневой иерархией подзапросов. Синтаксис конструкции WITH достаточно прост:

WITH идентификатор AS (запрос)

Например,

WITH subquery_ as ( SELECT "Название", "Цена"

FROM "Книги"

WHERE"Издательство" = 'BHV') SELECT *

FROM _subquery_

WHERE "Цена" >= ANY(SELECT "Цена" FROM _subquery_ )

Объединение двух или более запросов

48

Предложение UNION позволяет объединить результаты выполнения нескольких запросов к разным таблицам в одном подмножестве данных, реализуя один запрос. Синтаксис UNION имеет вид:

запрос_1 UNION запрос_1

Непременное требование – одинаковые количество и типы полей в объединяемых предложением UNION запросах, хотя второе требование можно обойти, если использовать преобразование типов в запросах. Например:

SELECT ”Имя”, “Адрес”

FROM “Заказчики”

UNION

SELECT “Название”, “Адрес”

FROM “Поставщики”

Следует иметь в виду, что:

избыточные дубликаты всегда исключаются из результата UNION;

предложением с UNION можно объединить любое число таблиц (проекций таблиц);

весь запрос может использовать только одно предложение ORDER BY. Оно применяется ко всему результату и должно находится после последней инструкции SELECT.

Задания для самостоятельной работы

1.Напишите запрос, который бы выводил все заказы с величиной предоплаты выше средней. Выведите код заказа, дату, имя заказчика и величину предоплаты.

2.Напишите запрос, который вычислил бы суммарные остатки количества книг, поставленных каждым поставщиком. Выведите код поставщика, количество видов книг и суммарный остаток.

3.Напишите запрос, который бы вывел все заказы, сделанные за первый квартал 2001 года заказчиками из г. Бреста, и подсчитал количество оплаченных и неоплаченных заказов.

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

5.Напишите запрос для получения списка книг, цены которых превысят 10000 руб. в случае повышения цен на 12%.

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

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

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

49

– с использованием естественного соединения, а другой – с коррелированным подзапросом.

9.Напишите запрос для выборки данных о книгах, которые в данный момент заказаны по крайней мере одним заказчиком, а также тех книг, которые к данному моменту времени ни разу не были заказаны.

10.Напишите запрос, использующий ANY или ALL, который бы находил всех заказчиков, которые не оплатили ни одного заказа.

11.Создайте объединение из двух запросов, которое вывело бы коды и имена всех заказчиков. Те из них, для которых суммы приобретенных книг выше средней, должны, кроме того, иметь комментарий "Высокий Рейтинг", а остальные – "Низкий Рейтинг".

2.2 Порядок выполнения работы

	1. Ознакомиться с теоретическими сведениями к лабораторной работе.
	2. Написать запросы к разработанной в ходе выполнения
	лабораторной работы №1 базе данных, используя простые и
	коррелированные подзапросы. Результаты запросов должны
	выводиться со смысловыми названиями столбцов и сопровождаться
	при необходимости текстовыми комментариями. Задания
	приведены в таблице 2.1.
	Таблица 2.1 – Варианты заданий
№	Название разрабатываемой БД и запросы.
1	Торговля

1.Получить список изделий, которые в данный момент времени отсутствуют на складе.

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

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

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

5.Получить счета, оплаченные по наличному расчету с начала года. Список упорядочить по убыванию суммарной стоимости изделий, фигурирующих в счете.

2	Коммунальные платежи.

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

2.Получить перечень предоставляемых коммунальных услуг с указанием тарифов, среднемесячных объемов потребления.

3.Получить списки клиентов, у которых потребление данного вида коммунальных услуг за последний месяц превышает среднее значение.

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

5.Получить данные о задолженности по каждому виду коммунальных услуг за каждый месяц с начала года.

50

3

Услуги.

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

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

3.Получить список первых 10 клиентов от 20 до 45 лет с максимальным количеством заказов. Для каждого из них вывести также суммарную стоимость заказов.

4.Получить список клиентов, обращавшихся в агентство с начала текущего года, упорядоченный по убыванию количества различных типов выполненных работ.

5.Получить список клиентов, суммарная стоимость заказов которых превышает среднюю стоимость заказа по агентству.

4	Начисление зарплаты.

1.Получить ведомость выплаты зарплаты за последний месяц, в которой для каждого работника указать: фамилию и инициалы, должность, начисленную сумму, сумму удержаний, сумму к выдаче. Данные упорядочить по должности и фамилии сотрудников.

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

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

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

5.Получить в процентном выражении долю заработной платы работников со средним, средним специальным и высшим образованием от общего фонда заработной платы.

5

Поставки.

1.Получить список поставщиков данного типа продукции, упорядоченный в порядке убывания оптовой цены этой продукции.

2.Получить список договоров, заключенных за последние три месяца, с истекшими сроками поставки. Данные вывести в порядке убывания задержки поставки.

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

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

5.Получить рейтинг поставщиков в зависимости от суммарного объема поставок для каждого типа продукции.

6	Билетная касса.

1.Получить информацию на данный момент времени обо всех рейсах, выполняемых в указанный пункт назначения, дата и время прибытия которых в этот пункт находятся

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

2.Для каждого рейса получить среднее число зарегистрированных пассажиров за все время эксплуатации. Данные вывести в порядке убывания значений.

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

4.Для каждого из пунктов назначения, фигурирующих в расписании, получить их долю

вобщем пассажиропотоке. Упорядочить по убыванию значений.

5.По каждой категории билетов получить суммарную стоимость проданных билетов и