Тема работы: Построение запросов. Построение отчетов в QReport.

Лабораторная работа №17

Тема работы: Построение запросов. Построение отчетов в QReport.

Цель работы: Приобретение практических навыков создания запросов, а также формирования отчетов в QReport.

Задание к работе:

1) изучить теоретический материал в лабораторной работе;

2) получить вариант индивидуального задания;

3) разработать интерфейс приложения;

4) создать приложение, формирующее запрос из нескольких таблиц БД, используя компонент Query;

5) разработать отчет на основании выбранных с помощью запроса данных;

6) сдать работу преподавателю.

Содержание отчета:

1) постановка задачи индивидуального задания;

2) программа (текст рабочего модуля) и графическое представление формы и отчета;

3) ответы на контрольные вопросы;

4) выводы о проделанной работе.

 

ТЕОРЕТИЧЕСКИЙ МАТЕРИАЛ

 

Стандарты и реализации языка SQL

 

SQL – это язык, который обеспечивает доступ к информации и позволяет управлять реляционными базами данных. Язык SQL – Structured Query Language – структурированный язык запросов.

Основы языка были заложены Э. Коддом. Язык SQL оперирует данными, представленными в виде логически связанных таблиц. Особенность языка заключается в ориентации на конечный результат обработки данных, а не на процедуру обработки (как в процедурных языках). Для решения задачи надо в терминах языка сформулировать, что требуется получить, а возможности того, как это сделать, реализованы в языке.

Язык SQL используется на разных платформах, позволяет работать с реляционными базами данных различных форматов. К настоящему времени выпущено несколько стандартов языка: SQL 86, SQL 89, SQL 92, SQL 99. Стандарты разрабатываются и принимаются двумя организациями: ANSI (Американским национальным институтом стандартов) и ISO (Международной организацией по стандартизации).

Стандарт SQL 86 определяет минимальный стандартный синтаксис. Был выпущен ANSI и поддержан ISO. Стандарт SQL 89 ввёл набор дополнительных операторов. Стандарт SQL 92 определил три уровня соответствия: основной, средний и полный. В большинстве случаев производители, объявившие о поддержке этого стандарта, реализовывали только основной уровень.

Стандарт SQL 99 ввёл в язык объектные и некоторые процедурные расширения. В этом стандарте определено обязательное ядро и уровни расширений. Ядро включает в себя основной уровень стандарта SQL 92. Уровни расширения не являются обязательными для реализации.

На практике большинство коммерческих СУБД расширяют SQL по своему усмотрению, добавляя различные особенности, которые, по мнению производителей, будут весьма полезны. Как правило, вносимые расширения диктуются текущими потребностями и могут быть учтены в новых стандартах. Таким образом, несмотря на наличие стандартов, реализации языка SQL значительно отличаются друг от друга. Приступая к использованию SQL для работы с конкретной базой данных, надо ознакомиться с описанием языка по документации соответствующей СУБД.

 

Группы операторов SQL

 

Язык SQL определяет набор операторов (команд), типы данных, набор встроенных функций. Так как язык SQL предназначен для манипулирования данными в реляционных базах данных, определения структуры баз данных и для управления правами доступа к данным в многопользовательской среде, то в

него в качестве составных частей входят:

· язык манипулирования данными (Data Manipulation Language, DML);

· язык определения данных (Data Definition Language, DDL);

· язык управления данными (Data Control Language, DCL).

 

Это не отдельные языки, а различные команды одного языка. Деление проведено только лишь с точки зрения функционального назначения команд. Язык манипулирования данными (DML, ЯМД) состоит из четырёх основных команд:

1. SELECT (выбрать) – извлечь данные из одной или нескольких таблиц;

2. INSERT (вставить) – добавить строки в таблицу;

3. UPDATE (обновить) – изменить значения полей в таблице;

4. DELETE (удалить) – удалить строки из таблицы.

5.

Некоторые авторы наиболее широко используемую команду SELECT выделяют в отдельную группу – язык запросов (DQL). Язык запросов составляет единственная команда SELECT со всеми своими многочисленными опциями и предложениями.

 

Язык определения данных (DDL, ЯОД) используется для создания (CREATE), изменения (ALTER), удаления (DROP) структуры базы данных и ее составных частей – таблиц, индексов, представлений (виртуальных таблиц), а также триггеров и хранимых процедур. Таким образом, DDL управляет объектами базы данных. У каждой СУБД свой набор объектов. Основными командами языка определения данных являются:

CREATE DATABASE – создать базу данных;

CREATE TABLE – создать таблицу;

CREATE VIEW – создать виртуальную таблицу;

CREATE INDEX – создать индекс;

CREATE TRIGGER – создать триггер;

CREATE PROCEDURE – создать хранимую процедуру;

ALTER DATABASE – модифицировать базу данных;

ALTER TABLE – модифицировать таблицу;

ALTER VIEW – модифицировать виртуальную таблицу;

ALTER INDEX – модифицировать индекс;

ALTER TRIGGER – модифицировать триггер;

ALTER PROCEDURE – модифицировать сохраненную процедуру;

DROP DATABASE – удалить базу данных;

DROP TABLE – удалить таблицу;

DROP VIEW – удалить виртуальную таблицу;

DROP INDEX – удалить индекс;

DROP TRIGGER – удалить триггер;

DROP PROCEDURE – удалить хранимую процедуру.

 

Язык управления данными (DCL) используется для управления правами доступа к данным и для управления выполнением процедур в многопользовательской среде. Более точно его можно было бы назвать языком управления доступом.

 

Он состоит из двух основных команд:

· GRANT – дать права;

· REVOKE – забрать права.

 

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

Встроенный SQL применяется в прикладных программах, написанных на других языках программирования, позволяет посылать запросы и обрабатывать полученные результаты, в том числе комбинируя навигационный (record-ориентированный) и реляционный (set-ориентированный) подходы.

Переходя от навигационного способа работы с данными к реляционному, следует учитывать, что в языке SQL СУБД Paradox запись (Record) обозначается Row, а поле (Field) – Column.

 

 

Применение языка SQL

 

Реляционные базы данных имеют мощный теоретический фундамент, основанный на математической теории множеств. Основными операциями над отношениями являются: выборка (Restriction), проекция (Projection), соединение (Join), объединение (Union), пересечение, разность, декартово произведение (Cross Join).

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

· в примерах используются таблицы поставляемой с системой Delphi демонстрационной базы данных DBDemos;

· в командах SQL зарезервированные слова записываются прописными буквами;

· для лучшего усвоения материала целесообразно сразу выполнять примеры и анализировать получаемые наборы данных;

· удобным средством для тестирования примеров является SQL Explorer.

 

Операции выборки и проекции являются унарными, поскольку они работают с одним отношением.

 

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

Например, вывести все строки таблицы country:

SELECT * FROM country

 

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

SELECT * FROM orders WHERE PaymentMethod="Credit"

 

 

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

Например, вывести названия фирм, город и страну из таблицы vendors:

SELECT VendorName,City,Country FROM vendors

 

На практике очень часто требуется получить подмножество столбцов и строк таблицы – выполнить комбинацию выборки и проекции. Для этого достаточно перечислить столбцы таблицы и наложить ограничения на строки.

Например, получить фамилии работников, которых зовут Roger:

SELECT firstName,lastName FROM employee

WHERE firstName="Roger"

 

Декартово произведение R×S двух отношений (двух таблиц) определяет новое отношение – результат сцепления каждой записи из отношения R с каждой записью из отношения S.

Пусть таблица R имеет поля а1, а2 и таблица S имеет поля b1, b2.

Оператор SELECT R.a1, R.a2, S.b1, S.b2 FROM R,S сформирует результирующую таблицу, причём если одна из исходных таблиц имеет N записей и K полей, а другая – M записей и L полей, то их декартово произведение будет содержать N×M записей и K+L полей. Исходные таблицы могут содержать поля с одинаковыми именами, тогда имена полей полученного набора данных будут содержать названия таблиц для обеспечения уникальности имён.

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

Если в предложении FROM указано более одной таблицы, то эти таблицы соединяются. По умолчанию результирующая таблица представляет собой перекрёстное соединение (Cross Join) или декартово произведение.

Операция объединения (UNION) позволяет объединить результаты отдельных запросов по нескольким таблицам в единую результирующую таблицу. Таким образом, предложение UNION объединяет вывод двух или более SQL-запросов в единый набор строк и столбцов. При этом результаты запросов должны быть совместимы, т. е. иметь одинаковое количество полей с совпадающими типами данных (быть совместимыми по объединению).

 

Пример 1. Из таблиц employee и country получить список работников и заказчиков, проживающих во Франции:

SELECT first_name,last_name,job_country FROM employee

WHERE job_country = "France"

UNION

SELECT contact_first,contact_last,country FROM customer

WHERE country = "France"

 

Операция пересечения отношений R и S определяет отношение (таблицу), которое содержит записи, присутствующие как R, так и в S. Отношения R и S должны быть совместимы по объединению. Таким образом, пересечением двух таблиц – R и S является таблица, содержащая все строки, присутствующие в обеих исходных таблицах одновременно.

Разность двух отношений R и S состоит из записей, которые имеются в отношении R, но отсутствуют в отношении S. Причем отношения R и S должны быть совместимы по объединению. Таким образом, разностью двух таблиц R и S является таблица, содержащая все строки, которые присутствуют в таблице R, но отсутствуют в таблице S. Наиболее важной командой языка манипулирования данными является команда SELECT.

 

Оператор SELECT

Общая форма команды SELECT

 

Общая форма оператора SELECT приводится в стандартах. В более простых случаях достаточно воспользоваться только некоторыми возможностями оператора SELECT:

SELECT [DISTINCT] список_выбираемых_полей

FROM список_таблиц или представлений

[WHERE условие_отбора_строк]

[GROUP BY спецификация_группировки

[HAVING условие_отбора_групп]]

[UNION другое_выражение_Select]

[ORDER BY спецификация_сортировки];

 

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

Простейшие конструкции языка SQL позволяют:

· назначать поля, которые должны быть выбраны;

· назначать к выборке все поля;

· управлять вертикальным и горизонтальным порядком выбираемых

· данных;

· подставлять собственные заголовки полей в результирующей таблице;

· производить вычисления в списке выбираемых элементов;

· использовать литералы в списке выбираемых элементов;

· ограничивать число возвращаемых строк;

· формировать сложные условия поиска;

· устранять одинаковые строки из результата.

 

 

Поля и предложение FROM

 

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

В предложении FROM перечисляются все объекты (один или несколько), из которых производится выборка данных. Каждая таблица или представление, которые упоминаются в запросе, должны быть перечислены в предложении FROM. В простейшем случае после слова FROM записывается имя таблицы, из которой извлекаются данные. Если требуется извлечение значений всех полей, то вместо списка полей можно указать символ *. Например, чтобы получить сведения из всех полей таблицы country, надо записать:

SELECT * FROM country

 

Для получения данных из определённых полей используется команда, в которой после слова SELECT перечислены только нужные поля:

SELECT Last_Name,First_Name,City,Country,Phone FROM custoly

 

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

SELECT First_Name,Last_Name,Phone,City,Country FROM custoly

 

Для уточнения объекта, которому принадлежит поле, перед именем поля указывается имя объекта. Задание составного имени имя_таблицы.имя_поля является обязательным при использовании нескольких таблиц или представлений, а также при использовании имён полей с пробелами. При задании полей, имена которых содержат пробел, надо использовать кавычки или апострофы.

Например, при выводе данных из таблицы biolife для полей Species Name и Length (cm) используются составные имена:

SELECT Category,Common_Name,biolife."Species Name", biolife."Length (cm)"

FROM biolife

 

SELECT Category,Common_Name,biolife.'Species Name', biolife.'Length (cm)'

FROM biolife

 

SELECT biolife.Category,biolife.Common_Name,

biolife.'Species Name',biolife.'Length (cm)'

FROM biolife

 

 

Литералы

 

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

апострофах.

SELECT LastName,"получает",Salary," в год" FROM employee

или

SELECT LastName,‘получает’,Salary,‘ в год’ FROM employee

 

К сожалению, могут возникнуть проблемы с кириллицей.

 

 

Конкатенация

 

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

Для этого используется операция конкатенации, которая задаётся двумя вертикальными чёрточками (||).

SELECT FirstName||' '||LastName,HireDate FROM employee

 

Этот запрос выводит список сотрудников с указанием даты поступления на работу.

 

 

Предложение WHERE

Операции сравнения

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

 

Пример 2. Получить список сотрудников с именем Brown (Lee):

SELECT LastName, FirstName, Salary FROM employee

WHERE LastName='Brown'

 

SELECT LastName, FirstName, Salary FROM employee

WHERE LastName='Lee '

 

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

 

Пример 3. Получить список сотрудников с зарплатой меньше 27 000:

SELECT LastName, FirstName, Salary FROM employee

WHERE Salary<27000

 

Пример 4. Получить список фирм-заказчиков с указанием города и страны за исключением заказчиков из Канады:

SELECT Company,City,Country FROM customer

WHERE Country <>'Canada'

 

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

SELECT Company,City,State FROM customer

WHERE Country ='US'

 

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

 

 

Операция BETWEEN

 

Предикат BETWEEN задает диапазон значений, для которого выражение принимает значение true. Разрешено также использовать конструкцию NOT BETWEEN.

 

Пример 6. Получить список сотрудников, у которых зарплата лежит в диапазоне от 25 000

до 30 000:

SELECT LastName,FirstName,Salary FROM employee

WHERE Salary BETWEEN 25000 and 30000

 

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

SELECT LastName,FirstName,Salary FROM employee

WHERE Salary>=25000 and Salary<=30000

 

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

 

Пример 7. Получить список сотрудников, фамилии которых начинаются с Nelson и заканчиваются Osborn:

SELECT LastName,FirstName,Salary FROM employee

WHERE LastName BETWEEN "Nelson" AND "Osborne"

 

Рисунок 3 - Результат реализации операции BETWEEN в примере 7

 

Пример 8. Вывести список сотрудников, фамилии которых находятся между Nel и Osb:

SELECT LastName,FirstName,Salary FROM employee

WHERE LastName BETWEEN "Nel" AND "Osb"

 

В таблице базы данных employee значений Nel и Osb нет.

 

Рисунок 4 - Результат реализации операции BETWEEN в примере 8

 

Однако сотрудники с фамилиями, начинающимися с символов, для которых выполняется условие «больше или равно Nel» и «не более Osb», попадут в выборку. Фамилии, начинающиеся с символов O, Os, Osb, попадают в заданный диапазон, а Osborne – нет.

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

 

Пример 9. Найти рыб с длиной меньше 10 и больше 80 дюймов (маленьких и очень больших):

SELECT Category,Common_Name,Length_In FROM biolife

WHERE Length_In NOT BETWEEN 10 AND 80

 

Операция IN

 

Предикат IN проверяет, совпадает ли заданное значение (например, значение столбца или функция от него) с одним из перечисленных в списке. Элементы списка записываются через запятую в круглых скобках. Если проверяемое значение равно какому-либо элементу в списке, то предикат принимает значение true. Разрешено использовать конструкцию NOT IN.

 

Пример 10. Вывести список компаний из городов Santa Maria, San Jose, Downey:

SELECT Company,City FROM customer

WHERE City in ('Santa Maria','San Jose','Downey')

 

Пример 11. Вывести заказы и даты их оплаты, у которых средством оплаты была не Visa и не AmEx:

SELECT OrderNo,SaleDate,PaymentMethod FROM orders

WHERE PaymentMethod not in ('Visa','AmEx')

 

Предикат LIKE

 

Предикат LIKE используется только с символьными данными. Он проверяет, соответствует ли данное символьное значение указанной подстроке с указанной маской. Предусмотрена также конструкция NOT LIKE.

В подстроке можно применять любые разрешённые символы (с учетом верхнего и нижнего регистров), а также специальные символы:

% – замещает любое количество символов (в том числе и 0),

_ – замещает только один символ.

 

Пример 12. Получить список сотрудников, фамилии которых начинаются с буквы F:

SELECT FirstName,LastName FROM employee

WHERE LastName LIKE "F%"

 

Пример 13. Получить список сотрудников, у которых имя заканчивается на «er»:

SELECT FirstName,LastName FROM employee

WHERE FirstName LIKE "%er"

 

Предикат IS NULL

 

В SQL-запросах NULL означает, что значение столбца неизвестно. Условия поиска, в которых значение столбца сравнивается с NULL, всегда принимают значение unknown и, соответственно, приводят к ошибке. Таким образом, в запросах нет смысла применять выражения вида

WHERE имя_поля=NULL

 

Если требуется определить, имеет ли поле значение, используется предикат IS NULL. Условие, содержащее IS NULL, принимает значение true только тогда, когда значение поля или выражения имеет значение NULL (пусто, не определено). Разрешено также использовать конструкцию IS NOT NULL, которая означает не пусто, имеет какое-либо значение. Предикат IS NULL возвращает только значения true или false.

Логические операции

 

Логические операции AND, OR, NOT позволяют сформировать сложные условия отбора записей. Если в одном выражении используется несколько логических операций, то они выполняются с учётом приоритета: сначала выполняется отрицание NOT, затем AND (логическое И), только после этого OR (логическое ИЛИ). Для изменения порядка выполнения операций разрешается использовать скобки.

 

Пример 14. По таблице country (рисунок 5) было выполнено два запроса. Проанализировать результаты.

 

Рисунок 5 - Исходная таблица

 

 

Запрос 1.

SELECT * FROM country

WHERE Area<100000 or Population<3000000 and Continent='South America'

 

Рисунок 6 - Результат запроса 1

 

Запрос 2.

SELECT * FROM country

WHERE (Area<100000 or Population<3000000) and Continent='South America'

 

Рисунок 7 - Результат запроса 2

 

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

 

 

Работа с датами

 

В разных СУБД отличаются встроенные функции для работы с датами и используются разные форматы для представления даты, даты и времени. Причём отличаются как внутреннее, так и внешнее представления. Внешне дата может быть представлена строками различных форматов, например:

”October 27,2008”

”27-OCT-2008”

”10-27-08”

”10/27/08”

”27.10.08”

 

Пример 15. Вывести список сотрудников, принятых на работу после заданной даты. Использовать разные варианты форматов даты:

SELECT FirstName,LastName,HireDate FROM employee

WHERE HireDate>'1.01.94'

 

SELECT FirstName,LastName,HireDate FROM employee

WHERE HireDate>'1/01/90'

 

SELECT FirstName,LastName,HireDate FROM employee

WHERE HireDate>'27-Oct-1992'

 

Таблицы формата Paradox поддерживают только часть из перечисленных выше форматов. В запросах приведены варианты разрешённых форматов даты.

 

Пример 16. Выполнить сравнение результатов двух вариантов запроса, отличающихся только форматом записи даты.

 

Рисунок 8 - Дата задана строкой '12/30/93'

 

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

 

SELECT FirstName,LastName,HireDate FROM employee

WHERE HireDate>'12/30/93'

 

Дата 12/30/93 означает: 12 – месяц, 30 –день, 93 – год.

 

Рисунок 9 - Дата задана строкой '31.12.1992'

 

Привычная форма даты 31.12.1992 означает: 31–день, 12–месяц, 1992–год. Значения дат можно сравнивать друг с другом, вычитать одну из другой.

 

Пример 17. Получить список служащих, проработавших на предприятии к 1/01/2000 более 8 лет:

SELECT FirstName,LastName,HireDate FROM employee

WHERE '1/01/2000'-HireDate > 8*365+2

 

Пример 18. Получить список сотрудников, поступивших на работу до 1.01.89 и после 31.12.93, то есть раньше 1989 г. и позже 1993 г.:

SELECT FirstName,LastName,HireDate FROM employee

WHERE HireDate NOT BETWEEN "1-JAN-1989" AND "31-DEC-1993"

 

Кроме абсолютных дат некоторые реализации языка SQL (например, InterBase) позволяют оперировать относительными значениями: yesterday (вчера), today (сегодня), now (сейчас, включая время), tomorrow (завтра).

Дата может неявно конвертироваться в строку (из строки), если строка, представляющая дату, имеет один из разрешённых форматов и выражение не содержит неоднозначностей в толковании типов столбцов.

 

Устранение дублирования

 

В таблицах реляционных баз данных по определению нет одинаковых строк. Однако при выполнении запросов часто выводится только часть полей и в результирующем наборе появляются строки с одинаковыми значениями. Для устранения дублирования служит модификатор DISTINCT. Этот модификатор указывается один раз в списке выбираемых элементов и действует на весь список.

Рисунок 10 - Результат запроса без дублирования категорий

 

 

Рисунок 11 - Результат запроса с дублированием категорий

 

Пример 22. По таблице parts получить список изделий:

SELECT DISTINCT Description FROM parts

 

Пример 23. По таблице biolife вывести перечень категорий, к которым относятся рыбы длиной более 70 см. Перечень упорядочить по алфавиту:

SELECT DISTINCT Category FROM biolife

WHERE biolife."Length (cm)">70 ORDER BY Category

 

 

Вычисления в запросах

Вычисляемые поля

При выводе данных из таблиц можно использовать значения полей для выполнения вычислений. Для создания вычисляемого поля надо в команде SELECT в списке выводимых значений записать выражение, которое будет вычисляться при выводе результатов запроса. Например, для получения из таблицы employee списка сотрудников с указанием зарплаты и зарплаты, увеличенной на 17%, достаточно выполнить запрос:

SELECT LastName,FirstName,Salary,Salary*1.17 FROM employee

 

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

При выводе результатов столбцу, содержащему вычисленные значения, автоматически даётся имя, построенное по введённому выражению. Язык SQL позволяет явным образом задать имя столбца с помощью фразы AS.

 

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

SELECT Description,OnHand*ListPrice+OnOrder*ListPrice

FROM parts

 

SELECT Description,(OnHand+OnOrder)*ListPrice FROM parts

 

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

 

SELECT Description,(OnHand+OnOrder)*ListPrice AS TotalCost

FROM parts

 

В последнем варианте запроса вычисляемому полю присвоено имя.

 

 

 

Итоговые функции

 

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

· SUM – вычисление суммы значений по заданному полю;

· MAX – определение максимального значения поля;

· MIN – определение минимального значения поля;

· AVG – вычисление арифметического среднего указанного поля (сумма

· значений, делённая на их количество);

· COUNT – определение количества записей в выходном наборе.

 

Итоговые функции оперируют со значениями в указанном поле таблицы и возвращают единственное значение. Функции COUNT, MIN и MAX применимы как к числовым, так и к нечисловым полям, а функции SUM и AVG могут применяться только к числовым полям.

При вычислении результатов любых функций сначала исключаются все пустые значения, а затем требуемая операция применяется к оставшимся конкретным значениям столбца. Особенно важно это учитывать при вычислении среднего значения. Вариант COUNT(*) применения функции COUNT является исключением из правил. В этом случае определяется количество всех строк в результирующей таблице независимо от того, что в них находится.

 

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

SELECT count(*) AS Number,max(Length_In) AS Max_Length,

min(Length_In) AS min_Length,avg(Length_In) AS avg_Length

FROM biolife

 

Итоговые функции используются в списке предложения SELECT и в составе предложения HAVING. Если список в предложении SELECT содержит итоговые функции, а в тексте запроса отсутствует фраза GROUP BY, обеспечивающая объединение данных в группы, то поля могут быть только аргументами итоговых функций.

 

Рисунок 12 - Применение итоговых функций

 

 

Выполнение группировки

 

Предложение GROUP BY предназначено для подведения промежуточных итогов в запросах. Для каждой отдельной группы создаётся единственная итоговая строка. При наличии в команде SELECT фразы GROUP BY каждый элемент списка в предложении SELECT должен иметь единственное значение для всей группы. Предложение SELECT может включать только следующие типы элементов: имена полей, итоговые функции, константы и выражения, включающие комбинации перечисленных выше элементов.

Все имена полей, приведённые в списке предложения SELECT, должны присутствовать и в фразе GROUP BY, за исключением случаев, когда имя столбца используется в итоговой функции. При этом в предложении GROUP BY могут быть поля, отсутствующие в списке предложения SELECT.

 

Пример 26. Вывести сведения о категории, количестве записей, максимальной, минимальной и средней длины рыбы в каждой категории:

SELECT Category, count(*)AS Number,

max(Length_In) AS Max_Length,min(Length_In) AS min_Length,

avg(Length_In) AS avg_Length

FROM biolife GROUP BY Category

 

Если совместно с GROUP BY используется предложение WHERE, то оно обрабатывается первым, а группировка применяется только к тем строкам, которые удовлетворяют условию поиска.

 

Пример 27. Вывести сведения о количестве и средней стоимости заказов для заказчиков с номерами более 6800:

SELECT CustNo, count(*)AS Number, avg(ItemsTotal) FROM orders

WHERE CustNo>6800

GROUP BY CustNo

 

Рисунок 13 - Применение группировки

 

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

 

Предложение HAVING

 

При помощи предложения HAVING отображаются все предварительно сгруппированные посредством GROUP BY блоки данных, удовлетворяющие заданным в HAVING условиям. Это дополнительная возможность выполнить фильтрацию в выходном наборе.

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

 

Пример 28. Вывести сведения о категориях (количестве записей, максимальной, минимальной и средней длине), в которых количество представителей не меньше двух:

SELECT Category, count(Category) AS Number,

max(Length_In) AS Max_Length, min(Length_In) AS min_Length,

avg(Length_In) AS avg_Length

FROM biolife

GROUP BY Category

HAVING count(Category)>=2

 

Рисунок 14 - Результат применения предложения HAVING

Пример выполнения запроса в Delphi:

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

Разместим на форме компоненты Query, DataSource, DBGrid, DBNavigator и кнопку закрытия программы, свяжем между собой компоненты Query, DataSource, DBGrid, DBNavigator (рисунок 15).

 

Рисунок 15 – Начальное размещение компонентов на форме

 

Присвоим компоненту Query1 имени QueryMain, компоненту DataSource1 – DataSourceMain. Для создания запроса откроем. Для компонента QueryMain установим свойство DatabaseName = DBDEMOS.

 

Рисунок 16 – Открытие окна утилиты SQL Explorer для создания SQL-запроса

 

Для выполнения задания нам нужны будут три таблицы:

1) Orders.db – таблица заказов. Основная таблица нашего приложения. Первичным ключем в ней является поле OrderNo. В ней имеются такие поля, как CustNo и EmpNo. CustNo – номер поставщика из таблицы поставщиков. EmpNo – номер заказчика из таблицы заказчиков.

2) Customer.db – таблица поставщиков. Является вспомогательной таблицей. Из нее нам необходимо будет получить только название компании. Ключевое поле – CustNo.

3) Employee.db – таблица заказчиков. Является вспомогательной таблицей. Из нее нам необходимо будет получить фамилию и имя заказчика.

 

В утилите SQL Explorer добавим все 3 таблицы, выбрав из выпадающего списка таблиц (рисунок 17).

 

Рисунок 17 - Добавление таблиц в SQL Explorer

 

Соединим таблицу Employee (заказчики) и таблицу Orders (заказы) по полю EmpNo. Для этого перетащим из таблицы Employee в таблицу Orders поле EmpNo. При этом если будет выбрана данная связь для просмотра, то в таблице Orders и Employee поле EmpNo будет выделено жирным шрифтом.

Также соединим таблицу Customer (поставщики) и таблицу Orders (заказы) по полю CustNo. Для этого перетащим из таблицы Customer в таблицу Orders поле CustNo. При этом если будет выбрана данная связь для просмотра, то в таблице Orders и Customer поле CustNo будет выделено жирным шрифтом.

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

Переместим поля Customer, Firstname, Lastname в начало запроса после поля OrderNo. Для этого выберем вкладку Selection и переместим поочередно каждое поле (рисунок 18). Проверим выполнение запроса, нажав кнопку . Результат выполнения запроса показан на рисунке 19.

Рисунок 18 – Установка порядка выборки столбцов в запросе

 

Рисунок 19 – Результат выполнения запроса

 

Далее необходимо скопировать сформированный SQL-запрос. Для этого необходимо в утилите SQL Builder нажать кнопку (Show and Edit SQL) или в меню выбрать пункт QueryàShow SQL (F7).

 

Рисунок 20 – Окно редактирования SQL-запроса

Закроем окно утилиты SQL Explorer. На вопрос сохранения запроса необходимо ответить утвердительно. Текст запроса автоматически сохранится в свойстве SQL компонента QueryMain.

Другим вариантом добавления SQL-запроса является ручное добавление. Для этого скопируем сформированный SQL-запрос и закроем окно редактирования SQL-запроса и окно утилиты SQL Explorer.

В проекте Delphi выберем компонент QueryMain и откроем свойство SQL. Появится окно (рисунок 21), в которое необходимо вставить сформированный SQL-запрос.

 

Рисунок 21 – добавление SQL-запроса в компонент QueryMain

 

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

 

Установка Quick Report.

Quick Report представляет собой стандартный набор компонентов для создания отчетов. Он поставляется вместе с Delphi, но не устанавливается в палитру компонентов автоматически.

Если пакет Quick Report у вас еще не установлен (на палитре компонентов отсутствует вкладка

QReport), то загрузите Delphi и закройте все открытые проекты (File -> Close All). Выбрерите пункт меню «Component -> Install Packages». Нажмите кнопку «Add» и выберите пакет «dclqrt70.bpl», который по умолчанию устанавливается по адресу:

c:\Program Files\Borland\Delphi7\bin\dclqrt70.bpl

и нажмите кнопку «Открыть».

Далее, нажмите кнопку «ОК» - пакет компонентов Quick Report уста