Новосибирская государственная академия экономики и управления
ЛАБОРАТОРНЫЙ ПРАКТИКУМ ПО ДИСЦИПЛИНЕ
«БАЗЫДАННЫХ»
Лабораторная работа N 7
«Язык баз данных SQL: команды манипуляции данными »
НОВОСИБИРСК 2000
SQL – это сокращенное название языка структурированных запросов (Structured Query Language). Из названия языка понятно, что его основное назначение заключается в формировании запросов на получение информации из базы данных. Команды на выборку данных составляют основу языка манипулирования данными DML - составной части языка SQL. Однако DML состоит не только из команд выборки данных из базы. Существуют также команды модификации данными, управления данными и другие.
В лабораторной работе рассматриваются базовые средства языка DML. В процессе выполнения лабораторной работы мы будем придерживаться стандарта SQL2.
В связи с тем, что SQL является объемным языком, будем рассматривать только основные команды. Различные специфические средства SQL рассматриваются в последующих лабораторных работах.
Для выполнения лабораторной работы требуется знание основ реляционной модели данных, основ реляционной алгебры и реляционного исчисления, принципов работы с СУБД MS SQL Server.
В результате выполнения лабораторной работы Вы освоите способы манипулирования данными с помощью команд языка SQL, рассмотрите диалект языка, реализованный в СУБД MS SQL Server.
ВВЕДЕНИЕ
SQL содержит широкий спектр возможностей манипуляции данными, как для создания запросов, так и для обновления базы данных. Эти возможности опираются только на логическую структуру базы данных, а не на ее физическую структуру, что согласуется с требованиями реляционной модели.
Первоначально структура синтаксиса SQL была основана (или, по крайней мере, казалась основанной) на реляционном исчислении Кодда. Единственной поддерживаемой операцией реляционной алгебры было объединение.
В SQL2 в дополнение к аналогичному реляционному исчислению синтаксису, разработанному в предыдущем стандарте, напрямую реализованы операции объединение, пересечение, разность и соединение. Операции выбора, проектирования и произведения поддерживались (и продолжают поддерживаться) практически напрямую, в то время как операции деления и присвоения поддерживаются в более громоздкой форме.
Сначала мы опишем язык запросов SQL, а затем его операции ввода и изменения данных. Операции изменения данных будут описаны в последнюю очередь, поскольку их структура в определенной степени опирается на структуру языка запросов.
Простые запросы
Для нас простым запросом будет запрос, который обращается только к одной таблице базы данных. Простые запросы помогут нам проиллюстрировать основную структуру SQL.
Простой запрос. Запрос, который обращается только к одной таблице базы данных.
Запрос: Кто работает штукатурами?
SELECT NAME
FROM WORKER
WHERE SKILL_TYPE = 'Штукатур'
Результат:
NAME
К.Немо
Г.Риковер
Этот запрос иллюстрирует три наиболее часто встречающиеся фразы SQL: SELECT, FROM и WHERE. Хотя в нашем примере мы поместили их на разные строки, они все могут стоять в одной строке. Они также могут помещаться с разными отступами, а слова внутри фраз могут разделяться произвольным числом пробелов. Рассмотрим характеристики каждой фразы.
Select. Фраза SELECT перечисляет столбцы, которые должны войти в результирующую таблицу. Это всегда столбцы некоторой реляционной таблицы. В нашем примере результирующая таблица состоит из одного столбца (NAME), но в общем случае она может содержать несколько столбцов; она также может содержать вычисленные значения или константы. Мы приведем примеры каждого из этих вариантов. Если результирующая таблица должна содержать более одного столбца, то все нужные столбцы перечисляются после команды SELECT через запятую. Например, фраза SELECT WORKER_ID, NAME выдаст в результате таблицу, состоящую из столбцов WORKER_ID и NAME.
Фраза SELECT. Задает столбцы результирующей таблицы.
From. Фраза FROM задает одну или более таблиц, к которым обращается запрос. Все столбцы, перечисленные во фразах SELECT и WHERE, должны существовать в одной из таблиц, перечисленных в команде FROM. В SQL2 эти таблицы могут быть напрямую определены в схеме как базовые таблицы или представления данных, или же они сами могут быть не имеющими имен таблицами, полученными в результате запросов SQL. В последнем случае запрос явно приводится в команде FROM.
Фраза FROM. Задает существующие таблицы, к которым обращается запрос.
Where. Фраза WHERE содержит условие. на основании которого выбираются строки таблицы (таблиц). В нашем примере условие состоит в том, что столбец SKILL_TYPE должен содержать константу 'Штукатур', заключенную в апострофы, как это всегда делается с текстовыми константами в SQL. Фраза WHERE — наиболее изменчивая команда SQL; она может содержать множество разнообразных условий. Большая часть нашего изложения будет посвящена иллюстрации различных конструкций, разрешенных в команде WHERE.
Фраза WHERE. Задает условие, на основании которого выбираются строки из заданных таблиц.
Приведенный выше запрос SQL обрабатывается системой в следующем порядке: FROM, WHERE, SELECT. To есть строки таблицы, указанной в команде FROM, помещаются в рабочую область для обработки. Затем к каждой строке последовательно применяется фраза WHERE. Все строки, не удовлетворяющие условию WHERE, исключаются из рассмотрения. Затем те строки, которые удовлетворяют условию WHERE, обрабатываются командой SELECT. В нашем примере из каждой такой строки выбирается NAME, и все выбранные значения выводятся в качестве результатов запроса.
Запрос: Привести все данные о зданиях офисов.
SELECT *
FROM BUILDING
WHERE TYPE = 'Офис'
Результат:
BUILDING
BLDG IDАДРЕСTYPEQLTY LEVELSTATUS
312 Ул.Вязов, 123 Офис 2 2
210 Березовая ул. 1011 Офис З 1
111 Осиновая ул. 1213 Офис 4 1
Звездочка (*) в команде SELECT означает «строка целиком». Это удобное сокращение, которым мы будем часто пользоваться.
Запрос: Какова недельная зарплата каждого электрика?
SELECT NAME, 'Недельная зарплата = ', 40 * HRLY_RATE
FROM WORKER
WHERE SKILL_TYPE = 'Электрик'
ORDER BY NAME
Результат:
NAME
М.Фарадей Недельная зарплата = 500.00
Х.Колумб Недельная зарплата = 620.00
Этот запрос иллюстрирует употребление и символьных констант (в нашем примере 'Недельная зарплата = '), и вычислений в команде SELECT, Внутри команды SELECT можно производить вычисления, в которых используются числовые столбцы и числовые константы, а также стандартные арифметические операторы (+, -, *, /), сгруппированные по мере необходимости с помощью скобок. Мы также включили новую команду ORDER BY, которая сортирует результат запроса в возрастающем алфавитно-числовом порядке по указанному столбцу. Если вы хотите упорядочивать результаты по убыванию, то к команде нужно добавить DESC. Фраза ORDER BY может сортировать результаты по нескольким столбцам, по одним - в порядке возрастания, по другим - в порядке убывания. Первым указывается столбец первичного ключа сортировки.
Символьная константа. Константа, состоящая из букв, цифр и «специальных» символов.
Запрос: У кого почасовая ставка от 10 до 12 долларов?
SELECT *
FROM WORKER
WHERE HRLY_RATE > = 10 AND HRLY_RATE < - 12
Результат:
WORKER ID NAME HRLY_RATE SKILL_TYPE SUPV_ID
2920 Р.Гаррет 10.00 Кровельщик 2920
1520 Г.Риковер 11.75 Штукатур 1520
Этот запрос иллюстрирует некоторые дополнительные возможности команды WHERE: операторы сравнения и булеву операцию AND (И). Для сравнения столбцов с другими столбцами или с константами могут использоваться шесть операторов сравнения (=, <> (не равно), <, >, <=, >=). Для создания составных условий или для отрицания условия могут использоваться булевы операции AND (И), OR (ИЛИ) и NOT (HE). Для группировки условий, как обычно в языках программирования, могут использоваться скобки.
Операторы сравнения =, <>, <, >, <=, >=.
Булевы операции AND (И), OR (ИЛИ) и NOT (HE).
Для формулировки этого запроса также можно былоиспользоватьоператор BETWEEN (между):
SELECT *
FROM WORKER
WHERE HRLY_RATE BETWEEN 10 AND 12
BETWEEN может использоваться для сравнения некоторой величины с двумя другими величинами, первая из которых меньше второй, если сравниваемая величина может быть равна каждой из данных величин или любому значению между ними.
Запрос: Перечислить штукатуров, кровельщиков и электриков.
SELECT *
FROM WORKER
WHERE SKILL_TYPE IN ('Штукатур', 'Кровельщик', 'Электрик')
Результат:
WORKER_ID NAME HRLY_RATE SKILL_TYPE SUPV_ID
1235 М.Фарадей 12.50 Электрик 1311
1412 К.Немо 13.75 Штукатур 1520
2920 Р.Гаррет 10.00 Кровельщик 2920
1520 Г.Риковер 11.75 Штукатур 1520
1311 Х.Колумб 15.50 Электрик 1311
Этот запрос поясняет использование оператора сравнения IN (В). Условие WHERE считается истинным, если тип специальности строки расположен внутри множества, указанного в скобках, то есть если тип специальности - штукатур, кровельщик или электрик. Мы еще встретимся с оператором IN в подзапросах.
Предположим, что мы не можем точно вспомнить написание специальности: «электрик» или «электронщик» или еще как-то. Символы шаблона, которые замещают неопределенные строки символов, облегчают поиск неточного написания в запросе.
Символы шаблона. Символы, замещающие неопределенные строки символов.
Запрос: Перечислить работников, чей тип специальности начинается с «Элек».
SELECT *
FROM WORKER
WHERE SKILL_TYPE LIKE ('Элек%')
Результат:
WORKER ID NAME HRLY_RATE SKILL_TYPE SUPV_ID
1235 М.Фарадей 12.50 Электрик 1311
1311 Х.Колумб 15.50 Электрик 1311
В SQL есть два символа шаблона: % (процент) и _ (подчеркивание). Подчеркивание замещает ровно один неопределенный символ. Процент замещает произвольное число символов, начиная с нуля. Когда используются символы шаблона, для сравнения символьных переменных с константами требуется оператор LIKE (как). Другие примеры:
NAME LIKE '__Колумб'
NAME LIKE '__K%'
Условие в первом примере истинно, если NAME состоит из двух символов, за которыми следует 'Колумб'. В таблице WORKER все имена начинаются с первого инициала и точки. Таким образом, при помощи этого условия мы. найдем всех работников по фамилии «Колумб». Условие второго примера позволяет найти всех работников, чьи фамилии начинаются на букву «К».
Запрос: Найти все работы, которые начинаются в течение ближайших двух недель.
SELECT *
FROM ASSIGNMENT
WHERE START _DATE BETWEEN CURRENT_DATE AND
CURRENT_DATE + INTERVAL '14' DAY
Результат: (Предположим, что текущая дата CURRENT DATE = 10.10)
WORKER_ID BLDG_ID START_DATE NUM_DAYS
1235 312 10.10 5
1235 515 17.10 22
3231 111 10.10 8
1412 435 15.10 15
3231 312 24.10 20
1311 460 23.10 24
Этот запрос иллюстрирует употребление оператора BETWEEN (между) со значениями типа date (дата) и interval (промежуток). CURRENT_DATE — это функция, всегда возвращающая значение сегодняшней даты. Выражение
CURRENT_DATE + INTERVAL '14' DAY
прибавляет двухнедельный промежуток к текущей дате. Таким образом, ASSIGNMENT выбирается (в предположении, что сегодня 10.10) в том случае, если в ней значение столбца START_DATE лежит между 10.10 и 24.10. Из этого видно, что мы можем прибавлять к полям дат величины типа interval. Более того, мы можем умножать значения промежутков на целые величины. Например, предположим, что мы хотим выяснить, какое число будет через определенное количество недель (обозначенное переменной NUM_WEEKS (ЧИСЛО НЕДЕЛЬ)). Мы можем это сделать так:
CURRENT_DATE + INTERVAL '7' DAY * NUM_WEEKS
2. Многотабличные запросы
Возможность связывать элементы данных вне границ одной таблицы важна для любого языка баз данных. В реляционной алгебре эту функцию выполняет операция соединения. Хотя значительная часть SQL основана непосредственно на реляционном исчислении, SQL связывает данные разных таблиц аналогично тому, как это делает операция соединения реляционной алгебры. Сейчас мы покажем, как это делается. Рассмотрим запрос:
Запрос: Каковы специальности рабочих, назначенных на здание 435?
Данные, необходимые для ответа, находятся в двух таблицах: WORKER и ASSIGNMENT. Для решения в SQL требуется перечислить обе таблицы в команде FROM и задать специальный тип условия WHERE:
SELECT SKILL_TYPE
FROM WORKER, ASSIGNMENT
WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID
AND BLDG_ID = 435
Что здесь происходит? Мы должны рассмотреть два этапа обработки системой данного запроса.
1. Как обычно, сначала обрабатывается фраза FROM. Однако в этом случае, поскольку в команде указаны две таблицы, система создает декартово произведение строк этих таблиц. Это означает, что создается (логически) одна большая таблица, состоящая из столбцов обеих таблиц, в которой каждая строка одной таблицы спарена с каждой строкой другой таблицы. В нашем примере, поскольку в таблице WORKER пять столбцов, а в таблице ASSIGNMENT четыре столбца, в декартовом произведении, созданном командой FROM, будет девять столбцов. Общее число строк декартова произведения равно m * n, где m - число строк таблицы WORKER; а n - число строк таблицы ASSIGNMENT. Поскольку в таблице WORKER 7 строк, а в таблице ASSIGNMENT 19 строк, то декартово произведение будет содержать 7х19 или 133 строки. Если в команде FROM перечислено более двух таблиц, то создается декартово произведение всех таблиц, указанных в команде.
Декартово произведение. Результат объединения каждой строки одной таблицы с каждой строкой другой таблицы.
2. После создания гигантской реляционной таблицы система, как и прежде, применяет команду WHERE. Каждая строка таблицы, созданной командой FROM. проверяется на выполнение условия WHERE. Строки, не удовлетворяющие условию, исключаются из рассмотрения. Затем к оставшимся строкам применяется фраза SELECT.
Фраза WHERE в нашем запросе содержит два условия:
1. WORKER. WORKER_ID = ASSIGNMENT.WORKER_ID
2. BLDG_ID = 435
Первое из этих условий - условие соединения. Обратите внимание, что поскольку обе таблицы WORKER и ASSIGNMENT содержат столбец с именем WORKER_ID, их декартово произведение будет содержать два столбца с таким именем. Для того чтобы различать их, мы помещаем перед именем столбца имя исходной таблицы, отделяя его точкой.
Первое условие означает, что в любой выбранной строке значение столбца WORKER_ID из таблицы WORKER должно совпадать со значением столбца WORKER_ID из таблицы ASSIGNMENT. В действительности мы соединяем две таблицы по WORKER_ID. Все строки, в которых значения этих двух столбцов не равны, исключаются из таблицы произведения. В точности то же самое происходит при выполнении операции естественного соединения реляционной алгебры. (Однако, некоторое отличие от естественного соединения все же есть: язык SQL автоматически не удаляет лишний столбец WORKER_ID). Полное соединение этих двух таблиц с дополнительным условием BLDG_ID = 435 представлено на рис. 1. Применение команды SELECT даст, в конце концов, следующий результат запроса:
SKILL TYPE
Штукатур
Кровельщик
Электрик
Рис. 1. Соединение таблиц WORKER и ASSIGNMENT
Теперь мы покажем, как в SQL присоединить таблицу к ней самой.
Запрос: Перечислить работников, указав имена их менеджеров.
SELECT А.WORKER_NAME, B.WORKER_NAME
FROM WORKER A, WORKER В
WHERE B.WORKER_ID = A.SUPV_ID
Фраза FROM в этом примере создает две «копии» таблицы WORKER, давая им псевдонимы А и В. Псевдоним - это альтернативное имя, данное таблице. Затем копии А и В таблицы WORKER соединяются командой WHERE на основании условия равенства WORKER_ID в В и SUPV_ID в А. Таким образом, каждая строка из А присоединяется к строке В, содержащей информацию о менеджере строки А (рис.2).
Рис. 2. Соединение двух копий таблицы WORKER
Выбирая из каждой строки два имени работника, мы получим требуемый список:
А.NAMEВ.NAME
М.Фарадей Х.Колумб
К.Немо Г.Риковер Р.Гаррет Р.Гаррет
П.Мэйсон П.Мэйсон Г.Риковер Г.Риковер Х.Колумб Х.Колумб Дж.Барристер П.Мэйсон
Псевдоним. Альтернативное имя, данное таблице.
A.WORKER_NAME представляет работника, a B.WORKER_NAME представляет менеджера. Обратите внимание, что некоторые работники - сами себе менеджеры, что следует из выполненного в их строках равенства WORKER_ID - SUPV_ID.
В SQL можно за один раз связать более двух таблиц:
Запрос: Перечислить работников, назначенных на здания офисов.
SELECT WORKER_NAME
FROM WORKER, ASSIGNMENT, BUILDING
WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID AND ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID AND
TYPE = 'Офис'
Результат:
NAME
М.Фарадей
К.Немо
Р.Гаррет
П.Мэйсон
Г.Риковер
Дж.Барристер
Обратите внимание, что если имя столбца (например, WORKER_ID или BLDG_ID) встречается более, чем в одной таблице, то для избежания неопределенности мы должны перед именем столбца указать имя исходной таблицы. Но если имя столбца встречается только в одной таблице, как TYPE в нашем примере, то никакой неопределенности нет, поэтому имя таблицы указывать не нужно.
Команды SQL этого запроса создают одну таблицу из трех реляционных таблиц базы данных. Первые две таблицы соединяются по WORKER_ID, после чего к полученной таблице присоединяется третья таблица по BLDG_ID. Условие
TYPE = 'Офис'
команды WHERE приводит к исключению всех строк, кроме строк, относящихся к офисным зданиям. Это соответствует требованиям запроса.
3. Подзапросы
Подзапрос. Запрос внутри запроса
Подзапрос может помещаться в команду WHERE запроса, в результате чего возможности команды WHERE расширяются. Рассмотрим пример.
Запрос: Каковы специальности рабочих, назначенных на здание 435?
SELECT SKTLL_TYPE
FROM WORKER WHERE WORKER_ID IN
(SELECT WORKER_ID
FROM ASSIGNMENT
WHERE BLDG_ID = 435)
Подзапрос в этом примере
(SELECT WORKER_ID
FROM ASSIGNMENT
WHERE BLDG_ID = 435)
Запрос, в котором содержится подзапрос, называется внешним запросом или главным запросом. Подзапрос приводит к созданию следующего множества ИД (идентификаторов) работников:
WORKER ID
Внешний запрос. Главный запрос, в котором содержатся все подзапросы.
Затем это множество ИД занимает место подзапроса во внешнем запросе. С этого момента выполняется внешний запрос, использующий множество, созданное подзапросом. Внешний запрос обрабатывает каждую строку таблицы WORKER в соответствии с условием WHERE. Если WORKER_ID строки лежит в (IN) множестве, созданном подзапросом, то SKILL_TYPE строки выбирается и выводится в результирующей таблице:
SKILL TYPE
Штукатур
Кровельщик
Электрик
Очень важно, что фраза SELECT подзапроса содержит WORKER_ID и только WORKER_ID. В противном случае фраза WHERE внешнего запроса, означающая, что WORKER_ID лежит в множестве ИД работников, не имела бы смысла.
Обратите внимание, что подзапрос может логично выполняться прежде, чем хотя бы одна строка рассматривается главным запросом. В некотором смысле подзапрос независим от главного запроса. Он может выполняться как полноценный запрос. Мы говорим, что такой подзапрос не коррелирован с главным запросом. Как мы вскоре увидим, подзапросы могут быть коррелированными.
Некоррелированный подзапрос. Подзапрос, значение которого не зависит ни от какого внешнего запроса.
Приведем пример подзапроса внутри подзапроса.
Запрос: Перечислить работников, назначенных на здания офисов.
Снова мы рассматриваем запрос, с помощью которого мы изучали соединение.
SELECT WORKER_MAME
FROM WORKER
WHERE WORKER_ID IN
(SELECT WORKER_ID
FROM ASSIGNMENT
WHERE BLDG_ID IN
(SELECT BLDG_ID
FROM BUILDING
WHERE TYPE = 'Офис'))
Результат:
NAME
М.Фарадей
К.Немо
Р.Гаррет
П.Мэйсон
Г.Риковер
Дж.Барристер
Обратите внимание, что нам нигде не нужно указывать перед именами столбцов имена таблиц, поскольку каждый подзапрос обрабатывает одну и только одну таблицу, так что никаких неопределенностей возникнуть не может.
Выполнение запроса происходит в порядке изнутри наружу. То есть самый внутренний запрос (или «самый нижний») выполняется первым, затем выполняется содержащий его подзапрос, а затем внешний запрос.
Коррелированные подзапросы. Все рассмотренные выше подзапросы были независимы от главных запросов, в которых они использовались. Под независимостью мы подразумеваем, что подзапросы могут выполняться сами по себе в качестве полноценных запросов. Теперь мы перейдем к рассмотрению класса подзапросов, результаты выполнения которых могут зависеть от строки, рассматриваемой главным запросом. Такие подзапросы называются коррелированными подзапросами.
Коррелированный подзапрос. Подзапрос, результат которого зависит от строки, рассматриваемой главным запросом.
Запрос: Перечислить работников, чьи почасовые ставки выше, чем ставки их менеджеров.
SELECT WORKER_NAME
FROM WORKER A
WHERE A.HRLY_RATE >
(SELECT B.HRLY_RATE
FROM WORKER В
WHERE B.WORKER_ID = A.SUPV_ID)
Результат:
NAME
К. Немо
Логические этапы выполнения этого запроса таковы:
1. Система создает две копии таблицы WORKER: копию А и копию В. В соответствии с тем, как мы их определили, А относится к работнику, В - к менеджеру.
2. Затем система рассматривает каждую строку А. Данная строка выбирается, если она удовлетворяет условию WHERE. Это условие означает, что строка будет выбрана, если величина HRLY_RATE в ней больше, чем HRLY_RATE, порожденная подзапросом.
3. Подзапрос выбирает величину HRLY_RATE из строки В, WORKER_ID которой равен SUPV_ID строки А, в данный момент рассматриваемой главным запросом. Это HRLY_RATE менеджера.
Обратите внимание, что поскольку A.HRLY_RATE может сравниваться только с одной величиной, подзапрос должен выдавать только одну величину. Эта величина меняется в зависимости от того, какая строка А рассматривается. Таким образом, подзапрос коррелирует с главным запросом. Мы встретимся с другими примерами коррелированных подзапросов позже, когда будем изучать встроенные функции.
Операторы EXISTS и NOT EXISTS
Предположим, что мы хотим идентифицировать рабочих, которые не назначены работать на некоторое здание. При поверхностном взгляде кажется, что такой запрос легко выполнить при помощи простого отрицания утвердительной версии запроса. Предположим, например, что нас интересует здание с BLDG_ID 435. Рассмотрим запрос:
SELECT WORKER_ID
FROM ASSIGNMENT
WHERE BLDG_ID NOT 435
К сожалению, это неверная формулировка решения. Запрос просто выдаст нам ИД работников, работающих на других зданиях. Очевидно, что некоторые из них могут также быть назначены и на здание 435.
В правильно сформулированном решении используется оператор NOT EXISTS (не существует):
SELECT WORKER_ID
FROM WORKER
WHERE NOT EXISTS
(SELECT *
FROM ASSIGNMENT
WHERE ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID AND
BLDG_ID = 435)
Результат:
WORKER_ID
Операторы EXISTS и NOT EXISTS всегда помещаются перед подзапросом. EXISTS принимает значение «истина», если множество, порожденное подзапросом, не пусто. Если порожденное подзапросом множество пусто, то EXISTS принимает значение «ложь». Оператор NOT EXISTS, естественно, работает в точности наоборот. Он истинен, если результат подзапроса пуст, и ложен в противном случае.
Оператор EXISTS. Принимает значение «истина», если результирующее множество не пусто.
Оператор NOT EXISTS. Принимает значение «истина», если результирующее множество пусто.
В этом примере мы воспользовались оператором NOT EXISTS. Подзапрос выбирает все такие строки таблицы ASSIGNMENT, в которых WORKER_ID имеет то же значение, что и в строке, рассматриваемой главным запросом, а BLDG_ID равен 435. Если это множество пусто, тогда строка работника, рассматриваемая главным запросом, выбирается, поскольку это означает, что данный работник не работает на здании 435.
В приведенном нами решении использовав коррелированный подзапрос. Если мы воспользуемся вместо NOT EXISTS оператором IN, то можем обойтись некоррелированным подзапросом:
SELECT WORKER_ID
FROM WORKER
WHERE WORKER_ID NOT IN
(SELECT WORKER_ID
FROM ASSIGNMENT
WHERE BLDG_ID = 435)
Это решение проще, чем решение с оператором NOT EXISTS. Возникает естественный вопрос, зачем нам вообще нужны EXISTS и NOT EXISTS. Ответ состоит в том, что NOT EXISTS является единственным средством решения запросов, содержащих в условии слово «каждый». Такие запросы решаются в реляционной алгебре с помощью операции деления, а в реляционном исчислении - с помощью квантора всеобщности. Приведем пример запроса, в условии которого есть слово «каждый»:
Запрос: Перечислить работников, назначенных на каждое здание.
Этот вопрос может быть реализован в SQL при помощи двойного отрицания. Мы переформулируем запрос, включив в него двойное отрицание:
Запрос: Перечислить таких работников, для которых не существует здания, на которое они не назначены.
Мы выделили двойное отрицание. Ясно, что этот запрос логически эквивалентен предыдущему.
Теперь мы хотим сформулировать решение на SQL. Для того чтобы упростить понимание окончательного решения, мы сначала дадим решение предварительной задачи: задачи идентификации всех зданий, на которые гипотетический работник, «1234» не назначен.
(I) SELECT BLDG_ID
FROM BUILDING
WHERE NOT EXISTS
(SELECT *
FROM ASSIGNMENT
WHERE ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID AND
ASSIGNMENT.WORKER_ID = 1234)
Мы пометили этот запрос (I), поскольку мы будем ссылаться на него позже. Если не существует здания, удовлетворяющего этому запросу, то тогда работник 1234 назначен на каждое здание и, следовательно, удовлетворяет условиям исходного запроса. Для того чтобы получить решение исходного запроса, мы должны обобщить запрос (I) с конкретного рабочего 1234 на переменную WORKER_ID и превратить этот модифицированный запрос в подзапрос большего запроса. Приведем решение:
(II) SELECT WORKER_ID
FROM WORKER
WHERE NOT EXISTS
(SELECT BLDG_ID
FROM BUILDING
WHERE NOT EXISTS
(SELECT *
FROM ASSIGNMENT
WHERE ASSIGNMENT.BLDG_ID = BUILDING.BLDG_ID AND
ASSIGNMENT.WORKER_ID = WORKER.WORKER_ID)
Результат:
WORKER ID
Обратите внимание, что подзапрос, начинающийся с четвертой строки запроса (II), идентичен запросу (I), в котором «1234» заменено WORKER.WORKER_ID. Запрос (II) можно прочитать следующим образом:
Выбрать WORKER_ID из WORKER, если не существует здания, на которое WORKER_ID не назначен.
Это соответствует условиям исходного запроса.
Мы видим, что оператор NOT EXISTS может использоваться для формулировки тех запросов, при решении которых в реляционной алгебре требовалась операция деления, а в реляционном исчислении - квантор всеобщности. С точки зрения простоты использования оператор NOT EXISTS не дает никаких особых преимуществ, то есть в запросах SQL, в которых дважды используется NOT EXISTS, ничуть не проще разобраться, чем в решениях реляционной алгебры с операцией деления или решениях реляционного исчисления с квантором всеобщности. Для создания языковых конструкций, позволяющих более естественным образом решать подобные запросы, потребуются дополнительные исследования.
Встроенные функции
Рассмотрим вопросы такого типа:
Каковы максимальная и минимальная почасовые ставки? Каково среднее число дней работы служащих на здании 435? Каково общее число дней, отведенных на штукатурные работы на здании 312? Сколько всего разных специальностей?
Для ответа на эти вопросы требуются статистических функции, которые рассматривают множество строк таблицы и выдают одно значение. В SQL есть пять таких функций, называемых встроенными функциями или функциями множества. Это функции SUM (сумма), AVG (среднее), COUNT (количество), МАХ (максимум) и MIN (минимум).
Встроенная функция (функция множества). Статистическая функция, оперирующая множеством строк: SUM (сумма), AVG (среднее), COUNT (количество), МАХ (максимум), MIN (минимум).
Запрос: Каковы максимальная и минимальная почасовые ставки?
SELECT MAX(HRLY_RATE), MIN(HRLY_RATE)
FROM WORKER
Результат: 17.40, 8.20
Функции MAX и MIN оперируют одним столбцом таблицы. Они выбирают максимальное или минимальное значение, соответственно, из этого столбца. Формулировка нашего запроса не содержит команды WHERE. Для большинства запросов это может быть не так, как показывает наш следующий пример.
Запрос: Каково среднее число дней работы служащих на здании 435?
SELECT AVG(NUM_DAYS)
FROM ASSIGNMENT
WHERE BLDG_ID =435
Результат: 12.33
Для того чтобы подсчитать искомое среднее, нужно рассматривать только те строки таблицы ASSIGNMENT, которые относятся к зданию 435. Как всегда в SQL, фраза WHERE сводит рассмотрение только к таким строкам.
Запрос: Каково общее число дней, отведенных на штукатурные работы на здании 312?
SELECT SUM(NUM_DAYS)
FROM ASSIGNMENT, WORKER
WHERE WORKER.WORKER_ID = ASSIGNMENT.WORKER_ID AND
SKILL_TYPE = 'Штукатур' AND
BLDG_ID = 312
Результат: 27
В решении используется соединение таблиц ASSIGNMENT и WORKER. Это необходимо, так как SKILL_TYPE находится в таблице WORKER, a BLDG_ID - в таблице ASSIGNMENT.
Запрос: Сколько всего разных специальностей?
SELECT COUNT (DISTINCT SKILL_TYPE)
FROM WORKER
Результат: 4
Поскольку одна и та же специальность может повторяться в нескольких разных строках, в этом запросе необходимо использовать ключевое слово DISTINCT (различный), чтобы система не сосчитала один и тот же тип специальности более одного раза. Оператор DISTINCT может использоваться с любой из встроенных функций, хотя, разумеется, с функциями МАХ и MIN он избыточен.
DISTINCT. Оператор, исключающий повторяющиеся строки.
Функции SUM и AVG должны использоваться только с числовыми столбцами. Другие функции могут использоваться и с числовыми, и с символьными данными. Все функции, кроме COUNT, можно использовать с вычисляемыми выражениями. Например:
Запрос: Какова средняя недельная зарплата?
SELECT AVG (40 * HRLY_RATE)
FROM WORKER
Результат: 509.14
COUNT может ссылаться на строку целиком, а не на отдельныйстолбец:
Запрос: Сколько зданий имеют уровень качества З?
SELECT COUNT (*)
FROM BUILDING WHERE
QLTY_LEVEL = 3
Результат: 3
Как показывают все эти примеры, если в команде SELECT стоит встроенная функция, то больше в этой команде SELECT ничего стоять не может. Единственное исключение из этого правила связано с фразой GROUP BY, которую мы сейчас рассмотрим.
Фразы GROUP BY и HAVING
В менеджменте часто требуется статистическая информация о каждой группе во множестве групп. Например, рассмотрим следующий запрос:
Запрос: Для каждого менеджера выяснить максимальную почасовую ставку среди его подчиненных.
Для того чтобы решить эту задачу, мы должны разделить работников на группы в соответствии с их менеджерами. Затем мы определим максимальную ставку внутри каждой группы. В SQL это делается таким образом:
SELECT SUPV_ID, MAX(HRLY_RATE)
FROM WORKER
GROUP BY SUPV_ID
Результат:
SUPV_IDMAX(HRLY RATE)
1311 15.50
1520 13.•75
2920 10.00
3231 17.40
При обработке этого запроса система сначала разбивает строки таблицы WORKER на группы по следующему правилу. Строки помещаются в одну группу тогда и только тогда, когда у них совпадает SUPV_ID. Затем фраза SELECT применяется к каждой группе. Поскольку в данной группе только одно значение SUPV_ID, то никакой неопределенности SUPV_ID в группе нет. Для каждой группы, фраза SELECT выводит SUPV_ID, a также вычисляет и выводит значение MAX(HRLY_RATE). Результат представлен выше.
В команде SELECT со встроенными функциями могут встречаться только те столбцы, которые входят во фразу GROUP BY. Обратите внимание, что SUPV_ID может использоваться в команде SELECT, поскольку он входит во фразу GROUP BY.
Фраза GROUP BY. Означает, что строки должны быть разбиты на группы с общими значениями указанного столбца (столбцов).
Фраза GROUP BY позволяет выполнять определенные сложные вычисления. Например, нам может понадобиться выяснить среднее значение этих максимальных ставок. Однако, вычисления со встроенными функциями ограничены в том смысле, что не разрешается использование встроенных функции внутри других встроенных функций. Таким образом, выражение типа
AVG(MAX(HRLY_RATE))
запрещено. Реализация подобного запроса будет состоять из двух этапов. Сначала мы должны поместить максимальные ставки в новую таблицу, а на втором этапе вычислить их среднее.
С командой GROUP BY можно использовать команду WHERE:
Запрос: Для каждого типа зданий выяснить средний уровень качества среди зданий статуса 1.
SELECT TYPE, AVG(QLTY_LEVEL)
FROM BUILDING
WHERE STATUS = 1
GROUP BY TYPE
Результат:
TYPEAVG(QLTY_LEVEL)
Магазин 1
Жилой дом 3
Офис 3.5
Фраза WHERE выполняется перед командой GROUP BY. Таким образом, ни одна группа не может содержать строку, в которой статус отличен от 1. Строки статуса 1 группируются по значению TYPE, а затем к каждой группе применяется фраза SELECT.
Фраза HAVING. Накладывает условия на группы.
Мы также можем применять условия и к группам, созданным фразой GROUP BY. Это делается при помощи фразы HAVING. Предположим, например, что мы решили конкретизировать один из предыдущих запросов:
Запрос: Для каждого менеджера, у которого более одного подчиненного, выяснить максимальную почасовую ставку среди его подчиненных.
Мы можем отразить это условие соответствующей командой HAVING:
SELECT SUPV_ID, MAX(HRLY_RATE)
FROM WORKER GROUP BY SUPV_ID
HAVING COUNT(*) > 1
Результат:
SUPV_ID MAX(HRLY_RATE)
1311 15.50
1520 13.75
3231 17.40
Разница между фразами WHERE и HAVING состоит в том, что WHERE применяется к строкам, в то время как HAVING применяется к группам.
Запрос может содержать и команду WHERE, и команду HAVING. В этом случае первой выполняется фраза WHERE, поскольку она выполняется до разбиения на группы. Например, рассмотрим следующую модификацию приведенного ранее запроса:
Запрос: Для каждого типа зданий выяснить средний уровень качества среди зданий статуса 1. Рассматривать только те типы зданий, максимальный уровень качества которых не превышает 3.
SELECT TYPE, AVG (QLTY_JLEVEL)
FROM BUILDING
WHERE STATUS = 1
GROUP BY TYPE
HAVING MAX(QLTY_LEVEL) <= 3
Результат:
TYPE AVG(QLTY_LEVEL)
Магазин 1
Жилой дом 3
Обратите внимание, что начиная с фразы FROM фразы выполняются по порядку, а затем применяется фраза SELECT. Так, к таблице BUILDING применяется фраза WHERE, и все строки, в которых STATUS отличен от 1, удаляются. Оставшиеся строки группируются по TYPE; все строки с одинаковым значением TYPE оказываются в одной группе. Таким образом, создается нескрлько групп, по одной для каждого значения TYPE. Затем к каждой группе применяется фраза HAVING, и те группы, в которых максимальное значение уровня качества превышает 3, удаляются. Наконец, к оставшимся группам применяется фраза SELECT.
7. Встроенные функции и подзапросы
Встроенные функции могут использоваться только во фразе SELECT или в команде HAVING. Однако фраза SELECT, содержащая встроенную функцию, может быть частью подзапроса. Рассмотрим пример такого подзапроса:
Запрос: У кого из работников почасовая ставка выше среднего?
SELECT WORKER_NAME
FROM WORKER
WHERE HRLY_RATE >
(SELECT AVG(HRLY_RATE)
FROM WORKER)
Результат:
NAME
К. Немо
П.Мэйсон
Х. Колумб
Обратите внимание, что подзапрос не коррелирует с главным запросом. Подзапрос выдает ровно одно значение - среднюю почасовую ставку. Главный запрос выбирает работника только в том случае, если его ставка больше вычисленной средней.
В коррелированных запросах также могут использоваться встроенные функции:
Запрос: У кого из работников почасовая ставка выше средней почасовой ставки среди подчиненных того же менеджера?
В этом случае вместо вычисления одной средней почасовой ставки для всех работников мы должны вычислить среднюю ставку каждой группы работников, подчиняющейся одному и тому же менеджеру. Более того, наше вычисление должно производиться заново для каждого работника, рассматриваемого главным запросом:
SELECT A. WORKER_NAME
FROM WORK