sql запросы. Язык структурных запросов sql введение
 Скачать 63.05 Kb.
|
|
Некоррелированные подзапросы. 1. Фраза ALL. Ключевое слово ALL, указываемое перед подзапросом используется для определения того, выполняется ли условие сравнения для каждого возвращаемого подзапросом значения. Если подзапрос не возвращает ни одного значения, то условие поиска считается выполненным. Пример Получить перечень поставщиков, рейтинг которых выше рейтинга любого лондонского поставщика. Selectx.номер_поставщика, x.фамилия, x.рейтинг from S x where x.рейтинг > all (select y.рейтинг from S y where y.город='Лондон') Сначала выполняется независимый внутренний подзапрос, его результатом является выборка (20, 20), затем - внешний запрос, приводящий к результату, записанному ниже.
Подготовьте запрос и проверьте полученный результат. 2. Фраза ANY. Ключевое слово ANY, указываемое перед запросом, используется для определения того, выполняется ли сравнение по крайней мере для одного значения, возвращаемого подзапросом. Если подзапрос не возвращает ни одного значения, то условие поиска считается не выполненным. Пример Получить перечень поставщиков, рейтинг которых выше рейтинга хотя бы одного парижского поставщика. Selectx.номер_поставщика, 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 wherespx.номер_детали 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.номер_поставщика) В коррелированном подзапросе внутренний подзапрос не может быть отработан раз и навсегда, прежде чем будет отработан внешний запрос, поскольку этот внутренний подзапрос зависит от переменной, значение которой изменяется по мере того, как система проверяет различные строки таблицы, участвующие во внешнем запросе. Обработка запроса выполняется по следующей схеме:
(Select номер_детали from sp where номер_поставщика='S1') результатом подзапроса является выборка (P1, P2, P3, P4, P5, P6);
'P2' in ('P1', 'P2', 'P3', 'P4', 'P5', 'P6')
Подготовьте запрос и проверьте полученный результат. 3.6. Коррелированный подзапрос с использованием в коррелированном и внешнем запросе одной и той же таблицы. Пример Выдать номера деталей, поставляемых более чем одним поставщиком. Select distinct spx.номер_детали from SP spx wherespx.номер_детали 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') Последовательность обработки запроса:
номер_поставщика = '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 wheresy.город=sx.город)
Подготовьте запрос и проверьте полученный результат. |