46180 (Математическая логика. Язык SQL), страница 3

Начальнику отдела продаж нужно внимательно следить за отстающими — теми, чей объем продаж ниже среднего уровня. Для этого ему следует написать такой запрос:

SELECT Name, Gr_Income

FROM Agents AS A1, (SELECT Agent_id,

SUM(Gross_Income)

FROM Contracts

GROUP BY Agent_id) AS T1(Agent_id,

Gr_Income)

WHERE A1.Agent_id = T1.Agent_id, Gr_Income<

AVG (T1.Gr_Income)

В раздел FROM вложен уже знакомый нам (но слегка сокращенный) запрос, занимающийся компоновкой. С помощью ключевого слова АS мы даем временной таблице его результатов и столбцам этой таблицы символьные имена, чтобы сослаться на них в основном запросе. Интересующий нас столбец «Объем контрактов на одного работника» называется теперь Gr_Income. А дальше в разделе WHERE основного запроса мы отбираем тех агентов, у которых это значение ниже среднего.

Оператор CASE

Иногда бывает необходимо прямо в ходе выполнения запроса преобразовывать символьные данные в числовые, и наоборот. В предыдущей заметке мы рассмотрели простейший случай компоновки, когда для вычисления общих параметров достаточно было просуммировать значения, содержавшиеся в группируемых записях. Но не все значения можно просуммировать. Предположим, что мы имеем дело со школьной ведомостью School_Sheet, в которой содержится информация относительно идентификатора ученика (Pupil_id), его имени (Name), пола (Gender) и класса, в котором он учится (Group_id). Если теперь возникнет задача сгруппировать детей по классам и определить, сколько человек учится в том или ином классе, то мы уже знаем, как это делать:

SELECT Group_id, COUNT(*) AS Total

FROM School_Sheet

GROUP BY Group_id

Однако куда бежать и за что хвататься, если нужно подсчитать, сколько в каждом классе мальчиков и девочек? Здесь к нам и придет на помощь оператор CASE:

SELECT Group_id,

SUM (CASE WHEN Gender=`M` THEN 1 ELSE

0)AS Boys,

SUM (CASE WHEN Gender = `F` THEN 1 ELSE

0)AS Girls,

Boys +Girls AS Total

FROM School_Sheet

GROUP BY Group_id

На каждой записи оператор CASE возвращает 0 или 1 в зависимости от содержимого поля Gender — следовательно, на единицу увеличивается счетчик мальчиков или счетчик девочек. Возможна и другая нотация для записи оператора CASE. Допустим, нам необходимо перевести буквенные оценки знаний учащихся в цифровые для нахождения среднего бала. Соответствующий оператор перевода будет записан так:

CASE Mark WHEN A THEN 5

WHEN B THEN 4

WHEN C THEN 3

WHEN D THEN 2

WHEN E THEN 1

Некоторые системы не поддерживают оператор CASE. Обойти эту проблему можно с помощью таблиц соответствия. В нашем примере это будет таблица Convert_Table с полями «NMark» и «LMark», содержащими цифровой и буквенный варианты. Если в исходной таблице с оценками School_Marks значения прописаны в символьной форме, то конверсию можно осуществить так:

SELECT Name, Discipline, NMark

FROM School_Marks AS S1, Convert_Table AS C1

WHERE S1.Mark = C1.LMark

Создание таблиц и манипуляции с данными

Возможности SQL выходят за пределы одного лишь составления запросов. С его помощью можно создавать новые таблицы, добавлять, обновлять и удалять данные. Преимущество перед ручным редактированием таблиц с помощью оболочки СУБД очевидно: редактирование осуществляется автоматически по заданным правилам при минимальном участии оператора — а значит, очень быстро и без ошибок. Очень важная область применения автоматических манипуляций данными — построение промежуточных таблиц. В системах, не в полной мере поддерживающих SQL-92, часто возникают ситуации, когда результат промежуточного запроса необходимо сохранить в новой таблице. Таблица создается с помощью оператора CREATE TABLE, после чего в скобках указываются наименования и типы полей:

CREATE TABLE Table1

(Field1 INTEGER NOT NULL,

Field2 VARCHAR(20) NOT NULL,

…)

Для каждого столбца можно задать дополнительные опции/ограничения, например NOT NULL (это означает, что в данном столбце не может быть «пустых» значений) или UNIQUE (означает, что в столбце не может быть повторяющихся значений). Наконец, при помощи оператора CONSTRAINT можно накладывать сложные ограничения на содержимое таблицы с применением полноценных запросов. Например, представим, что в нашей таблице Agents добавлен еще один столбец — «Work_Start_Date», в котором указано, когда агент поступил на работу. Поэтому можно задать ограничение Contract_Date, которое во избежание ошибок оператора будет контролировать, чтобы дата контракта, заключенного агентом, не была более ранней, чем дата его поступления на работу:

CREATE TABLE Contracts

(Agent_id INTEGER NOT NULL,

Client_id INTEGER NOT NULL,

CDate DATE NOT NULL,

Gross_Income Number NOT NULL,

CONSTRAINT Contract_Date

CHECK(EXISTS

(SELECT Agent_id, Work_Start_Date

FROM Agents AS A1

WHERE A1.Agent_id=

Contracts.Agent_id AND Contracts.CDate>

A1.Work_Start_Date)))

В данном случае комбинация СНЕСК (ЕХISTS (SELECT проверяет, существует ли вообще агент, на идентификатор которого ссылается добавляемая в таблицу запись, и если он существует — является ли дата заключения контракта более поздней, чем дата поступления агента на работу. Если оба условия выполняются, SELECT возвращает непустые результаты запроса, оператор EXISTS, соответственно, принимает значение TRUE, и СНЕСК оказывается удовлетворен. Кроме того, с помощью ключевого слова DEFAULT можно задать значения, которые хранятся в данном столбце по умолчанию. Оператор INSERT INTO позволяет автоматически добавлять в таблицу данные, полученные в результате запроса. К примеру, если создана промежуточная таблица Т1, где должна храниться информация относительно объема продаж каждого агента, то заполняться она будет с помощью знакомого нам запроса, выдающего сгруппированные результаты:

CREATE TABLE T1

(Agent_id INTEGER,

Gr_Income NUMBER);

INSERT INTO T1

SELECT Agent_id, SUM(Gross_Income) FROM

Contracts GROUP BY Agent_id

Существует версия этого оператора, которая позволяет добавлять в таблицу заранее определенные значения, например:

INSERT INTO Table1 VALUES (`John Smith`, 2, 34, 15)

Удаление строк из таблицы осуществляется с помощью оператора DELETE FROM, которому придает гибкость ключевое слово WHERE. Предположим, что мы хотим удалить из таблицы Contacts данные обо всех контрактах, заключенных до 2005 года. Для этого нам понадобится следующее выражение:

DELETE FROM Contracts

WHERE Cdate < `01.01.2005`

Условие поиска удаляемых записей можно сделать настолько сложным, насколько необходимо: с использованием данных из других таблиц и применением полноценных запросов. Чтобы добиться этого, необходимо воспользоваться комбинацией WHERE (EXISTS (SELECT, которая аналогична только что рассмотренной. Если запрос, идущий после ключевого слова SELECT возвращает непустые результаты, EXISTS и WHERE возвращают TRUE, и запись удаляется. Для корректировки данных в таблице применяется оператор UPDATE. Например, следующее выражение увеличивает в полтора раза значение в поле Field1 в тех строках, где это значение больше двух:

UPDATE Table1

SET Field1 = 1.5 * Field1

WHERE Field > 2

Ключевое слово WHERE здесь действует точно так же, как в случае с оператором DELETE.

Целиком таблицу можно уничтожить с помощью оператора DROP TABLE. Когда наша промежуточная таблица T1, которую мы обсуждали выше, будет уже не нужна, ее необходимо удалить с помощью следующей команды:

DROP TABLE T1

Список литературы

Перегудов Ф.И., Тарасенко Ф.П. Введение в системный анализ. Учебное пособие для для ВУЗов. - М.: Высшая школа, 1989. - 367 с.

Дэвид А. Марка, Клемент Ман Гоуэн. Методология структурного анализа и проектирования/ Пер. с англ. - М.: Метатехнология, 1993, 240 с.

Маклаков С. В. Bpwin, Erwin: Case-средства разработки информационных систем. М.: Диалог-МИФИ, 2000, 254с.

Леоненков Ф. В. Самоучитель UML. СПб.: BHV-Петербург, 2001, 304с.

Для подготовки данной работы были использованы материалы с сайта http://referat.ru