Правительство Российской Федерации

Государственное автономное образовательное учреждение

высшего профессионального образования

Московский государственный институт электроники и математики

Научно-исследовательского университета "Высшая школа экономики"

Кафедра вычислительных систем и сетей

ИЗУЧЕНИЕ ОСНОВ ЯЗЫКА SQL

Методические указания к лабораторным работам по курсу "Базы данных" Москва 2012 –2– Составитель: доцент, канд. техн. наук И.П. Карпова УДК 681.3 Изучение основ языка SQL: Методические указания к лабораторным работам по курсу "Базы данных" / Московский государственный институт электроники и математики НИУ ВШЭ; Сост.: И.П. Карпова. – М., 2012. – 38 с.

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

Для студентов II-IV курсов технических факультетов, изучающих автоматизированные информационные системы и системы баз данных.

Табл. 4. Ил. 4. Библиогр.: 3 назв.

ISBN –3– Содержание ЦЕЛЬ ВЫПОЛНЕНИЯ РАБОТ

1. ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

1.1. Общие положения

1.2. Терминология

1.3. Операции реляционной алгебры

1.3.1. Основные операции реляционной алгебры

1.3.2. Вспомогательные операции реляционной алгебры

1.4. Введение в язык SQL

1.4.1. Создание таблиц

1.4.2. Команды модификации данных

1.4.3. Извлечение данных из таблиц

1.4.4. Операторы и предикаты

1.4.5. Функции агрегирования

1.4.6. Запрос SELECT на нескольких таблицах

1.4.7. Подзапросы

1.4.8. Самосоединение

1.4.9. Замечания по использованию NULL-значений

1.4.10. Оператор CASE

1.4.11. Работа с представлениями

1.4.12. Удаление объектов базы данных

2. ВЫПОЛНЕНИЕ ЛАБОРАТОРНЫХ РАБОТ

3. ВАРИАНТЫ ЗАДАНИЙ К ЛАБОРАТОРНЫМ РАБОТАМ

Библиографический список

–4–

ЦЕЛЬ ВЫПОЛНЕНИЯ РАБОТ

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

1. ТЕОРЕТИЧЕСКИЕ СВЕДЕНИЯ

1.1. Общие положения SQL (Structured Query Language) – это структурированный язык запросов к реляционным базам данных (БД). Базы данных, основанные на реляционной модели данных (РМД), являются в настоящее время наиболее широко распространёнными вследствие своей простоты и универсальности методов обработки данных. SQL является декларативным языком, основанным на операциях реляционной алгебры.

Существуют три стандарта SQL [1], принятые ISO (International Standards Organization, Международной организацией по стандартизации): SQL- (SQL-1), SQL-92 (SQL-2) и SQL-99/2003 (SQL-3).

Большинство коммерческих систем управления базами данных (СУБД) поддерживают стандарт SQL-92, который принят ISO в качестве международного стандарта. Многие версии (диалекты) имеют свои отличия, которые касаются, в основном, синтаксиса и некоторых дополнительных возможностей.

1.2. Терминология В основе РМД лежит понятие отношения, представляющего собой подмножество декартова произведения доменов [2, 3]. Элементы отношения называют кортежами, элементы кортежа – атрибутами (полями). Длина кортежа (количество атрибутов) определяет арность отношения, количество кортежей – мощность отношения.

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

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

Атрибут (группа атрибутов), значения которого являются уникальными в рамках отношения, идентифицируют кортеж и называются потенциальными ключами. Если ключ состоит из нескольких атрибутов, он называется составным. Ключей может быть несколько; основным является первичный ключ (primary key), его значения обязательны для каждой строки. Таблица может иметь только один первичный ключ (ПК).

Отношение обладает двумя важными свойствами:

1. В отношении не должно быть одинаковых кортежей, т.к. это множество.

2. Порядок кортежей в отношении несущественен.

Связи между отношениями реализуются с помощью внешнего ключа.

Внешний ключ (foreign key) – это атрибут подчинённого (дочернего) отношения, который является копией первичного (primary key) или уникального (unique) ключа родительского отношения.

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

Каждая таблица хранит данные об одном типе объекта (сущности) предметной области (ПрО), причём одна строка таблицы содержит данные об одном экземпляре объекта данного типа. Например, таблица СТУДЕНТЫ может хранить данные обо всех студентах института, а отдельная строка этой таблицы – данные о конкретном студенте.

Данные в отношениях обрабатываются с помощью операций реляционной алгебры.

Операции реляционной алгебры (РА) применимы к реляционным отношениям (таблицам). Результатом выполнения операции реляционной алгебры также является отношение, построенное на основе одного или более исходных отношений. Существует пять основных операций РА и три вспомогательных, которые могут быть выражены через основные.

1.3.1. Основные операции реляционной алгебры 1. Проекция (project).

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

Пример 1. Пусть имеется отношение R(A,B,C) (рис.1,а).

Тогда проекция A,C(R) будет такой, как показано на рис.1,б.

2. Селекция (select).

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

Пример 2. Для отношения R(A,B,C) (рис. 2,а) селекция C=d(R) (при условии "значение атрибута C равно d") будет такой (рис. 2,б):

A B C A B C

3. Декартово произведение (сartesian product).

Это бинарная операция над разносхемными отношениями, соответствующая определению декартова произведения для РМД [3].

Пример 3. Пусть имеются отношение R(A,B) и отношение S(C,D,E) (рис. 3,а).

Тогда декартово произведение RS будет таким (рис. 3,б):

A B C D E A B C D E

Рис.3. Пример декартова произведения отношений 4. Объединение (union).

Объединением двух односхемных отношений R и S называется отношение T = R U S, которое включает в себя все кортежи обоих отношений без повторов.

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

5. Разность (except).

Разностью односхемных отношений R и S называется множество кортежей R, не входящих в S.

Пример 4. Пусть имеются отношение R(A,B,C) и отношение S(A,B,C) (рис. 4,а).

Тогда разность R–S будет такой (рис. 4,б):

A B C A B C A B C

1.3.2. Вспомогательные операции реляционной алгебры 6. Пересечение (intersect).

Пересечение двух односхемных отношений R и S есть подмножество кортежей, принадлежащих обоим отношениям. Это можно выразить через разность:

7. Соединение (join).

Эта операция определяет подмножество декартова произведения двух разносхемных отношений. Кортеж декартова произведения входит в результирующее отношение, если для атрибутов разных исходных отношений выполняется некоторое условие F. Соединение может быть выражено так:

Если условием является равенство значений двух атрибутов исходных отношений, такая операция называется эквисоединением. Естественным называется эквисоединение по одинаковым атрибутам исходных отношений.

Пример 5. Пусть имеются отношения R(A,B,C) и S(A,D,E) (рис. 5,а). Тогда естественное соединение R S будет таким, как показано на рис. 5,б.

A B C A D E A B C D E

Рис.5. Пример естественного соединения отношений 8. Деление (division).

Пусть отношение R содержит атрибуты {r1,r2,...,rk, rk+1,...,rn}, а отношение S – атрибуты {rk+1,...,rn}. Тогда результирующее отношение содержит атрибуты {r1,r2,...,rk}. Кортеж отношения R включается в результирующее отношение, если его декартово произведение с отношением S входит в R. Деление может быть выражено так:

R / S = r1,…,rk (R) – r1,…,rk ((r1,…,rk (R) S) – R).

Пример 6. Пусть имеются отношения R(A,B,C) и S(A,B) (рис. 6,а). Тогда частное R/S будет таким как показано на рис. 6,б.

A B C D C D A B

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

создавать базу данных и таблицы с полным описанием их структуры;

выполнять основные операции манипулирования данными (добавление, изменение, удаление данных);

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

Создание базы данных обычно выполняется автоматически при установке самой СУБД, поэтому в данном пособии не рассматривается.

Для реализации этих функций SQL включает три группы средств:

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

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

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

По стандарту ANSI подмножество команд DCL является частью DDL.

В командах SQL не различаются прописные и строчные буквы (за исключением строчных литералов). Символы и строки символов заключаются в одинарные кавычки, например, 'N', 'учебник'. Однострочные комментарии начинаются с двух минусов (--), многострочные заключаются в символы /* и */.

Каждая команда заканчивается символом ';'. Значения параметров команд, принятые по умолчанию, выделены подчеркиванием, например, ALL.

Примечание. Примем следующие обозначения для описания синтаксиса:

{ } – содержимое скобок рассматривается как единое целое для остальных символов;

| – заменяет слово ИЛИ;

[ ] – содержимое этих скобок является необязательным;

< > – содержимое этих скобок заменяется соответствующими ключевыми словами, литералами, идентификаторами или выражениями (в зависимости от контекста);

… – всё, что предшествует этим символам, может повторяться произвольное число раз;

.,.. – всё, что предшествует этим символам, может повторяться произвольное число раз, каждое вхождение отделяется запятой.

В соответствии со стандартов ISO идентификатор определяется как последовательность символов длиной не более 128, начинающаяся с буквы латинского алфавита и содержащая буквы латинского алфавита, цифры и знак подчеркивания (_). В большинстве СУБД накладываются более жёсткие ограничения на длину идентификатора.

Синтаксис команд и примеры, приведённые в данном пособии, соответствуют синтаксису СУБД Oracle 9i и выше.

Создание нового пустого отношения (таблицы) выполняется с помощью команды DDL CREATE TABLE. Упрощённый синтаксис этой команды:

CREATE TABLE

Расшифровка элементов описания приведена в табл. 1.

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

Тип данных поля. Можно использовать один из следующих типов:

– NUMERIC[(длина [, точность])], NUMBER[(длина [, точность])] – – CHAR[(длина)], VARCHAR(длина) – символьные строки фиксированной и переменной длины;

Размер поля в символах (для текста и чисел).

– PRIMARY KEY – первичный ключ (обязательный, уникальный и – UNIQUE – уникальность значения поля в пределах столбца таблицы;

– CHECK () – условие, которому должно удовлетворять

– REFERENCES [()] – внешний ключ.

– CHECK () – условие, которому должны удовлетворять – UNIQUE () – уникальное значение комбинации полей в пределах таблицы.

Если размер поля не указан, то принимается значение, принятое в данной СУБД по умолчанию для указанного типа. Для всех СУБД точность для числовых типов по умолчанию равна 0, размер поля типа CHAR по умолчанию равен 1, а для типа VARCHAR размер указывать обязательно.

Для типа DATE поддерживается арифметика дат, например:

( – ) – количество дней, прошедших между двумя датами;

( + 1/24) – через час (для типа дата-время).

Получить текущую дату можно с помощью специальной функции, имя которой зависит от СУБД: sysdate – для Oracle; now() – для Access и MySQL; getdate() – для MS SQL Server и т.д.

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

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

Для ограничений целостности можно задавать имена:

CONSTRAINT

Примеры создания таблиц:

1. Таблица "Отделы" с полями "Номер отдела" (ПК), "Название отдела":

CREATE TABLE depart 2. Таблица "Сотрудники" с полями "Номер отдела" (внешний ключ), "Табельный номер сотрудника" (ПК), "ФИО сотрудника", "Должность", "Оклад", "Дата рождения", "Телефон", "Дата поступления на работу":

CREATE TABLE emp (depno NUMERIC(2) CONSTRAINT ref_dep REFERENCES depart, CONSTRAINT check_salary CHECK (salary > 4600), Примечание: функция sysdate возвращает дату и время, поэтому следует с помощью функции trunc (сокращение от truncate) устанавливать время в 0 часов, 0 минут, 0 секунд.

3. Таблица "Дети сотрудников" с полями "Табельный номер родителя" (внешний ключ), "Имя ребенка", "Пол", "Дата рождения":

CREATE TABLE children ( tabno CHAR(5) CONSTRAINT ref_emp REFERENCES emp(tabno), CONSTRAINT pk_child PRIMARY KEY (tabno, name), CONSTRAINT check_sex CHECK (sex IN ('м', 'ж')));

Обратите внимание:

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

если внешний ключ ссылается на первичный ключ (ПК) другого отношения, имена полей ПК можно не указывать (см. создание таблицы emp);

типы полей внешнего ключа должны совпадать с типами полей первичного (или уникального) ключа, на который он ссылается;

если внешний ключ составной, список полей входящий в ключ, указывается после перечисления всех полей таблицы с ключевым словом FOREIGN KEY:

create table exam -- "Расписание зачетов", основная таблица create table tab -- "Зачеты", подчинённая таблица FOREIGN KEY (tclass, tdate) REFERENCES exam(eclass, edate));

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

INSERT – добавление записи в таблицу. Синтаксис команды:

INSERT INTO [(.,..)] Под подразумевается команда SELECT (см. ниже), результаты работы которой добавляются в указанную таблицу.

В предложении VALUES указываются выражения, порождающие значения атрибутов новой записи таблицы. Выражение может включать вызовы функций, определенных в данной СУБД, константы, знак операций конкатенации строк (||) или знаки арифметических операций: –, +, *, /. Типы значений выражений должны соответствовать типам полей таблицы. Строки и даты должны заключаться в одинарные кавычки. Формат даты должен соответствовать тому, который установлен в СУБД по умолчанию.

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

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

Если в списке полей отсутствует какое-либо поле таблицы, то ему будет присвоено значение NULL или значение по умолчанию (DEFAULT), если оно определено в командах CREATE TABLE или ALTER TABLE.

Пример: Добавить в таблицу "Сотрудники" новую запись:

INSERT INTO emp (depno, tabno, name, post, salary, born, phone) VALUES(3, '00112', 'Попов В.Г.', 'экономист', 45400, '1979-12-23', '115-34-11');

1 строка создана.

В данном случае дата рождения вводится как строка '1979-12-23' в соответствии с форматом даты по умолчанию. А в качестве даты поступления сотрудника на работу будет установлена текущая дата, т.к. для поля edate определено значение по умолчанию DEFAULT и в команде INSERT значение не вводится.

Примечание: посмотреть формат даты по умолчанию в СУБД Oracle можно так:

select sysdate from dual;

Изменить формат даты в Oracle можно следующей командой:

alter session set nls_date_format = 'yyyy-mm-dd';

'yyyy' означает год (4 цифры), 'mm' – месяц, 'dd' – день; разделители могут быть любыми.

UPDATE – обновление данных в таблице. Синтаксис:

UPDATE

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

Пример: Изменить должность и зарплату сотрудника Попова В.Г., табельный номер '00112':

UPDATE emp SET post = 'ст. экономист', salary = salary*1. WHERE tabno = '00112';

1 строка обновлена.

DELETE – удаление записей из таблицы. Синтаксис этой команды:

DELETE FROM

Эта команда удаляет из указанной таблицы те записи, которые удовлетворяют заданному условию отбора (WHERE ).

Внимание! Если не указывать условие выбора записей, то все записи таблицы будут удалены без предупреждения и без запроса на подтверждение!

Пример: Удалить запись о сотруднике Попове В.Г., табельный номер '00112':

DELETE FROM emp WHERE tabno = '00112';

1 строка удалена.

Извлечение данных из таблиц БД выполняется с помощью команды SELECT (селекция). Эта команда не изменяет данные в БД.

Результатом выполнения команды SELECT является временная таблица, которая помещается в курсор (специальную область памяти СУБД) и обычно сразу выводится на экран. Упрощённый синтаксис этой команды:

SELECT [ ALL | DISTINCT ] { * | } FROM { [] }.,..

[ WHERE ] [ GROUP BY { | }.,.. ] [ HAVING ] [ ORDER BY { | [ ASC | DESC ] }.,..] [ UNION [ALL] SELECT …];

Расшифровка элементов описания приведена в табл. 2. Порядок конструкций в команде SELECT не может быть изменён.

Список элементов, разделённых запятыми:

Выражение может включать имена полей, знаки операций, вызовы функций и константы. Алиас – это название столбца результата.

Имя или синоним имени таблицы или представления.

Временный синоним имени таблицы, определённый только внутри Условие, которое может быть истинным или ложным для каждой записи из таблицы (таблиц), определённых предложением FROM.

Имя поля (столбца) таблицы.

Число без десятичной точки. Номер элемента в.

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

DISTINCT – предикат удаления из результирующей таблицы повторов строк.

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

Рассмотрим основные предложения (фразы) команды SELECT:

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

(sal*0.87+bonus) AS salary Если надо вывести все поля из тех таблиц, к которым обращается данный запрос, можно указать символ *. В этом случае сначала будут выведены поля таблицы, стоящей первой в предложении FROM, затем – второй и т.д. Поля, относящиеся к одной таблице, будут выводиться в том порядке, в каком они были указаны при создании таблицы.

FROM – в этой части указывается имя таблицы (имена таблиц), из которой будут извлекаться данные.

WHERE – эта часть содержит условия выбора отдельных записей.

GROUP BY – объединяет в одну группу все записи с одинаковым значением указанного поля (комбинации полей). Каждой такой группе в результирующей таблице соответствует одна запись.

HAVING – позволяет указать условия выбора для групп записей.

ORDER BY – упорядочивает результирующие строки по значению одного или нескольких полей: ASC – по возрастанию, DESC – по убыванию.

Порядок выполнения операции SELECT такой:

1. Выбор из указанной во фразе FROM таблицы тех записей, которые удовлетворяют условию отбора (WHERE).

2. Группировка полученных записей (GROUP BY).

3. Выбор тех групп, которые удовлетворяют условию отбора (HAVING).

4. Сортировка записей в указанном порядке (ORDER BY).

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

Если во фразе FROM указаны две и более таблицы, то эта последовательность действий выполняется для декартова произведения указанных таблиц.

Отношения для примеров приведены в таблицах 3-5.

Таблица 4. Отношение "Отделы" (Depart) Таблица 5.Отношение "Дети"(Children) Примеры:

1. Вывести все записи (строки) из таблицы "Отделы":

SELECT * FROM depart;

DEPNO NAME

2. Вывести список сотрудников с указанием должности и зарплаты за вычетом подоходного налога, упорядочить по отделам и фамилиям:

SELECT depno, name, post, salary*0.87 AS sal ORDER BY depno, name;

DEPNO NAME POST SAL

3. Вывести список должностей с окладом в порядке убывания оклада:

SELECT DISTINCT post, salary ORDER BY salary DESC;

POST SALARY

Расширение возможностей команд языка SQL достигается за счёт применения различных операторов, предикатов и функций.

Операторы:

символьные: || – конкатенация строк;

арифметические: +, –, *, /;

сравнения: =, >, =, 40000 AND (depno=2 OR depno=3) ORDER BY name;

DEPNO NAME SALARY

5. Составить список сотрудников первого отдела с указанием должности:

SELECT post || ' ' || name AS ename WHERE depno= ORDER BY 1 DESC;

экономист Тамм Л.В.

экономист Дурова А.В.

начальник отдела Рюмин В.П.

вед. программист Серова Т.В.

Обратите внимание на добавление пробела между полями: || ' ' ||.

Предикаты, используемые в командах:

IN – предикат вхождения в список:

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

BETWEEN – предикат нахождения в диапазоне:

BETWEEN AND

– определяет, входит ли значение в указанные границы.

Если значение выражения меньше, чем, или больше, чем, предикат возвращает "ложь".

LIKE – предикат подобия:

– используется для сравнения строк, применяется только к полям типа CHAR, VARCHAR. Возможно использование шаблонов: '_' – один любой символ и '%' – произвольное количество символов (в т.ч., ни одного);

IS NULL – предикат неопределённого значения:

IS NULL

– определяет, установлено ли значение поля; возвращает истину, если не установлено. Другие предикаты и операторы сравнения возвращают неопределённый результат (NULL), если хотя бы один из операндов имеет значение NULL. Значение NULL интерпретируется как "ложь".

Все эти предикаты могут комбинироваться с операцией "не": NOT IN,

NOT LIKE, NOT BETWEEN, IS NOT NULL.

Примеры:

6. Вывести список программистов и ведущих программистов:

SELECT depno, name, post WHERE post like ('%программист%');

DEPNO NAME POST

7. Увеличить на 10% оклады начальникам отделов и программистам:

WHERE post LIKE 'нач%отдел%' OR post LIKE '%програм%';

4 строки обновлено.

8. Вывести список сотрудников старше 40 лет из 1-го и 3-го отделов:

SELECT depno, name, trunc(months_between(sysdate, born) / 12) AS age trunc(months_between(sysdate, born)/12) > 40;

DEPNO NAME AGE

Примечание: функция months_between() возвращает количество месяцев, прошедших между двумя датами, функция trunc() усекает полученное число до целого.

9. Список сотрудников, не имеющих телефонов:

SELECT tabno, name, post

TABNO NAME POST

034 Перова К.В. делопроизводитель 10.Список сотрудников, родившихся в 80-е годы ХХ века:

SELECT tabno, name, born, post WHERE born BETWEEN '1980-01-01' AND '1989-12-31';

TABNO NAME BORN POST

Для подсчёта различных агрегированных значений (по группе записей) стандарт SQL включает т.н. функции агрегирования:

COUNT(*) – определяет количество строк (записей) в результате.

MAX(), MIN() – определяет максимальное (минимальное) значение указанного поля в результирующем множестве.

SUM() – определяет арифметическую сумму значений указанного числового поля в результирующем множестве записей.

AVG() – определяет среднее арифметическое значений указанного числового поля в результирующем множестве записей.

Правила уточнения использования агрегирующих функций:

COUNT () – подсчёт количества ненулевых значений поля;

COUNT (distinct ) – подсчёт количества разных значений поля;

SUM (distinct ) – суммирование разных значений поля;

AVG (distinct ) – среднее арифметическое разных значений поля.

Примеры:

11.Посчитать количество сотрудников предприятия:

SELECT count(*), ' человек(а)' 12.Определить минимальную и максимальную зарплату сотрудников:

SELECT min(salary) AS minsal, max(salary) AS maxsal

MINSAL MAXSAL

13.Определить среднюю зарплату сотрудников 3-го отдела:

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

Примеры:

14.Посчитать количество сотрудников по отделам:

SELECT depno, count(*), ' сотрудник(а)' 15.Сумма зарплаты по отделам:

SELECT depno, sum(salary) AS sumsal

DEPNO SUMSAL

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

1.4.6. Запрос SELECT на нескольких таблицах Запрос SELECT на нескольких таблицах основан на декартовом произведении исходных таблиц. Если указать условие соответствия значений полей разных таблиц, то получится соединение таблиц. Для полей с одинаковыми названиями нужно указывать имя таблицы (или алиас) перед именем поля, разделяя их точкой (т.н. квалифицированная ссылка).

Примеры:

16.Запрос по двум таблицам. Вывести список сотрудников с детьми:

SELECT e.name, c.name AS child, c.born FROM emp e, children c /* e, c – алиасы таблиц*/ WHERE e.tabno = c.tabno /* условие соединения */ ORDER BY e.name, c.born;

NAME CHILD BORN

17.Посчитать количество сотрудников по отделам:

SELECT d.name, count(*), ' сотрудник(а)' WHERE e.depno=d.depno GROUP BY d.depno, d.name;

Данные об отделе 4, в котором нет сотрудников, выведены не будут, т.к.

для записи о 4-м отделе не выполняется условие соединения (e.depno=d.depno).

Подзапросом называется запрос SELECT, который находится внутри другой команды SQL. Подзапросы можно разделить на следующие группы в зависимости от возвращаемых результатов:

Скалярные – это подзапросы, возвращающие единственное значение.

Векторные – подзапросы, возвращающие от 0 до нескольких однотипных элементов (список элементов).

Табличные – это подзапросы, возвращающие таблицу.

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

Сначала рассмотрим использование подзапросов в команде SELECT.

Подзапросы могут располагаться в разных частях этой команды:

– в части FROM – табличные некоррелированные;

– в части WHERE – любые;

– в части HAVING – любые;

– в части SELECT – скалярные.

Подзапрос всегда стоит справа от оператора сравнения или предиката.

Следующие операторы используются для модификации операторов сравнения:

ALL – оператор, эквивалентный понятию "все". Например:

> ALL (< ALL) – больше (меньше) каждого значения элементов результирующего множества.

ANY (SOME) – оператор, эквивалентный понятию "любой".

= ANY – равно одному из значений элементов результирующего множества (эквивалентно использованию предиката IN).

> ANY (< ANY) – больше (меньше) любого значения элементов результирующего множества.

EXISTS – оператор, эквивалентный понятию "существует". Может использоваться с логическим оператором NOT.

Если список, модифицированный оператором ALL, содержит NULLзначение, то результирующий запрос будет пуст, т.к. нельзя сравнить никакое значение с NULL-значением.

Выражение ANY(…) не эквивалентно NOT IN: оно выполняется всегда, кроме случаев сравнения со списком NULL-значений.

Примеры использования подзапросов в части WHERE:

18.Выдать список сотрудников, имеющих детей:

а) с помощью операции соединения таблиц:

SELECT e.* FROM emp e, children c WHERE e.tabno=c.tabno;

б) с помощью некоррелированного векторного подзапроса:

SELECT * WHERE tabno IN (SELECT tabno FROM children);

в) с помощью коррелированного табличного подзапроса:

SELECT * WHERE EXISTS (SELECT * FROM children c WHERE e.tabno=c.tabno);

Оператор EXISTS возвращает "истину", если подзапрос выбирает хотя бы одну строку, и "ложь", если результат подзапроса пуст.

TABNO DEPNO NAME POST SALARY BORN PHONE

909 1 Серова Т.В. вед. программист 48500.0 20.10.1981 115-91- 19.Выдать список сотрудников, оклад которых выше среднего на предприятии (некоррелированный скалярный подзапрос):

SELECT depno, name, salary WHERE salary > ANY(SELECT avg(salary) FROM emp);

DEPNO NAME POST SALARY

Примеры использования подзапросов в части FROM:

20.Выдать список сотрудников, имеющих оклады выше среднего по отделу:

а) с помощью подзапроса в части WHERE:

SELECT depno, name, post, salary WHERE salary > (SELECT avg(salary) FROM emp m б) с помощью подзапроса в части FROM:

SELECT e.depno, name, post, salary (SELECT depno, avg(salary) avgsal FROM emp GROUP BY depno) m WHERE e.depno=m.depno AND e.salary>avgsal;

Второй вариант будет работать быстрее, т.к. подзапрос в части FROM вычисляется один раз, а коррелированный подзапрос в части WHERE вычисляется для каждой строки запроса верхнего уровня (в нашем случае – для каждого сотрудника).

DEPNO NAME POST SALARY

Пример использования подзапросов в части HAVING:

21.Выдать список отделов, в которых средние оклады ниже среднего оклада по предприятию:

SELECT depno, avg(salary) GROUP BY depno HAVING avg(salary) < (SELECT avg(salary) FROM emp);

Предложение UNION позволяет объединять результаты нескольких запросов SELECT для реализации соответствующей операции реляционной алгебры. Результаты этих запросов должны быть построены по одной схеме.

Предложение ORDER BY может встречаться в таком запросе один раз – в конце последнего предложения SELECT.

Пример использования операции UNION:

22.Посчитать количество сотрудников по всем отделам (включая те отделы, в которых нет сотрудников):

SELECT depno, count(*), ' сотрудник(а)' SELECT depno, 0, ' сотрудников' WHERE depno NOT IN (SELECT depno FROM emp) ORDER BY 1; /* упорядочение по первому столбцу */ А с помощью подзапроса в части SELECT можно запрос из примера написать гораздо короче. (К сожалению, использование подзапроса в части SELECT поддерживается не всеми СУБД).

Пример использования подзапросов в части SELECT:

23.Подсчёт количества сотрудников по всем отделам (включая те отделы, в которых нет сотрудников):

SELECT depno, (SELECT count(*) FROM emp e ORDER BY 1; /* упорядочение по первому столбцу */ В команде SELECT можно обратиться к одной и той же таблице несколько раз. При этом для каждой таблицы необходимо задать свой алиас, чтобы можно было обращаться к полям этих таблиц. Система будет выполнять такой запрос на основе декартова произведения таблиц, поэтому необходимо указывать условие соединения. А для того чтобы исключить соединение записи