Язык структурных запросов sql введение
 Скачать 58.6 Kb.
|
|
Некоррелированные подзапросы. 1. Фраза ALL. Ключевое слово ALL, указываемое перед подзапросом используется для определения того, выполняется ли условие сравнения для каждого возвращаемого подзапросом значения. Если подзапрос не возвращает ни одного значения, то условие поиска считается выполненным. Пример Получить перечень поставщиков, рейтинг которых выше рейтинга любого лондонского поставщика. Select x.номер_поставщика, x.фамилия, x.рейтинг from S x where x.рейтинг > all (select y.рейтинг from S y where y.город='Лондон') Сначала выполняется независимый внутренний подзапрос, его результатом является выборка (20, 20), затем - внешний запрос, приводящий к результату, записанному ниже.
Подготовьте запрос и проверьте полученный результат. 2. Фраза ANY. Ключевое слово ANY, указываемое перед запросом, используется для определения того, выполняется ли сравнение по крайней мере для одного значения, возвращаемого подзапросом. Если подзапрос не возвращает ни одного значения, то условие поиска считается не выполненным. Пример Получить перечень поставщиков, рейтинг которых выше рейтинга хотя бы одного парижского поставщика. Select x.номер_поставщика, x.фамилия, x.рейтинг from S x where x.рейтинг > any (select y.рейтинг from S y where y.город='Париж') Сначала выполняется независимый внутренний подзапрос, его результатом является выборка (10, 30), затем - внешний запрос, приводящий к результату, записанному ниже.
Подготовьте запрос и проверьте полученный результат. 3. Фраза IN. 3.1. Простой подзапрос. Пример Выдать фамилии поставщиков, поставляющих деталь P2. Select фамилия from S where номер_поставщика in (Select номер_поставщика from SP where номер_детали ='P2') Сначала выполняется внутренний подзапрос, его результатом является выборка (S1, S2, S3, S4), затем - внешний запрос, который после подстановки результатов внутреннего подзапроса имеет вид: Select фамилия from S where номер_поставщика in ('S1', 'S2', 'S3', 'S4')
Подготовьте запрос и проверьте полученный результат. 3.2. Подзапрос с несколькими уровнями вложенности. Пример Выдать фамилии поставщиков, поставляющих по крайней мере одну красную деталь. Select фамилия from S where номер_поставщика in (Select номер_поставщика from SP where номер_детали in (select номер_детали from P where цвет='Красный')) Сначала осуществляется самый внутренний подзапрос, дающий выборку (P1, P4, P6). После подстановки его результатов выполняется второй по вложенности подзапрос, дающий выборку (S1, S2, S4). Подстановка результатов второго выполненного подзапроса во внешний запрос приводит к окончательному результату.
Подготовьте запрос и проверьте полученный результат. 3.3. Использование одной и той же таблицы в подзапросе внешнем запросе. Пример Выдать номера поставщиков, поставляющих, по крайней мере, одну деталь, поставляемую поставщиком S2. Select distinct номер_поставщика from SP spx where spx.номер_детали in (Select spy.номер_детали from SP spy where spy.номер_поставщика='S2') Сначала выполняется внутренний подзапрос, дающий выборку (P1, P2). Подстановка его результатов во внешний запрос приводит к окончательному результату.
Подготовьте запрос и проверьте полученный результат. 3.4. Подзапрос с оператором сравнения отличным от IN. Пример Выдать номера поставщиков, находящихся в том же городе, что и поставщик S1. Select номер_поставщика from S where город = (Select город from S where номер_поставщика ='S1') Сначала выполняется внутренний подзапрос, дающий единственное значение "Лондон". Подстановка его результатов во внешний запрос приводит к окончательному результату.
Подготовьте запрос и проверьте полученный результат. Коррелированный подзапросы 3.5. Простой коррелированный подзапрос. Пример Выдать фамилии поставщиков, поставляющих деталь P2. Seleсt фамилия from S where 'P2' in (Select номер_детали from SP where номер_поставщика= S.номер_поставщика) В коррелированном подзапросе внутренний подзапрос не может быть отработан раз и навсегда, прежде чем будет отработан внешний запрос, поскольку этот внутренний подзапрос зависит от переменной, значение которой изменяется по мере того, как система проверяет различные строки таблицы, участвующие во внешнем запросе. Обработка запроса выполняется по следующей схеме: выбирается первая строка из S (номер_поставщика='S1"); выполняется подзапрос: (Select номер_детали from sp where номер_поставщика='S1') результатом подзапроса является выборка (P1, P2, P3, P4, P5, P6); завершается обработка запроса для первой строки из S, при выполнении которого проверяется условие 'P2' in ('P1', 'P2', 'P3', 'P4', 'P5', 'P6') поскольку проверяемое условие - истина, результатом обработки запроса для первой строки из S является фамилия "Смит"; аналогично повторяется обработка для остальных строк таблицы S.
Подготовьте запрос и проверьте полученный результат. 3.6. Коррелированный подзапрос с использованием в коррелированном и внешнем запросе одной и той же таблицы. Пример Выдать номера деталей, поставляемых более чем одним поставщиком. Select distinct spx.номер_детали from SP spx where spx.номер_детали in (Select spy.номер_детали from SP spy where spy.номер_поставщика<>spx.номер_поставщика)
Подготовьте запрос и проверьте полученный результат. 4. Фраза EXISTS. 4.1. Квантор существования EXISTS. В языке SQL предикат с квантором существования представляется выражением вида: EXISTS (select * from...) Данное выражение истинно тогда и только тогда, когда результат вычисления подзапроса, представленного с помощью select * from является непустым множеством, т.е. когда существует какая-либо запись в таблице, указанной во фразе from подзапроса, который удовлетворяет условию where этого подзапроса. Пример Выдать фамилии поставщиков, поставляющих деталь P2. Select фамилия from S where exists (Select * from SP where номер_поставщика = S.номер_поставщика and номер_детали = 'P2') Последовательность обработки запроса: выбирается первая строка из S (номер_поставщика='S1'); поскольку условие номер_поставщика = 'S1' и номер_детали = 'P2' - истина, результат обработки запроса для первой строки - фамилия Смит.
Подготовьте запрос и проверьте полученный результат. 4.2. Запрос, реализующий квантор общности. Квантор общности FORALL в SQL не поддерживается, однако он может быть выражен через квантор существования при помощи тождества FORALL x(p)=NOT(EXISTS x(NOT(p))). Пример Выдать фамилии поставщиков, которые поставляют все детали. Эквивалентная формулировка задачи может звучать так: Выдать фамилии поставщиков таких, что для всех деталей существует запись в таблице SP, указывающая, что данный поставщик поставляет эту деталь. Последнее утверждение, в свою очередь, эквивалентно следующему: выдать фамилии поставщиков таких, что не существует детали такой, что не существует записи в таблице SP, указывающей, что данный поставщик поставляют эту деталь. Select фамилия from S where not exists (Select * from P where not exists (Select * from SP where номер_поставщика=S.номер_поставщика and номер_детали=P.номер_детали))
Подготовьте запрос и проверьте полученный результат. 5. Использование функций в подзапросе. Пример Выдать номера поставщиков со значением поля рейтинг меньшим, чем максимальный рейтинг в таблице S. Select номер_поставщика from S where рейтинг < (Select max(рейтинг) from S)
Подготовьте запрос и проверьте полученный результат. Пример Выдать номер_поставщика, рейтинг и город всех поставщиков, у которых рейтинг больше либо равен среднему для их конкретного города (использование функций в коррелированном подзапросе). Select номер_поставщика, рейтинг, город from S sx where рейтинг >= (Select avg(рейтинг) from S sy where sy.город=sx.город)
Подготовьте запрос и проверьте полученный результат. VII. ОБЬЕДИНЕНИЕ Объединяемые оператором UNION таблицы должны быть совместны по объединению: иметь одинаковое число столбцов; соответствующие столбцы должны иметь одинаковые типы. Любое число предложений select может быть соединено оператором union. Избыточные дубликаты исключаются из результата объединения. Пример Выдать номера деталей, которые имеют вес более 16 фунтов, либо поставляются поставщиком S2. |